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

oracle中的pl/sql代码会涉及到两个执行引擎，一个专门处理标准的SQL语句，另外一个处理pl/sql的过程代码，一般在引擎切换上会带来cpu额外的开销。比如foreach循环语句和普通for循环的区别，就是foreach消除了引擎切换，一直驻留在执行标准SQL的引擎中，从而缩短了执行时间。
现在有一个客户信息表tacustomer， 包含了birthday, certificatetype, certificateno等字段，现在想要获知客户的生日信息。由于客户注册时生日等字段是可选择填写项，故大多数为空，所以要从证件号码certificateno中提取，certificatetype为证件类型，除身份证之外还有军官证、士兵证、户口本等枚举值，输入的值也不可靠，本来'0'代表身份证，但由于录入错误，使得非'0'值可能也指身份证，而'0'也可能输入的不是身份证。结合这些考虑因素，写了一个函数，输入参数为上述三个字段，输出为生日的年份。写了两个版本，在笔者的windows2003数据库服务器上（Intel(R) Xeon(R) CPU 5140 @ 2.33GHZ，4cpu，4.00GB内存），执行语句如下（tacustomer行数为200百万左右）：

-- 测试简单字符串连接的执行时间
SELECT COUNT(t.certificatetype || t.certificateno || t.birthday)
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

-- 测试自定义函数的执行时间
SELECT COUNT(f_extract_birthday(t.certificatetype, t.certificateno, t.birthday))
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

执行时间如下（秒）：
8.563
19.844（版本1）
57.953（版本2）

可以看出基本是3倍的关系（将上面的条件改为ROWNUM < 1000000，同样满足3倍的关系），版本1和版本2之间的区别是内部实现不同。第一代身份证15位必须全为0-9的数字，第二代18位的前17位全为数字，最后一个为数字或&#39;X&#39;。版本1和2对这个是否数字的校验采用了不同的方式。版本1逐个检查，版本2用cast(certno as numeric)并捕捉异常的方式，结果版本2比1快3倍。从中得出的结论，自定义函数一般性能比不上系统内建函数。

附两个版本的代码：

--版本1
create or replace FUNCTION f_extract_birthday(id in VARCHAR, birthday in VARCHAR)
RETURN VARCHAR
IS
i integer;
--id VARCHAR(18);
--birthday VARCHAR(8);

yyyy VARCHAR(4);
len SMALLINT;
trans BOOLEAN;
c SMALLINT;
ret VARCHAR(4);
val NUMERIC(18, 0);
BEGIN
i := 1;
--id := &#39;430302810315405&#39;;
--id := &#39;43030219810315405X&#39;;
--birthday := &#39;19790315&#39;;
trans := FALSE;
len := length(id);
--val := CAST(id AS NUMERIC);
--dbms_output.put_line(val);

if (len = 15) then
  -- 检查是否全为数字，否则转换操作会抛异常，导致程序崩溃，ascii(&#39;0&#39;) = 48, ascii(&#39;9&#39;) = &#39;57&#39;
  --for i in 1 .. 15 loop
  WHILE i <= 15 LOOP
    --ch := substr(id, i, 1); -- 非数字会报错
    c := ascii(substr(id, i, 1));
    if c < 48 or c > 57 then
      exit;
    end if;
    i := i + 1;
  end loop;
  
  IF i = 16 THEN
    trans := TRUE;
    ret := &#39;19&#39; || substr(id, 7, 2);
  END IF;
end if;

if (len = 18) then
  -- 2006-10-18 luocm
  -- 检查是否全为数字，否则转换操作会抛异常，导致程序崩溃，ascii(&#39;0&#39;) = 48, ascii(&#39;9&#39;) = &#39;57&#39;
  --for i in 1 .. 17 loop
  WHILE i <= 17 LOOP
    --ch := substr(id, i, 1); -- 非数字会报错
    c := ascii(substr(id, i, 1));
    if c < 48 or c > 57 then
      exit;
    end if;
    i := i + 1;
  end loop;

  c := ascii(substr(id, 18, 1));
  --if i <> 18 OR (c <> 88 AND c <> 120) OR c < 48 or c > 57 THEN -- 第18位为[0-9xX]
  if i = 18 AND (c = 88 OR c = 120 OR c >= 48 or c <= 57) THEN -- 第18位为[0-9xX]
    trans := TRUE;
    ret := substr(id, 7, 4);
  end if;
end if;

IF trans = FALSE THEN
   i := 1;
   
   WHILE i <= 8 LOOP
    c := ascii(substr(birthday, i, 1));
    if c < 48 or c > 57 then
      exit;
    end if;
    i := i + 1;
   end loop;
  
   IF i > 8 THEN
     yyyy := substr(ltrim(rtrim(birthday)), 1, 4);
     IF yyyy < 1900 OR yyyy > 2000 THEN
      ret := &#39;&#39;;
     ELSE
      ret := yyyy;
     END IF;
   ELSE
     ret := yyyy;
   END IF;
END IF;

RETURN ret;
end f_extract_birthday;


--版本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 := &#39;&#39;;

  IF certtype = &#39;0&#39; THEN -- 身份证
    BEGIN
      len := length(certno);
   
      if (len = 15) THEN
        val := CAST(certno AS NUMERIC);
        ret := &#39;19&#39; || 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 <> &#39;&#39;) THEN
    IF (ret IS NOT NULL) THEN
      val := CAST(ret AS INT);
      IF (val < 1900 OR val > 2000) THEN
        ret := &#39;&#39;;
      END IF;
    END IF;
  EXCEPTION
    WHEN value_error THEN -- 字符串转实数错误
      ret := &#39;&#39;;
      --dbms_output.put_line(certno || &#39;,&#39; || ret);
  END;

  RETURN ret;
end f_extract_birthday;

真是令人钦佩

晕 !