PL/SQL笔记
PL/SQL是Oracle在标准SQL语言上的过程性扩展。PL/SQL不仅允许嵌入SQL语句，而且允许定义变量和常量，允许使用条件语句和循环语句，允许使用异常处理各种错误，从而提供了更强大的功能。
?
SQL语言分类：
数据查询语言：SELECT
数据操纵语言（Data Manipulation Language，DML）：INSERT、UPDATE、DELETE
数据定义语言（Data Definition Language，DDL）：CREATE、ALTER、DROP。自动提交事务。
数据控制语言（Data Control Language，DCL）：GRANT、REVOKE。自动提交事务。
事务控制语言（Transaction Control Language，TCL）：COMMIT、ROLLBACK、SAVEPOINT
?
分隔符：
%?属性提示符
:?非PL/SQL变量提示符
@?远程数据库访问操作符
:=?赋值操作符
/**/?多行注释开始和结束分隔符。一般分布在多行上，用于说明一段代码的作用
--?单行注释提示符。一般用于说明单行代码的作用
?
当使用标识符定义常量、变量时，每行只能定义一个标识符。
?
可以使用以下方式访问数据库：
1、?SQL*Plus
2、?PL/SQL Developer
其中，SQL*Plus可以在命令行运行。在命令行运行SQL*Plus时使用sqlplus命令来完成。
示例：
sqlplus MyUser/mydb
语法：
sqlplus username/password[@server]
其中：username用于指定数据库用户名，password用于指定用户密码，server用于指
定主机字符串（网络服务名），当连接到本地数据库时，不需要提供网络服务名。
当在客户端使用SQL*Plus连接到远程数据库时，必须先使用Net Manager配置网络服务名，然后使用该网络服务名连接到远程数据库。
?
CALL insertEmp('2','卢杰',5000.00);
%ROWTYPE属性可以基于表或视图定义记录变量，当使用该属性定义记录变量时，记录成员的名称
和类型与表或视图列的名称和类型完全相同，为了简化表或视图所有列数据的处理，应该使用该属性定义
记录变量；而如果只是处理某几列数据，应该使用自定义记录类型和记录变量。
当表的列需要保存多个值时，可以使用VARRAY类型变量。如某个员工有多个联系方式：
CREATE TYPE phone_type IS VARRAY(5) OF VARCHAR2(11);
CREATE TABLE employee(
????? id NUMBER(4),
????? name VARCHAR2(10),
????? sal NUMBER(6,2),
????? phone phone_type
);
?
INSERT INTO employee(id,name,sal,phone)
VALUES(1,'canglang',5000.00,phone_type('15850734289','110','112'));
CREATE OR REPLACE PROCEDURE get_phones(eid NUMBER)
IS
???? phones phone_type;
BEGIN
???? SELECT phone INTO phones FROM emp WHERE id=eid;
???? FOR i IN 1..phones.COUNT LOOP
???????? dbms_output.put_line('员工号码：'||phones(i));
???? END LOOP;
END;
/
EXECUTE get_phones (1);
DECLARE
???? phones PHONE_TYPE:=phone_type('110','112','120');
BEGIN
???? UPDATE emp SET phone=phones WHERE id=1;
END;
/
PL/SQL、存储过程、函数、触发器之间有什么关系？分别给出一个实用、典型示例。
PL/SQL块包括：匿名块、命名块和子程序三种类型。子程序又包括：存储过程、存储函数、包和触发器。这里只介绍匿名块和子程序。
1、?匿名块
示例如下：
SET SERVEROUTPUT ON;
DECLARE
???? ename emp.name%TYPE;
BEGIN
???? SELECT name INTO ename FROM emp WHERE id=&id;
???? dbms_output.put_line('员工姓名：'||ename);
EXCEPTION
???? WHEN NO_DATA_FOUND THEN
???????? dbms_output.put_line('该员工号不存在！');
END;
/
该匿名块实现的功能是根据我们输入的员工号输出员工的姓名，若没该员工号则输出
“'该员工号不存在！”。
因为该PL/SQL块直接以DECLARE开始，没给出名称，所以该PL/SQL块属于匿名块。
2、?子程序
子程序包括存储过程、函数、包和触发器。
（1）存储过程
示例如下：
CREATE OR REPLACE PROCEDURE drop_table_if_exists(tab_name VARCHAR2)
IS
??? table_nums NUMBER(1);
BEGIN
??? SELECT COUNT(1) INTO table_nums FROM user_tables WHERE table_name=
UPPER(tab_name);
??? IF table_nums > 0 THEN
????? EXECUTE IMMEDIATE 'DROP TABLE ' || tab_name || ' CASCADE CONSTRAINTS';
??? END IF;
END;
/
该存储过程实现的是如MySQL中的DROP TABLE IF EXISTS ‘emp’;的功能。
??? 存储过程用于执行特定操作。当建立存储过程时，既可以指定输入参数（IN），也可以指定输出参数（OUT）。通过在存储过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。
在SQL*Plus中，使用EXECUTE命令或CALL命令调用存储过程，如下所示：
EXEC drop_table_if_exists('emp');
CALL drop_table_if_exists('dept');
（2）函数
示例如下：
CREATE OR REPLACE FUNCTION get_name(eid NUMBER) RETURN VARCHAR2
IS
ename VARCHAR2(15);
BEGIN??
??? SELECT name INTO ename FROM emp WHERE id=eid;
??? RETURN ename;
END;
/
该函数实现的是根据员工号返回员工姓名的功能。
??? 函数用于返回特定数据。当建立函数时，在函数头部必须包含RETURN子句，在函数体内必须包含RETURN语句返回数据。
当调用该函数时，可以使用SQL*Plus绑定变量存放输出结果，示例如下：
VAR ename VARCHAR2;
CALL gett_name('0') INTO :ename;
PRINT ename;
（3）包
示例如下：
--包规范
CREATE PACKAGE emp_pkg IS
--注意：这里没有BEGIN！
PROCEDURE drop_table_if_exists(tab_name VARCHAR2);
??? FUNCTION get_ name(eid NUMBER) RETURN VARCHAR2;
END;
/
--包体
CREATE PACKAGE BODY emp_pkg IS
--注意：这里没有BEGIN！
PROCEDURE drop_table_if_exists(tab_name VARCHAR2)
IS
table_nums NUMBER(1);
BEGIN
SELECT COUNT(1) INTO table_nums FROM user_tables WHERE
?table_name=UPPER(tab_name);
??? IF table_nums > 0 THEN
??? ?EXECUTE IMMEDIATE 'DROP TABLE ' || tab_name || ' CASCADE
CONSTRAINTS';
??? END IF;
END;
?????
FUNCTION get_ name(eid NUMBER) RETURN VARCHAR2
IS
ename VARCHAR2(15);
BEGIN
?SELECT name INTO ename FROM emp WHERE id=eid;
??? RETURN ename;
END;
END;
/
--调用包的存储过程
CALL emp_pkg.drop_table_if_exists(‘dept’);
--调用包的函数
VAR ename VARCHAR2;
CALL emp_pkg.get_ name('0') INTO :ename;
??? 包用于逻辑组合相关的存储过程和函数，它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、存储过程和函数。包规范只包含了过程和函数的说明，而没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。当调用包的过程和函数时，在过程和函数名前必须要带有包名作为前缀。
（4）触发器
示例如下：
CREATE OR REPLACE TRIGGER update_cascade
AFTER UPDATE OF name ON dept FOR EACH ROW—注意：这里没有分号！
BEGIN
??? UPDATE emp SET dept_name=:new.name WHERE dept_name=:old.name;
END;
/
UPDATE dept SET name=’ 研发一部’ WHERE name=’ 开发一部’;
COMMIT;
该触发器实现的是当改变dept表的name字段值时，级联更新emp表的dept_name字段值的功能。
Oracle中要实现级联更新只能使用触发器，没有级联更新语法。但有级联删除语法：
ALTER TABLE emp ADD CONSTRAINTS FK_DEPT_EMP FOREIGN KEY(dept_name)
REFERENCES dept(name) ON DELETE CASCADE;
?
当定义触发器时，必须指定触发事件以及触发操作，常用的触发事件包括INSERT、
UPDATE和DELETE语句，而触发操作实际是一个PL/SQL块。
触发器不能显式调用，当满足触发条件时，触发器会被自动触发执行。
存储过程与函数的异同：
存储过程与函数在功能和语法上很相似：
1、功能上：它们都是将一段SQL语句和PL/SQL语句封装在一段代码里，以完成某特定功能，方便多次调用，减少重复劳动，并提高运行效率；
2、语法上：它们都是CREATE OR REPLACE … IS … BEGIN … END;结构。
但它们在这两方面还是有很大的不同：
1、?功能上：函数一般用于返回一个结果，而存储过程一般用于完成特定的功能（如
存储过程drop_table_if_exists）；
2、语法上：
1）函数声明的末尾必须有RETURN语句，在函数体内也必须有RETURN语句；存储过程通过输出参数返回值；
??? 2）函数可以在SQL语句中直接调用，而存储过程必须单独使用。
在PL/SQL程序中有3种循环结构：
1、FOR variable IN low_bound . . upper_bound LOOP
…
END LOOP;
2、LOOP
…
END LOOP;
?3、WHILE condition LOOP
…
END LOOP;
试结合显式游标给这3种循环结构分别给出一个示例。
1、FOR variable IN low_bound . . upper_bound LOOP … END LOOP;
DECLARE
???? CURSOR emp_cursor IS SELECT * FROM emp FOR UPDATE;--为了更新
BEGIN
???? FOR emp IN emp_cursor LOOP
??????? IF emp.sal<5500 THEN
?????????? UPDATE emp SET sal=sal*1.5 WHERE CURRENT OF emp_cursor;--到当前游标
??????? END IF;
???? END LOOP;
END;
/
2、LOOP … END LOOP;
DECLARE
???? CURSOR emp_cursor IS SELECT * FROM emp;
???? emp emp_cursor %ROWTYPE;
BEGIN
???? OPEN emp_cursor;
???? LOOP
???????? FETCH emp_cursor INTO emp;
???????? EXIT WHEN emp_cursor %NOTFOUND;
???????? dbms_output.put_line(emp.name);
???? END LOOP;
???? CLOSE emp_cursor;
END;
/
3、WHILE condition LOOP … END LOOP;
DECLARE
???? CURSOR emp_cursor IS SELECT * FROM emp;
???? emp emp_cursor %ROWTYPE;
BEGIN
???? OPEN emp_cursor;
???? FETCH emp_cursor INTO emp;
???? WHILE emp_cursor %FOUND LOOP
??????? dbms_output.put_line(emp.name);
??????? FETCH? emp_cursor INTO emp;?
???? END LOOP;
???? CLOSE emp_cursor;
END;
/
优先使用第1种，不行的话使用第2种，不准使用第3种。因为第1种语法最简洁，第3种最繁琐。
--从员工号为id1的员工的账户里转出money到员工号为id1的员工的账户里
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE trans_account(id1 VARCHAR2,id2 VARCHAR2,
money NUMBER)
IS
??? e_no_id EXCEPTION;
BEGIN
??? UPDATE emp SET sal=sal-money WHERE id=id1;
??? UPDATE emp SET sal=sal+money WHERE id=id2;
??? IF SQL%NOTFOUND THEN
?????? RAISE e_no_id;
??? ELSE
?????? COMMIT;
?????? dbms_output.put_line('转账成功！');
??? END IF;
EXCEPTION
??? WHEN e_no_id THEN
???????? ROLLBACK;
???????? dbms_output.put_line('转账失败！');
END;
/
CALL trans_account('0','1',1000);
PL/SQL编译警告
PL/SQL警告可分成三类，通过使用不同的警告类型，可以禁止或显示特定警告消息：
1、?SERVER：该类警告用于检查可能出现的不可预料结果或错误结果，例如参数的别名问题；
2、PERFORMANCE：该类警告用于检查可能引起的性能问题；
3、INFORMATIONAL：该类警告用于检查子程序中的死代码；
ALL:该关键字用于检查以上所有警告。
这里只给出性能编译警告的示例：
/*
??? 编写PL/SQL子程序时，如果数值与变量的数据类型不符合，Oracle会隐式地转换数据类型。但数据类型转换会影响子程序的性能，所以在编写PL/SQL子程序时应该尽可能避免性能问题。
*/
--第一步：建立包含隐含数据类型转换的过程
CREATE OR REPLACE PROCEDURE update_sal(eid VARCHAR2,esal NUMBER)
IS
BEGIN
??? UPDATE emp SET sal=esal WHERE id=eid;
END;
/
--第二步：激活警告检查
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
--第三步：编译子程序
ALTER PROCEDURE update_sal COMPILE;
--第四步：使用show errors命令显示警告错误
show errors;
异常处理
Oracle提供了预定义异常、非预定义异常和自定义异常三种异常类型。
1、预定义异常
??? 预定义异常是指由PL/SQL系统提供的异常。当PL/SQL应用程序违反Oracle规则时，会触发预定义异常。为了处理各种常见的Oracle错误，PL/SQL为我们提供了21个预定义异常，每个预定义异常都对应一个Oracle系统错误。如NO_DATA_FOUND异常和TOO_MANY_ROWS异常。PL/SQL程序可以直接调用预定义异常。
2、非预定义异常
使用预定义异常只能处理21个Oracle错误，但在使用PL/SQL开发应用程序时，可能还会遇到其它的一些Oracle错误，这时就需要使用非预定义异常。例如：现在有emp和dept两张表，且emp表的dept_name字段外键引用dept表的name字段，则在修改emp的dept_name字段的值时，该值必须在dept表中存在，若不存在，则会触发非预定义异常。非预定义异常的使用步骤如下所示：
定义异常->关联异常和错误->捕获并处理异常
示例：
DECLARE
???? e_integrity EXCEPTION;--定义异常
???? PRAGMA EXCEPTION_INIT(e_integrity,-2291);--在异常和Oracle错误之间建立关联
BEGIN
???? UPDATE emp SET dept_name=&dname WHERE id=&eid;
EXCEPTION
???? WHEN e_integrity THEN--捕获并处理异常
????????? dbms_output.put_line('该部门不存在！');
END;
/
若给dept_name赋值“a”，将提示“该部门不存在！”。
3、?自定义异常
??? 预定义异常和非预定义异常都与Oracle错误有关，并且出现Oracle错误时会自动触发相应的异常；而自定义异常与Oracle错误没有任何关联，它是由开发人员为特定情况所定义的异常。例如上例中若输入一个不存在的员工号，不会提示任何错误，这对用户是很不友好的，此时应该让程序提示“该员工号不存在！”。为了达到这个目的，需要使用自定义异常。自定义异常的使用步骤如下所示：
定义异常->显式触发异常->捕获并处理异常
示例：
DECLARE
???? e_integrity EXCEPTION;
???? PRAGMA EXCEPTION_INIT(e_integrity,-2291);
???? e_no_emp EXCEPTION;--定义异常
BEGIN
???? UPDATE emp SET dept_name=&dname WHERE id=&eid;
???? IF SQL%NOTFOUND THEN
??????? RAISE e_no_emp;--显式触发异常
???? END IF;
EXCEPTION
???? WHEN e_integrity THEN
????????? dbms_output.put_line('该部门不存在！');
???? WHEN e_no_emp THEN--捕获并处理异常
????????? dbms_output.put_line('该员工号不存在！');
END;
/
若给dept_name赋值“a”，给id赋值“9”，将提示“该员工号不存在！”。
若不是为了集中处理异常，上面的例子还可这样简写：
DECLARE
???? e_integrity EXCEPTION;
???? PRAGMA EXCEPTION_INIT(e_integrity,-2291);
BEGIN
???? UPDATE emp SET dept_name='&dname' WHERE id=&eid;
???? IF SQL%NOTFOUND THEN
??????? dbms_output.put_line('该员工号不存在！');
???? END IF;
EXCEPTION
???? WHEN e_integrity THEN
????????? dbms_output.put_line('该部门不存在！');
END;
/
使用异常函数
??? 当在PL/SQL块中出现Oracle错误时，通过使用异常函数可以取得错误号以及相关的错误消息，其中函数SQLCODE用于取得Oracle错误号，SQLERRM用于取得与之关联的错误消息。另外，在子程序中使用RAISE_APPLICATION_ERROR可以自定义错误号和错误消息。它们在PL/SQL应用程序中处理未预料到的Oracle错误很实用。示例：
BEGIN
???? UPDATE emp SET dept_name=’&dname’ WHERE id=&eid;
???? IF SQL%NOTFOUND THEN
??????? RAISE_APPLICATION_ERROR(-20000,'该员工号不存在！');
???? END IF;
EXCEPTION
???? WHEN OTHERS THEN
????????? dbms_output.put_line(SQLCODE||'->'||SQLERRM);
END;
/
??? 给id赋一个不存在值，提示“-20000->ORA-20000: 该员工号不存在！”；
??? 给id赋一个存在值，但给dept_name赋一个不存在的值，提示“-2291->ORA-02291: 违反完整约束条件 (MYUSER.FK_DEPT_EMP) - 未找到父项关键字”。此时，这两个函数捕获到了开发人员没预料到的错误码和错误信息。
建议在所有子程序的EXCEPTION部分最后加上如下语句：
WHEN OTHERS THEN
??? dbms_output.put_line(SQLCODE||'->'||SQLERRM);
举一个带有输入输出参数的存储过程及其调用示例。
CREATE OR REPLACE PROCEDURE compute(num1 IN OUT NUMBER,num2 IN OUT NUMBER)
IS
??? v_num1 NUMBER;
??? v_num2 NUMBER;
BEGIN
??? v_num1 := num1/num2;
??? v_num2 := MOD(num1,num2);
??? num1 := v_num1;
??? num2 := v_num2;
END;
/
VAR num1 NUMBER;
VAR num2 NUMBER;
EXECUTE :num1 := &num1;
EXECUTE :num2 := &num2;
EXECUTE compute(:num1,:num2);
语句
SELECT * FROM user_source;
用于从user_source表中查看所有子程序的所有信息。
语句
SELECT object_name,object_type FROM user_objects GROUP BY object_type,object_name;
用于查询当前用户的所有对象的信息。
函数
1、不带参函数
CREATE OR REPLACE FUNCTION get_user RETURN VARCHAR2
IS
??? v_user VARCHAR2(20);
BEGIN
??? SELECT username INTO v_user FROM user_users;
??? RETURN v_user;
END;
/
--使用变量接收函数返回值
VAR v_user VARCHAR2(20);
EXECUTE :v_user := get_user;
--在SQL语句中直接调用函数
SELECT get_user FROM dual;
2、带参函数
建立函数时，不仅可以指定IN参数，还可以指定OUT参数和IN OUT参数。
CREATE OR REPLACE FUNCTION compute_ func(num1 NUMBER,num2 IN OUT NUMBER)
RETURN NUMBER
IS
??? v_num1 NUMBER;
??? v_num2 NUMBER;
BEGIN
??? v_num1 := num1/num2;
??? v_num2 := MOD(num1,num2);
??? num2 := v_num2;
??? RETURN v_num1;
EXCEPTION
??? WHEN zero_divide THEN
??????? raise_application_error(-20000,'除数不能为0！');
END;
/
VAR num1 NUMBER;
VAR num2 NUMBER;
EXECUTE :num1 := &num1;
EXECUTE :num2 := &num2;
EXECUTE :num1 := fun_compute(:num1,:num2);
因为该函数带有IN OUT参数，所以不能直接在SQL语句中调用该函数，否则出错：
SQL> SELECT compute_ func (:num1,:num2) FROM dual;
SELECT compute_ func (:num1,:num2) FROM dual
ORA-06572: 函数 COMPUTE_ FUN 具有输出参数
尽量不要写带有OUT参数或IN OUT参数的函数！
当修改了被引用对象的结构时，就会将相关依赖对象转变为无效（INVALID）状态。例如：
1、表结构修改之前，emp表的所有依赖对象的状态
SELECT object_name,object_type,status
FROM user_objects
WHERE object_name IN (SELECT name
????????????????????? FROM user_dependencies
????????????????????? WHERE referenced_name='EMP')
ORDER BY object_type,object_name;
2、修改emp表的结构
ALTER TABLE emp MODIFY name VARCHAR2(16);
3、再次查看表结构修改之后emp表的所有依赖对象的状态
对比表结构修改前后的依赖对象的status,可以发现很多变成了无效状态。（但为什么有的还是有效状态？）
此时为了避免这些对象（子程序和视图）运行错误，应该重新编译这些对象。示例如下：
ALTER FUNCTION get_name COMPILE;
包用于逻辑组合相关的PL/SQL类型（例如TABLE类型和RECORD类型）、PL/SQL项（例如游标和游标变量）和PL/SQL子程序（例如过程和函数）。通过使用PL/SQL包，不仅简化了应用设计，提高了应用性能，而且还能实现信息隐藏、子程序重载等功能。
??? 包规范实际是包与应用程序之间的接口，它用于定义包的公用组件，包括常量、变量、游标、过程和函数等。在包规范中定义的公用组件不仅可以在包内引用，还可以由其它子程序引用。建立包规范时，为了实现信息隐藏，不应将所有组件都放在包规范处定义，而应只定义公用组件。在包体中定义的组件只能在包内使用。包体名称必须与包规范名称相同。
※包的重载：
定义包时，使用重载功能，可以使用不同参数调用同名组件。例如我们希望既可以通过员工号，也可以通过员工名来查询员工的薪资，此时就可以使用包的重载功能。包的重载需符合3个条件：1、同名；2、不同参；（指参数个数或类型不同，只是参数名不同不称为不同参）3、返回值的数据类型完全相同。示例如下：（这里只给出包规范）
CREATE OR REPLACE PACKAGE get_sal_pkg
IS
??? FUNCTION get_sal(eid NUMBER) RETURN NUMBER;
??? FUNCTION get_sal(ename VARCHAR2) RETURN NUMBER;
END;
/
※包的构造过程
??? 在包中定义了全部变量之后，会话中可能还需要初始化全部变量，此时可使用包的构造过程，这类似于java语言中的构造方法。在会话内第一次调用包的公用组件时，会自动执行其构造过程，并且该构造过程在同一会话内只会执行一次。下面以插入新员工的信息和更新员工薪资时其薪资必须在现有员工的最低薪资与最高薪资之间为例说明如何使用包的构造函数：
CREATE OR REPLACE PACKAGE sal_pkg IS
min_sal NUMBER(8,2);
??? max_sal NUMBER(8,2);
???
PROCEDURE insert_emp(eid NUMBER,ename VARCHAR2,esal NUMBER,
ecomm NUMBER,ejob VARCHAR2,dname VARCHAR2);
??? PROCEDURE update_sal(eid NUMBER,esal NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY sal_pkg IS
PROCEDURE insert_emp(eid NUMBER,ename VARCHAR2,esal NUMBER,
ecomm NUMBER,ejob VARCHAR2,dname VARCHAR2)
??? IS
??? BEGIN
??????? --dbms_output.put_line(min_sal||','||max_sal);
??????? IF esal BETWEEN min_sal AND max_sal THEN
??????????? INSERT INTO emp(id,name,sal,comm,job,dept_name)
VALUES(eid,ename,esal,ecomm,ejob,dname);
??????????? COMMIT;
??????? ELSE
??????????? raise_application_error(-20000,'薪资必须在最低与最高之间，
插入失败！');
???? END IF;
EXCEPTION
???? WHEN DUP_VAL_ON_INDEX THEN
???????? raise_application_error(-20001,'?该员工号已存在！');
END;
PROCEDURE update_sal(eid NUMBER,esal NUMBER)
??? IS
??? BEGIN
??????? IF esal BETWEEN min_sal AND max_sal THEN
??????????? UPDATE emp SET sal=esal WHERE id=eid;
??????????? IF SQL%NOTFOUND THEN
??????????????? raise_application_error(-20002,'?该员工号不存在！');
??????????? ELSE
??????????????? COMMIT;
??????????? END IF;
??????? ELSE
raise_application_error(-20003, '薪资必须在最低与最高值间，修改失败！');
??????? END IF;
END;
--包的构造过程
BEGIN
??? ?SELECT min(sal),max(sal) INTO min_sal,max_sal FROM emp;
END;
/
由上可知，包的构造过程没有任何名称，它是在实现了包的其它子程序之后，以BEGIN
开始、END结束的部分。
?
??? 触发器分为语句触发器（默认）和行触发器。语句触发器只会执行一次触发器代码；行触发器会在每个被作用行上执行一次触发器代码。
1、?语句触发器示例
--不允许在周四、五修改emp表
CREATE OR REPLACE TRIGGER update_emp_trig
BEFORE INSERT OR UPDATE OR DELETE ON emp—注意：这里没有分号！
BEGIN
??? IF to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('THU','FRI') THEN
??????? CASE
??????????? WHEN INSERTING THEN
??????????????? raise_application_error(-20000,'不能在周四、五添加员工信息！');
??????????? WHEN UPDATING THEN
??????????????? raise_application_error(-20001,'不能在周四、五修改员工信息！');
??????????? WHEN DELETING THEN
??????????????? raise_application_error(-20002,'不能在周四、五删除员工信息！');
??????? END CASE;
??? END IF;
END;
/
2、?行触发器示例
前面举过一个修改dept表的部门名后触发级联修改emp表的dept_name字段值的AFTER行触发器示例。这里再举一个非常实用的例子：当给员工的涨薪时，会自动将该员工的员工号、员工名、涨薪前薪资、涨薪后薪资和修改日期记录下来，以备后查。
CREATE TABLE echange(
??? id NUMBER(5),--被涨薪员工的员工号?????
??? name VARCHAR2(10),
??? old_sal NUMBER(8,2),
??? new_sal NUMBER(8,2),
??? update_time DATE
);
ALTER TABLE echange ADD CONSTRAINTS PK_ECHANGE PRIMARY KEY(id);
ALTER TABLE echange ADD CONSTRAINTS FK_EMP_ECHANGE FOREIGN KEY(id) REFERENCES
emp(id);
CREATE OR REPLACE TRIGGER sal_change_trig
AFTER UPDATE OF sal ON emp FOR EACH ROW
DECLARE
??? v_temp INT;
BEGIN
??? SELECT count(*) INTO v_temp FROM echange WHERE id=:old.id;
??? IF v_temp=0 THEN
??????? INSERT INTO echange(id,name,old_sal,new_sal,update_time)
VALUES(:old.id,:old.name,:old.sal,:new.sal,SYSDATE);
??? ELSE
??????? UPDATE echange SET name=:old.name,old_sal=:old.sal,new_sal=:new.sal,
update_time=SYSDATE WHERE id=:old.id;
??? END IF;???
END;
/
DML触发器注意事项
当编写DML触发器时，在触发器的代码中不能读取其依赖的基表的数据，这在建立触发器时不会出现任何错误，但在执行触发操作时会显示错误信息。如：
--新员工的薪资不能高于上限
CREATE OR REPLACE TRIGGER check_sal_trig
BEFORE UPDATE OF sal ON emp FOR EACH ROW
DECLARE
??? v_maxsal NUMBER(8,2);
BEGIN
??? SELECT max(sal) INTO v_maxsal FROM emp;
??? IF :new.sal>v_maxsal THEN
???????? raise_application_error(-20001,'新员工的薪资不能高于上限！');?????????????
??? END IF;
END;
/
建立如上触发器时不会显示任何错误，但执行如下UPDATE操作：
UPDATE emp SET sal=9000.00 WHERE id=4;
后，会显示如下错误信息：
UPDATE emp SET sal=9000.00 WHERE id=4
ORA-04091: 表 MYUSER.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "MYUSER.CHECK_SAL_TRIG", line 4
ORA-04088: 触发器 'MYUSER.CHECK_SAL_TRIG' 执行过程中出错
INSTEAD OF触发器
对于简单视图，可以直接执行DML操作，但对于复杂视图，则不允许。如：
CREATE OR REPLACE VIEW emp_dept_view AS
??? SELECT e.id,e.name,e.sal,e.comm,e.job,d.name AS dname,d.mgr_name
??? FROM emp e,dept d
??? WHERE e.dept_name=d.name;
下面对如上复杂视图执行INSERT操作：
INSERT INTO emp_dept_view(id,name,sal,comm,job,dname,mgr_name)
VALUES(2,'老胖子',5000.00,380.00,'经理','人事部','老胖子');
报如下错：
ORA-01776: 无法通过联接视图修改多个基表
此时，使用INSTEAD OF触发器可以解决这个问题。
CREATE OR REPLACE TRIGGER emp_dept_trig
INSTEAD OF INSERT ON emp_dept_view FOR EACH ROW
DECLARE
??? v_num INT;
BEGIN
??? SELECT count(*) INTO v_num FROM emp WHERE id=:new.id;
??? IF v_num=0 THEN
??????? INSERT INTO emp(id,name,sal,comm,job,dept_name)
??????????? VALUES(:new.id,:new.name,:new.sal,:new.comm,:new.job,:new.dname);
??? END IF;
???
??? SELECT count(*) INTO v_num FROM dept WHERE name=:new.name;
??? IF v_num=0 THEN
??????? INSERT INTO dept(name,mgr_name)
??????????? VALUES(:new.dname,:new.mgr_name);
??? END IF;
END;
/
再执行INSERT操作：
INSERT INTO emp_dept_view(id,name,sal,comm,job,dname,mgr_name)
??? VALUES(2,'老胖子',5000.00,380.00,'经理','人事部','老胖子');
分别在表emp和表dept中插入了一条记录。
说明：
1、? 所谓复杂视图，是指符合以下情况之一的视图：
1）? 具有连接查询；
2）? 具有集合操作符（UNION,UNION ALL,INTERSECT,MINUS）
3）? 具有GROUP BY,CONNECT BY或START WITH等字句；
4）? 具有DISTINCT关键字；
5）? 具有分组函数（MIN,MAX,SUM,AVG,COUNT等）。
2、? 创建INSTEAD-OF触发器注意事项：
1）? 这种触发器只能用于视图；
2）? 必须指定FOR EACH ROW选项；
3）? 不能指定BEFORE和AFTER选项；
4）? 不能指定WITH OPTION选项。
3、?视图：
视图是逻辑表，没有任何数据，它只是一条SELECT语句，没有表结构和数据。
?
禁止触发器：
ALTER TRIGGER emp_dept_trig DISABLE;?
激活触发器：
ALTER TRIGGER emp_dept_trig ENABLE;
--禁止表上的所有触发器：
ALTER TABLE dept DISABLE ALL TRIGGERS;?
--激活表上的所有触发器：
ALTER TABLE dept ENABLE ALL TRIGGERS;
使用ALTER TABLE命令修改表结构后，会使依赖于该表的触发器转变为INVALID状态，
为使触发器继续生效，需重新编译触发器：
ALTER TRIGGER update_cascade COMPILE;
删除触发器：
DROP TRIGGER update_cascade;
当前用户为MyUser,如下语句可以切换到sys用户：
CONN SYS/mydb AS SYSDBA;
--授予用户MyUser2对表MyUser.emp增删改查的权限
GRANT SELECT,UPDATE,DELETE,INSERT ON MyUser.emp TO Myuser2;
--收回授予给用户MyUser2的对表MyUser.emp增删改查的权限
REVOKE SELECT,UPDATE,DELETE,INSERT ON MyUser.emp FROM MyUser2;
对象类型
面向对象的程序设计思想正在逐步取代面向过程的程序设计思想，PL/SQL也不例外。
在PL/SQL中，面向对象的程序设计思想是基于对象类型来完成的。
??? 对象类型是用户自定义的一种复合数据类型，它封装了数据结构和用于操纵这些数据结构的过程和函数。在建立复杂应用程序时，通过使用对象类型可以降低应用开发难度，进而提高应用开发的效率和速度。
对象类型包括属性和方法。其中，属性用于描述对象所具有的特征；方法用于实现对象所执行的操作。例如，员工对象类型emp_type应该具有员工号、姓名、薪资、职位以及部门号等属性；还应该具有用于调整职位、薪资和部门的方法。
??? 对象类型包括对象类型规范和对象类型体两个部分。其中对象类型规范是对象与应用的接口，用于定义对象的公用属性和方法；对象类型体用于实现对象类型规范所定义的公用方法。但对象类型体不是必须的，若对象类型规范没有包含公用方法则不需要定义对象类型体。所以，对象类型和包的结构及其各部分的功能完全相同。
示例：
CREATE OR REPLACE TYPE addr_type AS OBJECT(
??? province VARCHAR2(10),
??? city VARCHAR2(10),
??? street VARCHAR2(50),
??? zipcode VARCHAR2(6),
??? MEMBER FUNCTION get_addr_info RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY addr_type IS
--注意：这里与包规范一样没有BEGIN！
??? MEMBER FUNCTION get_addr_info RETURN VARCHAR2
??? IS
??? BEGIN
??????? RETURN province||city||street||zipcode;
??? END;
END;
/
CREATE OR REPLACE TYPE person_type AS OBJECT(
??? name VARCHAR2(10),
??? gender VARCHAR2(2),
??? birth DATE,
??? addr ADDR_TYPE,
??? MEMBER FUNCTION get_person_info RETURN VARCHAR2,
??? ORDER MEMBER FUNCTION get_age_diff(p PERSON_TYPE) RETURN INT
);
/
CREATE OR REPLACE TYPE BODY person_type IS
??? MEMBER FUNCTION get_person_info RETURN VARCHAR2
??? IS
??? BEGIN
??????? RETURN '姓名：'||name||'，性别：'||gender||'，生日：'
||to_char(birth,'yyyy/mm/dd')||'，地址：'||addr.get_addr_info();
??? END;
???
??? ORDER MEMBER FUNCTION get_age_diff(p PERSON_TYPE) RETURN INT
??? IS
??? BEGIN
??????? RETURN trunc((birth-p.birth)/365);
??? END;
END;
/
CREATE TABLE emp_tab(
??? id VARCHAR2(10),
??? person PERSON_TYPE,
??? sal NUMBER(8,2),
??? job VARCHAR2(10)
);
BEGIN
INSERT INTO emp_tab(id,sal,job,person) VALUES('0',5000.00,'JAVA工程师',
person_type('倪美俭','男',to_date('1987/01/20','yyyy/mm/dd'),
addr_type('江苏省','南京市','软件大道66号','210012')));
INSERT INTO emp_tab(id,sal,job,person) VALUES('1',6000.00,'C++工程师',
person_type('春哥','男',to_date('1989/01/20','yyyy/mm/dd'),
addr_type('江苏省','南京市','软件大道66号','210012')));
??? COMMIT;
END;
/
SET SERVEROUTPUT ON;
DECLARE
??? person1 emp_tab.person%TYPE;
??? person2 emp_tab.person%TYPE;
??? age_diff INT;
BEGIN
??? SELECT person INTO person1 FROM emp_tab WHERE id='0';
??? SELECT person INTO person2 FROM emp_tab WHERE id='1';
??? dbms_output.put_line('0号员工人员信息为：'||person1.get_person_info());
??? dbms_output.put_line('1号员工人员信息为：'||person2.get_person_info());
??? age_diff:=person1.get_age_diff(person2);
??? dbms_output.put_line('两人相差'||age_diff||'岁');
END;
/
--更新表记录
DECLARE
??? v_person emp_tab.person%TYPE;
??? eid emp.id%TYPE:=’&id’;
BEGIN
??? SELECT person INTO v_person FROM emp_tab WHERE id=eid;
??? v_person.addr.street:='&street';
??? UPDATE emp_tab SET person=v_person WHERE id=eid;
??? COMMIT;
END;
/
--删除表记录
BEGIN
??? DELETE FROM emp_tab WHERE id='&id';
END;
/
说明：
1、?STATIC方法与MEMBER方法：
STATIC方法只能由对象类型访问，不能由对象实例访问。MEMBER方法与之相反。
2、?MAP方法与ORDER方法：
??? 对象类型不能直接进行比较，例如对象类型person_type有多个属性，每个属性又具有特定的数据类型，所以无法直接进行比较。为了按照特定规则排序对象实例，可以在对象实例上定义MAP/ORDER方法，它们可以将对象实例映射为标量类?型数据（DATE,NUMBER或VARCHAR2等），然后根据该标量类型数据排序对象实例。但注意，对象类型最多只能定义一个MPA/ORDER方法。
使用MAP方法和ORDER方法的原则如下：
1、?如果应用程序不需要比较对象实例，则不需要定义MAP和ORDER方法；
2、?如果应用程序需要比较多个对象实例，则选择MAP方法；
3、?若果应用程序只需比较两个对象实例，则选择ORDER方法。
对象类型继承
对象类型继承是指一个对象类型继承另一个对象类型。对象类型继承有父类型和子类型继承。其中，父类型用于定义不同对象类型的公有属性和方法；子类型不仅继承了父类型的公有属性和方法，还可有自己的私有属性和方法。
示例：
--建立父对象类型
CREATE OR REPLACE TYPE person_type AS OBJECT(
??? name VARCHAR2(10),
??? gender VARCHAR2(2),
??? birth DATE,
??? addr VARCHAR2(50),
??? MEMBER FUNCTION get_person_info RETURN VARCHAR2
)NOT FINAL;
/
CREATE OR REPLACE TYPE BODY person_type IS
??? MEMBER FUNCTION get_person_info RETURN VARCHAR2
??? IS
??? BEGIN
??????? RETURN '姓名：'||name||'，性别：'||gender||'，生日：'||
to_char(birth,'yyyy/mm/dd')||'，住址：'||addr;
??? END;
END;
/
--建立子对象类型
CREATE OR REPLACE TYPE emp_type UNDER person_type(
??? id VARCHAR2(10),
??? sal NUMBER(8,2),
??? job VARCHAR2(10),
??? MEMBER FUNCTION get_emp_info RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY emp_type IS
??? MEMBER FUNCTION get_emp_info RETURN VARCHAR2
??? IS
??? BEGIN
??????? RETURN '员工号：'||id||'，薪资：'||sal||'，职位：'||job;
??? END;
END;
/
CREATE TABLE emp_tab OF emp_type;
BEGIN
INSERT INTO emp_tab(name,gender,birth,addr,id,sal,job) VALUES('倪美俭','男',
to_date('1987/01/20','yyyy/mm/dd'),'南京市软件大道66号','0',5000.00,
'JAVA工程师');
??? COMMIT;
END;
/
DECLARE
??? v_emp EMP_TYPE;
BEGIN
??? SELECT value(e) INTO v_emp FROM emp_tab e WHERE e.id=&id;
??? dbms_output.put_line(v_emp.get_person_info()||','||v_emp.get_emp_info());
END;
/
?
LOB变量
LOB（Large Object）是专门用于处理大对象（如大文本、图形/图像、视频等）的一种数据类型，其所存放的数据长度可以达到4G字节。Oracle将LOB分为两种：内部LOB和外部LOB。内部LOB包括CLOB、BLOB和NLOB三种类型，它们的数据被存储在数据库中，并且支持事务操作（提交、回滚、保存点）。外部LOB只有BFILE一种类型，该类型的数据被保存在OS文件中，并且不支持事务操作，BFILE所对应的OS文件内容只能读取，不能修改。其中，CLOB/NCLOB用于存储大批量字符数据，BLOB用于存储大批量二进制数据，BFILE用于存储指向OS文件的指针。在Oracle的早期版本中，存放大对象是使用LONG、LONG RAW来实现的，但这两种数据类型将被逐渐淘汰，Oracle建议使用LOB类型取代它们。
/*
这个示例用于演示如何用PL/SQL向数据库中：
1、将文本插入到数据库CLOB类型的字段中；将数据库CLOB类型的值读取到文本中。
2、将图片插入到数据库BLOB类型的字段中；将数据库BLOB类型的值读取到图片中。
*/
CREATE TABLE lob_tab(
??? id NUMBER(4),
??? clob_loc CLOB,
??? blob_loc BLOB,
??? bfile_loc BFILE
);
ALTER TABLE lob_tab ADD CONSTRAINTS PK_LOB_TAB PRIMARY KEY(id);
/*
使用utl_file包（Oracle系统包）访问OS文件时，需先为OS目录（该目录必须已存在）
建立相应的DERECTORY对象，然后赋予当前用户读写该DIRECTORY对象的权限。
*/
conn sys/mydb as sysdba;
CREATE OR REPLACE DIRECTORY dir AS 'F:\ lob_test;
GRANT READ,WRITE ON DIRECTORY dir TO MyUser;
/*
?将文本内容插入到数据库CLOB类型的字段中
*/
CREATE OR REPLACE PROCEDURE update_clob(eid NUMBER,dir VARCHAR2,
filename VARCHAR2)
IS
??? clob_loc CLOB;
??? bfile_loc BFILE;
??? amount INT;
??? src_offset INT:=1;
??? dest_offset INT:=1;
??? csid INT:=0;
??? lc INT:=0;
??? warning INT;
BEGIN
??? UPDATE lob_tab SET clob_loc=empty_clob() WHERE id=eid;
??? SELECT clob_loc INTO clob_loc FROM lob_tab WHERE id=eid FOR UPDATE;
??? bfile_loc:=bfilename(upper(dir),filename);
??? dbms_lob.open(bfile_loc,dbms_lob.file_readonly);
??? amount:=dbms_lob.getlength(bfile_loc);
??? dbms_lob.loadclobfromfile(clob_loc,bfile_loc,amount,dest_offset,src_offset,csid,lc,
warning);
??? dbms_lob.close(bfile_loc);
??? COMMIT;
EXCEPTION
??? WHEN NO_DATA_FOUND THEN
??????? dbms_output.put_line('该id不存在，修改失败！');
END;
/
EXECUTE update_clob(2,'tmp','clob2.txt');
/*
??? 将数据库CLOB类型的值读取到文本中
*/
CREATE OR REPLACE PROCEDURE read_clob(eid NUMBER,dir VARCHAR2,
filename VARCHAR2)
IS
??? clob_loc CLOB;
??? amount INT;
??? offset INT:=1;
??? buffer VARCHAR2(2000);
??? handle utl_file.FILE_TYPE;
BEGIN
??? SELECT clob_loc INTO clob_loc FROM lob_tab WHERE id=eid;
??? amount:=dbms_lob.getlength(clob_loc);
??? dbms_lob.read(clob_loc,amount,offset,buffer);
??? handle:=utl_file.fopen(dir,filename,'w',2000);
??? utl_file.put_line(handle,buffer);
??? utl_file.fclose(handle);
END;
/
EXECUTE read_clob(1,'TMP','clob3.txt');
/*
??? 将图片插入到数据库BLOB类型的字段中
*/
CREATE OR REPLACE PROCEDURE update_blob(eid NUMBER,dir VARCHAR2,filename VARCHAR2)
IS
??? blob_loc BLOB;
??? bfile_loc BFILE;
??? amount INT;
??? dest_offset INT:=1;
??? src_offset INT:=1;
BEGIN
??? UPDATE lob_tab SET blob_loc=empty_blob() WHERE id=eid;
??? SELECT blob_loc INTO blob_loc FROM lob_tab WHERE id=eid FOR UPDATE;
??? bfile_loc:=bfilename(upper(dir),filename);
??? dbms_lob.open(bfile_loc,dbms_lob.file_readonly);
??? amount:=dbms_lob.getlength(bfile_loc);
??? dbms_lob.loadblobfromfile(blob_loc,bfile_loc,amount,dest_offset,src_offset);
??? dbms_lob.close(bfile_loc);
??? COMMIT;
EXCEPTION
??? WHEN NO_DATA_FOUND THEN
??????? dbms_output.put_line('该id不存在，修改失败！');
END;
/
EXECUTE update_blob(1,'tmp','blob2.jpg');
/*
将数据库BLOB类型的值读取到图片中
当BLOB字段值大于32767字节时，要循环读取
??? 下面这段代码执行后，生成的图片有问题！
*/
DECLARE
??? blob_loc BLOB;
??? amount INT;
??? offset INT:=1;
??? buffer RAW(32767);
??? handle utl_file.FILE_TYPE;
???
BEGIN
??? SELECT blob_loc INTO blob_loc FROM lob_tab WHERE id=1;
??? amount:=dbms_lob.getlength(blob_loc);
??? --dbms_output.put_line('amount->'||amount);
??? handle:=utl_file.fopen('TMP','test5.jpg','w',32767);
??? dbms_lob.read(blob_loc,amount,offset,buffer);
??? utl_file.put_raw(handle,buffer);
??? utl_file.fclose(handle);
END;
/
/*
将文本/图片插入到数据库BFILE类型的字段中
*/
INSERT INTO lob_tab(id,bfile_loc) VALUES(2,bfilename('TMP','clob.txt'));
INSERT INTO lob_tab(id,bfile_loc) VALUES(3,bfilename('TMP','blob2.jpg'));
/*
?更新数据库BFILE类型的字段值
*/
UPDATE lob_tab SET bfile_loc=bfilename('TMP','clob.txt') WHERE id=1;
UPDATE lob_tab SET bfile_loc=bfilename('TMP','blob2.jpg') WHERE id=1;
/*
?将数据库BFILE类型的值读取到文本/图片中
*/
CREATE OR REPLACE PROCEDURE read_bfile(eid NUMBER,dir VARCHAR2,
filename VARCHAR2)
IS
??? bfile_loc BFILE;
??? amount INT;
??? offset INT:=1;
??? buffer RAW(32767);
??? handle utl_file.FILE_TYPE;
BEGIN
??? SELECT bfile_loc INTO bfile_loc FROM lob_tab WHERE id=eid;
??? dbms_lob.fileopen(bfile_loc, dbms_lob.file_readonly);
??? amount:=dbms_lob.getlength(bfile_loc);
??? dbms_lob.read(bfile_loc,amount,offset,buffer);
??? dbms_lob.fileclose(bfile_loc);
??? handle:=utl_file.fopen(upper(dir),filename,'w',32767);
??? utl_file.put_raw(handle,buffer);
??? utl_file.fclose(handle);
END;
/
EXECUTE read_bfile(1,'TMP','clob2.txt');
补充说明：
Oracle系统包之utl_file包
包utl_file用于读写OS文件。
下面介绍该包所提供的常用类型、过程和函数。
1、FILE_TYPE
该类型是utl_file包中所定义的记录类型。
其定义如下：
TYPE file_type IS RECORD(
id BINARY_INTEGER,
datatype BINARY_INTEGER
);
2、fopen
该函数用于打开OS文件。
示例：
handle:=utl_file.fopen('TMP','test5.jpg','w',32767);
语法：
utl_file.fopen(
dir IN VARCHAR2,
filename IN VARCHAR2,
???? open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER
) RETURN file_type;
成功执行该函数，会返回文件的句柄（handle），操作该句柄就可操作该文件。
3、fclose
该过程用于关闭已经打开的文件
4、put_raw
该过程用于将RAW缓冲区的数据写入到OS文件。
示例：
utl_file.put_raw(handle,buffer);
语法：
utl_file.put_raw(
handle IN utl_file.file_type,
buffer IN RAW,
auto_flush IN BOOLEAN DEFAULT FALSE
);
其中auto_flush用于指定是否要自动刷新缓冲区的数据。
5、put_line
该过程用于将文本缓冲区内容写入到文件中。
示例：
utl_file.put_line(handle,buffer);
语法：
utl_file.put_line(
handle IN utl_file.file_type,
buffer IN VARCHAR2,
auto_flush IN BOOLEAN DEFAULT FALSE
);
?
