CREATE PROCEDURE

说明

创建一个存储过程

语法

create_procedure ::=

plsql_procedure_source ::=

parameter_declaration ::=

inout_option ::=

nullable ::=

default_option ::=

参数

OR REPLACE

OR REPLACE PROCEDURE 将要么创建一个新的存储过程，要么替换现有的定义

procname

新建存储过程的名字，可以有大纲修饰，并且必须符合标识符规则。有关更多信息，请参见 关键字和标识符 。

AUTHID {CURRENT_USER | DEFINER}

AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用过程中的方法时所使用的权限模式。若不指定AUTHID参数，则调用权限验证默认是DEFINER，也就是存储过程执行时，以这个过程的创建者的身份来验证存取权限；如果指定AUTHID为CURRENT_USER，则在执行存储过程时根据调用者的身份来验证存取权限。

argname

参数的名字，必须符合标识符规则。

在同一个存储过程里，各个参数名不能重复。

在调用存储过程的时候，可以使用参数名来对参数赋值，不过如果使用参数名来对参数赋值必须在按序赋值的参数之后。参见示例。

IN | OUT | INOUT | IN OUT

参数输入/输出类型

  • IN 表示该参数是输入参数，在存储过程内不可改变传入的参数值
  • OUT 表示该参数是输出参数，在存储过程内初始为空
  • INOUT | IN OUT 表示该参数是输入输出参数

缺省则为 IN

datatype

参数类型，输入类型可以是基本类型，也可以是复合类型，域类型，或者和一个现有字段相同的类型．一个字段的类型是用 tablename.columnname%TYPE 表示的；使用这个东西可以帮助函数独立于表定义的修改。

NULL | NOT NULL

参数是否可以为空，缺省则为 NULL

  • NULL 表示该参数可以为空
  • NOT NULL 表示该参数不能为空

DEFAULT value := value

为该参数申明一个默认值

默认值必须是一个常量，不能是一个含有运算符的表达式('-'当作取负使用除外)

WITH ENCRYPTION

需要对源码进行加密

proc_body

存储过程体，有关说明请参见 PL过程语言开发手册 。

lanname

过程语言名，现有的过程语言为 plOSCAR 、 SQL。

注解

  • 参数个数最大值为 1024。
  • LANGUAGE SQL: 对于语言为SQL的过程，由SQL语句组成，绝大部分SQL语句都可以出现在过程里面，但是事务处理语句不能。
  • 执行存储过程: 存储过程在神通数据库里面用 EXEC procedure([ args ][, ... ])的方式来执行。请参见 EXEC 以获得更多信息。
  • 存储过程的prepare: 可以对一个存储过程进行PREPARE，请参见 PREPARE 以获得更多信息。
  • 对Oracle的兼容: 神通数据库在存储过程的创建语法上对Oracle进行兼容。

示例

示例1: 创建存储过程

--  清理环境
DROP PROCEDURE proc1 CASCADE;


--  创建存储过程
CREATE OR REPLACE PROCEDURE proc1(i INOUT INT, j INT) AS
    res INT;
BEGIN
    res := i + j;
    i := res;
END;
LANGUAGE 'ploscar';


--  执行存储过程
EXEC proc1(1, 2);
I(int)      |
------------
3           |
总数目：1


--  删除存储过程
DROP PROCEDURE proc1;

示例2: 创建存储过程(兼容 Oracle)

--  清理环境
DROP PROCEDURE proc2 CASCADE;


--  创建存储过程(兼容oracle)
CREATE OR REPLACE PROCEDURE proc2(i IN INT, j OUT INT) AS
BEGIN
    j := 100 / i;
    EXCEPTION
    WHEN ZERO_DIVIDE THEN
        j := 0;
END proc2;

/


--  执行存储过程
EXEC proc2(5, 9);
J(int)      |
------------
20          |
总数目：1


--  删除存储过程
DROP PROCEDURE proc2;

示例3: 创建存储过程(应用程序在调用过程中的方法时所使用的不同权限模式)

--  清理环境
DROP PROCEDURE PROC1 CASCADE;
DROP PROCEDURE PROC2 CASCADE;
DROP TABLE TAB1 CASCADE;
DROP USER U1 CASCADE;

CREATE USER U1 WITH PASSWORD '123';

CREATE TABLE TAB1(A INT);

--  创建存储过程，应用程序在调用过程中的方法时所使用的不同权限模式
--  以这个过程的创建者的身份来验证存取权限；
CREATE OR REPLACE PROCEDURE proc1 IS
BEGIN
	SELECT RELNAME FROM SYS_CLASS WHERE RELNAME='TAB1';
END;
/
--  以这个过程的调用者的身份来验证存取权限；
CREATE OR REPLACE PROCEDURE proc2 AUTHID CURRENT_USER IS
BEGIN
	SELECT RELNAME FROM SYS_CLASS WHERE RELNAME='TAB1';
END;
/

GRANT EXECUTE ON PROCEDURE SYSDBA.PROC1 TO U1;
GRANT EXECUTE ON PROCEDURE SYSDBA.PROC2 TO U1;


CONNECT U1/123;
--  执行存储过程
EXEC SYSDBA.PROC1;
RELNAME(name)      |
-------------------
TAB1               |
总数目：1

EXEC SYSDBA.PROC2;
ERROR, 表或视图 "INFO_SCHEM"."SYS_CLASS" 不存在或无权访问


CONNECT SYSDBA/szoscar55;

--  清理环境
DROP PROCEDURE PROC1;
DROP PROCEDURE PROC2;
DROP TABLE TAB1;
DROP USER U1;

示例4: 创建存储过程时对源码加密

 --  清理环境
 DROP PROCEDURE MyEncryptedProcedure;
 
 --创建存储过程
 CREATE PROCEDURE MyEncryptedProcedure
 WITH ENCRYPTION
 AS
 BEGIN
     -- 存储过程的代码
     SELECT 1 FROM dual;
 END

 ;
 /

select owner,name,type,text from dba_source where name='MYENCRYPTEDPROCEDURE';
OWNER(varchar)      |NAME(varchar)      |TYPE(varchar)      |TEXT(text)      |
--------------------------------------------------------------------------
SYSDBA              |MYENCRYPTEDPROCEDUR|PROCEDURE          | WITH ENCRYPTION|
                    |E                  |                   |: 31BAE3A54A652|
                    |                   |                   |850966CCEE2D9D9|
                    |                   |                   |0BFEF96D914BD21|
                    |                   |                   |0E9FE11E82B8A3E|
                    |                   |                   |80955EBBD86772D|
                    |                   |                   |970619794D0E11D|
                    |                   |                   |CF35D87B        |
总数目：1

--  清理环境
DROP PROCEDURE MyEncryptedProcedure;