Oracle 存储过程

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 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.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;
/
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值