PL/SQL之高级篇
原文地址:http://www.cnblogs.com/sin90lzc/archive/2012/08/30/2661117.html
参考文献:《Oracle完全学习手册》
1.概述
本文主要介绍PL/SQL中的有名程序块:存储过程、函数、包头/包体及触发器的使用。而这些的基础是PL/SQL无名块的编写,这在PL/SQL之基础篇 中已经讲述过了。
这四种程序块到底是什么东西呢?
存储过程可以认为它是一个无返回值的函数(排除参数返回);
函数则是一个带返回值的函数,但函数只能用于表达式中,不能像存储过程那样直接调用;
包头/包体与Java中的接口/实现类比较的话,包头就好比接口,而包体就好比实现类。
触发器则可以认为是一个事件处理函数,它能在某个事件发生时自动地执行。
2.存储过程
2.1存储过程的创建及修改
语法:
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] datatype [{(:= | DEFAULT ) defaultvalue}] [, ...])] {IS | AS} BEGIN procedure_body END procedure_name;
语法解析:
IN:输入参数;
OUT:输出参数;
IN OUT:即可输入也可输出的参数;
datatype:参数的数据类型,此处不能带精度。
:=|DEFAULT:用于设置参数的默认值。
Example:
--注意,在声明参数类型时,不能带精度,如VARCHAR2(20)后面的(20) CREATE OR REPLACE PROCEDURE test_procedure(name IN VARCHAR2 default 'Tim',age IN NUMBER :=20,sex IN OUT VARCHAR2,realname OUT VARCHAR2) IS rn VARCHAR2(10) default 'Tim Leung'; BEGIN IF name='Tim' THEN realname:=rn; END IF; IF sex='1' THEN sex:='男'; ELSE sex:='女'; END IF; DBMS_OUTPUT.PUT_LINE(name || ' age:'||age||' sex:'||sex||' realname:'||realname); END test_procedure;
2.2 过程的调用
declare result_sex varchar(3):='1'; result_rn varchar(10) default null; begin test_procedure('Tim',sex => result_sex,age => 20,realname => result_rn);--可以使用符号=>来传参 DBMS_OUTPUT.PUT_LINE('sex:'||result_sex); DBMS_OUTPUT.PUT_LINE('realname:'||result_rn); end; /*运行结果: Tim age:20 sex:男 realname:Tim Leung sex:男 realname:Tim Leung */
2.3 删除过程
DROP PROCEDURE test_procedure;
3.函数
过程用来完成一项任务,可能不返回值,也可能返回多个值,过程的调用是一条PL/SQL语句;函数包含RETURN子句,用来进行数据操作,并返回一个单独的函数值,函数的调用只能在一个表达式中。
3.1创建及修改函数
语法:
CREATE [OR REPLACE] FUNCTION function_name [(parameter1 {IN | OUT | IN OUT} datatype] [,...])] RETURN datatype { IS | AS } BEGIN function_body END function_name;
Example:
CREATE OR REPLACE FUNCTION test_function(name IN VARCHAR2 default 'Tim',age IN NUMBER :=20,sex IN OUT VARCHAR2,realname OUT VARCHAR2) RETURN VARCHAR2 IS rn VARCHAR2(10) default 'Tim Leung'; BEGIN IF name='Tim' THEN realname:=rn; END IF; IF sex='1' THEN sex:='男'; ELSE sex:='女'; END IF; RETURN (name || ' age:'||age||' sex:'||sex||' realname:'||realname); END test_function;
3.2调用函数
declare result_sex varchar(3):='1'; result_rn varchar(10) default null; begin --可以使用符号=>来传参 DBMS_OUTPUT.PUT_LINE(test_function('Tim',sex => result_sex,age => 20,realname => result_rn)); end; /*运行结果: Tim age:20 sex:男 realname:Tim Leung */
4.包头/包体
4.1包头创建及修改
语法:
CREATE [OR REPLACE] PACKAGE package_name { IS | AS } package_specification --存储过程,函数,变量声明部分 END package_name;
Example:
CREATE OR REPLACE PACKAGE test_package IS v_name VARCHAR2(20);--声明公有变量 PROCEDURE getName(id NUMBER);--声明过程getName PROCEDURE getName(p_name VARCHAR2);--声明重载过程getName FUNCTION getAge RETURN NUMBER;--声明函数getAge END test_package;
4.2包体的创建及修改
语法:
--包体名必须与包头名一致! CREATE [OR REPLACE] PACKAGE BODY package_name { IS | AS} package_implement --存储过程,函数实现部分 BEGIN package_init --包初始化部分,在包被第一次调用时执行 END; --注意!!!包体不能有END;子句
Example:
CREATE OR REPLACE PACKAGE BODY test_package IS --包体名与包头名要一致 /* 创建私有过程output,以供其他过程或函数调用,必须在其他过程或函数调用前创建 */ PROCEDURE output(output_str VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(output_str); END output; /* 创建公有过程getName */ PROCEDURE getName(id NUMBER) IS BEGIN output('do getName('||id||')'); END getName; /* 重载过程getName,重载的意思即有相同的过程名或函数名,相同的返回类型,不同的参数列表! */ PROCEDURE getNAME(p_name VARCHAR2) IS BEGIN output('do getNAME('||p_name|| ')!'); END getNAME; /* 创建函数getAge */ FUNCTION getAge RETURN NUMBER IS BEGIN RETURN 20; END getAge; /* 包的初始化块,在包被第一次调用时执行。初始化块只能在包体的末尾编写 */ BEGIN v_name:='Tim'; END; --注意!!包体不能有END;子句
4.3 包的调用
declare begin DBMS_OUTPUT.PUT_LINE(test_package.v_name); test_package.getName(10); test_package.getName('Tim'); DBMS_OUTPUT.PUT_LINE('getAge:' || test_package.getAge); end; /*运行结果: Tim do getName(10) do getNAME(Tim)! getAge:20 */
5.触发器
5.1触发器的分类
触发器主要有DML触发器、替代触发器、系统触发器及DDL触发器几种类型。
- DML触发器
DML触发器可以处理INSERT、UPDATE和DELETE事件。DML触发器可以在语句级或行级操作上被触发,语句级触发器对于每一个SQL语句只触发一次,行级触发器对SQL语句受影响的表中的每一行都触发一次 - 替代触发器
替代触发器主要针对处理视图的DML操作事件(INSERT、UPDATE和DELETE)。替代触发器只能在行级操作上被触发,不能在语句级上被触发。 - 系统触发器
分为数据库级(Database)和模式级(Schema)两种。数据库级触发器的触发事件对于所有用户都有效,模式级触发器仅被指定模式的用户触发。系统触发器支持的触发事件有:LOGON、LOGOFF、SERVERERROR、STARTUP和SHUTDOWN。 - DDL触发器
即由DDL语句(CREATE、ALTER或DROP等)触发的触发器。
5.2创建及修改触发器
语法:
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} trigger_event ON event_target [FOR EACH ROW] [WHEN condition] BEGIN trigger_body END trigger_name;
语法解析:
INSTEAD OF:仅用于替代触发器,即event_target必须为视图,而且必须带FOR EACH ROW子句
trigger_event:触发的事件。可以是DML事件(INSERT、UPDATE、DELETE、INSERT OR UPDATE、UPDATE OF column_name),系统事件(STARTUP、SHUTDOWN等)。
event_target:即指trigger_event事件是发生在哪个对象上的。如DML事件,那么event_target应该是表名或视图名,如果是系统事件,那么event_target就应该是DATABASE或SCHEMA
FOR EACH ROW:如果带此子句,即表示该触发器是行级触发器,如果省略,则是语句级触发器。
WHEN condition :用于限制行级触发器,只有满足condition条件,才会触发触发器。
Example:
CREATE OR REPLACE TRIGGER test_trigger AFTER INSERT OR UPDATE OF sal OR DELETE ON emp FOR EACH ROW BEGIN CASE WHEN INSERTING THEN --当事件为INSERT时,INSERTING为TRUE /* :NEW关键字可以获取新记录的数据,它只能用于行级触发器 */ DBMS_OUTPUT.PUT_LINE('INSERT INTO EMP VALUES('||:NEW.empno||','||:NEW.ename||','||:NEW.job||','||:NEW.mgr||','||:NEW.hiredate||','||:NEW.sal||','||:NEW.comm||','||:NEW.deptno||')'); WHEN UPDATING THEN --当事件为UPDATE时,UPDATING为TRUE DBMS_OUTPUT.PUT_LINE('UPDATING emp which empno='||:OLD.empno||'to sal:'||:NEW.sal);--:OLD关键字可以获取旧记录的数据,它只能用于行级触发器 WHEN DELETING THEN --当事件为DELETE时,DELETING为TRUE DBMS_OUTPUT.PUT_LINE('DELETING emp which empno='||:OLD.empno);--:OLD关键字可以获取旧记录的数据,它只能用于行级触发器 ELSE NULL; END CASE; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM); END test_trigger;
执行DML操作触发上面的触发器,效果如下:
5.3修改触发器的状态
触发器有ENABLED和DISABLED两种状态。
修改某个触发器的状态可以执行下面的SQL:
ALTER TRIGGER trigger_name ENABLE | DISABLE;
使某个表上的所有触发器有效或无效:
ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;
5.4删除触发器
DROP TRIGGER trigger_name;