范例:定义过程,输出一个雇员的完整信息
DECLARE v_emp_empnoemp.empno%TYPE ; v_emp_enameemp.ename%TYPE ; v_emp_jobemp.job%TYPE ; v_emp_hiredateemp.hiredate%TYPE ; v_emp_salemp.sal%TYPE ; v_emp_commemp.comm%TYPE ; BEGIN v_emp_empno := &inputempno ; SELECT ename,job,hiredate,sal,comm INTO v_emp_ename,v_emp_job,v_emp_hiredate,v_emp_sal,v_emp_comm FROM emp WHERE empno=v_emp_empno ; DBMS_OUTPUT.put_line('雇员编号:' || v_emp_empno || ',姓名:' || v_emp_ename || ',职位:' || v_emp_job || ',雇佣日期:' || TO_CHAR(v_emp_hiredate,'yyyy-mm-dd') || ',基本工资:' || v_emp_sal || ',佣金:' || NVL(v_emp_comm,0)) ; EXCEPTION WHEN others THEN RAISE_APPLICATION_ERROR(-20007,'此雇员信息不存在!') ; END ; / |
范例:使用记录类型接收查询返回结果
DECLARE v_emp_empnoemp.empno%TYPE ; TYPE emp_type IS RECORD ( enameemp.ename%TYPE , jobemp.job%TYPE , hiredateemp.hiredate%TYPE , salemp.sal%TYPE , commemp.comm%TYPE ) ; v_empemp_type ; -- 定义一个指定的复合类型变量 BEGIN v_emp_empno := &inputempno ; SELECT ename,job,hiredate,sal,comm INTO v_emp FROM emp WHERE empno=v_emp_empno ; DBMS_OUTPUT.put_line('雇员编号:' || v_emp_empno || ',姓名:' || v_emp.ename || ',职位:' || v_emp.job || ',雇佣日期:' || TO_CHAR(v_emp.hiredate,'yyyy-mm-dd') || ',基本工资:' || v_emp.sal || ',佣金:' || NVL(v_emp.comm,0)) ; EXCEPTION WHEN others THEN RAISE_APPLICATION_ERROR(-20007,'此雇员信息不存在!') ; END ; / |
范例:用户自己操作记录类型数据
DECLARE TYPE dept_type IS RECORD ( deptno dept.deptno%TYPE := 80, -- 定义默认值 dname dept.dname%TYPE , loc dept.loc%TYPE ) ; v_dept dept_type ; BEGIN v_dept.dname := 'MLDN ' ; -- 为记录类型成员赋值 v_dept.loc := '北京' ; -- 为记录类型成员赋值 DBMS_OUTPUT.put_line('部门编号:' || v_dept.deptno || ',名称:' || v_dept.dname || ',位置:' || v_dept.loc) ; END ; / |
范例:定义嵌套的记录类型
DECLARE TYPE dept_type IS RECORD ( deptno dept.deptno%TYPE := 80, -- 定义默认值 dname dept.dname%TYPE , loc dept.loc%TYPE ) ; TYPE emp_type IS RECORD ( empno emp.empno%TYPE , ename emp.ename%TYPE , job emp.job%TYPE , hiredate emp.hiredate%TYPE , sal emp.sal%TYPE , comm emp.comm%TYPE , deptdept_type ) ; v_empemp_type ; BEGIN SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc INTO v_emp.empno,v_emp.ename,v_emp.job,v_emp.hiredate,v_emp.sal,v_emp.comm, v_emp.dept.deptno,v_emp.dept.dname,v_emp.dept.loc FROM emp e,dept d WHERE e.deptno=d.deptno(+) AND e.empno=7369 ; DBMS_OUTPUT.put_line('雇员编号:' || v_emp.empno || ',姓名:' || v_emp.ename || ',职位:' || v_emp.job || ',雇佣日期:' || TO_CHAR(v_emp.hiredate,'yyyy-mm-dd') || ',基本工资:' || v_emp.sal || ',佣金:' || NVL(v_emp.comm,0)) ; DBMS_OUTPUT.put_line('部门编号:' || v_emp.dept.deptno || ',名称:' || v_emp.dept.dname || ',位置:' || v_emp.dept.loc) ; END ; / |
范例:增加一条新的记录,利用记录类型保存数据
DECLARE TYPE dept_type IS RECORD ( deptnodept.deptno%TYPE , dnamedept.dname%TYPE , locdept.loc%TYPE ) ; v_dept dept_type ; BEGIN v_dept.dname := 'MLDN' ; v_dept.loc := '北京' ; v_dept.deptno := 80 ; INSERT INTO dept VALUES v_dept ; -- 直接插入记录类型的数据 END ; / |
范例:修改数据,利用记录类型保存数据
DECLARE TYPE dept_type IS RECORD ( deptnodept.deptno%TYPE , dnamedept.dname%TYPE , locdept.loc%TYPE ) ; v_dept dept_type ; BEGIN v_dept.dname := 'MLDNJAVA' ; v_dept.loc := '中国' ; v_dept.deptno := 80 ; UPDATE dept SET ROW=v_dept WHERE deptno=v_dept.deptno ; END ; / |
范例:定义索引表
DECLARE TYPE info_index IS TABLE OF VARCHAR(20) INDEX BY PLS_INTEGER ; v_info info_index ; BEGIN v_info (1) := 'MLDN' ; v_info (10) := 'JAVA' ; DBMS_OUTPUT.put_line(v_info(1)) ; DBMS_OUTPUT.put_line(v_info(10)) ; END ; / |
范例:使用EXISTS()函数
DECLARE TYPE info_index IS TABLE OF VARCHAR(20) INDEX BY PLS_INTEGER ; v_info info_index ; BEGIN v_info (1) := 'MLDN' ; v_info (10) := 'JAVA' ; IF v_info.EXISTS(10) THEN DBMS_OUTPUT.put_line(v_info(1)) ; END IF ; IF v_info.EXISTS(30) THEN DBMS_OUTPUT.put_line(v_info(30)) ; ELSE DBMS_OUTPUT.put_line('索引号为30的数据不存在!') ; END IF ; END ; / |
范例:定义索引表,使用VARCHAR2作为下标索引
DECLARE TYPE info_index IS TABLE OF VARCHAR(50) INDEX BY VARCHAR2(30) ; v_info info_index ; BEGIN v_info ('公司名称') := '魔乐科技(MLDN)' ; v_info ('培训项目') := 'JAVA-Android高端培训' ; DBMS_OUTPUT.put_line(v_info('公司名称')) ; DBMS_OUTPUT.put_line(v_info('培训项目')) ; END ; / |
范例:定义ROWTYPE型的索引表
DECLARE TYPE dept_index IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER ; v_dept dept_index ; BEGIN v_dept(0).deptno := 80 ; v_dept(0).dname := 'MLDN教学部' ; v_dept(0).loc := '北京' ; IF v_dept.EXISTS(0) THEN DBMS_OUTPUT.put_line('部门编号:' || v_dept(0).deptno || ',名称:' || v_dept(0).dname || ',位置:' || v_dept(0).loc) ; END IF ; END ; / |
范例:使用记录类型操作
DECLARE TYPE dept_type IS RECORD ( deptnodept.deptno%TYPE := 80,-- 定义默认值 dnamedept.dname%TYPE , locdept.loc%TYPE ) ; TYPE dept_index IS TABLE OF dept_type INDEX BY PLS_INTEGER ; v_dept dept_index ; BEGIN v_dept(0).deptno := 80 ; v_dept(0).dname := 'MLDN教学部' ; v_dept(0).loc := '北京' ; IF v_dept.EXISTS(0) THEN DBMS_OUTPUT.put_line('部门编号:' || v_dept(0).deptno || ',名称:' || v_dept(0).dname || ',位置:' || v_dept(0).loc) ; END IF ; END ; / |
范例:创建表示多个项目的嵌套表类型
CREATE OR REPLACE TYPE project_nested AS TABLE OF VARCHAR2(50) NOT NULL ; / |
范例:定义部门表
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(30) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY (did) ) NESTED TABLE projects STORE AS projects_nested_table ; |
范例:查看department表结构
DESC department ; |
范例:向department表中增加数据
INSERT INTO department (did,deptname,projects) VALUES (10,'魔乐科技', project_nested('Java实战开发','Android实战开发')) ; INSERT INTO department (did,deptname,projects) VALUES (20,'MLDN出版部', project_nested('《Java开发实战经典》','《Java Web开发实战经典》' , '《Android开发实战经典》')) ; COMMIT ; |
范例:查询department表的全部记录
SELECT * FROM department ; |
范例:错误的查询20部门的所有项目
SELECT projects FROM department WHERE did=20 ; |
范例:将嵌套表数据变为数据表查询
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:修改部门20中的项目信息
UPDATE TABLE (SELECT projects FROM department WHERE did=20) pro SET VALUE(pro)='《Oracle开发实战经典》' WHERE pro.column_value='《Java Web开发实战经典》' ; COMMIT ; |
范例:查询修改后的20部门信息
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:删除嵌套表之中“《Java开发实战经典》”记录
DELETE FROM TABLE ( SELECT projects FROM department WHERE did=20) p WHERE p.column_value='《Java开发实战经典》' ; COMMIT ; |
范例:查询删除后的嵌套表数据
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:创建一个表示项目类型的对象
CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR2(50), projectfunds NUMBER , pubdate DATE ) ; / |
范例:定义嵌套表类型 —— project_nested
CREATE OR REPLACE TYPE project_nested AS TABLE OF project_type NOT NULL ; / |
范例:创建部门表,使用复合类型嵌套表
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(50) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY (did) ) NESTED TABLE projects STORE AS projects_nested_table ; |
范例:创建部门表,使用复合类型嵌套表
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(50) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY (did) ) NESTED TABLE projects STORE AS projects_nested_table(( projectid PRIMARY KEY , projectname NOT NULL , projectfunds NOT NULL , pubdate NOT NULL)) ; |
范例:向部门表中增加数据
INSERT INTO department (did,deptname,projects) VALUES (10,'魔乐科技', project_nested( project_type(1,'Java实战开发',8900,TO_DATE('2004-09-27','yyyy-mm-dd')) , project_type(2,'Android实战开发',13900,TO_DATE('2010-07-19','yyyy-mm-dd')))) ; INSERT INTO department (did,deptname,projects) VALUES (20,'MLDN出版部', project_nested( project_type(10,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》',69.8,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd')))) ; COMMIT ; |
范例:查询department表中的数据
SELECT * FROM department ; |
范例:查询一个部门的全部项目信息
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:修改某个部门某一项目的信息
UPDATE TABLE (SELECT projects FROM department WHERE did=20) pro SET VALUE(pro) = project_type(11,'《Oracle开发实战经典》',69.8,TO_DATE('2013-06-26','yyyy-mm-dd')) WHERE pro.projectid=11 ; COMMIT ; |
范例:查询更新后的数据
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:删除嵌套表中的一行记录
DELETE FROM TABLE( SELECT projects FROM department WHERE did=20) pro WHERE pro.projectid=12 ; COMMIT ; |
范例:查询删除后的数据
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; |
范例:在PL/SQL中定义新类型
DECLARE TYPE project_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_projects project_nested := project_nested('JAVA SE','JAVA EE','Android') ; BEGIN FOR x IN 1 .. v_projects.COUNT LOOP DBMS_OUTPUT.put_line(v_projects(x)) ; END LOOP ; END ; / |
范例:使用FOR循环输出嵌套表数据
DECLARE TYPE project_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_projects project_nested := project_nested('JAVA SE','JAVA EE','Android') ; BEGIN FOR x IN v_projects.FIRST .. v_projects.LAST LOOP DBMS_OUTPUT.put_line(v_projects(x)) ; END LOOP ; END ; / |
范例:在sqlplus中定义嵌套表数据类型
CREATE OR REPLACE TYPE project_nested IS TABLE OF VARCHAR2(50) NOT NULL ; / |
范例:在PL/SQL中直接使用project_nested
DECLARE v_projects project_nested := project_nested('JAVA SE','JAVA EE','Android') ; BEGIN FOR x IN v_projects.FIRST .. v_projects.LAST LOOP DBMS_OUTPUT.put_line(v_projects(x)) ; END LOOP ; END ; / |
范例:定义一个嵌套表类型
CREATE OR REPLACE TYPE project_nested AS TABLE OF VARCHAR2(50) NOT NULL ; / |
范例:创建一张包含嵌套表列的数据表
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(30) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY (did) ) NESTED TABLE projects STORE AS projects_nested_table ; |
范例:利用嵌套表实现数据增加操作
DECLARE v_project_list project_nested := project_nested('《Java开发实战经典》' , '《Android开发实战经典》' , '《Java WEB开发实战经典》') ; v_dept department%ROWTYPE ; BEGIN v_dept.did := 88 ; v_dept.deptname := '魔乐科技' ; v_dept.projects := v_project_list ; -- 直接赋予嵌套表 INSERT INTO department VALUES v_dept ; -- 直接使用ROWTYPE对象增加 END ; / |
范例:查看department表数据
SELECT * FROM department ; |
范例:利用嵌套表实现修改数据操作
DECLARE v_project_list project_nested := project_nested('《Java WEB高级案例篇》' , '《Android游戏开发教程》' , '《Struts2高级开发教程》') ; BEGIN UPDATE department SET projects=v_project_list WHERE did=88 ; -- 直接使用嵌套表保存更新数据 END ; / |
范例:查看更新后的数据
SELECT * FROM department ; |
范例:在PL/SQL中使用复合数据类型的嵌套表
CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR2(50), projectfunds NUMBER , pubdate DATE ) ; / |
DECLARE TYPE project_nested IS TABLE OF project_type NOT NULL ; v_projects project_nested := project_nested( project_type(10,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》',69.8,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd'))) ; BEGIN FOR x IN v_projects.FIRST .. v_projects.LAST LOOP DBMS_OUTPUT.put_line('项目编号:' || v_projects(x).projectid || ',项目名称:' || v_projects(x).projectname || ',金额:' || v_projects(x).projectfunds || ',发布日期:' || v_projects(x).pubdate) ; END LOOP ; END ; / |
范例:创建一张包含嵌套表类型
CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR2(50), projectfunds NUMBER , pubdate DATE ) ; / |
CREATE OR REPLACE TYPE project_nested AS TABLE OF project_type NOT NULL ; / |
范例:创建数据表,包含此嵌套表列
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(50) NOT NULL , projects project_nested , CONSTRAINT pk_did PRIMARY KEY (did) ) NESTED TABLE projects STORE AS projects_nested_table ; |
范例:通过PL/SQL执行数据表增加信息操作
DECLARE v_project_list project_nested := project_nested( project_type(10,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》',69.8,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd'))) ; v_deptdepartment%ROWTYPE ; BEGIN v_dept.did := 88 ; v_dept.deptname := '魔乐科技' ; v_dept.projects := v_project_list ;-- 直接赋予嵌套表 INSERT INTO department VALUES v_dept ; -- 直接使用ROWTYPE对象增加 END ; / |
范例:通过PL/SQL执行数据表更新操作
DECLARE v_project_list project_nested := project_nested( project_type(30,'《Java WEB高级案例篇》',380,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(31,'《Android游戏开发教程》',580,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(32,'《Struts2高级开发教程》',888,TO_DATE('2012-03-19','yyyy-mm-dd'))) ; BEGIN UPDATE department SET projects=v_project_list WHERE did=88 ; -- 直接使用嵌套表保存更新数据 END ; / |
范例:创建项目数组
CREATE OR REPLACE TYPE project_varray AS VARRAY(3) OF VARCHAR2(50) ; / |
范例:定义部门表,使用可变数组
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(30) NOT NULL , projects project_varray , CONSTRAINT pk_did PRIMARY KEY (did) ) ; |
范例:向department表中增加数据
INSERT INTO department(did,deptname,projects) VALUES (10,'魔乐科技', project_varray('ERP','CRM','CMS')) ; INSERT INTO department(did,deptname,projects) VALUES (20,'MLDN出版部', project_varray('《Java开发实战经典》','《Anbdroid开发实战经典》')) ; COMMIT ; |
范例:查看department表数
SELECT * FROM department ; |
范例:查找一个部门的所有项目
SELECT * FROM TABLE ( SELECT projects FROM department WHERE did=20) ; |
范例:修改一个部门的项目
UPDATE department SET projects=project_varray('《Oracle开发实战经典》','《Java开发实战经典》','《Android开发实战经典》') WHERE did=20 ; COMMIT ; |
范例:查询更新后的部门信息
SELECT * FROM TABLE ( SELECT projects FROM department WHERE did=20) ; |
范例:创建一个表示项目类型的对象
CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR2(50), projectfunds NUMBER , pubdate DATE ) ; / |
范例:定义新的数组类型
CREATE OR REPLACE TYPE project_varray AS VARRAY(3) OF project_type ; / |
范例:定义数据表,使用可变数组
DROP TABLE department PURGE ; CREATE TABLE department ( did NUMBER , deptname VARCHAR2(50) NOT NULL , projects project_varray , CONSTRAINT pk_did PRIMARY KEY (did) ) ; |
范例:向表中增加数据
INSERT INTO department(did,deptname,projects) VALUES (10,'魔乐科技', project_varray( project_type(10,'ERP管理系统',9000000,TO_DATE('2014-02-14','yyyy-mm-dd')) , project_type(11,'CRM客户系统',10000000,TO_DATE('2016-03-12','yyyy-mm-dd')))) ; INSERT INTO department(did,deptname,projects) VALUES (20,'MLDN出版部', project_varray( project_type(10,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》',69.8,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd')))) ; COMMIT ; |
范例:查看部门表数据
SELECT * FROM department ; |
范例:查看一个部门的所有项目
SELECT * FROM TABLE ( SELECT projects FROM department WHERE did=20) ; |
范例:更新一个部门的项目信息
UPDATE department SET projects=project_varray( project_type(15,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(16,'《Oracle开发实战经典》',87.8,TO_DATE('2013-08-27','yyyy-mm-dd')) , project_type(17,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd'))) WHERE did=20 ; COMMIT ; |
范例:查询更新后的部门信息
SELECT * FROM TABLE ( SELECT projects FROM department WHERE did=20) ; |
范例:在PL/SQL中使用可变数组
DECLARE TYPE project_varray IS VARRAY(3) OF VARCHAR2(50) ; v_projects project_varray := project_varray(NULL,NULL,NULL) ; BEGIN v_projects(1) := 'JAVA SE' ; v_projects(2) := 'JAVA EE' ; v_projects(3) := 'Android' ; FOR x IN v_projects.FIRST .. v_projects.LAST LOOP DBMS_OUTPUT.put_line(v_projects(x)) ; END LOOP ; END ; / |
范例:定义复合结构的可变数组
CREATE OR REPLACE TYPE project_type AS OBJECT( projectid NUMBER , projectname VARCHAR(50), projectfunds NUMBER , pubdate DATE ) ; / |
DECLARE TYPE project_varray IS VARRAY(3) OF project_type NOT NULL ; v_projects project_varray := project_varray( project_type(10,'《Java开发实战经典》',79.8,TO_DATE('2008-08-13','yyyy-mm-dd')) , project_type(11,'《Java Web开发实战经典》',69.8,TO_DATE('2010-08-27','yyyy-mm-dd')) , project_type(12,'《Android开发实战经典》',88,TO_DATE('2012-03-19','yyyy-mm-dd'))) ; BEGIN FOR x IN v_projects.FIRST .. v_projects.LAST LOOP DBMS_OUTPUT.put_line('项目编号:' || v_projects(x).projectid || ',项目名称:' || v_projects(x).projectname || ',金额:' || v_projects(x).projectfunds || ',发布日期:' || v_projects(x).pubdate) ; END LOOP ; END ; / |
范例:验证CARDINALITY运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; BEGIN DBMS_OUTPUT.put_line('集合长度:' || CARDINALITY(v_all)) ; END ; / |
范例:验证CARDINALITY运算符,使用SET运算符取消重复数据
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; BEGIN DBMS_OUTPUT.put_line('集合长度:' || CARDINALITY(SET(v_all))) ; END ; / |
范例:验证EMPTY运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested () ; BEGIN IF v_allA IS NOT EMPTY THEN DBMS_OUTPUT.put_line('v_allA不是一个空集合!') ; END IF ; IF v_allB IS EMPTY THEN DBMS_OUTPUT.put_line('v_allB是一个空集合!') ; END IF ; END ; / |
范例:使用MEMBER OF运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','beijing','lixinghua') ; v_str VARCHAR2(10) := 'mldn' ; BEGIN IF v_str MEMBER OF v_all THEN DBMS_OUTPUT.put_line('mldn字符串存在。') ; END IF ; END ; / |
范例:验证MULTISET EXCEPT运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET EXCEPT v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; / |
范例:验证MULTISET INTERSECT运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET INTERSECT v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; / |
范例:验证MULTISET UNION运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('beijing','java') ; v_newlist list_nested ; BEGIN v_newlist := v_allA MULTISET UNION v_allB ; FOR x IN 1 .. v_newlist.COUNT LOOP DBMS_OUTPUT.put_line(v_newlist(x)) ; END LOOP ; END ; / |
范例:验证SET运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; BEGIN IF v_allA IS A SET THEN DBMS_OUTPUT.put_line('v_allA是一个集合。') ; END IF ; END ; / |
范例:验证SUBMULTISET运算符
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_allA list_nested := list_nested ('mldn','beijing','java') ; v_allB list_nested := list_nested ('mldn','java') ; BEGIN IF v_allB SUBMULTISET v_allA THEN DBMS_OUTPUT.put_line('v_allB是v_allA的一个子集合。') ; END IF ; END ; / |
范例:使用COUNT函数取得集合中的元素个数
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN DBMS_OUTPUT.put_line('集合长度:' || v_all.COUNT) ; END ; / |
范例:使用DELETE函数删除一个数据
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN v_all.DELETE(1) ; -- 删除指定索引的数据 FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; / |
范例:DELETE()函数删除一个范围的数据
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN v_all.DELETE(1,3) ; -- 删除指定范围的数据 FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; / |
范例:判断某一数据是否存在
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua','android','java') ; BEGIN IF v_all.EXISTS(1) THEN DBMS_OUTPUT.put_line('索引为1的数据存在。') ; END IF ; IF NOT v_all.EXISTS(10) THEN DBMS_OUTPUT.put_line('索引为10的数据不存在。') ; END IF ; END ; / |
范例:扩充集合长度
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua') ; BEGIN v_all.EXTEND(2) ; -- 集合扩充2个长度 v_all(4) := 'android' ; v_all(5) := 'java' ; DBMS_OUTPUT.put_line('集合长度:' || v_all.COUNT) ; FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; / |
范例:扩充集合长度,并使用已有内容填充
DECLARE TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL ; v_all list_nested := list_nested ('mldn','魔乐科技','lixinghua') ; BEGIN v_all.EXTEND(2,1) ;-- 集合扩充2个长度,使用原始集合的第1个数据填充 DBMS_OUTPUT.put_line('集合长度:' || v_all.COUNT) ; FOR x IN v_all.FIRST .. v_all.LAST LOOP DBMS_OUTPUT.put_line(v_all(x)) ; END LOOP ; END ; / |
范例:使用LIMIT取得集合的最高下标
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android','java') ; BEGIN DBMS_OUTPUT.put_line('数组集合的最大长度:' || v_info.LIMIT) ; DBMS_OUTPUT.put_line('数组集合的数据量:' || v_info.COUNT) ; END ; / |
范例:验证NEXT函数
DECLARE TYPE info_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER ; v_info info_index ; v_foot NUMBER ; BEGIN v_info (1) := 'MLDN' ; v_info (10) := 'JAVA' ; v_info (-10) := 'Oracle' ; v_info (-20) := 'EJB' ; v_info (30) := 'Android' ; v_foot := v_info.FIRST ; -- 取得集合的第一个索引值 WHILE(v_info.EXISTS(v_foot)) LOOP -- 判断此索引数据是否存在 DBMS_OUTPUT.put_line('v_info(' || v_foot || ') = ' || v_info(v_foot)) ; v_foot := v_info.NEXT(v_foot) ; -- 取得下一个索引值 END LOOP ; DBMS_OUTPUT.put_line('索引为10的下一个索引是:' || v_info.NEXT(10)) ; DBMS_OUTPUT.put_line('索引为-10的上一个索引是:' || v_info.PRIOR(-10)) ; END ; / |
范例:验证TRIM函数
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android','java','oracle','ejb') ; BEGIN DBMS_OUTPUT.put_line('删除集合之前的数据量:' || v_info.COUNT) ; v_info.TRIM ; -- 删除1个集合的数据,还剩下4个数据 DBMS_OUTPUT.put_line('v_info.TRIM删除集合数据之后的数据量:' || v_info.COUNT) ; v_info.TRIM(2) ; -- 删除2个数据之后还剩下2个数据 DBMS_OUTPUT.put_line('v_info.TRIM(2)删除集合数据之后的数据量:' || v_info.COUNT) ; END ; / |
范例:处理集合未初始化异常
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray ; -- 此集合没有初始化 BEGIN v_info(0) := 10 ; -- 此时集合未初始化,所以产生异常 EXCEPTION WHEN COLLECTION_IS_NULL THEN DBMS_OUTPUT.put_line('集合未初始化,无法使用!') ; END ; / |
范例:处理访问索引超过集合长度异常
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android') ; BEGIN DBMS_OUTPUT.put_line(v_info(3)) ; -- 没有此索引数据 EXCEPTION WHEN SUBSCRIPT_BEYOND_COUNT THEN DBMS_OUTPUT.put_line('索引值超过定义的元素个数!') ; END ; / |
范例:处理访问索引超过集合最大定义长度异常
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android') ; BEGIN DBMS_OUTPUT.put_line(v_info(30)) ; -- 索引下标超过最大范围 EXCEPTION WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN DBMS_OUTPUT.put_line('索引值超过定义集合类型的最大元素个数!') ; END ; / |
范例:设置错误的索引数据
DECLARE TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ; v_info list_varray := list_varray('mldn','android') ; -- 此集合没有初始化 BEGIN DBMS_OUTPUT.put_line(v_info('1')) ; -- 1可以自动变为数字(PLS_INTEGER) DBMS_OUTPUT.put_line(v_info('a')) ; -- a无法自动转换为数字 EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.put_line('索引值类型错误!') ; END ; / |
范例:处理索引表集合中访问已删除数据集合异常
DECLARE TYPE info_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER ; v_info info_index ; BEGIN v_info(1) := 'mldn' ; v_info(2) := 'android' ; v_info(3) := 'java' ; v_info.DELETE(1) ; -- 删除数据 DBMS_OUTPUT.put_line(v_info(1)) ; -- 此元素已经被删除,无法访问 DBMS_OUTPUT.put_line(v_info(2)) ; DBMS_OUTPUT.put_line(v_info(3)) ; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('此数据已经被删除!') ; END ; / |
范例:通过PL/SQL程序块执行多条数据表更新
DECLARE TYPE emp_varray IS VARRAY(8) OF emp.empno%TYPE ; v_empnoemp_varray := emp_varray(7369,7566,7788,7839,7902) ; BEGIN FOR x IN v_empno.FIRST .. v_empno.LAST LOOP UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ; END LOOP ; END ; / |
范例:利用FORALL向数据库一次性发出多条语句
DECLARE TYPE empno_varray IS VARRAY(8) OF emp.empno%TYPE ; v_empno empno_varray := empno_varray(7369,7566,7788,7839,7902) ; BEGIN FORALL x IN v_empno.FIRST .. v_empno.LAST UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ; FOR x IN v_empno.FIRST .. v_empno.LAST LOOP DBMS_OUTPUT.put_line('雇员编号:' || v_empno(x) || '更新操作受影响的数据行为:' || SQL%BULK_ROWCOUNT(x)) ; END LOOP ; END ; / |
范例:批量接收查询数据
DECLARE TYPE ename_varray IS VARRAY(8) OF emp.ename%TYPE ; v_ename ename_varray ; BEGIN SELECT ename BULK COLLECT INTO v_ename FROM emp WHERE deptno=10 ; FOR x IN v_ename.FIRST .. v_ename.LAST LOOP DBMS_OUTPUT.put_line('10部门雇员姓名:' || v_ename(x)) ; END LOOP ; END ; / |
范例:批量接收数据
DECLARE TYPE dept_nested IS TABLE OF dept%ROWTYPE ; v_dept dept_nested ; BEGIN SELECT * BULK COLLECT INTO v_dept FROM dept ; -- 将雇员表全部数据拷贝到嵌套表之中 FOR x IN v_dept.FIRST .. v_dept.LAST LOOP DBMS_OUTPUT.put_line('部门编号:' || v_dept(x).deptno || ',名称:' || v_dept(x).dname || ',位置:' || v_dept(x).loc) ; END LOOP ; END ; / |