Oracle存储过程和自定义函数

Oracle存储过程和自定义函数

Oracle数据库开发必备利器之存储过程自定义函数

一、概述

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

 

存储过程和存储函数的相同点：完成特定功能的程序。

存储过程和存储函数的区别：是否用return语句返回值

二、存储过程的创建和调用

2.1创建和使用存储过程

用CREATE PROCEDURE命令建立存储过程和存储函数

语法：

create[or replace] PROCEDURE 过程名(参数列表)

AS

PLSQL子程序体;

Eg:第一个存错过程：打印Hello World

--第一个存错过程：打印Hello World

/*

1.execute执行存储过程（简写 exec）   exec sayhelloworld();

2.begin

    sayhelloworld();

    sayhelloworld()

  end;

  /

*/

create or replace PROCEDURE sayhelloworld

as

  --说明部分

begin

  DBMS_OUTPUT.PUT_LINE('Hello World');

end;

/

2.2带参数的存储过程

--创建一个带参数的存储过程

--给指定的员工涨100元的工资，并打印涨前和涨后的薪水

/*

如何调用：

begin

  raisesalary(7839);

  raisesalary(7566);

end;

/

*/

--eno 员工号作为存储过程的参数 如果存储过程和存储函数带有参数的话，需要指明是输入参数还是输出参数

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.1存储函数

函数为一命名的存储程序，可带参数，并返回一计算值。

函数和过程的结构类似，但必须有一个return子句，用于返回函数值。

 

创建存储函数的语法

create [or replace] FUNCTION 函数名（参数列表）

return 函数值类型

AS

PLSQL 子程序体；

Eg：查询某个员工的年收入

调用存储函数

--存储函数：查询某个员工的年收入

create or replace function queryempincome(eno in number)

return number

as

  --定义变量保存员工的薪水和奖金

  psal emp.sal%type;

  pcomm emp.comm%type;

BEGIN

  --得到该员工的月薪和奖金

  select sal,comm into psal,pcomm from emp where empno=eno;

  

  --直接返回年收入

  --NVL() 遇空时，则为0

  return psal*12+NVL(pcomm,0);

end;

/

 

select *from emp;

四、Out参数

一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。

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

 

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

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

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

 

原则：

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

Eg：

--out参数：查询某个员工姓名 月薪和职位

/*

1.查询某个员工的所有信息 -->out参数太多？

2.查询某个部门中所有员工的所有信息 -->out返回集合？

*/

CREATE OR REPLACE PROCEDURE queryempinform(eno in number,

                                           pename out varchar2,

                                           psal   out number,

                                           pjob   out varchar2)

as

BEGIN

  --得到该员工的姓名 月薪和职位

  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;

 

五、在应用中访问存储过程和存储函数

51.概述

 

52.在应用程序中访问存储过程

53.在应用程序中访问存储函数

六、在out参数中访问光标

6.1在out参数中使用光标，需要申明包结构，包结构分为包头和包体。

包头只负责声明，包体只负责实现。  包体需要实现包头中声明的所有方法。

案例：查询某个部门中所有员工的所有信息。

包头：

CREATE OT REPLACE PACKAGE MYPACKAGE AS

Type empcursor is ref cursor;

Procedure queryEmList(dno in number,empList out empcursor);

END MYPACKAGE;

 

包体：

CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS

Procedure queryEmpList(dno in number,empList out empcursor) AS

BEGIN

Open empList for select * from emp where deptno=dno;

END queryEmpList;

END MYPACKAGE;