Oracle--PL/SQL存储过程、存储函数详解

￡神魔★判官ぃ 2021-08-13 16:28 337阅读 0赞

### 存储过程 ###

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程语法：

create [or replace] PROCEDURE 过程名 [(参数名 in/out 数据类型)]
    AS 
    begin
    	PLSQL子程序体;
    end;

或者

create [or replace] PROCEDURE 过程名 [(in/out 数据类型)]
    is 
    begin
    	PLSQL子程序体：
    end 过程名;

示例：打印hello world

create or replace procedure sayhelloworld
    as
    begin
      dbms_output.put_line('hello world');
    end;

调用：

方式一：直接调用
    SQL> exec sayhelloworld;

![在这里插入图片描述][20190915093636615.png]

方式二：在另一个PL/SQL中调用
    begin
    	syahelloworld();
    end;

![在这里插入图片描述][2019091509390014.png]

##### 带参数的存储过程 #####

示例：给指定的员工涨100工资，并打印出涨前和涨后的工资

create or replace procedure raiseSalary(eno in number)
    is
           --定义变量保存涨前的薪水
           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 raiseSalary;

调用：

begin
      raiseSalary(eno => 7902);
      commit;
    end;

![在这里插入图片描述][20190915094813518.png]

#### 存储函数 ####

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

**建立存储函数的语法：**

CREATE [OR REPLACE] FUNCTION 函数名（参数列表）
    RETURN 函数值的类型
    AS
    	PLSQL子程序体；
    END[函数名];

**存储过程和存储函数的区别：**  
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。  
但过程和函数都可以通过out指定一个或多个输出参数。我们可以理用out参数，在过程和函数中实现返回多个值。

示例：查询某个员工的年薪

create or replace function queryEmpIncome(eno in number) 
    return number
    is
           --定义变量保存月薪和奖金
           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 queryEmpIncome;

调用：

begin
      :result := queryempincome(7839);
    end;

![在这里插入图片描述][20190915103324257.png]使用存储过程替换上面的示例：

create or replace procedure empincomep(eno in emp.empno%type, income out number)
    is
      psal emp.sal%type;
      pcomm emp.comm%type;
    begin
      select t.sal, t.comm into psal, pcomm from emp t 
      where t.empno = eno;
        income := psal*12+nvl(pcomm,0);
    end empincomep;

调用：

declare
      income number;
    begin
      empincomep(7369, income);
      dbms_output.put_line(income);
    end;

![在这里插入图片描述][20190915104927507.png]

[20190915093636615.png]: /images/20210813/d0ab7a1e3c994ae1a9393c054747a63c.png
[2019091509390014.png]: /images/20210813/a10d1266f16b45e9b4c9c031bbc881e3.png
[20190915094813518.png]: /images/20210813/81bd0a315440447db9af3565aa96eaaa.png
[20190915103324257.png]: /images/20210813/9b4a38b4e3ea41f8a936065f6d301e0a.png
[20190915104927507.png]: /images/20210813/801b5236550a437498b7f059794db5d1.png