转自：http://feier216.blog.163.com/blog/static/204878151201291202939326/
近期项目需要使用oracle存储过程，受SQL Server的影响，总也搞不定返回结果集的存储过程，查了很多资料，最终解决了这个头疼的问题，现总结如下：
1.创建存储过程的语句如下：
CREATE OR REPLACE
PROCEDURE GetEmpRS1 (p_recordset1 OUT SYS_REFCURSOR,
p_recordset2 OUT SYS_REFCURSOR,
PARAM IN STRING) AS
BEGIN
OPEN p_recordset1 FOR
SELECT RET1
FROM MYTABLE
WHERE LOOKUPVALUE > PARAM;
OPEN p_recordset2 FOR
SELECT RET2
FROM MYTABLE
WHERE LOOKUPVALUE >= PARAM;
END GetEmpRS1;
注意，在返回结果集上oracle和sql server有很大不同，sql server默认返回最后一个查询语句，而oracle不同，必须将查询放入游标，以参数的形式，传出去，在这里，使用ADO时，应了解一点：
在OLE DB的标准中，没有REF CURSOR类型的预定义数据类型，因此在调用存储过程时，不能创建REF CURSOR类型的参数。在ADO调用返回记录集的存储过程时，OLE DB自动为存储过程中REF CURSOR类型的传出参数返回记录集，该记录集可以赋值给一个Recordset对象。如果PL/SQL存储过程有一个或多个REF CURSOR类型的传出参数，OLE DB将这些参数的记录集集成在一起，并通过第一个REF CURSOR类型的传出参数返回，这些记录集同样可以赋值给一个Recordset对象。
2.C++代码如下：
_ConnectionPtr m_pConn;
_RecordsetPtr pRecordset;
_CommandPtr pCommand;
_ParameterPtr pParam1;
//We will use pParam1 for the sole input parameter.
//NOTE: We must not append (hence need not create)
//the REF CURSOR parameters. If your stored proc has
//normal OUT parameters that are not REF CURSORS, you need
//to create and append them too. But not the REF CURSOR ones!
//Hardcoding the value of i/p paramter in this example...
_variant_t vt;
vt.SetString("2");
m_pConn.CreateInstance (__uuidof (Connection));
pCommand.CreateInstance (__uuidof (Command));
//NOTE the "PLSQLRSet=1" part in
//the connection string. You can either
//do that or can set the property separately using
//pCommand->Properties->GetItem("PLSQLRSet")->Value = true;
//But beware if you are not working with ORACLE, trying to GetItem()
//a property that does not exist
//will throw the adErrItemNotFound exception.
m_pConn->Open (
_bstr_t ("Provider=OraOLEDB.Oracle;PLSQLRSet=1;Data Source=XXX"),
_bstr_t ("CP"), _bstr_t ("CP"), adModeUnknown);
pCommand->ActiveConnection = m_pConn;
pParam1 = pCommand->CreateParameter( _bstr_t ("pParam1"),
adSmallInt,adParamInput, sizeof(int),( VARIANT ) vt);
pCommand->Parameters->Append(pParam1);
pRecordset.CreateInstance (__uuidof (Recordset));
//NOTE: We need to specify the stored procedure name as COMMANDTEXT
//with proper ODBC escape sequence.
//If we assign COMMANDTYPE to adCmdStoredProc and COMMANDTEXT
//to stored procedure name, it will not work in this case.
//NOTE that in the escape sequence, the number '?'-s correspond to the
//number of parameters that are NOT REF CURSORS.
//注意此处调用， GetEmpRS1有三个参数，但是前两个Refcursor参数会被OLE DB自动放入结果集中，不用用户设置参数
pCommand->CommandText = "{CALL GetEmpRS1(?)}";
//NOTE the options set for Execute. It did not work with most other
//combinations. Note that we are using a _RecordsetPtr object
//to trap the return value of Execute call. That single _RecordsetPtr
//object will contain ALL the REF CURSOR outputs as adjacent recordsets.
pRecordset = pCommand->Execute(NULL, NULL,
adCmdStoredProc | adCmdUnspecified );
//After this, traverse the pRecordset object to retrieve all
//the adjacent recordsets. They will be in the order of the
//REF CURSOR parameters of the stored procedure. In this example,
//there will be 2 recordsets, as there were 2 REF CURSOR OUT params.
while( pRecordset !=NULL ) )
{
while( !pRecordset->GetadoEOF() )
{
//traverse through all the records of current recordset...
}
long lngRec = 0;
pRecordset = pRecordset->NextRecordset((VARIANT *)lngRec);
}
//Error handling and cleanup code (like closing recordset/ connection)
//etc are not shown here.
参考资料：
1. How to call an Oracle Stored Procedure that returns one or more REF CURSORS, using ADO from C++
2. 使用OLE DB和ADO调用返回记录集的Oracle存储过程