-- 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');
存储过程使用DDL语句
最新推荐文章于 2024-01-13 20:30:00 发布