如何在ORACLE SQL PROCEDURE中操作“if condition”？

Question

I am using ORACLE 10g & PHP codeigniter for my application. I have a procedure defined like this:-

CREATE OR REPLACE PROCEDURE 
        insertMovie (id IN NUMBER, title IN VARCHAR2, 
            sdate IN DATE, edate IN DATE,
                        image IN VARCHAR2, add_date IN DATE,
            message OUT NUMBER) 
    AS
        BEGIN
            INSERT INTO tbl_movie (movie_id, movie_title, movie_sdate, movie_edate, movie_image, movie_add_date) 
            VALUES ( id, 'title', to_date('sdate', 'yyyy-mm-dd hh24:mi:ss'), to_date('edate', 'yyyy-mm-dd hh24:mi:ss'), 'movie_image', to_date('add_date', 'yyyy-mm-dd hh24:mi:ss'));
            message:= 1;
        END;
/

Now, I want a if condition to return either 1 or 0. If insert will be successful, message will be 1. If insert fails. message will be 0.

How can I do that?

Answer 1

You can use the EXCEPTION block to trap any exceptions occured while executing the proc and set message to 0.

CREATE OR REPLACE PROCEDURE 
        insertMovie (id IN OUT NUMBER, title IN VARCHAR2, 
            sdate IN DATE, edate IN DATE,
                        image IN VARCHAR2, add_date IN DATE,
            message OUT NUMBER) 
    AS
        BEGIN
            IF ID is NULL THEN
               ID := SOME_SEQUENCE.NEXTVAL;
            END IF;
            INSERT INTO tbl_movie (movie_id, movie_title, movie_sdate, movie_edate, movie_image, movie_add_date) 
            VALUES ( id, 'title', to_date('sdate', 'yyyy-mm-dd hh24:mi:ss'), to_date('edate', 'yyyy-mm-dd hh24:mi:ss'), 'movie_image', to_date('add_date', 'yyyy-mm-dd hh24:mi:ss'));
            message := 1;
        EXCEPTION
          WHEN OTHERS THEN
            message := 0;
        END;
/