拉链表的自动处理函数

CREATE FUNCTION f_extract_incr_data_scd(i_schema_name_src character varying, i_table_name_src character varying, i_etl_date character varying, i_pk character varying, i_schema_name_dest character varying, i_table_name_dest character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$                                                                                                                                                 
/*参数解释
i_schema_name_src：拉链表新增数据来源表的schema
i_table_name_src：拉链表新增数据来源表表名
i_etl_date：跑批时间，格式：yyyy-mm-dd
i_pk：拉链表的主键字段列表，如有多个字段，以','分隔
i_schema_name_dest：拉链表schema
i_table_name_dest：拉链表表名
返回值：1（成功），-1（失败）
*/
	
declare                                                                                                                                                                                                                                      
		 v_schema_name_src	 	varchar2(80);
         v_table_name_src	  	varchar2(80);
         v_schema_name_dest	varchar2(80);
         v_table_name_dest 	varchar2(80); 
         v_tmp_table_name 	varchar2(80);
         v_pk			  	varchar2(200);                                                                                                                                                                                                 
         v_etl_date			varchar2(20); 
         v_mark				number(1);
         v_current_date		date;
         v_scd_start_date 	date;
		 v_scd_end_date 	date;  
		 v_return			integer;                                                                                                                                                                                                 
         v_column_list_src 	varchar2(4000);
         v_column_list_dest	varchar2(4000);
         v_column_list_enti	varchar2(4000);
         v_join_condition	varchar2(4000);  
         v_sql         		varchar2(9000);                                                                                                                                                
begin                                                                                                                                                                                                                                         
         v_schema_name_src := i_schema_name_src;   
		 v_table_name_src := i_table_name_src;
		 v_schema_name_dest := i_schema_name_dest;
		 v_table_name_dest := i_table_name_dest;
		 v_tmp_table_name := 't_'||v_table_name_src;
		 v_etl_date := i_etl_date;
		 v_pk := i_pk;
		 v_current_date := to_date(v_etl_date||' 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
		 v_scd_start_date := now();
		 v_scd_end_date := to_date('9999-12-31','yyyy-mm-dd');
		 v_mark := 1;
		 v_return := 0;
                                                                                                                                  
         dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||' call procedure begin');
		 
		 --获取新增数据源表字段列表（排除审计字段）用于数据比对
		 select string_agg(column_name,',' order by column_name) column_list into v_column_list_src
		 from user_tab_columns where table_name = v_table_name_src
		 and column_name not in ('proc_date','batch_id','data_src','entity_name','is_curren','end_date','begin_date');
		 --获取拉链表字段列表（排除审计字段）用于数据比对
		 select string_agg(column_name,',' order by column_name) column_list into v_column_list_dest
		 from user_tab_columns where table_name = v_table_name_dest
		 and column_name not in ('proc_date','batch_id','data_src','entity_name','is_curren','end_date','begin_date');
		 --如入参v_pk为‘*’，则获取所有的字段（审计字段除外）用于进行表关联
		  if v_pk = '*' then
		 	v_pk := v_column_list_src; 	
         end if;
 		 v_pk := ''''||replace(v_pk,',',''''||','||'''')||'''';
 		 --将join字段写入临时表
         create temporary table tmp_join_condition ( col_name text);
         v_sql := 'insert into tmp_join_condition select unnest(array['||v_pk||'])';
         execute immediate v_sql;
         --生成join条件
      	 select string_agg('a.'||a.col_name||' = b.'||a.col_name, ' and ') into v_join_condition from tmp_join_condition a;
         --创建临时表，用于保存新增的数据（即有新的PK）		 
		 v_sql := ' create temporary table '||v_tmp_table_name||' (like '||v_schema_name_src||'.'||v_table_name_src||')';
		 execute immediate v_sql;
		 
		 dbms_output.put_line(to_char(v_current_date,'yyyy-mm-dd hh24:mi:ss')||' create tmp');
		 --交付新增数据写入临时表
		 v_sql := ' insert into '||v_tmp_table_name;
		 v_sql := v_sql||'('||v_column_list_dest||',proc_date,batch_id,data_src,entity_name,is_curren,end_date,begin_date)';
		 v_sql := v_sql||' select '||v_column_list_dest||',proc_date,batch_id,data_src,entity_name,is_curren,end_date,begin_date from '||v_schema_name_src||'.'||v_table_name_src||' a';
		 v_sql := v_sql||' where exists (select 1 from (';		 
		 v_sql := v_sql||' select '||v_column_list_src||' from '||v_schema_name_src||'.'||v_table_name_src;
		 v_sql := v_sql||' minus ';
		 v_sql := v_sql||' select '||v_column_list_dest||' from '||v_schema_name_dest||'.'||v_table_name_dest;
		 v_sql := v_sql||' where is_curren = 1) b';
		 v_sql := v_sql||' where '|| v_join_condition;
		 v_sql := v_sql||')';
		 
		 dbms_output.put_line(v_sql);
		 execute immediate v_sql using in v_current_date;
		 
		 v_sql := ' update '||v_tmp_table_name;
		 v_sql := v_sql||' set entity_name = :1';
		 dbms_output.put_line(v_sql);
		 execute immediate v_sql using in v_table_name_src;		 
		 
		 --将发生变化的老数据关闭
	     v_sql := 'update '||v_schema_name_dest||'.'||v_table_name_dest||' a set a.end_date = :1,a.is_curren = 0 ';
		 v_sql := v_sql||' where a.is_curren = 1 and exists (select 1 from '||v_tmp_table_name||' b ';
		 v_sql := v_sql||' where '||v_join_condition||')';
		 dbms_output.put_line(v_sql);
		 execute immediate v_sql using in v_scd_start_date;
		 dbms_output.put_line(SQl%ROWCOUNT);
		 --将新增和发生变化的数据写入目标表
		 v_sql := 'insert into '||v_schema_name_dest||'.'||v_table_name_dest;
		 v_sql := v_sql||'('||v_column_list_dest||',proc_date,batch_id,data_src,entity_name,is_curren,end_date,begin_date)';
		 v_sql := v_sql||' select '||v_column_list_dest||',proc_date,batch_id,data_src,entity_name,is_curren,end_date,begin_date from '||v_tmp_table_name||' b' ;
		 dbms_output.put_line(v_sql);
	     execute immediate v_sql using in v_scd_start_date,v_scd_end_date;
	     dbms_output.put_line(SQl%ROWCOUNT);
	     
	     drop table tmp_join_condition ;
	     v_sql := ' drop table '||v_tmp_table_name||';';
		 execute immediate v_sql;
	     v_return := 1;
	     
		 dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||' call procedure end');
		 
		 return v_return;
		 
         EXCEPTION                                                                                                                                                                                                                            
            WHEN OTHERS THEN  
            begin                                                                                                                                                                                                                  
				dbms_output.put_line(v_sql||'-----'||sqlerrm);   
				v_return := -1;  
			end;
		
		return v_return; 
END $$;