oracle自定义函数的执行时间[3]

文章作者 100test 发表时间 2007:06:14 13:03:58
来源 100Test.Com百考试题网

--版本2
create or replace FUNCTION f_extract_birthday(certtype IN varchar, certno in VARCHAR, birthday in VARCHAR)
RETURN VARCHAR
IS
len SMALLINT.
c SMALLINT.
val NUMERIC(18, 0).
ret VARCHAR(4).
BEGIN
ret := .
IF certtype = 0 THEN -- 身份证
BEGIN
len := length(certno).

if (len = 15) THEN
val := CAST(certno AS NUMERIC).
ret := 19 || substr(certno, 7, 2).
ELSIF (len = 18) THEN
val := CAST(substr(certno, 1, 17) AS NUMERIC).
c := ascii(substr(certno, 18, 1)).

--if i <> 18 OR (c <> 88 AND c <> 120) OR c < 48 or c > 57 THEN -- 第18位为[0-9xX]
if (c = 88 OR c = 120 OR c >= 48 or c <= 57) THEN -- 第18位为[0-9xX]
ret := substr(certno, 7, 4).
end if.
end if.
EXCEPTION
WHEN value_error THEN -- 字符串转实数错误
NULL.
END.
END IF.
BEGIN
IF ret IS NULL THEN
val := CAST(birthday AS NUMERIC).
ret := substr(birthday, 1, 4).
END IF.

EXCEPTION
WHEN value_error THEN
NULL.
END.
BEGIN
-- 判断年份是否合法，暂定[1900, 2000]区间，过几年需要修改
--IF (ret <> ) THEN
IF (ret IS NOT NULL) THEN
val := CAST(ret AS INT).
IF (val < 1900 OR val > 2000) THEN
ret := .
END IF.
END IF.
EXCEPTION
WHEN value_error THEN -- 字符串转实数错误
ret := .
--dbms_output.put_line(certno || , || ret).
END.
RETURN ret.
end f_extract_birthday.

src="/oracle/js/wxgg_oracle.js">