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中使用,不能在数据库中使用,可以定义类型、数据类型字段;可以在函数、存储过程中做参数使用。
