过程三：（查询薪水大于某个值的员工）
18:48:59 SCOTT@ prod>create or replace procedure p3(p_sal number) as
18:50:55 2 r_t1 t1%rowtype;
18:50:55 3 type c_type is ref cursor;
18:50:56 4 c1 c_type;
18:50:56 5 begin
18:50:56 6 open c1 for '
18:50:56 7 select * from t1
18:50:56 8 where sal >:1'
18:50:56 9 using p_sal;
18:50:56 10 loop
18:50:56 11 fetch c1 into r_t1;
18:50:56 12 exit when c1%notfound;
18:50:56 13 dbms_output.put_line('Salary higher '||to_char(p_sal)||' Name is:');
18:50:56 14 dbms_output.put_line('ID is ' ||to_char(r_t1.id)||' Name is: '||r_t1.name);
18:50:56 15 end loop;
18:50:56 16 close c1;
18:50:56 17 end p3;
18:50:57 18 /
Procedure created.
Elapsed: 00:00:00.12
18:50:58 SCOTT@ prod>exec p3(2000);
Salary higher 2000 Name is:
ID is 10 Name is: scott
Salary higher 2000 Name is:
ID is 20 Name is: tom
Salary higher 2000 Name is:
ID is 30 Name is: jerry
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
18:51:15 SCOTT@ prod>
注意：在过程二中的动态SQL语句使用了占位符“:1“，其实它相当于函数的形式参数，使用”：“作为前缀，然后使用using语句将p_id在运行时刻将:1给替换掉，这里p_id相当于函数里的实参。另外过程三中打开的游标为动态游标，它也属于动态SQL的范畴，其整个编译和开发的过程与execute immediate执行的过程很类似。