PL/SQL复合数据类型

--一.PL/SQL复合数据类型
--(一).PL/SQL记录
--1.定义PL/SQL记录
--(1).定义PL/SQL记录
--Grammar
TYPE type_name IS RECORD(
    field_declaration1[,
    field_declaration2]...
);
idetifier type_name;

--Demo1
DECLARE
    TYPE emp_record_type IS RECORD(
        name emp.ename%TYPE,
        salary emp.sal%TYPE,
        dno emp.deptno%TYPE
    );
    emp_record emp_record_type;
...

--(2).使用%ROWTYPE属性定义记录变量
--Grammer
identifier table_name%ROWTYPE;
identifier view_name%ROWTYPE;

--Demo1
dept_record dept%ROWTYPE;
emp_record emp%ROWTYPE;

--2.使用PL/SQL记录
--Demo1:SELECT INTO 中用PL/SQL记录
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE emp_record_type IS RECORD(
  3             name emp.ename%TYPE,
  4             salary emp.sal%TYPE,
  5             dno emp.deptno%TYPE
  6     );
  7     emp_record emp_record_type;
  8  BEGIN
  9     SELECT ename,sal,deptno INTO emp_record
 10     FROM emp
 11     WHERE empno=&no;
 12     dbms_output.put_line(emp_record.name);
 13  END;
 14  /
输入 no 的值:  7788
原值   11:      WHERE empno=&no;
新值   11:      WHERE empno=7788;
SCOTT

PL/SQL 过程已成功完成。

--Demo2:SELECT INTO 中用PL/SQL记录成员变量记录
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE emp_record_type IS RECORD(
  3             name emp.ename%TYPE,
  4             salary emp.sal%TYPE,
  5             dno emp.deptno%TYPE
  6     );
  7     emp_record emp_record_type;
  8  BEGIN
  9     SELECT ename,sal INTO emp_record.name,emp_record.salary
 10     FROM emp
 11     WHERE empno=&no;
 12     dbms_output.put_line(emp_record.name);
 13  END;
 14  /
输入 no 的值:  7369
原值   11:      WHERE empno=&no;
新值   11:      WHERE empno=7369;
SMITH

PL/SQL 过程已成功完成。

--Demo3:INSERT 中用PL/SQL记录
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     dept_record dept%ROWTYPE;
  3  BEGIN
  4     dept_record.deptno:=50;
  5     dept_record.dname:='ADMINISTRATOR';
  6     dept_record.loc:='BEIJING';
  7     INSERT INTO dept VALUES dept_record;
  8     dbms_output.put_line('新增'||SQL%ROWCOUNT||'');
  9  END;
 10  /
新增1行

PL/SQL 过程已成功完成。

--Demo4:INSERT VALUES 中用PL/SQL记录成员
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     dept_record dept%ROWTYPE;
  3  BEGIN
  4     dept_record.deptno:=60;
  5     dept_record.dname:='SALES';
  6     dept_record.loc:='BEIJING';
  7     INSERT INTO dept(deptno,dname)
  8     VALUES (dept_record.deptno,dept_record.dname);
  9     dbms_output.put_line('新增'||SQL%ROWCOUNT||'');
 10  END;
 11  /
新增1行

PL/SQL 过程已成功完成。

--Demo5:UPDATE SET 中用PL/SQL记录变量
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     dept_record dept%ROWTYPE;
  3  BEGIN
  4     dept_record.deptno:=30;
  5     dept_record.dname:='SALES';
  6     dept_record.loc:='SHANGHAI';
  7     UPDATE dept SET ROW=dept_record
  8     WHERE deptno=&deptno;
  9     dbms_output.put_line('修改'||SQL%ROWCOUNT||'');
 10  END;
 11  /
输入 deptno 的值:  30
原值    8:      WHERE deptno=&deptno;
新值    8:      WHERE deptno=30;
修改1行

PL/SQL 过程已成功完成。

--Demo6:UPDATE SET 中用PL/SQL记录变量成员
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     dept_record dept%ROWTYPE;
  3  BEGIN
  4     dept_record.loc:='GUANGZHOU';
  5     UPDATE dept SET loc=dept_record.loc
  6     WHERE deptno=&deptno;
  7     dbms_output.put_line('修改'||SQL%ROWCOUNT||'');
  8  END;
  9  /
输入 deptno 的值:  10
原值    6:      WHERE deptno=&deptno;
新值    6:      WHERE deptno=10;
修改1行

PL/SQL 过程已成功完成。

--Demo7:DELETE 中用PL/SQL记录成员(只能用成员)
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     dept_record dept%ROWTYPE;
  3  BEGIN
  4     dept_record.deptno:=50;
  5     DELETE FROM dept WHERE deptno=dept_record.deptno;
  6     dbms_output.put_line('删除'||SQL%ROWCOUNT||'');
  7  END;
  8  /
