DB2自定义函数初识

色破飞机

　　1、创建简单的自定义函数

CREATE function ca (r DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 3.14159 * (r * r);
　　2、使用函数

SELECT ca(96.8) AS area FROM sysibm.sysdummy1
AREA
------------------------
+2.94374522816000E+004
1 record(s) selected.
　　3、创建返回table的自定义函数

CREATE FUNCTION jobemployees (job VARCHAR(8))
RETURNS TABLE (
empno CHAR(6),
firstname VARCHAR(12),
lastname VARCHAR(15)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT empno, firstnme, lastname
FROM employee
WHERE employee.job = jobemployees.job;
　　4、使用

SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk
EMPNO  FIRSTNAME    LASTNAME
------ ------------ ---------------
000120 SEAN         O'CONNELL
000230 JAMES        JEFFERSON
000240 SALVATORE    MARINO
000250 DANIEL       SMITH
000260 SYBIL        JOHNSON
000270 MARIA        PEREZ
6 record(s) selected.