行转列

Question

如下 
Select DEPTID,
        sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
        sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
        sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
        sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
        sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
        sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
        sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
 From GZJXCR
 Where RQ >= '2009-12-07' and RQ <= '2009-12-13'
 Group by DEPTID
是可以的。但在存储过程中
create or replace procedure BB_FDRXSFXBB
(v_HZRQ in Date,
 v_FDBH in NUMBER)
is
  v_MON_DATE      DATE;             /*周一*/
  v_TUE_DATE      DATE;             /*周二*/
  v_WED_DATE      DATE;             /*周三*/ 
  v_THUR_DATE     DATE;             /*周四*/
  v_FRI_DATE      DATE;             /*周五*/
  v_SAT_DATE      DATE;             /*周六*/
  v_SUN_DATE      DATE;             /*周日*/  
  
begin
  
  select trunc(v_HZRQ,'iw')   into v_MON_DATE  from dual;
  select trunc(v_HZRQ,'iw')+1 into v_TUE_DATE  from dual;
  select trunc(v_HZRQ,'iw')+2 into v_WED_DATE  from dual;
  select trunc(v_HZRQ,'iw')+3 into v_THUR_DATE from dual;
  select trunc(v_HZRQ,'iw')+4 into v_FRI_DATE  from dual;
  select trunc(v_HZRQ,'iw')+5 into v_SAT_DATE  from dual;
  select trunc(v_HZRQ,'iw')+6 into v_SUN_DATE  from dual;
  
  Select DEPTID,
         sum(decode(RQ,'2009-12-07',  (XSJE+XSJE_PF),0)) MON,
         sum(decode(RQ,'2009-12-08',  (XSJE+XSJE_PF),0)) TUE,
         sum(decode(RQ,'2009-12-09',  (XSJE+XSJE_PF),0)) WED,
         sum(decode(RQ,'2009-12-10',  (XSJE+XSJE_PF),0)) THUR,
         sum(decode(RQ,'2009-12-11',  (XSJE+XSJE_PF),0)) FRI,
         sum(decode(RQ,'2009-12-12',  (XSJE+XSJE_PF),0)) SAT,
         sum(decode(RQ,'2009-12-13',  (XSJE+XSJE_PF),0)) SUN
 From GZJXCR
 Where RQ >= v_MON_DATE and RQ <= v_HZRQ
 Group by DEPTID;
   
end;
报错
PLS-00428: an INTO clause is expected in this SELECT statement
请问怎么处理

Answer 1

存储过程中直接select 是没用的
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;
这点，oracle跟sql server不一样

Answer 2

PL/SQL不能仅一条标准select语句，要么使用游标返回结果集，要么和into配合（有且仅有一条记录），将选择结果值赋予变量。

Answer 3

像你这样的情况，应该使用ref cursor
然后使用
open cursor_name for
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;返回结果集。

Answer 4

在包头定义 ：TYPE  curRecordSet IS REF CURSOR;这个时在定义存储过程时定义一个变量
create or replace procedure BB_FDRXSFXBB
(v_HZRQ in Date,
v_FDBH in NUMBER，
v_out OUT curRecordSet )....
在你的
open v_out for
Select DEPTID,
sum(decode(RQ,'2009-12-07', (XSJE+XSJE_PF),0)) MON,
sum(decode(RQ,'2009-12-08', (XSJE+XSJE_PF),0)) TUE,
sum(decode(RQ,'2009-12-09', (XSJE+XSJE_PF),0)) WED,
sum(decode(RQ,'2009-12-10', (XSJE+XSJE_PF),0)) THUR,
sum(decode(RQ,'2009-12-11', (XSJE+XSJE_PF),0)) FRI,
sum(decode(RQ,'2009-12-12', (XSJE+XSJE_PF),0)) SAT,
sum(decode(RQ,'2009-12-13', (XSJE+XSJE_PF),0)) SUN
From GZJXCR
Where RQ >= v_MON_DATE and RQ <= v_HZRQ
Group by DEPTID;