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!

Oracle Stored Procedure

Discussion in 'SQL PL/SQL' started by keshav jain, Jul 3, 2014.

  1. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    Hi All

    I have writing a SP but stuck in way to write parametrized corsor
    My code is following....
    create or replace procedure IC_FISCAL_VIEW_TEST10(PARM_YEAR in NUMBER, PARM_PERIOD IN NUMBER,
    PARM_MLTS IN VARCHAR2, PARM_PLAN IN VARCHAR2, PARM_MCATG IN VARCHAR2, PARM_TYPE IN VARCHAR2, P_CURSOR OUT TYPE.CURSORTYPE )
    is

    LOC_YEAR INTEGER ;
    LOC_PERIOD INTEGER ;


    BEGIN

    IF (PARM_YEAR IS NOT NULL AND PARM_PERIOD IS NOT NULL) THEN
    LOC_YEAR := PARM_YEAR ;
    LOC_PERIOD := PARM_PERIOD ;
    ELSE

    SELECT DISTINCT YEAR, PERIOD INTO LOC_YEAR, lOC_PERIOD FROM PLAN_MCATG_RATE_MSTR
    WHERE
    ROWNUM = 1 ;

    END IF;

    IF (PARM_PLAN = 'ALL' AND PARM_MCATG = 'ALL') THEN

    IF PARM_TYPE = 'MM' THEN

    OPEN P_CURSOR FOR

    SELECT YEAR, PERIOD, sum(membr) as Value_TMM, ROUND(sum(membr)/12) as Value_AMM
    FROM PLAN_MCATG_MEMB_MSTR
    WHERE
    YEAR = LOC_YEAR
    AND PERIOD = LOC_PERIOD
    AND MLTS_TYP = PARM_MLTS
    GROUP BY YEAR,PERIOD;

    END IF;

    /* Write here cursor to get list of all Plans and COAs */
    IF PARM_TYPE = 'MD' THEN

    OPEN P_CURSOR FOR

    SELECT DISTINCT TYPE, CODE,"DESC" FROM(
    select DISTINCT 'MCO' AS TYPE ,PLAN_CDE AS CODE , MSTR_DESC AS "DESC" FROM ICUSER.MASTER_PLAN
    UNION
    SELECT DISTINCT 'MCATG' AS TYPE, MSTR_DESC AS CODE,MSTR_DESC AS "DESC" FROM ICUSER.MASTER_MCATG) ORDER BY TYPE DESC, CODE ;

    END IF;

    ELSE

    IF PARM_TYPE = 'MM' THEN

    OPEN P_CURSOR FOR

    SELECT YEAR, PERIOD, sum(membr) as Value_TMM, ROUND(sum(membr)/12) as Value_AMM
    FROM PLAN_MCATG_MEMB_MSTR
    WHERE
    YEAR = LOC_YEAR
    AND PERIOD = LOC_PERIOD
    AND MLTS_TYP = PARM_MLTS
    AND MSTR_PLAN = PARM_PLAN
    AND MSTR_MEM_CATG = PARM_MCATG
    GROUP BY YEAR,PERIOD;

    END IF;

    /* Write here parameterized cursor to get list of Plans and COAs using table master_plan and member_mcatg */


    END IF;
    END IF;
    END;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    May you please explain with some examples.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    I suppose , that to the link which I provided - there were good examples....

    Simple example :

    Code (SQL):

    SET serveroutput ON
    SET echo off
    DECLARE
     l_cur   sys_refcursor;
     l_tab   sys.odcinumberlist;
     
     -- open cursor
     PROCEDURE get_cursor (p_out OUT sys_refcursor,p_cnt_elem  pls_integer )
         AS
     BEGIN
         OPEN p_out FOR
         SELECT  rownum id
                 FROM user_tables
         WHERE rownum <= p_cnt_elem;    
     END;        
     
    BEGIN
        -- get cursor handle
        get_cursor(l_cur,&count_elem);
       
        fetch l_cur bulk collect INTO l_tab;        
       
        FOR z IN 1 .. l_tab.COUNT
        loop
            dbms_output.put_line('Z['||z||']= '||l_tab(z));
        END loop;    
        close l_cur;    
    END;    
    /

     


    Or in what your question....
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Keshav,

    You can't define a cursor 'on the fly' as your code implies. You must DECLARE them beforehand.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE MYPROC IS

         CURSOR my_csr IS
              SELECT ...your statement here;

    BEGIN
       OPEN my_csr;
       FETCH my_csr INTO ...something
       CLOSE my_csr;

       ...do something WITH the DATA
    END;
     
    If you wish to parameterize the cursor then the declaration changes to :
    Code (SQL):
    CURSOR my_csr(my_param NUMBER) IS
         SELECT stuff FROM here WHERE my_param = something
    Note that you reference the param in the where clause of the select statement defining the cursor. I would suggest you subscribe to Oracle Magazine and look up the articles on PL/SQL by Steven Feuerstein or pick up his book "Oracle PL/SQL Programming" via Amazon. The explanations therein on cursors is rather straightforward and quite useful.

    HTH

    CJ
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Keshav,

    Both options/solutions (provided by Sergey & DTSIGuy) are valid but it's still not clear what you require from your calling routines.

    Do you absolutely need a "parametrized cursor" (an output of cursor type) ?
    Do you just need this type of output for testing purposes or it's the "technical requirement" from the calling modules ? In which case, I suppose you need to "decode" the output (constituents of the cursor records) depending on the parameters you pass to the SP.

    And once we get to know your requirements, then we would be in a better position to see which fits best !