CREATE OR REPLACE
FUNCTION f_phone (par_telefon IN VARCHAR2)
RETURN VARCHAR2
IS
/* 21.11.2022 Check whether phone number(s) passed via PAR_TELEFON are
valid.
Check:
- length has to be between 6 (e.g. 654123) and 20 (e.g. +385 91/123-4567) characters
- can contain signs: +-/, space, digits
Return:
- phone number and its errors (if there are any)
- NULL if everything is OK
*/
retval VARCHAR2 (200);
l_str VARCHAR2 (200); -- list of errors
l_cnt NUMBER := 0; -- number of errors
BEGIN
FOR cur_r IN ( SELECT REGEXP_SUBSTR (par_telefon,
'[^,]+',
1,
LEVEL) val
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT (par_telefon, ',') + 1)
LOOP
IF LENGTH (cur_r.val) < 6
THEN
l_str := l_str || '; ' || 'too short';
l_cnt := l_cnt + 1;
ELSIF LENGTH (cur_r.val) > 20
THEN
l_str := l_str || '; ' || 'too long';
l_cnt := l_cnt + 1;
END IF;
IF TRANSLATE (cur_r.val, '~1234567890-/+ ', '~') IS NOT NULL
THEN
l_str := l_str || '; ' || 'allowed: 0-9 +-/';
l_cnt := l_cnt + 1;
END IF;
IF l_cnt > 0
THEN
retval :=
retval || '; ' || cur_r.val || ': ' || LTRIM (l_str, '; ');
l_cnt := 0;
l_str := NULL;
END IF;
END LOOP;
RETURN ltrim(retval, '; ');
END f_phone;
/
电子邮件地址:
CREATE OR REPLACE
FUNCTION f_e_mail (par_e_mail IN VARCHAR2)
RETURN VARCHAR2
IS
/* 21.11.2022 Check e-mail addresses.
*/
l_email VARCHAR2 (200);
retval VARCHAR2 (200);
l_str VARCHAR2 (200); -- list of errors
l_cnt NUMBER := 0; -- number of errors
BEGIN
l_email := RTRIM (par_e_mail, CHR (13));
FOR cur_r IN ( SELECT TRIM (REGEXP_SUBSTR (REPLACE (l_email, ' ', ''),
'[^,]+',
1,
LEVEL)) val
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT (l_email, ',') + 1)
LOOP
IF NOT REGEXP_LIKE (
cur_r.val,
'^[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$')
THEN
l_str := l_str || '; ' || 'invalid e-mail address';
l_cnt := l_cnt + 1;
END IF;
IF l_cnt > 0
THEN
retval :=
retval || '; ' || cur_r.val || ': ' || LTRIM (l_str, '; ');
l_cnt := 0;
l_str := NULL;
END IF;
END LOOP;
RETURN ltrim(retval, '; ');
END f_e_mail;
/
CREATE AND COMPILE JAVA SOURCE NAMED test_email AS
import javax.mail.internet.InternetAddress;
import javax.mail.internet.AddressException;
public class email {
public static int validateEmail(String email) {
try {
InternetAddress emailAddr = new InternetAddress(email);
emailAddr.validate();
} catch (AddressException ex) {
return 0;
}
return 1;
}
}
然后可以创建PL/SQL Package 函数:
CREATE FUNCTION validateEmail RETURN NUMBER
AS LANGUAGE JAVA NAME 'email.validateEmail(java.lang.String) return int';
/
如果您愿意,也可以加载并使用Apache Commons Validator包:
CREATE AND COMPILE JAVA SOURCE NAMED test_email AS
import org.apache.commons.validator.routines.EmailValidator;
public class email {
public static int validateEmail(String email) {
return EmailValidator.getInstance().isValid(email) ? 1 : 0;
}
}
2条答案
按热度按时间rn0zuynd1#
您正在执行的检查相当粗糙。
a@b
包含@
符号,但它不是有效的电子邮件地址。或者,长度小于10个字符的字符串可能是123AB-$)1
,但它不是有效的电话号码。我并不是说我要发布的函数100%正确,但应该比你要做的好得多。如果你愿意,可以调整它们。
电话号码:
电子邮件地址:
一两个测试:
myss37ts2#
验证电子邮件地址的最佳方法是向其发送电子邮件,并让所有者确认:它是有效地址;这是所有者的正确地址;他们想签约为你服务。
否则,如果要测试有效的电子邮件地址,并且在数据库中启用了Java,则可以使用JavaMail包。
loadjava
实用程序将jar加载到数据库中。如果您愿意,也可以加载并使用Apache Commons Validator包: