PROCEDURE check_client_email(p_check_char IN VARCHAR2, p_sign OUT VARCHAR2, p_message OUT VARCHAR2) IS v_email VARCHAR2(1000); v_singlechar VARCHAR2(3); v_err_code VARCHAR2(10); v_prompt VARCHAR2(1000); BEGIN p_sign := 'N'; --不區分大小寫，全部換成小寫 v_email := LOWER(p_check_char); v_email := NVL(v_email, '*'); --IF LENGTH(v_email) >= 6 THEN --檢查郵件地址中是否有“@”和“.”兩個關鍵字符 IF INSTR(v_email, '@') = 0 OR INSTR(v_email, '.') = 0 THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范，EMAIL未含有“@”和“.”兩個關鍵字符'; RETURN; END IF; --檢查： --1、是否出現了多個“@”符號 --2、檢查“@”符號是否在開頭和結尾 --3、“.”符號在“@”符號之前的部分及“@”符號之后的部分，均不能作為開始或結尾符 --4、“@”符號后的部分，點號至少出現一次，且不能連續出現 IF INSTR(v_email, '@', INSTR(v_email, '@') + 1) <> 0 OR INSTR(v_email, '@') = 1 OR INSTR(v_email, '@') = LENGTH(v_email) OR INSTR((SUBSTR(v_email, 1, INSTR(v_email, '@') - 1)), '.') = 1 OR INSTR(SUBSTR(v_email, 1, INSTR(v_email, '@') - 1), '.', -1) = LENGTH(SUBSTR(v_email, 1, INSTR(v_email, '@') - 1)) OR INSTR((SUBSTR(v_email, INSTR(v_email, '@') + 1)), '.') = 1 OR INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.', -1) = LENGTH((SUBSTR(v_email, INSTR(v_email, '@') + 1))) OR INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') = 0 OR INSTR((SUBSTR(v_email, INSTR(v_email, '@') + 1)), '..') > 0 THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范'; RETURN; END IF; --A段只能由字母、數字、下劃線、中劃線和點號組成 FOR i IN 1 .. LENGTH(SUBSTR(v_email, 1, INSTR(v_email, '@') - 1)) LOOP v_singlechar := SUBSTR((SUBSTR(v_email, 1, INSTR(v_email, '@') - 1)), i, 1); IF v_singlechar NOT IN ('_', '-', '.', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z') THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范'; RETURN; END IF; END LOOP; --B、C段開頭只能為字母或數字 IF SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1, INSTR(v_email, '.') - 1 - INSTR(v_email, '@')), 1, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z') OR SUBSTR(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') + 1), 1, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z') THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范'; RETURN; END IF; --B段只能由字母、數字、下劃線、中劃線和點號組成 IF LENGTH(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), 1, INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') - 1)) > 1 THEN FOR i IN 2 .. LENGTH(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), 1, INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') - 1)) LOOP v_singlechar := SUBSTR(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), 1, INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') - 1), i, 1); IF v_singlechar NOT IN ('_', '-', '.', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z') THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范'; RETURN; END IF; END LOOP; END IF; --C段只能由字母、數字、下劃線、中劃線和點號組成 IF LENGTH(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') + 1)) > 1 THEN FOR i IN 2 .. LENGTH(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') + 1)) LOOP v_singlechar := SUBSTR(SUBSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), INSTR(SUBSTR(v_email, INSTR(v_email, '@') + 1), '.') + 1), i, 1); IF v_singlechar NOT IN ('_', '-', '.', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z') THEN p_sign := 'N'; p_message := '錄入的EMAIL不規范'; RETURN; END IF; END LOOP; END IF; p_sign := 'Y'; p_message := '校驗成功'; EXCEPTION WHEN OTHERS THEN p_sign := 'E'; p_message := SUBSTRB('校驗EMAIL信息' || p_check_char || '時異常：' || SQLERRM, 1, 300); -- Logging the error v_err_code := NULL; pub_error_handle.exception_no_raise('l_pub_public_biz.check_client_email', v_err_code, p_message, v_prompt); END check_client_email;
