如何使用Oracle的游標？

Oracle中的游標分為顯示游標和隱式游標。

創(chuàng)新互聯(lián)是一家專業(yè)提供昌都企業(yè)網(wǎng)站建設,專注與網(wǎng)站制作、成都網(wǎng)站制作、HTML5、小程序制作等業(yè)務。10年已為昌都眾多企業(yè)、政府機構等服務。創(chuàng)新互聯(lián)專業(yè)網(wǎng)絡公司優(yōu)惠進行中。

顯示游標：

顯示游標是用cursor...is命令定義的游標，它可以對查詢語句(select)返回的多條記錄進行處理；顯示游標的操作：打開游標、操作游標、關閉游標；

隱式游標：

隱式游標是在執(zhí)行插入(insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(select)語句時由PL/SQL自動定義的。PL/SQL隱式地打開SQL游標，并在它內(nèi)部處理SQL語句，然后關閉它。

查看oracle已經(jīng)用了多少游標

select * from v$sysstat where name like '%cursors%'

select * from v$parameter where name like '%cursors%'

select count(0) from v$open_cursor

select sid,count(*) from v$open_cursor group by sid

select count(*),sql_text from v$open_cursor group by sql_text order by count(*) desc

select KGLLKFLG,KGLNAOBJ from X$KGLLK where KGLLKFLG=8;

select max(cursor_count) from (select count(*) cursor_count from v$open_cursor where user_name='CRING_SMS')

在sqlplus中執(zhí)行

SELECT v.name, v.value value FROM V$PARAMETER v WHERE name = 'open_cursors';

看看value是多少

使用下面的命令可以修改它的大小:

在 oracle9i 中應該可以直接進行修改：

alter system set open_cursors=30000;

如果可以就直接生效了；如果不行可以使用下面的語句：

alter system set open_cursors=30000 scope=spfile;

然后重啟數(shù)據(jù)庫生效

oracle存儲過程中打開游標有幾種方法？用open直接打開？

兩種方法\x0d\x0a1.聲明游標時寫好SELECT語句，如\x0d\x0aCURSOR r_cur1 IS select *** from tableName where 條件;\x0d\x0a使用時\x0d\x0a OPEN r_cur1;\x0d\x0a LOOP\x0d\x0a FETCH *** INTO variable;\x0d\x0a EXIT WHEN r_cur1%NOTFOUND OR r_cur1%NOTFOUND IS NULL;\x0d\x0a。。。\x0d\x0a2.聲明游標\x0d\x0accc sys_refcursor;\x0d\x0a使用時\x0d\x0a open ccc for select dept_code,dept_name from comm.dept_dict; \x0d\x0a loop\x0d\x0a fetch ccc into aa,bb;\x0d\x0a exit when ccc%notfound;\x0d\x0a 。。。\x0d\x0a end loop;\x0d\x0a close ccc;

快速掌握Oracle數(shù)據(jù)庫游標的使用方法

顯式游標

當查詢返回結果超過一行時 就需要一個顯式游標 此時用戶不能使用select into語句 PL/SQL管理隱式游標 當查詢開始時隱式游標打開 查詢結束時隱式游標自動關閉 顯式游標在PL/SQL塊的聲明部分聲明 在執(zhí)行部分或異常處理部分打開 取出數(shù)據(jù) 關閉

使用游標

這里要做一個聲明 我們所說的游標通常是指顯式游標 因此從現(xiàn)在起沒有特別指明的情況 我們所說的游標都是指顯式游標 要在程序中使用游標 必須首先聲明游標

聲明游標

語法

CURSOR cursor_name IS select_statement;

在PL/SQL中游標名是一個未聲明變量 不能給游標名賦值或用于表達式中

例

DELCARE CURSOR C_EMP IS SELECT empno ename salary FROM emp WHERE salary ORDER BY ename; BEGIN

在游標定義中SELECT語句中不一定非要表可以是視圖 也可以從多個表或視圖中選擇的列 甚至可以使用*來選擇所有的列

打開游標

使用游標中的值之前應該首先打開游標 打開游標初始化查詢處理 打開游標的語法是

OPEN cursor_name

cursor_name是在聲明部分定義的游標名

例

OPEN C_EMP; 關閉游標

語法

CLOSE cursor_name

例

CLOSE C_EMP; 從游標提取數(shù)據(jù) 從游標得到一行數(shù)據(jù)使用FETCH命令 每一次提取數(shù)據(jù)后 游標都指向結果集的下一行 語法如下

FETCH cursor_name INTO variable[ variable ]

對于SELECT定義的游標的每一列 FETCH變量列表都應該有一個變量與之相對應 變量的類型也要相同

例

SET SERVERIUTPUT ON DECLARE v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; CURSOR c_emp IS SELECT ename salary FROM emp; BEGIN OPEN c_emp; FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); FETCH c_emp INTO v_ename v_salary; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); CLOSE c_emp; END

