ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 所教授Oracle课程总结(6)——子程序

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

原创 Linux操作系统 作者:violetluna 时间:2009-04-12 21:41:50 0 删除 编辑

上一篇我们说到了匿名过程,在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博客 ” ,链接:http://blog.itpub.net/8061238/viewspace-588751/,如需转载,请注明出处,否则将追究法律责任。

下一篇: flex--初探
请登录后发表评论 登录
全部评论

注册时间:2009-03-29

  • 博文量
    7
  • 访问量
    8142