总结了一下PRO*C中存储过程调用,普通SQL语句游标执行,动态SQL语句游标执行的方法;[@more@]
PRO*C中常用SQL及游标、存储过程使用汇总:
1) exec sql select c1,c2 into :v1,v2 from table_a;
2) exec sql insert into table_a(v1,v2) select b.v1,b.v2 from table_b b where 1=2;
3) exec sql insert into table_a(v1,v2) select b.v1,b.v2 from table_b b where 1=2;
存储过程调用
4) exec sql call procedure_a(:v1,:v2);
5) sprintf(sta,"select v1,v2 into :v1,v2 from table_a where v3=%s",v3);
exec sql execute immediate :sta;
--普通SQL语句游标执行
6)exec sql declare cur1 cursor for
select v1,v2,v3 from table_a where 1=2;
exec sql open cur1;
do{
exec sql fetch cur1 into :v1,:v2,:v3;
if(sqlca.sqlcode==-1403) break;
....
}while(1) ;
exec sql close cur1;
--动态SQL语句游标执行
7)> sprintf(sta,"select c1,c2,c3 from table_a where c1=%s and c2=:v1 and c3=:v2",v1);
exec sql prepare select_msg from :sta;
exec sql declare cur1 cursor for select_msg;
exec sql open cur1 using :v1,:v2;
do{
exec sql fetch cur1 into :c1,:c2,:c3;
if(sqlca.sqlcode==1403)break;
....
}while(1);
exec sql close cur1;