這段代碼無疑是非常麻煩的 如果有多行返回結果 可以使用循環(huán)并用游標屬性為結束循環(huán)的條件 以這種方式提取數(shù)據(jù) 程序的可讀性和簡潔性都大為提高 下面我們使用循環(huán)重新寫上面的程序

SET SERVERIUTPUT ON DECLARE v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; CURSOR c_emp IS SELECT ename salary FROM emp; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_ename v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT PUT_LINE( Salary of Employee || v_ename || is || v_salary); END

記錄變量

定義一個記錄變量使用TYPE命令和%ROWTYPE 關于%ROWsTYPE的更多信息請參閱相關資料

記錄變量用于從游標中提取數(shù)據(jù)行 當游標選擇很多列的時候 那么使用記錄比為每列聲明一個變量要方便得多

當在表上使用%ROWTYPE并將從游標中取出的值放入記錄中時 如果要選擇表中所有列 那么在SELECT子句中使用*比將所有列名列出來要得多

例

SET SERVERIUTPUT ON DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS SELECT * FROM emp; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO r_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUT PUT PUT_LINE( Salary of Employee ||r_emp ename|| is || r_emp salary); END LOOP; CLOSE c_emp; END;

%ROWTYPE也可以用游標名來定義 這樣的話就必須要首先聲明游標

SET SERVERIUTPUT ON DECLARE CURSOR c_emp IS SELECT ename salary FROM emp; R_emp c_emp%ROWTYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO r_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUT PUT PUT_LINE( Salary of Employee ||r_emp ename|| is || r_emp salary); END LOOP; CLOSE c_emp; END;

帶參數(shù)的游標

與存儲過程和函數(shù)相似 可以將參數(shù)傳遞給游標并在查詢中使用 這對于處理在某種條件下打開游標的情況非常有用 它的語法如下

CURSOR cursor_name[(parameter[ parameter] )] IS select_statement;

定義參數(shù)的語法如下

Parameter_name [IN] data_type[{:=|DEFAULT} value]

與存儲過程不同的是 游標只能接受傳遞的值 而不能返回值 參數(shù)只定義數(shù)據(jù)類型 沒有大小

另外可以給參數(shù)設定一個缺省值 當沒有參數(shù)值傳遞給游標時 就使用缺省值 游標中定義的參數(shù)只是一個占位符 在別處引用該參數(shù)不一定可靠

在打開游標時給參數(shù)賦值 語法如下

OPEN cursor_name[value[ value] ];

參數(shù)值可以是文字或變量

例

