oracle 为null,oracle-function-into时为null报错

1 create or replace function P_ADD_CUSTOMER_FOR_CSS_heyt_test(i_cust_name in varchar2,2 i_en_name in varchar2,3 i_cust_sex in varchar2,4 i_cust_birthday indate,5 i_cust_id_type in varchar2,6 i_cust_id_no in varchar2,7 i_data_quality_level in char,8 i_death_date indate,9 i_part_code in varchar2,10 i_risk_level_time indate,11 i_risk_level in varchar2,12 i_risk_level_reason in varchar2,13 i_risk_level_user in varchar2,14 i_address in varchar2,15 i_postcode in varchar2,16 i_landline_phone in varchar2,17 i_mobile in varchar2,18 i_email in varchar2,19 i_papers_effect_date indate,20 i_papers_matu_date indate,21 i_occupation_grade in varchar2,22 i_occupation_code in varchar2,23 i_nationality in varchar2,24 i_sys_source in varchar2,25 i_cif_cust_no in varchar2,26 i_oper in varchar2,27 i_remark in varchar2)28 return varchar2 is

29 tInsuredNo varchar2(20);30 tInsuredNo_temp varchar2(20);31 i_cust_id_type_temp varchar2(20);32 i_cust_sex_temp varchar2(5);33

34 --pragma autonomous_transaction;

35 --tInsuredNo ldperson.customerno%type;

36 begin

37 DBMS_OUTPUT.put_line('sql:'||'查询客户号开始');38 begin

39 DBMS_OUTPUT.put_line('sql:'||i_cust_id_no);40 DBMS_OUTPUT.put_line('sql:'||i_cust_name);41 DBMS_OUTPUT.put_line('sql:'||i_cust_id_type);42 DBMS_OUTPUT.put_line('sql:'||i_cust_id_no);43 DBMS_OUTPUT.put_line('sql:'||i_cust_birthday);44 DBMS_OUTPUT.put_line('sql:'||i_cust_sex);45 Execute Immediate 'ALTER SESSION SET NLS_DATE_FORMAT =''YYYY-MM-DD HH24:MI:SS''';46

47 select count(1)48 intotInsuredNo_temp49 fromldperson_heyt_test t50 where t.customername =i_cust_name51 and t.idtype = decode(i_cust_id_type,'10','111','11','113','12','990','13','114','14','990','15','990','16','111','17','516','18','550','19','990','21','990','22','990','23','990','30','553','51','414','52','414','53','990','54','990','55','117','96','117','97','117','98','516','99','990',i_cust_id_type)52 and UPPER(t.idno) = UPPER(i_cust_id_no)53 and t.birthday =i_cust_birthday54 and t.gender = decode(i_cust_sex,'01','0','02','1','09','2','2')55 and rownum = 1;56

57 if tInsuredNo_temp != 0 then

58 selectt.customerno59 intotInsuredNo60 fromldperson_heyt_test t61 where t.customername =i_cust_name62 and t.idtype = decode(i_cust_id_type,'10','111','11','113','12','990','13','114','14','990','15','990','16','111','17','516','18','550','19','990','21','990','22','990','23','990','30','553','51','414','52','414','53','990','54','990','55','117','96','117','97','117','98','516','99','990',i_cust_id_type)63 and UPPER(t.idno) = UPPER(i_cust_id_no)64 and t.birthday =i_cust_birthday65 and t.gender = decode(i_cust_sex,'01','0','02','1','09','2','2')66 and rownum = 1;67 returntInsuredNo;68 end if;69 DBMS_OUTPUT.put_line('sql:'||'查询客户号结束');70

71

72 --如果客户号存不存在即需要生成客户号，添加客户地址两张表

73 if tInsuredNo_temp = 0 Then

74 DBMS_OUTPUT.put_line('sql:'||'查询客户号未找到，需要生成');75 Execute Immediate 'ALTER SESSION SET NLS_DATE_FORMAT =''YYYY-MM-DD HH24:MI:SS''';76

77 select decode(i_cust_id_type,'10','111','11','113','12','990','13','114','14','990','15','990','16','111','17','516','18','550','19','990','21','990','22','990','23','990','30','553','51','414','52','414','53','990','54','990','55','117','96','117','97','117','98','516','99','990',i_cust_id_type) into i_cust_id_type_temp fromdual;78 select decode(i_cust_sex,'01','0','02','1','09','2','2') into i_cust_sex_temp fromdual;79

80 --调用生成规则

81 tInsuredNo := 'CP' || lpad(createmaxno('CUSTOMERNO', 'SN'), 18, '0');82 DBMS_OUTPUT.put_line('sql:'||'客户号已生成');83

84 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) Values85 ('''||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||''','''||'86'||''','''||'00'||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''','''||i_oper||''','''||sysdate||''','''||'00:00:00'||''')';86 DBMS_OUTPUT.put_line('sql:'||'添加客户完成');87

88 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'||''')';89 DBMS_OUTPUT.put_line('sql:'||'添加地址完成');90

91 Execute Immediate 'commit';92 elsif tInsuredNo IS not null then

93 return(tInsuredNo);94 End If;95 return(tInsuredNo);96 end;97 Exception When Others Then

98 dbms_output.put_line('sqlcode:'||sqlcode);99 DBMS_OUTPUT.put_line('sqlerrm:'||substr(sqlerrm,1,100));100 Execute Immediate 'rollback';101 return(tInsuredNo);102 endP_ADD_CUSTOMER_FOR_CSS_heyt_test;103 /