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;