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!

How to define function inside a procedure

Discussion in 'SQL PL/SQL' started by mukulverma2408, Aug 22, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    Is it possible to define a function inside a procedure? if yes then how can we do it?
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I seldom nest subprograms within PL/SQL blocks. The vast majority of my development makes use of packages -- which doesn't preclude the use of nested subprograms, but makes it much less likely that there will be any reason for them to exist. My first guess was that the answer was 'no'... but I decided to test. First I made a simple nested procedure, which I knew would work:

    Code (Text):

    DECLARE
      v_val    NUMBER;

      PROCEDURE nest_proc(p_val  OUT  NUMBER)
      IS
      BEGIN
        p_val := 1;
      END nest_proc;
    BEGIN
      nest_proc(v_val);
      DBMS_OUTPUT.PUT_LINE('v_val = ' || v_val);
    END;

    v_val = 1
    I then transformed the nested procedure into a nested function. I was mildly suprised that it works just as well:

    Code (Text):

    DECLARE
      v_val    NUMBER;

      FUNCTION nest_func
      RETURN NUMBER
      IS
      BEGIN
        RETURN 1;
      END nest_func;
    BEGIN
      v_val := nest_func;
      DBMS_OUTPUT.PUT_LINE('v_val = ' || v_val);
    END;

    v_val = 1
    So yes... it can be done and the above example shows how.
     
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Matthew

    This is what i am trying to do
    Code (Text):

    CREATE OR REPLACE PROCEDURE GET_NAME IS
    FNAME EMP.FIRST_NAME%TYPE;
        FUNCTION GET_ID(SAL EMP.SALARY%TYPE) RETURN NUMBER IS
        EID EMP.EMPLOYEE_ID%TYPE;
        BEGIN
          SELECT EMPLOYEE_ID INTO EID FROM EMP WHERE SALARY=SAL;
          RETURN  EID;
        END;
    BEGIN
    SELECT FIRST_NAME INTO FNAME FROM EMP WHERE EMPLOYEE_ID=GET_ID(2100);
    DBMS_OUTPUT.PUT_LINE('FIRST NAME OF THE EMP IS ' || FNAME);
    END;
     
    It's not working and getting below error :
    Code (Text):

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    10/1     PL/SQL: SQL Statement ignored
    10/57    PL/SQL: ORA-00904: : invalid identifier
    10/57    PLS-00231: function 'GET_ID' may not be used in SQL
     
    is it feasible this way or package is the only way to achieve what i want?
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    No. What you are trying is not feasible. Nested functions (and procedures) are not accessible from outside the procedure they are nested within. While that SQL statement is called from inside the procedure -- the function is not accessible to the SQL Engine of Oracle. The SQL engine and the PL/SQL engine are separate entities. Any time a SQL operation is performed within a PL/SQL block, there is an internal 'switch' that takes place where Oracle hands operations over the the SQL Engine until the SQL is processed, at which point it switches back to processing by the PL/SQL engine.

    In this case, when the switch takes place, the SQL engine is now working from outside the PL/SQL block. It looks for a named function called GET_ID... and doesn't find it -- therefore the error.
     
    mukulverma2408 likes this.
  5. João Leite

    João Leite Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Porto
    You can follow the alternative given to use a package to contain the procedure and the function. That would allow the reuse of the function in other contexts.
    But if you want to follow your approach, you can assign the value of the function call to a variable, and then use the value in the query.
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    How exactly is this supposed to work? A function inside a procedure is not visible to the SQL engine for the reasons I stated above, so it cannot be called from a SQL statement. If the function can't be called from SQL... then what is it that will be executing the function in order to place the result in a variable which is then somehow made visible to the SQL engine so that it can be returned to a query?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It IS possible but not by hard-coding salary values into the procedure, and NOT by trying to execute the embedded function through SQL*Plus. You can do this, though:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE GET_NAME (P_SAL IN NUMBER) IS
      2   FNAME EMP.FIRST_NAME%TYPE;
      3   EMPID EMP.EMPLOYEE_ID%TYPE;
      4       FUNCTION GET_ID(SAL EMP.SALARY%TYPE) RETURN NUMBER IS
      5       EID EMP.EMPLOYEE_ID%TYPE;
      6       BEGIN
      7         SELECT EMPLOYEE_ID INTO EID FROM EMP WHERE SALARY=SAL;
      8         RETURN  EID;
      9       END;
    10   BEGIN
    11   EMPID:=GET_ID(P_SAL);
    12   SELECT FIRST_NAME INTO FNAME FROM EMP WHERE EMPLOYEE_ID=EMPID;
    13   DBMS_OUTPUT.PUT_LINE('FIRST NAME OF THE EMP IS ' || FNAME);
    14  EXCEPTION
    15     WHEN NO_DATA_FOUND THEN
    16             DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE FOUND WITH THE SUPPLIED SALARY ('||P_SAL||')');
    17   END;
    18  /

    PROCEDURE created.

    SQL> EXEC get_name(800)
    FIRST NAME OF THE EMP IS SMITH

    PL/SQL PROCEDURE successfully completed.

    SQL> EXEC get_name(1800)
    NO EMPLOYEE FOUND WITH THE SUPPLIED SALARY (1800)

    PL/SQL PROCEDURE successfully completed.

    SQL>
    Notice also the exception handler for when a salary is submitted that doesn't appear in the EMP table. And notice that the function was NOT called in the select statement, it was used to populate a variable.
     
    mukulverma2408 likes this.