关于ORACLE返回数据集的问题，望高手解答

Question

CREATE OR REPLACE  PACKAGE SYSTEM.TEST_PAK is
type testresultset is ref cursor;
end test_pak;
--建立程序包头 得到数据集，
CREATE OR REPLACE  PROCEDURE system.up_select 
 (str varchar2,RESULTSET OUT TEST_PAK.TESTRESULTSET)
ASbegin
  OPEN RESULTSET FOR
select u_id,u_name from SYSTEM.t_user where u_name = str;
end;
--一个简单的存储过程，不过能说明问题
------------------------------------
问题1:这么得到数据集有没有问题，如果没有问题。我想在SQL/PLUS还有PL/SQL D里面查看到该数据集的语句该怎么写，因为只能执行成功 看不到结果。接下来是第二个问题
create global temporary table system.tmp_usertable
 (u_id varchar2(10),u_name  varchar2(50)
)
  on commit delete rows;
-----建立事务级别临时表我想在另外一个存储过程中调用这个存储过程 比方说CREATE OR REPLACE  PROCEDURE system.up_insert  
 (str varchar2,RESULTSET OUT TEST_PAK.TESTRESULTSET)
AS
begin
 insert into tmp_usertable
 up_select(str,result)-----调用上面的存储过程，类似这样的功能 语句该怎么写
end;
有人给我回复是这样的 虽然有些看不明白，可感觉问题不大
create table emps(emp_id number(6),fname varchar2(20),salary number(8,2),status varchar2(10))
/create or replace procedure get_employees 
(slr number, rs out sys_refcursor)
is
begin
 open rs for select employee_id,first_name,salary*(1+nvl(commission_pct,0)) salary
  from employees where salary>slr;
end;
/create or replace procedure insert_emps
(salary number,status varchar2)
is
 type employees_rec_type is record (emp_id number(6),fname varchar2(20),salary number(8,2));
 type employees_tab_type is table of employees_rec_type;
 employees_tab employees_tab_type;
 
 type emps_tab_type is table of emps%rowtype;
 emps_tab emps_tab_type:=emps_tab_type();
 
 rs sys_refcursor;
begin
 get_employees(salary,rs);
 
-- 批量提取结果集到 nested table
 fetch rs bulk collect into employees_tab;
 
-- 处理要插入 emps 表的数据
 for i in 1..employees_tab.count loop
  emps_tab.extend;
  emps_tab(i).emp_id:=employees_tab(i).emp_id;
  emps_tab(i).fname:=employees_tab(i).fname;
  emps_tab(i).salary:=employees_tab(i).salary;
  emps_tab(i).status:=status;
 end loop;
  
-- 将数据批量插入 emps 表
 forall i in 1..emps_tab.count
 insert into emps values emps_tab(i);
 
 close rs;
 commit;
end;
/begin
 insert_emps(5000,'working');
end;
/select * from emps;drop procedure insert_emps
/
drop procedure get_employees
/
drop table emps purge
这是测试用的 可是问题又出来了，其中有个语句
-- 批量提取结果集到 nested table
 fetch rs bulk collect into employees_tab;
这个语句在oracle9.2.0.1版本中是执行不过去的 有没有其他语句代替 如果没有 还有什么办法解决这个问题

Answer 1

希望给出详细的解决办法 ORACLE存储过程不让返回数据集与值，因为以前的PB程序与SQL SERVER2000数据库，pb数据窗口调用存储过程 需要有个返回值做判断 ORACLE存储过程不让返回值 这个问题改怎么解决啊 
比方说
CREATE OR REPLACE PROCEDURE system.up_select  
 (str varchar2)
ASbegin
  if str = 'ok' then 
   --如果这是SQL SERVER 2000 我完全可以这么写
   select 1;
    else 
   --如果这是SQL SERVER 2000 我完全可以这么写   select 0 ;
   end if
end;
其中ORACLE存储过程返回值 有什么解决办法 在存储过程里加个 OUT参数 能不能解决这个问题

