db2 获取返回的游标_db2 存储过程动态游标及函数返回值总结

SET SCHEMA = 'ZHSQ';

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ZHSQ";

CREATE OR REPLACE  PROCEDURE "ZHSQ"."QUERY_PICTURE" (

IN "USER_ORGS"VARCHAR(4000),

IN "BEGIN_ROW"INTEGER,

IN "END_ROW"INTEGER,

IN "ADMINTYPE"INTEGER,

IN "BASE_SQL"VARCHAR(4000),

OUT "SQL_TEXT"VARCHAR(4000) )

SPECIFIC "SQL160728011925574"

DYNAMIC RESULT SETS 2

LANGUAGE SQL

NOT DETERMINISTIC

EXTERNAL ACTION

MODIFIES SQL DATA

OLD SAVEPOINT LEVEL

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 v_stmt1;

DECLARE CUR_CUSTS CURSOR with return to caller FOR v_stmt2;

IF ADMINTYPE <> 1 THEN

OPEN CUR_CUST;

LOOP

FETCH CUR_CUST

INTO CUST_ID;

--EXIT WHEN CUR_CUST%NOTFOUND;

set STR_CUST_IDS = '';

OPEN CUR_CUST_ORG_REL;

LOOP

FETCH CUR_CUST_ORG_REL

INTO CO_ID, CO_ORG;

--EXIT WHEN CUR_CUST_ORG_REL%NOTFOUND;

IF DIFFCODE(USER_ORGS, CO_ORG) = 0 THEN

set  STR_CUST_IDS = STR_CUST_IDS || '''' || cast(CO_ID as varchar(255)) || ''',';

END IF;

END LOOP;

CLOSE CUR_CUST_ORG_REL;

END LOOP;

CLOSE CUR_CUST;

END IF;

set STR_IDS_LENGTH = LENGTH(STR_CUST_IDS);

set QUERY_SQL      = 'SELECT T.* FROM (' || BASE_SQL;

set COUNT_SQL      = 'SELECT COUNT(*) FROM (' || BASE_SQL;

IF STR_IDS_LENGTH > 0 THEN

set  STR_CUST_IDS = SUBSTR(STR_CUST_IDS, 1, LENGTH(STR_CUST_IDS) - 1);

set  QUERY_SQL    = QUERY_SQL || ' AND P.PID NOT IN (' || STR_CUST_IDS || ')';

set  COUNT_SQL    = COUNT_SQL || ' AND P.PID NOT IN (' || STR_CUST_IDS || ')';

END IF;

set QUERY_SQL = QUERY_SQL || ') T WHERE T.NUM > ' || BEGIN_ROW || ' AND T.NUM <= ' || END_ROW ;

set COUNT_SQL = COUNT_SQL || ') T';

-- SQL_TEXT  = STR_CUST_IDS || ' : ' || ' == ' || QUERY_SQL;

prepare v_stmt1 from COUNT_SQL;

OPEN CUR_COUNTS;

prepare v_stmt2 from QUERY_SQL;

OPEN CUR_CUSTS;

END;

函数返回值

CREATE OR REPLACE  FUNCTION "ZHSQ"."F_GET_LEVEL" (

fid_infoVARCHAR(255)

)

RETURNS INTEGER

return with b(fid,parent_fid,level1) as ( select fid,parent_fid,1  from func where  parent_fid = -1 union all select a.fid,a.parent_fid,level1+1    from func as a,b  where b.fid = a.parent_fid ) select b.level1 from b where b.fid =fid_info

函数动态sql返回值

CREATE OR REPLACE  FUNCTION "ZHSQ"."F_GET_ESTABLISH_CIRCLE_RATE" (

"V_ORGINCODE"VARCHAR(255),

"V_ECID"VARCHAR(255),

"V_AUDIT"VARCHAR(255) )

RETURNS INTEGER

SPECIFIC "SQL160727013623370"

LANGUAGE SQL

NOT DETERMINISTIC

EXTERNAL ACTION

READS SQL DATA

begin

DECLARE  v_count INTEGER;

DECLARE v_str  VARCHAR(512);

SET v_str = 'select count(0) INTO '||v_count||' from establish_circle t, circle_yhxx y where t.ecid = y.ecid(+) and t.ecstatus = ''1'' and t.orgincode = ''' ||  v_orginCode || '''';

if v_ecid is not null then

SET  v_str = v_str || ' and t.ecid = ''' || v_ecid || '''';

end if;

if v_audit is not null then

SET v_str = v_str ||  ' and t.ecaudit  = ''' || v_audit || '''';

end if;

values v_str into v_count;

return v_count;

end;