OCCI调用带有返回结果集的存储过程，获取结果集中指定列的值

//用到的带有返回结果记录集的存储过程
create or replace procedure recordset(p_cur in out sys_refcursor,sno in int)
as
begin
  open p_cur for select * from student where student.sid = sno;
 end recordset;
 
//OCCI 存储调用代码示例
#include <stdio.h>
#include <stdlib.h>
#include <iostream>
#define LINUXOCCI 
#include <occi.h>
#include <list>
#include <iterator>
#include <iomanip>
#include <string.h>


using namespace std;
using namespace oracle::occi;


class TestCallPRO
{
public:
TestCallPRO(std::string userName,std::string passWD,std::string serverName);//构造
~TestCallPRO();
void LinkOnOracle();//链接数据库
void LinkOffOracle();//断开数据库
void SpliceSQL(std::list<char*>  &cValues);//拼接要执行的SQL语句
void CValuesToList(char* &p1,char *&p2,char* &p3,char *&p4,std::list<char*> &cValues);//值入栈
void ExecuteSQlProc(std::list<char*> &cValues);//绑定对应的值，执行SQL语句
void GetAssignValues(std::string astrColName);//获取结果集中指定的记录值
void ConvertStrToLower(const char* pCloumnName,std::string &tempstr);//获取结果集中指定列的值（可以使多条记录）
void GetValues(int iNdex);
protected:
Environment *m_env; //数据库环境变量指针
Connection *m_conn; //数据库连接指针
Statement *m_pstmt;//数据库执行对象
ResultSet *m_Result; //返回的查询结果的记录集指针


std::string m_userName; //数据库用户名
std::string m_passWD;    //用户名对应的密码
std::string m_serverName; //格式：<服务器IP>：<监听端口>/<数据库名>
};
TestCallPRO::TestCallPRO(std::string userName,std::string passWD,std::string serverName)
{
m_env = NULL;
m_conn = NULL;
m_pstmt = NULL;
m_Result = NULL;

m_userName = userName;
m_passWD =  passWD;
m_serverName = serverName;
}
void TestCallPRO::LinkOnOracle()
{
if(m_env == NULL)
m_env=Environment::createEnvironment();//创建数据库连接对象

if(m_conn == NULL)
m_conn = m_env->createConnection(m_userName,m_passWD,m_serverName);//Link
}

void TestCallPRO::LinkOffOracle()
{
m_env->terminateConnection(m_conn);
Environment::terminateEnvironment(m_env);
}

void TestCallPRO::SpliceSQL(std::list<char*>  &cValues)
{
/* std::string astrSQL = "";
astrSQL = "call "; 
astrSQL += "GetValuePRO"; 
astrSQL += "("; 
char suu[2] ={ '1','\0'};

std::list<char *>::iterator loIter = cValues.begin(); 

for(; loIter != cValues.end(); loIter++) 
{ 
std::string str = "";
str = &suu[0];
astrSQL += ":";
astrSQL += str;
astrSQL += ",";
++ suu[0];
}
if(astrSQL[astrSQL.length() - 1] == '(') 
{ 
astrSQL[astrSQL.length() - 1] = ';'; 
} 
else 
{ 
astrSQL[astrSQL.length() - 1 ] = ')'; 
} 
*/
m_pstmt = m_conn->createStatement("call recordset(:1,:2)");//执行非查询语句 不需要传递SQL语句 
}

void TestCallPRO::CValuesToList(char* &p1,char *&p2,char *&p3,char *&p4,std::list<char*> &cValues)
{
/* cValues.push_back(p1);
cValues.push_back(p2);
cValues.push_back(p3);
cValues.push_back(p4);
*/
}

