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 2, 2014.

  1. keshav jain

    keshav jain Active Member

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

    I am trying to create a Stored Procedure in which I want to show the sum of two column. I am trying to do it in Sql Developer. So please tell me how to code it.
    I wrote following code...

    create or replace procedure IC_FISCAL_VIEW_TEST2(PARM_YEAR in NUMBER, PARM_PERIOD IN NUMBER,PARM_CATG_TYP IN VARCHAR2,
    PARM_MLTS IN VARCHAR2, PARM_MCO IN VARCHAR2, PARM_COA IN VARCHAR2, PARM_TYPE IN VARCHAR2 )
    is

    begin

    DECLARE
    LOC_YEAR INTEGER ;
    LOC_PERIOD INTEGER ;
    CURSOR C1 IS

    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
    GROUP BY YEAR,PERIOD
    ORDER BY YEAR DESC,PERIOD DESC;

    BEGIN
    select year,period from
    (
    Select distinct year, period from PLAN_MCATG_MEMB_MSTR
    Order by year desc, period desc
    )
    where rownum<2;

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

    OPEN C1;


    END;
    end;

    Showing Error:

    Error(21,1): PLS-00428: an INTO clause is expected in this SELECT statement.
     
  2. rajenb

    rajenb Forum Expert

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

    Here's a simple example:

    Code (SQL):
    CREATE TABLE table1(
    col1 NUMBER,
    col2 NUMBER
    );
    INSERT INTO table1 VALUES (2, 3);
    --
    CREATE OR REPLACE FUNCTION f_sum_columns
    RETURN NUMBER
    IS
    l_sum NUMBER;
    BEGIN
      SELECT col1+col2
      INTO l_sum
      FROM table1;
      RETURN l_sum;
    END f_sum_columns;
    /

    SELECT f_sum_columns FROM dual;
    But it depends on exactly what you want to achieve.
    Please have a look at the Oracle documentation which provides ample explanation + examples on this subject.
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Open your defined connection, right click Procedures in the resulting tree list, choose New Procedure, give it a name and parameters (if you have any), click the DDL tab and insert the code you want...or just click OK. Voila! There's your procedure.

    HTH

    CJ
     
  4. keshav jain

    keshav jain Active Member

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

    I can not find where is ddl tab.
     
  5. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    hi
    actually i want to find the sum of a column and want to show that.
    Can you give me another example.
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    If you right click on Procedure in the navigation tree under your Connections you will get a dialog box. It will show your current schema, a default procedure name of "Procedure1". Immediately below that are two tabs in that window. One for Parameters and another for DDL.

    My version is 3.2.20.09 Build MAIN-09.87
     
  7. rajenb

    rajenb Forum Expert

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

    Your code has several mistakes....

    1) You need to correct it first (compilation errors as you reported):
    Replace
    with
    In a PL/SQL, you need to have the INTO clause after a SELECT.

    2) If you want an output from a procedure, you need to include OUT parameters to retrieve the values in the calling routines.
    3) Lots of unnecessary codes in your procedure.

    There are parameters which are not used (now) but I suppose you'll add more code to handle those.
    For the time being, I've tried to simplify it - guessing from your listing. See code below:

    Code (SQL):
    CREATE OR REPLACE
    PROCEDURE IC_FISCAL_VIEW_TEST2(
        parm_year     IN NUMBER,
        parm_period   IN NUMBER,
        parm_catg_typ IN VARCHAR2,
        parm_mlts     IN VARCHAR2,
        parm_mco      IN VARCHAR2,
        parm_coa      IN VARCHAR2,
        parm_type     IN VARCHAR2,
        x_sum_year    OUT NUMBER,
        x_sum_period  OUT NUMBER
      )
    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 YEAR,
          period
        INTO loc_year,
          loc_period
        FROM
          ( SELECT DISTINCT YEAR,  period
          FROM PLAN_MCATG_MEMB_MSTR
          ORDER BY YEAR DESC,
            period DESC
          )
        WHERE rownum <2;
      END IF;
      SELECT SUM(membr)      AS Value_TMM,
        ROUND(SUM(membr)/12) AS Value_AMM
      INTO x_sum_year,
        x_sum_period
      FROM PLAN_MCATG_MEMB_MSTR
      WHERE YEAR = loc_year
      AND period = loc_period;
    END IC_FISCAL_VIEW_TEST2;
    /
    And the call to test it:

    Code (SQL):
    DECLARE
    l_sum_year NUMBER;
    l_sum_period NUMBER;
    BEGIN
       IC_FISCAL_VIEW_TEST2(
          parm_year     => NULL,
          parm_period   => NULL,
          parm_catg_typ => NULL,
          parm_mlts     => NULL,
          parm_mco      => NULL,
          parm_coa      => NULL,
          parm_type     => NULL,
          x_sum_year    => l_sum_year,
          x_sum_period  => l_sum_period
       );
       dbms_output.put_line(l_sum_year);
       dbms_output.put_line(l_sum_period);
    END;
    /
    I hope you can continue or use this sample to proceed to your next step. Let us know if you encounter any issues.
     
  8. keshav jain

    keshav jain Active Member

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

    thank you so much.
    It's really help ful