要记住的例子嗣

create or replace package body PKG_LINBAOJI_TEST is

  -- Private type declarations
 
  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
 --<VariableName> <Datatype>;

 -- Function and procedure implementations
 function FUN_SUB_GET_MAT(V_SUB_MAT_NO JAY_MIS_REPROCDS.SUB_MAT_NO%TYPE) return INTEGER
  IS
     V_MY_REF MY_REF;
     v_row jay_mis_reprocds%rowtype; 
     I integer;
  begin
       insert into lbj_test (select * from jay_mis_reprocds t where t.mat_no=V_SUB_MAT_NO);                     
                   OPEN V_MY_REF FOR SELECT * FROM JAY_MIS_REPROCDS T WHERE  T.MAT_NO=V_SUB_MAT_NO;
                   FETCH V_MY_REF INTO V_ROW;
                WHILE V_MY_REF%FOUND LOOP
                       IF v_row.relation=1 THEN
                     
                      i:= FUN_SUB_GET_MAT(V_ROW.SUB_MAT_NO);
                      END IF;
                                         
                      FETCH V_MY_REF INTO V_ROW;         
                 END LOOP;      
    return(1);
  end FUN_SUB_GET_MAT;

--begin
  -- Initialization
  --<Statement>;
  --²Ä¤@­ÓÀx¦s¹Lµ{
 PROCEDURE IMPORT_REPROCD_MAT_NO(V_MAT_NO LBJ_TEST.MAT_NO%TYPE )
 IS
 

  V_MY_REF1 MY_REF;
  V_MY_REF2 MY_REF;
  V_MY_REF3 MY_REF;

  v_row1 jay_mis_reprocds%rowtype;
  v_row2 jay_mis_reprocds%rowtype;
  v_row3 jay_mis_reprocds%rowtype;
 
 
           
  BEGIN
  --§ä¨ì©Ò¥À¶¥
  insert into lbj_test (select * from jay_mis_reprocds t where t.mat_no=V_Mat_no);       
  --¥´¶}´å¼Ð
  OPEN V_MY_REF1 FOR SELECT * FROM JAY_MIS_REPROCDS  J WHERE  J.MAT_NO=V_Mat_no;
  fetch V_MY_REF1 into v_row1;
   while V_MY_REF1%found loop
  
        if v_row1.relation=1 then
                   insert into lbj_test (select * from jay_mis_reprocds t where t.mat_no=v_row1.sub_mat_no);                     
                   OPEN V_MY_REF2 FOR SELECT * FROM JAY_MIS_REPROCDS T WHERE  T.MAT_NO=V_ROW1.SUB_MAT_NO;
                   FETCH V_MY_REF2 INTO V_ROW2;
                   WHILE V_MY_REF2%FOUND LOOP
                         IF v_row2.relation=1 THEN
                            insert into lbj_test (select * from jay_mis_reprocds t where t.mat_no=v_row2.sub_mat_no); 
                                   OPEN  v_my_REF3 FOR SELECT * FROM JAY_MIS_REPROCDS T WHERE  T.MAT_NO=V_ROW2.SUB_MAT_NO;
                                    FETCH V_MY_REF3 INTO V_ROW3;
                                    WHILE V_MY_REF3%FOUND LOOP
                                          IF v_row3.relation=1 THEN
                                              insert into lbj_test (select * from jay_mis_reprocds t where t.mat_no=v_row3.sub_mat_no); 
                                          END IF;
                                         
                                          FETCH V_MY_REF3 INTO V_ROW3;         
                                    END LOOP;
                         END IF;
                         FETCH V_MY_REF2 INTO V_ROW2;
                  
                   END LOOP;         
        end if ; 
        FETCH V_MY_REF1 INTO V_ROW1;                     
    end loop;  
      
      
  END IMPORT_REPROCD_MAT_NO;
 --###############################
 
 -- -- ©I¥s¨ç¦¡ªº´ú¸Õ¹Lµ{
 PROCEDURE TEST_FUNCTION (V_MAT_NO JAY_MIS_REPROCDS.MAT_NO%TYPE)
 IS
   V_REF MY_REF;
   V_ROW JAY_MIS_REPROCDS%ROWTYPE;
   I INTEGER;
  
 BEGIN
      INSERT INTO LBJ_TEST SELECT * FROM JAY_MIS_REPROCDS T WHERE MAT_NO=V_MAT_NO;
      OPEN V_REF FOR SELECT * FROM JAY_MIS_REPROCDS WHERE MAT_NO= V_MAT_NO;
      FETCH V_REF INTO V_ROW;
       WHILE V_REF%FOUND  LOOP
              if v_row.relation=1 then
            
                                 i:= FUN_SUB_GET_MAT(V_ROW.SUB_MAT_NO);
              end if;            
              FETCH V_REF INTO V_ROW;
            
       END LOOP;
 END TEST_FUNCTION;     
 
end PKG_LINBAOJI_TEST;