为什么Oracle的Stored Procedure（存储过程）不能返回结果集？

[trybird]

难道只能返回其INput/OUTput参数和出错码，而不能返回结果集吗？
难道只有SQL查询才能返回结果集吗？
难道真的没有办法取到Oracle的Stored Procedure（存储过程）的结果集吗？

若有办法，请举例说明！谢谢！

[trybird]

SQL Server的Stored Procedure（存储过程）可以在客户端得到结果集
而Oracle的Stored Procedure（存储过程）不能在客户端得到查询的结果集
难道Oracle比SQL Server还要差，还要低能不成？

[Jneu]

gz

[xzou]

可以返回结果集游标，根据游标再取吧

[trybird]

To xzou(缺齿小狼)
只能返回结果集游标吗？这也太麻烦了！

[KingSunSha]

哈哈,别集别急,看完这个再说吧
http://www.csdn.net/expert/topic/274/274843.shtm

[xzou]

oracle里也有数组和集合，不过不知道怎么和高级语言对应，而且用数组会加大编写存储过程的难度，

[trybird]

http://www.vbip.com/books/1861001789/chapter_1789_10.asp

Sample chapter from Visual Basic Oracle 8 Programmer's Reference

PL/SQL, Stored Procedures and Triggers


--------------------------------------------------------------------------------



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 , 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

Returning Recordsets from Oracle Stored Procedures

The COBOL-like cursor that we have described before, the one that mimics a sequential flat file is also known as a REF CURSOR. Oracle made the declaration and deployment of ref cursors relatively easy. Again, the best way to learn is doing. Here is a ref cursor way of reading the first and last names of the X (X = F or X = M) gender in the PEOPLE table. Don't tell me that a simple SELECT statement and a simple dynaset can do the trick, because it can. But then there are cases in which the cursor has already been built for our COBOL programmers and we want to use it. This is especially true if the SELECT statement that defines the cursor is very convoluted and we don't want to repeat it. The stored procedure that contains the cursor is also precompiled so that we may get the results faster. Be it what it may, here is a new package (we could also have added this procedure to our old package) that contains a ref cursor.

CREATE OR REPLACE PACKAGE Personality
IS
cursor c1 IS
SELECT First_Name, Last_Name FROM People;
TYPE PeopleCur IS REF CURSOR RETURN c1%rowtype;
PROCEDURE GetNames
(
pGender IN VARCHAR2,
PeopleCursor in out PeopleCur
);
END Personality;

In the package header we declare a TYPE (PeopleCur), which is a REF CURSOR - a reference or pointer to a cursor. Here PeopleCur is declared as:

C1%rowtype

which simply states that it has the same field structure as the cursor c1, which itself is declared as a SELECT statement. PeopleCur is going to define an actual ref cursor called PeopleCursor. The definition is implicit and happens when we declare the PeopleCursor as a parameter of a stored procedure. Parameters in stored procedures are typed and thus defined. This is similar to what happens in ADA. We also declare PeopleCur as a ref cursor type.

We then declare a Stored Procedure within the package, which has two parameters, the first of the two is fairly straightforward - pGender is an input parameter of type VARCHAR2. The second parameter (PeopleCursor) to this procedure is an IN OUT parameter, declared as type PeopleCur, which is the TYPE we have just defined. This tells us that the third parameter will be returning a REF CURSOR with structure of c1%rowtype.

Now let's look at how we generate the package body, where the work of the procedure actually takes place. Note that there is no reference in this code to the cursor definition, as it only needs be defined once. All there is in the body is the PL/SQL code for the procedure.

CREATE OR REPLACE PACKAGE BODY Personality
AS
PROCEDURE GetNames
(
pGender IN VARCHAR2,
PeopleCursor in out Peoplecur
)
IS
begin
OPEN PeopleCursor FOR
SELECT First_Name, Last_Name FROM People WHERE gender = pGender;

END GetNames;
END Personality;

In the package body we actually build the stored procedure. The procedure takes in a pGender parameter. It then opens the PeopleCursor cursor and uses this parameter (specified in our WHERE clause) to get a cursor containing all the records in the People table that have field values corresponding to the value we give pGender.

That's it. Again you may do the work in the Navigator or use the CREATE OR REPLACE approach in SQL*PLUS. Here is the latter:



When we get ADO 2.5 we'll be able to address the ref cursor directly as a recordset. This will eliminate the need to use the PL/SQL table mechanism that we have described before. OO4O already has this capability and the table example only served us to learn another way to achieve the same end. Given a choice, the ref cursor approach is the one I prefer.

[trybird]

先谢谢KingSunSha(弱水三千)！
我现在在忙着出文档，等一会试一试
也谢谢xzou(缺齿小狼)！好象弱水三千提供的那个例子也确实是用Cursor的。

真是高手多啊！小的这里给您作揖了！

[trybird]

好啊，还有没有人有其他高见？
还有没有人要发言啊？

我可要买单了！

[whatapig]

各位，按照例子中说的是不是就能取到结果集了？
我也在解决同样的问题，帮我看看http://www.csdn.net/expert/topicview1.asp.谢谢。