删除1行

PL/SQL 过程已成功完成。

--(二).PL/SQL集合
--单行单列用标量变量
--单行多列用PL/SQL记录
--多行单列用PL/SQL集合
--集合类型包括索引表,嵌套表,变长数组
--1.索引表,也称为PL/SQL表
--特性:索引表下标可以为负数;索引表的元素个数没有限制;索引表只能作为PL/SQL复合数据类型使用;索引表不能作为表列类型使用

--Grammer:key_type可以使用数据类型BINARY_INTEGER,PLS_INTEGER,VARCHAR2
TYPE type_name IS TABLE OF element_type
[NOT NULL] INDEX BY key_type;
identifier type_name;

--Demo1
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE
  3     INDEX BY BINARY_INTEGER;
  4     ename_table ename_table_type;
  5  BEGIN
  6     SELECT ename INTO ename_table(-1)
  7     FROM emp
  8     WHERE empno=&no;
  9     dbms_output.put_line('雇员名:'||ename_table(-1));
 10  END;
 11  /
输入 no 的值:  7788
原值    8:      WHERE empno=&no;
新值    8:      WHERE empno=7788;
雇员名:SCOTT

PL/SQL 过程已成功完成。

--Demo2
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE area_table_type IS TABLE OF NUMBER
  3     NOT NULL INDEX BY VARCHAR(10);
  4     area_table area_table_type;
  5  BEGIN
  6     area_table('北京'):=1;
  7     area_table('上海'):=2;
  8     area_table('广州'):=3;
  9     dbms_output.put_line('第一个元素:'||area_table.first);
 10     dbms_output.put_line('第二个元素:'||area_table.last);
 11  END;
 12  /
第一个元素:北京
第二个元素:上海

PL/SQL 过程已成功完成。

--2.嵌套表
--特性:下标从1开始;元素个数没有限制;数组元素值可以稀疏;可以作为表列的数据类型使用

--Grammer
TYPE type_name IS TABLE OF element_type;
identifier type_name;

--Demo1:SELECT INTO
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3     ename_table ename_table_type;
  4  BEGIN
  5     --构造函数初始化嵌套表变量后才能在PL/SQL中引用
  6     ename_table:=ename_table_type('A','A','A');
  7     SELECT ename INTO ename_table(2)
  8     FROM emp
  9     WHERE empno=&no;
 10     dbms_output.put_line('雇员名:'||ename_table(2));
 11  END;
 12  /
输入 no 的值:  7788
原值    9:      WHERE empno=&no;
新值    9:      WHERE empno=7788;
雇员名:SCOTT

PL/SQL 过程已成功完成。

--Demo2:表列中使用嵌套表
SQL> CREATE TYPE phone_type IS TABLE OF VARCHAR(20);
  2  /

类型已创建。

SQL> CREATE TABLE employes(
  2     id NUMBER(4),
  3     name VARCHAR2(10),
  4     sal NUMBER(6,2),
  5     phone phone_type
  6  )
  7  --使用嵌套表作为表列的数据类型时,必须要为嵌套表指定专门的存储表
  8  NESTED TABLE phone STORE AS phone_table;

表已创建。

--Demo3:在PL/SQL块中为嵌套表列插入数据
SQL> --当定义嵌套表类型时,Oracle自动为该类型生成对应的构造方法。
SQL> --当为嵌套表插入数据时,需要使用嵌套表的构造方法。
SQL> BEGIN
  2     INSERT INTO employes VALUES(2,'SMITH',1000,
  3     phone_type('020-81181817','13922390000'));
  4     dbms_output.put_line('插入'||SQL%ROWCOUNT||'');
  5  END;
  6  /
插入1条

PL/SQL 过程已成功完成。

--Demo4:在PL/SQL块中检索嵌套表列的数据
SQL> DECLARE
  2     phone_table phone_type;
  3  BEGIN
  4     SELECT phone INTO phone_table
  5     FROM employes
  6     WHERE id=1;
  7     FOR i IN 1..phone_table.COUNT LOOP
  8             dbms_output.put_line('电话号码:'||phone_table(i));
  9     END LOOP;
 10  END;
 11  /
电话号码:020-81181818
电话号码:13922390110

PL/SQL 过程已成功完成。

--Demo5:在PL/SQL中更新嵌套表列的数据
SQL> DECLARE
  2     phone_table phone_type:=phone_type('020-817191111','13922222222',
  3             '030-81111111','13911111111');
  4  BEGIN
  5     UPDATE employes SET phone=phone_table
  6             WHERE id=1;
  7     dbms_output.put_line('更新'||SQL%ROWCOUNT||'');
  8  END;
  9  /
