doumenshi1475
2017-02-25 08:12
浏览 46

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

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?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • ds15812330851 2017-02-25 08:24
    已采纳

    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;
    /
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题