Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    17

    Unanswered: Stored Procedure Insert or Update

    Hi

    Can anybody assist me with a store procedure code, I want to insert data in a database in this fashion,I want to check first if the record exist, if not Insert or else Update.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by stahorse1 View Post
    I want to check first if the record exist, if not Insert or else Update.
    No need to use a stored procedure, use the MERGE Statement instead.

  3. #3
    Join Date
    Dec 2011
    Posts
    25

    Insert or update

    Use MERGE Statement.
    E.g
    CREATE TABLE CITY (CITY_ID NUMBER, CITY_NAME VARCHAR2(50 BYTE) ) ;

    E.g

    CREATE OR REPLACE
    PROCEDURE ADD_OR_UPDATE_CITY
    (
    IN_CITY_ID IN NUMBER,
    IN_CITY_NAME IN VARCHAR2
    )
    AS

    BEGIN

    BEGIN

    MERGE INTO City ct
    USING ( SELECT IN_CITY_ID AS CITY_ID,
    IN_CITY_NAME AS CITY_NAME
    FROM DUAL
    )SRC
    ON
    (
    ct.CITY_ID = SRC.CITY_ID
    )
    WHEN MATCHED THEN
    UPDATE SET
    ct.CITY_NAME = SRC.CITY_NAME
    WHEN NOT MATCHED THEN
    INSERT
    (
    ct.CITY_ID , ct.CITY_NAME
    )
    VALUES
    (
    SRC.CITY_ID ,SRC.CITY_NAME
    );

    END;

    --Exeption handling
    EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
    END ADD_OR_UPDATE_CITY;

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please use [code] tags the next time you post SQL scripts!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •