Calculate average grade : Utility Function « Stored Procedure Function « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Stored Procedure Function » Utility Function 




Calculate average grade
  
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3NOT NULL,
  5    grade      CHAR(1)
  6    );

Table created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000'CS', 102'A');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002'CS', 102'B');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003'CS', 102'C');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000'HIS', 101'A');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001'HIS', 101'B');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002'HIS', 101'B');

row created.

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

Function created.

SQL>
SQL> select AverageGrade('HIS', 101from dual;

AVERAGEGRADE('HIS',101)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B

row selected.

SQL>
SQL>
SQL> drop table myStudent;

Table dropped.

SQL>
SQL>

   
  














Related examples in the same category
1.Your own month add method
2.Raise power function
3.Raise pay level
4.Validate date value format
5.Function to update null value
6.Function to count tab
7.Temperature package: convert Celsius to Fahrenheit back, forth
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.