Create Or Replace Function averagegrade(
p_department In Varchar2,
p_course In Number Return Varchar2 As
v_averagegrade Varchar2(1);
v_numericgrade Number;
v_numberstudents Number;)
Cursor c_grades Is
Select grade
From registered_students
Where department=p_deparment
And course=p_course;
Begin
Select Count(*)
Into v_numberstudents
From registered_students
Where department=p_department
And course=p_course;
If v_numberstudent =0 Then
raise_application_error(-20001,'no students registered for'||
p_department|| ''||p_course);
End If;
Select Avg(decode(grade,'A',5,
'B',4,
'C',3,
'E',1))
Into V_numericgrade
From registered_students
Where department=p_department
And course=p_course ;
SELECT DECODE(ROUND(v_NumericGrade),5, 'A',
4, 'B',
3, 'C',
1, 'E')
INTO v_AverageGrade
FROM dual;
RETURN v_AverageGrade;
END AverageGrade;在调用过程中
SQL> Variable v_AverageGrade varchar2(1)
SQL> exec :v_AverageGrade :=AverageGrade('HIS',101)
提示错误
对象 RESERVETEST.AVERAGEGRADE 无效
PL/SQL: Statement ignored
如何解决呢 help
p_department In Varchar2,
p_course In Number Return Varchar2 As
v_averagegrade Varchar2(1);
v_numericgrade Number;
v_numberstudents Number;)
Cursor c_grades Is
Select grade
From registered_students
Where department=p_deparment
And course=p_course;
Begin
Select Count(*)
Into v_numberstudents
From registered_students
Where department=p_department
And course=p_course;
If v_numberstudent =0 Then
raise_application_error(-20001,'no students registered for'||
p_department|| ''||p_course);
End If;
Select Avg(decode(grade,'A',5,
'B',4,
'C',3,
'E',1))
Into V_numericgrade
From registered_students
Where department=p_department
And course=p_course ;
SELECT DECODE(ROUND(v_NumericGrade),5, 'A',
4, 'B',
3, 'C',
1, 'E')
INTO v_AverageGrade
FROM dual;
RETURN v_AverageGrade;
END AverageGrade;在调用过程中
SQL> Variable v_AverageGrade varchar2(1)
SQL> exec :v_AverageGrade :=AverageGrade('HIS',101)
提示错误
对象 RESERVETEST.AVERAGEGRADE 无效
PL/SQL: Statement ignored
如何解决呢 help
/* Formatted on 2008/06/04 11:12 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION averagegrade (
p_department IN VARCHAR2,
p_course IN NUMBER
)
RETURN VARCHAR2
AS
v_averagegrade VARCHAR2 (1);
v_numericgrade NUMBER;
v_numberstudents NUMBER; CURSOR c_grades
IS
SELECT grade
FROM registered_students
WHERE department = p_deparment AND course = p_course;
BEGIN
SELECT COUNT (*)
INTO v_numberstudents
FROM registered_students
WHERE department = p_department AND course = p_course; IF v_numberstudent = 0
THEN
raise_application_error (-20001,
'no students registered for'
|| p_department
|| ''
|| p_course
);
END IF; SELECT AVG (DECODE (grade, 'A', 5, 'B', 4, 'C', 3, 'E', 1))
INTO v_numericgrade
FROM registered_students
WHERE department = p_department AND course = p_course; SELECT DECODE (ROUND (v_numericgrade), 5, 'A', 4, 'B', 3, 'C', 1, 'E')
INTO v_averagegrade
FROM DUAL; RETURN v_averagegrade;
END averagegrade;
Create Or Replace Function averagegrade(
p_department In Varchar2,
p_course In Number Return Varchar2 As
v_averagegrade Varchar2(1);
v_numericgrade Number;
v_numberstudents Number;)
我整理了下的
CREATE OR REPLACE FUNCTION averagegrade (
p_department IN VARCHAR2,
p_course IN NUMBER
)
RETURN VARCHAR2
AS
v_averagegrade VARCHAR2 (1);
v_numericgrade NUMBER;
v_numberstudents NUMBER;
p_department In Varchar2,
p_course In Number Return Varchar2 As
v_averagegrade Varchar2(1);
v_numericgrade Number;
v_numberstudents Number;) <=括错地方了吧
Cursor c_grades Is
======================上面的改为===========
Create Or Replace Function averagegrade(
p_department In Varchar2)
p_course In Number Return Varchar2 As
v_averagegrade Varchar2(1);
v_numericgrade Number;
v_numberstudents Number;
提示错误 :并非所有变量都已绑定 问题没得解决
你再用select AverageGrade('HIS',105) from dual 试试看
在return之前就应该加“)”的,而不是在定义好变量的时候再加变量的你再试试看吧