下面这个脚本怎么执行?是直接执行吗?为什么总是报错?那里问题?PROCEDURE gen_info IS
cursor c_pol is
select pol.pol_num, pol.terr_cd, pol.pol_iss_dt, pol.agt_code,pol.pol_stat_cd,dtl.cli_num,dtl.birth_dt,ceil((get_cas_sys_dt-dtl.birth_dt)/365) age,
dtl.occp_code
from tpolicys pol,tclient_details dtl,tclient_policy_links lk
where pol.pol_num = lk.pol_num
and lk.link_typ = 'I'
and lk.cli_num = dtl.cli_num
and pol.pol_stat_cd in ('1','2','3','5')
and pol.terr_cd in ('NJ','HZ')
--and pol.terr_cd = 'HZ'
and pol.pol_iss_dt <= to_date('2008/9/30','yyyy/mm/dd')
-- and pol.pol_iss_dt between to_date('2008/01/01','yyyy/mm/dd') and to_date('2008/09/22','yyyy/mm/dd')
order by pol.agt_code,pol.pol_iss_dt desc;
cursor c_orp (pi_pol_num varchar2) is
select min (trxn_dt)
from torphan_policies
where pol_num = pi_pol_num;
--职业代码,职业类别
cursor c_occp (pi_occp_code varchar2) is
select occp_clas,occp_desc
from TOCCUPATION
where occp_code = pi_occp_code;
--加费记录
cursor c_extra (pi_cli_num varchar2) is
select distinct 'Y'
from tcoverages
where cli_num = pi_cli_num
and (
TEMP_FLAT_UNIT_PREM >0 or
TEMP_FLAT_PREM >0 or
PERM_FLAT_UNIT_PREM >0 or
PERM_FLAT_PREM >0 or
TEMP_MORT_UNIT_PREM >0 or
TEMP_MORT_PREM >0 or
PERM_MORT_UNIT_PREM >0 or
PERM_MORT_PREM >0 or
PERM_MORT_RATG >0 or
TEMP_MORT_RATG >0
) ;
-- 拒保记录
cursor c_rjct (pi_cli_num varchar2) is
select distinct 'Y'
from tcoverages
where cli_num = pi_cli_num
and CVG_STAT_CD = 'R';
-- 重大理赔记录
cursor c_dread_clm (p_cli_num varchar2) is
select distinct 'Y'
from tclaim_details
where clm_stat_code = 'A'
and ( clm_code in ('05','04')
or
clm_aprov_amt>10000
)
and cli_num = p_cli_num ;
l_orp_flag varchar2(1);
l_orp_dt date;
l_latest_dt date;
l_latest_pol varchar2(10);
l_agt_cd varchar2(10) :='######';
l_dread_clm varchar2(1) := 'N';
l_extra_prm varchar2(1) := 'N';
l_occp_desc varchar2(50);
l_occp_cat varchar2(10);
l_rjct varchar2(1) := 'N';
l_data_line varchar2(1000);
l_chk number;
l_file text_io.file_type;
l_file_nm varchar(100) := 'd:\S20081107.csv' ;
BEGIN
l_file := text_io.fopen(l_file_nm,'W');
l_chk := 1;
l_data_line := 'Sales Office,保单编号,客户编号,客户生日,客户年龄,是否孤儿单,成为孤儿单时间,保单签发日,营销员编号,营销员最近一张单子的保单签发日,最近一张单子的保单号码,保单状态,'||
'是否有重大理赔记录,是否有加费记录,职业代码,职业类别,是否有拒保记录';
text_io.put_line(l_file,l_data_line);
for cc in c_pol loop
l_chk := 2;
open c_orp(cc.pol_num);
fetch c_orp into l_orp_dt;
close c_orp;
l_chk := 3;
if l_orp_dt is null then
l_orp_flag := 'N';
else
l_orp_flag := 'Y';
end if;
if l_agt_cd <> cc.agt_code then
l_agt_cd := cc.agt_code;
l_latest_dt := cc.pol_iss_dt;
l_latest_pol := cc.pol_num;
end if;
l_chk := 4;
open c_dread_clm (cc.cli_num);
fetch c_dread_clm into l_dread_clm;
close c_dread_clm;
open c_extra (cc.pol_num);
fetch c_extra into l_extra_prm;
close c_extra;
open c_rjct(cc.cli_num);
fetch c_rjct into l_rjct;
close c_rjct;
open c_occp(cc.occp_code);
fetch c_occp into l_occp_cat,l_occp_desc;
close c_occp;
if instr(l_occp_desc,'学生') > 0 then
l_occp_desc := '学生';
elsif instr(l_occp_desc,'退休') > 0 then
l_occp_desc := '退休';
elsif instr(l_occp_desc,'家庭主妇') > 0 then
l_occp_desc := '家庭主妇';
else
l_occp_desc := '其他';
end if; l_data_line := cc.terr_cd ||','||
cc.pol_num ||','||
cc.cli_num ||','||
cc.birth_dt ||','||
cc.age ||','||
l_orp_flag ||','||
l_orp_dt ||','||
cc.pol_iss_dt ||','||
cc.agt_code ||','||
l_latest_dt ||','||
l_latest_pol ||','||
cc.pol_stat_cd ||','||
l_dread_clm ||','||
l_extra_prm ||','||
l_occp_desc ||','||
l_occp_cat ||','||
l_rjct ;
text_io.put_line(l_file,l_data_line); l_orp_flag := NULL;
l_orp_dt := NULL;
l_occp_cat := null;
l_occp_desc := null;
l_dread_clm := 'N';
l_extra_prm := 'N';
l_occp_desc := null;
l_occp_cat := null;
l_rjct := 'N';
l_chk := 5;
end loop;
text_io.fclose(l_file);
exception
when others then
IF Text_IO.Is_Open(l_file) THEN
Text_IO.Fclose(l_file);
end if;
message('Error generate for :'||l_chk ||sqlerrm);
END;
cursor c_pol is
select pol.pol_num, pol.terr_cd, pol.pol_iss_dt, pol.agt_code,pol.pol_stat_cd,dtl.cli_num,dtl.birth_dt,ceil((get_cas_sys_dt-dtl.birth_dt)/365) age,
dtl.occp_code
from tpolicys pol,tclient_details dtl,tclient_policy_links lk
where pol.pol_num = lk.pol_num
and lk.link_typ = 'I'
and lk.cli_num = dtl.cli_num
and pol.pol_stat_cd in ('1','2','3','5')
and pol.terr_cd in ('NJ','HZ')
--and pol.terr_cd = 'HZ'
and pol.pol_iss_dt <= to_date('2008/9/30','yyyy/mm/dd')
-- and pol.pol_iss_dt between to_date('2008/01/01','yyyy/mm/dd') and to_date('2008/09/22','yyyy/mm/dd')
order by pol.agt_code,pol.pol_iss_dt desc;
cursor c_orp (pi_pol_num varchar2) is
select min (trxn_dt)
from torphan_policies
where pol_num = pi_pol_num;
--职业代码,职业类别
cursor c_occp (pi_occp_code varchar2) is
select occp_clas,occp_desc
from TOCCUPATION
where occp_code = pi_occp_code;
--加费记录
cursor c_extra (pi_cli_num varchar2) is
select distinct 'Y'
from tcoverages
where cli_num = pi_cli_num
and (
TEMP_FLAT_UNIT_PREM >0 or
TEMP_FLAT_PREM >0 or
PERM_FLAT_UNIT_PREM >0 or
PERM_FLAT_PREM >0 or
TEMP_MORT_UNIT_PREM >0 or
TEMP_MORT_PREM >0 or
PERM_MORT_UNIT_PREM >0 or
PERM_MORT_PREM >0 or
PERM_MORT_RATG >0 or
TEMP_MORT_RATG >0
) ;
-- 拒保记录
cursor c_rjct (pi_cli_num varchar2) is
select distinct 'Y'
from tcoverages
where cli_num = pi_cli_num
and CVG_STAT_CD = 'R';
-- 重大理赔记录
cursor c_dread_clm (p_cli_num varchar2) is
select distinct 'Y'
from tclaim_details
where clm_stat_code = 'A'
and ( clm_code in ('05','04')
or
clm_aprov_amt>10000
)
and cli_num = p_cli_num ;
l_orp_flag varchar2(1);
l_orp_dt date;
l_latest_dt date;
l_latest_pol varchar2(10);
l_agt_cd varchar2(10) :='######';
l_dread_clm varchar2(1) := 'N';
l_extra_prm varchar2(1) := 'N';
l_occp_desc varchar2(50);
l_occp_cat varchar2(10);
l_rjct varchar2(1) := 'N';
l_data_line varchar2(1000);
l_chk number;
l_file text_io.file_type;
l_file_nm varchar(100) := 'd:\S20081107.csv' ;
BEGIN
l_file := text_io.fopen(l_file_nm,'W');
l_chk := 1;
l_data_line := 'Sales Office,保单编号,客户编号,客户生日,客户年龄,是否孤儿单,成为孤儿单时间,保单签发日,营销员编号,营销员最近一张单子的保单签发日,最近一张单子的保单号码,保单状态,'||
'是否有重大理赔记录,是否有加费记录,职业代码,职业类别,是否有拒保记录';
text_io.put_line(l_file,l_data_line);
for cc in c_pol loop
l_chk := 2;
open c_orp(cc.pol_num);
fetch c_orp into l_orp_dt;
close c_orp;
l_chk := 3;
if l_orp_dt is null then
l_orp_flag := 'N';
else
l_orp_flag := 'Y';
end if;
if l_agt_cd <> cc.agt_code then
l_agt_cd := cc.agt_code;
l_latest_dt := cc.pol_iss_dt;
l_latest_pol := cc.pol_num;
end if;
l_chk := 4;
open c_dread_clm (cc.cli_num);
fetch c_dread_clm into l_dread_clm;
close c_dread_clm;
open c_extra (cc.pol_num);
fetch c_extra into l_extra_prm;
close c_extra;
open c_rjct(cc.cli_num);
fetch c_rjct into l_rjct;
close c_rjct;
open c_occp(cc.occp_code);
fetch c_occp into l_occp_cat,l_occp_desc;
close c_occp;
if instr(l_occp_desc,'学生') > 0 then
l_occp_desc := '学生';
elsif instr(l_occp_desc,'退休') > 0 then
l_occp_desc := '退休';
elsif instr(l_occp_desc,'家庭主妇') > 0 then
l_occp_desc := '家庭主妇';
else
l_occp_desc := '其他';
end if; l_data_line := cc.terr_cd ||','||
cc.pol_num ||','||
cc.cli_num ||','||
cc.birth_dt ||','||
cc.age ||','||
l_orp_flag ||','||
l_orp_dt ||','||
cc.pol_iss_dt ||','||
cc.agt_code ||','||
l_latest_dt ||','||
l_latest_pol ||','||
cc.pol_stat_cd ||','||
l_dread_clm ||','||
l_extra_prm ||','||
l_occp_desc ||','||
l_occp_cat ||','||
l_rjct ;
text_io.put_line(l_file,l_data_line); l_orp_flag := NULL;
l_orp_dt := NULL;
l_occp_cat := null;
l_occp_desc := null;
l_dread_clm := 'N';
l_extra_prm := 'N';
l_occp_desc := null;
l_occp_cat := null;
l_rjct := 'N';
l_chk := 5;
end loop;
text_io.fclose(l_file);
exception
when others then
IF Text_IO.Is_Open(l_file) THEN
Text_IO.Fclose(l_file);
end if;
message('Error generate for :'||l_chk ||sqlerrm);
END;
直接用sqlplus來完成嗎