oracle自定义函数的执行时间

--版本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;

上一页  [1] [2] [3] 

【责编:Youping】