Oracle中的存过

青旅半醒 2024-03-22 15:29 14阅读 0赞

**目录**

一、什么是存储过程？

二、创建存储过程的语法

三、存储过程的作用有哪些？

3.1. 提高数据库性能：

3.2. 提高代码可维护性：

3.3. 提高安全性：

四、存储过程的类型有哪些？

五、存储过程的调用方式

5.1. 使用SQL语句调用存储过程：

5.2. 使用CALL语句调用存储过程：

5.3. 使用PL/SQL块调用存储过程：

5.4. 使用游标变量调用存储过程：

六、存储过程创建的流程

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

## 一、什么是存储过程？ ##

存储过程：**procedure**！！！--有名字的pl/SQL块(程序块)

存储过程是一种数据库对象，是一种存储在数据库中的可执行程序，是一些经过编写、编译而存储在数据库中的SQL语句集。

## 二、创建存储过程的语法 ##

语法如下：

create or replace procedure pro_name [(参1 [in]|out|in out 类型,参2...)] 
    is|as
    [声明变量]
    begin
      要执行的语句;
    [exception]
    end;

## 三、存储过程的作用有哪些？ ##

存储过程是用于完成一些特定的任务，可以被视为是一种可复用的代码，可以执行一系列的SQL语句，执行一系列的数据库操作，而不需要重复编写相同的代码。存储过程可以使用传入的参数进行动态化的执行，也可以接受输入参数并返回结果。存储过程可以被其他应用程序和工具调用，也可以直接从数据库中调用执行，也可以在触发器、作业等场景中使用。

存储过程通常用于完成一些复杂的业务逻辑、数据处理操作以及数据验证操作。通过存储过程可以提高数据库的性能和安全性，因为存储过程进行了编译，可以利用数据库的缓存机制，减少重复的SQL语句执行，从而提高数据库的效率。在存储过程中可以使用控制结构（if、while等）和变量，实现复杂的业务逻辑，增加数据库应用的灵活性和复用性，减少了代码的重复性，使得应用程序的维护更加方便和高效。因此存储过程的作用可总结为以下几点：

### 3.1. 提高数据库性能： ###

存储过程可以减少客户端和服务器之间的数据传输，减少了网络传输和SQL解析的开销，提高数据库的性能。

### 3.2. 提高代码可维护性： ###

将复杂的业务逻辑封装在一个单独的程序单元中，即将常用的SQL语句封装在存储过程中，可以减少代码的冗余，并且可以方便地进行修改和维护，从而提高代码的可维护性和可重用性。

### 3.3. 提高安全性： ###

存储过程可以限制用户对数据库的访问权限，对数据库的访问进行控制和限制，提高数据库的安全性。

## 四、存储过程的类型有哪些？ ##

常见的存储过程类型包括：

*  1. 简单存储过程：只包含一条SQL语句。
 *  2. 复杂存储过程：包含多条SQL语句和逻辑控制语句。
 *  3. 带参数存储过程：可以接受输入参数并返回结果。
 *  4. 带游标的存储过程：可以使用游标遍历查询结果集。

这四种存储过程只是比较常见的存储过程，它们是普通的存储过程，其实函数、包、触发器等也是属于存储过程的一种范畴。函数是一种比较特殊的存储过程，它返回一个值。函数可以接受参数，并且可以通过参数来计算返回值。包是一种将存储过程、函数和变量组合在一起的结构。包中的存储过程和函数可以被多个程序共享，并且可以在包的内部进行调用。而触发器是在数据库中发生特定事件时自动执行的存储过程。触发器可以在插入、更新或删除行时执行。

下面举例一个简单的普通存储过程，输入一个订单号，请查询出订单表中的订单信息，并将结果集返回给调用方，那么代码如下：

CREATE OR REPLACE PROCEDURE get_order_info
    (
      p_order_id IN NUMBER,
      p_cursor OUT SYS_REFCURSOR
    )
    AS
    BEGIN
      OPEN p_cursor FOR
      SELECT * FROM orders WHERE order_id = p_order_id;
    END;

解析：创建一个名为get\_order\_info的存储过程，该存储过程接受一个输入IN参数p\_order\_id和一个输出OUT参数p\_cursor。其中p\_order\_id是订单ID，p\_cursor是一个游标，用于返回查询结果。

在存储过程的主体中，使用OPEN语句打开一个游标，并将查询结果赋值给p\_cursor参数。查询语句使用WHERE子句过滤出order\_id等于p\_order\_id的订单记录。

调用该存储过程：

