vb调用oracle存储过程

[hongqi162]

包体

create or replace package sp_TJ
is
type resultData is ref cursor;
procedure GetGSDZJCTJ(rst out resultData,bDate in wzxtb.zxrq%type,eDate in wzxtb.zxrq%type,flag number,ACount out number,AMoney out number);
end sp_TJ;

调用
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnDb
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_TJ.GetGSDZJCTJ"

cmd.Parameters.Append cmd.CreateParameter("rst", '这里怎么处理？', adParamOutput)
cmd.Parameters.Append cmd.CreateParameter("bDate", adVarChar, adParamInput, 30, "20010101 00:00:00")
cmd.Parameters.Append cmd.CreateParameter("eDate", adVarChar, adParamInput, 30, "20010101 00:00:00")
cmd.Parameters.Append cmd.CreateParameter("flag", adNumeric, adParamInput, 4, 0)
cmd.Parameters.Append cmd.CreateParameter("ACount", adNumeric, adParamOutput, 4, 0)
cmd.Parameters.Append cmd.CreateParameter("AMoney", adNumeric, adParamOutput, 4, 0)

Set Rst = cmd.Execute

[hongqi162]

怎么没有人帮忙啊

rst为结果集在C#中可以这样写cmd.Parameters.Add("rst",OracleType.Cursor);

在vb中应该怎样写？
cmd.Parameters.Append cmd.CreateParameter("rst", '这里怎么处理？', adParamOutput)

[wdsimon]

顶

[chang1216]

帮你顶啊

[jobs002]

关注.....

[Snoworld]

顶

[waterfirer]

Dim rst As New ADODB.Recordset



rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
Set rst.Source = cmd
rst.Open
.......yourcode
rst.Close

[fanhaili]

友情UP

[开发者开聊]

http://www.vbcity.com/page.asp?p=chapter_2&f=books-wrox-vbora

http://www.jlcomp.demon.co.uk/faq/resultsets.html

[开发者开聊]

Returning PL/SQL Tables to VB

There is a great difference between the way we handle the PL/SQL table in ADO and OO4O. In ADO we use a Command object and a Recordset. With OO4O we use an OraSQLStmt object and a parameter array.

The ADO Way

Up till now Oracle and ADO have sometimes not been the best of partners. The method that we are forced to use here is rather inelegant.

The release of ADO 2.5 will improve matters immeasurably, as you will see in a little while.

Because we deal with Oracle we are going to have to make the call to the stored procedure as a SQL Passthrough and not as a stored procedure (adcmdStoredProc). Even this can only be achieved if you are using ODBC. Neither OLE DB ?Oracle's or Microsoft's - allows us to use the PL/SQL table the way I describe it here. Here is the full code that will allow you to test this method:

Option Explicit
Private mCnn As ADODB.Connection
Private mrsSelect As ADODB.Recordset
Private mCmd As ADODB.Command
Private msSelect As String
Private mCmdPrmGender As New ADODB.Parameter

Private Sub Form_Load()

Dim sConnect As String 'Declare our connection string
sConnect = "Data Source=" & ";" & _
"User ID=scott" & ";" & _
"Password=tiger" & ";"

Set mCnn = New ADODB.Connection
With mCnn
.CommandTimeout = 10
.CursorLocation = adUseClient
.Provider = "MSDAORA"
.Open sConnect

End With


'resultset is a keyword, fname the name of the table in the proc.

msSelect = "{call human_resources.first_names(?, " & _
"{resultset 1000, fname})}"

Set mCmd = New ADODB.Command

With mCmd
.CommandText = msSelect
.CommandType = adCmdText
.ActiveConnection = mCnn

Set mCmdPrmGender = .CreateParameter("pGender", adVarChar, _
adParamInput, 1, "M")
.Parameters.Append mCmdPrmGender
End With

Set mrsSelect = New ADODB.Recordset

mCmdPrmGender = "M"
Set mrsSelect = mCmd.Execute
MsgBox mrsSelect.Fields(0)
End Sub


Let's look at some sections the code in more detail. We have to define a Command object, a SQL string, a Recordset, a Parameter object and, of course, our Connection object. The result of the call will come back in the Recordset and we'll then span it to get the names:

Private mCnn As ADODB.Connection

Private mrsSelect As ADODB.Recordset

Private mCmd As ADODB.Command

Private msSelect As String

Private mCmdPrmGender As New ADODB.Parameter

We must call our stored procedure using placeholder syntax and the call statement. The general form is:

{call PackageName.ProcedureName(?, {resultset <NumberOfRows>, Table1, Table2 )})}

This will allow us to combine PL/SQL tables into a single Recordset. You may add as many parameters or tables as you need.

Remember because the table is an output parameter, you may not be able to use more than 2 in early versions of Oracle. This limitation does not exist with OO4O.

In our case:

msSelect = "{call human_resources.first_names(?, {resultset 1000, fname})}"

