Save returning value from a function to a variable : variable « SQL Plus « Oracle PL / SQL

1. Oracle PL / SQL
2. SQL Plus
3. variable

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 "JAVA2S.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 "JAVA2S.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>

   
    
    
  

Related examples in the same category