Answer 2

这样呢?看看成不
create table EMP
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2)
);create or replace procedure get_employees
(slr number, rs out sys_refcursor)
is
begin
 open rs for select t.empno,t.ename,t.sal  from emp t where t.empno=slr;
end;create or replace procedure insert_emps
(salary number,status varchar2)
is
 type employees_rec_type is record (emp_id number(6),fname varchar2(20),salary number(8,2));
 type employees_tab_type is table of employees_rec_type;
 employees_tab employees_tab_type; type emps_tab_type is table of emps%rowtype;
 emps_tab emps_tab_type:=emps_tab_type();
 rs sys_refcursor;
 v_num number(10);
begin
 get_employees(salary,rs);-- 批量提取结果集到 nested table
 v_num:=0;
 loop
 exit when rs%notfound;
  v_num:=v_num+1;
  fetch rs  into employees_tab(v_num);
 end loop;-- 处理要插入 emps 表的数据
 for i in 1..employees_tab.count loop
  emps_tab.extend;
  emps_tab(i).emp_id:=employees_tab(i).emp_id;
  emps_tab(i).fname:=employees_tab(i).fname;
  emps_tab(i).salary:=employees_tab(i).salary;
  emps_tab(i).status:=status;
 end loop;-- 将数据批量插入 emps 表
 forall i in 1..emps_tab.count
 insert into emps values emps_tab(i); close rs;
 commit;
end;

Answer 3

上面好像有问题.sqlplus里可以这么查看返回的cursor
创建一个匿名块，使用SQL*Plus的替代变量emp_num（雇员编号），查询emp表，通过外部变量显示对应的雇员名
1.refcursor 返回
SQL> define emp_no=7369
SQL> var o_cur refcursor
SQL> DECLARE
  2
  3  BEGIN
  4  OPEN :o_cur FOR SELECT* FROM emp WHERE empno=&emp_no;
  5  END;
  6  /
old   4: OPEN :o_cur FOR SELECT* FROM emp WHERE empno=&emp_no;
new   4: OPEN :o_cur FOR SELECT* FROM emp WHERE empno=7369;PL/SQL procedure successfully completed.SQL> print o_cur     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20
SQL>

Answer 4

在PL/SQL里如何查询返回的数据集呢 在PLPLUS里面可以这么写
sql>var x refcursor;
sql>exec MENURIGHT('user_code', :x);
sql>print :x;

Answer 5

我只关心的是
v_num:=0;
 loop
 exit when rs%notfound;
  v_num:=v_num+1;
  fetch rs  into employees_tab(v_num);
 end loop;
这块有没有问题 因为你主要就是修改了这一块 实现-- 批量提取结果集到 nested table这个功能

Answer 6

这样可以,但是就是太笨了些.我再看看吧.
create or replace procedure insert_emps
(salary number,status varchar2)
is
 type employees_rec_type is record (emp_id number(6),fname varchar2(20),salary number(8,2));
 type employees_tab_type is table of employees_rec_type index by binary_integer;
 employees_tab employees_tab_type; type emps_tab_type is table of emps%rowtype;
 emps_tab emps_tab_type:=emps_tab_type();
 rs sys_refcursor;
 v_num number(10);
 v_empid number(6);
 v_ename varchar2(100);
 v_sal   number(8,2);
begin
 get_employees(salary,rs);-- 批量提取结果集到 nested table
 v_num:=0;
 loop
 exit when rs%notfound;
  v_num:=v_num+1;
  fetch rs  into v_empid,v_ename,v_sal;
  employees_tab(v_num).emp_id:=v_empid;
  employees_tab(v_num).fname:=v_ename;
  employees_tab(v_num).salary:=v_sal;
  
 end loop;-- 处理要插入 emps 表的数据
 for i in 1..employees_tab.count loop
  emps_tab.extend;
  emps_tab(i).emp_id:=employees_tab(i).emp_id;
  emps_tab(i).fname:=employees_tab(i).fname;
  emps_tab(i).salary:=employees_tab(i).salary;
  emps_tab(i).status:=status;
 end loop;-- 将数据批量插入 emps 表
 forall i in 1..emps_tab.count
 insert into emps values emps_tab(i); close rs;
 commit;
