楼上的老大,我的存储过程这样定义--自定义游标类型 type UCursorType is REF CURSOR;procedure TESTP(P_INPUTSTR in VARCHAR2,--传入字串 P_RESULT out number,--返回的错误码 P_OUT out varchar2,--返回的信息 P_OUTCUR out Ucursortype--输出的游标 ) out,in参数我用 bindbyname 我在oci程序中这样调用存储过程 text plsqlBlock[512] ="BEGIN TEST.TESTP(:param1,:param2,:param3,:param4); END;"; 或text plsqlBlock[512] ="BEGIN TEST.TESTP(:param1,:param2,:param3); END;"; 这里到底有几个参数,是4个还是3个。 这不是sql语句,不知道游标的数据取出后会放在那
不过bind输入参数是用OCIBind**函数,bind输出参数是用OCIDefine**函数。
bind游标好像只能用OCIBindByName()OCIBindByName(stmthp, &bindhp, errhp, (text *)":curs", (sb4)strlen((char*)":curs"), (dvoid *)&stmthp2, (sb4)0, SQLT_RSET, (dvoid *)0,
(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, (ub4)OCI_DEFAULT)))stmthp2是用来fetch游标的。execute之后,bind游标sql语句的输入输出参数,然后直接fetch。
type UCursorType is REF CURSOR;procedure TESTP(P_INPUTSTR in VARCHAR2,--传入字串
P_RESULT out number,--返回的错误码
P_OUT out varchar2,--返回的信息
P_OUTCUR out Ucursortype--输出的游标
)
out,in参数我用 bindbyname
我在oci程序中这样调用存储过程
text plsqlBlock[512] ="BEGIN TEST.TESTP(:param1,:param2,:param3,:param4); END;";
或text plsqlBlock[512] ="BEGIN TEST.TESTP(:param1,:param2,:param3); END;";
这里到底有几个参数,是4个还是3个。
这不是sql语句,不知道游标的数据取出后会放在那
绑定存储过程的参数后,execute,然后同样绑定cursor所open的sql语句的参数,然后再fetch,数据就在你绑定cursor所open的sql语句的参数上。兄弟有没有用oci select过lob字段数据,速度狂慢啊,有没有提高性能的方法。
你为什么不试试OCIDefine**() 呢,不要太固执
存储过程代码,我的存储过程不是写在程序中的,而是写在oracle库中,因为存储过程的修改不能影响到程序的修改type UCursorType is REF CURSOR;
procedure (P_INPUTSTR IN OUT STRING,--传入字串
P_RESULT IN OUT INTEGER,--返回的错误码
P_OUT IN OUT STRING,--返回的信息
P_OUTCUR out Ucursortype--输出的游标
)
is
begin
open P_OUTCUR for
select a.testname from testblock a;
P_RESULT:=0;
P_OUT:='SUCCESS';
end TEST;程序中的代码
text plsqlBlock[512] ="BEGIN TEST.TESTP(:param1,:param2,:param3,:param4); OCIStmt *stmthpEw;
(void)OCIHandleAlloc((dvoid *) envhp, (dvoid **)&stmthpEw,
(ub4)OCI_HTYPE_STMT, (size_t)100, (dvoid **)&UserBuf);status = OCIBindByName(stmthp,&bindpp,errhp,(text *)":param4",-1,(dvoid *)stmthpEw, (sb4)0,SQLT_RSET,(dvoid *) 0,(ub2 *) 0,(ub2 *) 0,(ub4) 0,
(ub4 *) 0,OCI_DEFAULT);
执行断
status=OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,(OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_COMMIT_ON_SUCCESS);
再次绑定
status = OCIBindByName(stmthp,&bindpp,errhp,(text *)":param4",-1,(dvoid *)stmthpEw, (sb4)0,SQLT_RSET,(dvoid *) 0,(ub2 *) 0,(ub2 *) 0,(ub4) 0,
(ub4 *) 0,OCI_DEFAULT);status = OCIStmtFetch(stmthp, errhp, (ub4)1, (ub4) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT);while(status != OCI_NO_DATA)
{
printf("提取内容=<%s>\n",?????);
status = OCIStmtFetch(stmthp, errhp, (ub4)1, (ub4) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT);
}//while
按照钝刀大哥的想法实现,不过这里执行出错,这里的关键应该是绑定游标后,程序中怎么去得到游标中的数据,fetch()后的数据放在那里,select 语句是在程序中定义输出变量的,fetch()后的数据就放在这些输出变量中,但我的存储过程是写在oracle库中,也就是说存储过程中的语句对我是透明的,我写的存储过程只是一个测试,实际的比这复杂多了,里面还有insert 语句,请赐教
status = OCIBindByName(stmthp,&bindpp,errhp,(text *)":param4",strlen((char*)":param4"),(dvoid *)stmthpEw, (sb4)0,SQLT_RSET,(dvoid *) 0,(ub2 *) 0,(ub2 *) 0,(ub4) 0, (ub4 *) 0,OCI_DEFAULT);execute之后,再绑定select a.testname from testblock a;这个语句中的输出参数,应该是a.testname。
OCIDefineByPos(stmthEw, &defnhp, errhp, 1, (dvoid *) &(a.testname),
strlen(a.testname)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);然后fetch:
status = OCIStmtFetch(stmthpEw, errhp, (ub4)1, OCI_FETCH_NEXT,OCI_DEFAULT);
OCIDefineByPos(stmthEw, &defnhp, errhp, 1, (dvoid *) &(a.testname),
strlen(a.testname)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);应改为
char name[1000];
OCIDefineByPos(stmthEw, &defnhp, errhp, 1, (dvoid *) &name,1000, SQLT_STR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
#include <oci.h>
#include <orl.h>class connection
{
public:
connection();/*初始化*/
~connection();
sb4 connect(const text *username, const text *password,const text *database);
sword disconnect();
sword connection::Prepare(text *StrStmt);
sword connection::BindIntParam(OCIBind *bindpp,text *StrParam,sword &iVariable, sb4 iIntLen);
sword connection::BindNumParam(OCIBind *bindpp,text *StrParam,sword &iVariable ,sb4 iIntLen);
sword connection::BindStrParam(OCIBind *bindpp,text *StrParam,dvoid *StrBuf ,sb4 iStrLen);
sword connection::BindVarParam(OCIBind *bindpp,text *StrParam,dvoid *StrBuf,sb4 iStrLen);
sword connection::BindCurParam(OCIBind *bindpp,OCIStmt *stmthp,char *UserBuf,text *StrParam);//绑定游标类型
sword connection::DefineParam(OCIStmt *stmthpEw,int iPos,char *StrBuf,int iStrBufLen);//定义输出变量
sword connection::execute(short iCommit);//
sword connection::fetch(ub4 nRows);//提取结果集
sword dbcommit(); /*回滚*/
sword dbrollback(); /*提交*/
sb4 checkerr(sword status);
sb4 report_error(); protected:
OCIEnv *envhp ;//环境句柄
OCIError *errhp ;//错误句柄
OCIServer *srvhp ;//服务器句柄
OCISvcCtx *svchp ;//服务句柄
OCISession *authp ;//会话句柄
OCIStmt *stmthp ;//执行段句柄
OCIDefine *defnhp ;//定义段句柄 enum conn_state
{
not_connected,
connected
};
conn_state state;
};
sword connection::BindStrParam(OCIBind *bindpp,text *StrParam,dvoid *StrBuf,sb4 iStrLen)
{
sword status;
sb4 ErrCode;
status = OCIBindByName(stmthp,&bindpp,errhp,(text *)StrParam,
-1,(dvoid *)StrBuf,(sb4)iStrLen,SQLT_STR,(dvoid *) 0,
(ub2 *) 0,(ub2 *) 0,(ub4) 0, (ub4 *) 0, OCI_DEFAULT);
if( (ErrCode=checkerr(status) )!= 0)
{
return -1;
}
return status;
}
//引用传递整数值,绑定integer(8bit,16bit,32bit)
sword connection::BindIntParam(OCIBind *bindpp,text *StrParam,sword &iVariable ,sb4 iIntLen)
{
sword status;
sb4 ErrCode;
status = OCIBindByName( stmthp,&bindpp,errhp,(text *)StrParam,-1,
(sword *)&iVariable,(sb4)iIntLen,SQLT_INT,(dvoid *) 0,(ub2 *) 0,
(ub2 *) 0,(ub4) 0,(ub4 *) 0,OCI_DEFAULT); if( (ErrCode=checkerr(status) )!= 0)
{
return -1;
}
return status;
}
//绑定游标类型,stmthpEw 是为指针分配的游标句柄
sword connection::DefineParam(OCIStmt *stmthpEw,int iPos,char *StrBuf,int iStrBufLen)
{
sword status;
sb4 ErrCode;
status = OCIDefineByPos(stmthpEw,&defnhp,errhp,(ub4)iPos,(dvoid *)StrBuf,
(sb4)iStrBufLen,SQLT_STR,(dvoid *) 0,(ub2 *), (ub2 *)0,
OCI_DEFAULT);
if( (ErrCode=checkerr(status) )!= 0)
{
return -1;
}
return status;
}//iCommit=1 insert iCommit=0 select
sword connection::execute(short iCommit)
{
sword status;
sb4 ErrCode; if(iCommit = 1)
status=OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,(OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
else if( iCommit = 0 )
status=OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,(OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_COMMIT_ON_SUCCESS);
if((ErrCode=checkerr(status))!=0)
{
return -1;
}
return 0;
}sword connection::fetch(ub4 nRows)//提取结果集
{
sword status;
status = OCIStmtFetch(stmthp, errhp, (ub4)nRows, (ub4) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT);
return status;
}