在oracle存储过程中执行动态sql

create or replace procedure pr_zhaozhenlong_strsql
/*
名称：在存储过程中执行动态sql
功能：动态创建、删除表；
      动态修改表结构；
      动态修改表主键；
      判断某表、某列是否存在
调用：
      begin
        -- Call the procedure
        pr_zhaozhenlong_strsql;
      end;
创建人：赵振龙
创建时间：2007-01-03
*/
is
     v_rows      integer;
     v_sqlstr    varchar2(1000);
     v_tablename varchar2(50);
     v_pkname    varchar2(50);
begin
     --1、判断是否存在某表，删除、创建表
     --select * from tabs where table_name = 'TB_ZHAOZHENLONG'
     select count(1) into v_rows from tabs where table_name = 'TB_ZHAOZHENLONG';
     if v_rows >0 then
        execute immediate 'drop table tb_zhaozhenlong';
     end if;
     v_sqlstr := 'create table tb_zhaozhenlong("id" integer,rpt_date date,dept_id varchar2

(20),item varchar2(20), qty float)';
     execute immediate(v_sqlstr);

     select count(1) into v_rows from tabs where table_name = 'TB_TEMP_ZHAOZHENLONG';
     if v_rows >0 then
        execute immediate 'drop table tb_temp_zhaozhenlong';
     end if;
     
     --创建，基于会话的临时表
     v_sqlstr := 'create global temporary table tb_temp_zhaozhenlong(rpt_date date,dept_id 

varchar2(20),item varchar2(20), qty float, memo varchar(200))'
                 ||' on commit preserve rows';
     execute immediate(v_sqlstr);

     --2、判断是否存在某表，删除、创建表
     --select * from cols where table_name  = 'TB_ZHAOZHENLONG' and column_name = 'MEMO';
     select count(1) into v_rows from cols where table_name = 'TB_ZHAOZHENLONG' and 

column_name = 'MEMO';
     if v_rows <=0 then
        v_tablename :='tb_zhaozhenlong';
        v_sqlstr    := 'alter table ' ||v_tablename ||' add "demo" varchar2(100)';
        execute immediate(v_sqlstr);
     else
        execute immediate 'alter table tb_zhaozhenlong modify demo varchar2(200)';
     end if;

     --3、修改主键
     v_tablename :='TB_ZHAOZHENLONG';
     --第一步：增加列key_no
     v_sqlstr :='alter table '||v_tablename||' add key_no int';
     execute immediate(v_sqlstr);       
     --第二部：给key_no更新值
     v_sqlstr :='update '||v_tablename||' set key_no =rownum';
     execute immediate(v_sqlstr); 
     commit;       
     --第三步：将key_no置为非空
     v_sqlstr :='alter table '||v_tablename||'  modify key_no  int  not null';
     execute immediate(v_sqlstr);           
     --第四步：查找主键
     v_sqlstr :='select   count(1)'
                ||' from   user_constraints'   
                ||' where constraint_type=''P'' and  owner=user   and   

table_name='''||v_tablename ||'''' ;
     execute immediate(v_sqlstr) into v_rows;
     if v_rows >=1  then 
           v_sqlstr :='select   constraint_name'
                      ||' from   user_constraints'   
                      ||' where constraint_type=''P'' and  owner=user   and   

table_name='''||v_tablename ||'''' ;
           execute immediate(v_sqlstr) into v_pkname;
     end if;
     --第五步：删除主键
     if v_pkname  is  not null then 
         v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' DROP CONSTRAINT '|| v_pkname ||' 

CASCADE';
         execute immediate(v_sqlstr);
     else
         v_pkname := 'pk_' ||v_tablename;
     end if;
     --第六步：增加主键
     v_sqlstr := 'ALTER TABLE ' ||v_tablename ||' ADD (CONSTRAINT '|| v_pkname ||' PRIMARY 

KEY(rpt_date,dept_id,item,key_no))';
     execute immediate(v_sqlstr);

end pr_zhaozhenlong_strsql;
/