DB2 自定义函数

Db2 自定义函数和存储过程初步

一、自定义函数

先做个简单的，将输入参数原样返回。

CREATE FUNCTION ADMINISTRATOR.FUN1

(AAA VARCHAR(4)

)

RETURNS VARCHAR(4)

SPECIFIC ADMINISTRATOR.FUN1

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

STATIC DISPATCH

CALLED ON NULL INPUT

EXTERNAL ACTION

INHERIT SPECIAL REGISTERS

BEGIN ATOMIC

DECLARE bbb VARCHAR(4)

set bbb = aaa

return bbb

END

这是经过辅助工具生成的源码，我们可以发现如下几个特点：

1、 在函数名定义中加入（输入参数名 数据类型）

2、 随后定义返回值类型

3、 用BEGIN ATOMIC和END作为起止标示

4、 用set 定义赋值

5、 用return定义返回值

创建成功的函数怎莫说没找到？不要从字面上理解，很有可能是你输入函数的参数数据类型不匹配造成的，这在面向对象中不是叫多态吗。

改一下就可以输入整数了：

CREATE FUNCTION ADMINISTRATOR.FUN2

(AAA INTEGER

)

RETURNS INTEGER

SPECIFIC ADMINISTRATOR.SQL060220111756000

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

STATIC DISPATCH

CALLED ON NULL INPUT

EXTERNAL ACTION

INHERIT SPECIAL REGISTERS

BEGIN ATOMIC

DECLARE bbb INTEGER

set bbb = aaa

return bbb

END

以上写的函数叫什莫玩意，下面做个和数据库打交道的，反正函数主要就是用于做对照的，返回值唯一。

CREATE FUNCTION ADMINISTRATOR.FUN3 (AAA INTEGER )

RETURNS VARCHAR(20)

LANGUAGE SQL

BEGIN ATOMIC

DECLARE bbb VARCHAR(20)

set bbb = (select MONTH from IWH.LOOKUP_TIME where MONTH_ID = AAA)

return bbb

END

好了，懂得一些皮毛了。

二、存储过程

存储过程和函数很类似，只是用于批量实现一段逻辑的，而不是为了那个返回值，还有就是定义格式有些不同。

db2中提供了很多例子，下面就是一个定义游标和loop循环的。

-----------------------------------------------------------------------------

-- Licensed Materials - Property of IBM

--

-- Governed under the terms of the International

-- License Agreement for Non-Warranted Sample Code.

--

-- (C) COPYRIGHT International Business Machines Corp. 1995 - 2002

-- All Rights Reserved.

--

-- US Government Users Restricted Rights - Use, duplication or

-- disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

-----------------------------------------------------------------------------

--

-- SOURCE FILE NAME: loop.db2

--

-- SAMPLE: To create the LOOP_UNTIL_SPACE SQL procedure

--

-- To create the SQL procedure:

-- 1. Connect to the database

-- 2. Enter the command "db2 -td@ -vf loop.db2"

--

-- To call the SQL procedure from the command line:

-- 1. Connect to the database

-- 2. Enter the following command:

--db2 "CALL loop_until_space (?)"

--

-- You can also call this SQL procedure by compiling and running the

-- C embedded SQL client application, "loop", using the loop.sqc

-- source file available in the sqlproc samples directory.

-----------------------------------------------------------------------------

--

-- For more information on the sample scripts, see the README file.

--

-- For information on creating SQL procedures, see the Application

-- Development Guide.

--

-- For information on using SQL statements, see the SQL Reference.

--

-- For the latest information on programming, building, and running DB2

-- applications, visit the DB2 application development website:

-- http://www.software.ibm.com/data/db2/udb/ad

-----------------------------------------------------------------------------

CREATE PROCEDURE loop_until_space(OUT counter INT)

LANGUAGE SQL

BEGIN

DECLARE v_firstnme VARCHAR(12)

DECLARE v_midinit CHAR(1)

DECLARE v_lastname VARCHAR(15)

DECLARE v_counter SMALLINT DEFAULT 0

DECLARE c1 CURSOR FOR

SELECT firstnme, midinit, lastname

FROM employee

ORDER BY midinit DESC

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET counter = -1

-- initialize OUT parameter

SET counter = 0

