存储过程使用DDL语句

-- procedure 中使用 DDL 语句需要将DML封装存入string中,否则会出现异常
-- http://www.cnblogs.com/cici-new/p/4029895.html
/*Error: PLS-00103: 出现符号 "CREATE"在需要下列之一时：
( begin
case declare exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge*/
-- 最后要将表名称提取出成为变量名称
Create Or Replace Procedure modify_table_structure(tab_name In Varchar2) As
  pstring_droptable Varchar2(2000);
  pstring_createtab Varchar2(2000);
  pstring_deletetab Varchar2(2000);
  pstring_altertab  Varchar2(2000);
  pstring_inserttab Varchar2(2000);
  v_count Integer;
  
Begin 
  pstring_droptable := 'drop table '||table_name||'_back';
  pstring_createtab := 'create table '||table_name||'_back as select * from'||table_name;
  pstring_deletetab := 'delete from '||table_name;
  pstring_altertab := 'alter table'||table_name|| 'modify sell_money number (10,5)';
  pstring_inserttab := 
   'insert into '||table_name|| ' (id, busiman_name, sell_date, product_type, sell_money)
   select  id, busiman_name, sell_date, product_type, sell_money
   from  '||table_name||'_BACK' ;
  
  Select Count(*) Into v_count From User_Tables 
  Where table_name = 'table_name'||'_back';
  dbms_output.put_line('v_count 1 ' ||v_count);
  If (v_count > 0) Then
    Execute Immediate pstring_droptable;
    dbms_output.put_line('v_count 2'||v_count);
    End If;
    dbms_output.put_line(pstring_droptable);
    dbms_output.put_line(pstring_createtable);
    dbms_output.put_line(Pstring_deletetable);
    dbms_output.put_line(Pstring_altertable);
    dbms_output.put_line(pstring_inserttable);
    
   Execute Immediate pstring_createtable;
   Execute Immediate pstring_deletetable;
   Execute Immediate pstring_altertable;
   Execute Immediate pstring_inserttable;
   Execute Immediate pstring_droptable;
   
   End; 
  
  
  call Modify_Table_Structure('tab_name');