1 Create or Replace functionMca_detail_material_val (P_material_codeVARCHAR2,--actual Parameters2P_material_nameVARCHAR2,3P_mca_noVARCHAR24 ) 5 RETURN varchar2 as6 7 --Item code/name validation, data table (MATERIAL) does not exist in the item code/name, allowed through, fill other values are not allowed through8 9V_count1 Number;--Formal ParametersTenV_count2 Number; OneV_count3 Number; AV_count4 Number; - - CURSORGet_wl_material_code1 is the SELECT Count(M.material_code)--count () function: Number of records queried (number of records) - fromMATERIAL M - WHEREM.material_code=P_material_code; - + CURSORGet_wl_material_code2 is - SELECT Count(Material_code) + fromMca_detail M A WHEREM.material_code=P_material_code andM.mca_no<>P_mca_no; at --m.mca_no <> p_mca_no: When editing, exclude this ticket (do not compare)---material code - CURSORGet_wl_material_name1 is - SELECT Count(M.material_name) - fromMATERIAL M - WHEREM.material_name=P_material_name; - in CURSORGet_wl_material_name2 is - SELECT Count(M.material_name) to fromMca_detail M + WHEREM.material_name=P_material_name andM.mca_no<>P_mca_no; - BEGIN --m.mca_no <> p_mca_no: When editing, exclude this ticket (do not compare)---item name the OPENGet_wl_material_code1; * FETCHGet_wl_material_code1 intov_count1; $ CLOSEGet_wl_material_code1;Panax Notoginseng - OPENGet_wl_material_code2; the FETCHGet_wl_material_code2 intoV_count3; + CLOSEGet_wl_material_code2; A the OPENget_wl_material_name1; + FETCHGet_wl_material_name1 intoV_count2; - CLOSEget_wl_material_name1; $ $ OPENget_wl_material_name2; - FETCHGet_wl_material_name2 intov_count4; - CLOSEget_wl_material_name2; the - IFV_count1+V_count3<> 0 ThenWuyi RETURN '4823272';--error Message code (Information Code table), the item code already exists, not allowed to appear the same value! theelsif V_count2+V_count4<> 0 Then - RETURN '4823273';--error Message code (Information Code table), the item name already exists, not allowed to appear the same value! Wu END IF; - About RETURN '0'; $ - EndMca_detail_material_val;
The Oracle function "determines data that does not exist in the data table before it is allowed through"