Oracle函数循环使⽤⽅法
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;
oracle中trunc函数用法
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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。