Mega Code Archive

Save returning value from a function to a variable

SQL> SQL> CREATE TABLE myStudent ( 2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 ); Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10000, 'CS', 102, 'A'); 1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10002, 'CS', 102, 'B'); 1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10003, 'CS', 102, 'C'); 1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10000, 'HIS', 101, 'A'); 1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10001, 'HIS', 101, 'B'); 1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade) 2 VALUES (10002, 'HIS', 101, 'B'); 1 row created. SQL> CREATE OR REPLACE FUNCTION AverageGrade ( 2 p_Department IN myStudent.department%TYPE, 3 p_Course IN myStudent.course%TYPE) RETURN CHAR AS 4 v_AverageGrade CHAR(1); 5 v_NumericGrade NUMBER; 6 v_NumberStudents NUMBER; 7 8 CURSOR c_Grades IS 9 SELECT grade 10 FROM myStudent 11 WHERE department = p_Department 12 AND course = p_Course; 13 BEGIN 14 SELECT COUNT(*) 15 INTO v_NumberStudents 16 FROM myStudent 17 WHERE department = p_Department 18 AND course = p_Course; 19 20 IF v_NumberStudents = 0 THEN 21 RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' || 22 p_Department || ' ' || p_Course); 23 END IF; 24 25 SELECT AVG(DECODE(grade, 'A', 5, 26 'B', 4, 27 'C', 3, 28 'D', 2, 29 'E', 1)) 30 INTO v_NumericGrade 31 FROM myStudent 32 WHERE department = p_Department 33 AND course = p_Course; 34 35 SELECT DECODE(ROUND(v_NumericGrade), 5, 'A', 36 4, 'B', 37 3, 'C', 38 2, 'D', 39 1, 'E') 40 INTO v_AverageGrade 41 FROM dual; 42 43 RETURN v_AverageGrade; 44 END AverageGrade; 45 / Function created. SQL> SQL> SQL> VARIABLE v_AveGrade VARCHAR2(1) SQL> EXEC :v_AveGrade := AverageGrade('HIS', 101) PL/SQL procedure successfully completed. SQL> PRINT v_AveGrade V_AVEGRADE -------------------------------- B SQL> EXEC :v_AveGrade := AverageGrade('NUT', 307) BEGIN :v_AveGrade := AverageGrade('NUT', 307); END; * ERROR at line 1: ORA-20001: No students registered for NUT 307 ORA-06512: at "RNTSOFT.AVERAGEGRADE", line 21 ORA-06512: at line 1 SQL> PRINT v_AveGrade V_AVEGRADE -------------------------------- B SQL> EXEC :v_AveGrade := AverageGrade('MUS', 410) BEGIN :v_AveGrade := AverageGrade('MUS', 410); END; * ERROR at line 1: ORA-20001: No students registered for MUS 410 ORA-06512: at "RNTSOFT.AVERAGEGRADE", line 21 ORA-06512: at line 1 SQL> PRINT v_AveGrade V_AVEGRADE -------------------------------- B SQL> EXEC :v_AveGrade := AverageGrade('CS', 102) PL/SQL procedure successfully completed. SQL> PRINT v_AveGrade V_AVEGRADE -------------------------------- B SQL> SQL> SQL> SQL> SQL> drop table myStudent; Table dropped. SQL>