Oracle存储过程，存储函数

前言

Java程序是无法直接调用PL/SQL，但是可以调用，由PL/SQL编写的存储过程。

存储过程和存储函数

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

    存储过程和存储函数的区别之一，存储函数可以通过return返回一个函数。而存储过程不能通过return值返回函数。

创建存储过程

语法如下：

create [or replace] PROCEDURE 过程名(参数列表)
AS
     PL/SQL子程序体;

工具创建

在创建的存储过程中，is和as都是可以的。

create or replace procedure sayhello(参数) is
begin
  
end sayhello;

这里有个疑问，functions和procedures是什么区别，functions里也有语句。

调用存储过程

此处介绍的方法是在PL/SQL和SQL语句中的调用，非Java程序。

exec 存储过程名();--execute

begin
 存储过程名();
end;

例子

/*
第一个存储过程：打印HelloWorld

调用存储过程：
1. exec sayHelloWorld();
2. begin
     sayHelloWorld();
     sayHelloWorld();
   end;
   /
*/
create or replace procedure sayHelloWorld 
as
begin
  dbms_output.put_line('Hello World');  
end sayHelloWorld;

/*
给指定的员工涨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？

   dbms_output.put_line('涨前：'||psal||'  涨后:'||(psal+100));
end;

这里需要注意的是，是否需要commit，事务的把控应该交给调用的人。参数要用in。

如果在工具dep里使用，存储过程end；后需要加上斜杠/；

存储函数

    函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名，结果值的类型，以及参数类型等。

    建立存储函数的语法：

create [or replace] function 函数名（参数列表） return函数值类型
as
 PL/SQL子程序体

例子

 --查询某个员工的年收入
 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;

    --返回年收入
    return psal*12+nvl(pcomm,0);
 end;

测试存储函数

工具中点击test，如下

过程和函数中的in和out

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

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

例子

/*
查询某个员工的姓名 月薪 职位

思考：
1. 查询某个员工的所有信息 ---> out参数太多
2. 查询某个部门中的所有员工信息  ---> 返回集合
*/
create or replace procedure queryEmpInformation(eno in number,
                                                pename out varchar2,
                                                psal   out number,
                                                pjob   out varchar2)
as
begin
  
   select ename,sal,job into pename,psal,pjob from emp where empno=eno;                                               
end;