Re: Help SQLPLUS

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 12 Jun 2002 07:57:27 -0700
Message-ID: <45a06b65.0206120657.48287d7c_at_posting.google.com>


franck lejeune <flejeune_at_etu.utc.fr> wrote in message news:<Pine.OSF.4.33.0206111825410.12677-100000_at_vega.utc.fr>...
> Hi,
>
> I have to write only in SQLPLUS a script which insert a row in a table
> only if a test is ok, but I don't know how to do this...

Sure you can, here is a new record inserted only if the input employee does not already exist.

SQL> select ename, sal from emp;

ENAME SAL
---------- ----------

SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

SQL> column do_insert new_value do_insert noprint SQL> accept new_emp char prompt 'New employee: ' New employee: Raul
SQL> select count(*) do_insert from emp where ename = '&new_emp'; old 1: select count(*) do_insert from emp where ename = '&new_emp' new 1: select count(*) do_insert from emp where ename = 'Raul'

SQL> insert into emp (ename) select '&new_emp' from dual where 0 = (select &do_insert from dual);
old 1: insert into emp (ename) select '&new_emp' from dual where 0 = (select &do_insert from dual)
new 1: insert into emp (ename) select 'Raul' from dual where 0 = (select 0 from dual)

1 row created.

SQL> accept new_emp char prompt 'New employee: ' New employee: SMITH
SQL> select count(*) do_insert from emp where ename = '&new_emp'; old 1: select count(*) do_insert from emp where ename = '&new_emp' new 1: select count(*) do_insert from emp where ename = 'SMITH'

SQL> insert into emp (ename) select '&new_emp' from dual where 0 = (select &do_insert from dual);
old 1: insert into emp (ename) select '&new_emp' from dual where 0 = (select &do_insert from dual)
new 1: insert into emp (ename) select 'SMITH' from dual where 0 = (select 1 from dual)

0 rows created.

SQL> select ename, sal from emp;

ENAME SAL
---------- ----------

SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

Raul

15 rows selected.

SQL> Martin Received on Wed Jun 12 2002 - 16:57:27 CEST

Original text of this message