Re: If EXISTS before insert clause

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sat, 05 Feb 2005 05:09:24 GMT
Message-ID: <8yYMd.32122$ZD1.24865@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 Received on Fri Feb 04 2005 - 23:09:24 CST