We want to get the whole PL/SQL table back into the mrsSelect Recordset (result set). The pair (in which the comma seems to be missing and is not) mrsSelect 1000 contains an arbitrary large number.

The way the msSelect is coded with the parentheses is the syntax used for SQL Passthrough in ODBC. This limits the approach to using the ODBC provider. As we have said many times before, the ODBC provider is the most stable and the most able, but it is also the slowest. Here we did not have a choice. In ADO 2.5 the story is different, as you will see when we discuss REF CURSORS further along this chapter.

We now set the Command object:

Set mCmd = New ADODB.Command
With mCmd
.CommandText = msSelect
.CommandType = adCmdText
.ActiveConnection = mCnn

Set mCmdPrmGender = .CreateParameter("pGender", adVarChar, _
adParamInput, 1, "M")
.Parameters.Append mCmdPrmGender
End With


And create the Recordset, set the parameter value, assign the Recordset to the return value from the execution of the Command object. We now have the first names of men in the recordset and can have a look at the first field value:

Set mrsSelect = New ADODB.Recordset
mCmdPrmGender = "M"
Set mrsSelect = mCmd.Execute
MsgBox mrsSelect.Fields(0)
End Sub


The 0040 Way

We are going to create an OraSQLStmt object, a parameter, and a parameter array in the database object. We are also going to create a statement for calling a stored procedure. When the call is done, we'll have the first 100 names in the parameter array. Here is the full code, fully commented:

Private mOraSession As OraSession
Private mOraDatabase As OraDatabase
Private msSelect As String
Private mMyQuery As OraSqlStmt


Private Sub Form_Load()

Set mOraSession = CreateObject("OracleInProcServer." & _
"XOraSession")

' Open the databae. Option 1 is ORA_DEFAULT
Set mOraDatabase = mOraSession.OpenDatabase("", _
"scott/tiger", 1)

' Define the INPUT parameter and add it to the collecttion.
mOraDatabase.Parameters.Add "pGender", "F", 1

' Define the OUTPUT parameter array(defined as type
' VARCHAR2 by third argument) and addit to the collection.
mOraDatabase.Parameters.AddTable "pfnames", 2, 1, 100, 15

' make the SQL for calling the procedure.
msSelect = "Begin Human_resources.first_names" & _
"(:pGender, :pfnames); END;"

' Execute the SQL statement and create an OraSqlStmt object
' from the specified SQL statement and options. Option 0 is
' ORASQL_NO_AUTOBIND

Set mMyQuery = mOraDatabase.CreateSql(msSelect, 0)

' Set the parameter value and refresh
mOraDatabase.Parameters("pGender").Value = "F"
mMyQuery.Refresh

' Look at the first record.
MsgBox mOraDatabase.Parameters("pfnames").get_Value(0)
End Sub

[开发者开聊]

Using REF CURSORS in ADO

With ADO and OLEDB, Microsoft is making it easier for more developers to connect to Oracle databases, and do almost any common database task. There has been one feature however which has been glaringly absent. This is the ability to return a full-fledged recordset from an Oracle stored procedure's REF CURSOR parameter. With the upcoming version of ADO 2.5 and the Microsoft OLEDB provider for Oracle, this drastic limitation will finally be lifted.

Another set of third party ODBC and OLE DB drivers are from Merant (Formerly Intersolv and MicroFocus) that also allow us to do this. For further information refer to the Merant web site (www.Merant.com).

It is possible with the 2.5 (Beta) version of ADO to return recordsets from Oracle stored procedures, thereby eliminating almost any need for SQL code in your Visual Basic programs. With this functionality, it is now possible to almost entirely separate your database code from you VB code.

To use this functionality, we have created a Personality package to house the definition of the cursor that will be returned, as well as the stored procedure itself. Now we are going to look at the VB code that calls our stored procedure.

In VB we declare the objects, set their properties and execute the command.

Declaration:

Private sSQL As String

Private mrsGetNames As ADODB.Recordset

Private mStoredProcCmd As ADODB.Command

Private mStoredProcPrmGender As ADODB.Parameter

Creation:

sSQL = "Personality.GetNames"
Set mStoredProcCmd = New ADODB.Command
With mStoredProcCmd
.ActiveConnection = mADOConnection
.CommandType = adCmdStoredProc
.CommandText = sSQL 'the procedure name goes here.

Set mStoredProcPrmGender = .CreateParameter("pGender", _
adVarChar, adParamInput, 1)

.Parameters.Append mStoredProcPrmGender
End With


Note that we can now set the CommandType property to adCmdStoredProc to indicate that we are calling a stored procedure. Since the stored procedure is in a package we specify the name as Personality.GetNames. When a stored procedure is in a package the MS OLEDB driver will not automatically determine the parameters for the stored procedure. We have to manually append them. Here is an important point. Our stored procedure had two parameters: pGender and PeopleCursor. Note that we do not define a parameter for the ref cursor.

