Oracle游標(biāo)的使用實(shí)例詳解

2024-08-29 14:01:12

字體：大中小

來源：轉(zhuǎn)載

供稿：網(wǎng)友

什么是游標(biāo)？

①?gòu)谋碇袡z索出結(jié)果集，從中每次指向一條記錄進(jìn)行交互的機(jī)制。

②關(guān)系數(shù)據(jù)庫中的操作是在完整的行集合上執(zhí)行的。

由 SELECT 語句返回的行集合包括滿足該語句的 WHERE 子句所列條件的所有行。由該語句返回完整的行集合叫做結(jié)果集。

應(yīng)用程序，尤其是互動(dòng)和在線應(yīng)用程序，把完整的結(jié)果集作為一個(gè)單元處理并不總是有效的。

這些應(yīng)用程序需要一種機(jī)制來一次處理一行或連續(xù)的幾行。而游標(biāo)是對(duì)提供這一機(jī)制的結(jié)果集的擴(kuò)展。

游標(biāo)是通過游標(biāo)庫來實(shí)現(xiàn)的。游標(biāo)庫是常常作為數(shù)據(jù)庫系統(tǒng)或數(shù)據(jù)訪問 API 的一部分而得以實(shí)現(xiàn)的軟件，用來管理從數(shù)據(jù)源返回的數(shù)據(jù)的屬性（結(jié)果集）。這些屬性包括并發(fā)管理、在結(jié)果集中的位置、返回的行數(shù)，以及是否能夠在結(jié)果集中向前和/或向后移動(dòng)（可滾動(dòng)性）。

游標(biāo)跟蹤結(jié)果集中的位置，并允許對(duì)結(jié)果集逐行執(zhí)行多個(gè)操作，在這個(gè)過程中可能返回至原始表，也可能不返回至原始表。

換句話說，游標(biāo)從概念上講基于數(shù)據(jù)庫的表返回結(jié)果集。

由于它指示結(jié)果集中的當(dāng)前位置，就像計(jì)算機(jī)屏幕上的光標(biāo)指示當(dāng)前位置一樣，“游標(biāo)”由此得名。

游標(biāo)有什么作用？

①指定結(jié)果集中特定行的位置。

②基于當(dāng)前的結(jié)果集位置檢索一行或連續(xù)的幾行。

③在結(jié)果集的當(dāng)前位置修改行中的數(shù)據(jù)。

④對(duì)其他用戶所做的數(shù)據(jù)更改定義不同的敏感性級(jí)別。

⑤可以以編程的方式訪問數(shù)據(jù)庫。

引言

本節(jié)對(duì)Oracle中的游標(biāo)進(jìn)行詳細(xì)講解。本節(jié)所舉實(shí)例來源Oracle中scott用戶下的emp表dept表：

Oracle,游標(biāo)

一、游標(biāo)：

1、概念：

游標(biāo)的本質(zhì)是一個(gè)結(jié)果集resultset，主要用來臨時(shí)存儲(chǔ)從數(shù)據(jù)庫中提取出來的數(shù)據(jù)塊。

二、游標(biāo)的分類：

1、顯式游標(biāo)：

由用戶定義，需要的操作：定義游標(biāo)、打開游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)，主要用于對(duì)查詢語句的處理。

屬性：%FOUND %NOTFOUND %ISOPEN %ROWCOUNT

Example:打印emp表的員工信息

DECLARE CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_empno,v_name,v_job; DBMS_OUTPUT.PUT_LINE('員工號(hào)為:'||v_empno||'姓名是'||v_name||'職位：'||v_job); EXIT WHEN emp_cursor%NOTFOUND; END LOOP; CLOSE emp_cursor;END;

Oracle,游標(biāo)

這里嚴(yán)格按照顯示游標(biāo)的書寫規(guī)則：DECLARE emp_cursor定義游標(biāo)OPEN emp_cursor打開游標(biāo)FETCH emp_cursor INTO...提取數(shù)據(jù)CLOSE emp_cursor關(guān)閉游標(biāo),因?yàn)樘崛〕鰜淼臄?shù)據(jù)屬于多行，所以通過loop循環(huán)打印即可。

Example2:檢驗(yàn)游標(biāo)是否打開,如果打開顯示提取行數(shù)

DECLARE CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN OPEN emp_cursor; LOOP  FETCH emp_cursor INTO v_empno,v_name,v_job;  EXIT WHEN emp_cursor%NOTFOUND; END LOOP; IF emp_cursor%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('游標(biāo)已打開'); DBMS_OUTPUT.PUT_LINE('讀取了'||emp_cursor%ROWCOUNT||'行'); ELSE DBMS_OUTPUT.PUT_LINE('游標(biāo)沒有打開'); END IF;  CLOSE emp_cursor;END;

Oracle,游標(biāo)

