vbscript 语言通过序列和ADODB实现取号不重复

目的：通过VBScript脚本利用序列的性质，实现取号不重复

首先，表空间中创建表名为TABLE_YEWID的表格，主要有以下几个字段

-- Create table
create table TABLE_YEWID
(
  currentyear VARCHAR2(50),
  index_value VARCHAR2(50),
  申请人         NVARCHAR2(50),
  计算机         NVARCHAR2(50),
  计算机mac      NVARCHAR2(50),
  申请时间        DATE,
  计算机ip       NVARCHAR2(50)
)
tablespace FSYWK
  pctfree 10
  initrans 4
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

其次，创建序列，序列名为YEWID_SEQ

-- Create sequence 
create sequence YEWID_SEQ
minvalue 1nomaxvalue 
start with 1--从1开始
increment by 1--自增步距为1
nocache--无缓存，避免跳号
nocycle;--不循环

最后通过查询YEWID_SEQ.NEXTVAL取值，添加记录

dim adoConnection '库连接对象
dim     sequenceName:sequenceName= "YEWID_SEQ"
dim     tableName:tableName = "table_yewid"
Sub Onclick()
'1:开库
    inidatabase adoConnection
'2:获取序列nextval值
    getSequence adoConnection,sequenceName,maxindex
'3:获取计算机信息
    computer = GetComputer()
    computerip =GetIP()
    computermac=GetMAC()
    getSysDate  adoConnection,sysYear,sysDate
'4：table_yewid表中增加记录
    sql = "select currentyear,index_value,申请人,计算机,计算机IP,计算机MAC,申请时间 from "&tableName
    addNewRecord sql,sysYear,sysDate,maxindex,UserName,computer,computerip,computermac
'5：关库
    releasedatabase adoConnection
End Sub

'开库
Function inidatabase(byref adoConnection)
        Set adoConnection = CreateObject("ADODB.Connection")
      adoConnection.connectionstring="Driver={Microsoft ODBC for Oracle};Server=orcl;Uid=test;Pwd=test" 
        adoConnection.Open
End Function

'关库
Function releasedatabase(byval adoConnection)
        adoConnection.Close  
      Set adoConnection=Nothing
End Function
'获取oracle系统时间
function getSysDate(byval objconn,byref sysYear,byref sysDate)
        seqsql = "select to_char(sysdate,'yyyy'), to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual"
        Set adoRs = CreateObject("ADODB.RECORDSET")  
        adoRs.open seqsql, objconn, 3, 1
        sysYear = adoRs(0)
        sysDate = adoRs(1)
        adoRs.close
        set adoRs = nothing 
end function 
'获取序列
function getSequence(byval objconn,byval seqname,byref maxIndex)
        seqsql = "select "&seqname&".nextval from dual"
        Set adoRs = CreateObject("ADODB.RECORDSET")  
        adoRs.open seqsql, objconn, 3, 1
        maxIndex = adoRs(0)
        adoRs.close
        set adoRs = nothing 
end function 
'新建记录
function addNewRecord(byval sql,byval currentyear,byval currentDate,byval indexvalue,byval shenqr,byval computer,byval computerip,byval computermac)
        Set adoRs=CreateObject("ADODB.recordset")
        adoRs.Open  sql,adoConnection,3,2
        adoRs.AddNew
        adoRs("currentyear")=currentyear
        adoRs("index_value")=indexvalue
        adoRs("申请人")=shenqr
        adoRs("计算机")=computer
        adoRs("计算机IP")=computerip
        adoRs("计算机MAC")=computermac
        adoRs("申请时间")=currentDate'now()
        adoRs.Update
        adoRs.MoveNext
        adoRs.Close
        set adoRs = nothing 
end function 
  '获取本机计算机名
Function GetComputer()
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:\" & strComputer & "
ootcimv2")
    Set colComputers = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
    For Each objComputer in colComputers
        GetComputer = objComputer.Name
    Exit For
    Next
end function 
  '获取本机MAC
Function GetMAC()
    GetMAC = ""
    Dim mc,mo
    Set mc = GetObject("Winmgmts:").InstancesOf("Win32_NetworkAdapterConfiguration")
    For Each mo In mc
        If mo.IPEnabled = True Then 
            GetMAC = mo.MacAddress
            Exit For
        End If
    Next
    Set mc = nothing
End Function

  '获取本机IP
Function GetIP()
   ComputerName="."
    Dim objWMIService,colItems,objItem,objAddress
    Set objWMIService = GetObject("winmgmts:\" & ComputerName & "
ootcimv2")
    Set colItems = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each objItem in colItems
        For Each objAddress in objItem.IPAddress
            If objAddress <> "" then
                GetIP = objAddress
                Exit Function
            End If
        Next
    Next
End Function