This discussion is archived
14 Replies Latest reply: Nov 13, 2012 10:25 PM by Herald ten Dam RSS

plsql procedure

973713 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

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



    Thanks,
    Prasad
  • 7. Re: plsql procedure
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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