oracle存储过程:
...
type my_cursor is ref cursor;
...
procedure my_proc
(id in number, f3 out my_cursor,f4 out my_cursor);
is begin
open f3 for select * from sdb.test_table where age>id;
open f4 for select * from sdb.test_table2 where age>2*id;
end;
end;表test_table
id number, name varchar2, age number
链接数据库:
_RecordsetPtr m_recordset;
_ConnectionPtr m_connection;
_CommandPtr m_command;
...初始化环境...
m_connection->Open("Provider=MSDAORA.1;Use ID=sdf;Password=sdf;Data Source=oranetwork;Persist Security Info=True;");
m_command.CreateInstance(__uuidof(Command));
m_recordset.CreateInstance(__uuidof(Recordset));
m_command->ActiveConnection = m_connection;
m_command->CommandText = "{call pro_test_tabs(24)}";//存储过程
m_command->PutCommandType(adCmdText);
m_recordset = m_command->Execute(0,0,adCmdUnknown);
//取存储过程中游标的值
if (m_recordset->Fields->Count == NULL || m_recordset->Fields->Count==0)
{
std::cout << "没结果集" << std::endl;
}
else
{
int ncount = 0;
_variant_t varparam;
_bstr_t bstr;
while (!(m_recordset == NULL))
{
long colnum = m_recordset->Fields->Count;//去表格列数
if (colnum == 0) break;
std::cout << "\n--------------#table" << ++ncount << "-----------\n";
for (int j=0; j<colnum; j++)
{
varparam = m_recordset->Fields->GetItem((long)j)->Name;//获取列名
while (!m_recordset->EndOfFile)
{
for (int i=0; i<colnum; i++)
{
varparam.Clear();
varparam = m_recordset->GetFields()->GetItem((long)i)->Value;//获取表格单元格内容
if (varparam.vt != VT_NULL)
bstr = varparam;
else
bstr = "NULL";
printf(" %s ",(const char*)bstr);;//输出表格单元格内容
}//for
m_recordset->MoveNext();
}//while
long lngRec = 0;
m_recordset = m_recordset->NextRecordset((VARIANT *)lngRec);//获取下一个游标集合???获取不到
}while
}问题:!!!!!!
问题1:
在调用存储过程"{call pro_test_tabs(24)}"中其参数24正确传入存储过程中,即在
my_proc
(id in number, f3 out my_cursor,f4 out my_cursor);
打印出的是整个test_table表,并没有按age>id 来处理,跟踪发现获取到的id为空????????????想不通!
问题2:
取不到第二个游标f4的值,有报错:当前提供程序不支持从单一执行返回多个记录集。
该怎样取到f4游标中的值?
请高手帮忙,谢谢!
...
type my_cursor is ref cursor;
...
procedure my_proc
(id in number, f3 out my_cursor,f4 out my_cursor);
is begin
open f3 for select * from sdb.test_table where age>id;
open f4 for select * from sdb.test_table2 where age>2*id;
end;
end;表test_table
id number, name varchar2, age number
链接数据库:
_RecordsetPtr m_recordset;
_ConnectionPtr m_connection;
_CommandPtr m_command;
...初始化环境...
m_connection->Open("Provider=MSDAORA.1;Use ID=sdf;Password=sdf;Data Source=oranetwork;Persist Security Info=True;");
m_command.CreateInstance(__uuidof(Command));
m_recordset.CreateInstance(__uuidof(Recordset));
m_command->ActiveConnection = m_connection;
m_command->CommandText = "{call pro_test_tabs(24)}";//存储过程
m_command->PutCommandType(adCmdText);
m_recordset = m_command->Execute(0,0,adCmdUnknown);
//取存储过程中游标的值
if (m_recordset->Fields->Count == NULL || m_recordset->Fields->Count==0)
{
std::cout << "没结果集" << std::endl;
}
else
{
int ncount = 0;
_variant_t varparam;
_bstr_t bstr;
while (!(m_recordset == NULL))
{
long colnum = m_recordset->Fields->Count;//去表格列数
if (colnum == 0) break;
std::cout << "\n--------------#table" << ++ncount << "-----------\n";
for (int j=0; j<colnum; j++)
{
varparam = m_recordset->Fields->GetItem((long)j)->Name;//获取列名
while (!m_recordset->EndOfFile)
{
for (int i=0; i<colnum; i++)
{
varparam.Clear();
varparam = m_recordset->GetFields()->GetItem((long)i)->Value;//获取表格单元格内容
if (varparam.vt != VT_NULL)
bstr = varparam;
else
bstr = "NULL";
printf(" %s ",(const char*)bstr);;//输出表格单元格内容
}//for
m_recordset->MoveNext();
}//while
long lngRec = 0;
m_recordset = m_recordset->NextRecordset((VARIANT *)lngRec);//获取下一个游标集合???获取不到
}while
}问题:!!!!!!
问题1:
在调用存储过程"{call pro_test_tabs(24)}"中其参数24正确传入存储过程中,即在
my_proc
(id in number, f3 out my_cursor,f4 out my_cursor);
打印出的是整个test_table表,并没有按age>id 来处理,跟踪发现获取到的id为空????????????想不通!
问题2:
取不到第二个游标f4的值,有报错:当前提供程序不支持从单一执行返回多个记录集。
该怎样取到f4游标中的值?
请高手帮忙,谢谢!
解决方案 »
- 触发器的创建
- 控制用户访问的进程数目
- 在Linux下安装oracle9i,出现错误 %PROD_HOME %instal;没有那个文件或目录
- linux下oracle登录问题,急,请高手指教
- @@@@@@@@网站上线了!散分!来者有份!up有分!还请大家讨论一下这行的前途@@@@@@@@
- 在什么情况下oracle的collection.extend 不起作用
- oracle 9i exp 8i 数据如何实现???
- 求一条很难处理的更新语句(求高手帮忙mysql)
- 新手上路啊,大家帮帮忙!!!
- asp连接oracle时0x80004005错误,急,在线等!
- 提个问题
- 关于触发器,为什么跟其他代码写在一个文件PL/SQL SQL WINDOW里就报错呢?分开就又对了
open f3 for select * from sdb.test_table where age>id;
open f4 for select * from sdb.test_table2 where age>2*id;
应该用select 具体的字段,这是写良好代码的习惯
为什么"{call pro_test_tabs(24)}"中的24无法传入到存储过程中去?
m_connection->Open("Provider=MSDAORA.1;Use ID=sdf;Password=sdf;Data Source=oranetwork;Persist Security Info=True;PLSQLRSet=1"); 找了一圈终于找到,这个PLSQLRSet=1是必顺设置的。你其它的代码还有没有问题,就不知道了
你可以用union的方式合并两个结果集
_RecordsetPtr m_recordset;
_ConnectionPtr m_connection;
_CommandPtr m_command; m_connection.CreateInstance(__uuidof(Connection));
m_command.CreateInstance(__uuidof(Command));
//m_recordset.CreateInstance(__uuidof(Recordset)); //...初始化环境...
m_connection->Open("Provider=OraOLEDB.Oracle.1;Use ID=scott;Password=dba123;Data Source=ora1;PLSQLRSet=1", "scott", "dba123", NULL);
m_command->ActiveConnection = m_connection;
m_command->CommandText = "{call my_proc(24)}";//存储过程
//m_command->PutCommandType(adCmdText);
m_recordset = m_command->Execute(0,0,adCmdUnknown);
//
//取存储过程中游标的值
if (m_recordset->Fields->Count == NULL || m_recordset->Fields->Count==0)
{
//std::cout < < "没结果集" < < std::endl;
//CString str = "没有结果集";
}
else
{
int ncount = 0;
_variant_t varparam;
_bstr_t bstr;
while (!(m_recordset == NULL))
{
long colnum = m_recordset->Fields->Count;//去表格列数
if (colnum == 0) break;
//std::cout < < "\n--------------#table" < < ++ncount < < "-----------\n";
for (int j=0; j <colnum; j++)
{
varparam = m_recordset->Fields->GetItem((long)j)->Name;//获取列名
while (!m_recordset->adoEOF)
{
for (int i=0; i <colnum; i++)
{
varparam.Clear();
varparam = m_recordset->GetFields()->GetItem((long)i)->Value;//获取表格单元格内容
if (varparam.vt != VT_NULL)
bstr = varparam;
else
bstr = "NULL";
//printf(" %s ",(const char*)bstr);;//输出表格单元格内容
}//for
m_recordset->MoveNext();
}//while
}//for
long lngRec = 0;
m_recordset = m_recordset->NextRecordset((VARIANT *)lngRec);//获取下一个游标集合???获取不到
}//while
}
return 0;
create or replace procedure my_proc
(
v_id number
,v_f3 out sys_refcursor
,v_f4 out sys_refcursor
)
as
begin
open v_f3 for select * from test_table where id>v_id;
open v_f4 for select * from test_table2 where id>2*v_id;
end;
取不到第二个游标f4的值,有报错:当前提供程序不支持从单一执行返回多个记录集。 就是连接字符串里没有设置PLSQLRSet=1造成的。----------------------------------
你的第一个问题:
打印出的是整个test_table表,并没有按age>id 来处理,跟踪发现获取到的id为空????????????想不通!很明显了,你的表有个id字段,而你的my_proc这个sp也有个id的参数,这2个搞混了,建议你改这个参数名:v_id。否则,oracle选择id是字段名,而非参数名
但是收到的结果是不是整个表达内容,id>v_id这个限制没有用上啊?
用上了。
你用sqlplus来测试下就知道了。
sql>var x1 refcursor;
sql>var x2 refcursor;
sql>begin my_proc(24, :x1, :x2);
sql>print :x1;
sql>print :x2;你在sqlplus下依次执行以上的命令,看看结果就知道了。
_variant_t varparam;
_bstr_t bstr;
while (!(m_recordset == NULL))
{
long colnum = m_recordset->Fields->Count;//去表格列数
if (colnum == 0) break;
//std::cout < < "\n--------------#table" < < ++ncount < < "-----------\n";
for (int j=0; j <colnum; j++)
{
varparam = m_recordset->Fields->GetItem((long)j)->Name;//获取列名
//输出列名
}//for ------- 这个 for 应该在这里结果 while (!m_recordset->adoEOF)
{
for (int i=0; i <colnum; i++)
{
varparam.Clear();
varparam = m_recordset->GetFields()->GetItem((long)i)->Value;//获取表格单元格内容
if (varparam.vt != VT_NULL)
bstr = varparam;
else
bstr = "NULL";
//printf(" %s ",(const char*)bstr);;//输出表格单元格内容
}//for
m_recordset->MoveNext();
}//while
long lngRec = 0;
m_recordset = m_recordset->NextRecordset((VARIANT *)lngRec);//获取下一个游标集合???获取不到
}//while