Oracle中复合数据类型例子

declare
  type emp_record_type is record(
       name scott.emp.ename%type,
       salary scott.emp.sal%type,
       dno scott.emp.deptno%type
  );
  emp_record emp_record_type;
begin
       select ename,sal,deptno into emp_record from scott.emp where empno=&no;
       dbms_output.put_line(emp_record.name);
end; 


declare
   type emp_record_type is record(
        name scott.emp.ename%type,
        salary scott.emp.sal%type,
        dno scott.emp.deptno%type
   );
   emp_record emp_record_type;
begin
        select ename,sal,deptno into emp_record.name,emp_record.salary,emp_record.dno from scott.emp where empno=&no;
        dbms_output.put_line(emp_record.name);
end;   


declare
        dept_record scott.dept%rowtype;
begin
        dept_record.deptno:=1;
        dept_record.dname:='administrator';
        dept_record.loc:='NC';
        insert into scott.dept values dept_record;
end;


declare
        dept_record scott.dept%rowtype;
begin
        dept_record.deptno:=2;
        dept_record.dname:='administrator';
        insert into scott.dept(deptno,dname) values(dept_record.deptno,dept_record.dname);
end; 


declare
        dept_record scott.dept%rowtype;
begin
        dept_record.deptno:=1;
        dept_record.dname:='HZY';
        update scott.dept set row=dept_record where deptno=dept_record.deptno;
end;


declare
        dept_record scott.dept%rowtype;
begin
        dept_record.deptno:=1;
        delete from scott.dept where deptno=dept_record.deptno;
end;                     
   

declare
        type ename_table_type is table of scott.emp.ename%type
        index by binary_integer;
        ename_table ename_table_type;
begin
        select ename into ename_table(-1) from scott.emp where empno=&no;
        dbms_output.put_line('雇员名:'||ename_table(-1));
end;

declare
        type area_table_type is table of number
        index by varchar2(10);
        area_table area_table_type;
begin
        area_table('北京'):=3;
        area_table('上海'):=2;
        area_table('广州'):=1;
        dbms_output.put_line(area_table('北京'));
        dbms_output.put_line(area_table.last);
end;        


declare
        type ename_table_type is table of scott.emp.ename%type;
        ename_table ename_table_type;
begin
        ename_table:=ename_table_type('MARY','a','MARY');
        select ename into ename_table(2) from scott.emp where empno=&no;
        --for i in 1..ename_table.count loop
        dbms_output.put_line('雇员名:'||ename_table(2));
        --end loop;
end;        

create type phone_type is table of varchar2(20);

create table employee(
       id number(4),name varchar2(10),sal number(6,2),
       phone phone_type
)nested table phone store as phone_table;


begin
        insert into employee values(1,'scott',800,phone_type('0471-3456788','13804711111'));
end;

declare
        phone_table phone_type;
begin
        select phone into phone_table from employee where id=1;
        for i in 1..phone_table.count loop
            dbms_output.put_line('电话号码:'||phone_table(i));
            end loop;
end;        
    

declare
            phone_table phone_type:=phone_type('0471-3456788','13804711111','0471-2233066','13056278568');
begin
            update employee set phone=phone_table
            where id=1;
end;            


declare
            type ename_table_type is varray(20) of scott.emp.ename%type;
            ename_table ename_table_type:=ename_table_type('A','B','Mary');
begin
            select ename into ename_table(1) from scott.emp where empno=&no;
           -- for i in 1..ename_table.count loop             
                dbms_output.put_line('雇员名:'||ename_table(1));       
           -- end loop;
end;            

create type phone_type is varray(20) of varchar2(20);
create table employee(
              id number(4),name varchar2(10),sal number(6,2),
       phone phone_type
);

begin
       insert into employee values(1,'SCOTT',800,phone_type('0471-3456788','13804711111'));
end;

declare
       phone_table phone_type;
begin
       select phone into phone_table from employee where id=1;
       for i in 1..phone_table.count loop
           dbms_output.put_line(phone_table(i));
       end loop;
end;
 

declare
       type emp_table_type is table of scott.emp%rowtype
       index by binary_integer;
       emp_table emp_table_type;
begin
       select * into emp_table(1) from scott.emp where empno=&no;
       dbms_output.put_line('雇员姓名:'||emp_table(1).ename);
end;       

 
declare
       type ename_table_type is table of scott.emp.ename%type;
       ename_table ename_table_type:=ename_table_type('aa');
begin
       if ename_table.exists(1) then
          ename_table(1):='SCOTT';
          dbms_output.put_line(ename_table(1));
       else
          dbms_output.put_line('必须初始化集合元素');
       end if;
end;       
     


declare
       type ename_table_type is table of scott.emp.ename%type
       index by binary_integer;
       ename_table ename_table_type;
begin
       ename_table(-5):='SCOTT';
       ename_table(-2):='aa';
       ename_table(1):='bb';
       ename_table(0):='dd';
       dbms_output.put_line('集合元素总个数:'||ename_table.count);
       dbms_output.put_line('第一个元素:'||ename_table.first);
       dbms_output.put_line('最后一个元素:'||ename_table.last);
       dbms_output.put_line('元素0的前一个元素:'||ename_table.prior(0));
       dbms_output.put_line('元素-2的后一个元素:'||ename_table.next(-2));
end;       


declare
       type ename_table_type is varray(20) of varchar2(10);
       ename_table ename_table_type;
begin
       ename_table:=ename_table_type('Mary');
       ename_table.extend(5);
       dbms_output.put_line('元素总个数:'||ename_table.count);
       for i in 1..ename_table.count loop 
           dbms_output.put_line(ename_table(i));
       end loop;
end;       

declare
       type ename_table_type is table of varchar2(10);
       ename_table ename_table_type;
begin
       ename_table:=ename_table_type('A','B','C','D','E');
       ename_table.trim;
       dbms_output.put_line('元素总个数:'||ename_table.count);
end;


declare
       type ename_table_type is table of scott.emp.ename%type
       index by binary_integer; 
       ename_table ename_table_type;
begin
       ename_table(-5):='SCOTT';
       ename_table(-2):='aa';
       ename_table(1):='bb';
       ename_table(0):='dd';
       ename_table.delete(-2);

       dbms_output.put_line('元素总个数:'||ename_table.count);
end;       
       
select * from scott.dept;
select ename,sal,deptno from scott.emp;
select * from employee;