--Procedure
CREATE PROCEDURE FOS.DAILY_PRIVILEGE_EXCEPTION_EXTRACT_SHARE(IN ad_as_of_date DATE)
SPECIFIC PR_DAILY_PRI_EXCE_SHA
language SQL
P1: BEGIN
XXOO
end p1@
-- Exception
当存储过程出现警告或者错误时需要通过异常来处理,来决定是否要继续执行存储过程。它通常在程序变量定义和程序正文之间进行定义。
如下:
DECLARE ps_error_message VARCHAR(300);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 ps_error_message = MESSAGE_TEXT;
IF (SUBSTR(ps_error_message, 1, 8) != 'SQL0438N') THEN
INSERT INTO FTR.TR_REPORT_EXTRACTION_LOG VALUES('FTR.PR_EXTRACT_PAYMENT_REPORTABLE_ACCOUNT', ps_error_message, CURRENT TIMESTAMP);
--When exception occurs, update status = 'F'(Failed)
UPDATE FTR.TR_REPORT_PROPERTY SET PROPERTY_VALUE = 'F' WHERE PROPERTY_CODE = 'TR_PAYMNET_STATUS';
END IF;
RESIGNAL SQLSTATE '88888';
END;
CALL FTR.TR_LOG('FTR.PR_EXTRACT_PAYMENT_REPORTABLE_ACCOUNT', 'Start Extraction');
COMMIT;
在db2中,如果要使用sqlcode那么必须在使用前declare。
存储过程异常的处理:
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;
--Grammar
(1).if grammar
IF(1=0) THEN
XXOO
end if;
(2).merge
DB2中的Merge语句可以将一个表中的数据合并到另一个表中,在合并的同时可以根据条件进行插入、删除、更新等操作,功能非常强大。
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2 在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。
ON (join condition) alias1 and alias2 condition
WHEN MATCHED THEN
UPDATE table_name SET col1 = col_val1,col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values)
ELSE IGNORE;
(3).temporary session table grammar
SESSION临时表只能存在于某一session或某一交易上下文中,对其他用户不可见DB2,V8增加了对临时表的索引的支持, 这对于提升查询速度比较有意义;
DECLARE GLOBAL TEMPORARY TABLE SESSION.CUSTOMER_INFORMATION
(
REPORT_DATE DATE,
USERNAME VARCHAR(12),
DEAL_NUMBER DECIMAL(5, 0),
PASSWORD VARCHAR(40)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS 用来指定即使该交易提交了, t1临时表仍然存在,只有当session终止后,t1表才会消失.
NOT LOGGED;
(4).case when
-- case when
CASE WHEN CATEGORY = 'CBBC' AND REGION_ID = ls_inv_rationale_crossboarder_region_id THEN '184'
WHEN CATEGORY = 'LEPO' THEN '152'
ELSE '005|033'
END
(5).create table
1.
create table xx like bb
insert into test.cashier_position select * from tmp.cashier_position
2.
create table CNF.test_Procedure_table as (select * from CNF.TRANSACTION_DETAILS) definition only; -- only definition
insert into test.cashier_position select * from tmp.cashier_position
3.
分2步,先删除原来的,再建新的
1、ALTER TABLE table_name DROP PRIMARY KEY (id)
说明:把主键的定义删除。
ALTER TABLE table_name ADD PRIMARY KEY (no)
说明:更改表得的定义把no设为主键。
(6).while cursor
declare ret_cur cursor with hold FOR SELECT CP.CUSTOMER_NUMBER,CP.SUB_ACCOUNT_NUMBER,CP.ACCOUNT_CURRENCY_CODE,CP.DEAL_COMPANY_CODE,CP.DEAL_BRANCH_CODE,CP.CURRENT_BALANCE_AMOUNT
,CST.RESP_COMPANY_CODE,CST.RESP_BRANCH_CODE,CST.TEAM_CODE,CST.OFFICER_CODE,CST.ASSISTANT_CODE FROM FOS.CASHIER_POSITION CP
OPEN ret_cur;
FETCH FROM ret_cur INTO CUSTOMER_NUMBER,SUB_ACCOUNT_NUMBER,ACCOUNT_CURRENCY_CODE,DEAL_COMPANY_CODE,DEAL_BRANCH_CODE,CURRENT_BALANCE_AMOUNT
,RESP_COMPANY_CODE,RESP_BRANCH_CODE,TEAM_CODE,OFFICER_CODE,ASSISTANT_CODE;
WHILE EOF = 0 DO
xxxxx
FETCH FROM ret_cur INTO CUSTOMER_NUMBER,SUB_ACCOUNT_NUMBER,ACCOUNT_CURRENCY_CODE,DEAL_COMPANY_CODE,DEAL_BRANCH_CODE,CURRENT_BALANCE_AMOUNT
,RESP_COMPANY_CODE,RESP_BRANCH_CODE,TEAM_CODE,OFFICER_CODE,ASSISTANT_CODE;
END WHILE;
CLOSE ret_cur;
(7).execute sql or sp
----定义
DECLARE CC VARCHAR(4000);
DECLARE SQLSTR VARCHAR(4000);
DECLARE st STATEMENT;
DECLARE CUR CURSOR WITH RETURN TO CLIENT FOR CC;
----执行动态SQL不返回
PREPARE st FROM SQLSTR;
EXECUTE st;
----执行动态SQL返回
PREPARE CC FROM SQLSTR;
OPEN CUR;
For example
--FOS.PR_CREDIT_GUARANTEE_DEDUCTION (in,in,in,in,out,out,out,out,out,out)
begin
declare v_stmt VARCHAR(300) DEFAULT 'call FOS.PR_CREDIT_GUARANTEE_DEDUCTION (?, ? , ?,?, ?, ?, ?, ?, ?, ?)';
PREPARE v_st from v_stmt;
set ls_grpfg = '';
EXECUTE v_st into li_amount1, li_amount2, li_guaramt1s, li_guaramt2s, li_guaramt1g, li_guaramt2g using ls_CUSTOMER_NUMBER, ls_SUB_ACCOUNT_NUMBER , ls_grpfg, ad_creditdate;
end;
-- Divide page
SELECT * FROM xx FETCH FIRST 10 ROWS ONLY
select * from (select rownumber()over(order by dest.role_id desc) as rownum,dest.* from FOS.SA_ROLE_MENU dest) tempT
where rownum > 5 and rownum <= 10
select * from syscat.tablespaces fetch first 10 rows only
select tabname, tbspace from syscat.tables where tabname='EMP';
----判断表是否存在
select count(*) into @exists from syscat.tables where tabschema = current schema and tabname='ZY_PROCESSLOG';
----定义返回值
declare rs0 cursor with return to caller for select 0 from sysibm.sysdummy1;
----得到插入的自增长列最大值
VALUES IDENTITY_VAL_LOCAL() INTO 变量
--Level
在DB2中,共有四种隔离级: RS,RR,CS,UR.DB2默认的隔离级别是CS
1.UR隔离级别: 此隔离级别下,如果是读取操作,不会出现任何的行级锁。
SELECT current date,current time,current timestamp FROM sysibm.sysdummy1 with ur;
2.CS隔离级别: 此隔离级别仅锁住当前处理的纪录。
3.RR隔离级别: 在此隔离级别下, DB2会锁住所有相关的纪录。在一个SQL语句执行期间, 所有执行此语句扫描过的纪录都会被加上相应的锁,具体的锁的类型还是由操作的类型来决定, 如果是读取,则加共享锁; 如果是更新, 则加独占锁。由於会锁定所有為获得SQL语句的结果而扫描的纪录,所以锁 的数量可能会很庞大,这个时候,索引的增加可能会对SQL语句的执行有很大的影响,因為索引会影响SQL语句扫描的纪录数量。
4.RS隔离级别: 此隔离级别的要求比RR隔离级别稍弱,此隔离级别下会锁定所有符合条件的纪录。 不论是读取, 还是更新, 如果SQL语句中包含查询条件, 则会对所有符合条件的纪录加相应的锁。如果没有条件语句,也就是对錶中的所有记录进行处理,则会对所有的纪录加锁。
--Data type
decimal(13,2):在DECIMAL(m,n)的设置中,数总共m位,整数的位数不能大于(m-n),但小数位可以大于n,但会从大于n的有效位开始截断
varchar
char(2)
int
date
datetime
-- Functions
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。 STR (<float_expression>[,length[, <decimal>]]) length 指定返回的字符串的长度,decimal 指定返回的小数位数。如果没有指定长度,缺省的length 值为10, decimal 缺省值为0。当length 或者decimal 为负值时,返回NULL;当length 小于小数点左边(包括符号位)的位数时,返回length 个*;先服从length ,再取decimal ;当返回的字符串位数小于length ,左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (<character_expression>, <integer_expression>) 返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (<character_expression>, <integer_expression>) 返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (<expression>, <starting_ position>, length) 返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。 CHARINDEX (<’substring_expression’>, <expression>) 其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。 PATINDEX (<’%substring _expression%’>, <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。 QUOTENAME (<’character_expression’>[, quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。 REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值,则返回NULL 。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。 REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。 REPLACE (<string_expression1>, <string_expression2>, <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。
4、SPACE()
返回一个有指定长度的空白字符串。 SPACE (<integer_expression>) 如果integer_expression 值为负值,则返回NULL 。
5、STUFF()
用另一子串替换字符串指定位置、长度的子串。 STUFF (<character_expression1>, <start_ position>, <length>,<character_expression2>) 如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。如果length 长度大于character_expression1 中 start_ position 以右的长度,则character_expression1 只保留首字符。
6、VALUE(EXPRESSION1,EXPRESSION2)
VALUE函数是用返回一个非空的值,当其第一个参数非空,直接返回该参数的值,如果第一个参数为空,则返回第一个参数的值。
eg:
--表示如果T1.ID为空,则返回空串,如果T1.ID不为空,则返回T1.ID。
SELECT VALUE(ID,'') FROM T1
7、COALESCE(ARG1,ARG2...)
COALESCE返回参数集中第一个非null参数
8、LENGTH(ARG1)
LENGTH函数返回参数的长度。
eg:SELECT LENGTH(NAME) FROM T1
9、CONCAT(ARG1,ARG2)
CONCAT函数返回两个字符串的连接。
eg:SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM T1
10、INSERT(ARG1,POS,SIZE,ARG2)
INSERT函数返回一个字符串,将ARG1从POS处删除SIZE个字符,将ARG2插入该位置
11、LOCATE(ARG1,ARG2,<POS>)
LOCATE函数在ARG2中查找ARG1第一次出现的位置,如果指定POS,则从ARG2的POS处开始查找ARG1第一次出现的位置。
eg:SELECT LOCATE('a',NAME) FROM T1
12、POSSTR(EXP1,EXP2)
POSSTR函数返回EXP2在EXP1中的位置。
eg:SELECT LOCATE(NAME,'a') FROM T1
13、REPEAT(ARG1,NUM_TIMES)
REPEAT函数返回ARG1被重复NUM_TIMES次的字符串。
eg:SELECT REPEAT(NAME,2) FROM T1
14、SUBSTR(ARG1,POS,<LENGTH>)
SUBSTR函数返回ARG1中POS位置开始的LENGTH个字符,如果没有指定LENGTH,则返回剩余的字符。
eg:SELECT SUBSTR('CDNJFDJFJD',5,2) FROM T1
六、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])
2、CONVERT()
CONVERT (<data_ type>[ length ], <expression> [, style])
1)data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT() 函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号,不同的样式编号有不同的输出格式。
七、日期函数
1、time(current timestamp); date(current timestamp); date(current timestamp); day(date_expression);year(current timestamp);hour(current timestamp);minute(current timestamp);second(current timestamp);month(date_expression);microsecond(current timestamp);
返回date_expression中的对应的值
2、VARCHAR_FORMAT(current TIMESTAMP,'yyyy-mm-dd');
Format date
4、DATEADD()
DATEADD (<datepart>, <number>, <date>) 返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>, <date1>, <date2>) 返回两个指定日期在datepart 方面的不同之处,即date2 超过date1的差距值,其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (<datepart>, <date>)以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART (<datepart>, <date>) 以整数值的形式返回日期的指定部分。此部分由datepart 来指定。DATEPART (dd, date) 等同于DAY (date) DATEPART (mm, date) 等同于MONTH (date)DATEPART (yy, date) 等同于YEAR (date)
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间
9、
Oracle数据类型改变函数:to_char()、to_date()、to_number()等;
如果仅仅取年,月,日等,可以用to_char(sysdate, 'YYYY'),to_char('MM') ,to_char('DD')取得。
只取年月日TRUNC(SYSDATE),取时分秒TO_CHAR(SYSDATE,'HH24:MI:SS')。
DB2数据类型改变函数:char()、varchar()、int()、date()、time()等;
取得年,月,日等的写法:YEAR(current timestamp),MONTH(current timestamp),DAY(current timestamp),HOUR(current timestamp),MINUTE(current timestamp),SECOND(current timestamp),MICROSECOND(current timestamp);
只取年月日可以用DATE(current timestamp),取时分秒TIME(current timestamp)。
Char()是定长字符串(1-255),varchar()为非定长字符串(1-32672)日期,时间形态变为字符形态: char(current date),char(current time)
将字符串转换成日期或时间形态:TIMESTAMP('2002-10-2012:00:00'),DATE('2002-10-20'),DATE('10/20/2002'),TIME('12:00:00')
目前DB2 V8也支持to_char和to_date
10、execute date
current date+1 year;
current date+3 years+2 months +15 days;
current time +5 hours -3 minutes +10 seconds;
days(current date)- days(date('2010-02-20'));
11、date-to-string
char(current date);
char(current time);
12、string-to-date
timestamp('2010-03-09-22.43.00.000000');
timestamp('2010-03-09 22:44:36');
date('2010-03-09');
date('03/09/2010');
time('22:45:27');
time('22.45.27');
13、between two date
--秒的小数部分为单位
values timestampdiff(1,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--秒为单位
values timestampdiff(2,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--分为单位
values timestampdiff(4,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--小时为单位
values timestampdiff(8,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--天为单位
values timestampdiff(16,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--周为单位
values timestampdiff(32,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--月为单位
values timestampdiff(64,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--季度为单位
values timestampdiff(128,char(current timestamp - timestamp('2010-01-01-00.00.00')));
--年为单位
values timestampdiff(256,char(current timestamp - timestamp('2010-01-01-00.00.00')));
SELECT current date,current time,current timestamp FROM sysibm.sysdummy1;
SELECT VARCHAR_FORMAT(current timestamp - (MONTH (current timestamp) -1 )months-(DAY (current timestamp) -1 )days,'yyyy-mm-dd') FROM sysibm.sysdummy1
DATE(VS_REPORT_YEAR||'-01-01'),
DATE(VS_REPORT_YEAR||'-01-01') + 1 YEAR - 1 DAY ,