-- Create table
create global temporary table TTMPSALPACKPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
YSTOTAL NUMBER(19,2),
SSTOTAL NUMBER(19,2),
YHTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
On Commit Delete Rows;-- Create table
create global temporary table TTMPSALPACKMXPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PACKCODE VARCHAR2(20),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
DEPID NUMBER(19),
DEPCODE VARCHAR2(10),
DEPNAME VARCHAR2(100),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
SSTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
on commit Delete rows;
-----整理商品包
Insert into TTMPSALPACKPLU_TMPlsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,PluID,PluCode,PluName,ExPluCode,
BarCode,Unit,Spec,
Price,FsPrice,XsCount,YsTotal,SsTotal,YhTotal,XfType,
VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,A.PluID,A.PluCode,A.PluName,A.ExPluCode,
A.BarCode,A.Unit,A.Spec,
A.Price,A.FsPrice,A.XsCount,A.YsTotal,A.SsTotal,A.YhTotal,A.XfType,
B.VipCardNo,A.CxBillNo,A.HyBillNo
From tSalSalePlu A,tSalSale B
Where A.TranType='1' and A.PackType='2'
and B.OrgCode=A.OrgCode and B.SaleNo=A.SaleNo;
--对商品包数据进行拆包
Insert into tTmpSalPackMxPlu_Tmplsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,
PackCode,PluID,PluCode,PluName,ExPluCode,BarCode,Unit,Spec,
DepID,DepCode,DepName,Price,FsPrice,XsCount,SsTotal,
XfType,VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,
A.PluID as PackCode,B.PluID,B.PluCode,B.PluName,B.ExPluCode,B.BarCode,B.Unit,B.Spec,
0 as DepID,null as DepCode,null as DepName,B.Price,B.Price as FsPrice,--下面更新部门和售价
A.XsCount*B.PackageCount as XsCount,Round(A.SsTotal*B.PriceRate/100.0,2) as SsTotal,
A.XfType,A.VipCardNo,A.CxBillNo,A.HyBillNo
From tTmpSalPackPlu_Tmplsl A,tSkuPluPackageBody B
Where B.PackageID=A.PluID;--更新拆包后明细商品的部门和售价
Update tTmpSalPackMxPlu_Tmplsl A
Set (DepID,DepCode,DepName,Price,FsPrice)
=(Select B.DepID,(Select DepCode From tOrgDept Where DepID=B.DepID) as DepCode,
(Select DepName From tOrgDept Where DepID=B.DepID) as DepName,
B.Price,B.Price
From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID)
Where Exists(Select 1 From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID);
select Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1, 2) as DepCode, Dep.DepName as DepName,
sum(Rpt.XsCount) as XsCount,
sum(Rpt.YsTotal) as YsTotal,
sum(Rpt.SsTotal) as SsTotal,
sum(Rpt.YhTotal) as YhTotal
from ( Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE,FsPrice*XsCount-SsTotal As YhTotal,FsPrice*XsCount As YsTotal
from TTMPSALPACKMXPLU_TMPlsl
Union All
Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE ,YhTotal,YsTotal
from tSalSalePlu
Where Packtype<>'2'
) Rpt,
tOrgManage Org, tOrgDept Dep
where Rpt.OrgCode=Org.OrgCode
and Rpt.OrgCode=Dep.OrgCode
And Org.OrgCode=Dep.OrgCode
and SUBSTR(Rpt.DepCode, 1, 2)=Dep.DepCode and dep.isactive=1
And Exists (Select * From tSalSale S Where S.OrgCode=Rpt.OrgCode And S.saleNo=Rpt.saleNo And S.JzDate Is Null)---只查询未结账流水
Group by Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1,2), Dep.DepName;drop table TTMPSALPACKPLU_TMPlsl;
drop table TTMPSALPACKMXPLU_TMPlsl;Commit ;如题,在PLSQL中可以执行,但放到ADOQuery中就不能执行了
create global temporary table TTMPSALPACKPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
YSTOTAL NUMBER(19,2),
SSTOTAL NUMBER(19,2),
YHTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
On Commit Delete Rows;-- Create table
create global temporary table TTMPSALPACKMXPLU_TMPlsl
(
ORGCODE VARCHAR2(10),
SALENO VARCHAR2(20),
XSDATE DATE,
PAGENO NUMBER(10),
LNNO NUMBER(10),
PACKCODE VARCHAR2(20),
PLUID NUMBER(19),
PLUCODE VARCHAR2(20),
PLUNAME VARCHAR2(40),
EXPLUCODE VARCHAR2(20),
BARCODE VARCHAR2(20),
UNIT VARCHAR2(6),
SPEC VARCHAR2(40),
DEPID NUMBER(19),
DEPCODE VARCHAR2(10),
DEPNAME VARCHAR2(100),
PRICE NUMBER(19,4),
FSPRICE NUMBER(19,4),
XSCOUNT NUMBER(19,4),
SSTOTAL NUMBER(19,2),
XFTYPE VARCHAR2(1),
VIPCARDNO VARCHAR2(40),
CXBILLNO VARCHAR2(20),
HYBILLNO VARCHAR2(20)
)
on commit Delete rows;
-----整理商品包
Insert into TTMPSALPACKPLU_TMPlsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,PluID,PluCode,PluName,ExPluCode,
BarCode,Unit,Spec,
Price,FsPrice,XsCount,YsTotal,SsTotal,YhTotal,XfType,
VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,A.PluID,A.PluCode,A.PluName,A.ExPluCode,
A.BarCode,A.Unit,A.Spec,
A.Price,A.FsPrice,A.XsCount,A.YsTotal,A.SsTotal,A.YhTotal,A.XfType,
B.VipCardNo,A.CxBillNo,A.HyBillNo
From tSalSalePlu A,tSalSale B
Where A.TranType='1' and A.PackType='2'
and B.OrgCode=A.OrgCode and B.SaleNo=A.SaleNo;
--对商品包数据进行拆包
Insert into tTmpSalPackMxPlu_Tmplsl nologging (OrgCode,SaleNo,XsDate,PageNo,LnNo,
PackCode,PluID,PluCode,PluName,ExPluCode,BarCode,Unit,Spec,
DepID,DepCode,DepName,Price,FsPrice,XsCount,SsTotal,
XfType,VipCardNo,CxBillNo,HyBillNo)
Select A.OrgCode,A.SaleNo,A.XsDate,A.PageNo,A.LnNo,
A.PluID as PackCode,B.PluID,B.PluCode,B.PluName,B.ExPluCode,B.BarCode,B.Unit,B.Spec,
0 as DepID,null as DepCode,null as DepName,B.Price,B.Price as FsPrice,--下面更新部门和售价
A.XsCount*B.PackageCount as XsCount,Round(A.SsTotal*B.PriceRate/100.0,2) as SsTotal,
A.XfType,A.VipCardNo,A.CxBillNo,A.HyBillNo
From tTmpSalPackPlu_Tmplsl A,tSkuPluPackageBody B
Where B.PackageID=A.PluID;--更新拆包后明细商品的部门和售价
Update tTmpSalPackMxPlu_Tmplsl A
Set (DepID,DepCode,DepName,Price,FsPrice)
=(Select B.DepID,(Select DepCode From tOrgDept Where DepID=B.DepID) as DepCode,
(Select DepName From tOrgDept Where DepID=B.DepID) as DepName,
B.Price,B.Price
From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID)
Where Exists(Select 1 From tSkuPluEx B
where B.OrgCode=A.OrgCode and B.PluID=A.PluID);
select Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1, 2) as DepCode, Dep.DepName as DepName,
sum(Rpt.XsCount) as XsCount,
sum(Rpt.YsTotal) as YsTotal,
sum(Rpt.SsTotal) as SsTotal,
sum(Rpt.YhTotal) as YhTotal
from ( Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE,FsPrice*XsCount-SsTotal As YhTotal,FsPrice*XsCount As YsTotal
from TTMPSALPACKMXPLU_TMPlsl
Union All
Select ORGCODE ,SALENO,XSDATE,PAGENO,LNNO,PACKCODE,
PLUID,PLUCODE,PLUNAME,EXPLUCODE,BARCODE,UNIT,
SPEC,DEPID,DEPCODE,DEPNAME,PRICE,FSPRICE,XSCOUNT,
SSTOTAL,XFTYPE ,YhTotal,YsTotal
from tSalSalePlu
Where Packtype<>'2'
) Rpt,
tOrgManage Org, tOrgDept Dep
where Rpt.OrgCode=Org.OrgCode
and Rpt.OrgCode=Dep.OrgCode
And Org.OrgCode=Dep.OrgCode
and SUBSTR(Rpt.DepCode, 1, 2)=Dep.DepCode and dep.isactive=1
And Exists (Select * From tSalSale S Where S.OrgCode=Rpt.OrgCode And S.saleNo=Rpt.saleNo And S.JzDate Is Null)---只查询未结账流水
Group by Rpt.OrgCode, Org.OrgName, SUBSTR(Rpt.DepCode, 1,2), Dep.DepName;drop table TTMPSALPACKPLU_TMPlsl;
drop table TTMPSALPACKMXPLU_TMPlsl;Commit ;如题,在PLSQL中可以执行,但放到ADOQuery中就不能执行了
解决方案 »
- ShockwaveFlash1.CallFunction总提示错误
- TIdTCPServer控件接收数据报错 急!!急!!急!!
- (在线等)请问如何得知windows系统的DPI为96 还是120
- 想找一份程序员的工作,找了一个月,就是没人要!
- 为什么我在clientdataset的ApplyUpdates(0)的时候SQLSERVER总是报这样的错误?SQL Server Error:Cannot create new transaction because
- 关于快速拷贝文件的问题,怎样快速拷贝文件,不是使用COPYFILE,SHELL外壳等函数(各位文件、磁盘高手请进!)
- 哪里下载dev dxdbgrid(delphi5)?
- 如何设置目录的访问权限?
- 有关SQL语句的问题!!!!非常紧急!!包括两个过程!有些难度!!!
- QUESTION:怎样拷贝一个目录?
- 求毕业设计,联系QQ450814688.有效期3天。
- form1中有个listview,想按button按钮执行listview的onSelectItem事件处理程序?
--用户必须有create any table 权限
create or replace package pkg_test
is
type ResultData is ref cursor;
procedure test;
end pkg_test;
/
create or replace package body pkg_test
is
procedure test
is
cur ResultData;
fid varchar2(30);
fnumber int;
sqlstr varchar2(8000);
begin
sqlstr:='create global temporary table tmp( cid VARCHAR2(30),cnumber int )On Commit PRESERVE Rows';
Execute Immediate sqlstr;
Execute Immediate 'insert into tmp values(''1'',1)';
Execute Immediate 'insert into tmp values(''2'',2)';
sqlstr:='select * from tmp';
open cur for sqlstr;
loop
fetch cur into fid,fnumber;
exit when cur%notfound;
dbms_output.put_line( fid||'-'||fnumber );
end loop;
close cur;
end;
end pkg_test;
--测试
declare
i int;
begin
pkg_test.test;
end;
--输出结果
1-1
2-2
问题好像出在ADOQuery不能执行多条oracle语句上