虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


存储过程 ​

📄字数 5.2K
👁️阅读量 加载中...

存储过程是数据库中的一种重要对象，用于封装为实现特定功能而编写的一组 SQL 语句。它支持参数化调用，包括输入参数、输出参数和输入输出参数，也支持无参数调用。存储过程在创建时预编译，后续可通过名称和参数直接执行，提高了执行效率和代码复用性。

存储过程的使用场景有：

  • ‌数据验证和业务逻辑处理。
  • 多条SQL语句需批量执行。
  • 多语句场景下提升性能。

一、创建存储过程 ​

1.1 语法格式 ​

(VarDefList::= , StmtBlock::=)

1.2 参数说明 ​

  • Cre_Rep：可选项，存储函数创建语句，若忽略该语句效果等价于CREATE。

    • OR REPLACE：指定存储过程存在则替换，用于更新存储过程定义。
    • NOFORCE：不强制创建或替换，默认。
    • FORCE：强制创建或替换。

  • ProcDef：过程定义语句。

    • ProcDecl：过程声明。
      • IF NOT EXISTS：创建存储过程时存在同名存储过程则忽略此错误，该关键字无法判断已有同名存储过程与当前创建存储过程属性是否一致。
      • schema_name：存储过程所属模式名。
      • proc_name：存储过程名。
      • func_args：形式参数定义（包含参数名称、模式、类型、默认值等）。
        • func_arg：单个参数定义。
          • arg_name：参数名称。
          • IN: 表示输入参数。
          • OUT：表示输出参数，在过程内部被赋值，修改结果会返回给调用者。
          • IN OUT。它可以将实参传递进过程。在过程的内部，形参可以被读取也可以被写入。在过程结束时，形参的内容同时也被赋给实参。
          • TypeName：对应参数的数据类型。
          • (ASSIGN | DEFAULT) b_expr：可选的默认值表达式。
      • opt_authid：权限。
        • AUTHID DEFAULT：默认权限。
        • AUTHID DEFINER：使用定义者的权限执行。
        • AUTHID CURRENT_USER | AUTHID USER：使用调用者的权限执行。
    • COMMENT SCONST：添加注释。
    • IS | AS：任选两个关键字中的一个使用。
    • LANGUAGE {PLSQL | C} NAME build_in_proc_name：引用的内部接口，C会去调用系统内建过程，即数据库用c语言方式编译好的过程，此方式build_in_proc_name为内部过程定义的名称；忽略该参数或者指定PLSQL会当成用户自定义过程处理。
    • (schema_name '.')? proc_name：可选项，ProcDef语法最后的过程名，需和ProcDecl中定义的过程名保持一致，否则会报错。

注意

当def_compatible_mode设置为Oracle模式时，创建存储过程/函数会默认强制创建，忽略掉除语法、编译等错误之外的错误，相当于虚谷的CREATE OR REPLACE FORCE方式，创建成功但是存在错误的存储过程会是一个无效的存储过程，def_compatible_mode设置为其他模式时，和默认情况一致。

1.3 示例 ​

示例1：
无参数存储过程。创建无参存储过程，其封装了一个循环执行体与一条消息输出命令。循环体循环10次向test_proc_tab中插入数据，完成循环执行后输出循环次数消息。

sql
SQL> CREATE TABLE test_proc_tab(id INT,dtime TIME);

SQL> CREATE OR REPLACE PROCEDURE proc_test() IS
      loop_num INT;
     BEGIN
      loop_num := 0;
      FOR i IN 1 .. 10 LOOP
        INSERT INTO test_proc_tab (id, dtime) VALUES (i, current_time);
        loop_num := loop_num + 1;
      END LOOP;
      SEND_MSG('过程执行完成' || loop_num || '次');
      COMMIT;
    END;
    /

SQL> EXEC proc_test();

  -- 输出
  过程执行完成10次

SQL> SELECT COUNT(*) FROM test_proc_tab;

  +-------+
  | EXPR1 |
  +-------+
  | 10    |
  +-------+

示例2：
带输入型参数的存储过程。创建一个带输入参数的存储过程proc_test2，通过输入参数parameter控制向表test_proc_tab中插入数据条数，执行插入操作后输出插入次数信息。

