--建立一个包以定义一个游标
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;
/*
问题:以上调用是有问题的,无法使用,本人是初学者,觉得这样的调用应该是没问题的吧!
请大家帮忙解悟!
*/
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;
/*
问题:以上调用是有问题的,无法使用,本人是初学者,觉得这样的调用应该是没问题的吧!
请大家帮忙解悟!
*/
INTO e_name, e_job;
这里应该用pl/sql表,一个变量一次只能接收一个值,不能fetch BULK COLLECT
type t_name is table of scott.emp.ename%type;
type t_job is table of scott.emp.job%type;
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
CLARKMANAGERPL/SQL 过程已成功完成。SQL>
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