ORACLE|PL/SQL学习笔记 ORACLE|PL

PL/SQL概述 PL/SQL(Procedure Language&Structure Query Language)是Oracle对标准数据库语言的扩展，由两部分组成：一部分是数据库引擎部分；另一部分是可嵌入到许多产品工具中的独立引擎。
PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，由至少一个逻辑块组成。
完整的PL/SQL程序的总体结构

DECLARE /* *声明部分：再次声明PL/SQL块中用到的变量，类型和油表，以及局部的存储过程和函数 *变量的声明必须在使用之前 *每一条语句必须以分号结束，语句可以分行 */ BEGIN /* *执行部分：过程及SQL语句，即程序的主要部分。必须有，其他两部分不是必须的 */ EXCEPTION--异常处理部分：错误处理END; --必须带分号

例题

declare v_ename VARCHER2(10); v_salary NUMBER(7,2); v_hiredate DATE; begin SELECT eanme,sal,hiredate INTO v_ename,v_salary,v_hiredate FROM emp WHERE empno = &empno; --替换变量 dbms_output.put_line('雇员名：'||v_ename); dbms_output.put_line('工资：'||v_salary); dbms_output.put_line('入职日期：'||v_hiredate); exception WHEN NO_DATA_FOUND THEN dbms_output.put_line('您输入的雇员号不存在'); end;

PL/SQL块分类
匿名块：动态构造，只能执行一次
子程序：存储在数据库中的存储过程、函数及包等。可以在其他程序中调用它们
触发器：操作数据库会触发一些时间从而自动执行相应的程序
基本数据类型

文章图片

声明变量
变量名 [CONSTANT] 数据类型 [NOT NULL] [:=|DEFAULT 初始值]
CONSTANT用于定义常量
NOT NULL用于强制初始化变量
:=和DEFAULT用于为变量指定初始值
变量访问范围从定义开始直到与它所在的DECLARE相匹配的END为止
使用%TYPE

DECLARE v_ename emp.ename%TYPE; --该属性会自动根据表列或其他变量的类型和长度定义新变量。 v_salary emp.sal%TYPE; v_hiredate emp.hiredate%TYPE; BEGIN --与上面相同，省略 EXCEPTION END;

记录类型在PL/SQL中使用复合变量可以以特定的结构存储多个数值，PL/SQL记录类型是最常用的复合类型之一。记录类型是把逻辑相关的数据作为一个单元存储起来，它主要用于处理单行多列数据。
自定义PL/SQL记录类型

DECLARE /*首先记录类型*/ TYPE emp_record_type IS RECORD( ename emp.ename%TYPE,--注意这是逗号，与上面那一行是一条语句 alary emp.sal%TYPE,--Filed1 type [NOT NULL] [:=exp1],Filed用于定义记录成员 hiredate emp.hiredate%TYPE); /*声明记录变量*/ emp_record emp_record_type; --变量名类型 BEGIN SELECT eanme,sal,hiredate INTO emp_record FROM emp WHERE empno = &empno; --替换变量 dbms_output.put_line('雇员名：'||emp_record.ename); dbms_output.put_line('工资：'||emp_record.salary); dbms_output.put_line('入职日期：'||emp_record.hiredate); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('您输入的雇员号不存在'); END;

%ROWTYPE记录类型
使用%ROWTYPE可以使变量获得这个记录的数据类型，用于基本表或试图定义记录变量，记录成员的名称与类型与它所引用的表或视图的名称和类型一致

DECLARE /*使用%ROWTYPE声明记录变量*/ emp_record emp%ROWTYPE; BEGIN SELECT * INTO emp_record--emp_record封装了emp表的一行数据 FROM emp WHERE empno = &empno; --替换变量 dbms_output.put_line('雇员名：'||emp_record.ename); dbms_output.put_line('工资：'||emp_record.salary); dbms_output.put_line('入职日期：'||emp_record.hiredate); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('您输入的雇员号不存在'); END;

集合类型 PL/SQL一维表
PL/SQL多维表
嵌套表
可变数组
集合方法
运算符与表达式运算符

