如何使用ADO访问Oracle数据库存储过程
2007-05-13 12:30:37 来源:WEB开发网核心提示: CREATE OR REPLACE PACKAGE BODY packperson AS PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELEC
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/
4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。
5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。
6. 在代码窗口中添加以下代码:
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ",
" & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
End Sub
Private Sub cmdGetOne_Click()
Set Rs.Source = CPw2
inputssn = InputBox(
"Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Rs.Open
MsgBox "Person data: " & Rs(0) & "
, " & Rs(1) & ", " & Rs(2)
Rs.Close
End Sub
Private Sub Form_Load()
'使用合适的值代替以下用户ID,
口令(PWD)和服务器名称(SERVER)
Conn = "UID=*****;PWD=*****;driver=" _
& "{Microsoft ODBC for
Oracle};SERVER=dseOracle;"
Set Cn = New ADODB.Connection
'创建Connection对象
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
QSQL = "{call packperson.allperson(
{resultset 9,ssn,fname,"_
& "lname})}"
Set CPw1 = New ADODB.Command
'创建Command对象
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
QSQL ="{call packperson.oneperson(?,
{resultset 2,ssn, "_
& " fname,lname})}"
'调用存储过程
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append.CreateParameter(
,adInteger, _
adParamInput)
'添加存储过程参数
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = Nothing
End Sub
7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程。
更多精彩
赞助商链接