當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

PL/SQL复合数据类型

發布時間：2025/3/17 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 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 DECLARETYPE 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> DECLARE2 TYPE emp_record_type IS RECORD(3 name emp.ename%TYPE,4 salary emp.sal%TYPE,5 dno emp.deptno%TYPE6 );7 emp_record emp_record_type;8 BEGIN9 SELECT ename,sal,deptno INTO emp_record10 FROM emp11 WHERE empno=&no;12 dbms_output.put_line(emp_record.name);13 END;14 / 輸入 no 的值: 7788 原值 11: WHERE empno=&no; 新值 11: WHERE empno=7788; SCOTTPL/SQL 過程已成功完成。--Demo2:SELECT INTO 中用PL/SQL記錄成員變量記錄 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 TYPE emp_record_type IS RECORD(3 name emp.ename%TYPE,4 salary emp.sal%TYPE,5 dno emp.deptno%TYPE6 );7 emp_record emp_record_type;8 BEGIN9 SELECT ename,sal INTO emp_record.name,emp_record.salary10 FROM emp11 WHERE empno=&no;12 dbms_output.put_line(emp_record.name);13 END;14 / 輸入 no 的值: 7369 原值 11: WHERE empno=&no; 新值 11: WHERE empno=7369; SMITHPL/SQL 過程已成功完成。--Demo3:INSERT 中用PL/SQL記錄 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 dept_record dept%ROWTYPE;3 BEGIN4 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> DECLARE2 dept_record dept%ROWTYPE;3 BEGIN4 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> DECLARE2 dept_record dept%ROWTYPE;3 BEGIN4 dept_record.deptno:=30;5 dept_record.dname:='SALES';6 dept_record.loc:='SHANGHAI';7 UPDATE dept SET ROW=dept_record8 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> DECLARE2 dept_record dept%ROWTYPE;3 BEGIN4 dept_record.loc:='GUANGZHOU';5 UPDATE dept SET loc=dept_record.loc6 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> DECLARE2 dept_record dept%ROWTYPE;3 BEGIN4 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> DECLARE2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE3 INDEX BY BINARY_INTEGER;4 ename_table ename_table_type;5 BEGIN6 SELECT ename INTO ename_table(-1)7 FROM emp8 WHERE empno=&no;9 dbms_output.put_line('雇員名：'||ename_table(-1));10 END;11 / 輸入 no 的值: 7788 原值 8: WHERE empno=&no; 新值 8: WHERE empno=7788; 雇員名：SCOTTPL/SQL 過程已成功完成。--Demo2 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 TYPE area_table_type IS TABLE OF NUMBER3 NOT NULL INDEX BY VARCHAR(10);4 area_table area_table_type;5 BEGIN6 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> DECLARE2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE;3 ename_table ename_table_type;4 BEGIN5 --構造函數初始化嵌套表變量后才能在PL/SQL中引用6 ename_table:=ename_table_type('A','A','A');7 SELECT ename INTO ename_table(2)8 FROM emp9 WHERE empno=&no;10 dbms_output.put_line('雇員名：'||ename_table(2));11 END;12 / 輸入 no 的值: 7788 原值 9: WHERE empno=&no; 新值 9: WHERE empno=7788; 雇員名：SCOTTPL/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_type6 )7 --使用嵌套表作為表列的數據類型時，必須要為嵌套表指定專門的存儲表8 NESTED TABLE phone STORE AS phone_table;表已創建。--Demo3:在PL/SQL塊中為嵌套表列插入數據 SQL> --當定義嵌套表類型時，Oracle自動為該類型生成對應的構造方法。 SQL> --當為嵌套表插入數據時，需要使用嵌套表的構造方法。 SQL> BEGIN2 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> DECLARE2 phone_table phone_type;3 BEGIN4 SELECT phone INTO phone_table5 FROM employes6 WHERE id=1;7 FOR i IN 1..phone_table.COUNT LOOP8 dbms_output.put_line('電話號碼：'||phone_table(i));9 END LOOP;10 END;11 / 電話號碼：020-81181818 電話號碼：13922390110PL/SQL 過程已成功完成。--Demo5:在PL/SQL中更新嵌套表列的數據 SQL> DECLARE2 phone_table phone_type:=phone_type('020-817191111','13922222222',3 '030-81111111','13911111111');4 BEGIN5 UPDATE employes SET phone=phone_table6 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> DECLARE2 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 BEGIN6 SELECT ename INTO ename_varray(1) FROM emp7 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_type6 );表已創建。--4.PL/SQL記錄表 --特性：處理多行多列數據;結合了PL/SQL記錄與PL/SQL集合的優點; --demo1:PL/SQL塊中使用PL/SQL記錄表 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 TYPE emp_table_type IS TABLE OF emp%ROWTYPE3 INDEX BY BINARY_INTEGER;4 emp_table emp_table_type;5 BEGIN6 SELECT * INTO emp_table(1) FROM emp7 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 雇員工資：1200PL/SQL 過程已成功完成。--5.多級集合 --demo1:在PL/SQL塊中使用多級VARRAY SQL> DECLARE2 --定義一維VARRAY3 TYPE al_varray_type IS VARRAY(10) OF INT;4 --定義二維VARRAY5 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 BEGIN13 dbms_output.put_line('顯示二維數組所有元素');14 FOR i IN 1..nvl.COUNT LOOP15 FOR j IN 1..nvl(i).COUNT LOOP16 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)=9PL/SQL 過程已成功完成。--demo2:PL/SQL塊中使用多級嵌套表 SQL> DECLARE2 --定義一維嵌套表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 BEGIN13 dbms_output.put_line('顯示二維數組所有元素');14 FOR i IN 1..nvl.COUNT LOOP15 FOR j IN 1..nvl(i).COUNT LOOP16 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)=9PL/SQL 過程已成功完成。--demo3:PL/SQL中使用多級索引表 SQL> DECLARE2 --定義一維索引表3 TYPE al_table_type IS TABLE OF INT4 INDEX BY BINARY_INTEGER;5 --定義二維索引表6 TYPE nal_table_type IS TABLE OF al_table_type7 INDEX BY BINARY_INTEGER;8 nvl nal_table_type;9 BEGIN10 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 LOOP21 FOR j IN 1..nvl(i).COUNT LOOP22 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)=9PL/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> DECLARE2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE;3 ename_table ename_table_type;4 BEGIN5 IF ename_table.EXISTS(1) THEN6 ename_table(1):='SCOTT';7 ELSE8 dbms_output.put_line('嵌套表變量在PL/SQL塊中使用時必須初始化');9 END IF;10 END;11 / 嵌套表變量在PL/SQL塊中使用時必須初始化PL/SQL 過程已成功完成。--2.COUNT:集合元素個數 SQL> DECLARE2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE3 INDEX BY BINARY_INTEGER;4 ename_table ename_table_type;5 TYPE sal_table_type IS TABLE OF emp.sal%TYPE6 INDEX BY BINARY_INTEGER;7 sal_table ename_table_type;8 BEGIN9 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 集合元素總個數：0PL/SQL 過程已成功完成。--3.LIMIT:返回集合元素的最大個數,嵌套表和索引表的元素沒有個數限制,返回NULL SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 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 BEGIN7 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> DECLARE2 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%TYPE7 INDEX BY BINARY_INTEGER;8 ename_table1 ename_table1_type;9 BEGIN10 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 最后一個元素：10PL/SQL 過程已成功完成。--5,PRIOR和NEXT:返回集合元素的前(后)一個元素的下標 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 TYPE ename_table1_type IS TABLE OF emp.ename%TYPE3 INDEX BY BINARY_INTEGER;4 ename_table1 ename_table1_type;5 BEGIN6 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> DECLARE2 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 BEGIN9 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的元素個數為：2PL/SQL 過程已成功完成。--7:TRIM與DELETE --TRIM:刪除尾部元素,適用于嵌套表和和VARRAY --DELETE:刪除集合中的元素,適用于嵌套表和索引表 --TRIM:刪除尾部元素; --TRIM(n):刪除尾部n個元素; --DELETE:刪除集合中所有元素; --DELETE(n):刪除集合中第n個元素; --DELETE(m,n):刪除集合中第m到n個元素; SQL> DECLARE2 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%TYPE5 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 BEGIN10 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('張三','李四','王五','趙六');1617 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);2223 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);2829 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):1PL/SQL 過程已成功完成。--(四),集合賦值 --嵌套表和VARRAY --1.將一個集合賦值給另一個集合 --要求集合的類型必須相同 SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 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 BEGIN6 FOR i IN 1..name_varray1.COUNT LOOP7 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 LOOP11 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:SMITHPL/SQL 過程已成功完成。--2.給集合賦NULL SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 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 BEGIN6 FOR i IN 1..name_varray1.COUNT LOOP7 dbms_output.put_line('name_varray1_ini:'||name_varray1(i));8 END LOOP;9 name_varray1:=name_varray2;10 IF name_varray1 IS NULL THEN11 dbms_output.put_line('name_varray1的現有元素個數為：0');12 END IF;13 END;14 / name_varray1_ini:SCOTT name_varray1_ini:SMITH name_varray1的現有元素個數為：0PL/SQL 過程已成功完成。--3.集合操作符給嵌套表賦值 --SET,MULTISET UNION,MULTISET UNION DISTINCT,MULTISET INTERSECT,MULTISET EXCEPT; --SET去重 SQL> DECLARE2 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 BEGIN5 temp_table:=SET(temp_table);6 FOR i IN 1..temp_table.COUNT LOOP7 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:6PL/SQL 過程已成功完成。--MULTISET UNION 取并集 SQL> DECLARE2 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 BEGIN6 temp1_table:=temp1_table MULTISET UNION temp2_table;7 FOR i IN 1..temp1_table.COUNT LOOP8 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:4PL/SQL 過程已成功完成。--MULTISET UNION DISTINCT取非交集部分數據 SQL> DECLARE2 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 BEGIN6 temp1_table:=temp1_table MULTISET UNION DISTINCT temp2_table;7 FOR i IN 1..temp1_table.COUNT LOOP8 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:3PL/SQL 過程已成功完成。--MULTISET INTERSECT SQL> DECLARE2 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 BEGIN6 temp1_table:=temp1_table MULTISET INTERSECT temp2_table;7 FOR i IN 1..temp1_table.COUNT LOOP8 dbms_output.put_line('temp1_table_values:'||temp1_table(i));9 END LOOP;10 END;11 / temp1_table_values:5PL/SQL 過程已成功完成。--MULTISET EXCEPT取左邊非交集部分 SQL> DECLARE2 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 BEGIN6 temp1_table:=temp1_table MULTISET EXCEPT temp2_table;7 FOR i IN 1..temp1_table.COUNT LOOP8 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:8PL/SQL 過程已成功完成。--(五),集合比較 --IS NULL,IS EMPTY,=,CARDINALITY(),MEMBER OF,IS A SET,SUBMULTISET OF; --CARDINALITY:返回元素個數 SQL> DECLARE2 TYPE temp_table_type IS TABLE OF NUMBER;3 temp1_table temp_table_type:=temp_table_type(5,6,7,8);4 BEGIN5 dbms_output.put_line('temp1_table_CountValues:'||CARDINALITY(temp1_table));6 END;7 / temp1_table_CountValues:4PL/SQL 過程已成功完成。--MEMBER OF:判斷元素是否在另一個元素中 SQL> DECLARE2 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 BEGIN6 IF temp2 MEMBER OF temp1_table THEN7 dbms_output.put_line('temp2:'||temp2);8 END IF;9 END;10 / temp2:5PL/SQL 過程已成功完成。--SUBMULTISET OF:判斷一個嵌套表是否為另一個嵌套表的子集 SQL> DECLARE2 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 BEGIN7 IF temp2_table SUBMULTISET OF temp1_table THEN8 FOR i IN 1..temp2_table.COUNT LOOP9 dbms_output.put_line('temp2_table:'||temp2_table(i));10 END LOOP;11 END IF;12 IF temp3_table SUBMULTISET OF temp1_table THEN13 FOR i IN 1..temp3_table.COUNT LOOP14 dbms_output.put_line('temp3_table:'||temp3_table(i));15 END LOOP;16 END IF;17 END;18 / temp2_table:5 temp2_table:6PL/SQL 過程已成功完成。--IS A SET：判斷一個集合中是否有重復的值 SQL> DECLARE2 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 BEGIN6 IF temp1_table IS A SET THEN7 FOR i IN 1..temp1_table.COUNT LOOP8 dbms_output.put_line('temp1_table:'||temp1_table(i));9 END LOOP;10 END IF;11 IF temp2_table IS A SET THEN12 FOR i IN 1..temp2_table.COUNT LOOP13 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:8PL/SQL 過程已成功完成。--(五),批量綁定 --1.使用批量綁定與不使用批量綁定耗時DEMO對比 SQL> CREATE TABLE demo2 (3 id NUMBER(6) PRIMARY KEY,4 name VARCHAR2(10)5 );表已創建。SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 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 BEGIN11 FOR i IN 1..50000 LOOP12 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 LOOP17 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.72PL/SQL 過程已成功完成。SQL> DROP TABLE demo;表已刪除。SQL> CREATE TABLE demo2 (3 id NUMBER(6) PRIMARY KEY,4 name VARCHAR2(10)5 );表已創建。SQL> SET SERVEROUTPUT ON; SQL> DECLARE2 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 BEGIN11 FOR i IN 1..50000 LOOP12 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.COUNT17 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 / 總時間(秒)：.33PL/SQL 過程已成功完成。--2.使用FORALL的幾種用法 --語法1 FORALL index IN lower_bound..upper_boundsql_statement; --語法2 FORALL index IN INDICES OF collection[BETWEEN lower_bound.AND.upper_bound]sql_statement; --語法3 FORALL index IN VALUES OF index_collectionsql_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> DECLARE2 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 BEGIN9 SELECT * BULK COLLECT INTO id_table,name_table FROM demo;10 FOR i IN 1..id_table.COUNT LOOP11 IF i<10 THEN12 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_827PL/SQL 過程已成功完成。--DEMO2 SQL> DECLARE2 TYPE ename_table_type IS TABLE OF emp.ename%TYPE;3 ename_table ename_table_type;4 BEGIN5 DELETE FROM emp WHERE deptno=&no6 RETURNING ename BULK COLLECT INTO ename_table;7 dbms_output.put('雇員名：');8 FOR i IN 1..ename_table.COUNT LOOP9 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 MILLERPL/SQL 過程已成功完成。

轉載于:https://www.cnblogs.com/wean/archive/2012/08/21/2649536.html

總結

以上是生活随笔為你收集整理的PL/SQL复合数据类型的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。