Oracle數據庫游標的類型

游標是SQL的一個內存工作區 由系統或用戶以變量的形式定義 游標的作用就是用于臨時存儲從數據庫中提取的數據塊

創新互聯從2013年成立，是專業互聯網技術服務公司，擁有項目成都網站制作、網站建設、外貿網站建設網站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元延壽做網站,已為上家服務,為延壽各地企業和個人服務,聯系電話:028-86922220

Oracle數據庫的Cursor類型包含三種 靜態游標 分為顯式（explicit）游標和隱式（implicit）游標 REF游標 是一種引用類型 類似于指針

測試數據

create table student（sno number primary key sname varchar （ ））

declare i number:= ;

beginwhile i=

loop

insert into student（sno sname） values （i name ||to_char（i））

i:=i+ ;

end loop;

end;

隱式游標屬性

SQL%ROWCOUNT 整型代表DML語句成功執行的數據行數

SQL%FOUND 布爾型值為TRUE代表插入 刪除 更新或單行查詢操作成功

SQL%NOTFOUND 布爾型與SQL%FOUND屬性返回值相反

SQL%ISOPEN 布爾型DML執行過程中為真 結束后為假

declarebegin? update student set sname = name ||to_char（sno* ） where sname= name ;

if sql%found then

dbms_output put_line（ name is updated ）

else

dbms_output put_line（ 沒有記錄 ）

end if;

end;

declare

begin

for names in （select * from student） loop

dbms_output put_line（names sname）

end loop;

exception when others then

dbms_output put_line（sqlerrm）

end;

顯式游標屬性

%ROWCOUNT 獲得FETCH語句返回的數據行數

%FOUND 最近的FETCH語句返回一行數據則為真 否則為假

%NOTFOUND 布爾型 與%FOUND屬性返回值相反

%ISOPEN 布爾型 游標已經打開時值為真 否則為假

對于顯式游標的運用分為四個步驟

a 定義游標 Cursor [Cursor Name] IS;

b 打開游標 Open [Cursor Name];

c 操作數據 Fetch [Cursor name];

d 關閉游標 Close [Cursor Name];

典型顯式游標

declare cursor cur_rs is select * from student; sinfo student%rowtype;

begin? open cur_rs;

loop

fetch cur_rs into sinfo;

exit when cur_rs%%notfound;

dbms_output put_line（sinfo sname）

end loop;

exception when others then

dbms_output put_line（sqlerrm）

end;

帶參數open的顯式cursor:

declare cursor cur_rs（in_name varchar ） is select *

from student where sname=in_name;

begin? for sinfo in cur_rs（ sname ） loop

dbms_output put_line（sinfo sname）

end loop;

exception when others then

dbms_output put_line（sqlerrm）

end;

使用current of語句執行update或delete操作

declare

cursor cur_rs is select * from student for update;

begin? for sinfo in cur_rs loop

update student set sname=sname|| xx where current of cur_rs;

end loop;

mit;

exception when others then

dbms_output put_line（sqlerrm）

end;

REF游標 用于處理運行時才能確定的動態sql查詢結果 利用REF CURSOR 可以在程序間傳遞結果集（一個程序里打開游標變量 在另外的程序里處理數據）

也可以利用REF CURSOR實現BULK SQL 提高SQL性能

REF CURSOR分兩種 Strong REF CURSOR 和 Weak REF CURSOR

Strong REF CURSOR: 指定retrun type CURSOR變量的類型必須和return type一致

Weak REF CURSOR: 不指定return type 能和任何類型的CURSOR變量匹配

運行時根據動態sql查詢結果遍歷

create or replace package pkg_test as

type student_refcursor_type is ref cursor return student%rowtype;

procedure student_rs_loop（cur_rs IN student_refcursor_type）

end pkg_test ;

create or replace package body pkg_test as

procedure student_rs_loop（cur_rs IN student_refcursor_type） is

std student%rowtype;

begin? loop

fetch cur_rs into std;

exit when cur_rs%NOTFOUND;

dbms_output put_line（std sname）

end loop;

end student_rs_loop;