DECLARE
      v_order_cursor SYS_REFCURSOR;
      v_order_id NUMBER := 1001;
    BEGIN
      get_order_info(v_order_id, v_order_cursor);
      -- 处理查询结果集
    END;

调用存储过程，将订单ID作为参数传递给p\_order\_id，最后查询结果将通过p\_cursor参数返回。

## 五、存储过程的调用方式 ##

存储过程的调用方式与客户端编程语言有关。不同的客户端编程语言支持不同的存储过程调用方式。

常见的调用方式有以下几种：

### 5.1. 使用SQL语句调用存储过程： ###

EXECUTE procedure_name (parameter_list);

其中，procedure\_name是存储过程的名称，parameter\_list是存储过程的参数列表。

以下是一个简单的示例，展示如何创建一个存储过程并使用EXECUTE调用它：

创建存储过程：

CREATE OR REPLACE PROCEDURE update_salary (emp_id     IN NUMBER, 
                                               new_salary IN NUMBER) 
    AS
    BEGIN
      UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
      COMMIT;
    END;

两个参数：“emp\_id”和“new\_salary”。它将更新名为“employees”的表中的“salary”列，以便将新薪资设置为“new\_salary”，仅更新具有指定“emp\_id”的员工的薪资。

调用存储过程：

EXECUTE update_salary(1001, 5000);

调用“update\_salary”存储过程，并将“emp\_id”参数设置为1001，“new\_salary”参数设置为5000。此存储过程将更新员工ID为1001的薪资为5000。

### 5.2. 使用CALL语句调用存储过程： ###

CALL procedure_name (parameter_list);

其中，procedure\_name是存储过程的名称，parameter\_list是存储过程的参数列表。

举例说明：

创建一个存储过程，要求输入一个员工编号 ，打印他的工资

create or replace procedure s63 (v number)
    is
    v_sal number;
    begin
      select sal into v_sal from emp where empno=v;
      dbms_output.put_line(v_sal);
    end;

创建一个名为“s63”的存储过程，输入一个参数“v”，并从名为“emp”的表中选择与该参数相应的员工的薪资，并将其存储在“v\_sal”变量中。 然后，使用“dbms\_output.put\_line”语句将薪资输出到控制台。

调用该存储过程：

call s63(7788);

使用“call”语句并传递一个整数参数（此处为7788），该参数将作为“v”参数传递给存储过程。调用存储过程后，将在控制台上显示与该员工的薪资相应的值。

***上例中有参数输入的要求，那么无参数输入要求呢？***

比如：

创建一个存过，把emp表10部门的员工信息插入到emp\_5，更新emp\_5职位是MANAGER的员工的薪资为原来的一半，并打印出更新前和更新后的工资，具体语句如下：

create or replace procedure s600 as
    v_sal number;
    begin
    --向emp_5表插入部门编号为10的员工信息
      insert into emp_5 select * from emp where deptno=10;
    --查询工作为manager的薪资
      select sal into v_sal from emp_5 where job='MANAGER';
      dbms_output.put_line('更新前'||v_sal);
    --更新emp_5表中工作为manager的薪资为原来的一半
      update emp_5 set sal=sal/2 where job='MANAGER' 
      returning sal into v_sal;
      dbms_output.put_line('更新后'||v_sal);
    commit;
    end;

创建一个名为“s600”的存储过程。将在另一个名为“emp\_5”的表中插入“deptno”为10的员工信息。然后，将从“emp\_5”表中选择职位为“MANAGER”的员工的薪资，并将其存储在“v\_sal”变量中。接下来，将“emp\_5”表中职位为“MANAGER”的员工的薪资减半，并将更新后的薪资存储在“v\_sal”变量中。最后，使用“dbms\_output.put\_line”语句将更新前和更新后的薪资输出到控制台，并使用“commit”语句提交更改。

调用该存储过程：

call s600();

使用“call”语句并传递不带参数的空括号。调用存储过程并执行其中的代码，更新“emp\_5”表中职位为“MANAGER”的员工的薪资并将输出信息打印到控制台。

### 5.3. 使用PL/SQL块调用存储过程： ###

语法：

DECLARE
      -- 声明变量
      variable1 datatype;
      variable2 datatype;
    BEGIN
      -- 赋值
      variable1 := value1;
      variable2 := value2;
    
      -- 调用存储过程
      procedure_name (variable1, variable2);
    
      -- 其他操作
    END;

其中，variable1和variable2是存储过程的参数，value1和value2是变量的值。

举例说明：

***Ⅰ、创建一个存储过程，请输入一个员工编号，并输出部门平均工资：***

