上一篇我们说到了匿名过程,在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。