匿名块--

 

declare

  -- result cursor
  token varchar2(2) := '@';

  cursor c_regin is
    select nm_customer,
           col_b id_contract,
           TXT_CONTRACT_NUMBER || '-' || TXT_CONTRACT_TITLE1 CONTRACT,
           lv.id_version,
           lv.txt_version_type,
           lcv.DT_CONTRACT_START,
           lcv.DT_CONTRACT_END,
           
           PDB_AGREEMENT_ID,
           AGREEMENT_NAME,
           col_d num_year,
           col_aa - col_gg deferred_revenue, --deferred_revenue,
           col_gg billing,
           
           (select sum(col_j)
              from lms_gen_rpt_table
             where id_rpt_instance = 03051984
                  
               and col_a = l.col_b
               and col_n = l.col_d) num_adder_payment,
           -- col_i  cum_deferred_revenue /*, 
           
           (select sum(col_g)
              from lms_gen_rpt_table
             where id_rpt_instance = 03051984
                  
               and col_a = l.col_b
               and col_n = l.col_d) escalation_payment,
           
           (select sum(col_f)
              from lms_gen_rpt_table
             where id_rpt_instance = 03051984
                  
               and col_a = l.col_b
               and col_n = l.col_d) grossmob,
           
           (select sum(col_h)
              from lms_gen_rpt_table
             where id_rpt_instance = 03051984
                  
               and col_a = l.col_b
               and col_n = l.col_d) num_fixed_pmt,
           
           (select sum(col_i)
              from lms_gen_rpt_table
             where id_rpt_instance = 03051984
                  
               and col_a = l.col_b
               and col_n = l.col_d) num_variable_pmt
    
      from lms_gen_rpt_table l,
           LMS_CUSTOMER      LCUS,
           LMS_CONTRACT      LC,
           -- Lms_site_catalog lsc,
           -- lms_contract_version_site lcvs,
           LMS_CONTRACT_VERSION LCV,
           lms_version          lv,
           LMS_CONTRACT_PDB     lcp --,
    -- lms_region lr
    
     where id_rpt_instance = 03051985
       AND LCV.ID_CONTRACT = LC.ID_CONTRACT
          
       AND LC.ID_CUSTOMER = LCUS.ID_CUSTOMER
          
       AND COL_B = TO_CHAR(LCV.ID_CONTRACT)
          
       AND COL_C = TO_CHAR(LCV.ID_VERSION)
          
          -- and lcv.id_contract = lcvs.id_contract
          -- and lcv.id_version = lcvs.id_version
          --and lcvs.id_site = lsc.id_site
       and lv.id_version = lcv.id_version
          --and lsc.id_region = lr.id_region
       and lcv.id_contract = lcp.id_contract(+)
          
       and col_d in ('2007', '2008', '2009', '2010', '2011')
          
       and col_d <> 'Total'
       and lc.id_contract = 1112
     order by col_b, col_d;

  -- function
  Function FindRegin(in_id_contract IN varchar2, in_id_Version IN varchar2)
    RETURN varchar2 IS
    out_result varchar2(200);
  
    cursor c1 is
      select distinct lr.txt_region_name
        from lms_contract_version_site lcvs,
             lms_region                lr,
             lms_site_catalog          lsc
       where lcvs.id_contract = in_id_contract
         and lcvs.id_Version = in_id_Version
         and lsc.id_site = lcvs.id_site
         and lr.id_region = lsc.id_region;
  
  BEGIN
  
    for result_cur in c1 loop
      out_result := result_cur.txt_region_name || '---' || out_result;
    end loop;
  
    RETURN out_result;
  
  EXCEPTION
    WHEN OTHERS THEN
      return '';
  END FindRegin;

begin
  for result_cur in c_regin loop
  
    dbms_output.put(result_cur.NM_CUSTOMER);
    dbms_output.put(token);
    dbms_output.put(result_cur.ID_CONTRACT);
    dbms_output.put(token);
    dbms_output.put(result_cur.CONTRACT);
    dbms_output.put(token);
    dbms_output.put(result_cur.id_version);
    dbms_output.put(token);
    dbms_output.put(result_cur.txt_version_type);
    dbms_output.put(token);
    dbms_output.put(result_cur.DT_CONTRACT_START);
    dbms_output.put(token);
    dbms_output.put(result_cur.DT_CONTRACT_END);
    dbms_output.put(token);
    dbms_output.put(result_cur.PDB_AGREEMENT_ID);
    dbms_output.put(token);
    dbms_output.put(result_cur.AGREEMENT_NAME);
    dbms_output.put(token);
    dbms_output.put(result_cur.num_year);
    dbms_output.put(token);
    dbms_output.put(result_cur.deferred_revenue);
    dbms_output.put(token);
    dbms_output.put(result_cur.num_adder_payment);
    dbms_output.put(token);
    dbms_output.put(result_cur.escalation_payment);
    dbms_output.put(token);
    dbms_output.put(result_cur.grossmob);
    dbms_output.put(token);
    dbms_output.put(result_cur.num_fixed_pmt);
    dbms_output.put(token);
    dbms_output.put(result_cur.num_variable_pmt);
    dbms_output.put(token);
  
    dbms_output.put(FindRegin(result_cur.ID_CONTRACT,
                              result_cur.id_version));
  
    dbms_output.new_line();
  
  end loop;

end;

 

--

无权限的时候,dbms_output.put / putline --- 每一行限制是255个字符

Excel -> Data -> Text to Column  (select only 1 row)

Paste special ->transpose