Learning of SQL Day 34

 

Day 34: Functions and Procedures

Introduction:

• Functions and procedures are database objects that encapsulate reusable SQL code. Functions return a single value and can be used in SQL statements, while procedures perform actions and may or may not return values.

Key Concepts:

• Function: A set of SQL statements that returns a single value.

• Procedure: A set of SQL statements that perform actions and may return values.

• CREATE FUNCTION: The SQL statement used to create a function.

• CREATE PROCEDURE: The SQL statement used to create a procedure.

• RETURN: Used in functions to return a value.

• CALL: Used to execute a procedure.

SQL Commands and Examples:

1. Creating a Function:

   • Define a simple function that calculates the age of a student based on their birth date.

sql

-- Create a function to calculate age from birth date
CREATE FUNCTION CalculateAge(birthDate DATE)
RETURNS INT
BEGIN
    DECLARE age INT;
    SET age = TIMESTAMPDIFF(YEAR, birthDate, CURDATE());
    RETURN age;
END;

2. Using a Function:

   • Call the function within a SELECT statement.

sql

-- Use the CalculateAge function to get the age of each student
SELECT StudentID, FirstName, LastName, CalculateAge(DateOfBirth) AS Age
FROM Students;

3. Creating a Procedure:

   • Define a procedure that updates the age of a student.

sql

-- Create a procedure to update the age of a student
CREATE PROCEDURE UpdateStudentAge(IN studentID INT, IN birthDate DATE)
BEGIN
    UPDATE Students
    SET Age = CalculateAge(birthDate)
    WHERE StudentID = studentID;
END;

4. Using a Procedure:

   • Call the procedure to update the age of a student.

sql

-- Call the UpdateStudentAge procedure to update the age of a student
CALL UpdateStudentAge(1, '2000-01-01');

Practice Exercise:

1. Use the Students table with columns StudentID, FirstName, LastName, DateOfBirth, Age.

2. Write a query to create a function named CalculateGPA that takes student grades as input and returns the GPA.

3. Write a query to use the CalculateGPA function in a SELECT statement.

4. Write a query to create a procedure named UpdateStudentGPA that updates the GPA of a student.

5. Write a query to call the UpdateStudentGPA procedure.

sql

-- Create a function to calculate GPA from grades
CREATE FUNCTION CalculateGPA(grade1 INT, grade2 INT, grade3 INT)
RETURNS FLOAT
BEGIN
    DECLARE gpa FLOAT;
    SET gpa = (grade1 + grade2 + grade3) / 3;
    RETURN gpa;
END;

-- Use the CalculateGPA function to get the GPA of each student
SELECT StudentID, FirstName, LastName, CalculateGPA(90, 85, 88) AS GPA
FROM Students;

-- Create a procedure to update the GPA of a student
CREATE PROCEDURE UpdateStudentGPA(IN studentID INT, IN grade1 INT, IN grade2 INT, IN grade3 INT)
BEGIN
    UPDATE Students
    SET GPA = CalculateGPA(grade1, grade2, grade3)
    WHERE StudentID = studentID;
END;

-- Call the UpdateStudentGPA procedure to update the GPA of a student
CALL UpdateStudentGPA(1, 90, 85, 88);

Important Tips:

• Functions are useful for encapsulating logic that returns a single value and can be used in SQL statements.

• Procedures are versatile and can perform multiple actions, making them suitable for complex operations.

• Regularly review and optimize functions and procedures to ensure efficient performance.

Understanding functions and procedures enhances your ability to write reusable and efficient SQL code.