Oracle:立即执行多个语句

Question

我需要在多个表和索引上创建列，我需要首先捕获列是否已经存在，以便忽略多个执行，下面是处理多个列添加执行的单个异常的正确方法吗?

----------------------------------
-- Alter column null --
----------------------------------
DECLARE
col_allready_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(col_allready_exists, -01430);
BEGIN
execute immediate 'ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0';
execute immediate 'ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0';
execute immediate 'ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0';
execute immediate 'ALTER TABLE XtkRights ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
execute immediate 'ALTER TABLE NmsSeedMember ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
execute immediate 'ALTER TABLE NmsTrackingUrl ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';       
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
/

----------------------------------
-- Index creation --
----------------------------------
DECLARE
already_exists EXCEPTION;
columns_indexed EXCEPTION;
PRAGMA EXCEPTION_INIT ( already_exists, -955 );
PRAGMA EXCEPTION_INIT (columns_indexed, -1408);
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX XTKRIGHTS_TSLASTMODIFIED_IDX ON XTKRIGHTS(tsLastModified)';
EXECUTE IMMEDIATE 'CREATE INDEX ER_TSLASTMODIFIED_IDX_CC057ED6 ON NmsSeedMember(tsLastModified)';
EXECUTE IMMEDIATE 'CREATE INDEX RL_TSLASTMODIFIED_IDX_E5F04BF5 ON NmsTrackingUrl(tsLastModified)';
EXCEPTION
WHEN already_exists OR columns_indexed THEN
dbms_output.put_line('Index already exists, skipping...');
END;
/   

Is the following also correct?
DECLARE
allready_null EXCEPTION;
object_allready_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(allready_null, -01442);     
PRAGMA EXCEPTION_INIT(object_allready_exists, -955);        
BEGIN
execute immediate 'ALTER TABLE NMSACTIVECONTACT MODIFY (ISOURCEID NULL)';
execute immediate 'CREATE TABLE NMSACTIVECONTACT_CPY as SELECT * FROM NMSACTIVECONTACT where 1=0';
EXCEPTION
WHEN allready_null THEN
dbms_output.put_line('ISOURCEID is already null, skipping...');
WHEN object_allready_exists THEN
dbms_output.put_line('NMSACTIVECONTACT_CPY already exists, continuing...');
END;
/

Answer 1

不，捕获每个列的异常。如果你不这样做，那么它可能会在第一列失败，然后它会跳过所有剩余的语句，因为异常处理块被处理。

DECLARE
col_allready_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(col_allready_exists, -01430);
BEGIN
BEGIN
execute immediate 'ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0';
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
BEGIN
execute immediate 'ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0';
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
BEGIN
execute immediate 'ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0';
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
BEGIN
execute immediate 'ALTER TABLE XtkRights ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
BEGIN
execute immediate 'ALTER TABLE NmsSeedMember ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
BEGIN
execute immediate 'ALTER TABLE NmsTrackingUrl ADD tsLastModified TIMESTAMP(6) WITH TIME ZONE';       
EXCEPTION
WHEN col_allready_exists THEN
dbms_output.put_line('Already exists, skipping...');
END;
END;
/

Answer 2

我通常将DDL和异常处理放在一个过程中，并对要添加的每一列调用该过程:

declare
procedure add_column
( p_table_name   user_tab_columns.table_name%type
, p_column_name  user_tab_columns.column_name%type
, p_column_spec  varchar2 )
as
column_already_exists exception;
pragma exception_init(column_already_exists, -1430);
k_ddl constant long :=
'alter table '||p_table_name||' add '||p_column_name||' '||p_column_spec;
begin
execute immediate k_ddl;
dbms_output.put_line(p_table_name||'.'||p_column_name||' added');
exception
when column_already_exists then
dbms_output.put_line(p_table_name||'.'||p_column_name||' already exists, skipping...');
when others then
raise_application_error(-20000, 'Command failed: '||k_ddl, true);
end;
begin
add_column('XtkEnumValue',  'iOrder',        'number(20) default 0');
add_column('XtkReport',     'iDisabled',     'number(3) default 0');
add_column('XtkWorkflow',   'iDisabled',     'number(3) default 0');
add_column('XtkRights',     'tsLastModified','timestamp(6) with time zone');
add_column('NmsSeedMember', 'tsLastModified','timestamp(6) with time zone');
add_column('NmsTrackingUrl','tsLastModified','timestamp(6) with time zone');       
end;

您可以使用类似的逻辑来修改列，添加索引等。

如果我要将相同的列添加到多个表中，我可以使用笛卡尔积构造一个游标For循环来生成所有的表/列组合，并使用minus来减去user_tab_columns中已经存在的那些。