How can I execute only selective statements from a SQL file?

I have a .sql file that has a sequence of create table statements, like this:

crtab.sql

define ll='&1';
define ul='&2';

create table TAB1 (...);
create table TAB2 (...);
create table TAB3 (...);
...
create table TAB1000 (...);
开发者_StackOverflow社区

The user passes as arguments two parameters - the lower limit ll and the upper limit ul, which signify the range of table numbers to be created.

For example,

sqlplus crtab.sql 3 67

should create only tables TAB3 to TAB67.

How can I implement this logic in my .sql file?

1. Use associative array, and insert all the queries in that array in the following manner:

myArray(1) := 'CREATE TAB 1 ...';

...

myArray(1000) := 'CREATE TAB 1000 ...';

2. Loop through ll to ul and execute those queries, i.e.

for i in ll..ul
  loop
       @sql = myArray(i);
       exec @sql; 
  end loop;

I am sorry for any of the syntax errors.

Maybe like these

CREATE OR REPLACE PROCEDURE TABS_V1 (ll number, ul number) IS
BEGIN
    IF 1 BETWEEN ll AND ul THEN
          EXECUTE IMMEDIATE 'create table TAB1 (...)';
    END IF;
    IF 2 BETWEEN ll AND ul THEN
          EXECUTE IMMEDIATE 'create table TAB2 (...)';
    END IF;
    IF 3 BETWEEN ll AND ul THEN
          EXECUTE IMMEDIATE 'create table TAB3 (...)';
    END IF;

    ...

    IF 1000 BETWEEN ll AND ul THEN
          EXECUTE IMMEDIATE 'create table TAB1000 (...)';
    END IF;
END;



CREATE OR REPLACE PROCEDURE TABS_V2 (ll number, ul number) IS
    TYPE tabs IS TABLE OF VARCHAR2(4000) INDEX BY NUMBER;
    tabs all_tabs;
BEGIN
    all_tabs(1) = 'create table TAB1 (...)';
    all_tabs(1) = 'create table TAB2 (...)';
    all_tabs(1) = 'create table TAB3 (...)';
    ...
    all_tabs(1000) = 'create table TAB1000 (...)';

    FOR cnt IN ll .. ul LOOP
        EXECUTE IMMEDIATE all_tabs(cnt);
    END LOOP;
END;

Assuming the table structures are the same, you'd be better off creating & executing dynamic sql statements in a loop, based on your ll and ul. e.g. (in sql server syntax)

declare @sql varchar(1000)
declare @i int set @i = @ll
while @i <= @ul begin
    set @sql = 'create table TAB' + cast( @i as varchar ) + '(...);'
    exec @sql
    set @i = @i + 1 
end

If the table structures are different then just put an if statement around each create.

There are several ways to do this. First, you can use an anonymous block with either a nested table of the statements or an associative array of the statements. The nested table approach requires slightly less change to your existing script but there is a risk that the numbers will get out of sequence. Note that I'm using the alternative quoting mechanism, e.g. q'[character]... [character]', just in case your DDL contains some apostrophes.

This code is similar to some of the other answers, but with Oracle syntax and doesn't require creating additional objects.

Nested table script:

--Only create the tables between the two values (nested table)
declare
  type varchar_tab is table of varchar2(32767);
  table_statements varchar_tab := varchar_tab(
    q'!create table tab1 (test1 number)!',
    q'!create table tab2 (test1 number)!',
    q'!create table tab3 (test1 number)!',
    q'!create table tab4 (test1 number)!'
  );
begin
  for i in &1 .. &2 loop
    execute immediate table_statements(i);
  end loop;
end;
/

Associative array script:

--Only create the tables between the two values (associative array)
declare
  type varchar_tab is table of varchar2(32767) index by number;
  table_statements varchar_tab;
begin
  table_statements(1) := q'!create table tab1 (test1 number)!';
  table_statements(2) := q'!create table tab2 (test1 number)!';
  table_statements(3) := q'!create table tab3 (test1 number)!';
  table_statements(4) := q'!create table tab4 (test1 number)!';

  --Only create the tables between the two values
  for i in &1 .. &2 loop
    execute immediate table_statements(i);
  end loop;
end;
/

If you're looking for a way to keep your script almost identical to its current form, another approach would be to run the entire script and then have an anonymous block at the end that drops the unwanted tables. This keeps the top of your script really simple, but obviously there may be some concerns with automatically dropping tables.

--Drop all the tables except for those within the range
declare
  table_does_not_exist exception;
  pragma exception_init(table_does_not_exist, -00942);
begin
  for i in 1 .. 1000 loop
    if i between &1 and &2 then
      null;
    else
      begin
        execute immediate 'drop table tab'||i;
      exception when table_does_not_exist then null;
      end;
    end if;
  end loop;
end;
/

One hacked way to do it would be to use the C preprocessor and #if directives to include or not each statement baed on some macros you define on the command line. Then run the preprocessed file rather than the original.