更新1条

PL/SQL 过程已成功完成。

--3.变长数组VARRAY
--特性:下标从1开始;可以作为表列的类型使用;有限定义长度数组
--Grammer
TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];
identifier type_name;

--Demo1:PL/SQL块中使用VARRAY
SQL> DECLARE
  2     TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE;
  3     --PL/SQL块中引用VARRAY类型变量时,必须使用构造方法初始化。
  4     ename_varray ename_varray_type:=ename_varray_type('mary');
  5  BEGIN
  6     SELECT ename INTO ename_varray(1) FROM emp
  7             WHERE empno=&eno;
  8     dbms_output.put_line('雇员名:'||ename_varray(1));
  9  END;
 10  /
输入 eno 的值:  7788
原值    7:              WHERE empno=&eno;
新值    7:              WHERE empno=7788;

PL/SQL 过程已成功完成。

--Demo2:表列中使用VARRAY
SQL> DROP TABLE employes;

表已删除。

SQL> DROP TYPE phone_type;

类型已删除。

SQL>
SQL> CREATE TYPE phone_type IS VARRAY(20) OF VARCHAR2(20);
  2  /

类型已创建。

SQL> CREATE TABLE employes(
  2     id NUMBER(4),
  3     name VARCHAR2(10),
  4     sal NUMBER(6,2),
  5     phone phone_type
  6  );

表已创建。

--4.PL/SQL记录表
--特性:处理多行多列数据;结合了PL/SQL记录与PL/SQL集合的优点;
--demo1:PL/SQL块中使用PL/SQL记录表
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE emp_table_type IS TABLE OF emp%ROWTYPE
  3             INDEX BY BINARY_INTEGER;
  4     emp_table emp_table_type;
  5  BEGIN
  6     SELECT * INTO emp_table(1) FROM emp
  7             WHERE empno=&eno;
  8     dbms_output.put_line('雇员名:'||emp_table(1).ename);
  9     dbms_output.put_line('雇员工资:'||emp_table(1).sal);
 10  END;
 11  /
输入 eno 的值:  7788
原值    7:              WHERE empno=&eno;
新值    7:              WHERE empno=7788;
雇员名:SCOTT
雇员工资:1200

PL/SQL 过程已成功完成。

--5.多级集合
--demo1:在PL/SQL块中使用多级VARRAY
SQL> DECLARE
  2     --定义一维VARRAY
  3     TYPE al_varray_type IS VARRAY(10) OF INT;
  4     --定义二维VARRAY
  5     TYPE nal_varray_type IS VARRAY(10) OF al_varray_type;
  6     --初始化二维集合变量
  7     nvl nal_varray_type:=nal_varray_type(
  8             al_varray_type(1,2,3),
  9             al_varray_type(4,5,6),
 10             al_varray_type(7,8,9)
 11     );
 12  BEGIN
 13     dbms_output.put_line('显示二维数组所有元素');
 14     FOR i IN 1..nvl.COUNT LOOP
 15             FOR j IN 1..nvl(i).COUNT LOOP
 16                     dbms_output.put_line('nvl('||i||','||j||')='
 17                             ||nvl(i)(j));
 18             END LOOP;
 19     END LOOP;
 20  END;
 21  /
显示二维数组所有元素
nvl(1,1)=1
nvl(1,2)=2
nvl(1,3)=3
nvl(2,1)=4
nvl(2,2)=5
nvl(2,3)=6
nvl(3,1)=7
nvl(3,2)=8
nvl(3,3)=9

PL/SQL 过程已成功完成。

--demo2:PL/SQL块中使用多级嵌套表
SQL> DECLARE
  2     --定义一维嵌套表
  3     TYPE al_table_type IS TABLE OF INT;
  4     --定义二维嵌套表
  5     TYPE nal_table_type IS TABLE OF al_table_type;
  6     --初始化二维集合变量
  7     nvl nal_table_type:=nal_table_type(
  8             al_table_type(1,2,3),
  9             al_table_type(4,5,6),
 10             al_table_type(7,8,9)
 11     );
 12  BEGIN
 13     dbms_output.put_line('显示二维数组所有元素');
 14     FOR i IN 1..nvl.COUNT LOOP
 15             FOR j IN 1..nvl(i).COUNT LOOP
 16                     dbms_output.put_line('nvl('||i||','||j||')='
 17                             ||nvl(i)(j));
 18             END LOOP;
 19     END LOOP;
 20  END;
 21  /
显示二维数组所有元素
nvl(1,1)=1
nvl(1,2)=2
nvl(1,3)=3
nvl(2,1)=4
nvl(2,2)=5
nvl(2,3)=6
nvl(3,1)=7
nvl(3,2)=8
nvl(3,3)=9

