This discussion is archived
11 Replies Latest reply: Aug 5, 2013 2:39 PM by GTS (DBA) RSS

Reference thread for old one  "procedures using IN OUT"

GTS (DBA) Journeyer
Currently Being Moderated

Good Morning   ...

 

Already this  question raised here .

Due to lot of confusion and applying different things ...so , i'm starting new thread here.

This  is  NOT  a  duplicate  thread. both  purpose is different.     


MY  old thread  : https://forums.oracle.com/message/11133790#11133790

 

>> MY  emp table details  >>

 

SQL> desc emp;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EID                                                   NUMBER

ENAME                                            VARCHAR2(15)

EQUAL                                             VARCHAR2(15)

ESALARY                                         NUMBER

ECITY                                               VARCHAR2(15)

EPERK                                               NUMBER

ECONTACT_NO                              NUMBER


SQL> select * from emp;

 

      EID     ENAME        EQUAL        ESALARY         ECITY         EPERK       ECONTACT_NO

     ---------  ---------------  ------------  - ---------- ----------  ------------       -----------

       1       sona               mba                10000                  mumbai                               9994488410

       2      maya              mca                 15000                calcutta                               9994716872

 

>> Program  for  salary  update   using  procedure and it's parameter  >>


1  CREATE or REPLACE  procedure emp_details

  2  (id IN emp.eid%type,

  3   salary IN OUT emp.esalary%type) IS

  4  tmp_sal number;

  5  BEGIN

  6  select esalary into tmp_sal from emp where eid=id;

  7  if tmp_sal between  10000 and  20000 THEN

  8  salary:=tmp_sal*1.2;

  9  else if tmp_sal between  20001  and 29999 THEN

10  salary:=tmp_sal*1.8;

11  else if tmp_sal > 30000 then

12  salary :=tmp_sal*2.0;

13  END IF;

14  END IF;

15  END IF;

16* END;

SQL> /

 

Procedure created.

 

>> what i  expect really   >>

 

Already emp  table having  Esalary column ;

If i execute the procedure ,  all  emp records should be updated as per my condition.

" Already , I done same process with cursors. but  now my requirement  it should be done with "PROCEDURE   and  sub- program arguments."


This is  what  i got    Following  output is not  my  GOAL >> 

------------------------------------------------------------------------------

  1  DECLARE

  2  l_empno number :=1;

  3  l_sal number :=10000;

  4  begin

  5  emp_details(l_empno , l_sal);

  6  DBMS_OUTPUT.PUT_LINE(l_empno || l_sal);

  7* END;

SQL> /

112000   -----  ( bad output )


PL/SQL procedure successfully completed.

 

My questions are :   if i pass  '1'   record sona  esalary will be updated.  [ only with procedure ]

  - This is  what i expect  ..

  

Please Note : if i successfully finished ,  i can applu same logic on eperk column

