存储过程GETUSERLISTPAGE_PROC

CREATE OR REPLACE PROCEDURE GETUSERLISTPAGE_PROC(QYMC STRING,ZCH STRING,JYCS STRING,
   FDDBR STRING,QYZTFLAG STRING,PAGENUM NUMBER,MAXNUM NUMBER,PAGES OUT NUMBER,
      SIZES OUT NUMBER,L_C STRING,RESULTS_OUT OUT SYS_REFCURSOR)
 --内网综合查询
 IS
 WHERES STRING(30000):='';
 S STRING(30000);
 --v_firstNum number:=(pagenum-1)*maxnum + 1;--rownum 是从 1 开始的
 V_FIRSTNUM NUMBER:=PAGENUM+1;--ROWNUM 是从 1 开始的
 V_ENDNUM NUMBER:=((PAGENUM+15)/15)*MAXNUM;
 BEGIN
    IF QYMC IS NOT NULL THEN
      WHERES:=WHERES||'AND QYMC LIKE ''%'||QYMC||'%''';
    END IF;
    IF ZCH IS NOT NULL THEN
      WHERES:=WHERES||'AND ZCH LIKE '''||ZCH||'%''';
    END IF;
    IF JYCS IS NOT NULL THEN
      WHERES:=WHERES||'AND JYCS LIKE ''%'||JYCS||'%''';
    END IF;
    IF FDDBR IS NOT NULL THEN
      WHERES:=WHERES||'AND FDDBR LIKE ''%'||FDDBR||'%''';
    END IF;
    IF QYZTFLAG IS NOT NULL THEN
       IF INSTR(QYZTFLAG,'A') > 0 THEN
          WHERES:=WHERES||'AND PDDJ IS NULL';
       ELSE
          WHERES:=WHERES||'AND PDDJ = '''||QYZTFLAG||'''';
       END IF;
    END IF;
    --求游标集合
    IF L_C IS NOT NULL THEN
       S:='SELECT NBXH,ZCH,QYMC,JYCS,FDDBR,DJJG,CLRQ,QYLX,QYLXFLAG,QYZTFLAG,PDDJ,ZHFZ FROM
                  (SELECT NBXH,ZCH,QYMC,JYCS,FDDBR,DJJG,CLRQ,QYLX,QYLXFLAG,QYZTFLAG,(CASE WHEN (PDDJ IS NULL) THEN ''A'' ELSE PDDJ END ) AS PDDJ,
                  (CASE WHEN (ZHFZ IS NULL) THEN 100 ELSE ZHFZ END ) AS ZHFZ,ROWNUM AS RN FROM
                  (SELECT NBXH,ZCH,QYMC,ZS AS JYCS,FDDBR,
                  (SELECT ORGAN_NAME FROM SM_ORGAN_TB WHERE ORGAN_NO=DJJG) AS DJJG,CLRQ,QYLX,1 AS QYLXFLAG,XYDJ AS QYZTFLAG,
                  (SELECT QYZHPFZT.PDDJ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=AQYDJSX.ZCH ) AS PDDJ,
                  (SELECT QYZHPFZT.ZHFZ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=AQYDJSX.ZCH) AS ZHFZ
                  FROM AQYDJSX WHERE  TSLX=''1'' AND QYLX NOT LIKE ''8%''
                  UNION  ALL
                  SELECT NBXH,ZCH,ZHMC AS QYMC,JYDZXXDZ AS JYCS,FZRXM AS FDDBR,(SELECT ORGAN_NAME FROM SM_ORGAN_TB WHERE ORGAN_NO=DJJG) AS DJJG,
                  CLRQ,''9999'' AS QYLX,2 AS QYLXFLAG,XYDJ AS QYZTFLAG,(SELECT QYZHPFZT.PDDJ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=GTDJ.ZCH) AS PDDJ,
                  (SELECT QYZHPFZT.ZHFZ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=GTDJ.ZCH) AS ZHFZ
                  FROM GTDJ WHERE JYZT=''1'')
                  WHERE 1=1 '||WHERES||' ORDER BY QYLX) WHERE RN >='||V_FIRSTNUM||' AND RN <=' || V_ENDNUM;
         OPEN RESULTS_OUT FOR S;
    END IF;
    --求总条数
    SIZES:=0;
    PAGES:=0;
    IF L_C IS NULL THEN
       S:='SELECT COUNT(1) FROM (select ZCH,QYMC,JYCS,FDDBR,JYCS,QYZTFLAG,
                  (CASE WHEN (PDDJ IS NULL) THEN ''A'' ELSE PDDJ END ) AS PDDJ from(
                  SELECT ZCH,QYMC,FDDBR,ZS AS JYCS,XYDJ AS QYZTFLAG,
                  (SELECT QYZHPFZT.PDDJ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=AQYDJSX.ZCH ) AS PDDJ
                  FROM AQYDJSX WHERE  TSLX=''1'' AND QYLX NOT LIKE ''8%''
                  UNION  ALL
                  SELECT ZCH,ZHMC AS QYMC,FZRXM AS FDDBR,JYDZXXDZ AS JYCS,XYDJ AS QYZTFLAG,
                  (SELECT QYZHPFZT.PDDJ FROM QYZHPFZT WHERE QYZHPFZT.ZCH=GTDJ.ZCH) AS PDDJ
                  FROM GTDJ WHERE JYZT=''1'')
                  WHERE 1=1 '||WHERES||')';
                
        EXECUTE IMMEDIATE S INTO SIZES;
        --求总页数
        IF MOD(SIZES,MAXNUM)=0 THEN
           PAGES := SIZES/MAXNUM;
        ELSE
           PAGES := SIZES/MAXNUM + 1;
        END IF;
     END IF;
 END GETUSERLISTPAGE_PROC;

转载于:https://www.cnblogs.com/hupolinglong/articles/xgq_Oracle.html