本帖最后由 cimlan 于 2014-1-22 21:13 编辑
因为刚接触oracle,很多东西都是网上照葫芦画瓢写的，直接上代码了：跪求高手帮助纠结了几天了 实在没辙了！～～～其中 orgcode 门店组织 ，xscount 销售数量 ，ystotal 应收金额,sstotal 实收金额,yhtotal 优惠金额
其中临时表创建的用户是 readhscmp,执行以下代码 登录的用户也是 readhscmpdeclare
vs_bgndate varchar2(20);
vs_enddate varchar2(20);
vs_orgcode TRTMP_ORGPLUSALCOUNT0002.orgcode%type;
vs_tmpDate varchar2(10);
vs_tSalSalePluYYYY varchar2(20);
vs_SQL varchar2(12000);
vs_tSalSalePluYYYYY varchar2(20);
vs_xscount TRTMP_ORGPLUSALCOUNT0002.xscount%type;
vs_ystotal TRTMP_ORGPLUSALCOUNT0002.ystotal%type;
vs_sstotal TRTMP_ORGPLUSALCOUNT0002.sstotal%type;
vs_yhtotal TRTMP_ORGPLUSALCOUNT0002.yhtotal%type;
--定义游标
CURSOR c_cursor is select orgcode,xscount,ystotal,sstotal,yhtotal from TRTMP_ORGPLUSALCOUNT0002;
begin
vs_bgndate:='2014-01-01';
vs_enddate:='2014-01-21';
vs_orgcode:='10054';
vs_tmpdate:=vs_bgndate;
vs_tSalSalePluYYYY:='tSalSalePlu'||substr(vs_tmpdate,1,4)||substr(vs_tmpdate,6,2);
vs_tmpdate:=to_char(add_months(to_date(vs_tmpdate,'yyyy-mm-dd'),1),'yyyy-mm-dd');
vs_tSalSalePluYYYYY:='tSalSalePlu'||substr(vs_tmpdate,1,4)||substr(vs_tmpdate,6,2);
/*--创建临时表创建的用户是 readhscmp
create global temporary table trtmp_OrgPluSalCount0002
(
ORGCODE VARCHAR2(20) not null,
XSCOUNT NUMBER(19,4) default 0 not null,
YSTOTAL NUMBER(19,2) default 0 not null,
SSTOTAL NUMBER(19,2) default 0 not null,
YHTOTAL NUMBER(19,2) default 0 not null
)
on commit preserve rows;
*/
--清除临时表数据
delete from TRTMP_ORGPLUSALCOUNT0002;
--将每月数据插入到临时表
vs_SQL:='insert into TRTMP_ORGPLUSALCOUNT0002(orgcode,xscount,ystotal,sstotal,yhtotal)
select (a.orgcode ||''-''||b.orgname) as orgcode,sum(xscount) as xscount,sum(ystotal) as ystotal,sum(sstotal) as sstotal,sum(yhtotal) as yhtotal from (
(select orgcode ,sum(xscount) as xscount,sum(ystotal) as ystotal,sum(sstotal) as sstotal,sum(yhtotal) as yhtotal from tsalsaleplu where to_char(xsdate,''yyyy-mm-dd'') >='''||vs_bgndate||''' and to_char(xsdate,''yyyy-mm-dd'') ='''||vs_bgndate||''' and to_char(xsdate,''yyyy-mm-dd'')''0'' group by orgcode)
union all
(select orgcode ,sum(xscount) as xscount,sum(ystotal) as ystotal,sum(sstotal) as sstotal,sum(yhtotal) as yhtotal from '||vs_tSalSalePluYYYYY||' where to_char(xsdate,''yyyy-mm-dd'') >='''||vs_bgndate||''' and to_char(xsdate,''yyyy-mm-dd'')''0'' group by orgcode)) a ,tOrgManage b where a.orgcode = b.orgcode and a.orgcode like '''||vs_orgcode||''' group by a.orgcode ||''-''||b.orgname';
--执行 vs_SQL
execute immediate(vs_SQL);
--declare result_set cursor ;
--打开游标
begin
open c_cursor;
FETCH c_cursor INTO vs_orgcode, vs_xscount,vs_ystotal,vs_sstotal,vs_yhtotal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(to_char(vs_orgcode)||'---'||to_char(vs_xscount)||'---'||to_char(vs_ystotal)||'---'||to_char(vs_sstotal)||'---'||to_char(vs_yhtotal));
FETCH c_cursor INTO vs_orgcode, vs_xscount,vs_ystotal,vs_sstotal,vs_yhtotal;
end loop;
CLOSE c_cursor;
end;
END;复制代码
该代码执行后就报错 :
ora-00942;表或视图不存在
但是下面 接着运行 都能查的出来 也没有报错
select * from tsalsaleplu;
select * from tsalsaleplu201401;
select * from TRTMP_ORGPLUSALCOUNT0002;
|
