--sqlplus下运行 create or replace procedure pro_show_emp( empno_in in scott.emp.empno%type, cv_emp in out sys_refcursor --定义一个游标变量作为输出结果集 ) is begin open cv_emp for select * from scott.emp emp where emp.empno=empno_in;
exception when others then dbms_output.put_line(sqlerrm); end pro_show_emp; / SQL> variable x refcursor;--定义一个游标变量,接收por_show_emp返回的结果 SQL> exec pro_show_emp(7788,:x); PL/SQL procedure successfully completed. SQL> print x; --将返回的游标结果打印出来 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3100 20
SQL> set serveroutput on; SQL> declare 2 cursor cur_emp is select empno,ename,job from emp; 3 begin 4 for i in cur_emp loop 5 dbms_output.put_line('员工号:'||i.empno||' 员工姓名:'||i.ename||' 职位:'||i.job); 6 end loop; 7 end; 8 /
http://www.qqread.com/oracle/2008/07/b417687.html
--sqlplus下运行
create or replace procedure pro_show_emp(
empno_in in scott.emp.empno%type,
cv_emp in out sys_refcursor --定义一个游标变量作为输出结果集
)
is
begin
open cv_emp for
select * from scott.emp emp
where emp.empno=empno_in;
exception
when others then
dbms_output.put_line(sqlerrm);
end pro_show_emp;
/
SQL> variable x refcursor;--定义一个游标变量,接收por_show_emp返回的结果
SQL> exec pro_show_emp(7788,:x);
PL/SQL procedure successfully completed.
SQL> print x; --将返回的游标结果打印出来 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3100 20
SQL> declare
2 cursor cur_emp is select empno,ename,job from emp;
3 begin
4 for i in cur_emp loop
5 dbms_output.put_line('员工号:'||i.empno||' 员工姓名:'||i.ename||' 职位:'||i.job);
6 end loop;
7 end;
8 /
员工号:111 员工姓名:B 职位:
员工号:7369 员工姓名:SMITH 职位:CLERK
员工号:7499 员工姓名:ALLEN 职位:SALESMAN
员工号:7521 员工姓名:WARD 职位:SALESMAN
员工号:7566 员工姓名:JONES 职位:MANAGER
员工号:7654 员工姓名:MARTIN 职位:SALESMAN
员工号:7698 员工姓名:BLAKE 职位:MANAGER
员工号:7782 员工姓名:CLARK 职位:MANAGER
员工号:7839 员工姓名:KING 职位:PRESIDENT
员工号:7844 员工姓名:TURNER 职位:SALESMAN
员工号:7900 员工姓名:JAMES 职位:CLERK
员工号:7902 员工姓名:FORD 职位:ANALYST
员工号:7934 员工姓名:MILLER 职位:CLERK
PL/SQL procedure successfully completed
is
……………………
begin
……………………………………………………
end;
is
……………………
begin
……………………………………………………
end;