|
编程往往与数据库密不可分，一个项目往往有很多的表，很多时候通过excel来维护表结构，记录表名，字段，类型，注释等等信息，将表结构整理到excel已经是件很累的事了，如果整理好的表结构只是用来查字段当字典用，那就太浪费了，excel提供vba编程，我们可以通过一段小脚本来实现一键创建表结构。使用脚本创建的语句需要标准化每个sheet页的格式，格式根据自己需要来做，这里给出我使用的格式和脚本。
EXCEL模板
VBA编程
打开excel vba编程工具，在ThisWorkbook中添加如下代码：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'关闭工作簿前，删除新创建的工具条先
Dim bar_name As String
bar_name = "HRBSJ"
Application.CommandBars(bar_name).Delete
On Error GoTo Exception
Exit Sub
Exception:
End Sub
Private Sub Workbook_Open()
'打开工作簿时创建工具条
Dim bar_name As String
Dim new_bar As Office.CommandBar
bar_name = "HRBSJ"
Set new_bar = Application.CommandBars.Add(bar_name)
new_bar.Visible = True
new_bar.Position = msoBarLeft
With new_bar.Controls.Add(Type:=msoControlButton, before:=1)
.BeginGroup = True
.Caption = "生成建表脚本"
.TooltipText = "生成建表脚本"
.Style = msoButtonCaption
.OnAction = "Create_HR_Table_Script"
End With
On Error GoTo Exception
Exit Sub
Exception:
End Sub
创建模块：
Private Sub ShowTable()
frmTable.Show
End Sub
Private Sub Create_HR_Table_Script()
Dim line_tablename As Integer, len_col_id As Integer, len_str_type As Integer, col_num As Integer
Dim do_column As Boolean, column_end As Boolean
Dim table_name As String, str_col_id As String, str_space As String
Dim primary_col As String, index_col As String, str_primary As String
Dim str_temp As String, str_type As String, str_null As String, str_column As String
max_line = 1000
no_data = 0
do_column = False
column_end = False
str_column = ""
str_index = ""
line_tablename = 6
Set fs = CreateObject("Scripting.FileSystemObject")
sFilePath = ActiveWorkbook.Path & "Script"
If Dir(sFilePath, vbDirectory) = "" Then
MkDir sFilePath
End If
sFileName = sFilePath & "Create_HR_Table_Script.sql"
Set fhandle = fs.CreateTextFile(sFileName, True)
fhandle.WriteLine ("--华融表结构创建脚本，对应数据库Oracle")
fhandle.WriteLine ("--建表脚本创建开始:" & Date & " " & Time)
fhandle.WriteLine ("")
fhandle.WriteLine ("DECLARE")
fhandle.WriteLine (" --判断表是否存在")
fhandle.WriteLine (" FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)")
fhandle.WriteLine (" RETURN BOOLEAN AS")
fhandle.WriteLine (" iExists PLS_INTEGER;")
fhandle.WriteLine (" BEGIN")
fhandle.WriteLine (" SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name = UPPER(sTableName);")
fhandle.WriteLine (" RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;")
fhandle.WriteLine (" END;")
fhandle.WriteLine ("")
fhandle.WriteLine ("BEGIN")
For i_index = 2 To Sheets.Count '第一页是目录这里的参数控制开始的页数到截止的sheet页
Sheets(i_index).Select '从第二页开始，循环sheet页
For i_line = 3 To max_line
first_col = Trim(Sheets(i_index).Cells(i_line, 2))
Select Case first_col
Case "目标表说明"
table_name = Trim(Sheets(i_index).Cells(3, 4))
primary_col = Trim(Sheets(i_index).Cells(5, 4))
index_col = Trim(Sheets(i_index).Cells(8, 4))
If Len(primary_col) > 0 Then
primary_col = Replace(primary_col, "，", ",")
str_primary = "alter table " & table_name & " " & "add constraint pk_" & table_name & " primary key (" & primary_col & ")"
Else
str_primary = ""
End If
If Len(index_col) > 0 Then
index_col = Replace(index_col, "，", ",")
Else
index_col = ""
End If
Case "序号"
fhandle.WriteLine ("")
fhandle.WriteLine ("/* Table：" & table_name & " " & Trim(Sheets(i_index).Cells(2, 2)) & " */")
fhandle.WriteLine ("IF fc_IsTabExists('" & table_name & "') THEN")
fhandle.WriteLine (" execute immediate 'drop table " & table_name & "';")
fhandle.WriteLine ("END IF;")
fhandle.WriteLine ("")
fhandle.WriteLine ("execute immediate '")
fhandle.WriteLine ("create table " & table_name)
fhandle.WriteLine ("(")
Case 1
do_column = True
Case ""
do_column = False
End Select
If Trim(Sheets(i_index).Cells(i_line, 2)) = "" Then
do_column = False
End If
str_temp = ""
str_column = ""
If do_column = True Then
'标识最后一个字段列
If Trim(Sheets(i_index).Cells(i_line + 1, 2)) = "" Or Trim(Sheets(i_index).Cells(i_line + 1, 3)) = "" Then
column_end = True
Else
column_end = False
End If
'字段处理，及与数据类型的空格数处理
str_col_id = Trim(Sheets(i_index).Cells(i_line, 3))
len_col_id = Len(str_col_id)
For i = len_col_id To 30
str_space = str_space & " "
Next
str_column = str_col_id & str_space
'数据类型的处理
str_space = ""
str_type = Trim(Sheets(i_index).Cells(i_line, 4))
len_str_type = Len(str_type)
For i = len_str_type To 16
str_space = str_space & " "
Next
str_column = str_column & str_type & str_space
'是否为空的处理
str_space = ""
str_temp = Trim(Sheets(i_index).Cells(i_line, 5))
Select Case str_temp
Case "N"
str_null = "not null"
Case Else
str_null = ""
End Select
str_column = str_column & str_null
'加一列
If column_end = False Then
str_column = str_column & ","
fhandle.WriteLine (" " & str_column)
Else
fhandle.WriteLine (" " & str_column)
fhandle.WriteLine (") tablespace TS_TDC';")
End If
End If
Next ' 结束工作表的循环
'--加注释
If Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
fhandle.WriteLine (" -- Add comments to the table")
fhandle.WriteLine ("execute immediate 'comment on table " & Trim(Sheets(i_index).Cells(3, 4)) & " is ''" & Trim(Sheets(i_index).Cells(2, 2)) & "''';")
fhandle.WriteLine (" -- Add comments to the columns")
For i_line = 15 To max_line
If Trim(Sheets(i_index).Cells(i_line, 2)) <> "" And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
fhandle.WriteLine ("execute immediate 'comment on column " & Trim(Sheets(i_index).Cells(3, 4)) & "." & Trim(Sheets(i_index).Cells(i_line, 3)) & " is ''" & Trim(Sheets(i_index).Cells(i_line, 7)) & "''';")
End If
Next ' 结束工作表的循环
End If
'--加主键
If Len(str_primary) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
fhandle.WriteLine ("")
fhandle.WriteLine ("execute immediate '" & str_primary & " using index tablespace TS_TDC';")
End If
'--加索引
If Len(index_col) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then
fhandle.WriteLine ("")
fhandle.WriteLine ("execute immediate 'create index i_" & table_name & " on " & table_name & " (" & index_col & " ) tablespace TS_TDC';")
End If
Next '结束工作簿的循环
fhandle.WriteLine ("")
fhandle.WriteLine ("END;")
fhandle.WriteLine ("/")
fhandle.Close
Sheets(1).Select
MsgBox "表结构创建脚本成功！文件名" & sFileName
End Sub
保存后可在excel上看到 ‘生成建表脚本’ 按钮
最终的生产结果示例
--表结构创建脚本，对应数据库Oracle
--建表脚本创建开始:2015/5/2 18:35:26
DECLARE
--判断表是否存在
FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)
RETURN BOOLEAN AS
iExists PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name = UPPER(sTableName);
RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;
END;
BEGIN
/* Table：TEST 测试表 */
IF fc_IsTabExists('TEST') THEN
execute immediate 'drop table TEST';
END IF;
execute immediate '
create table TEST
(
c_unique_no VARCHAR2(32) not null,
c_proj_no VARCHAR2(40) not null,
c_busi_type VARCHAR2(40) not null,
c_cust_ID VARCHAR2(32)
) tablespace TEST';
-- Add comments to the table
execute immediate 'comment on table TEST is ''测试表''';
-- Add comments to the columns
execute immediate 'comment on column TEST.c_unique_no is ''PK主键''';
execute immediate 'comment on column TEST.c_proj_no is ''项目编号''';
execute immediate 'comment on column TEST.c_busi_type is ''业务类型''';
execute immediate 'comment on column TEST.c_cust_ID is ''客户ID''';
execute immediate 'alter table TEST add constraint pk_TEST primary key (c_unique_no) using index tablespace TEST';
END;
/
|
