解决oracle绑定变量重复,oracle 动态绑定变量解决方案

当前位置:我的异常网» Oracle开发 » oracle 动态绑定变量解决方案

oracle 动态绑定变量解决方案

www.myexceptions.net  网友分享于：2013-05-24  浏览：14次

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

然后用的时候直接提取

文章评论