考试统计查询包-优快云博客

CREATE OR REPLACE PACKAGE BODY EXAM_COUNT AS
/******************************************************************************
NAME: EXAM_COUNT
PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   --------- ---------- --------------- ------------------------------------
   1.0        2006-12-13             1. Created this package body.
******************************************************************************/

FUNCTION MyFunction(Param1 IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN Param1;
END;

PROCEDURE EXAM_COUNT_PROCEDURE(
CITY KS.DSDM%TYPE,
COUNTRY KS.XQDM%TYPE,
SCHOOL KS.BMDDM%TYPE,
EXAMTYPE varchar2,
STUTYPE varchar2,
KSKL varchar2,
PAGEINDEX INTEGER,
PAGESIZE INTEGER,
COUT OUT NUMBER,

RS OUT EXAM_CURSOR
) IS
    V_DS VARCHAR2(2);
V_XQ VARCHAR2(4);
V_BMD VARCHAR2(7);
V_SQL VARCHAR2(3000);
V_PARA VARCHAR2(1000);
V_KSLX VARCHAR2(1000);
V_COUNT NUMBER;
V_END INTEGER;
V_FROM INTEGER;
BEGIN
      V_DS := CITY;
      V_XQ := COUNTRY;
      V_BMD := SCHOOL;
      V_END:= PAGEINDEX * PAGESIZE + PAGESIZE;
      V_FROM := V_END - PAGESIZE+1;

   V_PARA:=' AND KS.KSZTDM=''002'' ';
   IF KSKL !='-1' AND KSKL IS NOT NULL THEN
   V_PARA:=V_PARA|| ' AND KS.KLDM='''||KSKL||'''';
   END IF;
   IF STUTYPE !='-1' AND STUTYPE IS NOT NULL THEN
   V_PARA:=V_PARA|| ' AND KS.KSLBDM='''||STUTYPE||'''';
   END IF;

---------------------------------------------------------------------------------------------------
      IF V_BMD != '0000000' AND V_BMD IS NOT NULL AND V_BMD !='-1'
      THEN                                               --统计一个中学的人数
         V_DS := SUBSTR (V_BMD, 1, 2);
    v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE DSDM='''
            || v_ds
            || ''' AND BMDDM='''
            || v_bmd
            || ''''
            || v_para;
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;

cout := v_count;

   IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
                       --如果选有考试类型,则统计该考试类型,否则,统计所以考试类型;
    V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.BMDDM='''
    ||V_BMD
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY CON DESC';

   -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
    ELSE
          V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.BMDDM='''
    ||V_BMD
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
  END IF;
   --DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);

----------------------------------------------------------------------------------------------------------
      ELSIF V_XQ != '0000' AND V_XQ IS NOT NULL AND V_XQ !='-1'
      THEN
         V_DS := SUBSTR (V_XQ, 0, 2);

         --以中学分组统计一个县的所有人数;
   IF V_BMD ='0000000' THEN
    IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN

     v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE DSDM='''
            || v_ds
            || ''' AND xqdm='''
            || v_xq
   || ''' AND KSLXDM='''
   ||EXAMTYPE
            || ''''
            || v_para;
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;
   cout := v_count;

    V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.XQDM='''
    ||V_XQ
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY CON DESC';

   -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
    ELSE
     v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE DSDM='''
            || v_ds
            || ''' AND xqdm='''
            || v_xq
            || ''''
            || v_para;
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;
   cout := v_count;

     V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.XQDM='''
    ||V_XQ
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
  END IF;
  ELSIF V_BMD ='-1' THEN
      IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
    v_sql :=
               'SELECT COUNT(DISTINCT KS.XQDM) AS CON FROM KS WHERE DSDM='''
            || v_ds
            || ''' AND xqdm='''
            || v_xq
   || ''' AND KSLXDM='''
   ||EXAMTYPE
            || ''''
            || v_para;
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;
   cout := v_count;
    V_SQL:=' SELECT KS.XQDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.XQDM='''
    ||V_XQ
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.XQDM ORDER BY CON DESC';

  -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
    ELSE
     v_sql :=
               'SELECT COUNT(DISTINCT KS.XQDM) AS CON FROM KS WHERE DSDM='''
            || v_ds
            || ''' AND xqdm='''
            || v_xq
            || ''''
            || v_para;
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;
   cout := v_count;
     V_SQL:=' SELECT KS.XQDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.XQDM='''
    ||V_XQ
    ||''''
    ||V_PARA
    ||' GROUP BY KS.XQDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
   END IF;
  END IF;
------------------------------------------------------------------------------------------------------------
      ELSIF V_DS != '00' AND V_DS IS NOT NULL AND V_DS !='-1'
      THEN
         --以县分组来统计一个市的人数;
         IF V_XQ='0000' THEN
   IF V_BMD ='0000000' THEN
    IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
    v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   || ''' AND KS.KSLXDM='''
   || EXAMTYPE
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;

    V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY CON DESC';

   -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
    ELSE
    v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;
     V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
  END IF;
  ELSIF V_BMD ='-1' THEN

      IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
   v_sql :=
               'SELECT COUNT(DISTINCT KS.XQDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   || ''' AND KS.KSLXDM='''
   || EXAMTYPE
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;

    V_SQL:=' SELECT KS.XQDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.XQDM ORDER BY CON DESC';

   -- DBMS_OUTPUT.PUT_LINE (' DTYJETYJETYET:' || V_SQL);
    ELSE
    v_sql :=
               'SELECT COUNT(DISTINCT KS.XQDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;
     V_SQL:=' SELECT KS.XQDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''''
    ||V_PARA
    ||' GROUP BY KS.XQDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;

   --DBMS_OUTPUT.PUT_LINE (' CEFJEOFIAPOEF:' || V_SQL);
     END IF;
  END IF;
   END IF;

  IF V_XQ='-1' THEN
            IF V_BMD ='0000000' THEN
    IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
    v_sql :=
               'SELECT COUNT(DISTINCT KS.BMDDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   || ''' AND KS.KSLXDM='''
   || EXAMTYPE
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;
     V_SQL:=' SELECT KS.BMDDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
    ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''''
    ||V_PARA
    ||' GROUP BY KS.BMDDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
  END IF;
  ELSIF V_BMD ='-1' THEN

    IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
    v_sql :=
               'SELECT COUNT(DISTINCT KS.DSDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds
   ||''' AND KS.KSLXDM='''
   ||EXAMTYPE
   ||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;
    V_SQL:=' SELECT KS.DSDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY KS.DSDM ORDER BY CON DESC';

   -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
    ELSE
    v_sql :=
               'SELECT COUNT(DISTINCT KS.DSDM) AS CON FROM KS WHERE KS.DSDM='''
            || v_ds||''''
            || v_para;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;
     V_SQL:=' SELECT KS.DSDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''''
    ||V_PARA
    ||' GROUP BY KS.DSDM ORDER BY TOTAL DESC';

         V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
     END IF;
  END IF;
  END IF;
---------------------------------------------------------------------------------------------------------
      ELSE
         --以市分组来统计一个省的人数;


   IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
   v_sql :=
               'SELECT COUNT(DISTINCT DSDM) AS CON FROM KS WHERE KS.KSLXDM='''
   || EXAMTYPE
   ||''''
            || v_para;
         -- DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
         EXECUTE IMMEDIATE v_sql
                      INTO v_count;

         cout := v_count;

    V_SQL:=' SELECT DSDM AS DM,COUNT(KSH)AS CON'
          ||' FROM KS WHERE KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA
    ||' GROUP BY DSDM ORDER BY CON DESC';

    ELSE
    v_sql := 'SELECT COUNT(DISTINCT KS.DSDM) AS CON FROM KS WHERE KS.KSH IS NOT NULL ' || v_para ;

EXECUTE IMMEDIATE v_sql
INTO v_count;

         cout := v_count;

        V_SQL:=' SELECT DSDM AS DM,COUNT(KSH)AS TOTal,'
          ||'sum(case ks.kslxdm when ''02'' then 1 else 0 end) as Y1,'
          ||'sum(case ks.kslxdm when ''03'' then 1 else 0 end) as Y2,'
          ||'sum(case ks.kslxdm when ''04'' then 1 else 0 end) as Y3,'
          ||'sum(case ks.kslxdm when ''06'' then 1 else 0 end) as Y4, '
    ||'sum(case ks.kslxdm when ''05'' then 1 else 0 end) as Y5, '
    ||'sum(case ks.kslxdm when ''09'' then 1 else 0 end) as Y6 '
          ||' FROM KS WHERE KS.KSH IS NOT NULL '
    || V_PARA
    ||'GROUP BY DSDM ORDER BY TOTAL DESC';
           V_SQL :=
               'SELECT DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM(SELECT ROWNUM RN,DM,Y1,Y2,Y3,Y4,Y5,Y6 FROM ( '
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;
   END IF;
      END IF;

----------------------------------------------------------------------------------------------------
DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);

OPEN RS FOR V_SQL;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE (' An exception occurried!');
END;

PROCEDURE EXAM_COUNTBY_PROCEDURE(
SCHOOL KS.BMDDM%TYPE,
EXAMTYPE KS.KSLXDM%TYPE,
STUTYPE KS.KSLBDM%TYPE,
KSKL KS.KLDM%TYPE,
PAGEINDEX INTEGER,
PAGESIZE INTEGER,
cout OUT NUMBER,
RS OUT EXAM_CURSOR
)IS
   V_DS VARCHAR2(2);
V_BMD VARCHAR2(7);
V_SQL VARCHAR2(2000);
V_PARA VARCHAR2(500);
V_KSLX VARCHAR2(500);
V_COUNT NUMBER;
V_END INTEGER;
V_FROM INTEGER;
BEGIN
      V_DS := SUBSTR(SCHOOL,1,2);
      V_BMD := SCHOOL;
      V_END:= PAGEINDEX * PAGESIZE + PAGESIZE;
      V_FROM := V_END - PAGESIZE+1;

   V_PARA:=' AND KS.KSZTDM=''002'' ';
   IF KSKL !='-1' AND KSKL IS NOT NULL THEN
   V_PARA:=V_PARA|| ' AND KS.KLDM='''||KSKL||'''';
   END IF;
   IF STUTYPE !='-1' AND STUTYPE IS NOT NULL THEN
   V_PARA:=V_PARA|| ' AND KS.KSLBDM='''||STUTYPE||'''';
   END IF;

   -----------------------------------------------------
   IF V_BMD != '0000000' AND V_BMD IS NOT NULL AND V_BMD !='-1'
      THEN                                               --统计一个中学的人数

   IF EXAMTYPE !='-1' AND EXAMTYPE IS NOT NULL THEN
                       --如果选有考试类型,则统计该考试类型,否则,统计所以考试类型;

     V_SQL:=' SELECT count(ksh) '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.BMDDM='''
    ||V_BMD
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA;

  EXECUTE IMMEDIATE v_sql
                     INTO v_count;

         cout := v_count;

    V_SQL:=' SELECT KSH,XM,XBDM,XQDM,SFZH,KLDM,KSLXDM,KSZTDM '
          ||' FROM KS WHERE KS.DSDM='''
    ||V_DS
    ||''' AND KS.BMDDM='''
    ||V_BMD
    || ''' AND KSLXDM='''
    ||EXAMTYPE
    ||''''
    ||V_PARA;

    V_SQL :=
               'SELECT KSH,XM,XBDM,XQDM,SFZH,KLDM,KSLXDM,KSZTDM FROM(SELECT ROWNUM RN,KSH,XM,XBDM,XQDM,SFZH,KLDM,KSLXDM,KSZTDM FROM ('
            || V_SQL
            || ')) WHERE RN>='
            || V_FROM
            || ' AND RN<='
            || V_END;

    ELSE
         DBMS_OUTPUT.PUT_LINE (' ERROE PARA');
  END IF;
  --DBMS_OUTPUT.PUT_LINE (' 人数统计SQL:' || V_SQL);
  END IF;
  OPEN RS FOR V_SQL;
  COMMIT;

  END;

END EXAM_COUNT;
/

Oracle 存储过程