end;

Answer 7

上面那个有点小失误.fetch写错地方了.最后一条数据会重复.
create or replace procedure insert_emps
(salary number,status varchar2)
is
 type employees_rec_type is record (emp_id number(6),fname varchar2(20),salary number(8,2));
 type employees_tab_type is table of employees_rec_type index by binary_integer;
 employees_tab employees_tab_type; type emps_tab_type is table of emps%rowtype;
 emps_tab emps_tab_type:=emps_tab_type();
 rs sys_refcursor;
 v_num number(10);
 v_empid number(6);
 v_ename varchar2(100);
 v_sal   number(8,2);
begin
 get_employees(salary,rs);-- 批量提取结果集到 nested table
 v_num:=0;
 loop  
 fetch rs  into v_empid,v_ename,v_sal;
 exit when rs%notfound;
  v_num:=v_num+1;
  employees_tab(v_num).emp_id:=v_empid;
  employees_tab(v_num).fname:=v_ename;
  employees_tab(v_num).salary:=v_sal;
  
 end loop;-- 处理要插入 emps 表的数据
 for i in 1..employees_tab.count loop
  emps_tab.extend;
  emps_tab(i).emp_id:=employees_tab(i).emp_id;
  emps_tab(i).fname:=employees_tab(i).fname;
  emps_tab(i).salary:=employees_tab(i).salary;
  emps_tab(i).status:=status;
 end loop;-- 将数据批量插入 emps 表
 forall i in 1..emps_tab.count
 insert into emps values emps_tab(i); close rs;
 commit;
end;

Answer 8

 v_num:=0;
 loop
 exit when rs%notfound;
  v_num:=v_num+1;
  fetch rs  into v_empid,v_ename,v_sal;
  employees_tab(v_num).emp_id:=v_empid;
  employees_tab(v_num).fname:=v_ename;
  employees_tab(v_num).salary:=v_sal;
  
 end loop;
你的意思这块语法是没问题的 执行结果也是没问题的 就是语法复杂了是这个意思吧 能实现
-- 批量提取结果集到 nested table这个功能
对吧 无非就是加3个变量 然后挨个字段赋值 用for循环遍历表中的行而已 对吧 这样的可能性是不是会很慢呢

Answer 9

好像用的不是FOR循环 这个我看错了 呵呵

Answer 10

你的版本不支持bulk的话也只有循环了.我只是觉得写的很笨,应该有比较简单的写法.

Answer 11

先这么用吧 你有时间想到好办法告诉我啊 呵呵 我问下 在pl/sql 里面查询数据集出来 你知道怎么写语句吗  还有就是1楼我问的那个问题 如果返回值在存储过程里面 用OUT参数可以吗 我怎么感觉够呛呢 有点迷糊

Answer 12

out就可以了.用test窗口返回值可以点击查看的.
还有就是通过sqlplus按上面写的展示.

Answer 13

这个在sqlplus里面就可以 打印出数据集信息 我想知道 在PL/SQL里面如果能看到数据集信息呢 你上面说的TEST 是不是就是调试存储过程那个功能 可以加段点的那个

Answer 14

对啊.plsql developer里的test窗口,如果是out参数的话,执行完下面列表里是可以查看返回值的.如果是cursor,返回值那里是几个小点,点开会打开一个窗口,里面就是cursor的情况.

Answer 15

好了 谢谢你了 我该弄东西了 一个破数据库 我都弄了半个多月了 从sql server 2000导到ORACLE里面 领导说先让我趟路子 也没给我定啥时间 我这人急性子 赶紧干完得了 呵呵  以后还得麻烦你啊 有问题记得帮我看看啊 呵呵