create or replace procedure s450(v1 number,
                                     v2 out number) 
    is
    begin
      select avg(sal) into v2 
      from emp 
      where deptno=(
                    select deptno 
                    from emp 
                    where empno=v1);
    end;

语句定义一个名为s450的存储过程，有两个参数：v1为输入参数，v2为输出参数。计算emp表中与v1所在的部门相同的所有员工的薪水平均值，并将结果赋值给v2。

调用该存储过程：

declare
    v_a number;
    begin
      s450(7788,v_a);
      dbms_output.put_line(v_a);
    end;

使用DECLARE语句定义一个变量v\_a，并将其作为输出参数传递给s450存储过程。然后，使用DBMS\_OUTPUT.PUT\_LINE语句输出v\_a的值。最后，执行该PL/SQL程序，s450存储过程会被调用，并将计算结果赋值给输出参数v2，最终输出v\_a的值。

***Ⅱ、创建一个存储过程，输入一个姓名和给他加的工资 ，然后输出新工资（使用in out 类型）***

create or replace procedure s400(vname varchar2,
                                     vsal  in out number) 
    is
    begin
      update emp_5 set sal=sal+vsal where ename=vname 
      returning sal into vsal;
       dbms_output.put_line(vsal);
    end;

两个参数：一个是员工姓名vname，另一个是in out类型的变量vsal。在存储过程中，使用UPDATE语句更新emp\_5表中ename等于vname的员工的薪水，将其加上vsal的值。使用RETURNING子句将更新后的薪水值返回到变量vsal中。最后，使用DBMS\_OUTPUT.PUT\_LINE函数将变量vsal的值输出到控制台上。

> ***①解释in out 类型：***
> 
> in out是Oracle数据库存储过程中的一种参数类型，表示**该参数既可以作为输入参数（in），也可以作为输出参数（out）**。in out参数在存储过程中可以被修改，并且这些修改可以在存储过程调用结束后传递回调用者。
> 
> 在存储过程中，in out参数可以用于传递某些数据，让存储过程对其进行修改，然后将修改后的值返回给调用者。例如，可以将一个员工的薪水作为in out参数传递给存储过程，存储过程可以根据某些条件对其进行修改，然后将修改后的薪水返回给调用者。
> 
> 理解in out参数的关键是理解它既可以作为输入参数，也可以作为输出参数。这意味着，在调用存储过程时，需要提供in out参数的初始值，存储过程可以修改该参数的值，并将修改后的值返回给调用者。在存储过程中，可以使用RETURNING子句将修改后的参数值返回给调用者。
> 
> ***②解释RETURNING子句：***
> 
>     update emp_5 set sal=sal+vsal where ename=vname 
>     returning sal into vsal;
> 
> 这是Oracle数据库的UPDATE语句，用于更新emp\_5表中ename等于vname的员工的薪水。其中，vsal是一个in out类型的变量，存储过程中传递的一个值，用于增加员工的薪水。在这条UPDATE语句中，将原始薪水值加上vsal的值，得到新的薪水值，并将其更新到表中。
> 
> 同时，该UPDATE语句使用RETURNING子句，将更新后的薪水值返回给存储过程中的变量vsal。这样，存储过程中的vsal变量就会被修改为更新后的薪水值，以便后续处理。
> 
> 使用RETURNING子句的好处：
> 
> 可以避免在存储过程中再次查询emp\_5表，从而**减少数据库的I/O操作，提高性能**。同时，使用RETURNING子句可以将更新后的数据传递给存储过程中的变量，**方便后续处理**。
> 
> 总之呢，使用RETURNING子句可以在UPDATE语句执行后，将更新后的数据返回给存储过程中的变量，方便后续处理，提高性能。

调用该存储过程：

declare
    vsal number:=1000;--先输入
    begin
      s400('SCOTT',vsal);--变量输出
    end;

在DECLARE块中，定义一个名为vsal的变量，并将其初始化为1000。然后调用存储过程s400，并将参数vname设置为'SCOTT'，将变量vsal作为第二个参数传递给存储过程。在存储过程执行完毕后，变量vsal的值将被修改为更新后的薪水值，并输出到控制台上。

***Ⅲ、创建一个存储过程，输出emp表的员工姓名和经理姓名（使用动态游标）***

create or replace procedure s350 (c1 out sys_refcursor) is
    aname varchar2(20);
    bname varchar2(20);
    begin
    open c1 for
    select a.ename,b.ename from emp a join emp b
    on a.mgr=b.empno ;
    loop
    fetch c1 into aname,bname;
    exit when c1%notfound;
    dbms_output.put_line('员工姓名：'||aname||' 经理姓名：'||bname);
    end loop;
    close c1;
    end;

