Oracle讲解
) AS findscore NUMBER(3); --查询出来的成绩 BEGIN SELECT score INTO findscore FROM score WHERE stuId=stu_Id AND courseId=course_Id; IF SQL%NOTFOUND THEN scoreout:=0; ELSE scoreout:=findscore; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN scoreout:=0; WHEN TOO_MANY_ROWS THEN scoreout:=0; END; / 调用
SET SERVEROUTPUT ON; DECLARE stu_id NUMBER(4); course_id NUMBER(2); scoreout NUMBER(3); BEGIN stu_id:=&请输入学号; course_id:=&请输入课程编号; findStudent(stu_id,course_id,scoreout); DBMS_OUTPUT.PUT_LINE('学号为'||stu_id||'的学员成绩是'||scoreout); END; /
--编写函数，接收课程的编号，求出这门课的总成绩并返回，调用此函数。 CREATE OR REPLACE FUNCTION findScore(course_id NUMBER) RETURN NUMBER AS totalScore NUMBER(4):=0; BEGIN SELECT sum(score) INTO totalScore FROM score WHERE courseId=course_id; return totalScore; END; / //调用
SELECT findscore(1) FROM dual; //调用
31
Oracle讲解
SET SERVEROUTPUT ON; DECLARE courseId NUMBER(2):=0; totalscore NUMBER(4):=0; BEGIN courseId:=&请输入课程编号; totalscore:=findScore(courseId); DBMS_OUTPUT.PUT_LINE('总成绩是:'||totalscore); END; /
32