存储过程
创建存储过程
//创建测试表 test_tab
create table test_tab (id int primary key, name varchar(30));
//创建有参数存储过程 p_test
create or replace procedure p_test(i in int)
as j int;
begin
for j in 1 ..i loop
insert into test_tab values(j,'p_test'||j);
end loop;
end;
调用存储过程
p_test(3);
//对表 test_tab 进行查询
select * from test_tab;
无参数存储过程创建
//创建无参数存储过程 p_test2
create or replace procedure p_test2 as j int;
begin
for j in 10 ..13 loop
insert into test_tab values(j,'p_test2:'||j);
end loop;
end;
无参数存储过程调用
p_test2;
包
通过包来创建应用程序或者使用包来管理过程和函数
创建包
包括两部分:
创建包规范
创建包主体
创建包规范
语法
CREATE [OR REPLACE] PACKAGE [IF NOT EXISTS] [<模式名>.]<包名> [WITH ENCRYPTION] [<调用者权限>] AS|IS <包内声明列表> END [包名]
<调用者权限>::=AUTHID DEFINER |
AUTHID CURRENT_USER
<包内声明列表>::= <包内声明>;{<包内声明>;}
<包内声明>::= <变量列表定义>|<游标定义>|<异常定义>|<过程定义>|<函数定义>|<类型声明>
<变量列表定义>::= <变量定义>{<变量定义>}
<变量定义>::= <变量名><变量类型>[<赋值标识><表达式>];
<变量类型>::=<DMSQL程序类型> |
[<模式名>.]<表名>.<列名>%TYPE |
[<模式名>.]<表名>%ROWTYPE> |
<记录类型>
<赋值标识>::=DEFAULT |
ASSIGN |
:=
<记录类型>::= RECORD(<变量名> <DMSQL程序类型>;{<变量名> <DMSQL程序类型>;})
<游标定义>::= CURSOR <游标名> [FOR <查询语句>]
<异常定义>::= <异常名> EXCEPTION [FOR <异常码>]
<过程定义>::= PROCEDURE <过程名> <参数列表>
<函数定义>::= FUNCTION <函数名><参数列表> RETURN <返回值数据类型>[RESULT_CACHE] [DETERMINISTIC] [PIPELINED]
<类型声明>::= TYPE <类型名称> IS <数据类型>
流程图
注意
- 创建包的名称不能与系统创建的模式名称相同;
- 包部件可以以任意顺序出现,其中的对象必须在引用之前被声明;
- 过程和函数的声明都是前向声明,包规范中不包括任何实现代码;
创建包主体
语法
CREATE [OR REPLACE] PACKAGE BODY [<模式名>.]<包名> [WITH ENCRYPTION] AS|IS <包体部分> END [包名]
<包体部分> ::= <包体声明列表> [<初始化代码>]
<包体声明列表> ::=<包体声明>[{,<包体声明>}]
<包体声明>::=<变量定义>|<游标定义>|<异常定义>|<过程定义>|<函数定义>|<类型声明> |<存储过程实现>|<函数实现>
<变量定义> ::= <变量名列表> <数据类型> [<默认值定义>]
<游标定义> ::= CURSOR <游标名> [FOR <查询语句>]
<异常定义> ::= <异常名> EXCEPTION [FOR <异常码>]
<过程定义> ::= PROCEDURE <过程名> <参数列表>
<函数定义> ::= FUNCTION <函数名> <参数列表> RETURN <返回值数据类型>
<类型声明> ::= TYPE <类型名称> IS <数据类型>
<存储过程实现> ::= PROCEDURE <过程名> <参数列表> AS|IS BEGIN <实现体> END [<过程名>];
<函数实现> ::= FUNCTION <函数名><参数列表> RETURN <返回值数据类型>[DETERMINISTIC] [PIPELINED]<AS|IS> BEGIN <实现体> END [<函数名>];
<初始化代码> ::= [[<说明部分>]BEGIN<执行部分>[<异常处理部分>]]
<说明部分> ::=[DECLARE]<说明定义>{<说明定义>}
<说明定义>::=<变量列表说明>|<异常变量说明>|<子游标定义>|<子过程定义>|<子函数定义>;
<变量列表说明>::= <变量初始化>{<变量初始化>}
<记录类型>::= RECORD(<变量名> <DMSQL程序类型>;{<变量名> <DMSQL程序类型>;})
<异常变量说明>::=<异常变量名>EXCEPTION[FOR<错误号>]
<子游标定义>::=cursor <游标名> [FOR<查询表达式>|<连接表>]
<子过程定义>::=PROCEDURE<过程名>[(<参数列>)]<IS|AS><模块体>
<子函数定义>::=FUNCTION<函数名>[(<参数列>)]RETURN<返回数据类型><IS|AS><模块体>
<执行部分>::=<SQL过程语句序列>{< SQL过程语句序列>}
<SQL过程语句序列>::=[<标号说明>]<SQL过程语句>;
<标号说明>::=<<<标号名>>>
<SQL过程语句>::=<SQL语句>|<SQL控制语句>
<异常处理部分>::=EXCEPTION<异常处理语句>{<异常处理语句>}
<异常处理语句>::= WHEN <异常名> THEN < SQL过程语句序列>;
流程图
重编译包
用于检验包的正确性
语法
ALTER PACKAGE [<模式名>.]<包名> COMPILE [CASCADE] [DEBUG];
1.< 模式名 > 指明被重编译的包所属的模式;
2.< 包名 > 指明被重编译的包的名字;
3.[CASCADE] 当指定 CASCADE 后,将级联重编译所有直接或间接引用该包的对象,需要考量影响范围,建议谨慎使用;
4.[DEBUG] 可忽略。
流程图
删除包
分为:
删除包规范
删除包主体
删除包规范
语法
DROP PACKAGE [IF EXISTS] [<模式名>.]<包名>;
1.< 模式名 > 指明被删除的包所属的模式,缺省为当前模式;
2.< 包名 > 指明被删除的包的名字。
流程图
说明
1.删除不存在的包规范会报错。若指定 IF EXISTS 关键字,删除不存在的包规范,不会报错;
2.如果被删除的包不属于当前模式,必须在语句中指明模式名;
3.如果一个包规范被删除,那么对应的包主体被自动删除。
删除包主体
语法
DROP PACKAGE BODY [IF EXISTS] [<模式名>.]<包名>;
流程图
说明
- 删除不存在的包主体会报错。若指定 IF EXISTS 关键字,删除不存在的包主体,不会报错;
- 如果被删除的包不属于当前模式,必须在语句中指明模式名。
参考:
https://eco.dameng.com/