存储过程使用DDL语句

-- procedure 中使用 DDL 语句需要将DML封装存入string中,否则会出现异常 -- https://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'); -- procedure 中使用 DDL 语句需要将DML封装存入string中,否则会出现异