Can I pass the cursor to a procedure?

Question

Is it possible to pass the cursor to a procedure?

CURSOR  BLT_CURSOR IS
SELECT  BLT.sol_id,
        BLT.bill_id,
        BLT.bank_id
FROM BLT;

This is my cursor.

Procedure abc(i want to pass the cursor here)

How to do it.

Answer 1

I assume that you are using Oracle (it would seem so).

You can do it:

PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS
   v_sol_id blt.sol_id%TYPE;
   v_bill_id blt.bill_id%TYPE;
   v_bank_id blt.bank_id%TYPE;
BEGIN
   LOOP
      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
      EXIT WHEN p_cursor%NOTFOUND;
      ...
   END LOOP;
END;

Then use it:

DECLARE
   v_cursor SYS_REFCURSOR;
BEGIN
   OPEN v_cursor FOR
      SELECT  BLT.sol_id,
              BLT.bill_id,
              BLT.bank_id
      FROM BLT;
   abc (v_cursor);
   CLOSE v_cursor;
END;

However, note that the abc procedure must know the cursor structure, i.e. returns three columns of certain types. If you want to pass any cursor to a procedure, you will need to look at using the DBMS_SQL package (and this is not trivial!)

Answer 2

It may be specific to a DBMS; for Oracle, see 6.12.7 Oracle PL / SQL Programming

Answer 3

MSSQL2005. 2008 .

, . . proc, , .

Answer 4

MSDN (SQL Server 2008): OUTPUT. , VARYING OUTPUT. , .

Answer 5

, . , , , , . , , , .

Answer 6

, , , , , , . , , . , , , , , .

, SQL , , ? , , SQL - , , , ( ) , . , , SQL, , .