1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Rowtype and refcursors are Illustrated with examples here

Discussion in 'SQL PL/SQL' started by Bharat, May 19, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Data types for a is varchar2 as in emp table ename column and b is for creating entire row:

    Code (SQL):
    DECLARE
    a emp.ename%TYPE;
    b emp%rowtype;
    BEGIN
    SELECT ename,sal,job INTO a,b.sal,b.job FROM emp WHERE empno=7844;
    dbms_output.put_line(a||' '||b.sal||' '||b.job);
    END;
    /
    -------------------------------------------------------------------------------------------------------------------------
    refcursor for procedure:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE p1(c IN OUT sys_refcursor)AS
    BEGIN
    OPEN c FOR
    SELECT * FROM emp WHERE deptno=10;
    END;
    /
     
    var a refcursor;
    EXEC p1(:a);
    print a;
     
    (OR)
     
    BEGIN
    p1(:a);
    END;
    -----------------------------------------------------------------------------------------------------------------------------
    parameterized cursor:

    Code (SQL):
    DECLARE
    CURSOR cur_tmp(dept NUMBER) IS
    SELECT ename,empno
    FROM emp
    WHERE deptno =dept;
    -- v_rec cur_tmp%ROWTYPE;
    BEGIN
    FOR i IN cur_tmp(10) LOOP
    dbms_output.put_line(i.ename);
    END LOOP;
    FOR i IN cur_tmp(20) LOOP
    dbms_output.put_line(i.empno);
    END LOOP;
    END;
    /
    ------------------------------------------------------------------------------------------------------------------------------------------
    DECLARE
    CURSOR cur_tmp (dept VARCHAR2) IS
    SELECT ename, deptno FROM emp WHERE deptno = dept;
    emp_rec cur_tmp%ROWTYPE;
    BEGIN
    OPEN cur_tmp(30);
    FETCH cur_tmp INTO emp_rec;
    dbms_output.put_line(emp_rec.ename);
    END;
    /
    --------------------------------------------------------------------------------------------------------------------------------------------
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been nice to see the output from these examples posted along with the code; doing so would show the examples work as expected as some members may not have access to an Oracle database or the demonstration tables.
     
    Bharat likes this.