db2循环、数组的基本使用

db2循环、数组的基本使用

本人银行菜鸟一只，平时写SQL写得很少，在工作中遇到了相关问题东拼西凑地解决了，写一篇博客记录一下~
参考文章：
db2中的循环语句：https://blog.csdn.net/yjdpkto/article/details/3029307
DB2 for IBM i 7.1 新功能: 对 SQL 数组的支持(转)：https://www.cnblogs.com/millen/archive/2011/12/01/2270305.html
IBM db2官方文档:https://www.ibm.com/docs/zh/db2/9.7?topic=support-triggers-plsql

db2循环

1．利用得到的游标在存储过程中循环：

       DECLARE CUR_FEESET CURSOR WITH RETURN TO CALLER FOR (
              SELECT
                     --
              FROM 表
              WHERE 条件
              );
       OPEN CUR_FEESET;--得到游标
       --得到游标记录数
       SELECT
       count(CIF_CSTNO)
       into v_count
       FROM CB_CSTINF A ,CB_CSTBSNINF B
       WHERE A.CIF_STT<>'3' AND A.CIF_CSTNO = B.CBI_CSTNO ;   --
       FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;--
       WHILE V_COUNT>0 DO
　　　　 ……..
         FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;--
       END WHILE; --

２．另一种db2标准循环格式(leave、iterate 用法)：

SET V_COUNT = LENGTH(V_VALIDAUTHCOMBOS);--

SET V_INDEX = 0;--

AUTHLOOP:
LOOP
             IF V_INDEX >= V_COUNT THEN
                     LEAVE AUTHLOOP;--相当于break
             END IF;--
              ....
             SET V_INDEX = V_INDEX + 1;--
             .......
             IF 条件　THEN
                   ITERATE AUTHLOOP;-- 相当于continue
             END IF;--      
END LOOP;--

3.截取字符串的循环（设V_TEMP=‘CB1001|CB1002|CB1003|’）：

SET V_LENGTH = LENGTH(V_TEMP);
   WHILE V_LENGTH>0 DO
        SET V_POS = POSSTR(V_TEMP,'|');
        SET V_CURRENT_BSN = SUBSTR( V_TEMP, 1, V_POS-1 );
        SET V_TEMP = SUBSTR( V_TEMP, V_POS+1 );
        SET V_LENGTH = LENGTH(V_TEMP);
        --最后一个字段，不再截取
        SET V_BSNTYPE = V_CURRENT_BSN;
   END WHILE;  

4.游标循环（不用open 游标）：

DROP PROCEDURE TESTFOR;
    CREATE PROCEDURE TESTFOR()
    LANGUAGE SQL
    BEGIN
         DECLARE V_TEMP1 VARCHAR(2);
         DECLARE V_TEMP2 VARCHAR(70);
         FOR V1 AS CURSOR1 CURSOR FOR
              SELECT STUDENT_ID AS TEMP1,STUDENT_NAME AS TEMP2 FROM STUDENT
         DO
              DELETE FROM STUDENT WHERE STUDENT_ID = TEMP1;
              SET V_TEMP1 = TEMP1;
              SET V_TEMP2 = TEMP2;
         END FOR;
         COMMIT;
    END;

db2数组的使用

在使用数组时要注意当前db2数据库版本是否支持数组

1.建立数组数据类型

CREATE TYPE IDList AS INTEGER ARRAY[1000];--下标为1-1000的整数值
CREATE TYPE Projects AS VARCHAR(100) ARRAY[50];--50个varchar(100)类型的值
CREATE TYPE Resumes AS XML ARRAY[];

数组类型建立之后，相关的数据类型信息可以在系统编目表中查询到。系统表SYSCAT.DATATYPES中的每一行都包含了一个数据类型的信息，数组数据类型也是一样的。注意，列METATYPE的值如果是’A’，则表明这是一个数组类型，如果是’U’，则是一个用户定义类型，如果是’S’，则表示是一个系统自定义类型。另外，列source_type表示这个数组类型的每一个元素是哪种数据类型的，常用的如”INTERGER”, ”VARCHAR”, “GRAPHIC” 或者”FLOAT”等等。同时，我们可以通过列maximum_cardinality获得此数组类型定义的最大基数，而如果此数组的基本数据类型是字符类型的话，列CCSID表示了此字符类型所使用的字符集编码。

2.声明数组变量

        
DECLARE intArrayVal1 IntArray;
DECLARE charArrayVal VarcharArray;

3.数组构造函数

SET StuList = ARRAY['Cynthia', 'Andy', 'Tom', 'Mary'];--枚举构造函数，这里的ARRAY不是之前声明的数组数据类型（直接写ARRAY）
SET StuList = ARRAY[SELECT name FROM allmembers WHERE occupation = 'student'];--查询构造函数

4.对数组元素进行单独赋值

SET StuArray[1] = 'Cynthia';
SET TeamMember[i] = (select count(headcount) from namelist where teamcode = i);
SET testArray[10] = null;

数组与表的互换函数示例以及数组类型的使用限制由于本次没有用到，见参考链接2。

本次需求：根据机构号插入固定的几条数据（每个机构需要插入的数据相同）

代码示例：

DROP TYPE V_ARRAY;
CREATE TYPE V_ARRAY AS VARCHAR(20) ARRAY[2]; --示例
BEGIN 
DECLARE CORGID VARCHAR(20);
DECLARE arr1 V_ARRAY;
DECLARE num INTEGER DEFAULT 1;
SET arr1 = ARRAY['org01','org02'];
AUTHLOOP:
LOOP
SET CORGID = arr1[num];
INSERT INTO TEST (ORGID,USER,DATE) VALUES (CORGID,'USER1','2021-05-07');
SET num =num+1;
IF num>2 THEN
    LEAVE AUTHLOOP;
END IF;
END LOOP;
END

TIPS: 先drop再create数组数据类型容易报错(如果数据库中没有该类型对应的名称),可以使用create or replace代替：

--修改后的代码
CREATE OR REPLACE TYPE V_ARRAY AS VARCHAR(20) ARRAY[2]; --示例
BEGIN 
DECLARE CORGID VARCHAR(20);
DECLARE arr1 V_ARRAY;
DECLARE num INTEGER DEFAULT 1;
SET arr1 = ARRAY['org01','org02'];
AUTHLOOP:
LOOP
SET CORGID = arr1[num];
INSERT INTO TEST (ORGID,USER,DATE) VALUES (CORGID,'USER1','2021-05-07');
SET num =num+1;
IF num>2 THEN
    LEAVE AUTHLOOP;
END IF;
END LOOP;
END