CREATE OR REPLACE procedure get_activity_code(in_activity_code in tm_recall_activity.activity_code%type,out_curEmp out SYS_REFCURSOR)
IS
     Col_c2 VARCHAR2(4000);
     Col_maxday number(5);
     the_day_count number(5);
     the_sql varchar2(4000);
BEGIN
     the_day_count:='1';                     --计数器
     select max(floor(c.repair_date-e.startdate)) INTO  Col_maxday         --计算最大列数，因为列数是不固定的，比如此处是38
          from tt_activity_vehicle c,tm_recall_activity e
          where e.recall_activity_id=c.recall_activity_id and e.activity_code='L001';
     IF  Col_maxday IS NULL THEN
          dbms_output.put_line('NO MAXDAY');
     END IF;
     the_sql:='select b.rssc_name as rssc,d.labor_no as type ';
     while the_day_count<=Col_maxday LOOP            --循环拼语句
          the_sql:=the_sql||',decode(floor(c.repair_date-e.startdate),'||the_day_count||',count(*),0)';
          the_day_count:=the_day_count+1;                   --加一，要拼出满足需要的列来，比如循环38次
     END LOOP;
     the_sql:=the_sql||' from tm_sst a,tm_rssc b,tt_activity_vehicle c,ti_workitem_detail           d,tm_recall_activity e
          where c.operate_sst_code=a.sst_code and a.rssc_id=b.rssc_id
          and e.recall_activity_id=c.recall_activity_id
          and e.activity_code='''||in_activity_code||'''                      --参数
          and d.labor_no in           (''MFJC0000'',''WXBY0000'',''WXBY1000'',''JCWB0000'',''JWBL0000'',''LP000000'',''JCDQ0000'')
          and c.operate_sst_code=d.sst_code and c.bill_no=d.bill_no
          group by b.rssc_name,(c.repair_date-e.startdate),d.labor_no order by           b.rssc_name,d.labor_no,floor(c.repair_date-e.startdate)';
--execute immediate the_sql;
     open out_curEmp for the_sql;                                            --PROCEDURE的输出关键
     EXCEPTION
     WHEN OTHERS THEN
          raise_application_error(-200020,'Error in get_activity_code'||SQLCODE);
     end get_activity_code;
/

 

----------------------------------------------------------------------------------------------------------------------

运行

-----------------------------------------------------------------------------------------------------------------------

原来的SQL是这个样子

  1  select b.rssc_name as rssc,d.labor_no as type ,count(*) as num ,floor(c.repair_date-e.startdate) as xday
  2  from tm_sst a,tm_rssc b,tt_activity_vehicle c,ti_workitem_detail d,tm_recall_activity e
  3  where c.operate_sst_code=a.sst_code and a.rssc_id=b.rssc_id
  4  and e.recall_activity_id=c.recall_activity_id
  5  and e.activity_code='L001'
  6  and d.labor_no in ('MFJC0000','WXBY0000','WXBY1000','JCWB0000','JWBL0000','LP000000','JCDQ0000') and c.operate_sst_code=d.sst_code and c.bill_no=d.bill_no
  7* group by b.rssc_name,(c.repair_date-e.startdate),d.labor_no order by b.rssc_name,xday
SQL> /

辽宁分销中心   MFJC0000                  1          2

辽宁分销中心   JCDQ0000                  1         38

辽宁分销中心   MFJC0000                  1         38

 

前两列不说了，第三列是数量，第四列是距离开始的天数（用来定位第三列应该显示的列的位置，比如第四列是38，则第三列的1应该在第38列处（不算前两列））

 

---------------------------------------------------------------------------------------------------------------------------------

最后拼出的SQL是这个样子

