CREATE OR REPLACE PROCEDURE pro_create_data_table_trigger(tableName in varchar2) IS
v_sql_table VARCHAR2(4000);
v_sql_index1 VARCHAR2(512);
v_sql_index2 VARCHAR2(512);
v_sql_trigger VARCHAR2(4000);
BEGIN
--拼接創建表SQl
v_sql_table:='create table '||tableName||' ('
||'ID NUMBER not null,'
||'TASK_ID NUMBER,'
||'USER_ID NUMBER,'
||'PROJECT_ID NUMBER,'
||'DATA_TYPE NUMBER,'
||'EMAIL VARCHAR2(64),'
||'CONTENT3 VARCHAR2(64),'
||'CONTENT2 VARCHAR2(64),'
||'CONTENT1 VARCHAR2(64),'
||'REAL_STATUS NUMBER,'
||'SHAM_STATUS NUMBER,'
||'OPEN_TIME DATE,'
||'OPEN_COUNT NUMBER,'
||'OPEN_IP VARCHAR2(64),'
||'OPEN_CITY VARCHAR2(64),'
||'CLICK_TIME DATE,'
||'CLICK_COUNT NUMBER,'
||'CLICK_IP VARCHAR2(64),'
||'CLICK_CITY VARCHAR2(64),'
||'REGISTE_TIME DATE,'
||'SEND_TIME DATE,'
||'SEND_IP VARCHAR2(64),'
||'SEND_NAME VARCHAR2(64),'
||'RESEND_COUNT NUMBER,'
||'CATEGORY VARCHAR2(64),'
||'LOCAL_CODE VARCHAR2(64),'
||'LOCAL_MSG VARCHAR2(128),'
||'ESP_CODE VARCHAR2(64),'
||'ESP_MSG VARCHAR2(128),'
||'DELETE_FLAG NUMBER,'
||'CREATE_TIME DATE,'
||'REMARK VARCHAR2(256),'
||'constraint PK_'||tableName||' primary key (ID)'
||')';
-- 拼接創建索引1SQL
v_sql_index1 := 'create index '||tableName||'_index1 on '||tableName||' (task_id)';
-- 拼接創建索引2SQL
v_sql_index2 := 'create index '||tableName||'_index2 on '||tableName||' (category asc)';
-- 拼接創建觸發器的SQL
v_sql_trigger := 'create or replace trigger zuc_'||tableName
||' before insert or update '
||' of email '
||' on '||tableName
||' for each row '
||'DECLARE'
||' v_category VARCHAR2(64);'
||'BEGIN'
||' SELECT esp_name INTO v_category'
||' FROM ex_edm_esp'
||' WHERE mail_suffix=SUBSTR(:new.email,INSTR(:new.email,''@'',1,1)+1, LENGTH(:new.email));'
||' :new.category := v_category;'
||'EXCEPTION'
||' WHEN no_data_found THEN'
||' :new.category := ''未知'';'
||' WHEN too_many_rows THEN'
||' :new.category := ''未知'';'
||' WHEN OTHERS THEN'
||' :new.category := ''未知'';'
||'END;';
-- 執行創建表
EXECUTE IMMEDIATE v_sql_table;
-- 執行創建SQL
EXECUTE IMMEDIATE v_sql_index1;
-- 執行創建SQL
EXECUTE IMMEDIATE v_sql_index2;
-- 創建觸發器
EXECUTE IMMEDIATE v_sql_trigger;
END;
CREATE OR REPLACE TRIGGER create_data_table_trigger
BEFORE INSERT
ON ex_edm_data_table
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
pro_create_data_table_trigger(:new.table_name);
END create_data_table_trigger;