PLSQL学习之存储过程和存储函数

本是古典何须时尚 2022-01-07 02:47 369阅读 0赞

## **存储过程和存储函数** ##

在Oracle中，存储过程和存储函数的概念其实是差不多的，一般地，我们都可以混合使用。只不过有的时候有的情况使用过程好一些，有的情况时候函数的时候好一些。下面会讲解在什么时机使用过程还是函数的。

首先，我们在学习存储过程和存储函数之前，先要明白我们**为什么要学他**....

其实存储过程和函数就是**类似与我们在Java中的函数的概念**....

到目前为止，我们的PLSQL是有几个缺点的：

*  PLSQL不能将其封装起来，**每次调用的时候都要将整片代码复制来调用**
 *  有的时候，我们想要将PLSQL的代码保存起来，**只能自己手动保存在硬盘中**，非常麻烦
 *  我们学数据库就是为了让程序能够调用的，**但是PLSQL不能让程序（java）调用**

因此，存储过程和存储函数就能解决上面的问题了，能够将代码封装起来，保存在数据库之中，让编程语言进行调用....

### **存储过程和函数的语法** ###

过程的语法：

create [or replace] procedure 过程名[(参数列表)]  
    as
            PLSQL程序体；【begin…end;/】

函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】
     RETURN  返回值类型
    AS
    PLSQL子程序体；
    
    【begin…end;/】

**无论是过程还是函数，as关键字都代替了declare关键字。**

--------------------

**创建第一个过程：**

CREATE OR REPLACE PROCEDURE hello
    AS
      BEGIN
        dbms_output.put_line('hello world');
      END;

调用过程的三种方式：

*  exec过程名【SQLPLUS中使用】
 *  **PLSQL程序调用**
 *  Java调用

PLSQL调用

BEGIN
      hello();
    
    END;

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
      AS
      BEGIN
        UPDATE EMP
        SET sal = sal * 1.2
        WHERE empno = pempno;
    
      END;

调用：

BEGIN
        bb(7369);
      END;

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

创建过程：**在过程中的参数，默认值是IN，如果是输出的话，那么我们要指定为OUT。**

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
    AS
      BEGIN
        SELECT
          ename,
          sal,
          job
        INTO pename, psal, pjob
        FROM emp
        WHERE empno = pempno;
      END;

调用：在调用的时候，使用到的psal，pname，pjob在调用的时候都没有定义的，因此我们**需要先定义变量后使用！**

DECLARE
    
      psal   emp.sal%TYPE;
      pename emp.ename%TYPE;
      pjob   emp.job%TYPE;
    
    BEGIN
      find(7369, psal, pename, pjob);
    
      dbms_output.put_line(psal || pename || pjob);
    
    END;/

创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER)
      --这里指定的是返回值类型
      RETURN NUMBER
    AS
      income NUMBER;
      BEGIN
        SELECT sal * 12
        INTO income
        FROM emp
        WHERE empno = pempno;
    
        /*在PLSQL中一定要有return语句*/
        RETURN income;
      END;

调用：在PLSQL中，赋值的语句不是直接“=”，而是:=

DECLARE
      income number;
    BEGIN
      income := findEmpIncome(7369);
      dbms_output.put_line(income);
    
    END;/

如果写的是=号，那么就会出现以下的错误：

[2017-07-11 13:58:14] [65000][6550] ORA-06550: 第 4 行, 第 10 列: 
    PLS-00103: 出现符号 "="在需要下列之一时：
     := . ( @ % ;
    ORA-06550: 第 4 行, 第 31 列: 
    PLS-00103: 出现符号 ";"在需要下列之一时：
     . ( ) , * % & -
       + / at mod remainder rem <an exponent (**)> and or ||
       multiset
    ORA-06550: 第 7 行, 第 4 列: 
    PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
     end
       not pragma final instantiable order overriding static member
       constructor map

--------------------

创建有参存储函数findEmpNameAndJobAndSal(编号)，查询7788号员工的的姓名(return)，职位(out)，月薪(out)，返回多个值

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER)
      --这里指定的是返回值类型
      RETURN VARCHAR
    AS
      /*查询出来的字段与列名相同，就使用列名相同的类型就行了。*/
      pename emp.ename%TYPE;
      BEGIN
        SELECT
          sal,
          ename,
          job
        INTO income, pename, pjob
        FROM emp
        WHERE empno = pempno;
    
        /*在PLSQL中一定要有return语句*/
        RETURN pename;
      END;

调用函数：

DECLARE
    
      /*输出的字段与列名的类型是相同的。*/
      income emp.sal%TYPE;
      pjob   emp.job%TYPE;
      pename emp.ename%TYPE;
    BEGIN
      pename := findEmpNameAndJobAndSal(7369, pjob, income);
      dbms_output.put_line(pename || pjob || income);
    
    END;/

### **过程与函数的使用场景** ###

我们发现过程与函数的区别其实是不大的，一般我们都可以用函数来实现的时候， 也可以使用过程来实现....

但是，总有些情况，使用函数比使用过程要好，使用过程比使用函数要好，那什么时候使用过程，什么时候使用函数呢？？？

不难发现的是，函数是必定要有一个返回值的，当我们在调用的时候，接受返回值就直接获取就行了。

也就是说

*  **当返回值只有一个参数的时候，那么就使用存储函数！**
 *  **当返回值没有参数或者多于一个参数的时候，那么就使用过程！**

作者：Java3y  
链接：http://www.imooc.com/article/25261  
来源：慕课网  
本文原创发布于慕课网 ，转载请注明出处，谢谢合作