Re: select/insert/delete

From: David Litchfield <david_at_databasesecurity.com>
Date: Wed, 08 Jun 2011 00:47:05 +0100
Message-ID: <4DEEB879.5040406_at_databasesecurity.com>

On 08/06/2011 00:12, Michael Moore wrote:
> I think the answer to this is NO, but just to be sure,
>
> Is there a single SQL statement that can:
>
> 1) SELECT row-X from table A
> 2) INSERT row-X into table B
> 3) DELETE row-X from table A
>
> It would be nice to be able to do this without a context switch.
Yes you can... but it requires a bit of trickery (a function that'll execute arbitrary sql)... Follow the example below...

SQL> create or replace function arbsql (x varchar2) return number is

   2 begin
   3 execute immediate x;
   4 return 1;
   5 end;
   6 /

Function created.

SQL> create table table_a (x number, y varchar2(30));

Table created.

SQL> insert into table_a (x,y) values (1,'test1');

1 row created.

SQL> insert into table_a (x,y) values (2,'test2');

1 row created.

SQL> insert into table_a (x,y) values (3,'test3');

1 row created.

SQL> commit;

Commit complete.

SQL> create table table_b (x number, y varchar2(30));

Table created.

SQL> insert into table_b (x,y) values (0,'');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_a;

          X Y

---------- ------------------------------
          1 test1
          2 test2
          3 test3

SQL> select * from table_b;

          X Y

---------- ------------------------------
          0

SQL> delete from table_a where x = 2 + arbsql('insert into table_b (x,y) values ((s
elect x from table_a where x = 2), (select y from table_a where x = 2))') - 1;

1 row deleted.

SQL> select * from table_a;

          X Y

---------- ------------------------------
          1 test1
          3 test3

SQL> select * from table_b;

          X Y

---------- ------------------------------
          0
          2 test2
          2 test2

HTH,
David

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 07 2011 - 18:47:05 CDT