void TestCallPRO::ExecuteSQlProc(std::list<char*> &cValues)
{
char *p1 = NULL;
int itemp = 0;
try
{

// std::list<char *>::iterator iter = cValues.begin();

m_pstmt->registerOutParam(1,OCCICURSOR);

// itemp = atoi((*iter));
// cout<<"begin 1"<<endl;
m_pstmt->setInt(2, 1001); //bind first values


//register all out values
// m_pstmt->registerOutParam(2,OCCICURSOR);
// m_pstmt->registerOutParam(3,OCCISTRING,10);
// m_pstmt->registerOutParam(4,OCCINUMBER);


}
catch(SQLException ex)
{
cout<<"Error Number"<<ex.getErrorCode()<<endl;
cout<<ex.getMessage()<<endl;
}

m_pstmt->executeQuery();//execute SQL　and return recordset pointer

cout<<"result"<<endl;
    m_Result = m_pstmt->getCursor(1);
}
void TestCallPRO::GetAssignValues(std::string astrColName) //获取指定列的数值
{
/* if(m_Result == NULL)
m_Result = m_pstmt->getResultSet();
*/
//返回的结果有多少列 
vector<MetaData>  listOfColumns = m_Result->getColumnListMetaData(); //获结果集中取表的列名
int nFieldCount = listOfColumns.size(); //应该是列的个数的大小

cout<<nFieldCount<<endl;
std::string    tempstr;
int nBufferIndex;


for(unsigned int i=0; i< nFieldCount; i++) 
{ 
cout<<"step into"<<endl;
MetaData columnObj = listOfColumns[i];//取出该字段信息 


//循环比较字段名称，看是否是需要提取的那个字段; 
// ConvertStrToLower(columnObj.getString (MetaData::ATTR_NAME).c_str(),tempstr); //获取当前列的名称 并转为小写存储到tempstr
tempstr = columnObj.getString (MetaData::ATTR_NAME);


if(strcmp(astrColName.c_str(), tempstr.c_str()) == 0)//两个字段名一致 astrColName形参   tempstr 获取到的列名
{
int nType = columnObj.getInt(MetaData::ATTR_DATA_TYPE); //获取到该字段的类型

cout<<nType<<endl;
cout<<"Type Cloumn："<<i<<tempstr<<columnObj.getString (MetaData::ATTR_NAME).c_str()<<endl;//打印该字段类型


std::string strvalue;
int iTempValues;


switch(nType) 
{ 
case SQLT_INT:
iTempValues = m_Result->getInt(i+1);
cout<<iTempValues<<endl;
break;
case SQLT_STR:
strvalue = m_Result->getString(i+1);
cout<<strvalue<<endl;
break;
case SQLT_NUM:
iTempValues = m_Result->getNumber(i+1);
cout<<iTempValues<<endl;
break;
case SQLT_CHR:
GetValues(i+1);
break;
default:
cout<<"No Type!"<<endl;
}

//打印获取到的值  
} 
}


cout<<"OK!!!!!"<<endl;
}


void TestCallPRO::GetValues(int iNdex)
{
while(m_Result->next())//列循环获取iNdex列的所有记录的值
{
string str = m_Result->getString(iNdex);
cout<<str<<endl;
}
}


//大小写转换
void TestCallPRO::ConvertStrToLower(const char* pCloumnName,std::string &tempstr)
{

// tempstr = pCloumnName;
// transform(tempstr.begin(),tempstr.end(),tempstr.begin(),tolower);
}
TestCallPRO::~TestCallPRO()
{
//析构
}
int main(void)
{
int iID = 1521;
char *p1 = new char[sizeof(int) +1];
memset(p1, 0, sizeof(int) +1);
sprintf(p1,"%d",iID);


char *p2 = new char[10];
strcpy(p2, "name");


char *p3 = new char[20];
strcpy(p2, "sex");

int age = 25;
char *p4 = new char[sizeof(int) +1];
memset(p4, 0, sizeof(int) +1);
sprintf(p4,"%d",age);

//开始执行
std::list<char *>  aoStoParam;
TestCallPRO  testcallPRO("xxxxxxxx","xxxxxxx","xx.xx.x.x:x/x");//上面有格式


testcallPRO.LinkOnOracle(); //oracle 的链接


testcallPRO.CValuesToList(p1, p2,p3,p4,aoStoParam);//入栈（容器）
testcallPRO.SpliceSQL(aoStoParam);//“sql语句拼接”
testcallPRO.ExecuteSQlProc(aoStoParam); //“sql 的执行”
testcallPRO.GetAssignValues("列名");//传参指定要获取的列
testcallPRO.LinkOffOracle(); //oracle 的断开
//结束
return 0;
}