Finally, we assign our recordset to the return value from the execution of our command. The OLEDB driver will automatically intercept our IN OUT ref cursor parameter and assign it to be the return from the excution:

mStoredProcPrmGender.Value = "M" 'set the input value

Set mrsGetNames = mStoredProcCmd.Execute 'execute

Voila! Remember, that you will have to wait for ADO 2.5 to be able to do this!

The time has come to do the same thing with 0040.

Using a REF CURSOR in OO4O

Again, In VB we declare the necessary objects, create them, set their parameters and execute them. ADO (2.5) gets the ref cursor records (rows) into a Recordset. OO4O gets them into a dynaset. This, however, is a special kind of a dynaset. It is read-only and is created using the CreatePLSQLdynaset method, rather than the CreateDynaset method of the OraDatabase.

The CreatePLSQLDynaset Method

You have already used the CreateDynaset method of OraDatabase to create dynasets. Creating the cursor based dynaset is every bit as simple. Here it is:

Set mOraDynaset = mOraDatabase.CreatePLSQLDynaset (SQL, CursorName, Options)

The method has three arguments (or parameters):

SQL ?The SQL statement must be a PL/SQL stored procedure with BEGIN and END around the call.
CursorName should exactly match the cursor created inside the stored procedure
Options ?A long integer that assumes any combination (logical sum) of the following:
Here is a table of valid Option values:

Value Constant Description
0
ORADYN_DEFAULT
In a read-only dynaset it means only that you get an Automatic MoveFirst (the dynaset when refreshed will already be at the first row), that blanks are stripped from the tail of a string, and that the dynaset caches as much as it can in the client memory.

2
ORADYN_NO_BLANKSTRIP
The dynaset normally strips trailing blanks from character fields. This option leaves the trailing blanks in.

8
ORADYN_NOCACHE
With this option, because only one record is held in memory, you can do only forward movement (MoveNext, but no MovePrevious), but you get faster results. (*)

64
ORADYN_NO_MOVEFIRST
The dynaset is unpopulated. You have to do a MoveFirst to fill it.



(*) You get faster results most of the time. This is because we (the human user) usually want just one record at a time and takes his time before requesting the next record. On the other hand, if we want to do a batch process, this will make too many round trips. In such a case it is best to use a custom dynaset (see Chapter 8) and cache as many records as we can in one round trip.

Armed with this knowledge let us proceed.

Declarations:

Private mDynGetNames As OraDynaset

Private sSQL As String

Establish our connection

Private Sub Form_Load()
Set mOraSession = CreateObject("OracleInProcServer." & _
"XOraSession")
Set mOraDatabase = mOraSession.DbOpenDatabase("", _
"scott/tiger", 1)


Note the difference between this sSQL and the simpler ADO string.

' make the SQL for calling the procedure.
sSQL = "Begin Personality.GetNames (:pGender," & _
":PeopleCursor); end;"


Note the exact spelling of PeopleCursor to match the name in the stored procedure.

We have to declare the parameters before we can create the dynaset. As was the case with ADO, we do not declare a parameter for the cursor. This was taken care of by the correct spelling of the cursor name.

mOraDatabase.Parameters.Add "pGender", "M", 1
Set mDynGetNames = mOraDatabase.CreatePlsqlDynaset(sSQL, _
"PeopleCursor", ORADYN_DEFAULT)


Now execute by calling the Refresh method and have a look at the first field value:

mDynGetNames.Refresh
MsgBox mDynGetNames.Fields(0)


Change the gender to F

mOraDatabase.Parameters("pGender").Value = "F"


And refresh again to get the ladies (I wish it were that simple!)

mdynGetNames.Refresh
MsgBox mDynGetNames.Fields(0)
End Sub


That's all folks!

[skyinfo]

關注

[hongqi162]

问题解决了
1 数据引擎
不要使用Provider=OraOledb.Oracle.1
使用Provider=MSDAORA.1
2 注意存储过程中参数的位置 结果集放到最后
--package
create or replace package testAAA
is
type resultData is ref cursor;
procedure test1(cname in varchar2,cdept in out varchar2,cid out resultData);

end;

--package body
create or replace package body testAAA
is
procedure test1(cname in varchar2,cdept in out varchar2,cid out resultData)
is
begin
open cid for
select * from wzbm;
cdept:='111';
end;
end;

vb 调用
'cnDb.ConnectionString = "Provider=MSDAORA.1;。。。。。。。。。。。。"
cnDb.Open

cmd.ActiveConnection = cnDb
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "testAAA.test1"
cmd.Parameters.Append cmd.CreateParameter("cname", adLongVarChar, adParamInput, 1000, "23")
cmd.Parameters.Append cmd.CreateParameter("cdept", adLongVarChar, adParamInputOutput, 1000, "23")

Set rst = cmd.Execute(Null, Null, adCmdStoredProc)

If rst.RecordCount > 0 Then
MsgBox rst.RecordCount
MsgBox cmd.Parameters("cdept").Value
End If



非常感谢参与的朋友