sql
SQL> CREATE OR REPLACE PROCEDURE proc_test2(parameter INTEGER) AS
        x int;
     BEGIN
        x := 0;
        FOR i IN 1 .. parameter LOOP
          INSERT INTO test_proc_tab VALUES (i, sysdate);
          x := x + SQL%ROWCOUNT;
        END LOOP;
        SEND_MSG('共插入：' || x || '次');
        COMMIT;
     END;
     /

SQL> EXEC proc_test2(3);

  -- 输出
  共插入：3次

SQL> SELECT COUNT(*) FROM test_proc_tab;

+-------+
| EXPR1 |
+-------+
| 13    |
+-------+

示例3：
带输出型参数的存储过程。创建一个带输出参数的存储过程proc_test3，循环10次向test_proc_tab表中插入数据，并将循环次数赋值给输出参数；块语句执行存储过程proc_test3，并打印输出参数信息。

sql
SQL>  CREATE OR REPLACE PROCEDURE proc_test3(parameter OUT INTEGER) AS
        x int;
      BEGIN
        x := 0;
        FOR i IN 1 .. 10 LOOP
          INSERT INTO test_proc_tab VALUES (i, sysdate);
          x := x + SQL%ROWCOUNT;
        END LOOP;
        parameter := x;
      END;
      /

SQL>  DECLARE
        OUTRET INT;
      BEGIN
        EXEC proc_test3(OUTRET);
        SEND_MSG('共插入：' || OUTRET || '次');
      END;
      /

  -- 输出
  共插入：10次

SQL>  SELECT COUNT(*) FROM test_proc_tab;

+-------+
| EXPR1 |
+-------+
| 23    |
+-------+

示例4：
创建存储过程支持IF NOT EXISTS关键字。创建一个与已有存储过程同名但属性不同的存储过程，不会改变原存储过程。

sql
SQL> CREATE PROCEDURE proc_pre1 AS
     BEGIN
        send_msg(123);
     END;
     /
SQL> EXEC pro_pre1;

SQL> CREATE PROCEDURE IF NOT EXISTS proc_pre1 AS  
     BEGIN
        send_msg(1234566);
     END; /*创建一个与原存储过程内容不同的存储过程，此处会返回警告*/
     /
     
SQL> EXEC pro_pre1; /*不会对原存储过程产生影响*/

示例5：
创建存储过程使用FORCE。强制创建一个有错误的存储过程，插入时col3不存在，若无FORCE创建则会报错。此处示例使用FORCE强制创建，不会报错Error但会返回警告Warning，并且创建的存储过程无效。

sql
-- 创建表tb_var
SQL> CREATE TABLE tb_var(var1 VARCHAR(30),var2 CHAR(3));

-- 强制创建存储过程proc_pre2
SQL> CREATE OR REPLACE FORCE PROCEDURE proc_pre2(col1 VARCHAR,col2 VARCHAR) IS
     BEGIN
        INSERT INTO tb_var VALUES(col1,col3);
     END;
     /
-- 返回警告col3不存在
Warning: [E19212] 代码编译错误
[E19182 L3 C33] 编译SQL失败字段变量或函数"COL3"不存在

-- 查询创建的存储过程proc_pre2
SQL> SELECT proc_name,valid FROM DBA_PROCEDURES;

+------------+-------+
| PROC_NAME  | VALID |
+------------+-------+
| PROC_PRE2  | F     |
+------------+-------+

-- 调用存储过程proc_pre2，返回错误存储过程无效
SQL> EXEC proc_pre2;
Error: [E8014] 存储过程或函数PROC_PRE2被标识为失效

示例6：
创建存储过程带默认值。

sql
-- in参数带默认值
SQL> CREATE TABLE in_para_tab1(id INT);

SQL> CREATE PROCEDURE in_para_pro1(a IN INT DEFAULT 10) AS
     BEGIN
      INSERT INTO in_para_tab1 VALUES(a);
     END;
     /

SQL> EXEC in_para_pro1;

SQL> SELECT * FROM in_para_tab1;

+----+
| ID |
+----+
| 10 |
+----+

-- in out参数带默认值
SQL> CREATE TABLE in_out_para_tab1(id INT);

SQL>  CREATE PROCEDURE in_out_para_pro1(a IN OUT INT DEFAULT 123)
      AS
      BEGIN
        INSERT INTO in_out_para_tab1 VALUES(a);
      END;
      /

SQL> EXEC in_out_para_pro1;

