oracle 动态绑定变量
建立一张表waste:
create table WASTE
(
  ID NUMBER(18) not null,
  ORIGIN_TYPE CHAR(1) default '0' not null,
  ORIGIN_ID NUMBER(18),
  ACC_TYPE CHAR(1),
  ACC_ID VARCHAR2(20),
  SUB_CODE VARCHAR2(8),
  AMOUNT NUMBER(18),
  CDDIRC CHAR(1),
  WST_TIME DATE,
  CHANN_SETTDATE DATE,
  SYS_SETTDATE DATE,
  SETT_FLAG CHAR(1) default '0' not null
)
动态查询语句：
var_SqlState:='select acc_id, sub_code, (select sum(amount) from waste where
  cddirc =''0''and sys_settdate >= :var_BeginDate and sys_settdate < :var_EndDate
  and acc_id = m.acc_id and sub_code = m.sub_code) as amount_post,
  (select count(*) from waste where 
  cddirc =''0'' and sys_settdate >= :var_BeginDate and sys_settdate < :var_EndDate 
  and acc_id = m.acc_id and sub_code = m.sub_code) as nums_post,
  (select sum(amount) from waste where 
  cddirc =''1'' and sys_settdate >= :var_BeginDate and sys_settdate < :var_EndDate
  and acc_id = m.acc_id and sub_code = m.sub_code) as amount_nega,
  (select count(*) from waste where 
  cddirc =''1'' and sys_settdate >= :var_BeginDate and sys_settdate < :var_EndDate 
  and acc_id = m.acc_id and sub_code = m.sub_code) as nums_nega 
  from waste m where 
  sys_settdate >= :var_BeginDate and sys_settdate < :var_EndDate  
  group by acc_id, sub_code'; 

  OPEN cWaste for var_SqlState using 
  var_BeginDate,var_EndDate,var_BeginDate,var_EndDate,
  var_BeginDate,var_EndDate,var_BeginDate,var_EndDate,
  var_BeginDate,var_EndDate;

其实需要传递的变量只有两个，var_BeginDate,var_EndDate，但因为查询语句中多次使用，就需要传入多次。有没有办法，使我只传递一次就可以了？？？


------解决方案--------------------
这样试试看：
[code=SQL][/code]
DECLARE
CURSOR waste_cursor(begin DATE,end DATE) IS
select acc_id, sub_code, (select sum(amount) from waste where
cddirc =''0''and sys_settdate >= :begin and sys_settdate < :end
and acc_id = m.acc_id and sub_code = m.sub_code) as amount_post,
(select count(*) from waste where
cddirc =''0'' and sys_settdate >= :begin and sys_settdate < :end
and acc_id = m.acc_id and sub_code = m.sub_code) as nums_post,
(select sum(amount) from waste where
cddirc =''1'' and sys_settdate >= :begin and sys_settdate < :end
and acc_id = m.acc_id and sub_code = m.sub_code) as amount_nega,
(select count(*) from waste where
cddirc =''1'' and sys_settdate >= :begin and sys_settdate < :end
and acc_id = m.acc_id and sub_code = m.sub_code) as nums_nega
from waste m where
sys_settdate >= :begin and sys_settdate < :end
group by acc_id, sub_code
然后用的时候直接提取