【VBA】EXCEL通过VBA生成SQL，自动生成创建表结构SQL

原文：https://blog.csdn.net/zutsoft/article/details/45441343

编程往往与数据库密不可分，一个项目往往有很多的表，很多时候通过excel来维护表结构，记录表名，字段，类型，注释等等信息，将表结构整理到excel已经是件很累的事了，如果整理好的表结构只是用来查字段当字典用，那就太浪费了，excel提供vba编程，我们可以通过一段小脚本来实现一键创建表结构。使用脚本创建的语句需要标准化每个sheet页的格式，格式根据自己需要来做，这里给出我使用的格式和脚本。

EXCEL模板

VBA编程

打开excel vba编程工具，在ThisWorkbook中添加如下代码：

 

[vb] view plain copy
 
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)  
  2. '关闭工作簿前，删除新创建的工具条先  
  3.   Dim bar_name As String  
  4.   bar_name = "HRBSJ"  
  5.   Application.CommandBars(bar_name).Delete  
  6.   On Error GoTo Exception  
  7.   Exit Sub  
  8. Exception:  
  9. End Sub  
  10. Private Sub Workbook_Open()  
  11. '打开工作簿时创建工具条  
  12.   Dim bar_name As String  
  13.   Dim new_bar As Office.CommandBar  
  14.     
  15.   bar_name = "HRBSJ"  
  16.   
  17.   Set new_bar = Application.CommandBars.Add(bar_name)  
  18.   new_bar.Visible = True  
  19.     
  20.   new_bar.Position = msoBarLeft  
  21.     
  22.         
  23.   With new_bar.Controls.Add(Type:=msoControlButton, before:=1)  
  24.     .BeginGroup = True  
  25.     .Caption = "生成建表脚本"  
  26.     .TooltipText = "生成建表脚本"  
  27.     .Style = msoButtonCaption  
  28.     .OnAction = "Create_HR_Table_Script"  
  29.   End With  
  30.     
  31.   
  32.     
  33.   On Error GoTo Exception  
  34.   Exit Sub  
  35. Exception:  
  36. End Sub  

 

