上一篇我们说到了匿名过程，在Oracle中可以通过匿名过程完成很多SQL不能完成的逻辑处理代码。但是匿名过程也有很多自己的缺陷，每次我们使用匿名过程的时候都需要重新写一次代码，非常的繁琐。

因此，更多的时候我们会用到过程中的另一部分，那就是子程序。子程序包括存储过程和存储函数，相比匿名过程来说，子程序可以将写好的过程代码编译并保存下来，下次需要用到直接调用即可，类似于java中的方法。接下来，我们一一来讲解子程序。

1.存储过程

存储过程的语法结构如下：

CREATE [OR REPLACE] PROCEDURE [(pram_list)]
IS|AS
BEGIN
[EXCEPTION]
END;
/

可以看到存储过程就像一个方法一样，可以传入参数。

比如我们现在写一个最简单的存储过程，输出hello,world

CREATE OR REPLACE PROCEDURE my_proc
IS
BEGIN
DBMS_OUTPUT.put_line('hello,world');
END;
/

上面代码创建了一个没有参数的存储过程，这里需要注意的是如果没有参数，过程名称后面不需要"()"。

存储过程的调用方式有多种，这里我们可以通过"EXECUTE my_proc"来执行存储过程。

存储过程的参数可以是输入参数，输出参数，也可以是输入输出参数，这里分别用IN,OUT,IN OUT来表示，默认情况下是输入参数模式，比如，下面的代码要求一个输入参数p_empno，用来找出员工编号为该参数的员工信息。

CREATE OR REPLACE PROCEDURE my_proc(p_empno number)
IS
l_emp emp%rowtype;
BEGIN
SELECT * INTO l_emp FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.put_line(l_emp.ename);
END;
/

在命令窗口执行"EXECUTE my_proc(7788)"输出"SCOTT"。这里的参数p_empno就是默认的输入参数。上面代码中我们根据传入的员工编号参数找到这个员工的记录，放入行属性类型l_emp中，然后通过l_emp.ename找出这条记录的ename字段的值。

这个存储过程可以在SQL*PLUS中输出信息，但是更多的时候我们是通过应用程序来使用Oracle，因此，我们需要一个传出来的值或者返回值，存储过程中的输出参数就是用来返回数据用的。在上面代码的基础上，我们修改根据传入的员工编号得到该员工的姓名，并且输出来，代码如下：

CREATE OR REPLACE PROCEDURE my_proc(p_empno number, p_ename OUT varchar2)
IS
l_emp emp%rowtype;
BEGIN
SELECT * INTO l_emp FROM emp WHERE empno = p_empno;
p_ename := l_emp.ename;
END;
/

我们通过一段匿名过程代码来调用这个存储过程。

DECLARE
       l_ename emp.ename%type;
BEGIN
     my_proc(7788, l_ename);
     DBMS_OUTPUT.put_line(l_ename);
END;
/

即可输出员工的姓名。
使用IN OUT参数的方式，传入薪资，找出员工中高于该薪资的人数，代码如下：

CREATE OR REPLACE PROCEDURE my_proc(p_sal IN OUT number)
IS
l_count number(8);
BEGIN
SELECT count(*) INTO l_count FROM emp WHERE sal > p_sal;
p_sal := l_count;
END;
/

使用下面的代码来执行：

DECLARE
      l_sal number(8);
BEGIN
     l_sal := 1200;
     my_proc(l_sal);
     DBMS_OUTPUT.put_line(l_sal);
END;
/

可以看到，IN,OUT,IN OUT参数的区别在于：
1. IN参数传入参数到存储过程之中，IN参数不能在过程代码中赋值。
2. OUT参数可以传出数据，OUT参数可以在过程中赋值，但是建议最好是定义一个局部变量计算出最终值以后再赋值给OUT参数。
3. IN OUT参数可以传入数据，也可以传出数据，再过程代码中可以被赋值。

2.存储函数

存储函数的结构如下：

CREATE OR REPLACE FUNCTION [(pram_list)] RETURN return_type
IS|AS
BEGIN
[EXCEPTION]
END;
/

一个最简单的输出"hello,world"的存储函数。

CREATE OR REPLACE FUNCTION my_fun RETURN number
IS
BEGIN
DBMS_OUTPUT.put_line('HELLO,WORLD');
RETURN null;
END;
/

调用的方式也很简单，与我们常用的聚合函数一样的用法，可以在过程中直接调用，也可以通过SELECT语句来调用，比如
SELECT my_fun() FROM dual;

绝大部分存储函数的参数类型是IN类型，存储函数更多的是通过RETURN语句来返回值。

比如我们根据员工编号得到员工的姓名，代码如下：

CREATE OR REPLACE FUNCTION my_fun(p_empno number) RETURN varchar2
IS
l_ename emp.ename%type;
BEGIN
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno;
RETURN l_ename;
END;
/

通过SELECT语句来调用

SELECT my_fun(7788) FROM dual;

需要注意的是：存储过程和存储函数的参数列表中不能指定具体的参数类型长度，如
p_empno number(8)是作为参数是错误的，应该写成p_empno number，这里也可以使用属性类型%type。

ITPub博客

所教授Oracle课程总结(6)——子程序

最新文章