![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
declare
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- result cursor
token varchar2(2) := '@';
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
--
无权限的时候,dbms_output.put / putline --- 每一行限制是255个字符
Excel -> Data -> Text to Column (select only 1 row)
Paste special ->transpose