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 in date, 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 in date, 9 i_part_code in varchar2, 10 i_risk_level_time in date, 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 in date, 20 i_papers_matu_date in date, 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 into tInsuredNo_temp 49 from ldperson_heyt_test t 50 where t.customername = i_cust_name 51 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_birthday 54 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 select t.customerno 59 into tInsuredNo 60 from ldperson_heyt_test t 61 where t.customername = i_cust_name 62 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_birthday 65 and t.gender = decode(i_cust_sex,'01','0','02','1','09','2','2') 66 and rownum = 1; 67 return tInsuredNo; 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 from dual; 78 select decode(i_cust_sex,'01','0','02','1','09','2','2') into i_cust_sex_temp from dual; 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) Values 85 ('''||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 end P_ADD_CUSTOMER_FOR_CSS_heyt_test; 103 /