算术运算符 +，-，*，/ 加减乘除 ** 乘方 :=赋值号 ||字符串连接符 =>关系号关系运算符 =，<,>,<=,>= 等于，小于，大于，小于等于，大于等于 <>，!=，~=，^= 不等于逻辑运算符 IS NULL 是否是空值 BETWEEN AND 取值介于两者之间 IN 取值是指定的列表值之一 AND，OR 与或 NOT 逻辑非，取反操作

表达式
NULL值
空值加数字仍是空值
空值加字符是字符
BOOLEAN，布尔值只有TRUE FALSE NULL三个值
数据库赋值
通过SELECT INTO语句来完成，每次执行SELECT INTO语句就赋值一次。不能将SELECT语句中的列赋值给布尔变量
可转换的类型赋值

v_count:=TO_NUMBER('20'); --CHAR转换成NUMBER v_pay:=TO_CHAR('3000.79')||'元'; --NUMBER转换成CHAR v_date:=TO_DATE('2012.07.03','yyyy.mm.dd'); --字符转换为日期 v_now:=TO_CHAR(SYSDATE,'yyyy.mm.dd.hh24:mi:ss'); --日期转换为字符

流程控制语句条件分支语句

IF-THEN DECLARE v_num1 NUMBER; v-num2 NUMBER; BEGIN v_num1:=&n1; v_num2:=&n2; IF v_num1>v_num2 THEN dbms_output.put_line(v_num1 ||'>'||v_num2); END IF; END; IF-THEN-ELSE IF v_num1>v_num2 THEN dbms_output.put_line(v_num1 ||'>'||v_num2); ELSE dbms_output.put_line(v_num1 ||'<='||v_num2); END IF; IF-THEN-ELSIF IF v_salary < 2000 THEN v_comment:='员工'||v_empno||'的工资较低'; ELSLF v_salary < 3500 THEN v_comment:='员工'||v_empno||'的工资适中'; ELSE v_comment:='员工'||v_empno||'的工资适中'; END IF; dbms_output.put_line(v_comment); CASE语句 CASE WHEN v_grade>90 THEN v_appraisal:='相当靠谱'; WHEN v_grade>75 THEN v_appraisal:='靠谱'; WHEN v_grade>60 THEN v_appraisal:='比较靠谱'; ELSE v_appraisal:='不靠谱'; --带有ELSE的CASE可以避免CASE_NOT_FOUND异常 END CASE; dbms_output.put_line(v_appraisal);

循环语句

简单循环 LOOP v_count; =v_count + 1; dbms_output.put_line('v_count的当前值为：'||v_count); EXIT WHEN v_count = 10; END LOOP; WHILE循环 WHILE v_count<=10 LOOP dbms_output.put_line('v_count的当前值为：'||v_count); v_count; =v_count + 1; END LOOP; FOR循环 /* *IN后面可以跟REVERSE，表示循环变量自动减一，可以省略。 *再后面的用于标注上限和下限的数字必须是从小到大的书序，且必须是整数。 *FOR语句可以使用EXIT退出循环。 */ FOR i IN 10..1 LOOP dbms_output.put_line('i的当前值为：'||i); END LOOP;

嵌套循环例题

DECLARE v_result NUMBER; BEGIN <> FOR i IN 1..10 LOOP <> FOR j IN 1..10 LOOP v_result := i*j; EXIT outer WHEN v_result > 40; END LOOP inner; dbms_output.put_line('<>v_result的当前值为：'||v_result); END LOOP outer; dbms_output.put_line('<>v_result的当前值为：'||v_result);

异常处理预定义异常
非预定义异常
自定义异常
游标 Oracle中主要有以下两种类型的游标
显式游标
处理步骤：

定义游标 CURSOR cursor_name[(parameter[,parameter]...)]IS select_statement; 游标参数只能为输入参数，格式如下： parameter_name [IN] datatype[{:=|DEFAULT} expression],数据类型不能有长度约束打开游标 OPEN cursor_name[([paramter=>] value[,parameter=>] value]...)]; 提取游标数据 FETCH cursor_name INTO {variable_list|record_variable }; 对游标指针指向的记录进行处理继续处理直到活动集合中没有数据，关闭游标 CLOSE cursor_name;

