客户端利用存储过程获取Oracle数据库的序列值

时　间:2022-10-25 22:21:22
作　者:AngelHis   ID：20576  城市：沈阳
摘　要:通常在access客户端是无法直接获取oracle数据库的序列，用Ado运行“SELECT SQ_REG_MZ.NEXTVAL INTO SQ_NO FROM DUAL; ”时会无法执行。解决方法：可以通过调用数据库存储过程的方法间接获取。
正　文:

1.数据库创建序列

-- Create sequence 

create sequence SQ_PATIENT_NO_MZ
minvalue 1
maxvalue 999999999999999999999999999
start with 1001
increment by 1
nocache;

2.数据库创建存储过程

Create or REPLACE PROCEDURE PRC_REG_MZ_NO(SQ_PATIENT_NO IN OUT NUMBER) IS
BEGIN 
  Select SQ_PATIENT_NO_MZ.NEXTVAL INTO SQ_PATIENT_NO FROM DUAL; 
END PRC_REG_MZ_NO;

3. 客户端创建调用存储过程的函数

Public Function orcProc(ProcName As String, Optional strPara As Variant, Optional WARN As Integer, Optional Conn As ADODB.Connection) As Integer
'******************
'通过数组参数调用存储过程
'参数 storProcName 为存储过程名
'参数 strPara() 使用数组作为存储过程参数
'******************
    On Error GoTo ERR
    Dim cmd As New ADODB.Command
    Dim ConnStr As String
    Dim I As Integer
    '    ConnStr = "DRIVER={Microsoft ODBC for oracle};UID=*****;PWD=*****;SERVER=*****;"
    '    conn.ConnectionString = ConnStr
    '    conn.Open
    DoCmd.Hourglass True
    If Conn Is Nothing Then Set Conn = orC_ADOConn()
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = ProcName
    If Not IsMissing(strPara) Then
        If UBound(strPara) >= 0 Then
            For I = 0 To UBound(strPara)
                cmd.Parameters(I).Value = strPara(I)
            Next
        End If
    End If
    cmd.Execute
    If Not IsMissing(strPara) Then
        If UBound(strPara) >= 0 Then
            For I = 0 To UBound(strPara)
                strPara(I) = cmd.Parameters(I).Value
            Next
        End If
    End If
    Set cmd = Nothing
    orcProc = 1
    DoCmd.Hourglass False
    Exit Function
ERR:
    DoCmd.Hourglass False
    Set cmd = Nothing
    orcProc = 0
    If WARN = 1 Then MsgBox "执行错误【" & ProcName & "】——" & ERR.Description, vbInformation, "系统提示"
End Function

4.测试

Function F_GET_PATIENT_NO_MZ_TEST() As Long
    Dim P() As Variant, I As Integer, L As Long
    P = Array(L)
    Debug.Print Now()
    I = orcProc("PRC_REG_MZ_NO", P, 1)
    Debug.Print Now()
    If I = 1 Then L = P(0)
    F_GET_PATIENT_NO_MZ_TEST = L
    Debug.Print L
End Function