Question

oracle警告: 创建的过程带有编译错误。

CREATE OR REPLACE PROCEDURE getstatistic(COURSE_NAME char)
AS
less60 number;
a60 number;
a70 number;
a80 number;
a90 number;
Current_Cno char(4);
BEGIN
SELECT Cno INTO Current_Cno
FROM Course
WHERE trim(Cname)=Course_Name;
SELECT COUNT(*) INTO less60
FROM SC
WHERE Cno=Current_Cno AND Grade<60;
SELECT COUNT(*) INTO a60
FROM SC
WHERE Cno=Current_Cno AND Grade>=60 AND Grade<70;
SELECT COUNT(*) INTO a70
FROM SC
WHERE Cno=Current_Cno AND Grade>=70 AND Grade<80;
SELECT COUNT(*) INTO a80
FROM SC
WHERE Cno=Current_Cno AND Grade>=80 AND Grade<90;
SELECT COUNT(*) INTO a90
FROM SC
WHERE Cno=Current_Cno AND Grade>=90 AND Grade<100;
DELETE
INSERT
INSERT
INSERT
INSERT
FROM
INTO
INTO
INTO
INTO
gradeLayer;
gradeLayer VALUES('[0,60)', less60);
gradeLayer VALUES('[60,70)', a60);
gradeLayer VALUES('[70,80)', a70);
gradeLayer VALUES('[80,90)',a80);
INSERT INTO gradeLayer VALUES('[90,100]', a90);
EXCEPTION WHEN NO_DATA_FOUND THEN
--RAISE_APPLICATION_ERROR(-20000, '输入错误，没有该课程!!!');
dbms_output.put_line('输入错误，没有该课程!!!');
return;
END;/

Answer 1

CREATE OR REPLACE PROCEDURE GETSTATISTIC(COURSE_NAME CHAR) AS
LESS60 NUMBER;
A60 NUMBER;
A70 NUMBER;
A80 NUMBER;
A90 NUMBER;
CURRENT_CNO CHAR(4);
BEGIN
SELECT CNO INTO CURRENT_CNO FROM COURSE WHERE TRIM(CNAME) = COURSE_NAME;
SELECT COUNT(*)
INTO LESS60
FROM SC
WHERE CNO = CURRENT_CNO
AND GRADE < 60;

SELECT COUNT(*)
INTO A60
FROM SC
WHERE CNO = CURRENT_CNO
AND GRADE >= 60
AND GRADE < 70;

SELECT COUNT(*)
INTO A70
FROM SC
WHERE CNO = CURRENT_CNO
AND GRADE >= 70
AND GRADE < 80;

SELECT COUNT(*)
INTO A80
FROM SC
WHERE CNO = CURRENT_CNO
AND GRADE >= 80
AND GRADE < 90;

SELECT COUNT(*)
INTO A90
FROM SC
WHERE CNO = CURRENT_CNO
AND GRADE >= 90
AND GRADE < 100;

DELETE GRADELAYER;
INSERT INTO GRADELAYER VALUES ('[0,60)', LESS60);
INSERT INTO GRADELAYER VALUES ('[60,70)', A60);
INSERT INTO GRADELAYER VALUES ('[70,80)', A70);

INSERT INTO GRADELAYER VALUES ('[80,90)', A80);
INSERT INTO GRADELAYER VALUES ('[90,100]', A90);
EXCEPTION
WHEN NO_DATA_FOUND THEN
--RAISE_APPLICATION_ERROR(-20000, '输入错误，没有该课程!!!');
DBMS_OUTPUT.PUT_LINE('输入错误，没有该课程!!!');
RETURN;
END;

这个过程调整一下，没什么问题