Re: PL-SQL - Table pointers?
Date: 1997/04/06
Message-ID: <33480138.1862868_at_newshost>#1/1
dbms_sql MIGHT be the answer (on delete cascade might be the other answer. did you know that using declaritive RI, you can have a foriegn key that specifies "on delete cascade" so that when the parent is deleted, the child goes too).
Here's a shell of a dbms_sql routine that will work:
create or replace
function execute_immediate( stmt in varchar2 )
return number
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
return rows_processed;
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;
/
dbms_sql supports bind variables (not shown here) as well.
You could now write DeleteX as:
create or replace procedure DeleteX
( tname in varchar2, a in varchar2, b in varchar2 )
as
status number;
begin
status := execute_immediate( 'delete from ' || tname ||
' where KeyA = ''' || a || ''' and ''' ||
' KeyB = ''' || b || '''' );
end;
It would be more efficient with bind variables...
On Sat, 05 Apr 1997 15:50:46 +0100, Bryan Dollery <Bryan_at_Bryan.prestel.co.uk> wrote:
>Hi,
>
>I have a number of tables all related by the same two field composite
>primary key. My requirement is that I delete a record from each table
>which matches a given primary key. I have done this thusly:
>
> DELETE
> FROM tableA
> WHERE keyA = A AND
> keyB = B ;
>
> DELETE
> FROM tableB
> WHERE keyA = A AND
> keyB = B ;
>
> DELETE
> FROM tableC
> WHERE keyA = A AND
> keyB = B ;
>
>This is of course an abstracted example. There are actually twenty
>tables and the key is six fields long.
>
>It would be neated if I could write something like this (I know that
>TABLE has a specific purpose, I am only using it for an example of what
>I would like to do):
>
> PROCEDURE DeleteX(table_name TABLE, A keytable.A%TYPE, B
>keytable.B%TYPE) IS
> BEGIN
> DELETE
> FROM table_name
> WHERE keyA = A AND
> keyB = B ;
> END DeleteX ;
>
>which I could then call like this:
>
> DeleteX(tableA, A, B) ;
> DeleteX(tableB, A, B) ;
> DeleteX(tableC, A, B) ;
>
>which is much better as it gives me the ability to place other code
>within the delete function, rather than write a lot of triggers to
>achieve the same functionallity, and slow down the system.
>
>Does anyone have any idea how to achieve this passing of table pointers
>in PL-SQL, or even if it is possible.
>
>All answers greatfully received.
>
>
>Bryan.
>--
>Bryan Dollery BSc(Hons)
>ByteSmart Systems Ltd.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Apr 06 1997 - 00:00:00 CEST