CREATE OR REPLACE FUNCTION xx_kja_test_almostfull(
p_Department xx_kja_test_classes.department%TYPE,
p_Course xx_kja_test_classes.course%TYPE)
RETURN BOOLEAN IS
v_currentStudents NUMBER;
v_maxstudent NUMBER;
v_ReturnValue BOOLEAN;
v_FullPercent CONSTANT NUMBER := 90;
BEGIN
SELECT current_students,max_students
INTO v_currentStudents,v_maxStudents
FROM Xx_Kja_Test_Classes
WHERE department = p_Department
AND course = p_Course;IF(v_CurrentStudent/v_MaxStudents * 100) > v_FullPercent
THEN
v_ReturnValue := TRUE;
ELSE
v_ReturnValue := FALSE;
END IF;
RETURN v_ReturnValue;
END almostfull;
------解决方案--------------------
是判断……
这种
v_FullPercent CONSTANT NUMBER := 90;定义并赋值
------解决方案--------------------
传值而已 可以一样 可以不一样 一样的话 可以查到数据 不一样的话查询不到数据
如表tb1中有2条数据
col1 col2
1 a
2 b
如果现在是根据传入的参数p_col2来匹配col2的话
如果p_col2传入的值为a
select * tb1 where col2='a'
查询的数据只有一条
col1 col2
1 a
如果传入的值为c
select * tb1 where col2='c'
那么查询出来的数据为空 没有符合col2='c' 的数据
理解？
------解决方案--------------------
- SQL code
CREATE OR REPLACE FUNCTION xx_kja_test_almostfull(p_Department xx_kja_test_classes.department%TYPE, p_Course xx_kja_test_classes.course%TYPE) RETURN BOOLEAN IS v_currentStudents NUMBER; v_maxstudent NUMBER; v_ReturnValue BOOLEAN; v_FullPercent CONSTANT NUMBER := 90;BEGIN SELECT current_students, max_students INTO v_currentStudents, v_maxStudents FROM Xx_Kja_Test_Classes WHERE department = p_Department --根据条件过滤出记录，找出current_students, max_students赋值给v_currentStudents, v_maxStudents两个变量 AND course = p_Course; IF (v_CurrentStudent / v_MaxStudents * 100) > v_FullPercent THEN --if判断语句，当v_CurrentStudent / v_MaxStudents大于百分之90时，给返回值赋值true,否则false v_ReturnValue := TRUE; ELSE v_ReturnValue := FALSE; END IF; RETURN v_ReturnValue; --返回值END almostfull;