oracle存二进制proc,Oracle存储过程Procedure简单介绍（第二部分）

在上一篇文章里我们介绍了存储过程的一些基本概念，这篇文章我们来讨论存储过程的一些高级选项：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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31358702/viewspace-2153894/，如需转载，请注明出处，否则将追究法律责任。