11 Replies Latest reply: Aug 5, 2013 4:39 PM by GTS (DBA) RSS

    Reference thread for old one  "procedures using IN OUT"

    GTS (DBA)

      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

          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

            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)

              @ 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

                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)

                  @ 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

                    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

                      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)


                        @  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)

                          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

                            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)

                              @ 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 !