【Oracle系列】- 存储过程(Stored Procedure)
一、什么是存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储过程在数据库中,经过第一次编译后再次调用不需要二次编译。用户通过指定的存储过程名称应传给参数来调用完成。
存储过程就是解决特定问题,有先后顺序的一组SQL语句集合。
在Oracle数据库中存储过程是Procedure。
二、存储过程优缺点
-
存储过程优点
-
效率高
存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的SQL语句在代码中,都要先分析编译才会执行。所以想对而言存储过程效率更高。
-
降低网络流量
存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的SQL语句。
-
复用性高
存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。
-
可维护性高
当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。
-
安全性高
完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。
-
-
存储过程缺点
- 增加数据库负荷: 存储过程编译存储在数据库服务端增加了数据库的负荷。
- 存储过程编辑编译困难:因存储过程编写一般在DBMS中完成,相对于代码编辑器,调试编写都不那么友好。
- 可移植性差:不同的数据库有独特的编写方法,不同达到一次编译多个数据库同时使用。
三、存储过程创建示例
3.1 创建语法
create or replace procedure schema.过程名(变量名 in|out 数据类型)
is | as -- 此处 is 和 as 等同
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end 过程名;
3.2 创建存储过程步骤
Oracle存储过程大体分为这么几个部分:
-
创建语句:create or replace procedure 存储过程名
如果没有or replace语句,则仅仅是新建一个存储过程。如果系统存在该存储过程,则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个,如果系统中有此存储过程则把原来删除掉,重新创建一个存储过程。
-
存储过程名定义:包括存储过程名和参数列表
参数名和参数类型。参数名不能重复, 参数传递方式:IN, OUT, IN OUT
IN 表示输入参数,按值传递方式。
OUT 表示输出参数,可以理解为按引用传递方式。可以作为存储过程的输出结果,供外部调用者使用。
IN OUT 即可作输入参数,也可作输出参数。
- 参数的数据类型只需要指明类型名,不需要指明参数类型
- 参数类型的宽度由外部调用者决定
- 过程可以有参数,也可以没有参数
-
变量声明块
紧跟着的as (is )关键字,可以理解为pl/sql的declare关键字,用于声明变量;
变量声明块用于声明该存储过程需要用到的变量,它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
-
过程语句块
从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
-
异常处理块
关键字为exception ,为处理语句产生的异常。该部分为可选
-
结束块
由end关键字结果
3.3 创建一个简单无参存储过程
create or replace procedure pro_noparameters
is
v_depte_id integer:= '';
begin
select dept_id into v_depte_id from sys_dept where dept_id = 100;
dbms_output.put_line('v_depte_id:' || v_depte_id);
end;
3.4 创建一个有入参的存储过程
create or replace procedure pro_parameters(
in_deptid in number
)
as
v_dept_name varchar2(30):='';
begin
select DEPT_NAME into v_dept_name from sys_dept where dept_id= in_deptid;
dbms_output.put_line('v_dept_name:' || v_dept_name);
end pro_parameters;
3.5 创建一个存储过程并返回结果集
create or replace procedure pro_showdata(data out sys_refcursor)
as
begin
open data for select * from sys_dept;
end pro_showdata;
3.6 存储过程中SELECT INFO
查出来一个结果集合赋值给一个集合变量。
-
语法
SELECT field BULK COLLECT INTO var_conllect FROM table where whereColStatement;
-
参数说明:
序号 | 参数 | 说明 |
---|---|---|
1 | field | 要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应) |
2 | var_collect | 集合变量(联合数组等),用来存放查到的结果 |
3 | table | 表名,要查询的表 |
4 | whereColStatement | 后面过滤条件语句 |
3.7 存储过程中FETCH INTO
从一个集合中抓取一部分数据赋值给一个集合变量
-
语法
FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]
-
参数说明
序号 | 参数 | 说明 |
---|---|---|
1 | cur1 | 数据集合 |
2 | var_collect | 数据集合 |
3 | [LIMIT rows] | 可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据 |
3.8 存储过程中RETURNING
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)
-
语法:
DMLStatement
RETURNING field BULK COLLECT INTO var_field;
-
参数说明:
序号 | 参数 | 说明 |
---|---|---|
1 | DMLStatement | 是一个DML语句 |
2 | field | 是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3) |
3 | var_field | 一个类型为该字段类型的集合,多个的话用逗号隔开,如:(var_field1, var_field2, var_field3) |
3.9 存储过程中FORALL
-
语法:
FORALL index IN bounds [SAVE EXCEPTIONS]
sqlStatement;
-
参数说明:
序号 | 参数 | 说明 |
---|---|---|
1 | index | 执行过程游标 |
2 | bounds | 游标边界,形式是start…end |
3 | [SAVE EXCEPTIONS] | 可选值,(后面详细介绍) |
4 | sqlStatement | 一个DML语句,这里有且仅有一个sql语句 |
3.10 存储过程中SAVE EXCEPTIONS
在执行DML时,遇到异常,导致某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来;如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段分别是:ERROR_INDEX和ERROR_CODE
ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号
ERROR_CODE:存储对应异常的,oracle错误代码
SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。
而这个异常记录集合异常的个数则由它的COUNT属性表示,即:SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。
3.11 存储过程中 INDICES OF
在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF
-
语法:
FORALL i INDICES OF collection [SAVE EXCEPTIONS]
sqlStatement;
3.12 存储过程中VALUES OF
VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:
如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;
当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;文章来源:https://www.toymoban.com/news/detail-464144.html
-
语法:
FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
sqlStatement;
四、总结
Oracle存储过程相对于Sql Service 和 Mysql比较复杂,难度较大。在解决实际问题需要根据场景选择响应的存储过程方案。文章来源地址https://www.toymoban.com/news/detail-464144.html
到了这里,关于【Oracle系列】- 存储过程(Stored Procedure)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!