ORACLE存储过程例子

create or replace procedure createtmptables IS

i INTEGER;

tablename VARCHAR(100);

sqlstr1 VARCHAR(100);  
sqlstr2 VARCHAR(100); 

begin

i := 1;
tablename := '';
sqlstr1 := '';   
sqlstr2 := ''; 

WHILE i < 16 
    
    LOOP
    
BEGIN
    
tablename := concat('tmp',to_char(i));

sqlstr1 := 'CREATE TABLE ' || tablename || ' AS SELECT * FROM  tmp';

EXECUTE IMMEDIATE sqlstr1;

sqlstr2 := 'alter table '  || tablename || ' add constraint pk_' || tablename || ' primary key (tmp_k)';

EXECUTE IMMEDIATE sqlstr2;

i := i+1;

END;

END LOOP;  

EXCEPTION

    WHEN OTHERS THEN
    ROLLBACK;

end createtmptables;