db2存储过程case函数_如何执行db2存储过程

1. 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, plication or
-- disclosure restricted by GSA ADP Schele Contract with IBM Corp.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: loop.db2
--
-- SAMPLE: To create the LOOP_UNTIL_SPACE SQL procere
--
-- To create the SQL procere:
-- 1. Connect to the database
-- 2. Enter the command "db2 -td@ -vf loop.db2"
--
-- To call the SQL procere 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 procere 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 proceres, 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 proceres 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;

2. 如何在DB2中执行存储过程

1、db2
create
database
数据库名
<--
创建数据库
2、db2
connect
to
数据库名
user
用户名
using
用户密码
<--
连接数据库
3、db2
-tvf
otpdb_v3_db2.sql
<--
为新建数据库建立表结构
4、db2
-td@
-f
存储过程文件绝对路径
<--
导入存储过程，无错误会提示成功
4、调用存储过程：
Windows
下：db2
call
存储过程名(参数1,参数2)
AIX
下：db2
<--
要先进入DB2，方可调用存储过程或执行SQL语句
db2=>call
存储过程名(参数1,参数2)
5、验证插入数据是否成功
Windows
下：db2
select
count(*)
from
FTOTP_USERINFO
AIX
下：db2
<--
要先进入DB2，方可调用存储过程或执行SQL语句
db2=>select
count(*)
from
FTOTP_USERINFO
Windows
下：db2
select
count(*)
from
FTOTP_TOKENINFO
AIX
下：db2
<--
要先进入DB2，方可调用存储过程或执行SQL语句
db2=>select
count(*)
from
FTOTP_TOKENINFO
6、db2
SELECT
TOKEN,
PUBKEY
FROM
FTOTP_TOKENINFO
FETCH
FIRST
10000
ROWS
ONLY
>
要保存文件的绝对全路径
<--
从
DB2
中导出前一万条记录
windows
-
e.g.
db2
SELECT
TOKEN,
PUBKEY
FROM
FTOTP_TOKENINFO
FETCH
FIRST
10000
ROWS
ONLY
>
c:\abc.txt
AIX
-
e.g.
db2
SELECT
TOKEN,
PUBKEY
FROM
FTOTP_TOKENINFO
FETCH
FIRST
10000
ROWS
ONLY
>
c:\abc.txt
<--
注意：不能先进入DB2，执行查询与导出命令组合
7、db2
drop
procere
存储过程名
<--
删除存储过程
8、db2
drop
database
数据库名
<--
删除指定名称的数据库
注，如果删除时提示有应用程序连接到这个数据库上，可以用如下命令断开所有应用程序的连接：
db2
force
application
all
<--
断开所有应用程序的连接

3. 在db2的命令行如何创建存储过程和函数

1.首先连接数据库 2.发出一下命令： DB2 -td@ -vf createSQLproc.DB2 createSQLproc.DB2文件中是创建存储过程的SQL语句； -td 选项让命令行处理程序使用@作为语句终止字符； -v 选项让命令处理程序将命令文本回显到标准输出； -f 让命令行处理程序从指定文件读取命令输入。

4. DB2中建立存储过程的详细参数和语法

DECLARE CHOICE2 CURSOR WITH RETURN TO CALLER FOR --声明游标 CHOICE2
SELECT COMPANYID,DEPTID,SEQID,SUBMITTIME,SUBMITDATE,
B.GRADEDESC HYEAR,PRODUCTID,PRODUCTCODE,PRODUCTDESC,
A.GRADEID,PRICE,SFZDPY,LQUANT,MQUANT,MCQUANT,LCQUANT,
UQUANT,OTHQUANT1,OTHQUANT2,OTHQUANT3,OTHQUANT4,EMP1,EMP2,STATUS,A.REMARK,
DECIMAL(ABS(MCQUANT-MQUANT)/NULLIF(MQUANT,0)*100,10,2)TAG
FROM T_SUPPLY_PPB_HY A
LEFT JOIN T_SUPPLY_GRADATION B ON A.GRADEID=B.GRADEID
WHERE HYEAR=TO_CHAR(P_NF)||P_BN
ORDER BY B.GRADEID,PRODUCTCODE,A.PRICE;

--1.DECIMAL（P，S）十进制数，小数点位置由数字的精度（P）和小数位（S）确定。
-- 精度是数字的总位数，必须小于32。小数位是小数部分数字的位数且总是小于或等于精度值。
-- 如果未指定精度和小数位，则十进制值的缺省精度为5，缺省小数位为0。
--2.语法：NULLIF ( expression , expression )
-- expression：（常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组）
-- 如果两个表达式不相等，NULLIF 返回第一个 expression 的值。如果相等，NULLIF 返回第一个 expression 类型的空值。如果两个表达式相等且结果表达式为 NULL，NULLIF 等价于 CASE 的搜索函数。

5. 如何执行db2存储过程

6. db2在linux后台执行创建存储过程以及函数的.sql文件报错，但是在toad中执行没问题

