存储过程复合变量
存储过程:
复合变量
Pl/Sql记录
DECLARE
TYPE content_record_type is RECORD (
name epp_t_content.content_ky%TYPE);
content_record content_record_type;
Begin
select content_ky INTO content_record
from epp_t_content where content_id = '1001022203';
dbms_output.put_line('雇员名:'||content_record.name);
End;
Pl/Sql表
declare
type content_table_type is table of epp_t_content.content_ky%TYPE
index by binary_integer;
content_table content_table_type;
begin
select content_ky into content_table(-1) from epp_t_content
where content_id = '1001023613';
dbms_output.put_line('雇员:'||content_table(-1));
end;
嵌套表
CREATE OR REPLACE TYPE content_type as OBJECT(
name varchar2(10),
salary NUMBER(6, 2),
hiredate DATE);
CREATE OR REPLACE TYPE content_array is TABLE OF content_type;
create table department(
deptno number(2),
dname varchar2(10),
content content_array
)NESTED TABLE content store as content;
建立了两个表,但只能操作department表不能操作content表
插入语句:
insert into department values(2, 'test', content_array(content_type('10000001',123, sysdate)
,content_type('10000002',123, sysdate),
content_type('10000003',123, sysdate)));
varray
create type content_type as object(
title varchar2(30),
pubdate Date
);
create type content_array is varray(20) of content_type;
create table author(
id number(6),
name varchar2(10),
content content_array
);
insert into author values(1, 'test', content_array(content_type('111',sysdate)))
表结构与上类似,但是没有多出一个content表。