● 테이블 헤더 내용
| 스키마명 | 엔터티명 | 테이블명 | 컬럼ID | 속성명 | 컬럼명 | Data Type | Data length | NOT_NULL |
● 쉬트 새로 생성
Function worksheet_create(sheet_name)
worksheet_exists = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = sheet_name Then worksheet_exits = True
Next ws
' '이미 존재하면 삭제하고 다시 만듬
If worksheet_exists Then
Application.DisplayAlerts = False
Worksheets(sheet_name).Delete
Worksheets.Add after:=Worksheets(6)
ActiveSheet.Name = sheet_name
Else
Worksheets.Add after:=Worksheets(6)
ActiveSheet.Name = sheet_name
End Function
● 표테두리 그리고 자동 행간격 맞춤
Public Function set_layout(sheet_name As String, start_row As Integer)
Set xlsh = Worksheets(sheet_name)
For i = start_row To xlsh.UsedRange.Rows.Count
For j = 0 To xlsh.UsedRange.Columns.Count - 1
If i = start_rows Then
xlsh.Cells(i, j + 1).Interior.ColorIndex = 6
xlsh.Cells(i, j + 1).HorizontalAlignment = xlCenterAcrossSelection
xlsh.Cells(i, j + 1).VerticalAlignment = xlCenter
Else
xlsh.Cells(i, j + 1).Interior.ColorIndex = 0
End If
xlsh.Cells(i, j + 1).Borders.Color = vbBlack
Next j
Next i
xlsh.Columns.AutoFit
End Function
● Ceil 함수
Function Ceil&(n#)
Ceil = -Int(-n)
If n < 0 Then Ceil = Fix(n)
End Function
● Sheet 존재 여부 확인 함수
Public Function IsExistSheet(sheetName As String) As Boolean
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name = sheetName Then
IsExistSheet = True
Exit Function
End If
Next
IsExistSheet = False
End Function
● 테이블 DDL 생성
Function crt_tbl_sql(sheet_name As String)
Dim TB_NM As String
Dim SH_NM As String
Dim COL_NM As String
Dim D_TYP As String
Dim D_LEN As Integer
Dim Def As String
Dim Not_null As String
Dim CR_TB_SQL As String
CR_TB_SQL = ""
Set xlsh = Worksheets(sheet_name)
xlsh.Range("L:O").Clear
For i = 2 To xlsh.UsedRange.Rows.Count
' ' 셀에서 속성 값 가져오기
SH_NM = xlsh.Cells(i, 1).Value
TB_NM = xlsh.Cells(i, 3).Value
COL_NM = xlsh.Cells(i, 6).Value
D_TYP = xlsh.Cells(i, 7).Value
D_LEN = xlsh.Cells(i, 8).Value
' ' 널 속성 가져오기
If xlsh.Cells(i, 9).Value = "NULL" Then
Not_null = ""
Else
Not_null = xlsh.Cells(i, 9).Value
End If
' ' 테이블 DDL 생성
If xlsh.Cells(i, 4).Value = 1 Then
If xlsh.Cells(i, 7).Value <> "DATE" Then
' ' Fist Colum
CR_TB_SQL = "CREATE TABLE " & SH_NM & "." & TB_NM & " (" & Chr(10) & " " & COL_NM & " " & D_TYP & "(" & D_LEN & ") " & Not_null & ","
ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
ddl_fm.TextBox1.SelText = CR_TB_SQL
Else
CR_TB_SQL = "CREATE TABLE " & SH_NM & "." & TB_NM & " ( " & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & ","
ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
ddl_fm.TextBox1.SelText = CR_TB_SQL
End If
Else
If xlsh.Cells(i, 3).Value = xlsh.Cells(i + 1, 3).Value Then
If xlsh.Cells(i, 7).Value <> "DATE" Then
CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & "(" & D_LEN & ") " & Not_null & ","
ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
ddl_fm.TextBox1.SelText = CR_TB_SQL
Else
CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & ","
ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
ddl_fm.TextBox1.SelText = CR_TB_SQL
End If
Else
CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & " )" & Chr(10) & "TABLESPACE CJENM_DATA_TS;" & Chr(10) & "" & Chr(10) & ""
ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
ddl_fm.TextBox1.SelText = CR_TB_SQL
End If
End If
Next i
End Function
● 테이블/컬럼 Comment 쿼리 생성
Function crt_comment_sql(sheet_name As String)
Dim TB_NM As String
Dim SH_NM As String
Dim COL_NM As String
Dim TB_COMT As String
Dim COL_COMT As String
Dim COMT_SQL As String
COMT_SQL = ""
Set xlsh = Worksheets(sheet_name)
For i = 2 To xlsh.UsedRange.Rows.Count
SH_NM = xlsh.Cells(i, 1).Value
TB_NM = xlsh.Cells(i, 3).Value
COL_NM = xlsh.Cells(i, 6).Value
TAB_COMT = xlsh.Cells(i, 2).Value
C_COMT = xlsh.Cells(i, 5).Value
If xlsh.Cells(i, 4).Value = 1 Then
' ' Fist Colum
COMT_SQL = "COMMENT ON TABLE " & SH_NM & "." & TB_NM & " IS '" & TAB_COMT & "' ;" & Chr(10) & "COMMENT ON COLUMN " & SH_NM & "." & TB_NM & "." & COL_NM & " IS '" & C_COMT & "';" & Chr(10)
ddl_fm.TextBox4.SelStart = Len(ddl_fm.TextBox4.Text)
ddl_fm.TextBox4.SelText = COMT_SQL
Else
COMT_SQL = "COMMENT ON COLUMN " & SH_NM & "." & TB_NM & "." & COL_NM & " IS '" & C_COMT & "';" & Chr(10)
ddl_fm.TextBox4.SelStart = Len(ddl_fm.TextBox4.Text)
ddl_fm.TextBox4.SelText = COMT_SQL
End If
Next i
End Function
● PK 생성 쿼리
Function crt_pk_sql(sheet_name As String)
Dim TB_NM As String
Dim SH_NM As String
Dim COL_NM As String
Dim P_SEQ As Integer
Dim C_ID As Integer
Dim UK_SQL As String
Dim PK_SQL As String
Set xlsh = Worksheets(sheet_name)
UK_SQL = " "
PK_SQL = " "
For i = 2 To xlsh.UsedRange.Rows.Count
SH_NM = xlsh.Cells(i, 1).Value
TB_NM = xlsh.Cells(i, 3).Value
C_ID = xlsh.Cells(i, 4).Value
COL_NM = xlsh.Cells(i, 6).Value
P_SEQ = xlsh.Cells(i, 10).Value
If C_ID = 1 Then
If P_SEQ = 1 Then
UK_SQL = "CREATE UNIQUE INDEX PK_" & TB_NM & " ON " & SH_NM & "." & TB_NM & "(" & COL_NM & ""
PK_SQL = "ALTER TABLE " & SH_NM & "." & TB_NM & " ADD CONSTRAINT PK_" & TB_NM & " PRIMARY KEY (" & COL_NM & ""
End If
Else
If P_SEQ > 0 Then
UK_SQL = UK_SQL + ", " & COL_NM & ""
PK_SQL = PK_SQL + ", " & COL_NM & ""
Else
If xlsh.Cells(i - 1, 10).Value > 0 Then
UK_SQL = UK_SQL + ") TABLESPACE CJENM_INDEX_TS;"
PK_SQL = PK_SQL + ") USING INDEX;" & Chr(10) & Chr(10)
ddl_fm.TextBox2.SelStart = Len(ddl_fm.TextBox2.Text)
ddl_fm.TextBox2.SelText = UK_SQL + Chr(10) + PK_SQL
Else
UK_SQL = ""
PK_SQL = ""
End If
End If
End If
Next i
End Function
● Snonym 생성 쿼리
Function crt_syn_sql(sheet_name As String)
Dim TB_NM As String
Dim SH_NM As String
Dim SYN_GT_SQL As String
SYN_GT_SQL = ""
Set xlsh = Worksheets(sheet_name)
For i = 2 To xlsh.UsedRange.Rows.Count
SH_NM = xlsh.Cells(i, 1).Value
TB_NM = xlsh.Cells(i, 3).Value
If xlsh.Cells(i, 4).Value = 1 Then
' ' Fist Colum
SYN_GT_SQL = "CREATE OR REPLACE PUBLIC SYNONYM " & TB_NM & " FOR " & SH_NM & "." & TB_NM & " ;" & Chr(10)
ddl_fm.TextBox3.SelStart = Len(ddl_fm.TextBox3.Text)
ddl_fm.TextBox3.SelText = SYN_GT_SQL
ElseIf xlsh.Cells(i, 4).Value = 2 Then
SYN_GT_SQL = "GRANT SELECT,UPDATE,INSERT,DELETE ON " & SH_NM & "." & TB_NM & " TO ENM_APP ;" & Chr(10) & "GRANT SELECT ON " & SH_NM & "." & TB_NM & " TO RL_ZFI_SEL ;" & Chr(10)
ddl_fm.TextBox3.SelStart = Len(ddl_fhttp://m.TextBox3.Text)
ddl_fm.TextBox3.SelText = SYN_GT_SQL
Else
SYN_GT_SQL = ""
End If
Next i
End Function
● 전체 함수 실행 / 팝업 폼실행
Sub crt_table()
crt_tbl_sql ("Table_Define")
crt_comment_sql ("Table_Define")
crt_syn_sql ("Table_Define")
crt_pk_sql ("Table_Define")
ddl_fm.Show
End Sub
Sub show_form()
ddl_fm.Top = 60
ddl_fm.Left = 200
ddl_fm.Show
End Sub
'OS > Windows' 카테고리의 다른 글
| tasklist 활용(windows command) (1) | 2023.11.15 |
|---|---|
| windows 포트포워딩 설정 (1) | 2023.07.02 |