oracle-function-into时为null报错
create or replace function P_ADD_CUSTOMER_FOR_CSS_heyt_test(i_cust_name in varchar2,
i_en_name in varchar2,
i_cust_sex in varchar2,
i_cust_birthday in date,
i_cust_id_type in varchar2,
i_cust_id_no in varchar2,
i_data_quality_level in char,
i_death_date in date,
i_part_code in varchar2,
i_risk_level_time in date,
i_risk_level in varchar2,
i_risk_level_reason in varchar2,
i_risk_level_user in varchar2,
i_address in varchar2,
i_postcode in varchar2,
i_landline_phone in varchar2,
i_mobile in varchar2,
i_email in varchar2,
i_papers_effect_date in date,
i_papers_matu_date in date,
i_occupation_grade in varchar2,
i_occupation_code in varchar2,
i_nationality in varchar2,
i_sys_source in varchar2,
i_cif_cust_no in varchar2,
i_oper in varchar2,
i_remark in varchar2)
return varchar2 is
tInsuredNo varchar2(20);
tInsuredNo_temp varchar2(20);
i_cust_id_type_temp varchar2(20);
i_cust_sex_temp varchar2(5); --pragma autonomous_transaction;
--tInsuredNo ldperson.customerno%type;
begin
DBMS_OUTPUT.put_line('sql:'||'查询客户号开始');
begin
DBMS_OUTPUT.put_line('sql:'||i_cust_id_no);
DBMS_OUTPUT.put_line('sql:'||i_cust_name);
DBMS_OUTPUT.put_line('sql:'||i_cust_id_type);
DBMS_OUTPUT.put_line('sql:'||i_cust_id_no);
DBMS_OUTPUT.put_line('sql:'||i_cust_birthday);
DBMS_OUTPUT.put_line('sql:'||i_cust_sex);
Execute Immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'''; select count(1)
into tInsuredNo_temp
from ldperson_heyt_test t
where t.customername = i_cust_name
and t.idtype = decode(i_cust_id_type,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',i_cust_id_type)
and UPPER(t.idno) = UPPER(i_cust_id_no)
and t.birthday = i_cust_birthday
and t.gender = decode(i_cust_sex,'','','','','','','')
and rownum = 1; if tInsuredNo_temp != 0 then
select t.customerno
into tInsuredNo
from ldperson_heyt_test t
where t.customername = i_cust_name
and t.idtype = decode(i_cust_id_type,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',i_cust_id_type)
and UPPER(t.idno) = UPPER(i_cust_id_no)
and t.birthday = i_cust_birthday
and t.gender = decode(i_cust_sex,'','','','','','','')
and rownum = 1;
return tInsuredNo;
end if;
DBMS_OUTPUT.put_line('sql:'||'查询客户号结束'); --如果客户号存不存在即需要生成客户号，添加客户地址两张表
if tInsuredNo_temp = 0 Then
DBMS_OUTPUT.put_line('sql:'||'查询客户号未找到，需要生成');
Execute Immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'''; select decode(i_cust_id_type,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',i_cust_id_type) into i_cust_id_type_temp from dual;
select decode(i_cust_sex,'','','','','','','') into i_cust_sex_temp from dual; --调用生成规则
tInsuredNo := 'CP' || lpad(createmaxno('CUSTOMERNO', 'SN'), 18, '');
DBMS_OUTPUT.put_line('sql:'||'客户号已生成'); Execute Immediate 'insert into ldperson (CustomerNo, CustomerName, Gender, Birthday, IDType, IDNo, IDInitiateDate, IDExpiryDate, Nationality, OccupationType, OccupationCode, DeathDate, NameEn, Remark, managecom, comcode, makeoperator, makedate, maketime, modifyoperator, modifydate, modifytime) Values
('''||tInsuredNo||''','''||i_cust_name||''','''||i_cust_sex_temp||''','''||i_cust_birthday||''','''||i_cust_id_type_temp||''','''||i_cust_id_no||''','''||i_papers_effect_date||''','''||i_papers_matu_date||''','''||i_nationality||''','''||i_occupation_grade||''','''||i_occupation_code||''','''||i_death_date||''','''||i_en_name||''','''||i_remark||''','''||''||''','''||''||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''')';
DBMS_OUTPUT.put_line('sql:'||'添加客户完成'); Execute Immediate 'insert into ldpersoncontactinfo (CustomerNo,PostalAddress,ZipCode,Phone,Mobile1,EMail1, makeoperator, makedate, maketime, modifyoperator, modifydate, modifytime)values ('''||tInsuredNo||''','''||i_address||''','''||i_postcode||''','''||i_landline_phone||''','''||i_mobile||''','''||i_email||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''')';
DBMS_OUTPUT.put_line('sql:'||'添加地址完成'); Execute Immediate 'commit';
elsif tInsuredNo IS not null then
return(tInsuredNo);
End If;
return(tInsuredNo);
end;
Exception When Others Then
dbms_output.put_line('sqlcode:'||sqlcode);
DBMS_OUTPUT.put_line('sqlerrm:'||substr(sqlerrm,1,100));
Execute Immediate 'rollback';
return(tInsuredNo);
end P_ADD_CUSTOMER_FOR_CSS_heyt_test;
/