存储过程如下:create or replace procedure MarketWatchFrame1 (
acct_id in out number,cust_id in out number,ending_co_id in out number,
industry_name in out varchar2,start_date in out varchar2,starting_co_id in out number,
pct_change out number,status out integer)
as
type stock_cur is ref cursor;
stock_list stock_cur;
old_mkt_cap number;
new_mkt_cap number;
symbol varchar2(15);
new_price number(8,2);
old_price number(8,2);
sec_num_out number(12);begin
if cust_id!=0 then
open stock_list for select wi_s_symb from watch_item,watch_list where wi_wl_id=wl_id and wl_c_id=cust_id;
elsif acct_id!=0 then
open stock_list for 
select hs_s_symb from holding_summary where hs_ca_id=acct_id;
elsif industry_name IS NOT NULL then//程序会执行的分支
open stock_list for
select s_symb 
from industry,company,security
where in_name=industry_name and
co_in_id=in_id and 
co_id between starting_co_id and ending_co_id and
s_co_id=co_id;
end if;old_mkt_cap:=0.0;
new_mkt_cap:=0.0;
pct_change:=0.0;fetch stock_list into symbol;
while stock_list%FOUND loop
select lt_price 
into    new_price
from last_trade
where lt_s_symb=symbol;
select s_num_out 
into sec_num_out
from security
where s_symb=symbol;
select dm_close
into  old_price
from daily_et
where dm_s_symb=symbol and dm_date=to_date(start_date,'yyyy-mm-dd');
old_mkt_cap:=old_mkt_cap+sec_num_out*old_price;
new_mkt_cap:=new_mkt_cap+sec_num_out*new_price;
fetch stock_list into symbol;
end loop;
if old_mkt_cap!=0 then 
pct_change:=100*(new_mkt_cap/old_mkt_cap-1);
status:=0;
else
pct_change:=0.0;
status:=412;
end if;
close stock_list;
dbms_output.put_line(industry_name);
dbms_output.put_line(ending_co_id);
dbms_output.put_line(starting_co_id);
dbms_output.put_line(pct_change);
end;
/
pro*程序如下:
#include <stdio.h>
#include <string.h> EXEC SQL INCLUDE SQLCA;main()
{
EXEC SQL BEGIN DECLARE SECTION;
static char usr[80];
static char wd[80];
long long acct_id;
long long cust_id;
long long start_co_id;
long long ending_co_id;
char start_date[20];
char industry_name[50];
float pct_change;
int status;
char tin_id[3];
EXEC SQL END DECLARE SECTION;memset (usr,'\0',sizeof(usr));
memset(wd,'\0',sizeof(wd));
memset(tin_id,'\0',sizeof(tin_id));
memset(start_date,'\0',sizeof(start_date));
memset(industry_name,'\0',sizeof(industry_name));
acct_id=0;
cust_id=0;start_co_id=4300000001;
ending_co_id=4300000500;
pct_change=12.0;
strcpy(start_date,"2000-10-2");
strcpy(industry_name,"Chemical Manufacturing");
strcpy(usr,"mzq");
strcpy(wd,"youyou");
EXEC SQL CONNECT :usr IDENTIFIED BY :wd;
printf("\nConnected to Oracle as user :%s\n",usr);
if(sqlca.sqlcode!=0)
{
printf("link error!");
}
else
{
printf("link ok!\n");
EXEC SQL SELECT in_id INTO :tin_id FROM  industry WHERE in_name=:industry_name;
EXEC SQL EXECUTE
BEGIN
MarketWatchFrame1(:acct_id,:cust_id,:ending_co_id,:industry_name,
:start_date,:start_co_id,:pct_change,:status);
END;
END-EXEC;
printf("%s\n",tin_id);
}
exit(0);
}当我在pl/sql块里调用存储过程时,能正常执行(故觉得存储过程没有问题),如下:
testMWF1.sql:
declare
acct_id  number(11);
cust_id  number(11);
ending_co_id  number(11);
industry_name  varchar2(50);
start_date  varchar2(15);
starting_co_id  number(11);
pct_change  number(3,2);
status  integer;
begin
acct_id:=0;
cust_id:=0;
industry_name:='Chemical Manufacturing';
start_date:='2000-10-2';
ending_co_id:=4300000500;
starting_co_id:=4300000001;
MarketWatchFrame1(acct_id,cust_id,ending_co_id,industry_name,start_date,starting_co_id,pct_change,status);
end;
/
start testMWF1.sql
Chemical Manufacturing
4300000500
4300000001
2.535575643484019953845736214618053151
但是当我执行pro*c程序时,存储过程并没有执行,运行结果如下:
Connected to Oracle as user :mzq
link ok!
CM
早上就遇到了这个问题,一直没解决,实在找不到原因,故由来此请众位指点!