Option Explicit
' 所有要写入文件的数据
Dim alldata() As String

' 表字段设定注释数组数据
Dim columnsCommentsArray() As String


' 数据所在单元格ID
' 物理表名ID
Const physicsTableNameRangeId = "C5"
' 表名称ID
Const tableNameRangeId = "C6"

' 列名(中文说明)所在列ID，例如：姓名
Const columnNameId = "B"
' 列名（物理）所在列ID，例如：NAME
Const physicsColumnNameId = "C"
' Data属性所在列ID，例如：NUMBER
Const dateTypeId = "D"
' 长度所在列ID，例如：3, 0
Const lengthId = "E"
' key所在列ID，例如：PK
Const keyId = "F"
' 是否可以为空ID，例如：NOT NULL
Const notNullId = "G"
' 默认值ID，例如：0
Const defaultValueId = "H"

' 1个空格
Const space1 = " "
' 2个空格
Const space2 = "  "
' 5个空格
Const space5 = "     "

' 数据值
' 物理表名数据
Dim physicsTableNameRangeValue As String
' 表名称数据
Dim tableNameRangeValue As String

' 列名(中文说明)所在列数据
Dim columnNameValue As String
' 列名（物理）所在列数据
Dim physicsColumnNameValue As String
' Data属性所在列数据
Dim dateTypeValue As String
' 长度所在列数据
Dim lengthValue As String
' key所在列数据
Dim keyValue As String
' 是否可以为空数据
Dim notNullValue As String
' 默认值数据
Dim defaultValueValue As String

' 列定义开始行
Const startIndex = 9
Const endIndex = 2000

' 主键字符串
Dim keyColumn As String

' -------------------------------
'      制作创建表SQL入口方法
' -------------------------------
Sub makeCreateTableSql()

    ' 物理表名数据
    physicsTableNameRangeValue = Range(physicsTableNameRangeId).Value
    ' 表名称数据
    tableNameRangeValue = Range(tableNameRangeId).Value

    ' 初始化数组
    initAlldataArray ("-- Create table")

    createTableColumnsPartData
    createTablespacePartData
    createTableCommentsPartData
    createColumnsCommentsPartData
    createTableKeyPartData

    writeFile ("C:\")
    
End Sub
' 创建表定义部分数据
'create table TABLE_NAME
'(
'  COL1      VARCHAR2(10) not null,
'  COL2      DATE
')
Function createTableColumnsPartData()
    addLineData ("create table " & physicsTableNameRangeValue)
    addLineData ("(")
    ' 创建表列定义行数据
    makeTableColumnsDetail
    addLineData (")")
    addLineData ("")
End Function
' 创建表空间定义
'tablespace USERS
'  pctfree 10
'  initrans 1
'  maxtrans 255
'  storage
'  (
'    initial 64K
'    minextents 1
'    maxextents unlimited
'  );
Function createTablespacePartData()
    addLineData ("tablespace USERS")
    addLineData ("  pctfree 10")
    addLineData ("  initrans 1")
    addLineData ("  maxtrans 255")
    addLineData ("  storage")
    addLineData ("  (")
    addLineData ("    initial 64K")
    addLineData ("    minextents 1")
    addLineData ("    maxextents unlimited")
    addLineData ("  );")
    addLineData ("")
End Function
' 创建表注释
' -- Add comments to the table
' comment on table TABLE_NAME is '表名称';
Function createTableCommentsPartData()
    addLineData ("-- Add comments to the table")
    addLineData ("comment on table " & physicsTableNameRangeValue & " is '" & tableNameRangeValue & "';")
    addLineData ("")
End Function
' 创建表字段注释
' -- Add comments to the columns
' comment on column TABLE_NAME.COL1 is '字段1';
Function createColumnsCommentsPartData()
    Dim i As Integer
    For i = 0 To UBound(columnsCommentsArray)
        addLineData (columnsCommentsArray(i))
    Next i
End Function
' 创建主键
'-- Create/Recreate primary, unique and foreign key constraints
'alter table TABLE_NAME
'  add primary key (COL1, COL2)
'  using index
'  tablespace USERS
'  pctfree 10
'  initrans 2
'  maxtrans 255
'  storage
'  (
'    initial 64K
'    minextents 1
'    maxextents unlimited
'  );
Function createTableKeyPartData()
    If keyColumn <> "" Then
        ' 去掉主键字符串最后的“,”
        keyColumn = Left(keyColumn, Len(keyColumn) - 1)

        addLineData ("-- Create/Recreate primary, unique and foreign key constraints")
        addLineData ("alter table " & physicsTableNameRangeValue)
        addLineData ("  add primary key (" & keyColumn & ")")
        addLineData ("  using index")
        addLineData ("  tablespace USERS")
        addLineData ("  pctfree 10")