end pkg_test ;

declare stdRefCur pkg_test student_refcursor_type;

begin? for i in loop

dbms_output put_line（ Student NO= || i）

open stdRefCur for select * from student where sno=i;

pkg_test student_rs_loop（stdRefCur）

end loop;

exception when others then dbms_output put_line（sqlerrm）

close stdRefCur;

end;

使用FORALL和BULK COLLECT子句 利用BULK SQL可以減少PLSQL Engine和SQL Engine之間的通信開銷 提高性能

加速INSERT UPDATE DELETE語句的執行 也就是用FORALL語句來替代循環語句

加速SELECT 用BULK COLLECT INTO 來替代INTO

create table

student_tmp as select sno

sname from student where = ;

刪除主鍵約束 alter table student drop constraint SYS_C ;

執行兩遍插入 insert into student select * from student where sno= ;

declare cursor cur_std（stdid student sno%type） is select sno

sname from student where sno=stdid;

type student_table_type is table of cur_std%rowtype index by pls_integer;

student_table student_table_type;

begin

open cur_std（ ）

fetch cur_std bulk collect into student_table;

close cur_std;

for i in unt loop

dbms_output put_line（student_table（i） sno ||

|| student_table（i） sname）

end loop;

forall i in student_table firststudent_table last

insert into student_tmp values（student_table（i） sno student_table（i） sname）

mit;

end;

lishixinzhi/Article/program/Oracle/201311/17358

Oracle游標使用總結

Oracle游標分為顯示游標和隱式游標

顯示游標（Explicit Cursor）:在PL/SQL程序中定義的 用于查詢的游標稱作顯示游標

隱式游標（Implicit Cursor）:是指非PL/SQL程序中定義的 而且是在PL/SQL中使用UPDATE/DELETE語句時 Oracle系統自動分配的游標

一 顯示游標

使用步驟

( )定義 ( )打開 ( )使用 ( )關閉

使用演示

首先創建測試用表STUDENT 腳本如下

( ) 使用WHILE循環處理游標

create or replace PROCEDURE PROC_STU AS

BEGIN

顯示游標使用 使用while循環

declare

定義游標 名稱為cur_stu

cursor cur_stu is

select stuno stuname from student order by stuno;

定義變量 存放游標取出的數據

v_stuno varchar( );

v_stuname varchar( );

begin

打開游標cur_stu

open cur_stu;

將游標的當前行取出存放到變量中

fetch cur_stu into v_stuno v_stuname;

while cur_stu%found 游標所指還有數據行 則繼續循環

loop

打印結果

dbms_output PUT_LINE(v_stuno|| ||v_stuname);

繼續將游標所指的當前行取出放到變量中

fetch cur_stu into v_stuno v_stuname;

end loop;

close cur_stu; 關閉游標

end;

END PROC_STU ;

( ) 使用IF ELSE代替WHILE循環處理游標

create or replace PROCEDURE PROC_STU AS

BEGIN

顯示游標使用 使用if判斷

declare

定義游標 名稱為cur_stu

cursor cur_stu is

select stuno stuname from student order by stuno;

定義變量 存放游標取出的數據

v_stuno varchar( );

v_stuname varchar( );

begin

打開游標cur_stu

open cur_stu;

將游標的當前行取出存放到變量中

fetch cur_stu into v_stuno v_stuname;

loop

if cur_stu%found then 如果游標cur_stu所指還有數據行

打印結果

dbms_output PUT_LINE(v_stuno|| ||v_stuname);

繼續將游標所指的當前行取出放到變量中

fetch cur_stu into v_stuno v_stuname;

else

exit;

end if;

end loop;

close cur_stu; 關閉游標

end;

END PROC_STU ;

( ) 使用FOR循環處理游標

create or replace PROCEDURE PROC_STU AS

BEGIN

顯示游標使用 使用for循環

declare

定義游標 名稱為cur_stu

cursor cur_stu is

select stuno stuname from student order by stuno;

begin

for stu in cur_stu

loop

dbms_output PUT_LINE(stu stuno|| ||stu stuname);

