Oracle存储过程中Procedure简单分析

今天就跟大家聊聊有关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;

看完上述内容，你们对Oracle存储过程中Procedure简单分析有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。