在toad的左边窗口中找到存储过程标签，选中存储过程，然后右键--execute procere。会弹出窗口，输入参数，就可以调试存储过程的。

7. 请教db2存储过程如何写变量为多个值的条件语句

CREATEPROCEDURETestIfElse(
p_aINT,
OUTp_bINT
)
LANGUAGESQL
BEGIN
IFp_a=1ORp_a=2THEN
SETp_b=100;
ELSEIFp_a=3ORp_a=4THEN
SETp_b=200;
ENDIF;
END

这样的效果么?

8. DB2中向目标表中取数，我修改了存储过程中的两个字段的取数逻辑，并且left jion了两张新表，

pc_id,screen_id哪个优先？以pc_id优先为例：
oracle用decode函数，sqlserver可以用case...when，给你个oracle的例子
create procere add_table
is
begin
insert into wip_led_opration(operation_id,led_pc_id,led_screen_id)
select operation_id,led_pc_id, decode(led_pc_id,null,led_screen_id,null)
-- 逻辑：第三列led_screen_id，先判断led_pc_id是否为空，为空则用led_screen_id，非空即led_pc_id存在，则led_screen_id列位置留空
from fnd_led_pc ,fnd_led_screen ,fnd_operation
where fnd_operation.status=1
and fnd_led_screen.status=1
and fnd_led_pc.status=1
-- and fnd_operation.status=fnd_led_screen.status --这个条件是多余的，两个值都=1了
and fnd_led_screen.status=fnd_led_pc.status ;
end;
/* 另外提几点建议
1. 建议给三个表加上别名
2. 多余的连接条件我注释掉了，虽然对执行计划应该没有太大影响
3. SELECT后最好标识出源表，比如fnd_operation.operation_id，这样自己看着也清楚
*/

9. db2 存储过程异常处理

存储过程异常的处理：
DECLARE handler-type HANDLER FOR condition handler-action

异常处理器类型(handler-type)有以下几种：
CONTINUE 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句。
EXIT 在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。
UNDO 在处理器操作执行之前，DB2会回滚存储过程中执行的SQL操作。在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。
异常处理器可以处理基于特定SQLSTATE值的定制异常，或者处理预定义异常的类。预定义的3种异常如下所示：
NOT FOUND 标识导致SQLCODE值为+100或者SQLSATE值为02000的异常。这个异常通常在SELECT没有返回行的时候出现。
SQLEXCEPTIOIN 标识导致SQLCODE值为负的异常。
SQLWARNING 标识导致警告异常或者导致+100以外的SQLCODE正值的异常。
如果产生了NOT FOUND 或者SQLWARNING异常，并且没有为这个异常定义异常处理器，那么就会忽略这个异常，并且将控制流转向下一个语句。如果产生了SQLEXCEPTION异常，并且没有为这个异常定义异常处理器，那么存储过程就会失败，并且会将控制流返回调用者。
以下示例声明了两个异常处理器。 EXIT处理器会在出现SQLEXCEPTION 或者SQLWARNING异常的时候被调用。EXIT处理器会在终止SQL程序之前，将名为stmt的变量设为"ABORTED",并且将控制流返回给调用者。UNDO处理器会将控制流返回给调用者之前，回滚存储过程体中已经完成的SQL操作。

清单3：异常处理器示例
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = 'ABORTED';
DECLARE UNDO HANDLER FOR NOT FOUND;
如果预定义异常集不能满足需求，就可以为特定的SQLSTATE值声明定制异常，然后再为这个定制异常声明处理器。语法如下：

清单4：定制异常处理器
DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'
处理器可以由单独的存储过程语句定义，也可以使用由BEGIN…END块界定的复合语句定义。注意在执行符合语句的时候，SQLSATE和SQLCODE的值会被改变，如果需要保留异常前的SQLSATE和SQLCODE，就需要在执行复合语句的第一个语句把SQLSATE和SQLCODE赋予本地变量或参数。
通常，会为存储过程定义一个执行状态的输出参数(例如：poGenStatus)。
declare sqlcode integer default 0;
begin
declare continue handler for sqlexception set ret = sqlcode;
declare continue handler for sqlwarning set ret = sqlcode;
declare continue handler for not found set ret = sqlcode;
end ; --异常的声明

--异常的处理
if sqlcode< 0 or sqlcode= 100 then
set O_RetCod = RetCode;
set O_RetMsg = 'CLN02:产品实例关联客户过程出错!';
insert into LOG.OPER_LOG_TAB(PROC_NAME,OBJ_TAB,REGION_COD,OPER_COUNT,ERR_CODE,DATA_TIME,OPER_TIME)
values('P_DW_CLEAN','GLOBAL TEMP',0,0,retcode,CHAR(last_3_mon_time),current TIMESTAMP);
return;
else
set RetCode = 0;
end if;

10. 关于DB2存储过程

db2中的listagg函数就可以啊

db2存储过程case函数

与db2存储过程case函数相关的内容