-会员绩效分析:机构名称 本月会员来客数 上月会员来客数 相比
--本月有效会员 上月有效会员 相比 本月会员客单 上月会员客单 相比
--本月来店频率 上月来店频率 相比 本月会员销售占比 上月会员销售占比 相比
--本月会员消费额 上月会员消费额 相比
--备注片区 上月总销售 上月新增会员
---------------------------------------------------------------------------------------------------
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 = '%'
ENDcreate 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 

解决方案 »

  1.   

    SQL SERVER的存储过程之中 SELECT *  FROM  XXX可以实现,存储过程会返回一个数据集;
    ORACLE是没有办法这样直接实现,在ORACLE内,需要提前创建好结构表,然后做INSERT 到结构表,作为数据集。
      

  2.   

    我想使用的是游标来实现SELECT ,但是不知道怎么建立
      

  3.   

    我想使用的是游标来实现SELECT ,但是不知道怎么建立
      

  4.   

    可以使用游标来返回;下面结构表保存的办法;游标的话,你百度一下写法就好;--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 就直接赋值你需要的值,数据保存在结果表;然后你操作结果表就好;