Oracle函数循环使用方法

uzayvdjcxl

　　create or replace function FUN_STUCHECK(D VARCHAR2)
RETURN INTEGER
IS
   V_ID varchar2(100);
   V_SEXNAME varchar2(100);
   V_AGE varchar2(100);
   V_YBQK_SG varchar2(100);
   V_YBQK_TZ number;
   V_YBQK_YYZK varchar2(100);
   V_YBQK_YYZK1 varchar2(100);
   V_COUNT INTEGER;
begin
   V_COUNT :=0;
  FOR record1 in (select ID, SEXNAME,AGE,YBQK_SG,YBQK_TZ,YBQK_YYZK
                            FROM hlr_stuhealthcheck) loop
    V_ID := record1.id;
    V_SEXNAME := record1.sexname;
    V_AGE := record1.age;
    V_YBQK_SG := trunc(record1.ybqk_sg);
    V_YBQK_TZ := to_number(record1.ybqk_tz);
    V_YBQK_YYZK := record1.ybqk_yyzk;
    V_YBQK_YYZK1 := 0;
    FOR record2 in (select YYBL,JDTZ,ZCTZ,CZ,FP FROM stu_heighweight_standard
                           WHERE SEXNAME=V_SEXNAME AND AGE=V_AGE AND HEIGHT=V_YBQK_SG) LOOP
        IF record2.YYBL is null THEN
           V_YBQK_YYZK1:='0';
           exit;
        END IF;
        IF V_YBQK_TZ < record2.YYBL THEN
           V_YBQK_YYZK1:='1';
           EXIT;
        END IF;
        IF V_YBQK_TZ <= record2.JDTZ THEN
           V_YBQK_YYZK1:='2';
           EXIT;
        END IF;
        IF V_YBQK_TZ <= record2.ZCTZ THEN
           V_YBQK_YYZK1:='3';
           EXIT;
        END IF;
        IF V_YBQK_TZ <= record2.CZ THEN
           V_YBQK_YYZK1:='4';
           EXIT;
        END IF;
        IF V_YBQK_TZ > record2.CZ THEN
           V_YBQK_YYZK1:='5';
           EXIT;
        END IF;
    END LOOP;
    IF V_YBQK_YYZK1 = '0' OR V_YBQK_YYZK1<>V_YBQK_YYZK THEN
           update hlr_stuhealthcheck set YBQK_YYZK = V_YBQK_YYZK1 where id=V_ID;
           V_COUNT := V_COUNT + 1;
    END IF;
  end loop;
COMMIT;
RETURN (V_COUNT);
end FUN_STUCHECK;