Oracle 存储过程 和 存储函数

1.存储过程和存储函数

(1) 概念：

     指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。

(2) 相同点：

     二者都是用于完成特定功能的程序。

(3) 不同点：

    ① 标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。

    ② 存储函数可以有一个 return 返回值，而存储过程没有返回值。

    ③ 函数可以在select语句中直接使用，而过程不能，例如：假设已有函数fun_getAVG() 返回number类型绝对值。那么select fun_getAVG(col_a) from table 这样是可以的。

2.存储过程：

create [or replace] PROCEDURE 过程名(参数列表)AsPLSQL子程序体

(1) 第一个示例：不带参数存储过程以及如何调用

--第一个存储过程:打印Hello Worldcreate or replace procedure sayhelloworldas  --说明部分begin  dbms_output.put_line('Hello World');end;/

--调用存储过程：exec sayhelloworld();--或者：execute sayhelloworld();--或者：beginsayhelloworld();  end; /

(2) 第二个示例：带参数存储过程以及如何调用

--创建一个带参数的存储过程:给指定的员工涨100块钱的工资,并且打印涨后的薪水create or replace procedure raisesalary(eno in number)as  --定义一个变量保存涨前的薪水  psal emp.sal%type;begin  --得到员工涨前的薪水  select sal into psal from emp where empno=eno;  --给该员工涨100  update emp set sal=sal+100 where empno=eno;  --需不需要commit:一般不在存储过程或者存储函数中commit和rollback.  --打印  dbms_output.put_line('涨前:'|| psal || ' 涨后:' ||(psal+100));end;/

调用以及最终的效果：

3.存储函数：

     函数为一命名的存储程序，可带参数，并返回一计算值,函数和过程的结构类似，但必须有一个 return 子句，用于返回函数值。

--创建存储函数的语法create [or replace] FUNCTION函数名（参数列表）return 函数值类型ASPLSQL子程序体；

示例：

--存储函数:查询某个员工的年收入create or replace function queryempincome(eno in number)return numberas  --定义变量保存员工的薪水和奖金  psal emp.sal%type;  pcomm emp.comm%type;begin  --得到该员工的月薪和奖金  select sal,comm into psal,pcomm from emp where empno=eno;   --直接返回年收入   return psal*12+pcomm;end;/

4. out 参数：

(1) 过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

     存储过程和存储函数都可以有out参数

     存储过程和存储函数都可以有多个out参数

     存储过程可以通过out参数来实现返回值

(2) 什么时候用存储过程/存储函数？

     原则：如果只有一个返回值，用存储函数；否则，就用存储过程。

(3) 示例

--out参数:查询某个员工姓名 月薪和职位create or replace procedure queryempinform(eno in number,                                     pename out varchar2,                                     psal    out number,                                     pjob    out varchar2)asbegin --得到该员工的姓名 月薪和职位 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;end;/

5.在 Java 应用程序中访问存储过程和存储函数

(1) 访问存储过程

create or replace procedure queryempinform(eno in number,                                           pename out varchar2,                                           psal    out number,                                           pjob    out varchar2)

(2) 访问存储函数

create or replace function queryempincome(eno in number)return number