Oracle 存储过程

最新推荐文章于 2025-04-01 15:26:31 发布
原创 最新推荐文章于 2025-04-01 15:26:31 发布 · 757 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#oracle #存储 #sql #integer #null #exception

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

springboot整合mybatis调用oracle存储过程
08-17
本文将深入探讨如何在Spring Boot项目中整合MyBatis，实现调用Oracle存储过程并处理游标返回的数据。 首先，我们需要在Spring Boot项目中引入相关的依赖。在`pom.xml`文件中添加Oracle JDBC驱动（ojdbc66-oracle...
帆软报表Oracle存储过程解决storeParameter1参数试用插件
12-12
总结起来，"帆软报表Oracle存储过程解决storeParameter1参数试用插件"主要是针对在调用无参数Oracle存储过程时出现的异常问题提供的一种解决方案。通过安装并配置这个插件，用户可以顺利地在帆软报表中调用不包含...
ORACLE存储过程
weixin_44740485的博客
10-28 4万+
目录一、存储过程的创建二、存储过程语法说明1.存储过程的基本结构2.变量的声明和赋值三、流程控制语句四、循环控制语句 本文是基于oracle编写的存储过程，以下所说的存储过程均为oracle中的存储过程，以及下面所说的存过都是存储过程的简称 一、存储过程的创建 创建存储过程的方式两种，一种是基于PLSQL创建，一种是ORACLE语法创建 PLSQL创建步骤： 1.连接plsql 2、找到左边对象下的procedures，点击右键 3、点击新建进入到创建存过页面 4、填写存储过程名称以及输入参数和输出
Oracle 存储过程 case when then用法
Golden的专栏
10-22 2400
1、connect_by_prior Create table AA( subid number(8), parentId number(8), bee varchar2(20) ); ---------------1------------------ ------2----------------3---------- --4-------5--------6--------7----- --------8--9-------------10--11--- -...
oracle 存储过程 case when then
LeehomeYU的专栏
05-12 9022
-- Created on 2017/5/12 by ADMINISTRATOR  declare    -- Local variables here   i integer;   imonth int;    updatestr        varchar2(50);         daystr           varchar2(5); begin   imonth:=
视图优化oracle,关于一个oracle视图如何优化其查询性能
weixin_29230773的博客
04-02 1150
环境：Solaris9+oracle9024SUN V890：16G内存，4个至强1.2GCPU现在建了一个表：用于学生查询历年成绩，速度太慢，请高手帮忙优化。表的脚本：CREATE OR REPLACE FORCE VIEW NEWJW.XS_KCCJB_VIEW(ZXJXJHH, KCH, KXH, XH, FAJHH,KCSXDM, KSLXDM, CXBKBZ, KSSJ, KCCJ,DJ...
oracle存储过程
最新发布
qq_22201881的博客
04-01 1143
存储过程
Oracle存储过程
weixin_50835854的博客
06-05 5929
这段时间实习，公司要使用存储过程，以前没接触过以为是啥高大上的技术，后来学习了一下，其实就是将一堆SQL命令打个包，下一次遇到类似的情况直接运行整个包也就是存储过程，然后就可以达到同样的效果。存储过程还可以和编程代码一样写比较复杂的逻辑，但是由于我最近并没有用到，所以咱是就记录这么多，以后用到了再补。
oracle 储存过程 异常,Oracle存储过程异常
weixin_39907762的博客
04-09 1377
由三种方式抛出异常1． 通过PL/SQL运行时引擎2. 使用RAISE语句3. 调用RAISE_APPLICATION_ERROR存储过程当数据库或PL/SQL在运行时发生错误时，一个异常被PL/SQL运行时引擎自动抛出。异常也可以通过RAISE语句抛出RAISE exception_name;显式抛出异常是程序员处理声明的异常的习惯用法，但RAISE不限于声明了的异常，它可以抛出任何任何异常。例...
Oracle存储过程详细教程
热门推荐
最初的节奏
01-19 8万+
Oracle存储过程详细教程 点关注不迷路，欢迎再访！ 目录Oracle存储过程详细教程一 .创建存储过程语法二.输出案例三.调用存储过程3.1 声明declare关键字3.2不声明declare关键字3.3call四.带有参数的存储过程五.in，out参数问题六.异常写法七.循环7.1while 循环7.2for循环八.基本正删改查 一 .创建存储过程语法 create or rep......
oracle存储过程详解
qq_31874627的博客
03-22 4074
在Oracle中，创建带参数的存储过程通常涉及到定义输入（IN）、输出（OUT）或输入输出（IN OUT）参数，并在存储过程的主体中使用这些参数。以下是一个简单的Oracle存储过程示例，它接受一个输入参数（一个数字），计算这个数字的平方，并通过一个输出参数返回结果。Oracle SQL Developer提供了一个强大的调试器，允许你逐步执行存储过程代码，查看变量值，设置断点等。这有助于增强代码的可维护性和可读性。游标的使用通常会增加存储过程的复杂性和执行时间，因此应谨慎使用，特别是在处理大量数据时。
oracle存储过程unwrap解密工具.zip
04-28
Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中，为了保护敏感代码或数据，开发人员有时会选择对存储过程进行加密。然而，当需要查看、调试或恢复这些加密的存储过程...
Mybatis调用Oracle存储过程的方法图文详解
08-29
Mybatis调用Oracle存储过程的方法详解 Mybatis是一款流行的持久层框架，广泛应用于Java企业级应用开发中。 Oracle存储过程是一种高效的数据库操作方式，通过存储过程可以实现复杂的业务逻辑操作。那么，如何使用...
Spring boot调用Oracle存储过程的两种方式及完整代码
08-18
Spring Boot 调用 Oracle 存储过程的两种方式及完整代码 在本文中，我们将为大家介绍 Spring Boot 调用 Oracle 存储过程的两种方式，并提供完整的代码示例。本文主要解决了在 Spring Boot 项目中调用 Oracle 存储...
Oracle 数据库的聚簇
wuhuif==专栏
10-16 1394
1. 什么是聚簇 　　聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。 　　2. 使用 Oracle 聚簇索引 　　聚簇是一种存储表的方法，这些表密切相关并经常一起连接进磁盘的同一区域。例如，表 B
oracle的文本导入、导出技巧
wuhuif==专栏
01-08 1287
oracle的文本导入、导出技巧（参照informix的load,unload） -------------------------------------------------------------------------------- http://www.chinaunix.net 作者:playboy  发表于：2006-03-22 23:27:34 【发表评论】【查看原文】【Or
oracle MVP
wuhuif==专栏
03-09 990
InStr函数实现代码减肥检测字符串中是否包含一个元音字母： 1、普通的方法： If UCase$(char) = "A" Or UCase$(char) = "E" Or UCase$(char) = "I" Or UCase$(char) = "O" Or UCase$(char) = "U" Then ’ it is a vowel End If 2、更加简练的方法： InStr("Aa
ORACLE存储过程源码学习指南
在数据库管理领域，Oracle存储过程是一组为了完成特定功能的SQL语句集，它们经过编译并且被存储在数据库中，可以由数据库应用程序通过特定的调用机制来执行。存储过程可以包含复杂的业务逻辑，并且可以通过参数传递...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值