Oracle综合练习二

--每种产品月销售排名

--要求：存储过程完成
--输入：年份
--输出：要求结果，第一行表头为 产品型号，1月，2月，3月……12月，合计

--销售明细表
--序号   型号      销售日期     数量  销售地区
--1     DFL4250   2011-3-4    10           地区1
--2     DFL4251   2011-4-5    200         地区2
--3     DFL5250   2011-6-7    49           地区3

........

CREATE SEQUENCE TEST_PRO_SEQUENCE
MINVALUE 1
MAXVALUE 99999999
START WITH 1
INCREMENT BY 1;

CREATE TABLE TEST_PRO(
TEST_PRO_SEQUID NUMBER(10),
TEST_PRO_PROID VARCHAR2(7),
TEST_PRO_SALEDATE DATE,
TEST_PRO_NUMBER NUMBER(10),
TEST_PRO_ADDRESS VARCHAR2(20),
CONSTRAINT PK_TEST_PRO PRIMARY KEY(TEST_PRO_SEQUID)
);   

CREATE OR REPLACE PROCEDURE TEST_PRO_INSERT
(V_TEST_PRO_SEQID NUMBER,V_TEST_PRO_PROID VARCHAR2,V_TEST_PRO_SALEDATE DATE,V_TEST_PRO_NUMBER NUMBER,V_TEST_PRO_ADDRESS VARCHAR2) 
IS 
BEGIN
  INSERT INTO TEST_PRO VALUES(V_TEST_PRO_SEQID,V_TEST_PRO_PROID,V_TEST_PRO_SALEDATE,V_TEST_PRO_NUMBER,V_TEST_PRO_ADDRESS);
  
  COMMIT;
  END;
  
  --测试
  BEGIN
    --TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4250',TO_DATE('2011-3-4','YYYY/MM/DD'),10,'地区一');   
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4251',TO_DATE('2011-4-4','YYYY/MM/DD'),10,'地区一');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4253',TO_DATE('2011-5-4','YYYY/MM/DD'),100,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4254',TO_DATE('2011-6-4','YYYY/MM/DD'),120,'地区一');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4255',TO_DATE('2011-7-4','YYYY/MM/DD'),110,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4256',TO_DATE('2011-8-4','YYYY/MM/DD'),150,'地区一');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4251',TO_DATE('2011-9-4','YYYY/MM/DD'),180,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4253',TO_DATE('2011-10-4','YYYY/MM/DD'),110,'地区一');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4254',TO_DATE('2011-11-4','YYYY/MM/DD'),140,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4255',TO_DATE('2011-12-4','YYYY/MM/DD'),180,'地区三');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4257',TO_DATE('2012-1-4','YYYY/MM/DD'),190,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4258',TO_DATE('2012-2-4','YYYY/MM/DD'),170,'地区三');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4259',TO_DATE('2012-3-4','YYYY/MM/DD'),190,'地区二');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4266',TO_DATE('2012-4-4','YYYY/MM/DD'),120,'地区三');
    TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4259',TO_DATE('2012-5-4','YYYY/MM/DD'),110,'地区三');
   TEST_PRO_INSERT(TEST_PRO_SEQUENCE.NEXTVAL,'DFL4259',TO_DATE('2012-5-7','YYYY/MM/DD'),110,'地区三');
   end;

插入结果如下：

decode函数和sum聚合函数以及group by结合使用

SELECT TEST_PRO_PROID AS 产品型号,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'01',TEST_PRO_NUMBER,0)) AS 一月, 
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'02',TEST_PRO_NUMBER,0)) AS 二月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'03',TEST_PRO_NUMBER,0)) AS 三月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'04',TEST_PRO_NUMBER,0)) AS 四月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'05',TEST_PRO_NUMBER,0)) AS 五月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'06',TEST_PRO_NUMBER,0)) AS 六月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'07',TEST_PRO_NUMBER,0)) AS 七月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'08',TEST_PRO_NUMBER,0)) AS 八月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'09',TEST_PRO_NUMBER,0)) AS 九月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'10',TEST_PRO_NUMBER,0)) AS 十月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'11',TEST_PRO_NUMBER,0)) AS 十一月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'12',TEST_PRO_NUMBER,0)) AS 十二月,
  SUM(TEST_PRO_NUMBER) AS 总数
  from TEST_PRO 
  where TO_CHAR(TEST_PRO_SALEDATE,'YYYY') = '2011'
  GROUP BY TEST_PRO_PROID;

结果如下：

存储过程：

 create or replace procedure TEST_PRO_SALES_MONTHS
(IN_YEAR IN VARCHAR2,RESULTSET OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN RESULTSET FOR 
  SELECT TEST_PRO_PROID AS 产品型号,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'01',TEST_PRO_NUMBER,'0')) AS 一月, 
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'02',TEST_PRO_NUMBER,'0')) AS 二月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'03',TEST_PRO_NUMBER,'0')) AS 三月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'04',TEST_PRO_NUMBER,'0')) AS 四月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'05',TEST_PRO_NUMBER,'0')) AS 五月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'06',TEST_PRO_NUMBER,'0')) AS 六月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'07',TEST_PRO_NUMBER,'0')) AS 七月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'08',TEST_PRO_NUMBER,'0')) AS 八月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'09',TEST_PRO_NUMBER,'0')) AS 九月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'10',TEST_PRO_NUMBER,'0')) AS 十月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'11',TEST_PRO_NUMBER,'0')) AS 十一月,
  SUM(DECODE(TO_CHAR(TEST_PRO_SALEDATE,'MM'),'12',TEST_PRO_NUMBER,'0')) AS 十二月,
  SUM(TEST_PRO_NUMBER) AS 总数
  from TEST_PRO 
  where TO_CHAR(TEST_PRO_SALEDATE,'YYYY') = IN_YEAR
  GROUP BY TEST_PRO_PROID;
  END TEST_PRO_SALES_MONTHS;

 plsq测试结果集：