PL/SQL 过程已成功完成。

--demo3:PL/SQL中使用多级索引表
SQL> DECLARE
  2     --定义一维索引表
  3     TYPE al_table_type IS TABLE OF INT
  4             INDEX BY BINARY_INTEGER;
  5     --定义二维索引表
  6     TYPE nal_table_type IS TABLE OF al_table_type
  7             INDEX BY BINARY_INTEGER;
  8     nvl nal_table_type;
  9  BEGIN
 10     nvl(1)(1):=1;
 11     nvl(1)(2):=2;
 12     nvl(1)(3):=3;
 13     nvl(2)(1):=4;
 14     nvl(2)(2):=5;
 15     nvl(2)(3):=6;
 16     nvl(3)(1):=7;
 17     nvl(3)(2):=8;
 18     nvl(3)(3):=9;
 19     dbms_output.put_line('显示二维数组所有元素');
 20     FOR i IN 1..nvl.COUNT LOOP
 21             FOR j IN 1..nvl(i).COUNT LOOP
 22                     dbms_output.put_line('nvl('||i||','||j||')='
 23                             ||nvl(i)(j));
 24             END LOOP;
 25     END LOOP;
 26  END;
 27  /
显示二维数组所有元素
nvl(1,1)=1
nvl(1,2)=2
nvl(1,3)=3
nvl(2,1)=4
nvl(2,2)=5
nvl(2,3)=6
nvl(3,1)=7
nvl(3,2)=8
nvl(3,3)=9

PL/SQL 过程已成功完成。

--(三).集合方法
--集合方法是Oracle提供的用于操作集合变量的内置函数或过程
--函数:EXISTS,COUNT,LIMIT,FIRST,NEXT,PRIOR,NEXT
--过程:EXTEND,TRIM,DELETE
--特性:集合方法只使用于PL/SQL,不适用于SQL语句中调用;EXTEND,TRIM只适用于嵌套表和VARRAY;
--集合方法调用语法
collection_name.method_name[(parameters)]

--1.EXISTS:确定集合元素是否存在,TRUE/FALSE
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3     ename_table ename_table_type;
  4  BEGIN
  5     IF ename_table.EXISTS(1) THEN
  6             ename_table(1):='SCOTT';
  7     ELSE
  8             dbms_output.put_line('嵌套表变量在PL/SQL块中使用时必须初始化');
  9     END IF;
 10  END;
 11  /
嵌套表变量在PL/SQL块中使用时必须初始化

PL/SQL 过程已成功完成。

--2.COUNT:集合元素个数
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE
  3             INDEX BY BINARY_INTEGER;
  4     ename_table ename_table_type;
  5     TYPE sal_table_type IS TABLE OF emp.sal%TYPE
  6             INDEX BY BINARY_INTEGER;
  7     sal_table ename_table_type;
  8  BEGIN
  9     ename_table(-9):='SCOTT';
 10     ename_table(0):='SMITH';
 11     ename_table(9):='MARY';
 12     dbms_output.put_line('集合元素总个数:'||ename_table.COUNT);
 13     dbms_output.put_line('集合元素总个数:'||sal_table.COUNT);
 14  END;
 15  /
集合元素总个数:3
集合元素总个数:0

PL/SQL 过程已成功完成。

--3.LIMIT:返回集合元素的最大个数,嵌套表和索引表的元素没有个数限制,返回NULL
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE;
  3     ename_varray ename_varray_type:=ename_varray_type('mary');
  4     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  5     ename_table ename_table_type:=ename_table_type('mary');
  6  BEGIN
  7     dbms_output.put_line('ename_varray集合元素的最大个数:'||ename_varray.LIMIT);
  8     dbms_output.put_line('ename_table集合元素的最大个数:'||ename_table.LIMIT);
  9  END;
 10  /
ename_varray集合元素的最大个数:20
ename_table集合元素的最大个数:

PL/SQL 过程已成功完成。

