Oracle遊標實用

  declare<br /> sqlstring varchar2(100);<br /> emprow emp_bak%rowtype;<br /> myno emp_bak.empno%type;<br />begin</p><p> --sqlstring:='create table emp_bak as select * from emp';</p><p> sqlstring:='select * from emp_bak where empno=:myno and sal>:mysal';</p><p> myno:='&請輸入員工編號';</p><p> --動態SQL<br /> execute immediate sqlstring into emprow using myno,3001 ;</p><p> dbms_output.put_line(emprow.sal);</p><p> exception<br /> when no_data_found then<br /> dbms_output.put_line('輸入的員工編號不存在');</p><p>end;</p><p>select * from emp_bak</p><p>--隱士遊標 遊標名字 SQL<br />--隱式遊標 是oracle 系統自動在運行DML語句的時候 產生的。 他自動開啟 自動關閉。<br />-- 一般情況下 該遊標 對於程式員來說 是透明的<br />declare</p><p>begin</p><p> update emp_bak set sal = 2500 where empno = 8888; </p><p> --輸出隱士遊標中的一個屬性 %rowcount 就是影響的行數<br /> dbms_output.put_line(sql%rowcount);</p><p>end;</p><p>-- %ISOPEN - 遊標是否開啟 布爾值<br />-- %noopen 沒有這個屬性！！</p><p>-- %FOUND – 遊標中是否還有資料<br />-- %NOTFOUND – 遊標中是否還有資料<br />-- %ROWCOUNT – SQL 陳述式影響的行數</p><p>-- CURSOR 遊標<br />--顯式遊標 有程式員自己定義遊標 自己定義 自己使用<br />--遊標要使用的話 步驟 1 定義 2 開啟 3 使用（迴圈） 4 關閉遊標</p><p>declare<br /> --`1 定義遊標<br /> cursor mycursor is select ename,sal from emp_bak;<br /> emprow emp_bak%rowtype;</p><p>begin<br /> -- 2 開啟遊標<br /> open mycursor;</p><p> -- 3 使用遊標</p><p> loop<br /> -- 遊標中的資料 是通過關鍵字 提取 fetch<br /> fetch mycursor into emprow.ename,emprow.sal; -- 1 提取資料 2 並且該遊標會指向下一行 </p><p> if(emprow.sal >2000 and emprow.sal<3000) then<br /> dbms_output.put_line(emprow.ename||' '|| emprow.sal);<br /> end if;<br /> -- dbms_output.put_line(emprow.ename||' '|| emprow.sal);<br /> exit when mycursor%NOTFOUND; --2 使用遊標的%NOTFOUND屬性檢測遊標是否還有資料 如果沒有了 那麼就退出迴圈！！</p><p> end loop;</p><p> -- 關閉遊標<br /> close mycursor;</p><p>end;</p><p>--簡單寫法 迴圈遊標<br />declare<br /> --`1 定義遊標<br /> cursor mycursor is select ename,sal from emp_bak;<br />begin</p><p> for m in mycursor --這行代碼 包含了 開啟遊標 提取遊標<br /> loop<br /> dbms_output.put_line(m.ename || m.sal);<br /> end loop;<br /> --迴圈結束 自動關閉遊標<br />end;</p><p>--帶參數的遊標<br />declare<br /> --`1 定義帶參數遊標<br /> cursor mycursor(mysal emp_bak.sal%type) is select ename,sal from emp_bak where sal<mysal;<br /> emprow emp_bak%rowtype;<br /> sal emp_bak.sal%type;<br />begin<br /> -- 2 開啟遊標並且給參數賦值</p><p> sal:='&請輸入薪水';<br /> open mycursor(sal);<br /> -- 3 使用遊標<br /> loop<br /> -- 遊標中的資料 是通過關鍵字 提取 fetch<br /> fetch mycursor into emprow.ename,emprow.sal; -- 1 提取資料 2 並且該遊標會指向下一行 </p><p> dbms_output.put_line(emprow.ename||' '|| emprow.sal);<br /> exit when mycursor%NOTFOUND; --2 使用遊標的%NOTFOUND屬性檢測遊標是否還有資料 如果沒有了 那麼就退出迴圈！！</p><p> end loop;</p><p> -- 關閉遊標<br /> close mycursor;</p><p>end;</p><p>--帶參數的遊標的簡單寫法<br />declare<br /> --`1 定義帶參數遊標<br /> cursor mycursor(mysal emp_bak.sal%type) is select ename,sal from emp_bak where sal<mysal;<br /> sal emp_bak.sal%type;<br />begin<br /> -- 2 開啟遊標並且給參數賦值<br /> sal:='&請輸入薪水';<br /> for m in mycursor(sal)<br /> loop<br /> dbms_output.put_line(m.ename||' '|| m.sal);<br /> end loop;<br />end;</p><p>--使用遊標來更新資料<br />declare<br /> cursor mycursor is select * from emp_bak for update; --帶更新的遊標必須要加上一個語句 for update<br /> emprow emp_bak%rowtype;<br /> sal emp_bak.sal%type;<br />begin<br /> open mycursor;<br /> -- 3 使用遊標<br /> loop<br /> -- 遊標中的資料 是通過關鍵字 提取 fetch<br /> fetch mycursor into emprow; -- 1 提取資料 2 並且該遊標會指向下一行<br /> exit when mycursor%NOTFOUND; --2 使用遊標的%NOTFOUND屬性檢測遊標是否還有資料 如果沒有了 那麼就退出迴圈！！<br /> if(emprow.sal=800) then<br /> update emp_bak set sal = 801 where current of mycursor; --current of mycursor 表示遊標當前這一行<br /> elsif(emprow.sal> 900 and emprow.sal<2000) then<br /> update emp_bak set sal = 2000 where current of mycursor; --current of mycursor 表示遊標當前這一行</p><p> end if;<br /> end loop;<br /> --迴圈完畢 提交<br /> commit;</p><p> -- 關閉遊標<br /> close mycursor;</p><p>end;</p><p>--使用遊標來更新資料<br />declare<br /> cursor mycursor is select * from emp_bak for update; --帶更新的遊標必須要加上一個語句 for update</p><p> emprow emp_bak%rowtype;<br /> sal emp_bak.sal%type;<br />begin<br /> for m in mycursor<br /> loop<br /> if(m.sal=800) then<br /> -- update emp_bak set sal = 801 where current of mycursor; --current of mycursor 表示遊標當前這一行<br /> delete from emp_bak where current of mycursor; --刪除<br /> elsif(m.sal> 900 and m.sal<=2000) then<br /> update emp_bak set sal = 1800 where current of mycursor; --current of mycursor 表示遊標當前這一行<br /> end if;<br /> end loop;<br /> --迴圈完畢 提交<br /> commit;<br />end;</p><p>-- 引用遊標 1 不能使用簡化方式寫 2 不能使用更新方式 3 可以進行賦值 4 如果是弱類型的 可以讓遊標變數指向不同的查詢結果</p><p>-- 引用遊標 1 先定義一個遊標類型 2 再定義遊標變數 3 使用語句給遊標變數賦值<br />-- 弱類型的引用遊標<br />declare<br /> type mycursor is ref cursor; --定義了一個遊標類型 int<br /> c mycursor;<br /> i int;<br /> emprow emp_bak%rowtype;<br /> deptrow dept%rowtype;<br />begin<br /> open c for select * from emp_bak; --給遊標變數賦值 並且開啟<br /> loop<br /> fetch c into emprow;<br /> exit when c%notfound;<br /> dbms_output.put_line(emprow.sal);<br /> end loop;<br /> close c;</p><p> open c for select * from dept;<br /> loop<br /> fetch c into deptrow;<br /> exit when c%notfound;<br /> dbms_output.put_line(deptrow.dname);<br /> end loop;<br /> close c;<br />end;</p><p>-- 引用遊標 1 先定義一個遊標類型 2 再定義遊標變數 3 使用語句給遊標變數賦值<br />-- 強型別的引用遊標<br />declare<br /> type mycursor is ref cursor return emp_bak%rowtype; --定義了一個強遊標類型<br /> c mycursor;<br /> k mycursor;<br /> i int;<br /> m int;<br /> emprow emp_bak%rowtype;<br /> deptrow dept%rowtype;<br />begin<br /> open c for select * from emp_bak; --給遊標變數賦值 並且開啟</p><p> k:=c;<br /> i:=m;<br /> loop<br /> fetch k into emprow;<br /> exit when k%notfound;<br /> dbms_output.put_line(emprow.sal);<br /> end loop;<br /> close c;</p><p>end;</p><p>