SQL> SELECT * FROM in_out_para_tab1;

+-----+
| ID  |
+-----+
| 123 |
+-----+

示例7：
创建存储过程带运行者身份类型，此处指定运行者为定义者的身份。

sql
SQL>  CREATE TABLE msg12(d1 NUMERIC(3,2));

SQL>  DECLARE
        x NUMBER := 1;
        y VARCHAR2(1) := '1';
      BEGIN
        PROCEDURE p (
        n NUMBER
        ) AUTHID DEFINER IS
        BEGIN
          INSERT INTO msg12 VALUES(n);
          NULL;
        END;
        p(x); 
        p(y); 
        p(TO_NUMBER(y)); 
      END;
      /

SQL> SELECT * FROM msg12;

+----+
| D1 |
+----+
| 1  |
| 1  |
| 1  |
+----+

二、删除存储过程 ​

2.1 语法格式 ​

2.2 参数说明 ​

  • IF EXISTS：删除时存储过程不存在，忽略此错误。
  • CASCADE | RESTRICT：可选项，默认使用RESTRICT，在删除存储过程时会检测存储过程是否被其他对象依赖，若存在依赖则删除失败，若无依赖则删除成功；CASCADE则表示强制删除存储过程，强制删除会无视依赖关系删除掉依赖对象。

2.3 示例 ​

由于存储过程pro2依赖于pro1，因此在尝试删除pro1时，数据库阻止了这一操作以保护依赖关系。使用CASCADE强制删除。

sql
-- 创建存储过程pro1
SQL> CREATE OR REPLACE PROCEDURE pro1 IS
     BEGIN
       DBMS_OUTPUT.PUT_LINE('abc');
     END;
     /

-- 创建存储过程pro2
SQL> CREATE PROCEDURE pro2 IS
     BEGIN
       EXEC pro1;
     END;
     /

-- 默认方式删除pro1返回错误
SQL> DROP PROCEDURE pro1;
Error: [E9002] 存在对存储过程或函数PRO1依赖的对象

SQL> SELECT valid FROM user_procedures WHERE proc_name='PRO2';

+-------+
| VALID |
+-------+
| T     |
+-------+

-- 强制删除pro1
SQL> DROP PROCEDURE pro1 CASCADE;

SQL> SELECT valid FROM user_procedures WHERE proc_name='PRO2';

+-------+
| VALID |
+-------+
| F     |
+-------+

三、重编译存储过程 ​

重编译存储过程是指在依赖对象发生变化后，数据库中的存储过程因失效而不能正常工作，此时需要通过重新编译的方式恢复其可用性。

3.1 语法格式 ​

3.2 示例 ​

步骤1：

创建存储过程pro1和pro2，pro2依赖于pro1。

sql
-- 创建存储过程pro1
SQL> CREATE OR REPLACE PROCEDURE pro1 IS
     BEGIN
      DBMS_OUTPUT.PUT_LINE('abc');
     END;
     /

-- 创建存储过程pro2
SQL> CREATE PROCEDURE pro2 IS
     BEGIN
      EXEC pro1;
     END;
     /

步骤2：
强制删除pro1，导致pro2失效。

sql

-- 强制删除存储过程pro1
SQL> DROP PROCEDURE pro1 CASCADE;  

-- 存储过程pro2已失效
SQL> SELECT valid FROM user_procedures WHERE proc_name='PRO2';

+-------+
| VALID |
+-------+
| F     |
+-------+

步骤3：
重新创建pro1，并对pro2进行重编译，功能恢复。

sql
-- 重新创建存储过程pro1
SQL> CREATE OR REPLACE PROCEDURE pro1 IS
     BEGIN
       DBMS_OUTPUT.PUT_LINE('abc');
     END;
     /

-- 此时pro2依然失效
SQL> SELECT valid FROM user_procedures WHERE proc_name='PRO2';
    
+-------+
| VALID |
+-------+
| F     |
+-------+

-- 重编译pro2
SQL> ALTER PROCEDURE pro2 RECOMPILE;

-- 存储过程pro2状态恢复为有效(VALID)
SQL> SELECT valid FROM user_procedures WHERE proc_name='PRO2';

+-------+
| VALID |
+-------+
| T     |
+-------+

四、反汇编存储过程 ​

将存储过程反汇编为类似汇编语言的指令码输出。

4.1 语法格式 ​