--4.FIRST和LAST返回集合变量第一个和最后一个的下标
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE;
  3     ename_varray ename_varray_type:=ename_varray_type('mary','mary','mary','mary');
  4     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  5     ename_table ename_table_type:=ename_table_type('mary','mary','mary','mary');
  6     TYPE ename_table1_type IS TABLE OF emp.ename%TYPE
  7             INDEX BY BINARY_INTEGER;
  8     ename_table1 ename_table1_type;
  9  BEGIN
 10     ename_table(1):='SCOTT';
 11     ename_table(2):='TIGER';
 12     ename_table(3):='MARY';
 13     ename_table(4):='BLAKE';
 14     dbms_output.put_line('第一个元素:'||ename_table.FIRST);
 15     dbms_output.put_line('最后一个元素:'||ename_table.LAST);
 16     ename_varray(1):='SCOTT';
 17     ename_varray(2):='SMITH';
 18     ename_varray(3):='MARY';
 19     ename_varray(4):='BLAKE';
 20     dbms_output.put_line('第一个元素:'||ename_varray.FIRST);
 21     dbms_output.put_line('最后一个元素:'||ename_varray.LAST);
 22     ename_table1(-5):='SCOTT';
 23     ename_table1(1):='SMITH';
 24     ename_table1(5):='MARY';
 25     ename_table1(10):='BLAKE';
 26     dbms_output.put_line('第一个元素:'||ename_table1.FIRST);
 27     dbms_output.put_line('最后一个元素:'||ename_table1.LAST);
 28  END;
 29  /
第一个元素:1
最后一个元素:4
第一个元素:1
最后一个元素:4
第一个元素:-5
最后一个元素:10

PL/SQL 过程已成功完成。

--5,PRIOR和NEXT:返回集合元素的前(后)一个元素的下标
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE ename_table1_type IS TABLE OF emp.ename%TYPE
  3             INDEX BY BINARY_INTEGER;
  4     ename_table1 ename_table1_type;
  5  BEGIN
  6     ename_table1(-5):='SCOTT';
  7     ename_table1(1):='SMITH';
  8     ename_table1(5):='MARY';
  9     ename_table1(10):='BLAKE';
 10     dbms_output.put_line('元素5的前一个元素下标:'||ename_table1.PRIOR(5));
 11     dbms_output.put_line('元素5的后一个元素下标:'||ename_table1.NEXT(5));
 12     dbms_output.put_line('元素-5的后一个元素下标:'||ename_table1.PRIOR(-5));
 13     dbms_output.put_line('元素10的后一个元素下标:'||ename_table1.NEXT(10));
 14  END;
 15  /
元素5的前一个元素下标:1
元素5的后一个元素下标:10
元素-5的后一个元素下标:
元素10的后一个元素下标:

PL/SQL 过程已成功完成。

--6,EXTEND:改变原有集合的尺寸,只适用于嵌套表和VARRAY
--EXTEND:为集合添加一个NULL元素;EXTEND(n):为集合添加n个NULL元素;EXTEND(n,i):为集合添加n个与第i个元素的值相同的元素;
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3     ename_table ename_table_type;
  4     TYPE ename_table_type1 IS TABLE OF emp.ename%TYPE;
  5     ename_table1 ename_table_type1;
  6     TYPE ename_table_type2 IS TABLE OF emp.ename%TYPE;
  7     ename_table2 ename_table_type2;
  8  BEGIN
  9     ename_table:=ename_table_type('MARY');
 10     ename_table1:=ename_table_type1('MARY');
 11     ename_table2:=ename_table_type2('MARY');
 12     ename_table.EXTEND(5,1);
 13     ename_table1.EXTEND(5);
 14     ename_table2.EXTEND();
 15     dbms_output.put_line('ename_table的元素个数为:'||ename_table.COUNT);
 16     dbms_output.put_line('ename_table1的元素个数为:'||ename_table1.COUNT);
 17     dbms_output.put_line('ename_table2的元素个数为:'||ename_table2.COUNT);
 18  END;
 19  /
ename_table的元素个数为:6
ename_table1的元素个数为:6
ename_table2的元素个数为:2

PL/SQL 过程已成功完成。

--7:TRIM与DELETE
--TRIM:删除尾部元素,适用于嵌套表和和VARRAY
--DELETE:删除集合中的元素,适用于嵌套表和索引表
--TRIM:删除尾部元素;
--TRIM(n):删除尾部n个元素;
--DELETE:删除集合中所有元素;
--DELETE(n):删除集合中第n个元素;
--DELETE(m,n):删除集合中第m到n个元素;
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3     ename_table ename_table_type;
  4     TYPE ename_table_type1 IS TABLE OF emp.ename%TYPE
  5             INDEX BY BINARY_INTEGER;
  6     ename_table1 ename_table_type1;
  7     TYPE ename_varray_type IS VARRAY(20) OF emp.ename%TYPE;
  8     ename_varray ename_varray_type;
  9  BEGIN
 10     ename_table:=ename_table_type('SCOTT','TIGER','NIOR','SMITH','MARY');
 11     ename_table1(-5):='SS';
 12     ename_table1(0):='TT';
 13     ename_table1(4):='WW';
 14     ename_table1(10):='GG';
 15     ename_varray:=ename_varray_type('张三','李四','王五','赵六');
 16
 17     dbms_output.put_line('-------ename_table:'||ename_table.COUNT);
 18     ename_table.TRIM();
 19     dbms_output.put_line('ename_table-TRIM:'||ename_table.COUNT);
 20     ename_table.TRIM(2);
 21     dbms_output.put_line('ename_table-TRIM(2):'||ename_table.COUNT);
 22
 23     dbms_output.put_line('-----ename_table1:'||ename_table1.COUNT);
 24     ename_table1.DELETE(4);
 25     dbms_output.put_line('ename_table1-DELETE(4):'||ename_table1.COUNT);
 26     ename_table1.DELETE(0,10);
 27     dbms_output.put_line('ename_table1-DELETE(0,10):'||ename_table1.COUNT);
 28
 29     dbms_output.put_line('-----ename_varray:'||ename_varray.COUNT);
 30     ename_varray.TRIM();
 31     dbms_output.put_line('ename_varray-TRIM:'||ename_varray.COUNT);
 32     ename_varray.TRIM(2);
 33     dbms_output.put_line('ename_varray-TRIM(2):'||ename_varray.COUNT);
 34  END;
 35  /
