DECLARE
str varchar2(4000) := '';
begin
str := 'select id';
FOR v_day IN (SELECT DISTINCT to_char(PLAN_DATE,'yy-MM-dd') FROM tb) LOOP
str := str || ',sum(decode(to_char(PLAN_DATE,''yy-MM-dd''),' || v_day || ', REQ_QTY, 0)) ' || v_day;
END LOOP;
str := str || ' from tb group by id';
dbms_output.put_line(str);
end;注:PLAN_DATE是日期型字段
编译时提示错误:调用'||'时参数个数或类型错误。
str varchar2(4000) := '';
begin
str := 'select id';
FOR v_day IN (SELECT DISTINCT to_char(PLAN_DATE,'yy-MM-dd') FROM tb) LOOP
str := str || ',sum(decode(to_char(PLAN_DATE,''yy-MM-dd''),' || v_day || ', REQ_QTY, 0)) ' || v_day;
END LOOP;
str := str || ' from tb group by id';
dbms_output.put_line(str);
end;注:PLAN_DATE是日期型字段
编译时提示错误:调用'||'时参数个数或类型错误。
v_str varchar2(4000):='';
v_day varchar2(20);
cursor c_Test is
select distinct to_char(Plan_Date,'yy-mm-dd') from tb;
begin
v_str:='select id';
open c_Test;
Loop
fetch c_Test into v_day;
exit when c_Test%NotFound;
v_str := v_str || ',sum(decode(to_char(PLAN_DATE,''yy-MM-dd''),' || v_day || ', REQ_QTY, 0)) ' || v_day;
End Loop;
close c_Test;
v_str :=v_str||' from tb group by id';
dbms_output.put_line(v_str);
end;
str varchar2(4000) := '';
begin
str := 'select id';
FOR v_day IN (SELECT DISTINCT to_char(PLAN_DATE,'yy-MM-dd') s FROM tb) LOOP
str := str || ',sum(decode(to_char(PLAN_DATE,''yy-MM-dd''),' || v_day.s || ', REQ_QTY, 0)) ' || v_day;
END LOOP;
str := str || ' from tb group by id';
dbms_output.put_line(str);
end;
str varchar2(4000) := '';
begin
str := 'select id';
FOR v_day IN (SELECT DISTINCT to_char(PLAN_DATE,'yy-MM-dd') s FROM tb) LOOP
str := str || ',sum(decode(to_char(PLAN_DATE,''yy-MM-dd''),' || v_day.s || ', REQ_QTY, 0)) ' || v_day.s;
END LOOP;
str := str || ' from tb group by id';
dbms_output.put_line(str);
end;