创建模块：

 
[vb] view plain copy
 
  1. Private Sub ShowTable()  
  2.   frmTable.Show  
  3. End Sub  
  4. Private Sub Create_HR_Table_Script()  
  5.   Dim line_tablename As Integer, len_col_id As Integer, len_str_type As Integer, col_num As Integer  
  6.   Dim do_column As Boolean, column_end As Boolean  
  7.   Dim table_name As String, str_col_id As String, str_space As String  
  8.   Dim primary_col As String, index_col As String, str_primary As String  
  9.     
  10.   Dim str_temp As String, str_type As String, str_null As String, str_column As String  
  11.   
  12.        
  13.   max_line = 1000  
  14.   no_data = 0  
  15.   do_column = False  
  16.   column_end = False  
  17.   str_column = ""  
  18.   str_index = ""  
  19.   line_tablename = 6  
  20.     
  21.   Set fs = CreateObject("Scripting.FileSystemObject")  
  22.   sFilePath = ActiveWorkbook.Path & "\Script\"  
  23.   If Dir(sFilePath, vbDirectory) = "" Then  
  24.     MkDir sFilePath  
  25.   End If  
  26.     
  27.   sFileName = sFilePath & "Create_HR_Table_Script.sql"  
  28.   Set fhandle = fs.CreateTextFile(sFileName, True)  
  29.   
  30.   fhandle.WriteLine ("--华融表结构创建脚本，对应数据库Oracle")  
  31.   fhandle.WriteLine ("--建表脚本创建开始:" & Date & " " & Time)  
  32.     
  33.   fhandle.WriteLine ("")  
  34.   fhandle.WriteLine ("DECLARE")  
  35.   fhandle.WriteLine ("  --判断表是否存在")  
  36.   fhandle.WriteLine ("  FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)")  
  37.   fhandle.WriteLine ("    RETURN BOOLEAN AS")  
  38.   fhandle.WriteLine ("   iExists PLS_INTEGER;")  
  39.   fhandle.WriteLine ("  BEGIN")  
  40.   fhandle.WriteLine ("    SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name  = UPPER(sTableName);")  
  41.   fhandle.WriteLine ("    RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;")  
  42.   fhandle.WriteLine ("  END;")  
  43.   fhandle.WriteLine ("")  
  44.   fhandle.WriteLine ("BEGIN")  
  45.     
  46.   For i_index = 2 To Sheets.Count  '第一页是目录这里的参数控制开始的页数到截止的sheet页  
  47.       Sheets(i_index).Select  '从第二页开始，循环sheet页  
  48.         
  49.         
  50.       For i_line = 3 To max_line  
  51.         first_col = Trim(Sheets(i_index).Cells(i_line, 2))  
  52.           
  53.           
  54.         Select Case first_col  
  55.           Case "目标表说明"  
  56.             table_name = Trim(Sheets(i_index).Cells(3, 4))  
  57.               
  58.             primary_col = Trim(Sheets(i_index).Cells(5, 4))  
  59.               
  60.             index_col = Trim(Sheets(i_index).Cells(8, 4))  
  61.               
  62.               
  63.             If Len(primary_col) > 0 Then  
  64.                 primary_col = Replace(primary_col, "，", ",")  
  65.                 str_primary = "alter table " & table_name & " " & "add constraint pk_" & table_name & " primary key (" & primary_col & ")"  
  66.             Else  
  67.                 str_primary = ""  
  68.             End If  
  69.               
  70.             If Len(index_col) > 0 Then  
  71.                 index_col = Replace(index_col, "，", ",")  
  72.             Else  
  73.                 index_col = ""  
  74.             End If  
  75.             
  76.           Case "序号"  
  77.             fhandle.WriteLine ("")  
  78.             fhandle.WriteLine ("/* Table：" & table_name & "  " & Trim(Sheets(i_index).Cells(2, 2)) & "  */")  
  79.             fhandle.WriteLine ("IF fc_IsTabExists('" & table_name & "') THEN")  
  80.             fhandle.WriteLine ("  execute immediate 'drop table " & table_name & "';")  
  81.             fhandle.WriteLine ("END IF;")  
  82.             fhandle.WriteLine ("")  
  83.             fhandle.WriteLine ("execute immediate '")  
  84.             fhandle.WriteLine ("create table " & table_name)  
  85.             fhandle.WriteLine ("(")  
  86.               
  87.            Case 1  
  88.             do_column = True  
  89.            Case ""  
  90.             do_column = False  
  91.         End Select  
  92.           
  93.         If Trim(Sheets(i_index).Cells(i_line, 2)) = "" Then  
  94.           do_column = False  
  95.         End If  
  96.           
  97.         str_temp = ""  
  98.         str_column = ""  
  99.           
  100.         If do_column = True Then  
  101.            
  102.          '标识最后一个字段列  
  103.          If Trim(Sheets(i_index).Cells(i_line + 1, 2)) = "" Or Trim(Sheets(i_index).Cells(i_line + 1, 3)) = "" Then  
  104.              column_end = True  
  105.          Else  
  106.              column_end = False  
  107.          End If  
  108.           
  109.          '字段处理，及与数据类型的空格数处理  
  110.           str_col_id = Trim(Sheets(i_index).Cells(i_line, 3))  
  111.           len_col_id = Len(str_col_id)  
  112.           For i = len_col_id To 30  
  113.              str_space = str_space & " "  
  114.           Next  
  115.           str_column = str_col_id & str_space  
  116.             
  117.           '数据类型的处理  
  118.           str_space = ""  
  119.           str_type = Trim(Sheets(i_index).Cells(i_line, 4))  
  120.    
  121.           len_str_type = Len(str_type)  
  122.           For i = len_str_type To 16  
  123.              str_space = str_space & " "  
  124.           Next  
  125.             
  126.           str_column = str_column & str_type & str_space  
  127.             
  128.           '是否为空的处理  
  129.           str_space = ""  
  130.           str_temp = Trim(Sheets(i_index).Cells(i_line, 5))  
  131.           Select Case str_temp  
  132.             Case "N"  
  133.                str_null = "not null"  
  134.             Case Else  
  135.                str_null = ""  
  136.           End Select  
  137.                                 
  138.           str_column = str_column & str_null  
  139.             
  140.           '加一列  
  141.           If column_end = False Then  
  142.             str_column = str_column & ","  
  143.             fhandle.WriteLine ("  " & str_column)  
  144.           Else  
  145.             fhandle.WriteLine ("  " & str_column)  
  146.             fhandle.WriteLine (") tablespace TS_TDC';")  
  147.           End If  
  148.             
  149.         End If  
  150.           
  151.       Next ' 结束工作表的循环  
  152.         
  153.        '--加注释  
  154.        If Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then  
  155.        fhandle.WriteLine (" -- Add comments to the table")  
  156.        fhandle.WriteLine ("execute immediate 'comment on table  " & Trim(Sheets(i_index).Cells(3, 4)) & " is ''" & Trim(Sheets(i_index).Cells(2, 2)) & "''';")  
  157.        fhandle.WriteLine (" -- Add comments to the columns")  
  158.        For i_line = 15 To max_line  
  159.        If Trim(Sheets(i_index).Cells(i_line, 2)) <> "" And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then  
  160.        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)) & "''';")  
  161.        End If  
  162.        Next ' 结束工作表的循环  
  163.        End If  
  164.           
  165.        '--加主键  
  166.        If Len(str_primary) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then  
  167.         fhandle.WriteLine ("")  
  168.         fhandle.WriteLine ("execute immediate '" & str_primary & " using index tablespace TS_TDC';")  
  169.        End If  
  170.          
  171.        '--加索引  
  172.        If Len(index_col) > 0 And Trim(Sheets(i_index).Cells(3, 2)) = "目标表说明" Then  
  173.        fhandle.WriteLine ("")  
  174.        fhandle.WriteLine ("execute immediate 'create index i_" & table_name & " on " & table_name & " (" & index_col & " )  tablespace TS_TDC';")  
  175.        End If  
  176.   
  177.   
  178.                                  
  179.   Next '结束工作簿的循环  
  180.   fhandle.WriteLine ("")  
  181.   fhandle.WriteLine ("END;")  
  182.   fhandle.WriteLine ("/")  
  183.     
  184.   fhandle.Close  
  185.     
  186.   Sheets(1).Select  
  187.     
  188.   MsgBox "表结构创建脚本成功！文件名" & sFileName  
  189. End Sub  


