-- select * from std_mes_shipdata ;
--set serveroutput on ;
做一个保存,以后参考:
create or replace package xxoutput
as
type mycrs is ref cursor ;
procedure xxfnsoutput ( cur out mycrs ) ;
end ;
create or replace package body xxoutput
as
procedure xxfnsoutput ( cur out mycrs )
is
tmpSQLString varchar2(4000) ;
iLoopN number ;
iLoopDayth number ;
iDaythNow number ;
begin
iLoopN := 1 ;
iLoopDayth :=1 ;
iDaythNow := to_number( substr( to_char ( sysdate,'YYYYMMDD' ),7,2 ) ) ;
tmpSQLString := ' select productSquare ' ;
-- 动态SQL :
while iLoopDayth <= iDaythNow
loop
tmpSQLString := tmpSQLString || ', sum( decode ( dayn, '''||to_char( trunc(sysdate,'MM')+(iLoopDayth-1),'YYYYMMDD' )
||''',qty,0)) as '||'D'
||to_char( trunc(sysdate,'MM')+(iLoopDayth-1),'YYYYMMDD' ) ;
iLoopDayth := iLoopDayth +1 ;
end loop ;
-- 附 动态SQL的源表:
tmpSQLString := tmpSQLString || ' from ( select case substr(trim(product_id),1,1)
when ''F'' then ''4''
when ''W'' then ''5''
when ''M'' then ''6''
end as productSquare,
substr(trim(ship_date),1,8) as dayn,
sum(qty) as qty
from std_mes_shipdata
where substr(trim(ship_date),1,6) = substr( to_char( trunc( sysdate,''MM''),''YYYYMMDD'' ),1,6)
group by substr(trim(product_id),1,1) , substr(trim(ship_date),1,8)
order by substr(trim(ship_date),1,8) ) drtbl group by productSquare ' ;
-- 打印最后的SQL 语句:
/*
while iLoopN<=length( trim( tmpSQLString ) )
loop
dbms_output.put_line( substr( trim( tmpSQLString ) ,iLoopN, 150 ) ) ;
iLoopN := iLoopN +150 ;
end loop ;
*/
open cur for tmpSQLString ;
end ;
end ;
plsql动态语句
最新推荐文章于 2024-02-17 23:22:36 发布