通過%ISOPEN屬性判斷游標(biāo)是否打開，%ROWCOUNT判斷獲取行數(shù)。

2、隱式游標(biāo)：由系統(tǒng)定義并為它創(chuàng)建工作區(qū)域，并且隱式的定義打開提取關(guān)閉，隱式游標(biāo)的游標(biāo)名就是'SQL',屬性和顯示游標(biāo)相同，主要用于對(duì)單行select語句或dml操作進(jìn)行處理。Example:又用戶輸入員工號(hào)修改員工工資如成功則打印輸出成功標(biāo)志。

為了盡量不改變?cè)?，?chuàng)建新表emp_new和原表數(shù)據(jù)相同：

CREATE TABLE emp_newASSELECT * FROM emp;

BEGIN UPDATE emp_new SET sal = sal+500 WHERE empno=&empno; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('成功修改'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('修改失敗'); ROLLBACK; END IF;END;

Oracle,游標(biāo)

這里注意增刪改以后要對(duì)做的操作進(jìn)行commit提交，如果操作失敗則rollback回滾剛才的操作。

3、參數(shù)游標(biāo)：

在定義游標(biāo)時(shí)加入?yún)?shù)的游標(biāo)，可以配合游標(biāo)for循環(huán)快速找到需要的數(shù)據(jù)。這里先講一下游標(biāo)for循環(huán)

A、游標(biāo)FOR循環(huán)：

隱含的執(zhí)行了打開提取關(guān)閉數(shù)據(jù)，代碼精簡(jiǎn)很多。Expression:

FOR table_record IN table_cursor LOOP

　　STATEMENT;

END LOOP;

Example:使用游標(biāo)For循環(huán)打印輸出員工信息：

DECLARECURSOR emp_cursor IS SELECT empno,ename,job FROM emp;BEGIN FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE('員工號(hào)：'||emp_record.empno||'員工姓名'||emp_record.ename||'員工職位'||emp_record.job); END LOOP;END;

Oracle,游標(biāo)

這里游標(biāo)FOR循環(huán)省去了對(duì)于取到的數(shù)據(jù)的變量的命名和賦值，同時(shí)如果全部打印則不用寫循環(huán)條件，代碼精簡(jiǎn)了很多。

如果想讓代碼更加精簡(jiǎn)，則可以去掉對(duì)游標(biāo)的聲明引入子查詢即可，操作如下。

BEGIN FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP DBMS_OUTPUT.PUT_LINE('員工號(hào)：'||emp_record.empno||'員工姓名'||emp_record.ename||'員工職位'||emp_record.job); END LOOP;END;

代碼更加精簡(jiǎn)，得到的結(jié)果相同。和隱式游標(biāo)是不是有點(diǎn)像，但隱式游標(biāo)主要用于的是單行select和dml語句的操作，注意2者用法的區(qū)別。

下面繼續(xù)參數(shù)游標(biāo)的實(shí)例：

Example：輸入部門號(hào)打印員工信息：

DECLARECURSOR emp_cursor(dno NUMBER)IS SELECT empno,ename,job FROM emp WHERE deptno=dno;BEGIN FOR emp_record IN emp_cursor(&dno) LOOP DBMS_OUTPUT.PUT_LINE('員工號(hào)'||emp_record.empno||'姓名'||emp_record.ename||'職位'||emp_record.job); END LOOP;END;

Oracle,游標(biāo)

這里既然有參數(shù)，那么必然會(huì)有對(duì)游標(biāo)的聲明，在結(jié)合游標(biāo)FOR循環(huán)快速超找所需要的數(shù)據(jù)。

三、使用游標(biāo)修改數(shù)據(jù)的注意事項(xiàng)

1、使用游標(biāo)修改數(shù)據(jù)時(shí)，為防止他人在自己操作數(shù)據(jù)時(shí)對(duì)數(shù)據(jù)進(jìn)行修改，oracle提供for update子句進(jìn)行加鎖。

同時(shí)在你使用update或delete時(shí)，必須使用where current of+name_cursor語句，以及在最后記得提交。如果是級(jí)聯(lián)操作則可以使用for update of 來進(jìn)行相關(guān)表的加鎖。

Example1：對(duì)職位是PRESIDENT的員工加1000工資，MANAGER的人加500工資

CREATE TABLE emp_newASSELECT * FROM emp;

DECLARECURSOR empnew_cursor IS SELECT ename,job FROM emp_new FOR UPDATE;BEGIN FOR empnew_record IN empnew_cursor LOOP DBMS_OUTPUT.PUT_LINE('姓名'||empnew_record.ename||'職位'||empnew_record.job); IF empnew_record.job='PRESIDENT' THEN  UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor; ELSIF empnew_record.job='MANAGER' THEN  UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor; END IF; END LOOP; COMMIT;END;

Oracle,游標(biāo)