DB2 自定义函数

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

一、自定义函数

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

CREATE FUNCTION ADMINISTRATORFUN1

(AAA VARCHAR(4)

)

RETURNS VARCHAR(4)

SPECIFIC ADMINISTRATORFUN1

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 ADMINISTRATORFUN2

(AAA INTEGER

)

RETURNS INTEGER

SPECIFIC ADMINISTRATORSQL060220111756000

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 ADMINISTRATORFUN3 (AAA INTEGER )

RETURNS VARCHAR(20)

LANGUAGE SQL

BEGIN ATOMIC

DECLARE bbb VARCHAR(20);

set bbb = (select MONTH from IWHLOOKUP_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: loopdb2

--

-- 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 loopdb2"

--

-- 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 loopsqc

-- 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://wwwsoftwareibmcom/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的 loopsqc就可以了

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的Control Center，在表对象上右击，其中有一个选项Show Related就可以这个对象和其他对象之间的关系，你说的那些函数用到了这个表也能看到。

如果函数同名，用 Specific Name来进行删除, 删除语法如下：

DROP SPECIFIC FUNCTION--specific-name

DB2内置数据类型可以分成数值型（numeric）、字符串型（character string）、图形字符串（graphic string）、二进制字符串型（binary string）或日期时间型（datetime）。还有一种叫做 DATALINK 的特殊数据类型。DATALINK 值包含了对存储在数据库以外的文件的逻辑引用。

可以采用TO_DATE函数将'2012-05'转化为日期格式

TO_DATE('2012-05', 'YYYY-MM')

例子：

db2 => create table TEST2(c1 timestamp)

DB20000I The SQL command completed successfully

db2 => insert into test2 values(to_Date('2012-05', 'YYYY-MM'))

DB20000I The SQL command completed successfully

db2 => select from test2

C1

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

2012-05-01-000000000000

1 record(s) selected

第二个问题：

比较日期格式直接用< 或者>符号就可以比较日期格式了。

如下：

db2 => select from test2 where c1 > current date

C1

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

0 record(s) selected

db2 => select from test2 where c1 <= current date

C1

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

2012-05-01-000000000000

1 record(s) selected