保存后可在excel上看到 ‘生成建表脚本’ 按钮

 

最终的生产结果示例

 

[sql] view plain copy
 
    1. --表结构创建脚本，对应数据库Oracle  
    2. --建表脚本创建开始:2015/5/2 18:35:26  
    3.   
    4. DECLARE  
    5.   --判断表是否存在  
    6.   FUNCTION fc_IsTabExists(sTableName IN VARCHAR2)  
    7.     RETURN BOOLEAN AS  
    8.    iExists PLS_INTEGER;  
    9.   BEGIN  
    10.     SELECT COUNT(*) INTO iExists FROM user_tables ut WHERE ut.table_name  = UPPER(sTableName);  
    11.     RETURN CASE WHEN iExists > 0 THEN TRUE ELSE FALSE END;  
    12.   END;  
    13.   
    14. BEGIN  
    15.   
    16. /* Table：TEST  测试表  */  
    17. IF fc_IsTabExists('TEST') THEN  
    18.   execute immediate 'drop table TEST';  
    19. END IF;  
    20.   
    21. execute immediate '  
    22. create table TEST  
    23. (  
    24.   c_unique_no                    VARCHAR2(32)     not null,  
    25.   c_proj_no                      VARCHAR2(40)     not null,  
    26.   c_busi_type                    VARCHAR2(40)     not null,  
    27.   c_cust_ID                      VARCHAR2(32)       
    28. ) tablespace TEST';  
    29.  -- Add comments to the table  
    30. execute immediate 'comment on table  TEST is ''测试表''';  
    31.  -- Add comments to the columns  
    32. execute immediate 'comment on column TEST.c_unique_no is ''PK主键''';  
    33. execute immediate 'comment on column TEST.c_proj_no is ''项目编号''';  
    34. execute immediate 'comment on column TEST.c_busi_type is ''业务类型''';  
    35. execute immediate 'comment on column TEST.c_cust_ID is ''客户ID''';  
    36.   
    37.   
    38. execute immediate 'alter table TEST add constraint pk_TEST primary key (c_unique_no) using index tablespace TEST';  
    39.   
    40. END;  
    41. /  
posted @ 2018-04-13 09:55  eyesfree  阅读(1100)  评论(0)  编辑  收藏  举报