游标属性：

%ISOPEN,检测游标是否已经打开，打开返回true %FOUND，检测游标结果集是否存在数据，存在返回true %NOTFOUND，检测游标是否不存在数据，存在返回FALSE %ROWCOUNT，返回已提取的实际行数

使用游标

使用标量变量接受数据 DECLARE /*声明游标*/ CURSOR dept_cursor IS SELECT deptno,dname FROM dept; v_deptno dept.deptno%TYPE; v_dname dept.dname%TYPE; BEGIN /*打开游标*/ OPEN dept_cursor; /*循环取值*/ LOOP /*将当前行数据提取到变量中*/ FETCH dept_cursor INTO v_deptno,v_dname; EXIT WHEN dept_cursor%NOTFOUND; --如果未提取到数据，则退出循环 dbms_output.put_line('部门号：'||v_deptno||',部门名：'||v_dname); END LOOP; /*关闭游标*/ CLOSE dept_cursor; END; 使用记录变量接收数据 dept_record dept%ROWTYPE; FETCH dept_cursor INTO dept_record EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3; --如果未提取到数据或已经提取三条，退出循环使用集合变量接受数据DECLARE CURSOR dept_cursor IS SELECT deptno,dname loc FROM dept; TYPE dept_table_type IS TABLE OF dept_cursor%ROWTYPE INDEX BY BINARY_INTEGER; /*声明集合变量*/ dept_table dept_table_type; idx NUMBER; BEGIN /*打开游标*/ OPEN dept_cursor; /*循环取值*/ LOOP idx:=dept_coursor%ROWCOUNT+1; FETCH dept_cursor INTO dept_table(idx); EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3; dbms_output.put_line('部门号：'||dept_table(idx).deptno||',部门名：'||dept_table(idx).dname||',所在地：'||dept_table(idx).loc); END LOOP; /*关闭游标*/ CLOSE dept_cursor; END; 带参数的游标 DECLARE /*声明带参数的游标，并指定缺省值*/ CURSOR dept_cursor(dept_no NUMBER DEFAULT 20) IS SELECT deptno,dname,loc FROM dept WHERE deptno&dept_no); LOOP FETCH dept_cursor INTO dept_record EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3; --如果未提取到数据或已经提取三条，退出循环 dbms_output.put_line('部门号：'||dept_record.deptno||',部门名：'||dept_record.dname||',所在地：'||dept_record.loc); END LOOP; END;

游标FOR循环
PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN，FETCH，CLOSE语句和循环语句的功能。

