Oracle存儲過程中Procedure簡單分析

165次閱讀

共計 3302 個字符，預計需要花費 9 分鐘才能閱讀完成。

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

這里我們來討論存儲過程的一些高級選項：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;