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_info VARCHAR(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;