Decode char value to number : DECODE « Conversion Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Conversion Functions
3. DECODE

Decode char value to number

   
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>
SQL> CREATE OR REPLACE FUNCTION AverageGrade (
  2    p_Department IN VARCHAR2,
  3    p_Course IN NUMBER) RETURN VARCHAR2 AS
  4
  5    v_AverageGrade VARCHAR2(1);
  6    v_NumericGrade NUMBER;
  7    v_NumberStudents NUMBER;
  8
  9    CURSOR c_Grades IS
 10      SELECT grade
 11        FROM myStudent
 12        WHERE department = p_Department
 13        AND course = p_Course;
 14  BEGIN
 15    Debug.Reset;
 16    Debug.Debug('p_Department', p_Department);
 17    Debug.Debug('p_Course', p_Course);
 18
 19    SELECT COUNT(*)
 20      INTO v_NumberStudents
 21      FROM myStudent
 22      WHERE department = p_Department
 23      AND course = p_Course;
 24
 25    Debug.Debug('After select, v_NumberStudents', v_NumberStudents);
 26
 27    IF v_NumberStudents = 0 THEN
 28      RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
 29        p_Department || ' ' || p_Course);
 30    END IF;
 31
 32    SELECT AVG(DECODE(grade, 'A', 5,
 33                             'B', 4,
 34                             'C', 3,
 35                             'D', 2,
 36                             'E', 1))
 37      INTO v_NumericGrade
 38      FROM myStudent
 39      WHERE department = p_Department
 40        AND course = p_Course;
 41
 42    SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
 43                                         4, 'B',
 44                                         3, 'C',
 45                                         2, 'D',
 46                                         1, 'E')
 47      INTO v_AverageGrade
 48      FROM dual;
 49
 50    RETURN v_AverageGrade;
 51  END AverageGrade;
 52  /

Function created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table myStudent;

Table dropped.

   
    
  

Related examples in the same category