| Open a Ref Cursor. - FOR LOOP [message #47993] |
Sun, 10 October 2004 23:44  |
Jai
Messages: 13
Registered: May 2002
|
Junior Member |
|
|
Hi,
I'm trying to open a refcursor using FOR loop. Is it possible.?
Otherwise give me some alternative solutions to do this.
Code: I want to do like this. Is it possible. Otherwise make some corrections in it.
CREATE OR REPLACE PROCEDURE CHECK_REFCUR(L_P_EMPNO NUMBER) IS
TYPE EmpCurTyp IS REF CURSOR;
L_REFCURSOR_CHECK EmpCurTyp;
L_V_SQL_STRING VARCHAR2(200);
BEGIN
L_V_SQL_STRING:='SELECT E.ENAME, D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.EMPNO= ' || L_P_EMPNO;
OPEN L_REFCURSOR_CHECK FOR L_V_SQL_STRING;
FOR REC IN L_REFCURSOR_CHECK LOOP
DBMS_OUTPUT.PUT_LINE(REC.ENAME || REC.DNAME);
END LOOP;
CLOSE L_REFCURSOR_CHECK;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
|
|
|
|
| Re: Open a Ref Cursor. - FOR LOOP [message #47995 is a reply to message #47993] |
Mon, 11 October 2004 00:29  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
A cursor-for loop is an implicit open, a fetch-loop and an implicit close of a cursor.
Since with a ref-cursor you explicitly open the cursor you cannot use a cursor-for loop.
You can however use:
begin
for r in 'select * from emp'
loop
..
end loop;
end;
No (obvious) need for a ref-cursor in your code.
Btw. DO NOT use literals in your cursor-code; the way you paste l_p_empno to your cursor will make your application perform poorly.
Search asktom.oracle.com for 'binds' to see what I mean
hth
|
|
|
|
|
|
| Re: Open a Ref Cursor. - FOR LOOP [message #48024 is a reply to message #47993] |
Tue, 12 October 2004 14:29 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
No, you cannot do it quite like that. You can open a ref cursor, dynamically or not, and fetch from it within a loop. Or you can use a regular cursor for loop, which implicitly opens a cursor, not a ref cursor. I see no need for opening a ref cursor, dynamically or otherwise, so I would just use a regular cursor for loop. However, I recognize that this may be just a simplification of your actual problem, which may require such things. Please see the various example below and adapt whichever best suits your needs or post additional requirements.
scott@ORA92> -- with dynamic ref cursor:
scott@ORA92> CREATE OR REPLACE PROCEDURE with_refcur1
2 (L_P_EMPNO IN emp.empno%TYPE)
3 IS
4 TYPE EmpCurTyp IS REF CURSOR;
5 L_REFCURSOR_CHECK EmpCurTyp;
6 L_V_SQL_STRING VARCHAR2(200);
7 v_ename emp.ename%TYPE;
8 v_dname dept.dname%TYPE;
9 BEGIN
10 L_V_SQL_STRING :=
11 'SELECT E.ENAME, D.DNAME
12 FROM EMP E,DEPT D
13 WHERE E.DEPTNO=D.DEPTNO
14 AND E.EMPNO = :L_P_EMPNO';
15 OPEN L_REFCURSOR_CHECK FOR L_V_SQL_STRING USING l_p_empno;
16 LOOP
17 FETCH l_refcursor_check INTO v_ename, v_dname;
18 EXIT WHEN l_refcursor_check%NOTFOUND;
19 DBMS_OUTPUT.PUT_LINE (v_ename || ' works in ' || v_dname);
20 END LOOP;
21 CLOSE L_REFCURSOR_CHECK;
22 EXCEPTION
23 WHEN OTHERS THEN
24 DBMS_OUTPUT.PUT_LINE (SQLERRM);
25 END with_refcur1;
26 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> VARIABLE g_empno NUMBER
scott@ORA92> EXECUTE :g_empno := 7934
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE with_refcur1 (:g_empno)
MILLER works in ACCOUNTING
PL/SQL procedure successfully completed.
scott@ORA92> -- with ref cursor (but not dynamic):
scott@ORA92> CREATE OR REPLACE PROCEDURE with_refcur2
2 (L_P_EMPNO IN emp.empno%TYPE)
3 IS
4 TYPE EmpCurTyp IS REF CURSOR;
5 L_REFCURSOR_CHECK EmpCurTyp;
6 v_ename emp.ename%TYPE;
7 v_dname dept.dname%TYPE;
8 BEGIN
9 OPEN L_REFCURSOR_CHECK FOR
10 SELECT E.ENAME, D.DNAME
11 FROM EMP E,DEPT D
12 WHERE E.DEPTNO=D.DEPTNO
13 AND E.EMPNO = L_P_EMPNO;
14 LOOP
15 FETCH l_refcursor_check INTO v_ename, v_dname;
16 EXIT WHEN l_refcursor_check%NOTFOUND;
17 DBMS_OUTPUT.PUT_LINE (v_ename || ' works in ' || v_dname);
18 END LOOP;
19 CLOSE L_REFCURSOR_CHECK;
20 EXCEPTION
21 WHEN OTHERS THEN
22 DBMS_OUTPUT.PUT_LINE (SQLERRM);
23 END with_refcur2;
24 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> VARIABLE g_empno NUMBER
scott@ORA92> EXECUTE :g_empno := 7934
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE with_refcur2 (:g_empno)
MILLER works in ACCOUNTING
PL/SQL procedure successfully completed.
scott@ORA92> -- returning ref cursor (not dynamic):
scott@ORA92> CREATE OR REPLACE PROCEDURE with_refcur3
2 (L_P_EMPNO IN emp.empno%TYPE,
3 l_refcursor_check OUT SYS_REFCURSOR)
4 IS
5 BEGIN
6 OPEN L_REFCURSOR_CHECK FOR
7 SELECT E.ENAME, D.DNAME
8 FROM EMP E,DEPT D
9 WHERE E.DEPTNO=D.DEPTNO
10 AND E.EMPNO = L_P_EMPNO;
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE (SQLERRM);
14 END with_refcur3;
15 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> VARIABLE g_empno NUMBER
scott@ORA92> EXECUTE :g_empno := 7934
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE with_refcur3 (:g_empno, :g_ref)
PL/SQL procedure successfully completed.
scott@ORA92> PRINT g_ref
ENAME DNAME
---------- --------------
MILLER ACCOUNTING
scott@ORA92> -- without ref cursor:
scott@ORA92> CREATE OR REPLACE PROCEDURE without_refcur
2 (L_P_EMPNO IN emp.empno%TYPE)
3 IS
4 BEGIN
5 FOR rec IN
6 (SELECT E.ENAME, D.DNAME
7 FROM EMP E,DEPT D
8 WHERE E.DEPTNO=D.DEPTNO
9 AND E.EMPNO = L_P_EMPNO)
10 LOOP
11 DBMS_OUTPUT.PUT_LINE (REC.ENAME || ' works in ' || REC.DNAME);
12 END LOOP;
13 EXCEPTION
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE (SQLERRM);
16 END without_refcur;
17 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> VARIABLE g_empno NUMBER
scott@ORA92> EXECUTE :g_empno := 7934
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE without_refcur (:g_empno)
MILLER works in ACCOUNTING
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
