Oracle Nested table、Record

1.如何在PL/SQL中创建和使用Nested table;
2.如何在PL/SQL中创建和使用Record;

1.如何在PL/SQL中创建和使用Nested table

DECLARE
 /**创建一个 nested table **/
 TYPE type_nstb_noind_var is table of varchar2(300); 
 /** 创建一个索引类型为varchar的nested table,也可以指定其它类型(BINARY_INTEGER、PLS_INTEGER、String、Long、RAW、LONG RAW、ROWID,CHAR,CHARACTER) **/
 Type type_nstb_var is table of varchar2(300) index by varchar2(30);

 /** 创建一个包含索引并且不能为空的 nested table **/
 Type type_nstb_con_notnull is table of varchar2(300) index by varchar2(300) not null;
  /** 根据 emp 表的ename列类型创建 nested table **/ 
 TYPE type_nstb_ename is table of emp.ename%type;
 
 /**定义游标 **/
 CURSOR MYCUR IS SELECT ENAME FROM EMP;
  
 /**根据游标类型创建 nested table **/
 type type_nestb_bycur is table of mycur%rowtype;
 
  /** nested table 没有存储值限制,未初始化时默认值为null,使用时要引用 **/
 /** 定义变量引用 **/
 v_noind type_nstb_noind_var;
 v_var type_nstb_var;
 v_noind_append type_nstb_noind_var;
 v_noind_delete type_nstb_noind_var;
 
 PROCEDURE P(STR VARCHAR2) is
 BEGIN
 DBMS_OUTPUT.PUT_LINE(STR);
 END;
 
 BEGIN
 /**初始化 nested table:**/
 v_noind:=type_nstb_noind_var('American','China','Japan'); /**每个位值不能超过指定最大值**/
 P('get v_noind first index value: '||V_NOIND.FIRST);/**获得第一个索引下标值**/
 P('get v_noind last index value: '||V_NOIND.LAST); /**获得最后一个索引下标值**/
 P('get v_noind by index 1: '||V_NOIND(1)); /**获得第一个索引下标对应值**/
 p('get v_noind first value by first index value: '||v_noind(v_noind.first)); /**也可以这样获取值**/
 v_noind(3):='Englind'; /**替换一个索引下标对应的值**/
 
 /**输出所有值 **/
  FOR J IN V_NOIND.FIRST..V_NOIND.LAST LOOP
  P('using loop print v_noind('||j||') value:'||V_NOIND(J));
 END LOOP;
 
  /** 从emp表中取出ename列所有值到nested table **/
  SELECT ENAME BULK COLLECT INTO V_NOIND FROM EMP;
  
  /**如果涉及操作的数据量比较大,可以先定义游标,然后分批从游标中提取数据**/
  OPEN MYCUR; /** 打开游标 **/
   FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10; /**10代表每次提取的数据量,这在批量处理更新数据是会很有用**/
  LOOP 
    FOR I IN V_NOIND.FIRST..V_NOIND.LAST LOOP
     P('v_noind('||i||')value:'||V_NOIND(I));
    END LOOP;
   FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10;    
  EXIT WHEN MYCUR%NOTFOUND; /** 当游标内无数据是退出循环 **/
   END LOOP;
  CLOSE MYCUR; /** 关闭游标 **/
  
  /** 也可以像这样取数 **/
   OPEN MYCUR;
   LOOP FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10;
     FOR I IN V_NOIND.FIRST..V_NOIND.LAST LOOP
     P(V_NOIND(I));
    END LOOP;
    EXIT WHEN MYCUR%NOTFOUND; 
   END LOOP;
 
 /** Nested table 未指定索引下标类型时,默认为number类型;如果想保存其它类型的索引下标类型,则在创建Nested table 时指定 INDEX BY VARCHAR2(300)或其它类型(BINARY_INTEGER、PLS_INTEGER、String、Long、RAW、LONG RAW、ROWID,CHAR,CHARACTER) **/ 
 /**为索引下标类型为varchar2的nested table赋值**/
   v_var('A'):='China';
   v_var('B'):='Japen';
   v_var('C'):='German';
   p('get v_var first index value: '||v_var.first);  /**获得第一个索引下标**/
   p('get v_var first value: '||v_var(v_var.first));  /**获得第一个索引下标对应的值**/
   p('get v_var contain element count: '||v_var.count); /**获得Nested table 存储总数**/
   if(v_var.exists('B'))then /**判断某个索引下标值是否存在**/
   p('China has in v_var nested table');
   end if;
   
   p('get v_var next value by index "B":'||v_var(v_var.next('B'))); /** 获取索引下标'B'的下一个值 **/
   p('get v_var prior value by index "C":'||v_var(v_var.prior('C'))); /** 获取索引下标'C'的上一个值 **/

       /**遍历**/
      v_first:=v_var.first;
      loop
      dbms_output.put_line(v_first);
      v_first:=v_var.next(v_first);
     if(v_first=v_var.last)then
       exit;
       end if;
     end loop;

   /**向nested table 内添加项,此方法不能用于指定了索引下标类型 INDEX BY ..的nested table **/
   v_noind_append:=type_nstb_noind_var();
   v_noind_append.extend; /**向nested table 内添加一个空项**/
   v_noind_append.extend(2); /** 向nested table 内添加一个索引下标为2的项 **/
   v_noind_append(2):='Append2'; /** 为刚添加的索引下标项2赋值 **/
    v_noind_append.extend(3,2);  /** 根据索引下标为2 的项在nested table 内添加3个新项,值一样,但索引下标不一样**/
   for i in v_noind_append.first..v_noind_append.last loop
   p('v_noind('||i||'):'||v_noind_append(i)); 
   end loop;
   
    v_noind_delete:=type_nstb_noind_var('one','two','three','four','five','six','seven','eight','night','ten');
    v_noind_delete.delete(1); /**删除nested table 项1**/
    v_noind_delete.delete(2,4);/**删除第2项到第4项**/
    v_var.delete('A'); /**如果定义type是使用了INDEX BY.. 指定索引项类型,则可以通过key删除项**/
    for i in v_noind_delete.first..v_noind_delete.last loop /**注意如果删除中间项后打印会报错(ORA-01403: no data found)**/
   p('print after delete v_noind_delete('||i||'):'||v_noind_delete(i)); 
   end loop;   
