梦幻之旅

DEBUG - 天道酬勤

   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  671 随笔 :: 6 文章 :: 256 评论 :: 0 Trackbacks
CREATE OR REPLACE PROCEDURE pro_create_data_table_trigger(tableName in varchar2IS
    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;
posted on 2011-06-18 18:43 HUIKK 阅读(123) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: