PL/SQL学习 第二节

---

Conllection Type

PL/SQL 有 Collection Type ：关联数组 [(associative array)][1], VARRAY [(variable-size array)][1], and nested table。三种声明的都必须初始化， VARRAY 和 Nestes Table 这两种有构造函数，关联数组 [associative arraty][1] 没有构造函数。

[【VARRAY 】][1]：可以用构造函数修改和初始化数组，也可以通过下标值来进行访问：

set serveroutput on;DECLARETYPE People IS VARRAY(10) OF VARCHAR2(12);Stu People := People('Yang','Wang','Zhang','Li');PROCEDURE print_item(    parm_head VARCHAR2)ISBEGIN  dbms_output.put_line(parm_head);  FOR i IN Stu.FIRST .. Stu.LAST  LOOP    dbms_output.put_line(i ||' : '|| Stu(i));  END LOOP;   dbms_output.put_line('________________');END;BEGIN  print_item('First:"');  Stu(2) := 'kai';  print_item('Seccend:');  Stu := People('Wu','Gu','Text','helo');  print_item('Three:');end;

上面示例中，如果在定义类型的时候把 VARRAY 换成 TABLE（嵌套表） 例子同样成立。
在循环的时候还可以用 While , 只是如果通过他内部提供的方法来访问的时候 需要有一小点改变:

SET serveroutput ON;DECLARETYPE People IS VARRAY(10) OF VARCHAR2(12);Stu People    := People('Yang','Wang','Zhang','Li');i PLS_INTEGER := Stu.FIRST;PROCEDURE print_item(    parm_head VARCHAR2)ISBEGIN  dbms_output.put_line(parm_head);  WHILE i IS NOT NULL  LOOP    dbms_output.put_line(i ||' : '|| Stu(i));    i := Stu.NEXT(i);  END LOOP;  dbms_output.put_line('________________');END;BEGIN  print_item('First:"');  i      := Stu.FIRST();  Stu(2) := 'kai';  print_item('Seccend:');  i   := Stu.FIRST();  Stu := People('Wu','Gu','Text','helo');  print_item('Three:');END;

Table 和 VARRAY 在内存分配来说 TABLE 是允许离散的，就是 下标值可以有间隔 如 ： Stu(1) , Stu (3) …… 等等。[VARRAY 是确定大小的， Table 可以动态增长其长度][3]

Table 还有一种用法（ 我理解为另一种用法） [associative array] [1] :

动态数组，这种方式没有构造函数， 如果定义了需要手动初始化，其值：

DECLARETYPE PeopleIS  TABLE OF VARCHAR2(23) INDEX BY PLS_INTEGER;  Stu People;  i PLS_INTEGER := Stu.FIRST;PROCEDURE print_item(    parm_head VARCHAR2)ISBEGIN  dbms_output.put_line(parm_head);  WHILE i IS NOT NULL  LOOP    dbms_output.put_line(i ||' : '|| Stu(i));    i := Stu.NEXT(i);  END LOOP;  dbms_output.put_line('________________');END;BEGIN  Stu(1) := 'Yang';  Stu(2) := 'Wang';  Stu(3) := 'Xhang';  Stu(4) := 'Li';  print_item('First:"');  i      := Stu.FIRST();  Stu(2) := 'kai';  print_item('Seccend:');  i := Stu.FIRST();  --  Stu := People('Wu','Gu','Text','helo'); --没有构造函数  print_item('Three:');END;

初始化 AssociatIve array 比较方便的好的方法是用一个函数去初始化，可以在程序包中完成这件事情。

程序包：

--*包头*CREATE OR REPLACE PACKAGE My_Types IS  TYPE My_AA IS TABLE OF VARCHAR(20) INDEX BY PLS_INTEGER;  FUNCTION Init_My_AA RETURN My_AA;END My_Types;

在这个程序包中 自定义了一个 关联数组 My_AA 和一个用于初始化的函数。

包体

CREATE OR REPLACE PACKAGE BODY My_Types IS  FUNCTION Init_My_AA RETURN My_AA IS    ret My_AA;  BEGIN    ret(1) := 'HOW';    ret(2) := 'ARE';    ret(3) := 'YOU';    RETURN ret;  END Init_My_AA;END My_Types;

程序包体，包体的名称必须跟包头内声明的一样，包体内实现了 对数组 My_AA 的初始化。

测试调用

 DECLARE  v My_Types.My_AA := My_Types.Init_My_AA();  Idx PLS_INTEGER := v.FIRST();  PROCEDURE print_my_aa(my_ar My_Types.My_AA) IS  BEGIN    IF my_ar.COUNT() = 0 THEN      dbms_output.put_line('empty');      RETURN;    END IF;    FOR i IN my_ar.FIRST() .. my_ar.COUNT() LOOP      dbms_output.put_line(my_ar(i));    END LOOP;  END print_my_aa;BEGIN  print_my_aa(v);  dbms_output.put_line(' Counts : ' || v.Count());END;

创建一个隐含代码块来调用，[注意][2] 在声明的时候要用 程序包名 . 变量名 的格式，在调用初始化函数的时候也要这样。 上面例子中 用一个存储过程来打印结果。