select b.rssc_name as rssc,d.labor_no as type
,decode(floor(c.repair_date-e.startdate),1,count(*),0),decode(floor(c.repair_date-e.startdate),2,count(*),0),
decode(floor(c.repair_date-e.startdate),3,count(*)),decode(floor(c.repair_date-e.startdate),4,count(*)),
decode(floor(c.repair_date-e.startdate),5,count(*)),decode(floor(c.repair_date-e.startdate),6,count(*)),
decode(floor(c.repair_date-e.startdate),7,count(*)),decode(floor(c.repair_date-e.startdate),8,count(*)),
decode(floor(c.repair_date-e.startdate),9,count(*)),decode(floor(c.repair_date-e.startdate),10,count(*)),
decode(floor(c.repair_date-e.startdate),11,count(*)),decode(floor(c.repair_date-e.startdate),12,count(*)),
decode(floor(c.repair_date-e.startdate),13,count(*)),decode(floor(c.repair_date-e.startdate),14,count(*)),
decode(floor(c.repair_date-e.startdate),15,count(*)),decode(floor(c.repair_date-e.startdate),16,count(*)),
decode(floor(c.repair_date-e.startdate),17,count(*)),decode(floor(c.repair_date-e.startdate),18,count(*)),
decode(floor(c.repair_date-e.startdate),19,count(*)),decode(floor(c.repair_date-e.startdate),20,count(*)),
decode(floor(c.repair_date-e.startdate),21,count(*)),decode(floor(c.repair_date-e.startdate),22,count(*)),
decode(floor(c.repair_date-e.startdate),23,count(*)),decode(floor(c.repair_date-e.startdate),24,count(*)),
decode(floor(c.repair_date-e.startdate),25,count(*)),decode(floor(c.repair_date-e.startdate),26,count(*)),
decode(floor(c.repair_date-e.startdate),27,count(*)),decode(floor(c.repair_date-e.startdate),28,count(*)),
decode(floor(c.repair_date-e.startdate),29,count(*)),decode(floor(c.repair_date-e.startdate),30,count(*)),
decode(floor(c.repair_date-e.startdate),31,count(*)),decode(floor(c.repair_date-e.startdate),32,count(*)),
decode(floor(c.repair_date-e.startdate),33,count(*)),decode(floor(c.repair_date-e.startdate),34,count(*)),
decode(floor(c.repair_date-e.startdate),35,count(*)),decode(floor(c.repair_date-e.startdate),36,count(*)),
decode(floor(c.repair_date-e.startdate),37,count(*)),decode(floor(c.repair_date-e.startdate),38,count(*))
 from  tm_sst a,tm_rssc b,tt_activity_vehicle c,ti_workitem_detail
d,tm_recall_activity e
where c.operate_sst_code=a.sst_code
and a.rssc_id=b.rssc_id
and e.recall_activity_id=c.recall_activity_id
and
e.activity_code='L001'
and d.labor_no in
('MFJC0000','WXBY0000','WXBY1000','JCWB0000','JWBL0000','LP000000','JCDQ0000')
and c.operate_sst_code=d.sst_code and c.bill_no=d.bill_no
group by b.rssc_name,(c.repair_date-e.startdate),d.labor_no order by
b.rssc_name,d.labor_no,floor(c.repair_date-e.startdate);

 

----------------------------------------------------------------------------------------------------------------------------

结果是

第一行

辽宁分销中心
JCDQ0000         0
0
0
0
0
0
0
0
0
 0
 0
 0

 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0

 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 1

第二行
辽宁分销中心
MFJC0000         0
1
0
0
0
0
0
0
0
 0
 0

 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0

 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0

 0

 

直接COPY的结果，没有修改，大家看起来可能困难些，见量吧。里面SQL也没修改，以后有时间再写个明白的吧

 

 

=======================================================

类似

CREATE OR REPLACE  PACKAGE PKG_JCCTEST1                          
AS

  type rc_class is ref cursor;

。。。。。。。。。。。。。。

procedure GetSubAndSum2(p1 number,p2  number ,
              ResultCursor out rc_class);

程序调用'{call PKG_JCCTEST1.GetSubAndSum2(?,?)}'

==========================================================

以下是COPY别人JAVA的脚本。

或

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

 TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

 

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;

END TESTC;

 

package com.hyq.src;

import java.sql.*;

import java.io.OutputStream;

import java.io.Writer;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.jdbc.driver.*;

 

 

public class TestProcedureTHREE {

  public TestProcedureTHREE() {

  }

  public static void main(String[] args ){

    String driver = "oracle.jdbc.driver.OracleDriver";

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

 

    try {

      Class.forName(driver);

      conn =  DriverManager.getConnection(strUrl, "hyq", "hyq");

 

      CallableStatement proc = null;

      proc = conn.prepareCall("{ call hyq.testc(?) }");

      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

      proc.execute();

      rs = (ResultSet)proc.getObject(1);

 

      while(rs.next())

      {

          System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

      }

    }

    catch (SQLException ex2) {

      ex2.printStackTrace();

    }

    catch (Exception ex2) {

      ex2.printStackTrace();

    }

    finally{

      try {

        if(rs != null){

          rs.close();

          if(stmt!=null){

            stmt.close();

          }

          if(conn!=null){

            conn.close();

          }

        }

      }

      catch (SQLException ex1) {

      }

    }

  }

}

在这里要注意，在执行前一定要先把oracle的驱动包放到class路径里，否则会报错的。