Oracle Nested table、Record

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

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

  1 DECLARE
  2  /**创建一个 nested table **/
  3  TYPE type_nstb_noind_var is table of varchar2(300); 
  4  /** 创建一个索引类型为varchar的nested table,也可以指定其它类型(BINARY_INTEGER、PLS_INTEGER、String、Long、RAW、LONG RAW、ROWID,CHAR,CHARACTER) **/
  5  Type type_nstb_var is table of varchar2(300) index by varchar2(30);
  6 
  7  /** 创建一个包含索引并且不能为空的 nested table **/
  8  Type type_nstb_con_notnull is table of varchar2(300) index by varchar2(300) not null;
  9   /** 根据 emp 表的ename列类型创建 nested table **/ 
 10  TYPE type_nstb_ename is table of emp.ename%type;
 11  
 12  /**定义游标 **/
 13  CURSOR MYCUR IS SELECT ENAME FROM EMP;
 14   
 15  /**根据游标类型创建 nested table **/
 16  type type_nestb_bycur is table of mycur%rowtype;
 17  
 18   /** nested table 没有存储值限制,未初始化时默认值为null,使用时要引用 **/
 19  /** 定义变量引用 **/
 20  v_noind type_nstb_noind_var;
 21  v_var type_nstb_var;
 22  v_noind_append type_nstb_noind_var;
 23  v_noind_delete type_nstb_noind_var;
 24  
 25  PROCEDURE P(STR VARCHAR2) is
 26  BEGIN
 27  DBMS_OUTPUT.PUT_LINE(STR);
 28  END;
 29  
 30  BEGIN
 31  /**初始化 nested table:**/
 32  v_noind:=type_nstb_noind_var('American','China','Japan'); /**每个位值不能超过指定最大值**/
 33  P('get v_noind first index value: '||V_NOIND.FIRST);/**获得第一个索引下标值**/
 34  P('get v_noind last index value: '||V_NOIND.LAST); /**获得最后一个索引下标值**/
 35  P('get v_noind by index 1: '||V_NOIND(1)); /**获得第一个索引下标对应值**/
 36  p('get v_noind first value by first index value: '||v_noind(v_noind.first)); /**也可以这样获取值**/
 37  v_noind(3):='Englind'; /**替换一个索引下标对应的值**/
 38  
 39  /**输出所有值 **/
 40   FOR J IN V_NOIND.FIRST..V_NOIND.LAST LOOP
 41   P('using loop print v_noind('||j||') value:'||V_NOIND(J));
 42  END LOOP;
 43  
 44   /** 从emp表中取出ename列所有值到nested table **/
 45   SELECT ENAME BULK COLLECT INTO V_NOIND FROM EMP;
 46   
 47   /**如果涉及操作的数据量比较大,可以先定义游标,然后分批从游标中提取数据**/
 48   OPEN MYCUR; /** 打开游标 **/
 49    FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10; /**10代表每次提取的数据量,这在批量处理更新数据是会很有用**/
 50   LOOP 
 51     FOR I IN V_NOIND.FIRST..V_NOIND.LAST LOOP
 52      P('v_noind('||i||')value:'||V_NOIND(I));
 53     END LOOP;
 54    FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10;    
 55   EXIT WHEN MYCUR%NOTFOUND; /** 当游标内无数据是退出循环 **/
 56    END LOOP;
 57   CLOSE MYCUR; /** 关闭游标 **/
 58   
 59   /** 也可以像这样取数 **/
 60    OPEN MYCUR;
 61    LOOP FETCH MYCUR BULK COLLECT INTO V_NOIND LIMIT 10;
 62      FOR I IN V_NOIND.FIRST..V_NOIND.LAST LOOP
 63      P(V_NOIND(I));
 64     END LOOP;
 65     EXIT WHEN MYCUR%NOTFOUND; 
 66    END LOOP;
 67  
 68  /** Nested table 未指定索引下标类型时,默认为number类型;如果想保存其它类型的索引下标类型,则在创建Nested table 时指定 INDEX BY VARCHAR2(300)或其它类型(BINARY_INTEGER、PLS_INTEGER、String、Long、RAW、LONG RAW、ROWID,CHAR,CHARACTER) **/ 
 69  /**为索引下标类型为varchar2的nested table赋值**/
 70    v_var('A'):='China';
 71    v_var('B'):='Japen';
 72    v_var('C'):='German';
 73    p('get v_var first index value: '||v_var.first);  /**获得第一个索引下标**/
 74    p('get v_var first value: '||v_var(v_var.first));  /**获得第一个索引下标对应的值**/
 75    p('get v_var contain element count: '||v_var.count); /**获得Nested table 存储总数**/
 76    if(v_var.exists('B'))then /**判断某个索引下标值是否存在**/
 77    p('China has in v_var nested table');
 78    end if;
 79    
 80    p('get v_var next value by index "B":'||v_var(v_var.next('B'))); /** 获取索引下标'B'的下一个值 **/
 81    p('get v_var prior value by index "C":'||v_var(v_var.prior('C'))); /** 获取索引下标'C'的上一个值 **/
 82 
       /**遍历**/
      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;

 83    /**向nested table 内添加项,此方法不能用于指定了索引下标类型 INDEX BY ..的nested table **/
 84    v_noind_append:=type_nstb_noind_var();
 85    v_noind_append.extend; /**向nested table 内添加一个空项**/
 86    v_noind_append.extend(2); /** 向nested table 内添加一个索引下标为2的项 **/
 87    v_noind_append(2):='Append2'; /** 为刚添加的索引下标项2赋值 **/
 88     v_noind_append.extend(3,2);  /** 根据索引下标为2 的项在nested table 内添加3个新项,值一样,但索引下标不一样**/
 89    for i in v_noind_append.first..v_noind_append.last loop
 90    p('v_noind('||i||'):'||v_noind_append(i)); 
 91    end loop;
 92    
 93     v_noind_delete:=type_nstb_noind_var('one','two','three','four','five','six','seven','eight','night','ten');
 94     v_noind_delete.delete(1); /**删除nested table 项1**/
 95     v_noind_delete.delete(2,4);/**删除第2项到第4项**/
 96     v_var.delete('A'); /**如果定义type是使用了INDEX BY.. 指定索引项类型,则可以通过key删除项**/
 97     for i in v_noind_delete.first..v_noind_delete.last loop /**注意如果删除中间项后打印会报错(ORA-01403: no data found)**/
 98    p('print after delete v_noind_delete('||i||'):'||v_noind_delete(i)); 
 99    end loop;   
100 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中使用,不能在数据库中使用,可以定义类型、数据类型字段;可以在函数、存储过程中做参数使用。