Re: if else logic in SQLPlus scripts
Date: Fri, 20 Sep 2002 17:26:47 -0700
Message-ID: <3D8BBCC6.1A7041F3_at_oracle.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
I've used a workaround technique with SPOOL and SQL-from-SQL. Your 'if' condition is represented in WHERE clauses, so the command file content is dependent on the condition, then you execute the spooled command file.
e.g. if no row with 'newdata' exists, then insert it, otherwise update it.
set <formatting stuff> off
spool a.sql
select 'insert into mytable (mysequence.nextval, ''data'', sysdate);' from dual
where not exists (select 'x' from mytable where mycol = 'data') union
select 'update mytable set last_modified = sysdate where mycol = ''data'';' from dual
where exists (select 'x' from mytable where mycol = 'data') /
spool off
_at_a
hth
Martin Doherty
Kevin Gillins wrote:
> Technically speaking, the example shown is a PL/SQL block within SQLPLUS.
> :-))
>
> SQLPLUS does not have any if/else support. As Daniel has shown, a PL/SQL
> block or if you prefer, database procedures will do this for you.
> None-the-less, you can accomplish if/else using the same sqlplus interface.
>
> Kevin
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D8B8AF1.EC93ACF6_at_exesolutions.com...
> > Steve Mitchell wrote:
> >
> > > I know I saw a treatment of this someplace on the web, but I cannot find
> it
> > > now that I look for it.
> > >
> > > I would like to do some simple if/then/else logic from within my SQL
> plus
> > > scripts.
> > >
> > > Does anybody have any examples of this?
> > >
> > > Thanks.
> > >
> > > --steve
> >
> > SET SERVEROUTPUT ON
> >
> > DECLARE
> >
> > i PLS_INTEGER;
> >
> > BEGIN
> > SELECT COUNT(*)
> > INTO i
> > FROM all_tables;
> >
> > IF i > 100 THEN
> > DBMS_OUTPUT.PUT_LINE('There Are More Than 100 Tables');
> > ELSE
> > DBMS_OUTPUT.PUT_LINE('There Are Less Than 100 Tables');
> > END IF;
> > END;
> > /
> >
> > Daniel Morgan
> >
--------------39AAFD39B93A75F3E8512843
Content-Type: text/x-vcard; charset=UTF-8;
name="martin.doherty.vcf"
Content-Transfer-Encoding: 7bit Content-Description: Card for Martin Doherty Content-Disposition: attachment;
filename="martin.doherty.vcf"
begin:vcard
n:Doherty;Martin
tel;work:+1 650 506-8398
x-mozilla-html:FALSE
url:http://industries.oraclecorp.com
org:Oracle Corporation;Industry Solutions
version:2.1
email;internet:martin.doherty_at_oracle.com
title:Senior Software Specialist
adr;quoted-printable:;;Mail Stop C3038=0D=0A10 Twin Dolphin Dr=0D=0A;Redwood Shores;CA;94065;USA
fn:Martin Doherty
end:vcard
--------------39AAFD39B93A75F3E8512843-- Received on Sat Sep 21 2002 - 02:26:47 CEST