1 在存储过程中使用ddl语句有如下异常:
create or replace procedure test_create_table
(Table_Name in VARCHAR2,
column_name1 in varchar2,
column_name2 in varchar2,
column_name3 in varchar2,
column_type in varchar2) as
/*修改某个表的表结构*/
begin
create table Table_Name( column_name1 column_type,
column_name2 column_type,
column_name3 column_type
);
end test_create_table;
Compilation errors for PROCEDURE CICI.TEST_CREATE_TABLE Error: PLS-: 出现符号 "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
Line:
Text: create table Table_Name(
Compilation errors for PROCEDURE CICI.TEST_CREATE_TABLE Error: PLS-: 出现符号 "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
Line:
Text: create table Table_Name(
2 将DML封装存入string中
create or replace procedure Modify_Table_Structure as
Pstring_droptable varchar2(2000);
Pstring_createtable varchar2(2000);
Pstring_deletetable varchar2(2000);
Pstring_altertable varchar2(2000);
pstring_inserttable varchar2(2000);
v_count integer;
begin
Pstring_droptable := 'drop table CONTRACT_KANGJIA_BACK';
Pstring_createtable := 'create table CONTRACT_KANGJIA_BACK as select * from CONTRACT_KANGJIA ';
Pstring_deletetable := 'delete from CONTRACT_KANGJIA';
Pstring_altertable := 'alter table CONTRACT_KANGJIA modify sell_money number(10,5)';
pstring_inserttable := 'insert into CONTRACT_KANGJIA (id, busiman_name, sell_date, product_type, sell_money) select id, busiman_name, sell_date, product_type, sell_money
from CONTRACT_KANGJIA_BACK'; select count(*)
into v_count
from user_tables
where table_name = 'CONTRACT_KANGJIA_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; execute immediate Pstring_createtable;
execute immediate Pstring_deletetable;
execute immediate Pstring_altertable;
execute immediate pstring_inserttable;
execute immediate Pstring_droptable;
end;
3 进一步提炼.将表名称提取成变量名称
create or replace procedure Modify_Table_Structure(table_name in varchar2) as
Pstring_droptable varchar2(2000);
Pstring_createtable varchar2(2000);
Pstring_deletetable varchar2(2000);
Pstring_altertable varchar2(2000);
pstring_inserttable varchar2(2000);
v_count integer;
begin
Pstring_droptable := 'drop table '||table_name||'_BACK';
Pstring_createtable := 'create table '||table_name||'_BACK as select * from '||table_name;
Pstring_deletetable := 'delete from '||table_name;
Pstring_altertable := 'alter table '||table_name|| ' modify sell_money number(10,5)';
pstring_inserttable :=
'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;
4 执行成功！
call Modify_Table_Structure('contract_kangjia');