Db2 sql summarize

--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 ,