END;

2.如何在PL/SQL中创建和使用Record

declare
 type nstb_job is table of varchar2(30) ;

/**定义Record**/
type ts_record is record 
(
id emp.empno%type, /**定义record字段id类型是emp表empno列类型**/
name varchar2(30), /**定义record字段name类型是varchar2**/
dept Tdept%rowtype, /**定义record 字段dept类型是tdept表 **/
item_no number(5) not null :=10,/**定义record字段item_no字段不能为空且默认值是10 注:指定不能为空时必须有默认值**/
job NSTB_JOB, /**定义record字段job 类型是nested table**/
orderno number  :=10 /**定义record 字段orderno并初始化**/
);
rd_emp emp%rowtype; /**定义record tb_emp,字段内容是emp表**/
ts ts_record; /**使用record前必须引用**/

 PROCEDURE P(STR VARCHAR2) is
 BEGIN
 DBMS_OUTPUT.PUT_LINE(STR);
 END;

begin
    /**初始化record***/
    ts.id:=100;
    ts.name:='sywu';
    ts.dept.deptno:=12; /**因为dept是类型(表),初始化时必须初始化类型(表)**/
    ts.dept.dname:='IBM';
    ts.dept.loc:='ssdtes';
    ts.job:=NSTB_JOB('java developer','oracle database administrator'); /**初始化job,注意:如果定义类型时指定了Index by .. 是不能这样初始化的,会报类型不在此区域错误**/
 p('id:'||ts.id||' name:'||ts.name||' deptno:'||ts.dept.deptno||' job:'||ts.job(ts.job.first));

 /**将dept 表中的某一列导入到record ts的dept 类型中**/ 
 select * into ts.dept from dept where rownum=1;
 p('deptno:'||ts.dept.deptno||' dname:'||ts.dept.dname||' loc:'||ts.dept.loc);
 
 /**将emp 表中的某一列导入到record rd_emp中**/
 select * into rd_emp from emp where empno=7369;
 p('empno:'||rd_emp.empno||' job:'||rd_emp.job);
end;

总结:Nested table: 无存储值限值,可以存储一条或多条记录,无需初始化,默认为null,可以在数据库或PL/SQL中使用.可以使用默认索引下标或定义索引下标,自定义添加删除项,可以分批提取游标中的数据,使用时必须引用;
Record:一次运行中只能提取一条记录,只能在PL/SQL中使用,不能在数据库中使用,可以定义类型、数据类型字段;可以在函数、存储过程中做参数使用。