plsql动态语句

--   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 ;