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简单分析有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注编程网数据库频道，感谢大家的支持。