循環做隱含檢查 %notfound

end loop;

自動關閉游標

end;

END PROC_STU ;

( ) 常用的使用EXIT WHEN處理游標

create or replace

PROCEDURE PROC_STU _ AS

BEGIN

顯示游標使用 使用exit when循環

declare

定義游標 名稱為cur_stu

cursor cur_stu is

select stuno stuname from student order by stuno;

定義變量 存放游標取出的數據

v_stuno varchar( );

v_stuname varchar( );

begin

打開游標cur_stu

open cur_stu;

loop

將游標的當前行取出存放到變量中

fetch cur_stu into v_stuno v_stuname;

exit when cur_stu%notfound; 游標所指還有數據行 則繼續循環

打印結果

dbms_output PUT_LINE(v_stuno|| ||v_stuname);

end loop;

close cur_stu; 關閉游標

end;

END PROC_STU _ ;

二 隱式游標

使用演示

create or replace PROCEDURE PROC_STU AS

BEGIN

隱式游標使用

update student set stuname= 張燕廣 where stuno= ;

如果更新沒有匹配則插入一條新記錄

if SQL%NOTFOUND then

insert into student(STUNO STUNAME AGE GENDER)

values( 張燕廣 男 );

end if;

END PROC_STU ;

說明

所有的SQL語句在上下文區內部都是可執行的 因為都有一個游標指向上下文區 此游標就是

SQL游標 與現實游標不同的是 SQL游標在PL/SQL中不需要打開和關閉 而是在執行UPDATE

DELETE是自動打開和關閉

上面例子中就是通過SQL%NOTFOUND游標屬性判斷UPDATE語句的執行結果決定是否需要插入新記錄 CREATE TABLE STUDENT (

STUNAME VARCHAR ( BYTE)

STUNO VARCHAR ( BYTE)

AGE NUMBER

GENDER VARCHAR ( CHAR)

lishixinzhi/Article/program/Oracle/201311/17531

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

聲明游標時寫好SELECT語句，如

CURSOR r_cur1 IS select *** from tableName where 條件;

使用時

OPEN r_cur1;

LOOP

FETCH *** INTO variable;

EXIT WHEN r_cur1%NOTFOUND OR r_cur1%NOTFOUND IS NULL;

甲骨文股份有限公司（NASDAQ：ORCL，Oracle）是全球大型數據庫軟件公司。總部位于美國加州紅木城的紅木岸（Redwood Shores），現時首席執行官為公司創辦人勞倫斯·埃里森（Lawrence J. Ellison）。

如何用Oracle查看與修改最大游標數

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

連接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL show parameter open_cursors;

NAME TYPE VALUE

------------------------------------ ----------- -------------------

open_cursors integer 3002、查看當前打開的游標數目

SQL select count(*) from v$open_cursor;

COUNT(*)

----------174943、修改Oracle最大游標數

SQL alter system set open_cursors=1000 scope=both;

系統已更改。

快速掌握Oracle數據庫游標的使用方法

顯式游標

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

使用游標

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

聲明游標

語法

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; 從游標提取數據 從游標得到一行數據使用FETCH命令 每一次提取數據后 游標都指向結果集的下一行 語法如下

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

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

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的更多信息請參閱相關資料

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

當在表上使用%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;

帶參數的游標

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

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

定義參數的語法如下

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

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

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

在打開游標時給參數賦值 語法如下

OPEN cursor_name[value[ value] ];

參數值可以是文字或變量

例

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循環

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

打開游標

開始循環

從游標中取值

那一行被返回

處理

關閉循環

關閉游標

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

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

下面我們用for循環重寫上面的例子

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循環中使用查詢

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

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中的子查詢沒有什么區別

游標中的更新和刪除

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

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

語法

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

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

在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

查看oracle已經用了多少游標

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中執行

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;

然后重啟數據庫生效

分享題目：如何看oracle游標 oracle 游標 for
本文來源：http://vcdvsql.cn/article24/hpgsje.html

成都網站建設公司_創新互聯，為您提供域名注冊、網站建設、軟件開發、用戶體驗、建站公司、

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