Re: If EXISTS before insert clause

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sat, 5 Feb 2005 07:21:31 -0500
Message-ID: <daOdnTcgHYzQJ5nfRVn-ig@comcast.com>

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:8yYMd.32122$ZD1.24865_at_twister.nyroc.rr.com...
>
> "JPike" <jpike_at_welcom.com> wrote in message
> news:1107557736.605423.126420_at_o13g2000cwo.googlegroups.com...
>> OK, say I had the following in a .sql file
>>
>> IF EXISTS(SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)
>> BEGIN
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('CFLD','CAGR_UID','CAGR','CAGR_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('CFLD','LKUP_UID','LKUP','LKUP_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('CIMP','IMPT_UID','IMPT','IMPT_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('ITTH','TOLR_UID','TOLR','TOLR_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('RISK','CATG_UID','CATG','CATG_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('SCOR','IMPT_UID','IMPT','IMPT_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('SCOR','PROB_UID','PROB','PROB_UID') ;
>> INSERT INTO WPS_DELB (TABLE_TYPE,FLD_NAME,FKEY_TABLE,FKEY_FLD_NAME)
>> VALUES ('SCOR','SEV_UID','SEV','SEV_UID') ;
>> END
>>
>> How would I covert that to PL/SQL and run it? It sounds to me like you
>> are just saying change the top part to:
>>
>> Declare a NUMBER;
>> SELECT COUNT(PRD_UID) into a FROM WST_PRD WHERE PRD_UID = 502;
>> IF (a > 0)
>> BEGIN
>> .
>> .
>> .
>> END IF
>>
>> But if I do that and try to run in TOAD it doesn't work.
>>
>> I am new to Oracle so any help would be appreciated. I just don't
>> define Oracle as easy. I have worked two places and everyone I have met
>> hates dealing with it.
>>
>
> Well if you have not worked much in oracle ... some things might appear to
> be hard for you.
> There are multiple ways to do this in oracle ...
>
> I'll show you two ways to do it in oracle 9iRel2 (first should work in
> previous versions too):
>
> declare
> a number;
> begin
> select count(*) into a from mytable1 where m = 1;
> if (a > 0) then
> insert into mytable (x,y) values (1,1);
> insert into mytable (x,y) values (2,2);
> insert into mytable (x,y) values (3,3);
> end if;
> end;
> /
>
> insert all
> into mytable (x,y) values (1,1)
> into mytable (x,y) values (2,2)
> into mytable (x,y) values (3,3)
> select 1 from mytable1 where m = 1 and rownum = 1;
>
> Anurag
>
>

Anurag,

(sorry for the extra email, hit the wrong button for my last reply)

Good suggestion on the multi-table insert (which also has an ELSE clause) -- however, that is specific to the INSERT statements. FOR loop syntax would be needed in the OP's case since he also wants to include table creates:

    for r1 in ( SELECT PRD_UID FROM WST_PRD WHERE PRD_UID = 502)     loop

        INSERT INTO WST_PFD (PFG_UID, OBJ_UID)
        VALUES('14014', 'GRP_VIEW_RSK')
        .
        .
        execute immediate 'create ...';
        .

    end loop;

Jpike,

Look into EXECUTE IMMEDIATE for including DDL in your PL/SQL -- but understand how Oracle handles explicit commits. Keep in mind that including DDL in PL/SQL is not typically done for a runtime operation, but is reasonable for an install or maintenance script. A little advise: when something 'doesn't work', be sure to read the error messages -- your 'doesn't work in TOAD' code was missing a THEN keyword in the IF statement. Just like C# is not VB, PL/SQL is not TSQL, so you'll have to learn new syntax.
Also, realize that people 'hate dealing with' things that they don't understand and don't care (or have time) to learn about -- don't fall into the same trap, it ensures failure.

++ mcs Received on Sat Feb 05 2005 - 06:21:31 CST