定义两个变量aname和bname用于存储员工和经理的姓名。然后，使用join语句将emp表中的员工和经理进行关联，并将结果存储在游标c1中。接着，使用fetch语句从游标中获取每一行数据，并将员工和经理的姓名分别存储在aname和bname变量中。当游标中没有数据时，使用exit语句退出循环。最后，使用dbms\_output.put\_line语句将结果输出到屏幕上。最后，使用close语句关闭游标c1。

> ***解释sys\_refcursor参数***
> 
> 在Oracle存储过程中，使用c1 out sys\_refcursor这种参数的原因是为了**返回一个游标**，以便在客户端应用程序中处理结果集。sys\_refcursor是一种特殊的数据类型，它允许在存储过程中声明一个游标变量，并将其作为参数返回给客户端应用程序。
> 
> 使用游标返回结果集的好处：
> 
> 可以提高存储过程的灵活性和可重用性。存储过程可以返回不同的结果集，而不需要修改存储过程的代码。客户端应用程序也可以根据需要对结果集进行处理，而不需要在存储过程中进行复杂的处理。
> 
> 另外，使用游标还可以提高性能，因为它允许在存储过程中一次性获取所有的结果集，而不需要将结果集存储在临时表中，从而避免了在存储过程中频繁读写临时表的开销。

调用该存储过程：

declare
    c1 sys_refcursor;
    begin
      s350(c1);
    end;

在匿名块中声明一个变量c1，数据类型是sys\_refcursor，这个变量用于存储存储过程返回的游标。调用存储过程s350，并将变量c1作为参数传递给存储过程。当存储过程执行完毕后，这个匿名块也就结束。

执行后看下运行结果，如图所示：

![7cdac48925544811b92a575330f15ee5.png][]

***利用sys\_refcursor解决下面问题：***

***创建一个存过，要求输出每个人的名字 ，并且他们名字中的每一个字母，用逗号隔开***

create or replace procedure s300(c1 out sys_refcursor) 
    is
    v_ename varchar2(20);
    v_sql varchar2(20);
    begin
      open c1 for select ename from emp;
      loop
        fetch c1 into v_ename;
        for i in 1..length(v_ename) loop
          v_sql:=v_sql||substr(v_ename,i,1)||',';
        end loop;
        exit when c1%notfound;
       dbms_output.put_line(v_ename||' '||rtrim(v_sql,','));
       v_sql:= null;
       end loop;
       close c1;
     end;

输出参数c1，它是一个游标类型的引用变量。 在存储过程的主体中，首先打开游标c1并将其设置为查询emp表中的所有员工姓名。 然后，使用循环语句fetch从游标中获取每个员工的姓名，并使用for循环遍历该姓名的每个字符。 在for循环中，将每个字符添加到v\_sql变量中，并在最后一个字符后添加一个逗号。 当循环结束时，使用dbms\_output.put\_line函数输出员工的姓名和拆分后的字符，并使用rtrim函数删除最后一个逗号。 最后，关闭游标并结束存储过程。

调用该存储过程：

declare
    c1 sys_refcursor;
    begin
      s300(c1);
    end;

最后我们执行查看运行结果如下：

![c9e6411b59f84ecfb5bfe67084ce11ad.png][]

### 5.4. 使用游标变量调用存储过程： ###

-- 声明游标变量
    variable_name SYS_REFCURSOR;
    
    -- 调用存储过程
    procedure_name (parameter, variable_name);
    
    -- 处理游标数据
    LOOP
      FETCH variable_name INTO variable_list;
      EXIT WHEN variable_name%NOTFOUND;
      -- 处理游标数据
    END LOOP;

其中，variable\_name是游标变量，parameter是存储过程的参数，variable\_list是游标中的数据。

下面是一个使用游标变量调用的示例代码：

创建存储过程：

CREATE OR REPLACE PROCEDURE get_employee_info(dept_id IN NUMBER) 
    AS
      CURSOR emp_cur IS
        SELECT empno, ename, job, sal 
        FROM emp 
        WHERE deptno = dept_id;
      emp_rec emp_cur%ROWTYPE;
    BEGIN
      OPEN emp_cur;
      LOOP
        FETCH emp_cur INTO emp_rec;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('EMPNO: ' || emp_rec.empno || ', NAME: ' || emp_rec.ename || ', JOB: ' || emp_rec.job || ', SAL: ' || emp_rec.sal);
      END LOOP;
      CLOSE emp_cur;
    END;

