Oracle存储过程中Procedure简单分析

63次阅读

共计 3302 个字符，预计需要花费 9 分钟才能阅读完成。

今天就跟大家聊聊有关 Oracle 存储过程中 Procedure 简单分析，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

这里我们来讨论存储过程的一些高级选项：cursor 游标
游标是 SQL 的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据的 io 交换会降低效率，影响速度。这也是引入游标概念的一个原因。

cursor 游标可以分为三种类型：隐式 cursor、显式 cursor 和动态 (ref) cursor
1、首先介绍一下隐式 cursor
对于 select…into…语句，一次只能从数据库中获取到一条数据，对于这种类型的 DML Sql 语句，就是隐式 Cursor。例如：Select / Update / Insert/ Delete 操作。
作用：可以通过隐式 cursor 的属性来了解操作的状态和结果，从而达到流程的控制。Cursor 的属性包含：
SQL%ROWCOUNT 整型代表 DML 语句成功执行的数据行数
SQL%FOUND 布尔型值为 TRUE 代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型与 SQL%FOUND 属性返回值相反
SQL%ISOPEN 布尔型 DML 执行过程中为真，结束后为假
隐式 Cursor 是系统自动打开和关闭 Cursor.

下面举个例子：
create or replace procedure Obj_up as
begin
update obj set object_name = MyMarry where object_id = 3
if SQL%Found then
dbms_output.put_line(Object_name is updated successfully!
commit;
else
dbms_output.put_line(Object_name is updated failed!
end if;
end;
执行这个存储过程
begin
— Call the procedure
obj_up;
end;

2、然后介绍一下显式 cursor
对于从数据库中提取多行数据，就需要使用显式 Cursor。显式 Cursor 的属性包含：
游标的属性返回值类型意义
%ROWCOUNT 整型获得 FETCH 语句返回的数据行数
%FOUND 布尔型最近的 FETCH 语句返回一行数据则为真，否则为假
%NOTFOUND 布尔型与 %FOUND 属性返回值相反
%ISOPEN 布尔型游标已经打开时值为真，否则为假

对于显式游标的运用分为四个步骤：
定义游标—Cursor [Cursor Name] IS
打开游标—Open [Cursor Name]
操作数据—Fetch [Cursor name]
关闭游标—Close [Cursor Name]这个步骤不要遗漏。

下面举个例子：
create or replace procedure proc_salary is
– 定义变量
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
– 定义游标
CURSOR emp_cursor IS
SELECT empno, ename, sal from emp;
BEGIN
– 循环开始
LOOP
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
FETCH emp_cursor
INTO v_empno, v_ename, v_sal;
– 退出循环的条件
EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL;
dbms_output.put_line(emp no || v_empno || name is || v_ename || salary is ||
v_sal);
END LOOP;
END;
/
执行
begin
proc_salary;
end;

3、最后介绍一下动态 cursor
静态游标在执行前就能确定对应查询语句，最多只是传递一些查询参数而已，所以比较容易处理。动态游标是在执行前查询 SQL 是动态拼接的，不确定具体查询那些表和条件。
与隐式 Cursor, 显式 Cursor 的区别，Ref Cursor 是可以通过在运行期间传递参数来获取数据结果集。而另外两种 Cursor，是静态的，在编译期间就决定数据结果集。
Ref cursor 的使用
Type [Cursor type name] is ref cursor
Define 动态的 Sql 语句
Open cursor
操作数据—Fetch [Cursor name]
Close Cursor

下面举个例子
动态 SQL 作为游标执行的语句，定义时仅说明类型，打开时指定 SQL. 循环处理是采用 loop，所以需要手动结束。

create or replace PROCEDURE PX_VARIFY_ZRP_EXT_MATCH(V_TABLE_NAME IN VARCHAR2,
V_IMPORT_ID IN VARCHAR2) AS
V_SQL VARCHAR2(1024);
VU_SQL VARCHAR2(1024);
TYPE CV_PERSONS IS REF CURSOR;
CV_PERSON CV_PERSONS;
V_ID NAT_PERSON_INFO.ID%TYPE; — ID
V_ID_TYPE NAT_PERSON_INFO.ID_TYPE%TYPE; — 证件类型
V_ID_CODE NAT_PERSON_INFO.ID_CODE%TYPE; — 证件号码
V_PERSON_ID VARCHAR2(36); — 返回主体 ID
V_CNT NUMBER(5, 0); — 符合条件记录数
BEGIN
V_SQL := SELECT ID,ID_TYPE,ID_CODE
V_SQL := V_SQL || FROM || V_TABLE_NAME;
V_SQL := V_SQL || WHERE IMPORT_ID = || V_IMPORT_ID;
OPEN CV_PERSON FOR V_SQL;
LOOP
FETCH CV_PERSON
INTO V_ID, V_ID_TYPE, V_ID_CODE;
EXIT WHEN CV_PERSON%NOTFOUND;
PX_VARIFY_ZRP_IDS_ATOM(V_ID_TYPE, V_ID_CODE, V_PERSON_ID, V_CNT);
IF V_CNT = 1 AND V_PERSON_ID IS NOT NULL THEN
VU_SQL := UPDATE || V_TABLE_NAME || SET PERSON_ID = ||
V_PERSON_ID || WHERE ID = || V_ID ||
END IF;
IF V_CNT = 0 AND V_PERSON_ID IS NULL THEN
VU_SQL := UPDATE || V_TABLE_NAME ||
SET IMPORT_CHECK_FLAG = 0 ,
VU_SQL := VU_SQL || CHECK_ERR_MSG = CHECK_ERR_MSG || ||
验证规则代码:1001; 错误描述: 未找到对应自然人信息.
VU_SQL := VU_SQL || WHERE ID = || V_ID ||
END IF;
DBMS_OUTPUT.PUT_LINE(VU_SQL);
execute immediate VU_SQL;
END LOOP;
执行
begin
PX_VARIFY_ZRP_EXT_MATCH;
end;