Thanks in  advance !!

  • 1. Re: Reference thread for old one  "procedures using IN OUT"
    9423755 Explorer
    Currently Being Moderated

    What's the issue?

    I've not run your code but on purely visual inspection you check whether tmp_sal is between 10000 and 20000, which it is because BETWEEN is inclusive.

    So salary = 10000*1.2 = 12000.

    You output the empno (1) and the updated salary variable (12000) , but concatenated together you get 112000.

  • 2. Re: Reference thread for old one  "procedures using IN OUT"
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     

    Good Morning  ...

     

    Already this  question raised here .

    Due to lot of confusion and applying different things ...so , i'm starting new thread here.

    This  is  NOT  a  duplicate  thread. both  purpose is different.    


    MY  old thread  : https://forums.oracle.com/message/11133790#11133790

     

    >> MY  emp table details  >>

     

    SQL> desc emp;

    Name                                      Null?    Type

    ----------------------------------------- -------- ----------------------------

    EID                                                  NUMBER

    ENAME                                            VARCHAR2(15)

    EQUAL                                            VARCHAR2(15)

    ESALARY                                        NUMBER

    ECITY                                              VARCHAR2(15)

    EPERK                                              NUMBER

    ECONTACT_NO                              NUMBER

     

    SQL> select * from emp;

     

          EID    ENAME        EQUAL        ESALARY        ECITY        EPERK      ECONTACT_NO

        ---------  ---------------  ------------  - ---------- ----------  ------------      -----------

          1      sona              mba                10000                  mumbai                              9994488410

          2      maya              mca                15000                calcutta                              9994716872

     

    Whenever you have a question, post a complete test script so that the people who want to help you can re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for any tables needed, and the results you want from that data. In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML. Explain, using specific examples, how you get those results from that data. Always say what version of Oracle you're using (e.g. 11.2.0.2.0). See the forum FAQ: https://forums.oracle.com/message/9362002

    >> Program  for  salary  update  using  procedure and it's parameter  >>


    1  CREATE or REPLACE  procedure emp_details

      2  (id IN emp.eid%type,

      3  salary IN OUT emp.esalary%type) IS

      4  tmp_sal number;

      5  BEGIN

      6  select esalary into tmp_sal from emp where eid=id;

      7  if tmp_sal between  10000 and  20000 THEN

      8  salary:=tmp_sal*1.2;

      9  else if tmp_sal between  20001  and 29999 THEN

    10  salary:=tmp_sal*1.8;

    11  else if tmp_sal > 30000 then

    12  salary :=tmp_sal*2.0;

    13  END IF;

    14  END IF;

    15  END IF;

    16* END;

    SQL> /

     

    Procedure created.

     

    >> what i  expect really   >>

     

    Already emp  table having  Esalary column ;

    If i execute the procedure ,  all  emp records should be updated as per my condition.

    You seem to be contradicting yourself. Do you want all rows updated, or do you only want to update the rows the meet your condition "eid = id"? If you want to update the table, then use an UPDATE statement.

    " Already , I done same process with cursors. but  now my requirement  it should be done with "PROCEDURE  and  sub- program arguments."


    This is  what  i got    Following  output is not  my  GOAL >> 

    ------------------------------------------------------------------------------

      1  DECLARE

      2  l_empno number :=1;

      3  l_sal number :=10000;

      4  begin

      5  emp_details(l_empno , l_sal);

      6  DBMS_OUTPUT.PUT_LINE(l_empno || l_sal);

      7* END;

    SQL> /

    112000  -----  ( bad output )

    What's bad about it? What is the output that you want to see?  Explain how you get that output.


    PL/SQL procedure successfully completed.

     

    My questions are :  if i pass  '1'  record sona  esalary will be updated.  [ only with procedure ]

      - This is  what i expect  ..

     

    Please Note : if i successfully finished ,  i can applu same logic on eperk column

    Thanks in  advance !!

    If you want to actually update the value in the table, you can do it like this:

     

    CREATE or REPLACE  procedure emp_details

    (   id     IN      emp.eid%TYPE

    ) IS

    BEGIN

        UPDATE  emp

        SET     esalary = esalary * CASE

                                        WHEN  esalary <= 20000

                                        THEN  1.2

                                        WHEN  esalary <= 30000

                                        THEN  1.8

                                        ELSE  2.0

                                   END

        WHERE   eid      = id

        AND     esalary  >= 10000;

    END;

    /

     

    If eid is unique, and you want the procedure to pass back the new value of the esalary column, then you can add 2 lines to the procedure above:

     

    CREATE OR REPLACE  procedure emp_details

    (   id     IN      emp.eid%TYPE

    ,   salary OUT     emp.esalary%TYPE       -- 1st line added

    ) IS

    BEGIN

        UPDATE  emp

        SET     esalary = esalary * CASE

                                        WHEN  esalary <= 20000

                                        THEN  1.2

                                        WHEN  esalary <= 30000

                                        THEN  1.8

                                        ELSE  2.0

                                   END

        WHERE   eid      = id

        AND     esalary  >= 10000

        RETURNING  esalary INTO  salary;      -- 2nd line added

    END;

    /

     

  • 3. Re: Reference thread for old one  "procedures using IN OUT"
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ frank klush ..

     

    I think  , i have given  everything from my side.

    If it is n't sufficient additional baic information

     

    My db version is   10.2.0.1.0  , .

     

    >> emp  TABLE  Creation >>

     

    SQL>  create table emp 

    (EID number ,  ENAME varchar2(15),  EQUAL varchar2(15),  ESALARY number,   ECITY varchar2(15),

    EPERK number,  ECONTACT_NO number) ;


    Table created.


    >> emp table  record details  >>


    SQL> select * from emp;

     

    EID         ENAME       EQUAL     ESALARY    ECITY      EPERK          ECONTACT_NO

    ----------  --------------    ------------     -------------   -------------   -------------        --------------------

         1          sona              mba             10000         mumbai                             xxxxxxxxxx

         2         maya              mca             15000         calcutta                             xxxxxxxxxx  

     

    SQL> select count(*) from emp;

      COUNT(*)

       ----------

            2

     

    Okie ,   my requirements  ...

     

    If i execute above procedure (emp_details) , it  should  update  sona  record  (esalary column) ,  when passing argument as 1

    Program won't  ask  to provide input for salary column. Salary will be taken from "emp "  table

    salary should be  10000*1.2 = 12000.  and  NOT  112000

     

    If i execute above procedure (emp_details) , it  should  update  maya  record  (esalary column) ,  when passing argument as  2


    Please note :  Everything should be  done only with procedures  and   IN ,  IN OUT   Parameters.

    If  i clear  my  requirement , then i will apply same logic on eperk.


    >> My expected  output  , when executing  epm_details procedure  >>


    If  i hit 1 ,  output should be


    SQL> select * from emp;


    EID         ENAME       EQUAL     ESALARY    ECITY      EPERK          ECONTACT_NO

    ----------  --------------    ------------     -------------   -------------   -------------        --------------------

         1          sona              mba             11200        mumbai                             xxxxxxxxxx

         2         maya              mca             15000        calcutta                             xxxxxxxxxx  


    Hope , this time i am saying clear explanation.






  • 4. Re: Reference thread for old one  "procedures using IN OUT"
    9423755 Explorer
    Currently Being Moderated

    Do you expect your *table* to be updated? How can your table be updated, when you haven't issued an UPDATE statement? You've modified a parameter to your procedure - "salary".

     

    CREATE or REPLACE  procedure emp_details   (id IN emp.eid%type,  salary IN OUT emp.esalary%type) IS

  • 5. Re: Reference thread for old one  "procedures using IN OUT"
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ Frank klush ;

     

    It's working well.  Thanks a lot !!

     

    can i apply same logic to update  eperk column  ?

     

    >> Please see here , one more  doubt >>   

     

    SQL> select * From  emp;

     

    EID      ENAME    EQUAL      ESALARY    ECITY     EPERK      ECONTACT_NO

    -------  -------------- -------------------------------  ---------    -----------     ----------------------

      4    sona                msc             10000          mumbai                          ##########


    SQL> declare

      2  i_no number :=8;

      3  begin

      4  emp_details(i_no);

      5  end;

      6  /

    PL/SQL procedure successfully completed.

     

    SQL> select * From emp;

     

    EID      ENAME    EQUAL      ESALARY    ECITY     EPERK      ECONTACT_NO

    -------  -------------- -------------------------------  ---------    -----------     ----------------------

      4    sona                msc             10000          mumbai                      ##########

     

    Please note here , when passing argument ,   i hit  '8'   

    but there is no record no  '8'. so record is not updated.

    but  manually i have to check "whether it 's updated or not" to avoid that  issue , 

     

    " can i get any  error  msg like " record not found"  if i pass wrong value ,   (without trigger)

                 Thanks.

  • 6. Re: Reference thread for old one  "procedures using IN OUT"
    ibney Explorer
    Currently Being Moderated

    Update statement never got no record found exception if where condition not match.Only select statement will get no record found exception if record is not there in the table.

  • 7. Re: Reference thread for old one  "procedures using IN OUT"
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Depending on our exact requirements:

     

    CREATE OR REPLACE  procedure emp_details

    (   id       IN      emp.eid%TYPE

    ,   salary   OUT     emp.esalary%TYPE

    ,   perk     OUT     emp.eperk%TYPE

    ,   num_done OUT     PLS_INTEGER -- will be 0 (no UPDATE done) or 1

    ) IS

        TYPE new_values_rec  IS RECORD

        (   esalary  emp.esalary%TYPE

        ,   eperk    emp.eperk%TYPE

        );

      

        new_values  new_values_rec;

    BEGIN

        UPDATE  emp

        SET     esalary = esalary * CASE

                                        WHEN  esalary > 30000

                                        THEN  2.0

                                        WHEN  esalary > 20000

                                        THEN  1.8

                                        WHEN  esalary >= 10000

                                        THEN  1.2

                                        ELSE  1.0

                                   END

        ,      eperk   = eperk *   CASE

                                       WHEN  eperk > 30000

                                       THEN  2.0

                                       WHEN  eperk > 20000

                                       THEN  1.8

                                       WHEN  eperk >= 10000

                                       THEN  1.2

                                       ELSE  1.0

                                   END

        WHERE   eid      = id

        AND     (   esalary  >= 10000

                OR  eperk    >= 10000

                )

        RETURNING  esalary, eperk

        INTO       new_values;

      

        salary   := new_values.esalary;

        perk     := new_values.eperk;

        num_done := SQL%ROWCOUNT;

    END;

    /

     

    If the RETURNING clause is to return 2 or more values, you have to put them into a record.

    The CASE expressions are a little more complicated now, because we can't assume anything about esalary or eperk in them.

  • 8. Re: Reference thread for old one  "procedures using IN OUT"
    GTS (DBA) Journeyer
    Currently Being Moderated


    @  FRUNK KLUSH  - You are tire-less !  ..


    It's so good to hear back from you and thanks again for your contribution ;


     


  • 9. Re: Reference thread for old one  "procedures using IN OUT"
    GTS (DBA) Journeyer
    Currently Being Moderated

    SQL> select * From emp;

     

      EID     ENAME     EQUAL     ESALARY      ECITY   EPERK

    ---------   ------------   ---------------  ----------------    ---------     -------------

       2        rose              mca                10000         calacutta       10000

       1        sona             msc                10000          pune              10000


    Elapsed: 00:00:00.02


    >>  Updating Eperk  column >>

     

    SQL> CREATE or REPLACE  procedure emp_det

           (id  IN  emp.eid%TYPE) IS

        BEGIN

          UPDATE  emp

          SET  eperk = esalary * CASE

                        WHEN  esalary <= 20000

                        THEN  1.2

                        WHEN  esalary <= 30000

                        THEN  1.8

                        ELSE  2.0

                        END

        WHERE   eid   = id

        AND esalary  >= 10000;

    END;

    /

    Procedure   created.

     

    >> Passing  IN  Param   value  >>  

     

      1  declare

      2  i_no number :=2;       ----------------- passing in param value

      3  begin

      4  select eid into i_no from emp where eid=2;    -----------------------------  again assigned same value

      5  emp_det(i_no);

      6  DBMS_OUTPUT.PUT_LINE('input no is :' || i_no);

      7  exception

      8  when  no_data_found then

      9  dbms_output.put_line('no Such record');

    10* end;

          /

    input no is :2

    PL/SQL procedure successfully completed.


    SQL> commit;

     

    SQL> select * From emp;

     

      EID     ENAME     EQUAL     ESALARY      ECITY   EPERK

    ---------   ------------   ---------------  ----------------    ---------     -------------

       2        rose              mca                10000         calacutta       12000

       1        sona             msc                10000          pune              10000



    Ok fine. Query works well

    but i am lagging to write   query efficiently.

    Here , 2 times i have to enter input value.  line no : 2 and line  no :4 ; (red marked )


    MY Ques:   Can we  force  oracle  to  get  input  value  from  input variable  ( i_no )  to  select statement  automatically ?

    Thanks in advance. !

     

     




  • 10. Re: Reference thread for old one  "procedures using IN OUT"
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

     

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     

    SQL> select * From emp;

     

      EID     ENAME     EQUAL     ESALARY      ECITY   EPERK

    ---------   ------------   ---------------  ----------------    ---------     -------------

       2        rose              mca                10000         calacutta       10000

       1        sona             msc                10000          pune              10000


    Elapsed: 00:00:00.02


    >>  Updating Eperk  column >>

     

    SQL> CREATE or REPLACE  procedure emp_det

           (id  IN  emp.eid%TYPE) IS

        BEGIN

          UPDATE  emp

          SET  eperk = esalary * CASE

                        WHEN  esalary <= 20000

                        THEN  1.2

                        WHEN  esalary <= 30000

                        THEN  1.8

                        ELSE  2.0

                        END

        WHERE   eid   = id

        AND esalary  >= 10000;

    END;

    /

    Procedure   created.

     

    >> Passing  IN  Param   value  >>  

     

      1  declare

      2  i_no number :=2;       ----------------- passing in param value

      3  begin

      4  select eid into i_no from emp where eid=2;    -----------------------------  again assigned same value

      5  emp_det(i_no);

      6  DBMS_OUTPUT.PUT_LINE('input no is :' || i_no);

      7  exception

      8  when  no_data_found then

      9  dbms_output.put_line('no Such record');

    10* end;

          /

    input no is :2

    PL/SQL procedure successfully completed.


    SQL> commit;

     

    SQL> select * From emp;

     

      EID     ENAME     EQUAL     ESALARY      ECITY   EPERK

    ---------   ------------   ---------------  ----------------    ---------     -------------

       2        rose              mca                10000         calacutta       12000

       1        sona             msc                10000          pune              10000



    Ok fine. Query works well

    but i am lagging to write   query efficiently.

    Here , 2 times i have to enter input value.  line no : 2 and line  no :4 ; (red marked )


    MY Ques:   Can we  force  oracle  to  get  input  value  from  input variable  ( i_no )  to  select statement  automatically ?

    Thanks in advance. !

     

     




    Sure, you can use i_no instead of the literal 2 on line 4, just like you used it on lines 5 and 6:

     

    select eid into i_no from emp where eid = i_no;

     

    However, if you want to be efficient, add another OUT argument to emp_details, and let the procedure tell you if the eid was found or not.

    Using the 4-argument procedure I posted in reply #7, above, you could do this:

     

    DECLARE
        i_no emp.eid%TYPE := 2;       ----------------- passing in param value
        s  emp.esalary%TYPE;
        p  emp.eperk%TYPE;
        n  PLS_INTEGER;
    BEGIN
        emp_details (i_no, s, p, n);

     

        IF  n > 0
        THEN
            dbms_output.put_line (  i_no || ' was updated');
        ELSE
            dbms_output.put_line (  i_no || ' not in emp table');
        END IF;
    END;

    ;

    /

     

    If you don't need the salary and perk arguments, then change the procedure so it only requires id and num_done.

  • 11. Re: Reference thread for old one  "procedures using IN OUT"
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ GRANK KLUSH

     

    select eid into i_no from emp where eid = i_no;

     

       Always i am doing very silly. Thanks  for  information.


       I will work and and get back to you on that specific program.


         Thanks  Frank klush  ... Hmm ... Thanks GURU !

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points