Oracle存儲過程中Procedure簡單分析

今天就跟大家聊聊有關(guān)Oracle存儲過程中Procedure簡單分析，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

這里我們來討論存儲過程的一些高級選項：cursor游標
游標是SQL的一個內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。在某些情況下，需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計算機內(nèi)存中進行處理，最后將處理結(jié)果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度才會提高，否則頻繁的磁盤數(shù)據(jù)的io交換會降低效率，影響速度。這也是引入游標概念的一個原因。


cursor游標可以分為三種類型：隱式cursor、顯式cursor和動態(tài)(ref) cursor
1、首先介紹一下隱式cursor
對于select…into…語句，一次只能從數(shù)據(jù)庫中獲取到一條數(shù)據(jù)，對于這種類型的DML Sql語句，就是隱式Cursor。例如：Select / Update / Insert/ Delete操作。 
作用：可以通過隱式cursor的屬性來了解操作的狀態(tài)和結(jié)果，從而達到流程的控制。Cursor的屬性包含： 
SQL%ROWCOUNT 整型 代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù) 
SQL%FOUND 布爾型 值為TRUE代表插入、刪除、更新或單行查詢操作成功 
SQL%NOTFOUND 布爾型 與SQL%FOUND屬性返回值相反 
SQL%ISOPEN 布爾型 DML執(zhí)行過程中為真，結(jié)束后為假 
隱式Cursor是系統(tǒng)自動打開和關(guān)閉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;
執(zhí)行這個存儲過程
begin
  -- Call the procedure
  obj_up;
end;


2、然后介紹一下顯式cursor
對于從數(shù)據(jù)庫中提取多行數(shù)據(jù)，就需要使用顯式Cursor。顯式Cursor的屬性包含： 
游標的屬性 返回值類型意義
%ROWCOUNT 整型 獲得FETCH語句返回的數(shù)據(jù)行數(shù) 
%FOUND 布爾型 最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假 
%NOTFOUND 布爾型 與%FOUND屬性返回值相反 
%ISOPEN 布爾型 游標已經(jīng)打開時值為真，否則為假


對于顯式游標的運用分為四個步驟： 
定義游標—Cursor [Cursor Name] IS
打開游標—Open [Cursor Name]
操作數(shù)據(jù)—Fetch [Cursor name]
關(guān)閉游標—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
  --循環(huán)開始
  LOOP
    IF NOT emp_cursor%ISOPEN THEN
      OPEN emp_cursor;
    END IF;  
    FETCH emp_cursor
      INTO v_empno, v_ename, v_sal;
    --退出循環(huán)的條件
    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;
/
執(zhí)行
begin
  proc_salary;
end;

3、最后介紹一下動態(tài)cursor
靜態(tài)游標在執(zhí)行前就能確定對應(yīng)查詢語句，最多只是傳遞一些查詢參數(shù)而已，所以比較容易處理。動態(tài)游標是在執(zhí)行前查詢SQL是動態(tài)拼接的，不確定具體查詢那些表和條件。
與隱式Cursor,顯式Cursor的區(qū)別，Ref Cursor是可以通過在運行期間傳遞參數(shù)來獲取數(shù)據(jù)結(jié)果集。而另外兩種Cursor，是靜態(tài)的，在編譯期間就決定數(shù)據(jù)結(jié)果集。 
Ref cursor的使用
Type [Cursor type name] is ref cursor 
Define 動態(tài)的Sql語句 
Open cursor 
操作數(shù)據(jù)—Fetch [Cursor name] 
Close Cursor 

下面舉個例子
動態(tài)SQL作為游標執(zhí)行的語句，定義時僅說明類型，打開時指定SQL.循環(huán)處理是采用loop，所以需要手動結(jié)束。

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); -- 符合條件記錄數(shù)  
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 || ' ||
                ''' 驗證規(guī)則代碼:1001;錯誤描述:未找到對應(yīng)自然人信息.''';
      VU_SQL := VU_SQL || ' WHERE ID = ''' || V_ID || '''';
    END IF;
    DBMS_OUTPUT.PUT_LINE(VU_SQL);
    execute immediate VU_SQL;
  END LOOP;
執(zhí)行
begin
  PX_VARIFY_ZRP_EXT_MATCH;
end;

看完上述內(nèi)容，你們對Oracle存儲過程中Procedure簡單分析有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。