Oracle的存储过程、函数与触发器（代码版）

1.引言

    为了弥补自己在SQL的使用上的一些不足，因此决定将Oracle里面的过程和函数重新学习一下，主要还是因为实验的时候遇到了，方式有些懵，但是又被这些不懂的知识点折服，这些特性能够很大程度减少数据库维护的工作量。

2.基本结构介绍

    学习的时候可以联想一下之前C语言的面向过程编程，也就是比较普通的函数。这些特殊结构在Oracle中被称为PL/SQL程序块，编译并存储在数据库内部，这些PL/SQL程序块通常被称为子程序，包含声明部分、可执行部分以及异常处理部分（可选）。子程序通常包含存储过程和函数，存储过程与函数略有不同，存储过程是单纯的执行，而函数则是执行并包含返回值。

3.设计子程序的优点

(1)模块化：将程序分解成逻辑模块；
(2)可重用性：可以被任意数目的程序执行；
(3)可维护性：能够简化数据库的维护操作；
(4)安全性：子程序的执行能够设置权限，数据更加安全；

4.存储过程

##[]表示可选部分
create [or replace] procedure procedure_name([parameter list])
IS/AS
	<local variable declaration>
BEGIN
	<executable statements>
[
EXCEPTION
	<exception handlers>
]
END;

需要补充说明：SQL Server不能写or replace，SQL Server里面可以写IS或者AS来定义变量，SQL Server里面不能使用EXCEPTION，给出一个查询职工姓名的例子：

create or replace procedure find_emp(emp_no in number)
AS
    emp_name varchar(20);
BEGIN
    select last_name||' '||first_name into emp_name 
    from employees where employee_id = emp_no;
    dbms_output.put_line('雇员的名字是：'||emp_name);
EXCEPTION
    when NO_DATA_FOUND THEN
    dbms_output.put_line('雇员没有找到！');
END find_emp;

使用该过程的方式：

set serveroutput on;
##设置控制台的dbms_output.put_line输出内容可见
exec find_emp(198);
##注意带上exec表示执行函数

例子举完了，回到理论部分，这里的参数有三种类型：
（1）in：输入类型,即由应用程序将数据传入oracle存储过程中；这种参数在存储过程中是只读参数，在存储过程中无法对该类型的参数进行修改；
（2）out：输出参数，是在存储过程中对该值进行赋值，在程序中获取值。
（3）in out：输入输出参数，兼具以上两种特性，但可读可写。
如果觉着这个还是不好理解，试试这个解释：in是传入消息，out是传出消息，in/out是消息通讯。
如果是只读参数（in类型）还是比较好理解的，但是，对于out类型参数可能有点难以理解，这里给出一个例子：

--构建一个存储过程（类似于没有返回指定的函数）
create or replace procedure test02(j out int)
as 
begin
    j:=100;
    DBMS_OUTPUT.PUT_LINE(j);--这条语句本身就是一个存储过程
end;

--这里类似于使用匿名函数调用
--含有out类型参数的存储过程，只能在其他的存储过程中调用，
--并且在赋值的时候只能使用变量赋值
DECLARE
k number;
begin
test02(k);
end;
--分析应用场景：比如sql语句的参数检测，
--检测用户想写入的内容是否符合SQL的各种约束
--最终的结果其实相当于引用参数的不含返回值的函数

in/out类型同时兼具两者的特点（当然也破坏了一些特点），只能在存储过程中调用，传入需要是变量，引用型改变，被传入存储过程的变量的改变会拓展到函数外部（out部分的特点）。这个时候你可能会想，那这个in/out岂不像out一样，为了说明这个问题（扣一波定义）：

out会忽略传入的值，给出例子：

--out类型，存储过程
create or replace procedure test02(j out int)
as 
begin
    DBMS_OUTPUT.PUT_LINE(j);
end
--in/out 类型存储过程
create or replace procedure test03(j in out int)
as 
begin
    DBMS_OUTPUT.PUT_LINE(j);
end;
--调用
DECLARE
k number;
begin
    k:=200;
    DBMS_OUTPUT.PUT_LINE('out类型函数输出：');
    test02(k);
    DBMS_OUTPUT.PUT_LINE('in out类型函数输出：');
    test03(k);
end;

这里输出：两个空行，？？？，直接根据定义分析一波，首先在k以out类型传入之后，由于引用覆盖变成了空，故而二次传入第三个函数还是空，初始值已被修改。如果调整两个函数的位置可以得到想要的结果了：

