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取值，添加记录

  1 dim adoConnection '库连接对象
  2 dim     sequenceName:sequenceName= "YEWID_SEQ"
  3 dim     tableName:tableName = "table_yewid"
  4 Sub Onclick()
  5 '1:开库
  6     inidatabase adoConnection
  7 '2:获取序列nextval值
  8     getSequence adoConnection,sequenceName,maxindex
  9 '3:获取计算机信息
 10     computer = GetComputer()
 11     computerip =GetIP()
 12     computermac=GetMAC()
 13     getSysDate  adoConnection,sysYear,sysDate
 14 '4：table_yewid表中增加记录
 15     sql = "select currentyear,index_value,申请人,计算机,计算机IP,计算机MAC,申请时间 from "&tableName
 16     addNewRecord sql,sysYear,sysDate,maxindex,UserName,computer,computerip,computermac
 17 '5：关库
 18     releasedatabase adoConnection
 19 End Sub
 20 
 21 '开库
 22 Function inidatabase(byref adoConnection)
 23         Set adoConnection = CreateObject("ADODB.Connection")
 24       adoConnection.connectionstring="Driver={Microsoft ODBC for Oracle};Server=orcl;Uid=test;Pwd=test" 
 25         adoConnection.Open
 26 End Function
 27 
 28 '关库
 29 Function releasedatabase(byval adoConnection)
 30         adoConnection.Close  
 31       Set adoConnection=Nothing
 32 End Function
 33 '获取oracle系统时间
 34 function getSysDate(byval objconn,byref sysYear,byref sysDate)
 35         seqsql = "select to_char(sysdate,'yyyy'), to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual"
 36         Set adoRs = CreateObject("ADODB.RECORDSET")  
 37         adoRs.open seqsql, objconn, 3, 1
 38         sysYear = adoRs(0)
 39         sysDate = adoRs(1)
 40         adoRs.close
 41         set adoRs = nothing 
 42 end function 
 43 '获取序列
 44 function getSequence(byval objconn,byval seqname,byref maxIndex)
 45         seqsql = "select "&seqname&".nextval from dual"
 46         Set adoRs = CreateObject("ADODB.RECORDSET")  
 47         adoRs.open seqsql, objconn, 3, 1
 48         maxIndex = adoRs(0)
 49         adoRs.close
 50         set adoRs = nothing 
 51 end function 
 52 '新建记录
 53 function addNewRecord(byval sql,byval currentyear,byval currentDate,byval indexvalue,byval shenqr,byval computer,byval computerip,byval computermac)
 54         Set adoRs=CreateObject("ADODB.recordset")
 55         adoRs.Open  sql,adoConnection,3,2
 56         adoRs.AddNew
 57         adoRs("currentyear")=currentyear
 58         adoRs("index_value")=indexvalue
 59         adoRs("申请人")=shenqr
 60         adoRs("计算机")=computer
 61         adoRs("计算机IP")=computerip
 62         adoRs("计算机MAC")=computermac
 63         adoRs("申请时间")=currentDate'now()
 64         adoRs.Update
 65         adoRs.MoveNext
 66         adoRs.Close
 67         set adoRs = nothing 
 68 end function 
 69   '获取本机计算机名
 70 Function GetComputer()
 71     strComputer = "."
 72     Set objWMIService = GetObject("winmgmts:\" & strComputer & "
ootcimv2")
 73     Set colComputers = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
 74     For Each objComputer in colComputers
 75         GetComputer = objComputer.Name
 76     Exit For
 77     Next
 78 end function 
 79   '获取本机MAC
 80 Function GetMAC()
 81     GetMAC = ""
 82     Dim mc,mo
 83     Set mc = GetObject("Winmgmts:").InstancesOf("Win32_NetworkAdapterConfiguration")
 84     For Each mo In mc
 85         If mo.IPEnabled = True Then 
 86             GetMAC = mo.MacAddress
 87             Exit For
 88         End If
 89     Next
 90     Set mc = nothing
 91 End Function
 92 
 93   '获取本机IP
 94 Function GetIP()
 95    ComputerName="."
 96     Dim objWMIService,colItems,objItem,objAddress
 97     Set objWMIService = GetObject("winmgmts:\" & ComputerName & "
ootcimv2")
 98     Set colItems = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
 99     For Each objItem in colItems
100         For Each objAddress in objItem.IPAddress
101             If objAddress <> "" then
102                 GetIP = objAddress
103                 Exit Function
104             End If
105         Next
106     Next
107 End Function