求助:我的写法有错吗?ORA-00984,ORA-06512错误..?

Question

DECLARE  
CURSOR c_br IS SELECT * FROM broker_ret; 
CURSOR c_hfhd (p_cuno VARCHAR2) IS        
  SELECT * FROM hs_his.hisfuholddrop     
  WHERE fund_account=p_cuno;   
r_br broker_ret%ROWTYPE;  
r_hfhd  hs_his.hisfuholddrop%ROWTYPE;
BEGIN  
  OPEN c_br;  
     LOOP  
        FETCH c_br INTO r_br;  
        EXIT WHEN c_br%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE('part1 ok!');   
        OPEN c_hfhd(r_br.cust_no);  
            LOOP  
               FETCH c_hfhd INTO r_hfhd;  
               EXIT WHEN c_hfhd%NOTFOUND;  
               IF r_br.ret_mode='1' 
               THEN execute immediate 'insert into broker_fund values                                          
(r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,r_br.business_amount*amt)';
               END IF;
               IF r_br.ret_mode='2' 
               THEN execute immediate 'insert into broker_fund(tx_date,broker_no,cust_no,exch_code,vari_code,ret_mode,amt,fare) values                                                                       
(r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,r_br.total_fare*amt)';
               END IF;
               IF r_br.ret_mode='3' 
               THEN execute immediate 'insert into broker_fund values                                                                       
(r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,(r_br.total_fare-r_br.exch_fare)*amt)';
               END IF;
               IF r_br.ret_mode='4' 
               THEN execute immediate 'insert into broker_fund values                                                                       
(r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,r_br.total_fare*amt)';
               END IF;
            END LOOP;  
        CLOSE c_hfhd;  
        DBMS_OUTPUT.PUT_LINE('part2 ok!');  
     END LOOP;  
  CLOSE c_br;  
END; 
ORA-00984列在此处不允许,ORA-06512错误 在line23..?
我是搞了2个游标嵌套在一起,然后向第3张表里插记录,谁能帮我看看怎么回事..上网插过好久也没有解决...麻烦大家了.谢谢!

Answer 1

execute immediate 'insert into broker_fund values                                                                      
(r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,(r_br.total_fare-r_br.exch_fare)*amt)'
该语句写错了,你的变量在这全变成了字符串了,可以使用
declare 
  ....
  amt1 number;
begin
   ....
   amt1 := r_br.total_fare*amt;
   execute immediate 'insert into broker_fund(tx_date,broker_no,cust_no,exch_code,vari_code,ret_mode,amt,fare) values                                                                      
   (:initdate,:broker_no,:cust_no,:exch_code,:vari_code,:ret_mode,:amt,:total_fare) '
   using r_hfhd.initdate,r_br.broker_no,r_br.cust_no,r_br.exch_code,r_br.vari_code,r_br.ret_mode,r_br.amt,amt1;
   ....
end ;

Answer 2

IF r_br.ret_mode='2'把该情况的sql单独在数据库里执行一下看看有没有什么错误 ...