在用赋值语句赋值的时候，也可以把一个集合赋值给一个已经存在的集合。但是要是同样的数据类型，元素类型一样也不行。

DECLARE  TYPE pro IS VARRAY(6) OF VARCHAR2(12);  TYPE pro_1 IS VARRAY(6) oF VARCHAR2(12);  group1 pro := pro('how', 'are', 'you');  group2 pro;  group3 pro_1 := pro_1('Just', 'So', 'So');BEGIN  group2 := group1;  group2 := group3;END;ORA-06550: 第 11 行, 第 13 列: PLS-00382: 表达式类型错误ORA-06550: 第 11 行, 第 3 列: PL/SQL: Statement ignored

上面， group2 ，group3 元素类型是一样的，但 type 重定义的数据类型不一样，产生异常。

Conllection 类型 的变量也可以声明称数组 （二维数组）：

DECLARE  TYPE t1 IS VARRAY(10) OF VARCHAR2(23);  TYPE nt1 IS VARRAY(10) OF t1;  va  t1 := t1('hello', 'where', 'are', 'you', 'from');  nva nt1 := nt1(va,                 t1('I`m', 'KanKan', 'What`s'),                 t1('Today', 'Before', 'Yesterday'),                 va);  PROCEDURE print_nt1(parm_nt1 nt1) IS  BEGIN    IF parm_nt1 IS NULL THEN      dbms_output.put_line('NULL');      RETURN;    ELSIF parm_nt1.COUNT() = 0 THEN      dbms_output.put_line('empty');      RETURN;    ELSE      FOR i IN nva.FIRST() .. nva.COUNT() LOOP        FOR j IN nva(i).FIRST() .. nva(i).COUNT() LOOP          dbms_output.put('   ' || nva(i) (j) || '  ');        END LOOP;        dbms_output.put_Line('');      END LOOP;    END IF;  END print_nt1;BEGIN  dbms_output.put_line('-------------------START BEFORE--------------------------');  print_nt1(nva);  dbms_output.put_line('--------------------EDN AFTER----------------------------');  nva.EXTEND();  nva(nva.COUNT) := va;  print_nt1(nva);  dbms_output.put_line(nva.COUNT);END;

创建一个二维数组 ，访问方式跟 C 语言 类似。如果需要扩展的时候可以用 [EXTEND][1] 关键字，如上面 EXTEND 之后，数组的长度就 +1 。

存储过程、游标、Nest Table

用隐含代码块儿来演示：

纯属自己熟悉语法：

DECLARE  Idx PLS_INTEGER := 0;    --定义recordTYPE re_empIS  RECORD  (    t_name EMP.ENAME%TYPE ,    t_mgr EMP.MGR%TYPE);  --  定义一个record 用于存储游标值  --定义tabletype tb_empIS  TABLE OF re_emp;  -- 定义变量  emps tb_emp;  -- 定义table， 保存多条记录  --游标  CURSOR cur(num NUMBER)  IS    SELECT ename ,MGR FROM emp WHERE sal < num;   -- 定义一个游标从表中取出满足条件的记录  --存储过程PROCEDURE pro_search_emps(    t_num IN NUMBER,    t_es OUT tb_emp )IS  es re_emp;   -- 定义临时的 record BEGIN  dbms_output.put_line('______ procedure begin _____');  dbms_output.put_line('t_num : '|| t_num);  t_es := tb_emp();  -- 初始化table  否则会有 Exception  FOR es IN cur(t_num) -- 往Table 里面添加数据 （1）  LOOP    t_es.EXTEND();  -- 再 Table 末尾添加一个 NULL 值    Idx       := Idx +1;    t_es(Idx) := es;  END LOOP;END pro_search_emps;  --存储过程往 定义的Table中添加数据BEGIN  pro_search_emps(13354,emps);  IF emps     IS NOT NULL THEN    Idx       := emps.FIRST;    WHILE Idx IS NOT NULL    LOOP      dbms_output.put_line(emps(Idx).t_name);      Idx := emps.NEXT(Idx);    END LOOP;  ELSE    dbms_output.put_line('emps  is Empty');  END IF;END;

目的：
定义一个 Record ，用来存储从 Cursor 里面取出来的数据。 又定义了一个 Table 存储多条记录。
注意：
- 在使用定义变量之前一定要先初始化，定义变量尽量在定义类型之前

刚才 Demo 里面的 for 循环如果换成 while 方式 代码如下：

    OPEN cur(t_num);     FETCH cur INTO es;    IF cur%notfound THEN      dbms_output.put_line('查无信息');      RETURN;    ELSE          WHILE cur%found          LOOP            t_es.EXTEND();            Idx       := Idx +1;            t_es(Idx) := es;            FETCH cur INTO es;          END LOOP;    END IF;    CLOSE cur;

把 for <var> in <Cursor> loop <statement> end loop; 这部分语句 替换成上面的 while 方式。私以为： while 方式 比较容易理解，但 for 更简短。

Mark 完了……