DECALRE CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR ) IS SELECT ename salary FROM emp WHERE deptno=p_dept ORDER BY ename r_dept DEPT%ROWTYPE; v_ename EMP ENAME%TYPE; v_salary EMP SALARY%TYPE; v_tot_salary EMP SALARY%TYPE; BEGIN OPEN c_dept; LOOP FETCH c_dept INTO r_dept; EXIT WHEN c_dept%NOTFOUND; DBMS_OUTPUT PUT_LINE ( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; OPEN c_emp(r_dept deptno); LOOP FETCH c_emp INTO v_ename v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT PUT_LINE ( Name: || v_ename|| salary: ||v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; CLOSE c_emp; DBMS_OUTPUT PUT_LINE ( Toltal Salary for dept: || v_tot_salary); END LOOP; CLOSE c_dept; END;

游標FOR循環(huán)

在大多數(shù)時候我們在設計程序的時候都遵循下面的步驟

打開游標

開始循環(huán)

從游標中取值

那一行被返回

處理

關閉循環(huán)

關閉游標

可以簡單的把這一類代碼稱為游標用于循環(huán) 但還有一種循環(huán)與這種類型不相同 這就是FOR循環(huán) 用于FOR循環(huán)的游標按照正常的聲明方式聲明 它的優(yōu)點在于不需要顯式的打開 關閉 取數(shù)據(jù) 測試數(shù)據(jù)的存在 定義存放數(shù)據(jù)的變量等等 游標FOR循環(huán)的語法如下

FOR record_name IN (corsor_name[(parameter[ parameter] )] | (query_difinition) LOOP statements END LOOP;

下面我們用for循環(huán)重寫上面的例子

DECALRE CURSOR c_dept IS SELECT deptno dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR ) IS SELECT ename salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP SALARY%TYPE; BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT PUT_LINE ( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; FOR r_emp IN c_emp(r_dept deptno) LOOP DBMS_OUTPUT PUT_LINE ( Name: || v_ename || salary: || v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; DBMS_OUTPUT PUT_LINE ( Toltal Salary for dept: || v_tot_salary); END LOOP; END;

在游標FOR循環(huán)中使用查詢

在游標FOR循環(huán)中可以定義查詢 由于沒有顯式聲明所以游標沒有名字 記錄名通過游標查詢來定義

DECALRE v_tot_salary EMP SALARY%TYPE; BEGIN FOR r_dept IN (SELECT deptno dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT PUT_LINE( Department: || r_dept deptno|| ||r_dept dname); v_tot_salary:= ; FOR r_emp IN (SELECT ename salary 　FROM emp 　WHERE deptno=p_dept 　ORDER BY ename) LOOP DBMS_OUTPUT PUT_LINE( Name: || v_ename|| salary: ||v_salary); v_tot_salary:=v_tot_salary+v_salary; END LOOP; DBMS_OUTPUT PUT_LINE( Toltal Salary for dept: || v_tot_salary); END LOOP; END;

游標中的子查詢

語法如下

CURSOR C IS SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE dname!= ACCOUNTING );

可以看出與SQL中的子查詢沒有什么區(qū)別

游標中的更新和刪除

在PL/SQL中依然可以使用UPDATE和DELETE語句更新或刪除數(shù)據(jù)行 顯式游標只有在需要獲得多行數(shù)據(jù)的情況下使用 PL/SQL提供了僅僅使用游標就可以執(zhí)行刪除或更新記錄的方法

UPDATE或DELETE語句中的WHERE CURRENT OF子串專門處理要執(zhí)行UPDATE或DELETE操作的表中取出的最近的數(shù)據(jù) 要使用這個方法 在聲明游標時必須使用FOR UPDATE子串 當對話使用FOR UPDATE子串打開一個游標時 所有返回集中的數(shù)據(jù)行都將處于行級（ROW LEVEL)獨占式鎖定 其他對象只能查詢這些數(shù)據(jù)行 不能進行UPDATE DELETE或SELECT FOR UPDATE操作

語法

FOR UPDATE [OF [schema ]lumn[ [schema ]lumn] [nowait]

在多表查詢中 使用OF子句來鎖定特定的表 如果忽略了OF子句 那么所有表中選擇的數(shù)據(jù)行都將被鎖定 如果這些數(shù)據(jù)行已經(jīng)被其他會話鎖定 那么正常情況下ORACLE將等待 直到數(shù)據(jù)行解鎖

在UPDATE和DELETE中使用WHERE CURRENT OF子串的語法如下

WHERE{CURRENT OF cursor_name|search_condition}

例

DELCARE CURSOR c IS SELECT empno salary FROM emp WHERE m IS NULL FOR UPDATE OF m; v_m NUMBER( ); BEGIN FOR r IN c LOOP IF r salary THEN v_m:=r salary* ; ELSEIF r salary THEN v_m:=r salary* ; ELSEIF r salary THEN v_m:=r salary* ; ELSE v_m:=r salary* ; END IF; UPDATE emp; SET m=v_m WHERE CURRENT OF c l; END LOOP; END

lishixinzhi/Article/program/Oracle/201311/16865