DECLARE CURSOR dept_cursor IS SELECT deptno,dname,loc FORM dept; BEGIN FOR dept_record IN dept_cursor LOOP /*dept_record为游标FOR循环语句隐含声明的索引变量，该变量为记录型变量，其结构与游标查询语句返回的结构集合的结构相同*/ dbms_output.put_line('部门号：'||dept_record.deptno||',部门名：'||dept_record.dname||',所在地：'||dept_record.loc); EXIT WHEN dept_cursor%NOTFOUND OR dept_cursor%ROWCOUNT>3; END; 如果在游标FOR循环中不需要引用游标属性，为了简化PL/SQL快，可以直接FOR循环中引用子查询```sql BEGIN FOR dept_record IN (SELECT deptno,dname,loc FROM dept)LOOP dbms_output.put_line('部门号：'||dept_record.deptno||',部门名：'||dept_record.dname||',所在地：'||dept_record.loc); END LOOP; END;

游标修改和删除操作
定义游标必须带有 FOR UPDATE子句
更新或者删除游标行时，则可在DELETE和UPDATE语句中使用WHERE CURRENT OF cursor_name子句

使用游标修改数据 DECLARE v_deptno emp.deptno%type:=&p_deptno; CURSOR emp_cursor IS SELECT empno,sal FROM emp WHERE deptno = v_deptno FOR UPDATE NOWAIT; --NOWAIT用于指定不等待锁，之前可以有个[OF column_reference]用于指定被加锁的特定表 BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.sal<1500 THEN dbms_output.put_line('职工号：'||emp_record.empno||'，工资：'||emp_record.sal); UPDATE emp SET sal = 1500 WHERE CURRENT OF emp_cursor; --指示游标所指向的当前行 END IF; END LOOP; COMMIT; --在PL/SQL中，如果进行数据的DML操作，需要进行事务的显示提交； END; /*当查询语句设计多张表时，如果不带有OF子句，会在多张表上同时加锁，如果只在特定表上加锁，需要带有OF子句*/使用游标删除数据 DECLARE v_deptno emp.deptno%type:=&p_deptno; CURSOR emp_cursor IS SELECT empno,deptno,sal FROM emp FOR UPDATE NOWAIT; BEGIN FOR emp_record IN emp_cursor LOOP IF emp_record.sal<1500 AND emp_record.deptnp=v_deptno THEN dbms_output.put_line('职工号：'||emp_record.empno||'，工资：'||emp_record.sal); DELETE FROM emp WHERE CURRENT OF emp_cursor; END IF; END LOOP; COMMIT; --在PL/SQL中，如果进行数据的DML操作，需要进行事务的显示提交； END;

隐式游标
每次运行SELECT或DML语句时，PL/SQL都会打开一个隐式的游标。隐式游标的默认名称是SQL

DECLARE curename emp.ename%TYPE; curjob emp.Job%TYPE; BEGIN SELECT ename,job into curename,curjob from emp WHRER empno = 7698; IF SQL%FOUND THEN dbms_output.put_line(); END IF; END;

游标变量游标变量与游标都是一个指向多行查询结果集合中当前数据行的指针。但游标变量是动态的，而游标是静态的。游标只能指向一个查询的内存处理区域，而游标变量则可以与不同的查询语句相连，可以指向不同查询语句的内存处理区域（不是同时）。
定义游标变量
TYPE ref_type_name IS REFCURSOR [RETURN return_type];
ref_type_name是新定义的游标变量类型名称
return_type为游标变量的返回值类型，它必须为记录型变量
打开游标变量
OPEN cursor_variabe_name FOR select_statement;
提取数据
FETCH cursor_variable_name INTO {variable [,variable]…|record_variabe};
关闭游标变量
CLOSE cursor_variable_name;

游标变量操作 /*定义REF CURSOR，根据用户输入的部门号，输出雇员的相关信息*/ DECLARE TYPE emp_job_record IS RECORD( empno emp.empno%TYPE; ename emp.ename%TYPE; jpb emp.job%TYPE; ); /*强类型REF CURSOR*/ TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_record; --指定RETURN子句，在打开游标时，SELECT语句的返回结果必须与RERUEN子句的记录类型匹配；不指定RETURN语句时，在打开游标时可以指定任何SELECT语句。 /*弱类型REF CURSOR*/ --TYPE emp_job_refcur_type IS REF CURSOR RETURN /*强类型游标变量*/ emp_ref_cur emp_job_refcur_type; emp_job emp_job_record; v_deptno dept.deptno%TYPE:=&p_deptno; BEGIN OPEN emp_ref_cur FOR SELECT empno,ename,job FROM emp WHERE deptno=v_deptno ORDER BY deptno; FETCH emp_ref_cur INTO emp_job; WHILE emp_ref_cur%FOUND LOOP dbms_output.put_line(emp_job.empno||':'||emp_job.ename||'is a'||emp_job.job); FETCH emp_ref_cur INTO emp_job; END LOOP; END;

存储过程

存储过程是一组与存储在数据库中，为了完成某种特定功能的PL/SQL语句集。 **语法结构**

CREATE [OR REPLACE] PROCEDURE Procedure_name --存储过程名字 [(argument[{IN|OUT|IN OUT}]Type,argument[{IN|OUT|IN OUT}]Type)]--参数列表，缺省为IN。 {IS|AS} <声明部分>--包含类型，游标，变量，异常和嵌套子程序的声明 BEGIN <执行部分> EXCEPTION <可选的异常处理程序> END;

带三种参数的存储过程

/*使用标量变成作为输入参数*/ CREATE OR REPLACE PROCEDURE add_dept(deptno dept.deptno%TYPE,dname dept.dname%TYPE,loc dept.loc%TYPE) IS BEGIN INSERT INTO dept VALUES(deptno,dname,loc); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line('主键冲突，重新指定主键值'); END; /*三种调用方式*/ BEGIN add_dept(60,'FINANCE','CHICAGO'); add_dept(deptno=>90,dname=>'FINANCE',loc=>'CHICAGO'); add_dept(&deptno,'&dname','&loc'); END; /*使用记录类型作为输入参数*/ CREATE OR REPLACE PROCEDURE add_dept(dept_record dept%ROWTYPE) ... INSERT INTO dept VALUES(dept_record.deptno,dept_record.dname,deptno_record.loc); ... END; /*调用*/ DECLARE dept_record dept%ROWTYPE; BEGIN dept_record.deptno:=&deptno; dept_record.dname:='&dname'; dept_record.loc:='&loc'; add_dept(dept_record); COMMMIT; END; /*使用集合类型作为输入参数*/ 暂时省略--教材P226/*带输出参数的存储过程*/ CREATE OR REPLACE PROCEDURE get_dept(p_deptno dept.deptno%TYPE,dname OUT dept.dname%TYPE,loc OUT dept.loc%TYPE) IS BEGIN SELECT dname,loc INTO dname,loc FROM dept WHERE deptno=p_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('不存在该部门'); END; /*调用：get_dept(v_deptno,v_dname,v_loc)*//*带输入输出参数的存储过程*/ --省略

维护存储过程

删除存储过程 DROP PROCEDURE add_sub; 重新编译存储过程 ALTER PROCEDURE get_dept COMPILE; 重新编译处于INVALID状态的过程确定过程状态 SELECT * FROM user_objects WHERE status = 'INVALID' AND object_type = 'PROCEDURE'; 查询数据字典USER_OBJECTS中处于INVALID状态的过程信息查看过程文本 SELECT text FROM user_source WHERE name = UPPER('get_dept') AND type='PROCEDURE'; 查询数据字典USRE_SOURCE，取得当前用户所拥有的的子程序名称及其创建文本

存储过程中使用DDL语句

CREATE OR REPLACE PROCEDURE ddl_pro IS createsql varchar(100); BEGIN createsql :='create table T_TEACHER(tno number,tname varchar2(50))'; EXECUTE IMMEDIATE createsql; END; /*执行后会创建表ddl_pro*/

自定义函数

文章图片

/*使用记录类型作为返回类型*/ CREATE OR REPLACE FUNCTION get_dept_info(p_deptno dept.deptno%TYPE) RETURN dept%ROWTYPE --指定返回值类型为记录类型 IS dept_record dept%ROWTYPE; BEGIN SELECT *INTO dept_record FROM dept WHERE deptno = p_deptno; RETURN dept_record; --返回记录值 EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20003,'指定的部门不存在'); END; /*调用*/ DECLARE dept_record dept%ROWTYPE; BEGIN dept_record:=get_dept_info(&deptno); dbms_output.put_line(dept_record.deptno); END; /*使用集合类型作为返回类型*/ CREATE OR REPLACE FUNCTION get_dept_name(p_loc dept.loc%TYPE) RETURN dname_table_type --指定返回值类型为集合类型 IS dname_table dname_table_type; BEGIN SELECT dname BULK COLLECT INTO dname_table FROM dept WHERE loc = UPPER(p_loc); RETURN dname_table; --返回记录值 EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20003,'指定的部门不存在'); END; /*调用*/ DECLARE dname_table dname_table_type; BEGIN /*使用集合变量接收返回值*/ dname_table:=get_dept_name('&loc'); FOR i IN 1..dname_table.COUNT LOOP dmbs_output.put_line('部门名：'||dname_table(i)); END lOOP; END;

维护函数
与维护存储过程类似 PROCEDURE变FUNCTION
包

包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，具有面向对象程序设计语言的特点，是对这些PL/SQL程序单元的封装。包类似于类，变量相当于类中的成员变量，过程和函数相当于类方法。

/*包定义，部分声明包内的共有元素*/ CREATE[OR REPLACE]PACKAGE package_name {IS|AS} <定义公用常量、变量、类型、游标> <定义公用的过程和函数,只能包含过程头或者函数头> END [package_name]; /*包体定义，实现包定义所规范的公用过程和函数，还可以定义报的私有组件，报的私有组件只能在该包中引用*/ CREATE[OR REPLACE]PACKAGE BODY package_name --包体名称必须与包完全一致，包体定义共有子程序也应该与包中声明的子程序格式一致。 {IS|AS} <定义私有常量、变量、类型、游标、过程和函数> <实现公用的过程和函数> END [package_name]; /*在当前方案中创建包或者包体需要用户具有 CREATE PACKAGE(BOBY)权限，如果在其他方案中，用户必须有CREATE ANG PACKAGE(BOBY)权限*/

调用包

/*定义包*/ CREATE OR REPLACE PACKAGE dept_pack IS v_num INT; /*向部门表中添加数据，使用记录类型作为过程的参数*/ PROCEDURE add_dept(dept_record dept%ROWTYPE); /*从部门表中移除指定的部门信息*/ PROCEDURE del_dept(p_deptno NUMBER); /*从部门表中查询并返回指定的部门信息*/ FUNCTION get_dept(p_deptno NUMEBR) RETURN dept%ROWTYPE; END dept_pack; /*定义包体*/ CREATE OR REPLACE PACKAGE BODY dept_pack IS /*check_dept是包体的私有子程序*/ FUNCTION check_dept(p_deptno dept.deptno%TYPE) RETURN BOOLEAN IS v_count INT; BEGIN SELECT COUNT(*) INTO v_count FROM dept WHERE deptno = p_deptno; IF v_count >0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END check_dept; /*实现add_dept过程*/ PROCEDURE add_dept(dept_record dept%ROWTYPE) IS BEGIN IF check_dept(dept_record.deptno)=FALSE THEN INSERT INTO dept VALUES(dept_record.deptno,dept_record.dname,UPPER(dept_record.loc)); dbms_output.put_line('添加成功！'); ELSE dbms_output.put_line('添加失败：部门编号冲突'); END IF; END add_dept; /*实现del_dept过程*/ PROCEDURE del_dept(p_deptno NUMBER) IS BEGIN IF check_dept(dept_record.deptno)=TRUE THEN DELETE FROM dept WHERE deptno=p_deptno; dbms_output.put_line('删除成功！'); ELSE dbms_output.put_line('删除失败：指定部门不存在！'); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('删除出错：'||SQLCODE||'---'||SQLERRM); END del_dept; /*实现get_dept函数*/ FUNCTION get_dept(p_deptno NUMEBR) RETURN dept%ROWTYPE IS dept_record dept%ROWTYPE; no_result EXCEPTION; BEGIN IF check_dept(p_deptno)=TRUE THEN SELECT * INTO dept_record FROM dept WHERE deptno=p_deptno; RETURN dept_record; ELSE RAISE no_result; END IF; EXCEPTION WHEN no_result THEN RAISE_APPLICATION_ERROR(-20009,'查询部门不存在'); WHEN OTHERS THEN /*输出错误编码和信息*/ --dbms_output.put_line(||SQLCODE||'---'||SQLERRM); RAISE_APPLICATION_ERROR(-20100,'查询出错'); END get_dept; END dept_pack; /*调用包*/ DECLARE dept_record dept%ROWTYPE; BEGIN /*访问公共变量*/ dept_pack.v_num:=10; /*增加数据*/ dept_record.deptno := &add_deptno; dept_record.dname := &dname; dept_record.loc := &loc; dept_pack.add_dept(dept_record); /*删除数据*/ dept_record.deptno := &del_deptno; dept_pack.del_dept(dept_record.deptno); /*查询数据*/ dept_record.deptno := &query_deptno; dept_record :=dept_pack.get_dept(dept_record.deptno); dbms_output.put_line(dept_record.deptno||','||dept_record.dname||','||dept_record.loc); COMMIT; END;

使用集合类型
使用集合变量作为输入参数，可以将调用环境或者应用程序的集合变量数据或者数组数据传递包的公用过程中
作为返回类型，可以将多行数据返回到调用环境或者应用程序中

CREATE OR REPLACE PACKAGE dept_pack2 IS /*定义基于记录类型的嵌套表*/ TYPE dept_table_type IS TABLE OF dept%ROWTYPE; /*获取所有部门的信息*/ FUNCTION get_dept RETURN dept_table_type; END dept_pack2; CREATE OR REPLACE PACKAGE BOBY dept_pack2 IS /*实现get_dept函数*/ FUNCTION get_dept RETURN dept_table_type IS dept_table dept_table_type; BEGIN SELECT * BULK COLLECT INTO dept_table FROM dept; RETURN dept_table; END get_dept; END dept_pack2; DECLARE dept_table dept_pack2.dept_table_type; /*注意：下述声明是错误的*/ --TYPE dept_table_type IS TABLE OF dept%ROWTYPE; --dept_tabl dept_table_type; /*会出现类型不匹配的错误*/ BEGIN dept_table := dept_pack2.get_dept; FOR i IN 1..dept_table.COUNT LOOP dbms_output.put_line('部门名：'||dept_table(i).deptno ||'，部门名：'||dept_table(i).dname||'，位置：'||dept_table(i).loc); END LOOP; END;

重载

CREATE OR REPLACE PACKAGE overload_demo IS FUNCTION func1 RETURN number; FUNCTION func1(n1 number) RETURN number; FUNCTION func1(n1 varchar2) RETURN number; FUNCTION func1(n1 numebr,n2 varchar2) RETURN number; END overload_demo; /*引入重载特性，将dept_pack和包dept_pack2合并定义*/ CREATE OR REPLACE PACKAGE dept_pack3 IS v_num INT; /*向部门表中添加数据，使用记录类型作为过程的参数*/ PROCEDURE add_dept(dept_record dept%ROWTYPE); /*从部门表中移除指定的部门信息*/ PROCEDURE del_dept(p_deptno NUMBER); /*从部门表中查询并返回指定的部门信息*/ FUNCTION get_dept(p_deptno NUMEBR) RETURN dept%ROWTYPE; /*定义基于记录类型的嵌套表*/ TYPE dept_table_type IS TABLE OF dept%ROWTYPE; /*获取所有部门的信息*/ FUNCTION get_dept RETURN dept_table_type; END dept_pack;

构造过程
对包体进行定义时，可以指定构造过程用于初始化包公用变量，它类似于高级语言的构造函数或者构造方法
包的构造过程没有任何名称，它是在实现了包的其他过程和函数之后，以BEGIN开始，以END结束的部分。

/*使用构造过程对dept_pack中的v_num进行初始化*/ CREATE OR REPLACE PACKAGE BODY dept_pack IS /*子过程定义部分省略*/ /*构造过程*/ BEGIN v_num:=5; END dept_pack;

维护包
类似存储过程与函数
触发器

触发器用于定义与数据库有关的某个事件发生时数据库将要执行的操作。触发器分为以下几类语句触发器，在执行DML操作时，将激活该类触发器行触发器，在执行DML操作时，每作用一行，触发一次 INSTEAD OF触发器，该触发器时仅基于在视图上进行DML操作所创建的触发器事件触发器，是基于ORACLE系统事件或客户事件建立并触发，如执行DDL命令，数据库启动关闭

CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|DELETE|UPDATE[OF column [,column...]]} [REFERENCING {OLD [AS] old|NEW[AS]new|PARENT as parent}] ON table_name [FOR EACH ROW] [WHEN condition] trigeer_boby; /* *BEFORE指的是触发事件之前触发当前触发器 AFTER相反 *REFERENCING在行触发器用于指定引用新旧数据的引用名称，缺省情况下分别为OLD，NEW *WHEN子句说明触发约束条件 condition是一个逻辑表达式。WHEN子句指定的的触发约束条件只能用在BEFORE跟AFTER行触发器中 *不能用在INSTEAD OF行触发器等。 */

语句触发器
语句触发器是指基于DML操作所建立的触发器

BEFORE触发器 CREATE OR REPLACE TRIGGER trg_dept BEFORE INSERT UR UPDATE OR DELETE ON dept DECLARE v_now VARCHAR2(30); BEGIN v_now:=TO_CHAR(sysdate,'YYYY-mm-DD hh24:Mi:ss'); CASE WHEN INSERTING THEN--触发器条件谓词 dbms_output.put_line(v_now||'对dept表进行了insert操作'); WHEN UPDATING THEN dbms_output.put_line(v_now||'对dept表进行了update操作'); WHEN DELETING THEN dbms_output.put_line(v_now||'对dept表进行了delete操作'); END CASE; END;

行触发器
行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器。

CREATE OR REPLACE TRIGGER trg_dept_before BEFORE INSERT OR UPDATE OR DELETE ON dept FOR EACH ROW --去掉就是语句触发器 DECLARE v_now VARCHAR2(30); BEGIN v_now:=TO_CHAR(sysdate,'YYYY-mm-DD hh24:Mi:ss'); CASE WHEN INSERTING THEN dbms_output.put_line(v_now||'对dept表进行了insert操作'); dbms_output.put_line('添加数据：'||:new.deptno||','||:new.dname||','||:new.loc); WHEN UPDATING THEN dbms_output.put_line(v_now||'对dept表进行了update操作'); dbms_output.put_line('修改前数据：'||:old.deptno||','||:old.dname||','||:old.loc); dbms_output.put_line('修改后数据：'||:new.deptno||','||:new.dname||','||:new.loc); WHEN DELETING THEN dbms_output.put_line(v_now||'对dept表进行了delete操作'); dbms_output.put_line('删除数据：'||:old.deptno||','||:old.dname||','||:old.loc); END CASE; END;

假如雇员表基于下述序列生成 CREATE SEQUENCE seq_emp INCREMENT BY 1 START WITH 7000 MAXVALUE 9999 NOCYCLE; 向雇员表添加数据时，自动生成雇员编号 CREATE OR REPLACE TRIGGER trg_emp_before BEFORE INSERT ON emp FOR EACH ROW --去掉就是语句触发器 BEGIN SELECT seq_emp.nextval INTO:new.empno FROM DUAL; END; --执行添加数据时候，无需引用序列

AFTER触发器

创建部门表的日志记录表 CREATE TABLE dept_log( uname VARCHAR2(40),--用户 oper_time DATE,--操作时间 oper_type VARCHAR2(10),--操作类别 info VARCHAR2(100)--操作内容 ); 为部门表创建AFTER行触发器 CREATE OR REPLACE TRIGGER trg_dept_delete AFTER INSERT OR DELETE ON dept FOR EACH ROW DECLARE v_info VARCHAR2(100); BEGIN CASE WHEN INSERTING THEN v_info:=:new.deptno||','||:new.dname||','||:new.loc; INSERT INTO dept_log VALUES(user,sysdate,'insert',v_info); WHEN DELETTING THEN v_info:=:new.deptno||','||:new.dname||','||:new.loc; INSERT INTO dept_log VALUES(user,sysdate,'delete',v_info); END CASE; END;

UPDATE OF限制
对于UPDATE操作的触发器，可以使用OF子句限制触发条件。

CREATE OR REPLACE TRIGGER trg_emp_update BEFORE UPDATE OF sal ON emp FOR EACH ROW BEGIN IF:new.sal<:old.sal THEN dbms_output.put_line('职工工资不能降低'); raise_application_error(-20009,'职工工资不能降低'); END IF; END;

WHEN限制

CREATE OR REPLACE TRIGGER trg_dept_delete AFTER INSERT OR DELETE ON dept FOR EACH ROW WHEN(new.deptno>0 AND new.deptno<50) DECLARE v_info VARCHAR2(100); BEGIN CASE WHEN INSERTING THEN v_info:=:new.deptno||','||:new.dname||','||:new.loc; INSERT INTO dept_log VALUES(user,sysdate,'insert',v_info); WHEN DELETTING THEN v_info:=:new.deptno||','||:new.dname||','||:new.loc; INSERT INTO dept_log VALUES(user,sysdate,'delete',v_info); END CASE; END;

维护触发器 【ORACLE|PL/SQL学习笔记】暂略