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
EMPNOFIRSTNAME LASTNAME
------ ------------ ---------------
000120 SEAN O'CONNELL
000230 JAMES JEFFERSON
000240 SALVATORE MARINO
000250 DANIEL SMITH
000260 SYBIL JOHNSON
000270 MARIA PEREZ
6 record(s) selected.
页:
[1]