一个输入参数dept\_id，类型为数字，使用游标变量emp\_cur查询emp表中deptno等于输入参数dept\_id的所有员工的信息，并将其赋值给emp\_rec变量。然后，使用循环语句遍历所有查询结果，并使用DBMS\_OUTPUT.PUT\_LINE语句输出每个员工的信息。最后，关闭游标。

调用存储过程：

DECLARE
      dept_id NUMBER := 10;
    BEGIN
      get_employee_info(dept_id);
    END;

使用DECLARE语句定义一个变量dept\_id，并将其赋值为10。然后，调用存储过程get\_employee\_info，并将dept\_id作为输入参数传递给该存储过程。最终，该存储过程会查询emp表中deptno等于10的所有员工的信息，并将其输出到控制台。

## 六、存储过程创建的流程 ##

举个栗子：创建一个存储过程，要求输入一个员工编号 ，并输出部门平均工资。

创建存储过程，找到左侧对象栏-<当前用户>-Procedures，

![26f813fcf2de4b5e9d270a91f73d8096.png][]

然后右键【新建】 到下图，输入存储过程名，比如：pro\_s450，然后点击确定

![eb3168c83a55452d9b364033d2d880a9.png][]

接着按照要求编写SQL语句，完成存过编写；

![41cb79c744d04459a0a4f56193146ec6.png][]

下一步点击F8执行当前窗口，完成编译，就可以在左侧对象栏看到创建的存过，如图所示：

![cec1a9a0f0f04da3af9caecbf0fadf76.png][]

下一步右键刚创建的存过名，找到测试，点击进入测试窗口，如下图：

![0f6e885796a74389b55ba33c3dbe6946.png][]

在参数窗口输入**入参值**，即输入一个V1的值，比如：7788，然后F8运行，查看是否有出参或者有DBMS输出，如果有，则存过编写无问题，若无，则需要查看存过的错误语句在哪一行，进行修改。执行结果有出参2215，如下图所示：

![7aedfe6aebc14c3bbb3768bd4c7179d5.png][]

单独拿出存过中的SQL语句进行查询发现结果一样。证明上述存过是没有问题的，这就是一个完整的流程。

![0c4a6634e10349c38b772c448df56c1c.png][]

>  ***注意：***
> 
> 创建存储过程最好不要直接在sql窗口创建，而是通过对象栏右键procedures新建，逐步进行创建编译和测试，原因有如下几点：
> 
>  *  **1. 可以避免语法错误**：在对象栏右键procedures新建时，系统会自动生成模板，用户只需要按照模板填写相应的SQL语句即可，这样可以避免因手写SQL语句出现语法错误。
>  *  **2. 可以提高效率**：通过对象栏右键procedures新建，可以快速创建存储过程，不需要手动输入大量的SQL语句，同时也可以快速编译和测试存储过程。
>  *  **3. 可以保证安全性**：在对象栏右键procedures新建时，可以设置存储过程的访问权限，只有经过授权的用户才能够访问该存储过程，这样可以保证数据库的安全性。
>  *  **4. 可以方便管理**：通过对象栏右键procedures新建，可以方便地管理存储过程，包括添加、修改、删除等操作，同时也可以方便地查看存储过程的属性和参数信息。
> 
> 特别是工作时候在生产环境数据库，最好通过对象栏右键procedures新建的方式来创建存储过程，这样可以避免上述的问题，保证系统的可靠性和安全性。

[7cdac48925544811b92a575330f15ee5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/26b37778861847819866b1041c00d757.png
[c9e6411b59f84ecfb5bfe67084ce11ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/dac108f9f4db42bb8574e7c3f24ff647.png
[26f813fcf2de4b5e9d270a91f73d8096.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d17cad1f7613476abac954f43c1a46d9.png
[eb3168c83a55452d9b364033d2d880a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/1c7d890e284748bc8eb361488162912c.png
[41cb79c744d04459a0a4f56193146ec6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b7ab87190b804ecf9435037f2538659b.png
[cec1a9a0f0f04da3af9caecbf0fadf76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ca66d2d773f54bb29b855ef70bb88153.png
[0f6e885796a74389b55ba33c3dbe6946.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/fa6d0cf0c32e447ca3e236fc67d83b0a.png
[7aedfe6aebc14c3bbb3768bd4c7179d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cc6ec815b381459182d3f06e446bf5be.png
[0c4a6634e10349c38b772c448df56c1c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/c2aa05bffe744500bfeff62ada3b15c5.png