PL/SQL之高级编

参考文献:《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;

 

5.5系统触发器(略)

5.6DDL触发器(略)

 

 

posted on 2012-08-30 01:07 TimLeung 阅读(...) 评论(...) 编辑 收藏

导航