暮色呼如
Db2 自定义函数和存储过程初步一、自定义函数先做个简单的，将输入参数原样返回。CREATE FUNCTION ADMINISTRATOR.FUN1(AAA VARCHAR(4))RETURNS VARCHAR(4)SPECIFIC ADMINISTRATOR.FUN1LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASTATIC DISPATCHCALLED ON NULL INPUTEXTERNAL ACTIONINHERIT SPECIAL REGISTERSBEGIN ATOMICDECLARE 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 INTEGERSPECIFIC ADMINISTRATOR.SQL060220111756000LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASTATIC DISPATCHCALLED ON NULL INPUTEXTERNAL ACTIONINHERIT SPECIAL REGISTERSBEGIN ATOMICDECLARE bbb INTEGER;set bbb = aaa;return bbb;END;以上写的函数叫什莫玩意，下面做个和数据库打交道的，反正函数主要就是用于做对照的，返回值唯一。CREATE FUNCTION ADMINISTRATOR.FUN3 (AAA INTEGER )RETURNS VARCHAR(20)LANGUAGE SQLBEGIN ATOMICDECLARE 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 SQLBEGINDECLARE v_firstnme VARCHAR(12);DECLARE v_midinit CHAR(1);DECLARE v_lastname VARCHAR(15);DECLARE v_counter SMALLINT DEFAULT 0;DECLARE c1 CURSOR FORSELECT firstnme, midinit, lastnameFROM employeeORDER BY midinit DESC;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET counter = -1;-- initialize OUT parameterSET counter = 0;OPEN c1;fetch_loop:LOOPFETCH c1 INTOv_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 parameterSET v_counter = v_counter + 1;IF v_midinit = ' ' THENLEAVE fetch_loop;END IF;END LOOP fetch_loop;CLOSE c1;-- Now assign the value of the local-- variable to the OUT parameterSET 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 FORSELECT firstnme, midinit, lastnameFROM employeeORDER BY midinit DESC;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET counter = -1;Open C1Close C1FETCH c1 INTOv_firstnme, v_midinit, v_lastname;3、Loop循环fetch_loop:LOOPFETCH c1 INTOv_firstnme, v_midinit, v_lastname;SET v_counter = v_counter + 1;IF v_midinit = ' ' THENLEAVE fetch_loop;END IF;END LOOP fetch_loop;4、ifIF v_midinit = ' ' THENLEAVE fetch_loop;END IF5、 CASE v_modWHEN 0 THENEND CASE;6、WHILE v_counter < (v_numRecords / 2 + 1) DOSET v_salary1 = v_salary2;FETCH c1 INTO v_salary2;SET v_counter = v_counter + 1;END WHILE;