-------ename_table:5
ename_table-TRIM:4
ename_table-TRIM(2):2
-----ename_table1:4
ename_table1-DELETE(4):3
ename_table1-DELETE(0,10):1
-----ename_varray:4
ename_varray-TRIM:3
ename_varray-TRIM(2):1

PL/SQL 过程已成功完成。

--(四),集合赋值
--嵌套表和VARRAY
--1.将一个集合赋值给另一个集合
--要求集合的类型必须相同
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(40);
  3     name_varray1 name_varray_type:=name_varray_type('SCOTT','SMITH');
  4     name_varray2 name_varray_type;
  5  BEGIN
  6     FOR i IN 1..name_varray1.COUNT LOOP
  7             dbms_output.put_line('name_varray1_ini:'||name_varray1(i));
  8     END LOOP;
  9     name_varray2:=name_varray1;
 10     FOR i IN 1..name_varray2.COUNT LOOP
 11             dbms_output.put_line('name_varray2_ini:'||name_varray2(i));
 12     END LOOP;
 13  END;
 14  /
name_varray1_ini:SCOTT
name_varray1_ini:SMITH
name_varray2_ini:SCOTT
name_varray2_ini:SMITH

PL/SQL 过程已成功完成。

--2.给集合赋NULL
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(40);
  3     name_varray1 name_varray_type:=name_varray_type('SCOTT','SMITH');
  4     name_varray2 name_varray_type;
  5  BEGIN
  6     FOR i IN 1..name_varray1.COUNT LOOP
  7             dbms_output.put_line('name_varray1_ini:'||name_varray1(i));
  8     END LOOP;
  9     name_varray1:=name_varray2;
 10     IF name_varray1 IS NULL THEN
 11             dbms_output.put_line('name_varray1的现有元素个数为:0');
 12     END IF;
 13  END;
 14  /
name_varray1_ini:SCOTT
name_varray1_ini:SMITH
name_varray1的现有元素个数为:0

PL/SQL 过程已成功完成。

--3.集合操作符给嵌套表赋值
--SET,MULTISET UNION,MULTISET UNION DISTINCT,MULTISET INTERSECT,MULTISET EXCEPT;
--SET去重
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp_table temp_table_type:=temp_table_type(1,2,2,3,4,5,6,6);
  4  BEGIN
  5     temp_table:=SET(temp_table);
  6     FOR i IN 1..temp_table.COUNT LOOP
  7             dbms_output.put_line('temp_table_values:'||temp_table(i));
  8     END LOOP;
  9  END;
 10  /
temp_table_values:1
temp_table_values:2
temp_table_values:3
temp_table_values:4
temp_table_values:5
temp_table_values:6

PL/SQL 过程已成功完成。

--MULTISET UNION 取并集
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4     temp2_table temp_table_type:=temp_table_type(1,2,3,4);
  5  BEGIN
  6     temp1_table:=temp1_table MULTISET UNION temp2_table;
  7     FOR i IN 1..temp1_table.COUNT LOOP
  8             dbms_output.put_line('temp1_table_values:'||temp1_table(i));
  9     END LOOP;
 10  END;
 11  /
temp1_table_values:5
temp1_table_values:6
temp1_table_values:7
temp1_table_values:8
temp1_table_values:1
temp1_table_values:2
temp1_table_values:3
temp1_table_values:4

PL/SQL 过程已成功完成。

--MULTISET UNION DISTINCT取非交集部分数据
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,5,7,8);
  4     temp2_table temp_table_type:=temp_table_type(1,2,2,3);
  5  BEGIN
  6     temp1_table:=temp1_table MULTISET UNION DISTINCT temp2_table;
  7     FOR i IN 1..temp1_table.COUNT LOOP
  8             dbms_output.put_line('temp1_table_values:'||temp1_table(i));
  9     END LOOP;
 10  END;
 11  /
