| caching data in array for select ... where alt1 in (data) [message #7673] |
Mon, 30 June 2003 01:45  |
Andreas Perjus
Messages: 1
Registered: June 2003
|
Junior Member |
|
|
Hi,
i'm having to delete a couple of statements where they all have the same dependancy. I.e
DELETE FROM table1 WHERE column1 IN select_statement_1)
DELETE FROM table2 WHERE column3 IN select_statement_1)
DELETE FROM table3 WHERE column4 IN select_statement_1)
The problem is that the select statement is quite big. I was wondering if I could cache the result of the select_statement_1 in a array or something which I could then use in the delete statement and only have to run the actual fetch once??
I'm running 8.1.7
|
|
|
|
|
|
| Re: caching data in array for select ... where alt1 in (data) [message #7675 is a reply to message #7673] |
Mon, 30 June 2003 02:41  |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Try using FOR ALL statement.
i.e. Sample code
DECLARE
TYPE NumList IS VARRAY(15) OF NUMBER;
depts NumList := NumList();
BEGIN
-- fill in the varray here using the SQL statement
...
FORALL j IN depts.FIRST..depts.LAST
DELETE From Table 1 Where col1 = depts(j);
DELETE From Table 2 Where col3 = depts(j);
DELETE From Table 3 Where col4 = depts(j);
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
