oracle数据库我建了一个存储过程和一个包,主要是想利用参数游标查询我所需要的数据,类似的代码如下:CREATE OR REPLACE PACKAGE spm
IS TYPE rctl IS REF CURSOR
PROCEDURE proc1(num1 varchar2,num2 varchar2,rc1 IN OUT rctl);END;CREATE OR REPLACE PROCEDURE proc1(num1 varchar2,num2 varchar2,rc1 OUT spm.rctl
is
v_d1 varchar2(12);
v_d2 varchar2(12);
BEGIN
OPEN rc1 FOR SELECT a,b FROM A,B WHERE A=num1,B=num2 ;
LOOP
FETCH rc1 into v_d1,v_d2;
EXIT WHEN rc1%NOTFOUND;
dbms_output.put_line(v_d1||':'||v_d2);
END LOOP;
CLOSE rc1;
END;我想通过上述存储过程实现在PL/SQL developer 中显示出我所需要的数据:
但我输入 SQL> set serveroutput on
SQL> exec proc1('100','11')提示:
begin proc1('100','11'); end;ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
我想问一下是因为我存储过程写的有问题还是因为我没有传入需要的游标参数??应该怎样为该过程附一个游标参数呢?谢谢
IS TYPE rctl IS REF CURSOR
PROCEDURE proc1(num1 varchar2,num2 varchar2,rc1 IN OUT rctl);END;CREATE OR REPLACE PROCEDURE proc1(num1 varchar2,num2 varchar2,rc1 OUT spm.rctl
is
v_d1 varchar2(12);
v_d2 varchar2(12);
BEGIN
OPEN rc1 FOR SELECT a,b FROM A,B WHERE A=num1,B=num2 ;
LOOP
FETCH rc1 into v_d1,v_d2;
EXIT WHEN rc1%NOTFOUND;
dbms_output.put_line(v_d1||':'||v_d2);
END LOOP;
CLOSE rc1;
END;我想通过上述存储过程实现在PL/SQL developer 中显示出我所需要的数据:
但我输入 SQL> set serveroutput on
SQL> exec proc1('100','11')提示:
begin proc1('100','11'); end;ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
我想问一下是因为我存储过程写的有问题还是因为我没有传入需要的游标参数??应该怎样为该过程附一个游标参数呢?谢谢
declare
r spm.rct1;
begin
proc1( '100 ', '11 ',r);
end;
存储过程有三个参数
调用的地方也要是三个参数
这个存储过程定义的有问题阿,包头、包体的参数类型不一样,是一个存储过程么
调用的时候,
declare
r spm.rct1;
begin
spm.proc1( '100 ', '11 ',r);
end;
2 p_tname in varchar2,
3 result out sys_refcursor
4 )
5 is
6 temp varchar2(1000);
7 begin
8 temp:='select * from ' || p_tname;
9 OPEN result FOR temp;
10 end;
11 /过程已创建。已用时间: 00: 00: 00.04
scott@SZTYORA>
scott@SZTYORA> var c_ur refcursor;
scott@SZTYORA> exec proc_test('emp',:c_ur);PL/SQL 过程已成功完成。已用时间: 00: 00: 00.01
scott@SZTYORA> print c_ur; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10已选择14行。已用时间: 00: 00: 00.07
scott@SZTYORA>
is
v_d1 varchar2(12);
v_d2 varchar2(12);
BEGIN
OPEN rc1 FOR SELECT a,b FROM A,B WHERE A=num1,B=num2 ;--,改成and
/*LOOP
FETCH rc1 into v_d1,v_d2;
EXIT WHEN rc1%NOTFOUND;
dbms_output.put_line(v_d1||':'||v_d2);
END LOOP;*/--如果没必要,这部分也去掉
/*CLOSE rc1;*/--去掉,都close了,你还out它做什么
END;