1．%FOUND 用法，只有在DML 語句影響一行或者多行時，%FOUND 屬性才返回 TRUE。下列示例演示了 %FOUND 的用法：
begin
update employees2 set first_name = first_name ||'' t'' where employee_id = 2;
if SQL%found then
dbms_output.put_line(''數據已經更新'');
else
dbms_output.put_line(''數據沒有找到'');
end if;
end;
/
declare
cursor emp2cursor is select * from employees2 where division_id = ''SUP'';
myrecord employees2%rowtype;
begin
open emp2cursor ;
fetch emp2cursor into myrecord;
while emp2cursor%found loop
dbms_output.put_line ('' employee_id= ''||myrecord.employee_id
||'' division_id = '' || myrecord.division_id || '' first_name = ''||myrecord.first_name);
fetch emp2cursor into myrecord;
end loop;
close emp2cursor;
end;
/
2． %ROWCOUNT 屬性返回DML語句影響的行數，如果DML 語句沒有影響任何行，則
%ROWCOUNT 屬性將返回 0；以下代碼演示了%ROWCOUNT用法：
begin
update employees2 set first_name = ''john'' where employee_id = 2;
-- dbms_output.put_line(''更新了''|| SQL%ROWCOUNT || ''行'');
if SQL%ROWCOUNT > 0 THEN
dbms_output.put_line(''更新了'');
end if;
end;
declare
firstname employees2.first_name%type;
cursor rowcount_cur is select first_name from employees2
where division_id = ''SUP'';
begin
open rowcount_cur ;
loop
fetch rowcount_cur into firstname;
exit when rowcount_cur%NOTFOUND ;
dbms_output.put_line(''顯示行數為:''||rowcount_cur%ROWCOUNT);
end loop;
close rowcount_cur;
end;
/
3. %ISOPEN屬性返回游標是否已打開的值。在執行SQL語句之後。Oracle 自動關閉SQL游標，所以隱式游標的%ISOPEN 屬性始終為FALSE。以下代碼通過顯示游標判斷游標是否已經打開：
declare
cursor isopen_cursor(empid number) is select first_name from employees2 where employee_id = empid;
firstname employees2.first_name%type;
begin
if isopen_cursor %isopen then
dbms_output.put_line(''游標已經打開'');
else
open isopen_cursor(2);
fetch isopen_cursor into firstname;
dbms_output.put_line(firstname);
close isopen_cursor ;
end if;
end;
/
4.游標簡單示例，以下代碼通過聲名游標，顯示了公司ID 為 ‘SUP’的記錄：
declare
cursor emp2cursor is select * from employees2 where division_id = ''SUP'';
myrecord employees2%rowtype;
begin
open emp2cursor ;
fetch emp2cursor into myrecord;
while emp2cursor%found loop
dbms_output.put_line('' employee_id= ''||myrecord.employee_id||''
division_id = '' || myrecord.division_id || '' first_name = ''||myrecord.first_name);
fetch emp2cursor into myrecord;
end loop;
close emp2cursor;
end;
/以下代碼顯示了employees2表中的信息：
set serveroutput on
declare
employeeid number(5);
divisionid varchar2(10);
jobid varchar2(10);
firstName varchar2(20);
lastName varchar2(20);
sal number(10);
cursor employeeinfo is select * from employees2 ;
begin
open employeeinfo;
loop
fetch employeeinfo into employeeid,divisionid,jobid,firstname,lastname,sal;
exit when employeeinfo%notfound;
dbms_output.put_line(employeeinfo%rowcount||'' ''||'' employeeid=''||employeeid
||'' ''||''firstName''||firstName||'' '' ||''lastName =''||lastName);
end loop;
end;
/
以下代碼演示了如何更新 employees2 表中的 first_name 字段：
set serveroutput on
declare
firstName varchar2(20);
cursor employees2_cursor is select first_name from employees2 where employee_id=1 for update of
first_name;
begin
open employees2_cursor;
loop
fetch employees2_cursor into firstName;
exit when employees2_cursor%notfound;
update employees2
set first_Name=''jeff '' where current of employees2_cursor;
end loop;
close employees2_cursor;
commit;
end;
/
帶參數的顯示游標
//帶參數游標
declare
cursor emp2_param_cursor(empid number) is select first_name from employees2 where employee_id = empid;
firstname employees2.first_name%type;
begin
open emp2_param_cursor(2);
loop
fetch emp2_param_cursor into firstname;
exit when emp2_param_cursor%notfound;
dbms_output.put_line(''firstname =''||firstname);
end loop;
close emp2_param_cursor;
end;
/
// 使用FOR 循環顯示游標內容
declare
cursor emp2_for_cursor(empid number) is select first_name from employees2 where employee_id = empid;
begin
for cur in emp2_for_cursor(2) loop
dbms_output.put_line(cur.first_name);
end loop;
end;
/
循環游標。可以使用循環游標簡化顯示游標的處理代碼。循環游標隱式打開游標，自動從活動集獲取行，然後在處理完成所有行時關閉游標。以下代碼演示循環游標的用法：
帶參數循環：
set serveroutput on
declare
divisionid varchar2(5);
cursor employees2_cursor4(divid varchar2) is select employee_id,division_id,job_id,first_name,last_name from
employees2 where division_id = divid;
begin
divisionid :=&divisionid;
for employee in employees2_cursor4(divisionid)
loop
dbms_output.put_line(''employee_id=''||employee.employee_id);
end loop;
end;
/
REF 游標的使用：
Connect scott/tiger;
SET SERVEROUTPUT ON
-- 先執行
ACCEPT tab PROMPT ''你想查看什麼信息？員工信息(E)或部門信息(D):''
-- 再執行
DECLARETYPE refcur_t IS REF CURSOR;
refcur refcur_t;
p_id NUMBER;
p_name VARCHAR2(100);
selection VARCHAR2(1) := UPPER(SUBSTR(''&tab '',1,1));
BEGIN
IF selection = ''E'' THEN
OPEN refcur FOR
SELECT empno id, ename name FROM emp;
DBMS_OUTPUT.PUT_LINE(''===員工信息==='');
ELSIF selection = ''D'' THEN
OPEN refcur FOR
SELECT deptno id, dname name
FROM dept;
DBMS_OUTPUT.PUT_LINE(''===部門信息==='');
ELSE
DBMS_OUTPUT.PUT_LINE(''請輸入員工信息(E) 或 部門信息(D)'');
RETURN;
END IF;
FETCH refcur INTO p_id, p_name;
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(''#'' || p_id || '':'' || p_name);
FETCH refcur INTO p_id, p_name;
END LOOP;
CLOSE refcur;
END;
/
以下代碼演示了動態SQL 的用法：（以下代碼演示薪水高於2500的員工信息）
SET SERVEROUTPUT ON
VARIABLE maxsal NUMBER
EXECUTE :maxsal := 2500
DECLARE
r_emp emp%ROWTYPE;
TYPE c_type IS REF CURSOR;
cur c_type;
p_salary NUMBER;
BEGIN
p_salary := :maxsal;
OPEN cur FOR ''select * from emp where sal>:1
order by sal desc''
USING p_salary;
LOOP
FETCH cur INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(''編號：''|| r_emp.empno
|| '' 姓名：'' || r_emp.ename
|| '' 薪水：'' || r_emp.sal );
END LOOP;
CLOSE cur;
END;
/