temp1_table_values:5
temp1_table_values:7
temp1_table_values:8
temp1_table_values:1
temp1_table_values:2
temp1_table_values:3

PL/SQL 过程已成功完成。

--MULTISET INTERSECT
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4     temp2_table temp_table_type:=temp_table_type(2,3,4,5);
  5  BEGIN
  6     temp1_table:=temp1_table MULTISET INTERSECT temp2_table;
  7     FOR i IN 1..temp1_table.COUNT LOOP
  8             dbms_output.put_line('temp1_table_values:'||temp1_table(i));
  9     END LOOP;
 10  END;
 11  /
temp1_table_values:5

PL/SQL 过程已成功完成。

--MULTISET EXCEPT取左边非交集部分
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4     temp2_table temp_table_type:=temp_table_type(2,3,4,5);
  5  BEGIN
  6     temp1_table:=temp1_table MULTISET EXCEPT temp2_table;
  7     FOR i IN 1..temp1_table.COUNT LOOP
  8             dbms_output.put_line('temp1_table_values:'||temp1_table(i));
  9     END LOOP;
 10  END;
 11  /
temp1_table_values:6
temp1_table_values:7
temp1_table_values:8

PL/SQL 过程已成功完成。

--(五),集合比较
--IS NULL,IS EMPTY,=,CARDINALITY(),MEMBER OF,IS A SET,SUBMULTISET OF;
--CARDINALITY:返回元素个数
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4  BEGIN
  5     dbms_output.put_line('temp1_table_CountValues:'||CARDINALITY(temp1_table));
  6  END;
  7  /
temp1_table_CountValues:4

PL/SQL 过程已成功完成。

--MEMBER OF:判断元素是否在另一个元素中
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4     temp2 NUMBER:=5;
  5  BEGIN
  6     IF temp2 MEMBER OF temp1_table THEN
  7             dbms_output.put_line('temp2:'||temp2);
  8     END IF;
  9  END;
 10  /
temp2:5

PL/SQL 过程已成功完成。

--SUBMULTISET OF:判断一个嵌套表是否为另一个嵌套表的子集
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,6,7,8);
  4     temp2_table temp_table_type:=temp_table_type(5,6);
  5     temp3_table temp_table_type:=temp_table_type(7,9);
  6  BEGIN
  7     IF temp2_table SUBMULTISET OF temp1_table THEN
  8             FOR i IN 1..temp2_table.COUNT LOOP
  9                     dbms_output.put_line('temp2_table:'||temp2_table(i));
 10             END LOOP;
 11     END IF;
 12     IF temp3_table SUBMULTISET OF temp1_table THEN
 13             FOR i IN 1..temp3_table.COUNT LOOP
 14                     dbms_output.put_line('temp3_table:'||temp3_table(i));
 15             END LOOP;
 16     END IF;
 17  END;
 18  /
temp2_table:5
temp2_table:6

PL/SQL 过程已成功完成。

--IS A SET:判断一个集合中是否有重复的值
SQL> DECLARE
  2     TYPE temp_table_type IS TABLE OF NUMBER;
  3     temp1_table temp_table_type:=temp_table_type(5,5,6,7,8);
  4     temp2_table temp_table_type:=temp_table_type(5,6,7,8);
  5  BEGIN
  6     IF temp1_table IS A SET THEN
  7             FOR i IN 1..temp1_table.COUNT LOOP
  8                     dbms_output.put_line('temp1_table:'||temp1_table(i));
  9             END LOOP;
 10     END IF;
 11     IF temp2_table IS A SET THEN
 12             FOR i IN 1..temp2_table.COUNT LOOP
 13                     dbms_output.put_line('temp2_table:'||temp2_table(i));
 14             END LOOP;
 15     END IF;
 16  END;
 17  /
temp2_table:5
temp2_table:6
temp2_table:7
temp2_table:8

PL/SQL 过程已成功完成。

--(五),批量绑定
--1.使用批量绑定与不使用批量绑定耗时DEMO对比
SQL> CREATE TABLE demo
  2  (
  3     id NUMBER(6) PRIMARY KEY,
  4     name VARCHAR2(10)
  5  );

