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;
