创建存储过程

主要语法结构

语法格式

CreProcedureStmt::=
    {CREATE | CREATE OR REPLACE | CREATE OR REPLACE FORCE} ProcDef

ProcDef::=
    ProcDecl [COMMENT SCONST] {IS | AS} LANGUAGE {PLSQL | C} NAME ColId
|   ProcDecl [COMMENT SCONST] {IS | AS} [DECLARE] VarDefList StmtBlock [name_space]    

ProcDecl::=
    PROCEDURE [IF NOT EXISTS] name_space func_args [AUTHID {DEFAULT | USER | ColId}]

参数说明

• CREATE OR REPLACE：可选关键字。如果使用了OR REPLACE，则在创建过程时，首先会检测数据库中是否存在同名存储过程，如果存在则替换该存储过程，如果不存在则创建。
• CREATE OR REPLACE FORCE：可选关键字。如果使用OR REPLACE FORCE，则在创建时会强制创建存储过程，忽略除语法、编译错误之外的错误，返回一个警告，有错误但创建成功的存储过程是一个无效的存储过程。
• IF NOT EXISTS：创建存储过程时存在同名存储过程则忽略此错误，该关键字无法判断已有同名存储过程与当前创建存储过程属性是否一致。
• name_space：定义的存储过程、存储函数名称。
• func_args：形式参数信息，注意不要使用系统关键字作为形式参数名。
• ColId：引用的内部接口名称。
• VarDefList：变量定义语句。包括变量定义、游标定义、异常定义等。详细信息请参见PL/SQL语言 > PL/SQL语法章节。
• StmtBlock：BEGIN...END块语句执行体，若执行体包含DDL语句，则需用动态SQL方式进行，如EXECUTE IMMEDIATE('$SQL');。详细信息请参见PL/SQL语言 > PL/SQL语法章节。

注意：

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

形式参数func_args

语法格式

func_args::=
    (FuncArg [,FuncArg]...)
|   ()

FuncArg::=
  ColLabel [IN | OUT | IN OUT] TypeName [DEFAULT b_expr]

参数说明

• ColLabel：参数名称。
• IN：表示该参数为输入型参数，即调用时采用值传递，不能携带返回值给调用者。若未明确指示参数属性，则默认该参数为输入型参数。
• OUT：表示该参数可用于携带返回值给调用者。在过程执行中的主要任务是被赋值。当过程结束时，形参会被赋给实参，返回给调用者。
• IN OUT：综合IN和OUT。它可以将实参传递进过程。在过程的内部，形参可以被读取也可以被写入。在过程结束时，形参的内容同时也被赋给实参。
• TypeName：对应参数的数据类型，详细信息请参见PL/SQL语言 > PL/SQL语法章节。
• [DEFAULT b_expr]：可选的默认值表达式。

示例

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

  CREATE TABLE test_proc_tab(id INT,dtime TIME);
  
  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;
  
  EXEC proc_test();
  
  -- 输出
  过程执行完成10次
  
  SELECT COUNT(*) FROM test_proc_tab;
  
  EXPR1 | 
  ------------------------------------------------------------------------------
  10 |

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

  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;
  
  EXEC proc_test2(3);
  
  -- 输出
  共插入：3次
  
  SELECT COUNT(*) FROM test_proc_tab;
  
  EXPR1 | 
  ------------------------------------------------------------------------------
  13 |

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

  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;
  
  DECLARE
    OUTRET INT;
  BEGIN
    EXEC proc_test3(OUTRET);
    SEND_MSG('共插入：' || OUTRET || '次');
  END;
  
  -- 输出
  共插入：10次
  
  SELECT COUNT(*) FROM test_proc_tab;
  
  EXPR1 | 
  ------------------------------------------------------------------------------
  23 |

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

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

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

  -- 创建表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;
      /
  Execute successful.
  Use time:3 ms.  
  
  -- 返回警告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被标识为失效