表已创建。

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE id_table_type IS TABLE OF NUMBER(6)
  3             INDEX BY BINARY_INTEGER;
  4     TYPE name_table_type IS TABLE OF VARCHAR2(10)
  5             INDEX BY BINARY_INTEGER;
  6     id_table id_table_type;
  7     name_table name_table_type;
  8     start_time NUMBER(10);
  9     end_time NUMBER(10);
 10  BEGIN
 11     FOR i IN 1..50000 LOOP
 12             id_table(i):=i;
 13             name_table(i):='Name_'||to_char(i);
 14     END LOOP;
 15     start_time:=dbms_utility.get_time;
 16     FOR i IN 1..id_table.COUNT LOOP
 17             INSERT INTO demo VALUES(id_table(i),name_table(i));
 18     END LOOP;
 19     end_time:=dbms_utility.get_time;
 20     dbms_output.put_line('总时间(秒):'||to_char((end_time-start_time)/100));
 21  END;
 22  /
总时间(秒):3.72

PL/SQL 过程已成功完成。

SQL> DROP TABLE demo;

表已删除。

SQL> CREATE TABLE demo
  2  (
  3     id NUMBER(6) PRIMARY KEY,
  4     name VARCHAR2(10)
  5  );

表已创建。

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     TYPE id_table_type IS TABLE OF NUMBER(6)
  3             INDEX BY BINARY_INTEGER;
  4     TYPE name_table_type IS TABLE OF VARCHAR2(10)
  5             INDEX BY BINARY_INTEGER;
  6     id_table id_table_type;
  7     name_table name_table_type;
  8     start_time NUMBER(10);
  9     end_time NUMBER(10);
 10  BEGIN
 11     FOR i IN 1..50000 LOOP
 12             id_table(i):=i;
 13             name_table(i):='Name_'||to_char(i);
 14     END LOOP;
 15     start_time:=dbms_utility.get_time;
 16     FORALL i IN 1..id_table.COUNT
 17             INSERT INTO demo VALUES(id_table(i),name_table(i));
 18     end_time:=dbms_utility.get_time;
 19     dbms_output.put_line('总时间(秒):'||to_char((end_time-start_time)/100));
 20  END;
 21  /
总时间(秒):.33

PL/SQL 过程已成功完成。

--2.使用FORALL的几种用法
--语法1
FORALL index IN lower_bound..upper_bound
    sql_statement;
--语法2
FORALL index IN INDICES OF collection
    [BETWEEN lower_bound.AND.upper_bound]
    sql_statement;
--语法3
FORALL index IN VALUES OF index_collection
    sql_statement;

--INSERT,UPDATE,DELETE中均可以使用批量绑定
--FORALL i IN 8..10
--FORALL i IN INDICES OF id_table:使用批量绑定跳过NULL集合元素
--FORALL i IN VALUES OF id_table:使用批量绑定访问指定值得集合元素
--sql%bulk_rowcount(2):返回第二个元素作用的行数

--3.BULK COLLECT
--用于取得批量数据,只能在SLECT INTO,FETCH INTO,DML返回子句
--DEMO1
SQL> DECLARE
  2     TYPE id_table_type IS TABLE OF NUMBER(6)
  3             INDEX BY BINARY_INTEGER;
  4     TYPE name_table_type IS TABLE OF VARCHAR2(10)
  5             INDEX BY BINARY_INTEGER;
  6     id_table id_table_type;
  7     name_table name_table_type;
  8  BEGIN
  9     SELECT * BULK COLLECT INTO id_table,name_table FROM demo;
 10     FOR i IN 1..id_table.COUNT LOOP
 11             IF i<10 THEN
 12                     dbms_output.put_line('id_table:'||id_table(i));
 13                     dbms_output.put_line('name_table:'||name_table(i));
 14             END IF;
 15     END LOOP;
 16  END;
 17  /
id_table:819
name_table:Name_819
id_table:820
name_table:Name_820
id_table:821
name_table:Name_821
id_table:822
name_table:Name_822
id_table:823
name_table:Name_823
id_table:824
name_table:Name_824
id_table:825
name_table:Name_825
id_table:826
name_table:Name_826
id_table:827
name_table:Name_827

PL/SQL 过程已成功完成。

--DEMO2
SQL> DECLARE
  2     TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3     ename_table ename_table_type;
  4  BEGIN
  5     DELETE FROM emp WHERE deptno=&no
  6             RETURNING ename BULK COLLECT INTO ename_table;
  7     dbms_output.put('雇员名:');
  8     FOR i IN 1..ename_table.COUNT LOOP
  9             dbms_output.put(ename_table(i)||'  ');
 10     END LOOP;
 11     dbms_output.new_line;
 12  END;
 13  /
输入 no 的值:  10
原值    5:      DELETE FROM emp WHERE deptno=&no
新值    5:      DELETE FROM emp WHERE deptno=10
雇员名:CLARK  KING  MILLER

PL/SQL 过程已成功完成。

 

posted on 2012-08-21 18:10  wean  阅读(567)  评论(0编辑  收藏  举报

导航