动态交叉报表存储过程

CREATE OR REPLACE PACKAGE BODY BBCL_AJHG03
IS
--功能：岩石及原油饱粉分析鉴定统计表
--引用表：AJHG03
--输入参数：样品批号(P_YPPH)
--编写人：杨丽
--日期：2004-2-24
PROCEDURE BBCL_AJHG03(
P_YPPH VARCHAR2,
C OUT CUR
)
AS
TYPE INDEX_TABLE IS TABLE OF VARCHAR2(16)  INDEX BY BINARY_INTEGER;
TYPE HSMC_INDEX  IS TABLE OF VARCHAR2(80)  INDEX BY BINARY_INTEGER;
P_HSHL   AJHG03.HSHL%TYPE;
P_YPBH1  INDEX_TABLE;
P_YPBH2  INDEX_TABLE;
P_HSMC   HSMC_INDEX;
P_YPSD1  VARCHAR2(16);
P_YPSD2  VARCHAR2(16);
P_SQL    VARCHAR2(2000);
P_STR    VARCHAR2(2000);
P_SQL1   VARCHAR2(2000);
--A        VARCHAR2(16);
I  INTEGER;
J  INTEGER;
K  INTEGER;
BEGIN
   BEGIN
     DELETE TEMP_AJHG03;
     COMMIT;
     J := 1;
     FOR C IN (SELECT DISTINCT CNAME,COLNO
         FROM COL
         WHERE UPPER(TNAME) LIKE 'TEMP_AJHG03' AND COLNO >2 ORDER BY COLNO )
     LOOP
       P_SQL := 'ALTER  TABLE  TEMP_AJHG03 DROP COLUMN  ' ||C.CNAME||' ';
       EXECUTE IMMEDIATE  P_SQL;
       COMMIT;
       J := J + 1;
     END LOOP;
   END;
  
   INSERT INTO TEMP_AJHG03(HSMC,XH) VALUES('YPSD1',1);
   COMMIT;
  
   INSERT INTO TEMP_AJHG03(HSMC,XH) VALUES('YPSD2',2);
   COMMIT;
  
   INSERT INTO TEMP_AJHG03(HSMC)
     SELECT DISTINCT HSMC FROM AJHG03  WHERE TRIM(YPPH) = TRIM(P_YPPH) ;
   COMMIT;
  
   BEGIN
   I :=1 ;
   FOR C IN (SELECT DISTINCT TRIM(YPBH) AS YPBH
       FROM AJHG03
       WHERE TRIM(YPPH) = TRIM(P_YPPH) ORDER BY YPBH ASC)
   LOOP
     P_YPBH1(I) := C.YPBH;
     P_YPBH2(I) := SUBSTR(P_YPBH1(I),1,5)||'_'||SUBSTR(P_YPBH1(I),7);
     P_SQL := 'ALTER TABLE TEMP_AJHG03 ADD (' ||RTRIM(P_YPBH2(I))|| ' VARCHAR2(16))';
     EXECUTE IMMEDIATE  P_SQL;
  /*BEGIN
       A := 1;
       FOR C IN ( SELECT  TRIM(YPSD1) AS YPSD1,TRIM(YPSD2) AS YPSD2
           FROM AJHD01
           WHERE TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = P_YPBH1(I))
      LOOP
        P_YPSD1(A) := C.YPSD1;
        P_YPSD2(A) := C.YPSD2;
        P_JD(A) := P_YPSD1(A)||'~'||P_YPSD2(A);
        --P_YPBH3(A) := C.YPBH ;
        P_STR := 'UPDATE TEMP_AJHG03 SET  '||TRIM(P_YPBH2(I))||' = :P_JD(A) WHERE TRIM(HSMC) = ''';
        P_STR := P_STR ||'  '||'''';
       EXECUTE IMMEDIATE  P_STR USING P_JD(A);
         COMMIT;
       A := A + 1;
       END LOOP;
     END;*/
         SELECT TRIM(YPSD1)  INTO P_YPSD1  FROM AJHD01
           WHERE TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = TRIM(P_YPBH1(I));
         IF P_YPSD1 = 'NULL' OR P_YPSD1 = ' ' THEN
           SELECT TRIM(YYH)  INTO P_YPSD2  FROM AJHD01
             WHERE TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = TRIM(P_YPBH1(I));
         ELSE    
           SELECT TRIM(YPSD2)  INTO P_YPSD2  FROM AJHD01
             WHERE TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = TRIM(P_YPBH1(I));
         END IF;
        
         P_SQL1 := 'UPDATE TEMP_AJHG03 SET  '||TRIM(P_YPBH2(I))||' = :P_YPSD1  WHERE TRIM(HSMC) = ''';
         P_SQL1 := P_SQL1 ||'YPSD1'|| '''';
         EXECUTE IMMEDIATE  P_SQL1 USING P_YPSD1;
           COMMIT;
        
         P_SQL1 := 'UPDATE TEMP_AJHG03 SET  '||TRIM(P_YPBH2(I))||' = :P_YPSD2  WHERE TRIM(HSMC) = ''';
         P_SQL1 := P_SQL1 ||'YPSD2'|| '''';
         EXECUTE IMMEDIATE  P_SQL1 USING P_YPSD2;
           COMMIT;

     SELECT HSMC BULK COLLECT INTO P_HSMC
        FROM AJHG03 
        WHERE TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = TRIM(P_YPBH1(I)) AND HSMC IS NOT NULL ORDER BY HSMC;
     FOR K IN 1..P_HSMC.COUNT LOOP
       SELECT DISTINCT HSHL INTO P_HSHL FROM AJHG03
         WHERE  TRIM(YPPH) = TRIM(P_YPPH) AND TRIM(YPBH) = TRIM(P_YPBH1(I)) AND TRIM(HSMC) = TRIM(P_HSMC(K));
      
     P_STR := 'UPDATE TEMP_AJHG03 SET  '||TRIM(P_YPBH2(I))||' = :P_HSHL WHERE TRIM(HSMC) = ''';
     P_STR := P_STR ||P_HSMC(K)||'''';
     EXECUTE IMMEDIATE  P_STR USING P_HSHL;
       COMMIT;
     END LOOP;
    
     I := I + 1;
   END LOOP;
   END;
   OPEN  C  FOR  SELECT  *  FROM  TEMP_AJHG03 ORDER BY XH;
END;
END;