oracle初学者你请教

[duanzhi1984]

--建立一个包以定义一个游标
create or replace package PK_test
as
type cur_test is ref cursor;
end PK_test;

--建立一个过程,通过输入JOBID，返回一个表记录（游标）!
create or replace procedure PR_TEST_CURSOR
(JOBID IN VARCHAR2,cur_test out pk_test.cur_test)
as
begin
open cur_test for select ENAME,JOB from emp where JOB=JOBID;
end;

--通过过程调用生成游标(红色部分），并利用游标打印.
declare
type t_name is table of scott.emp.ename%type;
type t_job is table of scott.emp.job%type;
cr_test pk_test.cur_test;
e_name t_name;
e_job t_job;
begin
--open cr_test for select ename,job from emp where job='MANAGER';
PR_TEST_CURSOR('MANAGER',cr_test);
fetch cr_test BULK COLLECT
INTO e_name, e_job;
CLOSE cr_test;

for i in e_name.first..e_name.last loop
dbms_output.put_line(e_name(i)||e_job(i));
end loop;
end;


/*
问题：以上调用是有问题的,无法使用，本人是初学者，觉得这样的调用应该是没问题的吧！
请大家帮忙解悟！
*/

[duanzhi1984]

顶一个先，没人能解决。奇怪了

[vber1010]

暂时没看出问题，报错是什么？

[suiziguo]

fetch cr_test BULK COLLECT
INTO e_name, e_job;


这里应该用pl/sql表，一个变量一次只能接收一个值，不能fetch BULK COLLECT

[duanzhi1984]

[Quote=引用 1 楼 suiziguo 的回复:]
fetch cr_test BULK COLLECT
INTO e_name, e_job;


这里应该用pl/sql表，一个变量一次只能接收一个值，不能fetch BULK COLLECT
[/Quote]
我用的是表啊!!
type t_name is table of scott.emp.ename%type;
type t_job is table of scott.emp.job%type;

[叶涛网站推广优化]

我这也没问题啊

[iqlife]

错误是什么,?

[tangren]

没有问题，10g 10.2.0.1.0

SQL> SET SERVEROUTPUT ON;
SQL> --建立一个包以定义一个游标
SQL> CREATE OR REPLACE PACKAGE PK_test AS
  2    TYPE cur_test IS REF CURSOR;
  3  END PK_test;
  4  /

程序包已创建。

SQL> 
SQL> --建立一个过程,通过输入JOBID，返回一个表记录（游标）!
SQL> CREATE OR REPLACE PROCEDURE PR_TEST_CURSOR(JOBID    IN VARCHAR2,
  2                                             cur_test OUT pk_test.cur_test) AS
  3  BEGIN
  4    OPEN cur_test FOR
  5      SELECT ENAME, JOB FROM emp WHERE JOB = JOBID;
  6  END;
  7  /

过程已创建。

SQL> 
SQL> -- 通过过程调用生成游标(红色部分），并利用游标打印.
SQL> DECLARE
  2    TYPE t_name IS TABLE OF scott.emp.ename%TYPE;
  3    TYPE t_job IS TABLE OF scott.emp.job%TYPE;
  4    cr_test pk_test.cur_test;
  5    e_name  t_name;
  6    e_job   t_job;
  7  BEGIN
  8    --open cr_test for select ename,job from emp where job='MANAGER';
  9    PR_TEST_CURSOR('MANAGER', cr_test);
 10    FETCH cr_test BULK COLLECT
 11      INTO e_name, e_job;
 12    CLOSE cr_test;
 13  
 14    FOR i IN e_name.first .. e_name.last LOOP
 15      dbms_output.put_line(e_name(i) || e_job(i));
 16    END LOOP;
 17  END;
 18  /
JONESMANAGER
BLAKEMANAGER
CLARKMANAGER

PL/SQL 过程已成功完成。

SQL> 

[duanzhi1984]

错误是:

ORA-06550: line 5, column 11:
PLS-00905: object SYS.PK_TEST is invalid
ORA-06550: line 5, column 11:
PL/SQL: Item ignored
ORA-06550: line 10, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
ORA-06550: line 11, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored