壹、自定義函數
先做個簡單的，將輸入參數原樣返回。
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:
-- /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;