Re: Help SQLPLUS
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