OPEN c1

fetch_loop:

LOOP

FETCH c1 INTO

v_firstnme, v_midinit, v_lastname

-- Use a local variable for the iterator variable

-- because SQL procedures only allow you to assign

-- values to an OUT parameter

SET v_counter = v_counter + 1

IF v_midinit = ' ' THEN

LEAVE fetch_loop

END IF

END LOOP fetch_loop

CLOSE c1

-- Now assign the value of the local

-- variable to the OUT parameter

SET counter = v_counter

END；

说明：

1、 注释中写的很明白了，如何创建和执行。就是要注意，如果你想把代码拿出来执行，要把END @改为END

2、 不用C的 loop.sqc就可以了

3、 注意与函数的不同：

a) 起止标示begin\end不同

b)返回值定义不同，不定义return而在函数名定义中加OUT。。。。。。

格式定义

1．(IN deptNumber SMALLINT, OUT medianSalary DOUBLE)

使用（51，？）

2．游标

DECLARE c1 CURSOR FOR

SELECT firstnme, midinit, lastname

FROM employee

ORDER BY midinit DESC

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET counter = -1

Open C1

Close C1

FETCH c1 INTO

v_firstnme, v_midinit, v_lastname

3、Loop循环

fetch_loop:

LOOP

FETCH c1 INTO

v_firstnme, v_midinit, v_lastname

SET v_counter = v_counter + 1

IF v_midinit = ' ' THEN

LEAVE fetch_loop

END IF

END LOOP fetch_loop

4、if

IF v_midinit = ' ' THEN

LEAVE fetch_loop

END IF

5、 CASE v_mod

WHEN 0 THEN

END CASE

6、WHILE v_counter <(v_numRecords / 2 + 1) DO

SET v_salary1 = v_salary2

FETCH c1 INTO v_salary2

SET v_counter = v_counter + 1

END WHILE

DB2 Universal Database（DB2 UDB）用户定义函数（user-defined functions，UDF）允许您添加自己的函数定义，从而扩展 DB2 的内置函数。有了这些用户定义函数，就可以通过编写自己的用于事务、计算等方面的函数来定制 DB2，以满足业务需求。

UDF 鼓励代码重用，这样可以提高应用程序开发人员的生产率。如果您有很多实现相同逻辑的程序，那么可以通过 UDF 标准化该逻辑，并让所有这些程序使用相同的实现。一旦在 DB2 中定义好一个 UDF，便可以像使用内置 DB2 函数那样，在 SQL 语句中使用这个 UDF。如果是在应用程序中编写这个函数，而不是将其定义为 UDF，那么 SQL 查询工具 （例如 CLP）的用户将不能访问这个函数。UDF 允许任何前端，包括 Java™ 程序，访问这个标准逻辑。

直接从数据库引擎（而不是从应用程序代码）中调用 UDF 还可以使性能获得极大的提高，尤其是在将数据发送回应用程序进行进一步处理之前，可以使用 UDF 来过滤数据。

由于UDF是预先编译的，性能普遍优于一般的查询，UDF使用的存取计划一经编译就会相对稳定。我在开发中曾多次发现，使用UDF 代替查询或视图中的复杂部分会提高几倍甚至几十倍的性能，主要原因是迫使DB2使用指定的存取计划来充分利用index或者调整其访问过程(如Join 顺序、Filter位置等)。使用UDF 进行优化的基本思路是：将复杂查询分解为多个部分执行，针对每个部分优化处理，将各部分组合时能够避免存取计划的一些不必要变化，优化整体性能。

当然了，有一个小问题。自定义函数(UDB)只能包含那些不会改变数据库状态的语句(诸如INSERT、UPDATE或DELETE语句是不允许的)。并且只允许完整SQL PL语言的子集出现在SQL函数中(不能是CALL语句、游标和条件处理)。

自定义函数语法

CREATE FUNCTION 函数名(传入参数名 datatype, ……)

RETURNS datatype –返回值类型

BEGIN ATOMIC

函数体

END

例子

create function ADD(a int ,b int)

returns int

LANGUAGE SQL

BEGIN ATOMIC

DECLARE sum int default 0

set sum = a + b

return sum

END

调用测试:VALUES (ADD(1, 2))

---