高手们，帮小弟看看这个存储过程该如何写！

Question

比较多，各位大哥大姐耐心看下！
流程是这样的SP可以发布资源，根据资源类型不同存放到不同的资源信息表！
user用户可以下载资源，下载记录存放在下载记录表中！现在要查询指定月份各SP单个资源被下载总量！同时区分下载者地区以及是否为2010年之前注册用户！
区分是否为2010前注册用户的方法为用户名开头为100的为2010前注册，200的为2010后注册！
最后结果如下：
下面是简单的建表及测试数据！
--SP信息表
create table spinfo
(
    spid NUMBER(20),                --sp编号
    spname varchar2(100)            --sp名称
)insert into spinfo values('1','刘德华');
insert into spinfo values('2','张学友');
insert into spinfo values('3','郭富城');--USER信息表
create table userinfo
(
    id int,
    spname varchar2(50)             --SP用户名
)insert into userinfo values(1,'2001184');
insert into userinfo values(2,'1001098');
insert into userinfo values(3,'1002094');
insert into userinfo values(4,'2002068');
insert into userinfo values(5,'1001023');--单个资源信息表
create table resourcesinfo
(
    rid varchar2(50)，              --资源编号
    resourcesname varchar2(20),     --资源名称
    spid number(20)
)
insert into resourcesinfo values('10001','资源A','1');
insert into resourcesinfo values('10002','资源B','2');
insert into resourcesinfo values('10003','资源C','1');
insert into resourcesinfo values('10004','资源D','3');
insert into resourcesinfo values('10005','资源E','1');--下载记录表
create table downloadlog
(
    username varchar2(50),           --下载者名称
    rid varchar2(50)，               --资源编号
    downtime date,                   --下载时间
    rtype varhchar2(2)               --资源类型  1为单个资源  2为打包资源
)
insert into downloadlog values('2001184','10001',to_date('2010-6-21 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('1001098','10002',to_date('2010-6-21 15:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('2001184','10003',to_date('2010-6-25 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('1001098','10004',to_date('2010-6-26 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('2001184','10001',to_date('2010-6-26 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('1002094','10004',to_date('2010-6-27 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('2002068','10003',to_date('2010-6-27 12:09:45','YYYY-MM-DD hh24:mi:ss'));
insert into downloadlog values('2002068','10002',to_date('2010-6-28 12:09:45','YYYY-MM-DD hh24:mi:ss'));--USER用户业务表
create table userserviceinfo
( 
     username varchar(50),                       --用户名
     localid  number(20)                         --地区ID
)insert into userserviceinfo values('2001184','21');
insert into userserviceinfo values('1001098','22');
insert into userserviceinfo values('1002094','23');
insert into userserviceinfo values('2002068','21');
insert into userserviceinfo values('1001023','22');--地区信息表
create table localinfo
(
     localid number(20),                        --地区ID
     localname varchar(50)                      --地区名称
)insert into localinfo values('21','北京');
insert into localinfo values('22','上海');
insert into localinfo values('23','广州');

Answer 1

rtype varhchar2(2)               --资源类型  1为单个资源  2为打包资源
写错了不少地方
下边插入值也不够，虽然这里没用

Answer 2

SELECT spinfo.SPNAME AS SP名称,
       COUNT('X') AS 发布资源总被下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'北京100',1,NULL)) AS 北京2010前注册用户下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'北京200',1,NULL)) AS 北京2010后注册用户下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'上海100',1,NULL)) AS 上海2010前注册用户下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'上海200',1,NULL)) AS 上海2010后注册用户下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'广州100',1,NULL)) AS 广州2010前注册用户下载次数,
       COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),'广州200',1,NULL)) AS 广州2010后注册用户下载次数
FROM downloadlog
LEFT JOIN resourcesinfo ON downloadlog.RID = resourcesinfo.RID
LEFT JOIN spinfo ON resourcesinfo.SPID = spinfo.SPID
LEFT JOIN userserviceinfo ON downloadlog.username = userserviceinfo.USERNAME
LEFT JOIN localinfo ON userserviceinfo.LOCALID = localinfo.LOCALID
GROUP BY spinfo.SPNAME

