建立一张表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,但因为查询语句中多次使用,就需要传入多次。有没有办法,使我只传递一次就可以了???
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,但因为查询语句中多次使用,就需要传入多次。有没有办法,使我只传递一次就可以了???
解决方案 »
- 怎么样把一个表的一个字段赋值给另一个字段?
- SQL问题,谢谢!!!
- oracle语句如何强制走索引
- linux pro*c 的编译与生成
- 请问我应该安装什么样的客户端?
- ==== oracle奇怪现象,一个简单的update语句不能执行。 ====
- 监听服务(OracleOraHome81TNSListener)在服务栏里一刷新就没有了,连停止、已启动或都已禁用这个几个字都没有了。
- select deptno into k from dept where dep = :temp 当没有找到时报错,如何处理?
- 关于数据库存储图像的问题:
- 如何设置表空间为默认?谢谢
- 这个更新太诡异了。高手进
- sql递归查询
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
然后用的时候直接提取