1、存储过程创建过程如下所示:
CREATE OR REPLACE
procedure test_getsalary(
p_deptno in varchar,
p_cursor out sys_refcursor
)
is begin
open p_cursor for select * from a_table where A_PART=p_deptno;
end test_getsalary;
首先定义两个参数,p_deptno是查询数据的过滤条件,p_cursor是返回数据的游标
2、调用过程如下所示:
declare
v_deptno varchar(20) := '总裁部';
v_cursor sys_refcursor;
r_emp a_table%rowtype;
begin
test_getsalary(v_deptno, v_cursor);
loop
fetch v_cursor into r_emp;
exit when v_cursor%notfound;
dbms_output.put_line('empno: ' || r_emp.a_id || ' ename: ' || r_emp.a_name || ' job: ' || r_emp.a_salary);
end loop;
close v_cursor;
end;