1

Is it possible to use dynamic SQL in a FOR statement?

I am using a FOR loop to handle a batch of records from a SELECT statement. However, what if I want the SELECT statement to be framed at run time, such as the table name may be stored in a variable.

for thisRecord as
    select myColumn from MyTable  --can this be dynamic?
do
    .......
end for;

I want something like the following

declare myCursor cursor for stmt;

set dynamicStmt = 'select myColumn from '||varTable;
prepare stmt from dynamicStmt;

for thisRecord as myCursor
do
    ......
end for;
tshepang
  • 12,111
  • 21
  • 91
  • 136
Aobo Yang
  • 157
  • 1
  • 14
  • Could you wrap the entire cusor/FOR statements in the dynamic sql? Wouldn't advice it, but I guess it's a possibility. – Allan S. Hansen Nov 20 '13 at 10:48
  • As part of a stored procedure, sure (...how else are you planning to use the `FOR` statement anyways?). What are you trying to accomplish, though? I get a little worried every time somebody's storing table names for dynamic statements... – Clockwork-Muse Nov 20 '13 at 11:27
  • @AllanS.Hansen I think I can't since FOR is some kinds of logical control statements like IF..ElSE instead of SQL statements. Even it's possible, the codes will be mussed up. I guess that's also why you won't advice it. – Aobo Yang Nov 20 '13 at 11:47
  • @Clockwork-Muse 'sure' means it's possible? but how? could you give an example? I want to pass the table name as a parameter to this procedure since I try to write only one FOR loop for every table required. Of course, the logic of processes inside the loop for each table are same. That's what I want to accomplish. Is it a bad manner to store table names for dynamic SQL? I don't know why it make you worried. – Aobo Yang Nov 20 '13 at 12:02
  • Among other things, it makes you more open to SQL Injection. However, the real concern (especially as you've outlined that the "logic is the same for each table") is that the schema design may potentially be a little off. Wanting to deal with each table the same isn't necessarily the issue - what processing are you actually doing? What about these tables make it so that they _can_ be processed identically? Depending on the situation, all the data should be in one table, with a differentiating column added (or not - it depends). – Clockwork-Muse Nov 20 '13 at 12:13
  • @Clockwork-Muse You are right,differentiating column will work. However, even though the structure of these tables are close, they still have their private columns respectively. Merge them into one will lead to lots of meaningless columns for some records. Spit these columns to another table will require for JOIN later. Better performance is another reason. Store in one table means every SELECT need to specify the differentiating column in WHERE clause and JOIN more unnecessary data. They have close data model, but are different stuff, which means application always want them separately. – Aobo Yang Nov 20 '13 at 13:48
  • @Clockwork-Muse Here is another situation. Every table in my DB has a column to store the 'Last Update Timestamp' for each records. Now I have to modify the time for all tables. – Aobo Yang Nov 20 '13 at 14:01
  • You're correct, you should leave those tables separate - I was somewhat concerned that all the tables had the same data model (there are questions where there's a separate table for each 'parent' entity....). However, now I'm worried for a _different_ reason - why are you attempting to update that timestamp? That kind of column is often an audit value - changing it may cause legal issues, or potentially just program ones. – Clockwork-Muse Nov 20 '13 at 22:30
  • @Clockwork-Muse I see...totally agree. Thanks for your comments. Now I just want it to be a sample to find out the technical possibility for my question. – Aobo Yang Nov 21 '13 at 03:04

2 Answers2

1

You could simply use FETCH in a loop.

WarrenT
  • 4,502
  • 19
  • 27
0

Since no one else answer my question, I guess this means it's impossible to directly use dynamic SQL in FOR statement.

However as said by others, I can use FETCH. Here is another tricky method I find out later, which works in my case.

Use dynamic SQL to create an alias for given table and use this alias in the FOR statement. The only thing you should pay attention is to make sure the alias exists so the procedure can be compiled.

Create or replace procedure MyProcedure(tableName varchar(50））
Begin
  EXECUTE IMMEDIATE 'Drop alias myAlias';
  EXECUTE IMMEDIATE 'Create alias myAlias for '||tableName;

  for thisRecord as
   select * from myAlias
  do
     .........
  end for;
End
Aobo Yang
  • 157
  • 1
  • 14