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函數相關的內容