Answer 3

with t as (select b.localname, c.downtime, e.spname
  from downloadlog c
  left join resourcesinfo d on c.rid = d.rid
  left join spinfo e on d.spid = e.spid
  left join userserviceinfo a on c.username=a.username
  left join localinfo b on a.localid=b.localid)
select spname,
localname||case  when downtime> to_date('2010-01-01') then '2010后' 
else '2010前' end as yy,
count(1) over(partition by spname,localname) as xx
from t
最后行转列  就不写了 
7楼  你那个判断2010前和后的逻辑没看懂  

Answer 4

根据lz说“区分是否为2010前注册用户的方法为用户名开头为100的为2010前注册，200的为2010后注册”，所以用了username的前三位来判断

Answer 5

我数据库出了点问题，还没试！不过我觉得你是不是理解错了！
userinfo表username对应userserviceinfo表中的username,而userserviceinfo表中的地区ID对应localinfo中的地区ID，不过你好像都没用到userserviceinfo表！

Answer 6

用了啊：
LEFT JOIN userserviceinfo ON downloadlog.username = userserviceinfo.USERNAME
LEFT JOIN localinfo ON userserviceinfo.LOCALID = localinfo.LOCALID
不明白lz的意思，userserviceinfo的东西不用显示出来啊

Answer 7

是这样的，根据lz说的“区分是否为2010前注册用户的方法为用户名开头为100的为2010前注册，200的为2010后注册”，并且查询的时候也是按照地区区分的，所以取得localinfo.LOCALNAME和downloadlog.username的前三位，拼成一个串，然后判断来求和。比如北京100就可以判断是北京2010前的

Answer 8

CREATE OR REPLACE PACKAGE PCK_TEST IS  TYPE RESULTCURSOR IS REF CURSOR;  PROCEDURE PRO_TEST(CUR_RESULT OUT RESULTCURSOR);END PCK_TEST;CREATE OR REPLACE PACKAGE BODY PCK_TEST IS  PROCEDURE PRO_TEST(CUR_RESULT OUT RESULTCURSOR) IS
    CUR_LOCAL RESULTCURSOR;
    V_LOCAL   LOCALINFO.LOCALNAME%TYPE := NULL;
    V_SQL     LONG;
  BEGIN
    V_SQL := 'SELECT spinfo.SPNAME AS SP名称, COUNT(''X'') AS 发布资源总被下载次数';
    IF NOT CUR_LOCAL%ISOPEN THEN
      OPEN CUR_LOCAL FOR
        SELECT LOCALINFO.LOCALNAME FROM LOCALINFO;
    END IF;
    LOOP
      FETCH CUR_LOCAL
        INTO V_LOCAL;
      EXIT WHEN CUR_LOCAL%NOTFOUND;
      V_SQL := V_SQL ||
               ', COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),''' ||
               V_LOCAL || '100'',1,NULL)) AS ' || V_LOCAL ||
               '2010前注册用户下载次数';
      V_SQL := V_SQL ||
               ', COUNT(decode(localinfo.LOCALNAME||substr(downloadlog.username,1,3),''' ||
               V_LOCAL || '200'',1,NULL)) AS ' || V_LOCAL ||
               '2010后注册用户下载次数';
    END LOOP;
    V_SQL := V_SQL ||
             ' FROM downloadlog LEFT JOIN resourcesinfo ON downloadlog.RID = resourcesinfo.RID LEFT JOIN spinfo ON resourcesinfo.SPID = spinfo.SPID LEFT JOIN userserviceinfo ON downloadlog.username = userserviceinfo.USERNAME LEFT JOIN localinfo ON userserviceinfo.LOCALID = localinfo.LOCALID GROUP BY spinfo.SPNAME';
    IF NOT CUR_RESULT%ISOPEN THEN
      OPEN CUR_RESULT FOR V_SQL;
    END IF;
    IF CUR_LOCAL%ISOPEN THEN
      CLOSE CUR_LOCAL;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || '------' || SQLERRM);
  END;BEGIN
  NULL;
END PCK_TEST;写了个procedure，测过了，可能别人有更简单的方法