db2自定义函数能返回几个参数_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.