14 Replies Latest reply: Nov 14, 2012 12:25 AM by Herald ten Dam RSS

    plsql procedure

    973713
      Dear Everyone,

      I am new to plsql.
      I am having doubt in plsql procudure.

      Table Name--Order
      ---------------------------
      Order_No Varchar2(10) PK
      Order_Date Date PK
      No_Of_Computers Int



      How can i check whether the values are present or not.


      How can i insert values into table using procedure.
      If Order_No,Order_Date exist update No_Of_Computers by one.
      else insert the values into the table.

      Edited by: 970710 on Nov 11, 2012 7:38 PM
        • 1. Re: plsql procedure
          sb92075
          970710 wrote:
          Dear Everyone,

          I am new to plsql.
          I am having doubt in plsql procudure.

          Table Name--Order
          ---------------------------
          Order_No Varchar2(10) PK
          Order_Date Date PK
          No_Of_Computers Int


          How can i insert values into table using procedure.
          If Order_No,Order_Date exist update No_Of_Computers by one.
          else insert the values into the table.
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: plsql procedure
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
            >
            How can i insert values into table using procedure.
            If Order_No,Order_Date exist update No_Of_Computers by one.
            else insert the values into the table.
            >
            You can use MERGE to do that. Here is sample code for doing a merge into the EMP table.
            -- simple merge query
            MERGE INTO EMP e
            USING (SELECT 12 empno,12000 sal FROM DUAL) q
            ON (e.empno = q.empno )
            WHEN MATCHED THEN 
              UPDATE SET E.SAL = Q.SAL
            WHEN NOT MATCHED THEN
              INSERT (EMPNO, SAL) VALUES (Q.EMPNO, Q.SAL)
            There are examples in the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
            • 3. Re: plsql procedure
              Gurujothi
              Hi,

              Always give the details and go through the link How do I ask a question on the forums?
              and give some more details what do you want actually.
              970710 wrote:
              How can i check whether the values are present or not.
              by
              select count(*) from order 
              you can find whether rows exist or not.
              970710 wrote:
              How can i insert values into table using procedure.
              through normal insert statement you can.
              970710 wrote:
              If Order_No,Order_Date exist update No_Of_Computers by one.
              else insert the values into the table.
              If Order_No,Order_Date not exist means nothing is in table(No records)
              then what do you want to insert??

              Give some details.

              Regards,
              Guru
              • 4. Re: plsql procedure
                973713
                Hi guru,

                Sorry For inconvenience

                In the table some records are already exist.
                When you are inserting one record ,it will check whether that record is there or not.
                If there update no_Of_Computers.
                else insert the record.



                Thanks,
                Prasad
                • 5. Re: plsql procedure
                  sb92075
                  970710 wrote:
                  Hi guru,

                  Sorry For inconvenience

                  In the table some records are already exist.
                  When you are inserting one record ,it will check whether that record is there or not.
                  If there update no_Of_Computers.
                  else insert the record.



                  Thanks,
                  Prasad
                  sounds like a good candidate for MERGE statement
                  • 6. Re: plsql procedure
                    973713
                    Hi,

                    Will you suggest me how to use merge statement in a procedure.



                    Thanks,
                    Prasad
                    • 7. Re: plsql procedure
                      sb92075
                      970710 wrote:
                      Hi,

                      Will you suggest me how to use merge statement in a procedure.



                      Thanks,
                      Prasad
                      when all else fails, Read The Fine Manual

                      http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606
                      • 8. plsql procedure
                        973713
                        Hi,

                        I wrote like this


                        create or replace procedure order_Proc
                        (In_Order_No in order_item.Order_No%type,
                        In_Order_Date in order_item.Order_Date%type)
                        is
                        begin

                        MERGE INTO Order_item o
                        USING (SELECT In_Order_No , In_Order_Date FROM dual) d
                        ON
                        (o.Order_No = d.In_Order_No and o.Order_Date = d.In_Order_Date)


                        WHEN MATCHED THEN UPDATE SET
                        o.No_Of_Computers = o.No_Of_Computers + 1;

                        WHEN NOT MATCHED THEN INSERT(
                        o.Order_No,
                        o.Order_Date
                        )VALUES(
                        d.In_Order_No,
                        d.In_Order_Date

                        );

                        end order_Proc;
                        /



                        It is showing problem;

                        Will You please check where is the problem.


                        Thanks,
                        Prasad
                        • 9. Re: plsql procedure
                          Herald ten Dam
                          Hi,

                          we don't have your table definition, but I think it has more columns than you insert now. And show the error of the procedure by using "show errors".

                          Herald ten Dam
                          http://htendam.wordpress.com
                          • 10. Re: plsql procedure
                            973713
                            Hi,

                            SQL> desc order_item;
                            Name Null? Type
                            ----------------------------------------- -------- ----------------------------

                            ORDER_NO Not Null VARCHAR2(10)
                            ORDER_DATE Not Null DATE
                            NO_OF_COMPUTERS NUMBER(38)


                            I have to give Order_No , Order_Date from procedure
                            It will check from the table whether that record is present or not.
                            If not insert that values into that table.
                            else update No_Of_Computers by one.


                            Thanks,
                            Prasad
                            • 11. Re: plsql procedure
                              973713
                              Hi,


                              SQL> @ E:\Oracle_Programs\a.sql;


                              Procedure code
                              ------------------------
                              create or replace procedure order_Proc
                              (In_Order_No in order_item.Order_No%type,
                              In_Order_Date in order_item.Order_Date%type)
                              is
                              begin

                              MERGE INTO Order_item o
                              USING (SELECT In_Order_No , In_Order_Date FROM dual) d
                              ON
                              (o.Order_No = d.In_Order_No and o.Order_Date = d.In_Order_Date)


                              WHEN MATCHED THEN UPDATE SET
                              o.No_Of_Computers = o.No_Of_Computers + 1

                              WHEN NOT MATCHED THEN INSERT(
                              o.Order_No,
                              o.Order_Date
                              )VALUES(
                              d.In_Order_No,
                              d.In_Order_Date

                              );

                              end order_Proc;
                              /



                              Procedure created.

                              SQL> exec order_Proc('O100',sysdate);
                              BEGIN order_Proc('O100',sysdate); END;

                              *
                              ERROR at line 1:
                              ORA-00904: "D"."IN_ORDER_DATE": invalid identifier
                              ORA-06512: at "SCOTT.ORDER_PROC", line 7
                              ORA-06512: at line 1


                              SQL> exec order_Proc('O100','10-feb-89');
                              BEGIN order_Proc('O100','10-feb-89'); END;

                              *
                              ERROR at line 1:
                              ORA-00904: "D"."IN_ORDER_DATE": invalid identifier
                              ORA-06512: at "SCOTT.ORDER_PROC", line 7
                              ORA-06512: at line 1


                              Please resolve my problem.


                              Thanks,
                              Prasad
                              • 12. Re: plsql procedure
                                AlbertoFaenza
                                Hi Prasad,

                                When you put some code please enclose it between two lines starting with {noformat}
                                {noformat}, as specified in <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002">How do I ask a question on the forums?</a>
                                i.e.:
                                {noformat}
                                {noformat}
                                SELECT ...
                                {noformat}
                                {noformat}
                                
                                Here the correct code:
                                CREATE TABLE order_item
                                (
                                order_no VARCHAR2(10) NOT NULL
                                , order_date DATE NOT NULL
                                , no_of_computers NUMBER(38)
                                );

                                CREATE OR REPLACE PROCEDURE order_proc (in_order_no IN order_item.order_no%TYPE
                                , in_order_date IN order_item.order_date%TYPE)
                                IS
                                BEGIN
                                MERGE INTO order_item o
                                USING (SELECT in_order_no order_no, in_order_date order_date
                                FROM DUAL) d
                                ON (d.order_no = o.order_no AND d.order_date = o.order_date)
                                WHEN MATCHED
                                THEN
                                UPDATE SET o.no_of_computers = o.no_of_computers + 1
                                WHEN NOT MATCHED
                                THEN
                                INSERT ( order_no, order_date, no_of_computers)
                                VALUES (d.order_no, d.order_date, 1);
                                END order_proc;
                                /

                                exec order_Proc('O100',TRUNC(SYSDATE));

                                SELECT * FROM order_item;

                                ORDER_NO ORDER_DATE NO_OF_COMPUTERS
                                ---------- ---------- ---------------
                                O100 13-NOV-12 1

                                exec order_Proc('O100',TRUNC(SYSDATE));

                                SELECT * FROM order_item;

                                ORDER_NO ORDER_DATE NO_OF_COMPUTERS
                                ---------- ---------- ---------------
                                O100 13-NOV-12 2
                                Regards.
                                Al
                                
                                Edited by: Alberto Faenza on Nov 13, 2012 3:03 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                • 13. Re: plsql procedure
                                  973713
                                  Hi,

                                  Thanks its working properly.

                                  but when i am giving


                                  {
                                  PL/SQL procedure successfully completed.

                                  SQL> select * from order_item;

                                  ORDER_NO ORDER_DAT NO_OF_COMPUTERS
                                  ---------- --------- ---------------
                                  O100 14-NOV-12 2

                                  SQL> exec order_Proc('O100',SYSDATE);

                                  PL/SQL procedure successfully completed.

                                  SQL> select * from order_item;

                                  ORDER_NO ORDER_DAT NO_OF_COMPUTERS
                                  ---------- --------- ---------------
                                  O100 14-NOV-12 2
                                  O100 14-NOV-12 1

                                  SQL> exec order_Proc('O100',SYSDATE);

                                  PL/SQL procedure successfully completed.

                                  SQL> select * from order_item;

                                  ORDER_NO ORDER_DAT NO_OF_COMPUTERS
                                  ---------- --------- ---------------
                                  O100 14-NOV-12 2
                                  O100 14-NOV-12 1
                                  O100 14-NOV-12 1

                                  }



                                  Its coming error in the output.



                                  Thanks,
                                  Prasad
                                  • 14. Re: plsql procedure
                                    Herald ten Dam
                                    Hi,

                                    you are putting in SYSDATE, it has a time stamp. So if you check for date, then it will always be different. See the example where TRUNC(SYSDATE) is used. If you use SYSDATE, every time a record gets inserted, it will not be an update/merge.

                                    Herald ten Dam
                                    http://htendam.wordpress.com