最近公司接了一个项目需要把oracle转成db2数据库 db2数据库网上资源比较少,为此在函数和存储过程中折腾了好长时间,现总结如下:
CREATE OR REPLACE
PROCEDURE QUERY_PICTURE(USER_ORGS VARCHAR(4000),
BEGIN_ROW INTEGER,
END_ROW INTEGER,
ADMINTYPE INTEGER,
BASE_SQL VARCHAR(4000),
OUT SQL_TEXT VARCHAR(4000))
DYNAMIC RESULT SETS 2
BEGIN
DECLARE CUST_ID VARCHAR(4000);
DECLARE CO_ID VARCHAR(4000);
DECLARE CO_ORG VARCHAR(4000);
DECLARE STR_CUST_IDS VARCHAR(4000);
DECLARE QUERY_SQL VARCHAR(4000);
DECLARE COUNT_SQL VARCHAR(4000);
DECLARE STR_IDS_LENGTH INTEGER;
DECLARE CUR_CUST CURSOR for
SELECT C.PID FROM PICTURE C WHERE C.pSTATE <> -1;
DECLARE CUR_CUST_ORG_REL CURSOR for
SELECT RO.PICTUREID, cast(LISTAGG(O.ORGINCODE) as varchar(32670)) ORGS
FROM PICTURE_ORG_RELATION RO
LEFT JOIN ORGANIZATIONS O
ON O.ORGINCODE = RO.ORGINCODE
left join picture p
on p.pid = ro.pictureid
WHERE O.ORGSTATE <> -1
and p.pSTATE <> -1
GROUP BY RO.PICTUREID;
DECLARE CUR_COUNTS CURSOR with return to caller FOR COUNT_SQL;
DECLARE CUR_CUSTS CURSOR with return to caller FOR QUERY_SQL;
IF ADMINTYPE <> 1 THEN
OPEN CUR_CUST;
LOOP
FETCH CUR_CUST
INTO CUST_ID;
--EXIT WHEN CUR_CUST%NOTFOUND;
set STR_CUST_IDS = '';
OPEN