oracle 11g 的过程和函数

1. 子程序

子程序即为命名的 PL/SQL 块，编译并存储在数据库中。子程序包含如下各个部分：

声明部分
可执行部分
异常处理部分(可选)

子程序的分类：

过程－执行某些操作
函数－执行操作并返回值

子程序的优点：

模块化：将程序分解为逻辑模块
可重用性：可以被任意数目的程序调用
可维护性：简化维护操作
安全性：通过设置权限，使数据更安全

2. 过程

语法如下：

CREATE [OR REPLACE] PROCEDURE 
    <procedure name> [(<parameter list>)] -- parameter list 即为可指定需要的参数列表，可选
IS|AS 
   [<local variable declaration>] -- 声明需要的变量，可选
BEGIN
   <executable statements> -- 可指定部分
[EXCEPTION
   <exception handlers>] -- 异常处理部分，可选
END;

过程指定示例如下所示：

emp 表的数据如下所示：

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

下面创建名为 find_emp 的存储过程，通过输入不同的员工编号入参来执行不同的操作和提示，具体如下所示：

CREATE OR REPLACE PROCEDURE
  find_emp(emp_no in NUMBER) -- in 可不指明，默认表示 emp_no 为入参
AS
  empname VARCHAR2(20);
BEGIN
  SELECT ename INTO empname
  FROM EMP WHERE empno = emp_no;
  DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp;
/

参数列表为 IN 类型参数的存储过程，可以有 SQLPLUS 直接调用和通过 PL/SQL 块调用两种调用方式，示例如下：

exec find_emp(7782); -- sqlplus 直接执行

begin -- PL/SQL 块执行
    find_emp(7782);
end;
/

2.1 参数和调用

参数不能有具体精度，比如 number(8) ，varchar(20) 等。

存储过程参数的三种模式：

IN
- 用于接受调用程序的值
- 默认的参数模式
OUT
- 用于向调用程序返回值
IN OUT
- 用于接受调用程序的值，并向调用程序返回更新的值

由于前面已经举例了 IN 类型参数的示例，下面分别来举例 OUT 类型和 IN OUT 类型参数的示例。

2.1.1 OUT 参数

create or replace procedure proc_out(num out number)
as
begin
    num := 100;
    --DBMS_OUTPUT.PUT_LINE(num);
end;
/

参数列表为 OUT 类型参数的存储过程不同于 IN 类型，只能通过 PL/SQL 块来调用执行，示例如下：

declare
    k number;
begin
    proc_out(k); -- 调用存储过程，k 即为 proc_out 的 num 参数
    DBMS_OUTPUT.PUT_LINE('proc_out 存储过程的执行结果：' || k);
end;
/
proc_out 存储过程的执行结果：100 -- 执行结果

2.1.2 IN OUT 参数

-- 交换两个参数的值
create or replace procedure proc_exchange(p1 in out number, p2 in out number)
as
    v_tmp number;
begin
    v_tmp := p2;
    p2 := p1;
    p1 := v_tmp;
end;
/

参数列表为 OUT 类型参数的存储过程与 OUT 类型的调用方式一样，只能通过 PL/SQL 块来调用执行，示例如下：

declare
    p1 number := 100;
    p2 number := 200;
begin
    proc_exchange(p1, p2);
    DBMS_OUTPUT.PUT_LINE('p1 = ' || p1);
    DBMS_OUTPUT.PUT_LINE('p2 = ' || p2);
end;
/
-- 执行结果
p1 = 200
p2 = 100

2.2 授权

将过程的执行权限授予其他用户示例如下：

1 2	SQL> GRANT EXECUTE ON find_emp TO MARTIN; SQL> GRANT EXECUTE ON swap TO PUBLIC;

2.3 删除

删除过程示例如下：

1	SQL> DROP PROCEDURE find_emp;

3. 函数

函数是可以返回值的命名的 PL/SQL 子程序。创建函数的语法如下：

CREATE [OR REPLACE] FUNCTION 
    <function name> [(param1,param2)] -- 参数列表，可选
    RETURN <datatype>  -- 返回类型
IS|AS 
  [local declarations] -- 变量声明，可选
BEGIN
  Executable Statements; -- 可执行部分
  RETURN result; -- 返回具体值
[EXCEPTION -- 异常处理部分，可选
  Exception handlers;]
END;

定义函数的有如下限制：

函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型，只能是数据库类型
函数的返回类型也必须是数据库类型

访问函数的两种方式：

使用 PL/SQL 块
使用 SQL 语句

3.1 创建

函数创建示例如下所示：

CREATE OR REPLACE FUNCTION fun_hello
  RETURN  VARCHAR2
IS
BEGIN
  RETURN '朋友，您好';
END;
/

3.2 调用

调用上述的 fun_hello 函数方式如下：

SQL> select fun_hello from dual;

FUN_HELLO
--------------------------------------------------------------------------------
朋友，您好

4. 函数与过程的比较

过程	函数
作为 PL/SQL 语句执行	作为表达式的一部分调用
在规格说明中不包含 RETURN 子句	必须在规格说明中包含 RETURN 子句
不返回任何值	必须返回单个值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值	必须包含至少一条 RETURN 语句