练习七 Procedure中使用DDL
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-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 Line: 9 Text: create table Table_Name(
Compilation errors for PROCEDURE CICI.TEST_CREATE_TABLE 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 Line: 9 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');