Excle生成T层加工ODS层存储过程
Private Sub Create_SQL_Click()
Dim v_target_sql As String '用于目标表INSERT字段并接
Dim v_source_sql As String '用于源表SELECT字段并接
Dim v_target_col_sql As String '用于目标表SELECT字段并接
Dim v_where_sql As String '用于多个字段关联条件并接
Dim v_where_col_num As String '用于单个字段空值判断
Set mysheet1 = Workbooks(ThisWorkbook.Name).Sheets(1) '说明
Set mysheet2 = Workbooks(ThisWorkbook.Name).Sheets(2) '数据字典
Set FSO = CreateObject("Scripting.FileSystemObject")
'建目录
If FSO.FolderExists(ThisWorkbook.Path & "\DB") = False Then '判断文件夹是否存在
FSO.CreateFolder (ThisWorkbook.Path & "\DB")
FSO.CreateFolder (ThisWorkbook.Path & "\DB\DBS")
FSO.CreateFolder (ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "")
FSO.CreateFolder (ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures")
FSO.CreateFolder (ThisWorkbook.Path & "\DB\PATCH")
'建总调角本
Set Fcreate_run = FSO.CreateTextFile(ThisWorkbook.Path & "\DB\PATCH\run.sql", True)
Fcreate_run.WriteLine ("-- Create Procedures ")
Fcreate_run.Close
Set Fcreate_run = Nothing
End If
'建立角本文件
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的列
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '判断是否存储过程开始
Set Fcreate = FSO.CreateTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", True)
Fcreate.Close
Set Fcreate = Nothing
'建总调角本
Set Fcreate_run = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\PATCH\run.sql", 8, False)
Fcreate_run.WriteLine ("@../DBS/" & mysheet1.Range("B2").Value & "/Procedures/" & mysheet2.Range("A" & i).Value & ".prc")
Fcreate_run.Close
Set Fcreate_run = Nothing
End If '判断是否存储过程开始
End If '判断版本
Next i
'增加存储过程头
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的列
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '判断是否存储过程开始
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine ("create or replace procedure " & mysheet1.Range("B2").Value & "." & mysheet2.Range("A" & i).Value & "(P_I_DATE IN VARCHAR2,")
Fopen.WriteLine (" P_O_RESULT OUT VARCHAR2) is")
Fopen.WriteLine (" /*====================================================================+")
Fopen.WriteLine (" 版权信息：版权所有(c) 2012，RESOFT")
Fopen.WriteLine (" 作业名称：" & mysheet2.Range("A" & i).Value & "")
Fopen.WriteLine (" 责任人 : 杨奕彬")
If mysheet2.Range("Q" & i).Value = "T1" Then
Fopen.WriteLine (" 功能描述: 增量事件类加载算法")
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then
Fopen.WriteLine (" 功能描述: 全量登记簿类加载算法")
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then
Fopen.WriteLine (" 功能描述: 状态类不带删除标识拉链加载算法")
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then
Fopen.WriteLine (" 功能描述: 状态类带删除标识拉链加载算法")
End If
Fopen.WriteLine (" 需求来源: ")
Fopen.WriteLine (" 目标表 : " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " " & mysheet2.Range("D" & i).Value & "")
Fopen.WriteLine (" 源表 : " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " " & mysheet2.Range("L" & i).Value & "")
Fopen.WriteLine (" 版本号 : V1.0")
If mysheet2.Range("Q" & i).Value = "T1" Then
Fopen.WriteLine (" 加载策略: 事件类，仅增加，无删")
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then
Fopen.WriteLine (" 加载策略: 登记簿类，全量，无删")
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then
Fopen.WriteLine (" 加载策略: 状态类，全量、增量，无删")
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then
Fopen.WriteLine (" 加载策略: 状态类，全量、无删,对于当天缺少的数据打上删除标识")
End If
Fopen.WriteLine (" 修改历史: V1.0")
Fopen.WriteLine (" 版本 更改日期: 更改人 更改说明")
Fopen.WriteLine (" V1.0 2017.05.10 杨奕彬 create")
Fopen.WriteLine (" =======================================================================*/")
Fopen.WriteLine (" V_DATA_DATE VARCHAR2(8);")
Fopen.WriteLine (" V_STEP VARCHAR2(10) := '0';")
Fopen.WriteLine (" V_SUCCESS VARCHAR2(10) := 'SUCCESS';")
Fopen.WriteLine (" V_FAILED VARCHAR2(10) := 'FAILED';")
Fopen.WriteLine (" V_START_TIME VARCHAR2(100);")
Fopen.WriteLine (" V_END_TIME VARCHAR2(100);")
Fopen.WriteLine (" V_PROC_NAME VARCHAR2(100);")
Fopen.WriteLine (" V_TABLE_NAME VARCHAR2(30);")
Fopen.WriteLine (" V_SCHEMA VARCHAR2(8);")
Fopen.WriteLine (" V_EDATE VARCHAR2(8);")
Fopen.WriteLine ("")
Fopen.WriteLine ("BEGIN")
Fopen.WriteLine ("")
Fopen.WriteLine (" V_DATA_DATE := P_I_DATE;")
Fopen.WriteLine (" P_O_RESULT := V_SUCCESS;")
Fopen.WriteLine (" V_PROC_NAME := '" & mysheet1.Range("B2").Value & "." & mysheet2.Range("A" & i).Value & "';")
Fopen.WriteLine (" V_TABLE_NAME := '" & mysheet2.Range("C" & i).Value & "';")
Fopen.WriteLine (" V_START_TIME := TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF');")
Fopen.WriteLine (" V_SCHEMA := '" & mysheet2.Range("B" & i).Value & "';")
Fopen.WriteLine (" V_EDATE := '99991231';")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If '判断是否存储过程开始
End If '判断版本
Next i
'增加步骤1 数据恢复 T1\T2\T3\T4
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
'针对表第一条记录处理
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '判断表第一条记录
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
Fopen.WriteLine (" --支持重跑，删除当日数据")
Fopen.WriteLine (" V_STEP := '删除当日数据';")
Fopen.WriteLine (" DELETE FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "")
Fopen.WriteLine (" WHERE DATA_DATE ='V_DATA_DATE';")
Fopen.WriteLine (" COMMIT;")
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
Fopen.WriteLine (" --支持重跑，清空目标表数据")
Fopen.WriteLine (" V_STEP := '清空目标表数据';")
Fopen.WriteLine (" EXECUTE IMMEDIATE 'TRUNCATE TABLE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "';")
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
Fopen.WriteLine (" --支持重跑，删除开始日期大于等于数据日期的记录")
Fopen.WriteLine (" V_STEP := '删除开始日期大于等于数据日期的记录';")
Fopen.WriteLine (" DELETE FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "")
Fopen.WriteLine (" WHERE START_DATE>='V_DATA_DATE';")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.WriteLine (" --支持重跑，将end_date大于当天且不为99991231更新为99991231")
Fopen.WriteLine (" V_STEP := '将end_date大于当天且不为99991231开链';")
Fopen.WriteLine (" UPDATE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "")
Fopen.WriteLine (" SET END_DATE=V_EDATE")
Fopen.WriteLine (" WHERE END_DATE >= TO_CHAR(TO_DATE(V_DATA_DATE,'YYYYMMDD')-1,'YYYYMMDD')")
Fopen.WriteLine (" AND END_DATE <> V_EDATE;")
Fopen.WriteLine (" COMMIT;")
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Fopen.WriteLine (" --支持重跑，删除开始日期大于等于数据日期的记录")
Fopen.WriteLine (" V_STEP := '删除开始日期大于等于数据日期的记录';")
Fopen.WriteLine (" DELETE FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "")
Fopen.WriteLine (" WHERE START_DATE>='V_DATA_DATE';")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.WriteLine (" --支持重跑，将end_date大于当天且不为99991231更新为99991231")
Fopen.WriteLine (" V_STEP := '将end_date大于当天且不为99991231开链';")
Fopen.WriteLine (" UPDATE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "")
Fopen.WriteLine (" SET END_DATE=V_EDATE")
Fopen.WriteLine (" WHERE END_DATE >= TO_CHAR(TO_DATE(V_DATA_DATE,'YYYYMMDD')-1,'YYYYMMDD')")
Fopen.WriteLine (" AND END_DATE <> V_EDATE;")
Fopen.WriteLine (" COMMIT;")
End If
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If '判断表第一条记录
End If '判断版本
Next i
'增加步骤2 中间表处理 清空临时表 T1\T2\T3\T4
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
'针对表第一条记录处理
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '判断表第一条记录
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
Fopen.WriteLine (" --清空临时表数据")
Fopen.WriteLine (" V_STEP := '清空临时表数据';")
Fopen.WriteLine (" EXECUTE IMMEDIATE 'TRUNCATE TABLE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1';")
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
Fopen.WriteLine (" --清空临时表数据")
Fopen.WriteLine (" V_STEP := '清空临时表数据';")
Fopen.WriteLine (" EXECUTE IMMEDIATE 'TRUNCATE TABLE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1';")
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
Fopen.WriteLine (" --清空临时表数据")
Fopen.WriteLine (" V_STEP := '清空临时表数据';")
Fopen.WriteLine (" EXECUTE IMMEDIATE 'TRUNCATE TABLE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1';")
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Fopen.WriteLine (" --清空临时表数据")
Fopen.WriteLine (" V_STEP := '清空临时表数据';")
Fopen.WriteLine (" EXECUTE IMMEDIATE 'TRUNCATE TABLE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1';")
End If
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If '判断表第一条记录
End If '判断版本
Next i
'增加步骤3 增量剥离后的数据插入中间表
'源表-目标表
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '表第一条记录处理
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
'INSERT TABLE加工 第一个要插入的目标字段
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工 第一个SELECT字段
v_source_sql = " SELECT " & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
'INSERT TABLE加工 要插入的目标字段
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工 第一个SELECT字段
v_source_sql = " SELECT " & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
'INSERT TABLE加工 要插入的目标字段
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'目标表SELECT 加工 目标表第一个SELECT字段
v_target_col_sql = " SELECT B." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'源表SELECT 加工 源表第一个SELECT字段
v_source_sql = " SELECT A." & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
'INSERT TABLE加工 要插入的目标字段
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'目标表SELECT 加工 目标第一个SELECT字段
v_target_col_sql = " SELECT B." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'源表SELECT 加工 源表第一个SELECT字段
v_source_sql = " SELECT A." & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
End If
Else '表其他记录处理
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
'INSERT TABLE加工 第二个以上要插入的目标字段
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工 第二个以上SELECT字段
v_source_sql = v_source_sql & " ," & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
'INSERT TABLE加工 第二个以上要插入的目标字段
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工 第二个以上SELECT字段 第二个以上目标表SELECT字段
v_source_sql = v_source_sql & " ," & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
'START_DATE、END_DATE不进行MINUS操作
If mysheet2.Range("F" & i).Value <> "START_DATE" And mysheet2.Range("F" & i).Value <> "END_DATE" Then
'INSERT TABLE加工 第二个以上要插入的目标字段
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'目标表SELECT 加工 第二个以上目标表SELECT字段
v_target_col_sql = v_target_col_sql & " ,B." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'源表SELECT 加工 第二个以上源表SELECT字段
v_source_sql = v_source_sql & " ,A." & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
End If
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
'START_DATE、END_DATE不进行MINUS操作
If mysheet2.Range("F" & i).Value <> "START_DATE" And mysheet2.Range("F" & i).Value <> "END_DATE" Then
'INSERT TABLE加工 第二个以上要插入的目标字段
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'DEL_IND值置为' '
If mysheet2.Range("F" & i).Value = "DEL_IND" Then
'目标表SELECT 加工 第二个以上目标表SELECT字段
v_target_col_sql = v_target_col_sql & " ,' '" & vbCrLf
'源表SELECT 加工 第二个以上源表SELECT字段
v_source_sql = v_source_sql & " ,' '" & vbCrLf
Else
'目标表SELECT 加工 第二个以上目标表SELECT字段
v_target_col_sql = v_target_col_sql & " ,B." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'源表SELECT 加工 第二个以上源表SELECT字段
v_source_sql = v_source_sql & " ,A." & mysheet2.Range("M" & i).Value & " --" & mysheet2.Range("N" & i).Value & "" & vbCrLf
End If
End If
End If
End If '第一个字段
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i + 1).Value Then '表最后一条记录处理
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增量剥离后的数据")
Fopen.WriteLine (" V_STEP := '增量剥离后的数据插入临时表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " --" & mysheet2.Range("L" & i).Value & "")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增量剥离后的数据")
Fopen.WriteLine (" V_STEP := '增量剥离后的数据插入临时表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " --" & mysheet2.Range("L" & i).Value & "")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增量剥离后的数据")
Fopen.WriteLine (" V_STEP := '增量剥离后的数据插入临时表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " A --" & mysheet2.Range("L" & i).Value & "")
Fopen.WriteLine (" MINUS")
Fopen.Write (v_target_col_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " B --" & mysheet2.Range("D" & i).Value & "")
Fopen.WriteLine (" WHERE V_DATA_DATE>=B.START_DATE")
Fopen.WriteLine (" AND V_DATA_DATE<=B.END_DATE")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增量剥离后的数据")
Fopen.WriteLine (" V_STEP := '增量剥离后的数据插入临时表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " A --" & mysheet2.Range("L" & i).Value & "")
Fopen.WriteLine (" MINUS")
Fopen.Write (v_target_col_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " B --" & mysheet2.Range("D" & i).Value & "")
Fopen.WriteLine (" WHERE B.END_DATE=V_EDATE")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If
End If '表最后一条记录处理
End If '判断版本
Next i
'增加步骤3-2 用于T4带删除标识拉链 T4
'当天没有送到的记录打上删除标识插入中间表
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '表第一条记录处理
If mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
'INSERT TABLE加工
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'目标表SELECT 加工
v_target_col_sql = " SELECT A." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
End If '
Else '其他字段
If mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
If mysheet2.Range("F" & i).Value <> "START_DATE" And mysheet2.Range("F" & i).Value <> "END_DATE" Then
'INSERT TABLE加工
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
If mysheet2.Range("F" & i).Value = "DEL_IND" Then
'目标表SELECT 加工
v_target_col_sql = v_target_col_sql & " ,'D'" & vbCrLf
Else
'目标表SELECT 加工
v_target_col_sql = v_target_col_sql & " ,A." & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
End If
End If
End If
End If
'以主键做为关联条件
If mysheet2.Range("I" & i).Value = "Y" Then
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '第一个字段
v_where_sql = " AND B." & mysheet2.Range("M" & i).Value & "=A." & mysheet2.Range("F" & i).Value & "" & vbCrLf
v_where_col_num = " AND B." & mysheet2.Range("M" & i).Value & " IS NULL" & vbCrLf
Else
v_where_sql = v_where_sql & " AND B." & mysheet2.Range("M" & i).Value & "=A." & mysheet2.Range("F" & i).Value & "" & vbCrLf
End If
End If
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i + 1).Value Then '表最后一条记录处理
If mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增量数据(DELETE)")
Fopen.WriteLine (" V_STEP := '需要打删除标记的数据插入临时表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_target_col_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " A --" & mysheet2.Range("D" & i).Value & "")
Fopen.WriteLine (" LEFT JOIN " & mysheet2.Range("J" & i).Value & "." & mysheet2.Range("K" & i).Value & " B")
Fopen.WriteLine (" ON 1=1")
Fopen.Write (v_where_sql)
Fopen.WriteLine (" WHERE A.END_DATE=V_EDATE")
Fopen.WriteLine (" AND A.DEL_IND<> 'D'")
Fopen.Write (v_where_col_num)
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If '
End If '表最后一条记录处理
End If '判断版本
Next i
'增加步骤3-1 闭链处理，将中间表中存在的记录在目标表中闭链，用于T3\T4
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
'主键关联条件
If mysheet2.Range("I" & i).Value = "Y" Then
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '表第一行处理
v_where_sql = " AND A." & mysheet2.Range("F" & i).Value & "=B." & mysheet2.Range("F" & i).Value & "" & vbCrLf
Else
v_where_sql = v_where_sql & " AND A." & mysheet2.Range("F" & i).Value & "=B." & mysheet2.Range("F" & i).Value & "" & vbCrLf
End If
End If
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i + 1).Value Then '表最后一行处理
If mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --将目标表中END_DATE=99991231 且在临时表中存在的记录闭链")
Fopen.WriteLine (" V_STEP := '将拉链表中需要闭链的数据闭链';")
Fopen.WriteLine (" UPDATE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " A")
Fopen.WriteLine (" SET END_DATE=TO_CHAR(TO_DATE(V_DATA_DATE,'YYYYMMDD')-1,'YYYYMMDD')")
Fopen.WriteLine (" WHERE EXISTS")
Fopen.WriteLine (" (")
Fopen.WriteLine (" SELECT '1'")
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 B")
Fopen.WriteLine (" WHERE 1 = 1")
Fopen.Write (v_where_sql)
Fopen.WriteLine (" )")
Fopen.WriteLine (" AND A.END_DATE = V_EDATE")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --将目标表中END_DATE=99991231 且在临时表中存在的记录闭链")
Fopen.WriteLine (" V_STEP := '将拉链表中需要闭链的数据闭链';")
Fopen.WriteLine (" UPDATE " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & " A")
Fopen.WriteLine (" SET END_DATE=TO_CHAR(TO_DATE(V_DATA_DATE,'YYYYMMDD')-1,'YYYYMMDD')")
Fopen.WriteLine (" WHERE EXISTS")
Fopen.WriteLine (" (")
Fopen.WriteLine (" SELECT '1'")
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 B")
Fopen.WriteLine (" WHERE 1 = 1")
Fopen.Write (v_where_sql)
Fopen.WriteLine (" )")
Fopen.WriteLine (" AND A.END_DATE = V_EDATE")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If
End If '表最后一行处理
End If '判断版本
Next i
'增加步骤4，中间表插入目标表 T1/T2/T3/T4
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的行
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '第一行记录处理
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
'INSERT TABLE加工
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = " SELECT " & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
'INSERT TABLE加工
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = " SELECT " & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
'INSERT TABLE加工
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = " SELECT " & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
'INSERT TABLE加工
v_target_sql = " INSERT INTO " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "" & vbCrLf
v_target_sql = v_target_sql & " (" & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = " SELECT " & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
End If
Else '其他字段
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
'INSERT TABLE加工
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = v_source_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
'INSERT TABLE加工
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
v_source_sql = v_source_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
'INSERT TABLE加工
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
If mysheet2.Range("F" & i).Value = "START_DATE" Then
v_source_sql = v_source_sql & " ,V_DATA_DATE" & vbCrLf
ElseIf mysheet2.Range("F" & i).Value = "END_DATE" Then
v_source_sql = v_source_sql & " ,V_EDATE" & vbCrLf
Else
v_source_sql = v_source_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
End If
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
'INSERT TABLE加工
v_target_sql = v_target_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
'SELECT 加工
If mysheet2.Range("F" & i).Value = "START_DATE" Then
v_source_sql = v_source_sql & " ,V_DATA_DATE" & vbCrLf
ElseIf mysheet2.Range("F" & i).Value = "END_DATE" Then
v_source_sql = v_source_sql & " ,V_EDATE" & vbCrLf
Else
v_source_sql = v_source_sql & " ," & mysheet2.Range("F" & i).Value & " --" & mysheet2.Range("G" & i).Value & "" & vbCrLf
End If
End If
End If '第一个字段
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i + 1).Value Then '判断最后一个字段
If mysheet2.Range("Q" & i).Value = "T1" Then '增量事件类加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增加或数据日期当天的数据")
Fopen.WriteLine (" V_STEP := '临时表数据插入目标表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 ")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T2" Then '全量登记簿类加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增加或数据日期当天的数据")
Fopen.WriteLine (" V_STEP := '临时表数据插入目标表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 ")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T3" Then '状态类不带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增加或数据日期当天的数据")
Fopen.WriteLine (" V_STEP := '临时表数据插入目标表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 ")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
ElseIf mysheet2.Range("Q" & i).Value = "T4" Then '状态类带删除标识拉链加载算法
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine (" --插入增加或数据日期当天的数据")
Fopen.WriteLine (" V_STEP := '临时表数据插入目标表';")
Fopen.Write (v_target_sql)
Fopen.WriteLine (" )")
Fopen.Write (v_source_sql)
Fopen.WriteLine (" FROM " & mysheet2.Range("B" & i).Value & "." & mysheet2.Range("C" & i).Value & "_TMP1 ")
Fopen.WriteLine (" ;")
Fopen.WriteLine (" COMMIT;")
Fopen.WriteLine ("")
Fopen.Close
Set Fopen = Nothing
End If
End If '判断最后一个字段
End If '判断版本
Next i
'增加存储过程尾
For i = 2 To mysheet2.UsedRange.Rows.Count '遍历所有的列
If mysheet1.Range("B1").Value = mysheet2.Range("P" & i).Value Then '判断版本
If mysheet2.Range("A" & i).Value <> mysheet2.Range("A" & i - 1).Value Then '判断是否存储过程开始
Set Fopen = FSO.OpenTextFile(ThisWorkbook.Path & "\DB\DBS\" & mysheet1.Range("B2").Value & "\Procedures\" & mysheet2.Range("A" & i).Value & ".prc", 8, False)
Fopen.WriteLine ("")
Fopen.WriteLine (" PACK_UTIL.WRITE_TRACE(V_PROC_NAME,")
Fopen.WriteLine (" V_START_TIME,")
Fopen.WriteLine (" V_END_TIME,")
Fopen.WriteLine (" P_O_RESULT);")
Fopen.WriteLine (" EXCEPTION")
Fopen.WriteLine (" WHEN OTHERS THEN")
Fopen.WriteLine (" P_O_RESULT := V_FAILED;")
Fopen.WriteLine (" PACK_UTIL.WRITE_LOG(V_PROC_NAME,")
Fopen.WriteLine (" V_STEP,")
Fopen.WriteLine (" SUBSTR(SQLERRM, 1, 500),")
Fopen.WriteLine (" P_O_RESULT);")
Fopen.WriteLine (" RAISE_APPLICATION_ERROR(-20001, V_PROC_NAME);")
Fopen.WriteLine ("END " & mysheet2.Range("A" & i).Value & ";")
Fopen.WriteLine ("/")
Fopen.Close
Set Fopen = Nothing
End If '判断是否存储过程开始
End If '判断版本
Next i
Set FSO = Nothing
End Sub
浙公网安备 33010602011771号