--------------------------------------
declare
type myrecord_type is record(
first_name employees.first_name%type,
last_name employees.last_name%type);
TYPE mycur_type IS REF CURSOR RETURN myrecord_type;
cur mycur_type;
person myrecord_type;
begin
if 1 = 2 then
open cur for
select first_name, last_name from employees where employee_id = 198;
else
open cur for
select first_name, last_name from employees where employee_id = 199;
end if;
loop
fetch cur
into person;
exit when cur%NOTFOUND;
dbms_output.put_line(person.first_name || ' ' || person.last_name);
end loop;
close cur;
end;

---------------------------------
alter session set plsql_ccflags = 'to_debug:true'

declare
$if $$to_debug $then
cursor cur is select first_name, last_name from employees where employee_id = 198;
$else
cursor cur is select first_name, last_name from employees where employee_id = 199;
$end
begin
for person in cur loop
dbms_output.put_line(person.first_name || ' ' || person.last_name);
end loop;
end;
/*
Example Using ALTER PROCEDURE to Set PLSQL_CCFLAGS
ALTER PROCEDURE circle_area COMPILE PLSQL_CCFLAGS = 'my_debug:TRUE'
REUSE SETTINGS;
*/

-----------------------------------------
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<>
LOOP
i := i + 1;
j := 0;
<>
LOOP
j := j + 1;
s := s + i * j; -- sum a bunch of products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
END;
------------------------------------------------------
--cursor with parameter
DECLARE
CURSOR c1(job VARCHAR2, max_wage NUMBER) IS
SELECT *
FROM employees
WHERE job_id = job
AND salary > max_wage;
BEGIN
FOR person IN c1('CLERK', 3000) LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
person.salary || ', Job Id = ' || person.job_id);
END LOOP;
END;

作者“红豆加奶”