Oracle數據庫游標使用大全

　　SQL是用於訪問ORACLE數據庫的語言PL/SQL擴展和加強了SQL的功能它同時引入了更強的程序邏輯 PL/SQL支持DML命令和SQL的事務控制語句DDL在PL/SQL中不被支持這就意味作在PL/SQL程序塊中不能創建表或其他任何對象較好的PL/SQL程序設計是在PL/SQL塊中使用象DBMS_SQL這樣的內建包或執行EXECUTE IMMEDIATE命令建立動態SQL來執行DDL命令PL/SQL編譯器保證對象引用以及用戶的權限
　　
　　　　下面我們將討論各種用於訪問ORACLE數據庫的DDL和TCL語句
　　
　　　　查詢
　　
　　　　SELECT語句用於從數據庫中查詢數據當在PL/SQL中使用SELECT語句時要與INTO子句一起使用查詢的返回值被賦予INTO子句中的變量變量的聲明是在DELCARE中SELECT INTO語法如下
　　
　　SELECT [DISTICT|ALL]{*|column[column]}
　　INTO (variable[variable] |record)
　　FROM {table|(subquery)}[alias]
　　WHERE
　　
　　　　PL/SQL中SELECT語句只返回一行數據如果超過一行數據那麼就要使用顯式游標（對游標的討論我們將在後面進行）INTO子句中要有與SELECT子句中相同列數量的變量INTO子句中也可以是記錄變量
　　
　　　　%TYPE屬性
　　
　　　　在PL/SQL中可以將變量和常量聲明為內建或用戶定義的數據類型以引用一個列名同時繼承他的數據類型和大小這種動態賦值方法是非常有用的比如變量引用的列的數據類型和大小改變了如果使用了%TYPE那麼用戶就不必修改代碼否則就必須修改代碼
　　
　　　例
　　
　　v_empno SCOTTEMPEMPNO%TYPE;
　　v_salary EMPSALARY%TYPE;
　　
　　　　不但列名可以使用%TYPE而且變量游標記錄或聲明的常量都可以使用%TYPE這對於定義相同數據類型的變量非常有用
　　
　　DELCARE
　　V_A NUMBER():=;
　　V_B V_A%TYPE:=;
　　V_C V_A%TYPE;
　　BEGIN
　　DBMS_OUTPUTPUT_LINE
　　(V_A=||V_A||V_B=||V_B||V_C=||V_C);
　　END
　　
　　SQL>/
　　V_A= V_B= V_C=
　　PL/SQL procedure successfully completed
　　
　　SQL>
　　
　　　　其他DML語句
　　
　　　　其它操作數據的DML語句是:INSERTUPDATEDELETE和LOCK TABLE這些語句在PL/SQL中的語法與在SQL中的語法相同我們在前面已經討論過DML語句的使用這裡就不再重復了在DML語句中可以使用任何在DECLARE部分聲明的變量如果是嵌套塊那麼要注意變量的作用范圍
　　
　　　　例
　　
　　CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
　　　AS
　　　　v_ename EMPENAME%TYPE;
　　　BEGIN
　　　　SELECT ename INTO v_ename
　　　　FROM emp
　　　　WHERE empno=p_empno;
　　
　　　　INSERT INTO FORMER_EMP(EMPNOENAME)
　　　　VALUES (p_empnov_ename);
　　
　　　　DELETE FROM emp
　　　　WHERE empno=p_empno;
　　
　　　　UPDATE former_emp
　　　　SET date_deleted=SYSDATE
　　　　WHERE empno=p_empno;
　　
　　　　EXCEPTION
　　　　　WHEN NO_DATA_FOUND THEN
　　　　　DBMS_OUTPUTPUT_LINE(Employee Number Not Found!);
　　
　　　END
　　
　　　　DML語句的結果
　　
　　　　當執行一條DML語句後DML語句的結果保存在四個游標屬性中這些屬性用於控制程序流程或者了解程序的狀態當運行DML語句時PL/SQL打開一個內建游標並處理結果游標是維護查詢結果的內存中的一個區域游標在運行DML語句時打開完成後關閉隱式游標只使用SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNT三個屬性SQL%FOUNDSQL%NOTFOUND是布爾值SQL%ROWCOUNT是整數值
　　
　　　　SQL%FOUND和SQL%NOTFOUND
　　
　　　　在執行任何DML語句前SQL%FOUND和SQL%NOTFOUND的值都是NULL在執行DML語句後SQL%FOUND的屬性值將是
　　
　　　　 TRUE :INSERT
　　
　　　　 TRUE :DELETE和UPDATE至少有一行被DELETE或UPDATE
　　
　　　　 TRUE :SELECT INTO至少返回一行
　　
　　　　當SQL%FOUND為TRUE時SQL%NOTFOUND為FALSE
　　
　　　　SQL%ROWCOUNT
　　
　　　　在執行任何DML語句之前SQL%ROWCOUNT的值都是NULL對於SELECT INTO語句如果執行成功SQL%ROWCOUNT的值為如果沒有成功SQL%ROWCOUNT的值為同時產生一個異常NO_DATA_FOUND
　　
　　　　SQL%ISOPEN
　　
　　　　SQL%ISOPEN是一個布爾值如果游標打開則為TRUE 如果游標關閉則為FALSE對於隱式游標而言SQL%ISOPEN總是FALSE這是因為隱式游標在DML語句執行時打開結束時就立即關閉
　　
　　　　事務控制語句
　　
　　　　事務是一個工作的邏輯單元可以包括一個或多個DML語句事物控制幫助用戶保證數據的一致性如果事務控制邏輯單元中的任何一個DML語句失敗那麼整個事務都將回滾在PL/SQL中用戶可以明確地使用COMMITROLLBACKSAVEPOINT以及SET TRANSACTION語句
　　
　　　　COMMIT語句終止事務永久保存數據庫的變化同時釋放所有LOCKROLLBACK終止現行事務釋放所有LOCK但不保存數據庫的任何變化SAVEPOINT用於設置中間點當事務調用過多的數據庫操作時中間點是非常有用的SET TRANSACTION用於設置事務屬性比如readwrite和隔離級等
　　
　　　　顯式游標
　　
　　　　當查詢返回結果超過一行時就需要一個顯式游標此時用戶不能使用select into語句PL/SQL管理隱式游標當查詢開始時隱式游標打開查詢結束時隱式游標自動關閉顯式游標在PL/SQL塊的聲明部分聲明在執行部分或異常處理部分打開取數據關閉下表顯示了顯式游標和隱式游標的差別
　　　　　　　　　　　　　　　　　　　表 隱式游標和顯式游標
　　[[The No Picture]]
　　使用游標
　　
　　　　這裡要做一個聲明我們所說的游標通常是指顯式游標因此從現在起沒有特別指明的情況我們所說的游標都是指顯式游標要在程序中使用游標必須首先聲明游標
　　
　　　　聲明游標
　　
　　　　語法
　　
　　CURSOR cursor_name IS select_statement;
　　
　　　　在PL/SQL中游標名是一個未聲明變量不能給游標名賦值或用於表達式中
　　
　　　　例
　　
　　DELCARE
　　CURSOR C_EMP IS SELECT empnoenamesalary
　　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 EMPENAME%TYPE;
　　v_salary EMPSALARY%TYPE;
　　CURSOR c_emp IS SELECT enamesalary FROM emp;
　　BEGIN
　　OPEN c_emp;
　　FETCH c_emp INTO v_enamev_salary;
　　DBMS_OUTPUTPUT_LINE(Salary of Employee|| v_ename
　　||is|| v_salary);
　　FETCH c_emp INTO v_enamev_salary;
　　DBMS_OUTPUTPUT_LINE(Salary of Employee|| v_ename
　　||is|| v_salary);
　　FETCH c_emp INTO v_enamev_salary;
　　DBMS_OUTPUTPUT_LINE(Salary of Employee|| v_ename
　　||is|| v_salary);
　　CLOSE c_emp;
　　END
　　
　　　　這段代碼無疑是非常麻煩的如果有多行返回結果可以使用循環並用游標屬性為結束循環的條件以這種方式提取數據程序的可讀性和簡潔性都大為提高下面我們使用循環重新寫上面的程序
　　
　　SET SERVERIUTPUT ON
　　DECLARE
　　v_ename EMPENAME%TYPE;
　　v_salary EMPSALARY%TYPE;
　　CURSOR c_emp IS SELECT enamesalary FROM emp;
　　BEGIN
　　OPEN c_emp;
　　LOOP
　　FETCH c_emp INTO v_enamev_salary;
　　EXIT WHEN c_emp%NOTFOUND;
　　DBMS_OUTPUTPUT_LINE(Salary of Employee|| v_ename
　　||is|| v_salary);
　　END
　　
　　　　記錄變量
　　
　　　　定義一個記錄變量使用TYPE命令和%ROWTYPE關於%ROWsTYPE的更多信息請參閱相關資料
　　
　　　　記錄變量用於從游標中提取數據行當游標選擇很多列的時候那麼使用記錄比為每列聲明一個變量要方便得多
　　
　　　　當在表上使用%ROWTYPE並將從游標中取出的值放入記錄中時如果要選擇表中所有列那麼在SELECT子句中使用*比將所有列名列出
From:http://tw.wingwit.com/Article/program/Oracle/201311/17373.html