create or replace package pkg_content
as
Type cur_content is ref cursor;
procedure getContent
(parm_rec out cur_content);
end pkg_content;
create or replace package body pkg_content
as
procedure getContent
(parm_rec out cur_content)
as
begin
open parm_rec
for
select subject,username,createtime,lastanswertime,hitcount
from content;
end getContent;
end pkg_content;
本人刚学习PL/SQL,请高手写一匿名块,调用上面软件包中的存储过程,将游标中的数据打印出来.谢谢!
------解决方案--------------------
在sqlplus中依次输入如下命令:
set serveroutput on;
var c refcursor;
exec pkg_content.getContent(:c);
print c;
------解决方案--------------------
收藏的一个例子：
- SQL code
--tom写的print_table--将一个查询的结果 记录by记录 地列出来create or replace procedure print_table( p_query in varchar2 )AUTHID CURRENT_USERisl_theCursor integer default dbms_sql.open_cursor;l_columnValue varchar2(4000);l_status integer;l_descTbl dbms_sql.desc_tab;l_colCnt number;beginexecute immediate'alter session setnls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );for i in 1 .. l_colCnt loopdbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);end loop;l_status := dbms_sql.execute(l_theCursor);while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loopfor i in 1 .. l_colCnt loopdbms_sql.column_value( l_theCursor, i, l_columnValue );dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )|| ': ' ||l_columnValue );end loop;dbms_output.put_line( '-----------------' );end loop;execute immediate'alter session set nls_date_format=''dd-MON-rr'' ';exceptionwhen others thenexecute immediate'alter session set nls_date_format=''dd-MON-rr'' ';raise;end;/
------解决方案--------------------
- SQL code
create or replace package pkg_employees as Type cur_employees is ref cursor; procedure getemployees (parm_rec out cur_employees); end pkg_employees; create or replace package body pkg_employees as procedure getemployees (parm_rec out cur_employees) as begin open parm_rec for select employee_id,last_name,salary from employees where salary > 10000 order by salary desc; end getemployees; end pkg_employees;declare p_cursor pkg_employees.cur_employees; eid EMPLOYEES.EMPLOYEE_ID%type; elastname EMPLOYEES.LAST_NAME%type; esalary EMPLOYEES.SALARY%type;begin pkg_employees.getemployees(p_cursor); loop fetch p_cursor into eid,elastname,esalary; exit when p_cursor%notfound; dbms_output.put_line(eid || '|' || elastname || '|' || esalary); end loop;end;