DECLARE
k number;
begin
  	 k:=200;
  	 DBMS_OUTPUT.PUT_LINE('in out类型函数输出：');
  	 test03(k);
  	 DBMS_OUTPUT.PUT_LINE('out类型函数输出：');
	 test02(k);
end;

5.函数

    如果看到这里说明对于存储过程的里面应该比较好了，当然算是一个入门级理解吧，这样的话如果是函数的话那就很容易了。这里就写简洁一些，函数的PL/SQL模板：

##[]表示可选部分
create [or replace] function function_name ([param1,param2])
return <datatype>
IS/AS
	<local variable declaration>
BEGIN
	<executable statements>
	return result;--函数必须要有返回值
[
EXCEPTION
	<exception handlers>
]
END;

函数的使用限制：
（1）函数只能传入in类型的参数；
（2）形参不能是PL/SQL类型，只能是数据库类型；
（3）函数的返回值也必须是数据库类型；
（4）再次强调，必须含有返回值，否则就和存储过程比较相似了。
（5）补充说明，函数的使用可以使PL/SQL，也可以是SQL语句。
（6）过程里面也可以使用return，但是表示的终止，函数里面的return必须要返回有效值。
直接给个例子：

--创建函数
create or replace function self_introduce(username in varchar2)
return varchar2
as
    vegetable_bird_name employees.last_name%TYPE;
    --这里的%TYPE表示的是和数据表里面的字段的类型一致
begin
    vegetable_bird_name:=username;
    return vegetable_bird_name||' is a vegetable bird.';
end;
--使用函数
DECLARE
    name varchar2(100);
begin
    name:=self_introduce('Flying_dark_feather');
    DBMS_OUTPUT.put_line(name);
end;

最后给出过程和和函数复杂一点的例子（由于需要具体的数据库才能运行，所以这里直接截图了）：
过程的复杂例子：

函数的复杂例子：

6.触发器

    这里重新介绍一下触发器，依然还是站在SQL编程的角度上来看，基本语法如下：

##[]表示可选部分
create [or replace] trigger trigger_name after|before
 instead of|insert|[OR] UPDATE [of column_list]
[[or]DELETE]
--before 和after表示的是建立触发器的种类，也就是生效的阶段，
--注意：在SQLServer中只能建立after触发器
--instead of后面接受的是DML操作类别
ON table_name|view_name
references {old as old/new as new}--其实只为新数据和元数据取别名
[for each row]--说明是行级触发器还是表级触发器
[when (condition)]--触发器追加出发限制条件
BEGIN 
--省略具体触发器内容
END;
--注意：触发器不能够使用rollback、commit、create、drop、alter以及savepoint等指令

给出如下例子：

--当客户下完订单后，自动统计该订单所有图书价格总额
CREATE OR REPLACE TRIGGER trg_sumcost
AFTER INSERT  ON ORDERS--用户插入触发
FOR EACH ROW--行级触发器
DECLARE 
  sumcost NUMBER;
  v_order_id ORDERS.order_id%TYPE;
BEGIN 
  IF INSERTING THEN
    v_order_id := :new.order_id;--:new表示的是新数据
    SELECT SUM(QUANTITY*COST) INTO sumcost FROM BOOKS,ORDERS,ORDERITEM
    WHERE  ORDERS.order_id = ORDERITEM.order_id AND ORDERITEM.ISBN = BOOKS.ISBN
     AND ORDERS.order_id = ORDERS.order_id;
    DBMS_OUTPUT.PUT_LINE('总价格：'||sumcost);
  END IF;
END trg_orderitem;

    对于表级触发器作补充说明：当触发器里面没有for each row的时候即为表级触发器，一旦使用了:new或者:old必须是行级触发器；多行操作的时候，表级触发器只触发一次，而行级触发器触发多次。
    最后，再给出一个偏向于实用的例子：

--禁止客户在非工作时间（早上 8:00 之前，晚上 17:00 之后）下订单。
CREATE OR REPLACE TRIGGER trg_orderitem
BEFORE INSERT OR UPDATE OR DELETE ON orderitem
BEGIN 
  IF TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '17:00'
    OR TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN')
  THEN
	RAISE_APPLICATION_ERROR(-20005,'只能在正常的时间内进行改变。');
END IF;
END trg_orderitem;

7.参考文献

    (1)oracle——存储过程参数