PL/SQL

一.过程：
过程用于执行特定的操作。当建立过程时，既可以指定输入参数（IN），也可以

指定输出参数（OUT）。通过在过程中使用输入参数，可以将应用环境的数据传递

到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。
定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS
         [局部变量声明]
         BEGIN
            可执行语句
          EXCEPTION
            异常处理语句
          END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输

出;

操作以有的过程:在PL/SQL块中直接使用过程名;在程序外使用execute <过程名>

[(参数列表)]

使用:
   示例:
创建过程:
create or replace procedure p_1(n in out number) is
    r emp%rowtype;
BEGIN
     dbms_output.put_line('姓名 薪水');
     select * into r from emp where empno=n;
     dbms_output.put_line(r.ename||' '||r.sal);    --输出结果,需要 set

serverout on 才能显示.
    n:=r.sal;
END;
使用过程:
declare
    n number;
begin
    n:=&请输入员工号;
    p_1(n);
    dbms_output.put_line('n的值为 '||n);
end;

删除过程:
    DROP PROCEDURE <过程名>;

 

二.函数：
函数用于返回特定的数据。当建立函数时，在函数头部必须包含return子句，而

在函数体内必须要包含return语句返回数据。

定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型

IS
         [局部变量声明]
         BEGIN
            可执行语句
          EXCEPTION
            异常处理语句
          END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输

出;

使用:
   示例:
创建函数:
create or replace function f_1(n number) return number is
    r emp%rowtype;
BEGIN
     dbms_output.put_line('姓名 薪水');
     select * into r from emp where empno=n;
     dbms_output.put_line(r.ename||' '||r.sal);    --输出结果,需要 set

serverout on 才能显示.
     return r.sal;
END;
使用函数:
declare
    n number;
     m number;
begin
    n:=&请输入员工号;
    m:=f_1(n);
    dbms_output.put_line('m的值为 '||m);
end;

删除函数:
    DROP FUNCTION <函数名>;

 

 

三.包：
包用于逻辑组合相关的过程和函数，它由包规范和包体两部分组成。包规范用于

定义公用的常量，变量，过程和函数。在PL/SQL中建立包规范可以使用create

package命令。包规范只包含了过程和函数的说明，而没有过程和函数的实现代码

。
包体用于实现包规范中的过程和函数，在PL/SQL中建立包体可以使用create

package body命令。

定义:
   定义包的规范
      CREATE [OR REPLACE] PACKAGE <数据包名> AS
               --公共类型和对象声明
               --子程序说明
      END;
    定义包的主体
      CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS
               --公共类型和对象声明
               --子程序主体
      BEGIN
            -初始化语句
      END;

使用:
   示例:
创建数据包规范:
create or replace package pack_1 as
      n number;
      procedure p_1;
      FUNCTION f_1 RETURN number;
end;

创建数据包主体:
create or replace package body pack_1 as
  procedure p_1 is
       r emp%rowtype;
  begin
       select * into r from emp where empno=7788;
       dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal);
   end;

   FUNCTION f_1 RETURN number is
       r emp%rowtype;
   begin
        select * into r from emp where empno=7788;
       return r.sal;
   end;
end;

使用包:
declare
      n number;
begin
      n:=&请输入员工号;
      pack_1.n:=n;
      pack_1.p_1;
      n:=pack_1.f_1;
      dbms_output.put_line('薪水为 '||n);
end;

在包中使用REF游标
示例:
创建数据包规范:
create or replace package pack_2 as
     TYPE c_type is REF CURSOR; --建立一个ref游标类型
     PROCEDURE p_1(c1 in out c_type); --过程的参数为ref游标类型;
end;

创建数据包主体:
create or replace package body pack_2 as
  PROCEDURE p_1(c1 in out c_type) is
  begin
       open c1 for select * from emp;
   end;
end;

使用包:
var c_1 refcursor;
set autoprint on;
execute pack_2.p_1(:c_1);

 

删除包:
    DROP PACKAGE <包名>;

 

 

四.触发器：
触发器是指隐含执行的存储过程，当定义触发器时，必须要指定触发事件以及触

发操作，常用的触发事件包含insert,update和delete语句，而触发器操作实际是

一个PL/SQL块

创建触发器:
    CREATE [OR REPLACE] TRIGGER <触发器名>
    BEFORE|AFTER
    INSERT|DELETE|UPDATE [OF <列名>] ON <表名>
    [FOR EACH ROW]
     WHEN (<条件>)
     <pl/sql块>

     关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;
     关键字"FOR EACH ROW"指定触发器每行触发一次.
      关键字"OF <列名>" 不写表示对整个表的所有列.
     WHEN (<条件>)表达式的值必须为"TRUE".

特殊变量:
     :new --为一个引用最新的列值;
     :old --为一个引用以前的列值;
这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,

而insert只有:new ,delect 只有:old;

使用RAISE_APPLICATION_ERROR
     语法:RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|

false}]);
     抛出用户自定义错误.
     如果参数为'TRUE',则错误放在先前的堆栈上.

INSTEAD OF 触发器
     INSTEAD OF 触发器主要针对视图（VIEW)将触发的dml语句替换成为触发器

中的执行语句，而不执行dml语句.

禁用某个触发器
     ALTER TRIGGER <触发器名> DISABLE
重新启用触发器
     ALTER TRIGGER <触发器名> ENABLE
禁用所有触发器
     ALTER TRIGGER <触发器名> DISABLE ALL TRIGGERS
启用所有触发器
     ALTER TRIGGER <触发器名> ENABLE ALL TRIGGERS
删除触发器
     DROP TRIGGER <触发器名>