有一个存储过程,如下:
create or replace procedure getname(cur_sql out sys_refcursor) is
BEGIN open cur_sql for
SELECT time_id, name, amount
FROM product_output
WHERE time_id like '2010%';end;如何调用该存储过程,展现查询得到的数据?
create or replace procedure getname(cur_sql out sys_refcursor) is
BEGIN open cur_sql for
SELECT time_id, name, amount
FROM product_output
WHERE time_id like '2010%';end;如何调用该存储过程,展现查询得到的数据?
2 BEGIN
3
4 open cur_sql for
5 SELECT empno, ename
6 FROM emp;
7
8 end;
9 /
Procedure created
SQL>
SQL> create or replace procedure display_data
2 as
3 cv_emp sys_refcursor;
4 v_empno emp.empno%type;
5 v_ename emp.ename%type;
6 begin
7 getname(cv_emp);
8 loop
9 fetch cv_emp into v_empno,v_ename;
10 exit when cv_emp%notfound;
11 dbms_output.put_line('empno is '||v_empno||',and ename is '||v_ename||'.');
12 end loop;
13 end;
14 /
Procedure created
SQL> set serveroutput on
SQL> exec display_data;
empno is 7369,and ename is SMITH.
empno is 7499,and ename is ALLEN.
empno is 7521,and ename is WARD.
empno is 7566,and ename is JONES.
empno is 7654,and ename is MARTIN.
empno is 7698,and ename is BLAKE.
empno is 7782,and ename is CLARK.
empno is 7788,and ename is SCOTT.
empno is 7839,and ename is KING.
empno is 7844,and ename is TURNER.
empno is 7876,and ename is ADAMS.
empno is 7900,and ename is JAMES.
empno is 7902,and ename is FORD.
empno is 7934,and ename is MILLER.
PL/SQL procedure successfully completed
SQL>
SQL>exec getname(:tcur);
SQL>print :tcur;
我把你的代码
loop
9 fetch cv_emp into v_empno,v_ename;
10 exit when cv_emp%notfound;
11 dbms_output.put_line('empno is '||v_empno||',and ename is '||v_ename||'.');
12 end loop;改为
for v_cur in cur loop
dbms_output.put_line(v_cur.v_time || ',' || v_cur.v_product_id);
end loop;它就提示 cur不是过程或尚未定义,这是怎么回事?如何解决?