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簡單分析有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。