qt调用oracle存储过程
我用qt操作oracle数据库，根据帮助文档，有下面的用法：
QSqlQuery query;
query.prepare("CALL AsciiToInt(?, ?)");
query.bindValue(0, "A");
query.bindValue(1, 0, QSql::Out);
query.exec();
int i = query.boundValue(1).toInt(); // i is 65
但当参数中有游标类型sys_refcursor 时，该如何调用，又如何接受返回结果呢？
[解决办法]
QT可能不支持这个功能 － Accessing REF CURSOR in Stored Procedure from Qt。
可以通过ODBC API来做，同样是跨平台的
The following sample application shows how to return a result set by using Oracle ODBC Driver:
/*
* Sample Application using Oracle reference cursors through ODBC
*
* Assumptions:
*
* 1) Oracle Sample database is present with data loaded for the EMP table.
*
* 2) Two fields are referenced from the EMP table, ename and mgr.
*
* 3) A data source has been setup to access the sample database.
*
*
* Program Description:
*
* Abstract:
*
* This program demonstrates how to return result sets using
* Oracle stored procedures
*
* Details:
*
* This program:
* Creates an ODBC connection to the database.
* Creates a Packaged Procedure containing two result sets.
* Executes the procedure and retrieves the data from both result sets.
* Displays the data to the user.
* Deletes the package then logs the user out of the database.
*
*
* The following is the actual PL/SQL this code generates to
* create the stored procedures.
*
DROP PACKAGE ODBCRefCur;
CREATE PACKAGE ODBCRefCur AS
TYPE ename_cur IS REF CURSOR;
TYPE mgr_cur IS REF CURSOR;
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2);
END;
/
CREATE PACKAGE BODY ODBCRefCur AS
PROCEDURE EmpCurs(Ename IN OUT ename_cur, Mgr IN OUT mgr_cur, pjob IN VARCHAR2)
AS
BEGIN
IF NOT Ename%ISOPEN
THEN
OPEN Ename for SELECT ename from emp;
END IF;
IF NOT Mgr%ISOPEN
THEN
OPEN Mgr for SELECT mgr from emp where job = pjob;
END IF;
END;
END;
/
*
* End PL/SQL for Reference Cursor.
*/
/*
* Include Files
*/
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
/*
* Defines
*/
#define JOB_LEN 9
#define DATA_LEN 100
#define SQL_STMT_LEN 500
/*
* Procedures
*/
void DisplayError( SWORD HandleType, SQLHANDLE hHandle, char *Module );
/*
* Main Program
*/
int main()
{
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
SQLRETURN rc;
char *DefUserName ="scott";
char *DefPassWord ="tiger";
SQLCHAR ServerName[DATA_LEN];
SQLCHAR *pServerName=ServerName;
SQLCHAR UserName[DATA_LEN];
SQLCHAR *pUserName=UserName;
SQLCHAR PassWord[DATA_LEN];
SQLCHAR *pPassWord=PassWord;
char Data[DATA_LEN];
SQLINTEGER DataLen;
char error[DATA_LEN];
char *charptr;
SQLCHAR SqlStmt[SQL_STMT_LEN];
SQLCHAR *pSqlStmt=SqlStmt;
char *pSalesMan = "SALESMAN";
SQLINTEGER sqlnts=SQL_NTS;
/*
* Allocate the Environment Handle
*/
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Environment Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Set the ODBC Version
*/
rc = SQLSetEnvAttr( hEnv,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3,0);
if (rc != SQL_SUCCESS)
{
printf( "Cannot Set ODBC Version\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Allocate the Connection handle
*/
rc = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Connection Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Get User Information
*/
strcpy ((char *) pUserName, DefUserName );
strcpy ((char *) pPassWord, DefPassWord );
/*
* Data Source name
*/
printf( "\nEnter the ODBC Data Source Name\n" );
charptr = gets ((char *) ServerName);
/*
* User Name
*/
printf ( "\nEnter User Name Default [%s]\n", pUserName);
charptr = gets ((char *) UserName);
if (*charptr == '\0')
{
strcpy ((char *) pUserName, (char *) DefUserName );
}
/*
* Password
*/
printf ( "\nEnter Password Default [%s]\n", pPassWord);
charptr = gets ((char *)PassWord);
if (*charptr == '\0')
{
strcpy ((char *) pPassWord, (char *) DefPassWord );
}
/*
* Connection to the database
*/
rc = SQLConnect( hDbc,pServerName,(SQLSMALLINT) strlen((char *)pServerName),pUserName,(SQLSMALLINT) strlen((char *)pUserName),pPassWord,(SQLSMALLINT) strlen((char *)pPassWord));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_DBC, hDbc, "SQLConnect");
}
/*
* Allocate a Statement
*/
rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
if (rc != SQL_SUCCESS)
{
printf( "Cannot Allocate Statement Handle\n");
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
/*
* Drop the Package
*/
strcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
/*
* Create the Package Header
*/
strcpy( (char *) pSqlStmt, "CREATE PACKAGE ODBCRefCur AS\n");
strcat( (char *) pSqlStmt, " TYPE ename_cur IS REF CURSOR;\n");
strcat( (char *) pSqlStmt, " TYPE mgr_cur IS REF CURSOR;\n\n");
strcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");
strcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur,pjob IN VARCHAR2);\n\n");
strcat( (char *) pSqlStmt, "END;\n");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Create the Package Body
*/
strcpy( (char *) pSqlStmt, "CREATE PACKAGE BODY ODBCRefCur AS\n");
strcat( (char *) pSqlStmt, " PROCEDURE EmpCurs (Ename IN OUT ename_cur,");
strcat( (char *) pSqlStmt, "Mgr IN OUT mgr_cur, pjob IN VARCHAR2)\n AS\n BEGIN\n");
strcat( (char *) pSqlStmt, " IF NOT Ename%ISOPEN\n THEN\n");
strcat( (char *) pSqlStmt, " OPEN Ename for SELECT ename from emp;\n");
strcat( (char *) pSqlStmt, " END IF;\n\n");
strcat( (char *) pSqlStmt, " IF NOT Mgr%ISOPEN\n THEN\n");
strcat( (char *) pSqlStmt, " OPEN Mgr for SELECT mgr from emp where job = pjob;\n");
strcat( (char *) pSqlStmt, " END IF;\n");
strcat( (char *) pSqlStmt, " END;\n");
strcat( (char *) pSqlStmt, "END;\n");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Bind the Parameter
*/
rc = SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,JOB_LEN,0,pSalesMan,0,&sqlnts);
/*
* Call the Store Procedure which executes the Result Sets
*/
strcpy( (char *) pSqlStmt, "{CALL ODBCRefCur.EmpCurs(?)}");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLExecDirect");
}
/*
* Bind the Data
*/
rc = SQLBindCol( hStmt,1,SQL_C_CHAR,Data,sizeof(Data),&DataLen);
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLBindCol");
}
/*
* Get the data for Result Set 1
*/
printf( "\nEmployee Names\n\n");
while ( rc == SQL_SUCCESS )
{
rc = SQLFetch( hStmt );
if ( rc == SQL_SUCCESS )
{
printf("%s\n", Data);
}
else
{
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
}
}
}
printf( "\nFirst Result Set - Hit Return to Continue\n");
charptr = gets ((char *)error);
/*
* Get the Next Result Set
*/
rc = SQLMoreResults( hStmt );
if (rc != SQL_SUCCESS)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");
}
/*
* Get the data for Result Set 2
*/
printf( "\nManagers\n\n");
while ( rc == SQL_SUCCESS )
{
rc = SQLFetch( hStmt );
if ( rc == SQL_SUCCESS )
{
printf("%s\n", Data);
}
else
{
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLFetch");
}
}
}
printf( "\nSecond Result Set - Hit Return to Continue\n");
charptr = gets ((char *)error);
/*
* Should Be No More Results Sets
*/
rc = SQLMoreResults( hStmt );
if (rc != SQL_NO_DATA)
{
DisplayError(SQL_HANDLE_STMT, hStmt, "SQLMoreResults");
}
/*
* Drop the Package
*/
strcpy( (char *) pSqlStmt, "DROP PACKAGE ODBCRefCur");
rc = SQLExecDirect(hStmt, pSqlStmt, strlen((char *)pSqlStmt));
/*
* Free handles close connections to the database
*/
SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
SQLDisconnect( hDbc );
SQLFreeHandle( SQL_HANDLE_DBC, hDbc );
SQLFreeHandle( SQL_HANDLE_ENV, hEnv );
printf( "\nAll Done - Hit Return to Exit\n");
charptr = gets ((char *)error);
return(0);
}
/*
* Display Error Messages
*/
void DisplayError( SWORD HandleType, SQLHANDLE hHandle, char *Module )
{
SQLCHAR MessageText[255];
SQLCHAR SQLState[80];
SQLRETURN rc=SQL_SUCCESS;
long NativeError;
SWORD RetLen;
SQLCHAR error[25];
char *charptr;
rc =
SQLGetDiagRec(HandleType,hHandle,1,SQLState,&NativeError,MessageText,255,&RetLen);
printf( "Failure Calling %s\n", Module );
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
printf( "\t\t\t State: %s\n", SQLState);
printf( "\t\t\t Native Error: %d\n", NativeError );
printf( "\t\t\t Error Message: %s\n", MessageText );
}
printf( "\nHit Return to Exit\n");
charptr = gets ((char *)error);
exit(1);
}
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
