Sometimes there is business logic that is too hard to program in a SELECT statement. To get around this you would use a User-Defined Function. But you shouldn't have a function just on its own. You need to put it into a package first. Packages are containers that hold procedures and functions. It helps keeping code organized and manageable. Packages are made up of a package object plus a package body object. The package object includes interfaces to the package bod object. The package body object contains the SQL logic to be run.

 

1. Create the package and package body by running the script...

 

CREATE OR REPLACE PACKAGE DATA_CLEANUP AS 

-- Declare Function

END DATA_CLEANUP;

/

CREATE OR REPLACE PACKAGE BODY DATA_CLEANUP AS

-- Create Function

END;

/         

 

1. Add the function declaration by replacing -- Declare Function with...

 

FUNCTION AVERAGE_GRADE /*IN*/ RETURN /*OUT*/;

 

1. Add the function code by replacing -- Create Function with 

 

FUNCTION AVERAGE_GRADE /*IN*/ RETURN /*OUT*/ AS

  -- AvgGrade

BEGIN 

RETURN NULL;

END AVERAGE_GRADE;

 

1. Replace all your /*OUT*/ parameters with NUMBER

 

1. Run your script to confirm it compiles

 

1. Replace your /*IN*/ parameters with (IN_StudentID IN NUMBER) to pass a parameter into your function. You will use it to help you identify which student you need to calculate the grade average for.

 

1. Run your script to confirm it compiles

 

1. Declare your AvgGrade variable by replacing --AvgGrade with...

 

AvgGrade NUMBER;

 

1. Set your returning variable by replacing NULL with AvgGrade

 

1. Run your script to confirm it compiles

 

1. Set your AvgGrade variable using a SELECT by adding the following line between BEGIN and RETURN (watch your semicolons)...

 

SELECT AVG(PercentGrade) INTO /*Variable*/ FROM GRADE

WHERE StudentID = /*IN variable*/;

 

1. Set AvgGrade by replacing /*Variable*/ with AvgGrade

1. Filter for the average calculation by replacing /*IN variable*/ with IN_StudentID

1. Run your script to confirm it compiles

 Submit AVERAGE_GRADE function Results

i. Run SELECT StudentID, FirstName, LastName FROM STUDENT

ii. Add your DATA_CLEANUP.AVERAGE_GRADE function to the SELECT

iii. Run your script to confirm it compiles.