-會員績效分析：機構名稱 本月會員來客數 上月會員來客數 相比
--本月有效會員 上月有效會員 相比 本月會員客單 上月會員客單 相比
--本月來店頻率 上月來店頻率 相比 本月會員銷售占比 上月會員銷售占比 相比
--本月會員消費額 上月會員消費額 相比
--備注片區 上月總銷售 上月新增會員
---------------------------------------------------------------------------------------------------
ALTER proc [dbo].[HFYK_bbfx_hyjx]
@ksrq char(10)='', --開始日期
@jsrq char(10)='', --截止日期
@xl char(2)='',
@duifbsh varchar(11) = '%', --分店標識
@entid varchar(11)=''
as
---exec hfyk_bbfx_hyjx '2016-04-01','2016-04-30','A','','E1N3SZFNIB6'
--select * from entdoc
--declare @ksrq char(10)
--declare @jsrq char(10)
--本月會員績效
IF (@DUIFBSH = '' OR EXISTS(SELECT 1 FROM ORGDOC where ISORG='Y' AND ORGID = @duifbsh))
BEGIN
SET @duifbsh = '%'
END
create table #byhyjx
(
fdbs varchar(20) null default '',
byhylks decimal(14,2) null default 0,
byyxhy decimal(14,2) null default 0,
byhykdj decimal(14,2) null default 0,
byhyldpl decimal(14,2) null default 0,
byhyxszb decimal(14,2) null default 0,
byhyxshe decimal(14,2) null default 0,
byxshe decimal(14,2) null default 0,
byxzhy decimal(14,2) null default 0,
byprofit decimal(14,2) null default 0,
byprofitrate decimal(14,2) null default 0
)
--上月會員績效
create table #syhyjx
(
fdbs varchar(20) null default '',
syhylks decimal(14,2) null default 0,
syyxhy decimal(14,2) null default 0,
syhykdj decimal(14,2) null default 0,
syhyldpl decimal(14,2) null default 0,
syhyxszb decimal(14,2) null default 0,
syhyxshe decimal(14,2) null default 0,
syxshe decimal(14,2) null default 0,
syxzhy decimal(14,2) null default 0,
syprofit decimal(14,2) null default 0,
syprofitrate decimal(14,2) null default 0
)
--select @ksrq='2012-07-01',@jsrq='2012-07-19'
if @xl='A'
begin
insert into #byhyjx(fdbs,byhylks,byyxhy,byhykdj,byhyxszb,byhyxshe,byxshe,byxzhy,byprofit,byprofitrate)
select a.ORGID as duifbsh,b.byhylks,b.byyxhy,b.byhykdj,b.byhyxshe/e.byxshe AS byhyxszb,b.byhyxshe,e.byxshe,
isnull(d.byxzhy,0) as byxzhy,b.profit,b.profitrate
from ORGDOC a
left join (select entid,OrgId as fdbs,count(cardid) byhylks,isnull(count(distinct cardid),0) as byyxhy,SUM(PaidInAmt) AS byhyxshe,
SUM(profit) as profit,round(SUM(profit)/SUM(PaidInAmt),2) AS profitrate,
case when count(cardid)=0 then 0 else sum(PaidInAmt)/count(cardid) end byhykdj
from retbillmt where cardid<>'' AND Dates>=@ksrq and Dates<=@jsrq
group by entid,OrgId) b on a.ORGID=b.fdbs and a.ENTID=b.EntId
left join (select entid,OrgId as fdbs,SUM(PaidInAmt) AS byxshe
from retbillmt WHERE Dates>=@ksrq and Dates<=@jsrq
group by entid,OrgId) e on a.ORGID=e.fdbs and a.ENTID=e.EntId
left join (select entid,fkorgid,isnull(count(cardid),0) as byxzhy from CARDDOC
where fakrq>=@ksrq and fakrq<=@jsrq group by entid,fkorgid) d on a.ENTID=d.EntId AND a.ORGID=d.fkorgid
where a.ORGID like @duifbsh
--上月
insert into #syhyjx(fdbs,syhylks,syyxhy,syhykdj,syhyxszb,syhyxshe,syxshe,syxzhy,syprofit,syprofitrate)
select a.ORGID as duifbsh,b.byhylks,b.byyxhy,b.byhykdj,b.byhyxshe/e.byxshe AS byhyxszb,b.byhyxshe,e.byxshe,
isnull(d.byxzhy,0) as byxzhy,b.profit,b.profitrate
from ORGDOC a
left join (select entid,OrgId as fdbs,count(cardid) byhylks,isnull(count(distinct cardid),0) as byyxhy,SUM(PaidInAmt) AS byhyxshe,
SUM(profit) as profit,round(SUM(profit)/SUM(PaidInAmt),2) AS profitrate,
case when count(cardid)=0 then 0 else sum(PaidInAmt)/count(cardid) end byhykdj
from retbillmt where cardid<>'' AND Dates>=convert(char(10),dateadd(mm,-1,@ksrq),121)and Dates<=convert(char(10),dateadd(mm,-1,@jsrq),121)
group by entid,OrgId) b on a.ORGID=b.fdbs and a.ENTID=b.EntId
left join (select entid,OrgId as fdbs,SUM(PaidInAmt) AS byxshe,COUNT(billno) AS bylks
from retbillmt WHERE Dates>=convert(char(10),dateadd(mm,-1,@ksrq),121) and Dates<=convert(char(10),dateadd(mm,-1,@jsrq),121)
group by entid,OrgId) e on a.ORGID=e.fdbs and a.ENTID=e.EntId
left join (select entid,fkorgid,isnull(count(cardid),0) as byxzhy from CARDDOC
where fakrq>=convert(char(10),dateadd(mm,-1,@ksrq),121) and fakrq<=convert(char(10),dateadd(mm,-1,@jsrq),121) group by entid,fkorgid) d on a.ENTID=d.EntId AND a.ORGID=d.fkorgid
where a.ORGID like @duifbsh
select a.orgno,a.orgcode,a.ORGNAME ,
byxzhy as [新增會員|本月],syxzhy as [新增會員|上月],byxzhy-isnull(syxzhy,0) as [新增會員|比較],
byyxhy as [有效會員|本月],syyxhy as [有效會員|上月],byyxhy -isnull(syyxhy,0) as [有效會員|比較],
byhylks as [會員來客數|本月],syhylks as [會員來客數|上月],byhylks -isnull(syhylks,0) as [會員來客數|比較],
byhykdj as [會員客單價|本月],syhykdj as [會員客單價|上月],byhykdj-isnull(syhykdj,0) as [會員客單價|比較],
byxshe as [銷售總額|本月], syxshe as [銷售總額|上月],byxshe-isnull(syxshe,0) as [銷售總額|比較],
byhyxshe as [會員銷售額|本月],syhyxshe as [會員銷售額|上月],byhyxshe-isnull(syhyxshe,0) as [會員銷售額|比較],
byhyxszb as [會員銷售占比|本月],syhyxszb as [會員銷售占比|上月],byhyxszb-isnull(syhyxszb,0)as [會員銷售占比|比較],
byprofit as [會員毛利|本月],syprofit as [會員毛利|上月],byprofit-isnull(syprofit,0) as [會員毛利|比較],
byprofitrate as [會員毛利率|本月],syprofitrate as [會員毛利率|上月],a.entid
from ORGDOC a ,#byhyjx b,#syhyjx c
where a.ORGID=b.fdbs and a.ORGID=c.fdbs AND a.ENTID=@entid AND a.IsOrg<>'Y'
order by a.ORGID
drop table #byhyjx
drop table #syhyjx
end
uj5u.com熱心網友回復：
SQL SERVER的存盤程序之中 SELECT * FROM XXX可以實作，存盤程序會回傳一個資料集；ORACLE是沒有辦法這樣直接實作，在ORACLE內，需要提前創建好結構表，然后做INSERT 到結構表，作為資料集。
uj5u.com熱心網友回復：
我想使用的是游標來實作SELECT ，但是不知道怎么建立uj5u.com熱心網友回復：
我想使用的是游標來實作SELECT ，但是不知道怎么建立
uj5u.com熱心網友回復：
可以使用游標來回傳；下面結構表保存的辦法；游標的話，你百度一下寫法就好；
--ORACLE 沒有#TABLE 這樣的臨時表，需要創建物體表；
--下面是在當前USER下創建的表和存盤程序；
CREATE TABLE BYHYJX /*本月會員績效*/
(fdbs VARCHAR(20) null default '',
byhylks NUMBER(14,2) null default 0,
byyxhy NUMBER(14,2) null default 0,
byhykdj NUMBER(14,2) null default 0,
byhyldpl NUMBER(14,2) null default 0,
byhyxszb NUMBER(14,2) null default 0,
byhyxshe NUMBER(14,2) null default 0,
byxshe NUMBER(14,2) null default 0,
byxzhy NUMBER(14,2) null default 0,
byprofit NUMBER(14,2) null default 0,
byprofitrate NUMBER(14,2) null default 0);
CREATE TABLE SYHYJX /*上月會員績效*/
(fdbs VARCHAR(20) null default '',
syhylks NUMBER(14,2) null default 0,
syyxhy NUMBER(14,2) null default 0,
syhykdj NUMBER(14,2) null default 0,
syhyldpl NUMBER(14,2) null default 0,
syhyxszb NUMBER(14,2) null default 0,
syhyxshe NUMBER(14,2) null default 0,
syxshe NUMBER(14,2) null default 0,
syxzhy NUMBER(14,2) null default 0,
syprofit NUMBER(14,2) null default 0,
syprofitrate NUMBER(14,2) null default 0);
CREATE TABLE JIEGUO /*結果表*/
(....../*根據你的結果情況創建資料表*/...... )
/*下面是存盤程序*/
CREATE OR REPLACE HFYK_bbfx_hyjx(V_KSRQ IN CHAR(10) ,
V_JSRQ IN CHAR(10) ,
V_RETURN OUT INTEGER)
--會員績效分析：機構名稱 本月會員來客數 上月會員來客數 相比
--本月有效會員 上月有效會員 相比 本月會員客單 上月會員客單 相比
--本月來店頻率 上月來店頻率 相比 本月會員銷售占比 上月會員銷售占比 相比
--本月會員消費額 上月會員消費額 相比
--備注片區 上月總銷售 上月新增會員
--V_KSRQ --開始日期
--V_JSRQ --截止日期
IS
V_DUIFBSH VARCHAR(11) := '%@%'; --分店標識 @
V_ENTID VARCHAR(11) := '';
---exec hfyk_bbfx_hyjx '2016-04-01','2016-04-30','A','','E1N3SZFNIB6'
--select * from entdoc
--declare @ksrq char(10)
--declare @jsrq char(10)
--本月會員績效
BEGIN
/*IF (@DUIFBSH = '' OR EXISTS(SELECT 1 FROM ORGDOC where ISORG='Y' AND ORGID = @duifbsh))
BEGIN
SET @duifbsh = '%'
END
if @xl='A'
begin*/
EXECUTE IMMEDIATE 'TRUNCATE TABLE BYHYJX' ;
INSERT INTO BYHYJX
(FDBS,
BYHYLKS,
BYYXHY,
BYHYKDJ,
BYHYXSZB,
BYHYXSHE,
BYXSHE,
BYXZHY,
BYPROFIT,
BYPROFITRATE)
SELECT A.ORGID AS DUIFBSH,
B.BYHYLKS,
B.BYYXHY,
B.BYHYKDJ,
B.BYHYXSHE / E.BYXSHE AS BYHYXSZB,
B.BYHYXSHE,
E.BYXSHE,
NVL(D.BYXZHY, 0) AS BYXZHY,
B.PROFIT,
B.PROFITRATE
FROM ORGDOC A
LEFT JOIN (SELECT ENTID,
ORGID AS FDBS,
COUNT(CARDID) BYHYLKS,
NVL(COUNT(DISTINCT CARDID), 0) AS BYYXHY,
SUM(PAIDINAMT) AS BYHYXSHE,
SUM(PROFIT) AS PROFIT,
ROUND(SUM(PROFIT) / SUM(PAIDINAMT), 2) AS PROFITRATE,
CASE
WHEN COUNT(CARDID) = 0 THEN
0
ELSE
SUM(PAIDINAMT) / COUNT(CARDID)
END BYHYKDJ
FROM RETBILLMT
WHERE CARDID <> ''
AND DATES >= V_KSRQ
AND DATES <= V_JSRQ
GROUP BY ENTID, ORGID) B
ON A.ORGID = B.FDBS
AND A.ENTID = B.ENTID
LEFT JOIN (SELECT ENTID, ORGID AS FDBS, SUM(PAIDINAMT) AS BYXSHE
FROM RETBILLMT
WHERE DATES >= V_KSRQ
AND DATES <= V_JSRQ
GROUP BY ENTID, ORGID) E
ON A.ORGID = E.FDBS
AND A.ENTID = E.ENTID
LEFT JOIN (SELECT ENTID, FKORGID, NVL(COUNT(CARDID), 0) AS BYXZHY
FROM CARDDOC
WHERE FAKRQ >= V_KSRQ
AND FAKRQ <= V_JSRQ
GROUP BY ENTID, FKORGID) D
ON A.ENTID = D.ENTID
AND A.ORGID = D.FKORGID
WHERE A.ORGID LIKE V_DUIFBSH ;
COMMIT;
--上月
EXECUTE IMMEDIATE 'TRUNCATE TABLE SYHYJX' ;
INSERT INTO SYHYJX
(FDBS,
SYHYLKS,
SYYXHY,
SYHYKDJ,
SYHYXSZB,
SYHYXSHE,
SYXSHE,
SYXZHY,
SYPROFIT,
SYPROFITRATE)
SELECT A.ORGID AS DUIFBSH,
B.BYHYLKS,
B.BYYXHY,
B.BYHYKDJ,
B.BYHYXSHE / E.BYXSHE AS BYHYXSZB,
B.BYHYXSHE,
E.BYXSHE,
NVL(D.BYXZHY, 0) AS BYXZHY,
B.PROFIT,
B.PROFITRATE
FROM ORGDOC A
LEFT JOIN (SELECT ENTID,
ORGID AS FDBS,
COUNT(CARDID) BYHYLKS,
NVL(COUNT(DISTINCT CARDID), 0) AS BYYXHY,
SUM(PAIDINAMT) AS BYHYXSHE,
SUM(PROFIT) AS PROFIT,
ROUND(SUM(PROFIT) / SUM(PAIDINAMT), 2) AS PROFITRATE,
CASE
WHEN COUNT(CARDID) = 0 THEN
0
ELSE
SUM(PAIDINAMT) / COUNT(CARDID)
END BYHYKDJ
FROM RETBILLMT
WHERE CARDID <> ''
AND DATES >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
GROUP BY ENTID, ORGID) B
ON A.ORGID = B.FDBS
AND A.ENTID = B.ENTID
LEFT JOIN (SELECT ENTID,
ORGID AS FDBS,
SUM(PAIDINAMT) AS BYXSHE,
COUNT(BILLNO) AS BYLKS
FROM RETBILLMT
WHERE DATES >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
GROUP BY ENTID, ORGID) E
ON A.ORGID = E.FDBS
AND A.ENTID = E.ENTID
LEFT JOIN (SELECT ENTID, FKORGID, NVL(COUNT(CARDID), 0) AS BYXZHY
FROM CARDDOC
WHERE DATES >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
GROUP BY ENTID, FKORGID) D
ON A.ENTID = D.ENTID
AND A.ORGID = D.FKORGID
WHERE A.ORGID LIKE V_DUIFBSH;
COMMIT;
INSERT INTO JIEGUO
SELECT A.ORGNO,
A.ORGCODE,
A.ORGNAME,
BYXZHY,
SYXZHY,
BYXZHY - NVL(SYXZHY, 0) OP1,
BYYXHY,
SYYXHY,
BYYXHY - NVL(SYYXHY, 0) OP2,
BYHYLKS,
SYHYLKS,
BYHYLKS - NVL(SYHYLKS, 0) OP3,
BYHYKDJ,
SYHYKDJ,
BYHYKDJ - NVL(SYHYKDJ, 0) OP4,
BYXSHE,
SYXSHE,
BYXSHE - NVL(SYXSHE, 0) OP5,
BYHYXSHE,
SYHYXSHE,
BYHYXSHE - NVL(SYHYXSHE, 0) OP6,
BYHYXSZB,
SYHYXSZB,
BYHYXSZB - NVL(SYHYXSZB, 0) OP7,
BYPROFIT,
SYPROFIT,
BYPROFIT - NVL(SYPROFIT, 0) OP8,
BYPROFITRATE,
SYPROFITRATE,
A.ENTID
FROM ORGDOC A, BYHYJX B, SYHYJX C
WHERE A.ORGID = B.FDBS
AND A.ORGID = C.FDBS
AND A.ENTID = V_ENTID
AND A.ISORG <> 'Y'
ORDER BY A.ORGID;
COMMIT;
V_RETURN :=0;
END;
--到此結束了，兩個輸入變數V_KSRQ，V_JSRQ； 1個輸出變數V_RETURN；
--中間的引數V_DUIFBSH和V_ENTID 就直接賦值你需要的值，資料保存在結果表；然后你操作結果表就好；
uj5u.com熱心網友回復：
只能寫游標不讓寫中間表，呼叫中間表的方式uj5u.com熱心網友回復：
找本書，對著語法看看，然后再看看能不能提高效率轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103493.html
標籤：開發
上一篇：ora-01410