该线程包含一些示例代码,这些示例代码显示了从VB执行Oracle存储过程和函数的方法。
希望用户发现它们有用。
仅以IN类型作为参数模式的Oracle Procedure。
========================================
CREATE OR REPLACE PROCEDURE DEPTINS
(
DNO DEPT.DEPTNO%TYPE,
DN DEPT.DNAME%TYPE,
LC DEPT.LOC%TYPE
)
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO DEPT VALUES(DNO,DN,LC);
DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20002,'DUPLICATE VALUE......!');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SOME OTHER ERROR ......!');
END;
从Vb调用上述Oracle过程。
================================
'general declaration
Dim CON As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim PR As New ADODB.Parameter
Dim PR1 As New ADODB.Parameter
Dim PR2 As New ADODB.Parameter
Dim CMD As New ADODB.Command
Private Sub Command1_Click()
On Error GoTo MYERR
CON.Open "Provider=MSDAORA.1;Password=DEBASIS;User ID=DEBASIS;Data Source=DAS;Persist Security Info=True"
CMD.ActiveConnection = CON
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "DEPTINS"
Set PR = CMD.CreateParameter("DNO", adNumeric, adParamInput, 2, Val(T1.Text))
CMD.Parameters.Append PR
Set PR1 = CMD.CreateParameter("DN", adVarChar, adParamInput, 14, Trim(T2.Text))
CMD.Parameters.Append PR1
Set PR2 = CMD.CreateParameter("LC", adVarChar, adParamInput, 13, Trim(T3.Text))
CMD.Parameters.Append PR2
CMD.Execute
CON.Close
Exit Sub
MYERR:
I = Val(Mid(Err.Description, 4, 6))
If I = -20002 Then
MsgBox "DUPLICATE ENTRY"
ElseIf I = -20001 Then
MsgBox "SOME OTHER ERROR"
End If
End Sub
还要检查
具有IN和OUT类型参数的存储过程。