Learning of SQL Day 33

 

Day 33: Stored Procedures

Introduction:

• Stored procedures are a set of SQL statements that can be stored and executed on the database server. They help in encapsulating logic, improving performance, and ensuring consistency.

Key Concepts:

• Stored Procedure: A precompiled collection of SQL statements stored on the server.

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

• CALL: Executes a stored procedure.

• Parameters: Allows passing of input values and returning of output values.

SQL Commands and Examples:

1. Creating a Stored Procedure:

   • Define a simple stored procedure that retrieves student details.

sql

-- Create a stored procedure to retrieve student details
CREATE PROCEDURE GetStudentDetails()
BEGIN
    SELECT StudentID, FirstName, LastName, Age, CourseID
    FROM Students;
END;

2. Executing a Stored Procedure:

   • Call a stored procedure to execute the encapsulated SQL statements.

sql

-- Execute the stored procedure to retrieve student details
CALL GetStudentDetails();

3. Stored Procedure with Parameters:

   • Define a stored procedure that accepts input parameters.

sql

-- Create a stored procedure to retrieve details of a specific student by StudentID
CREATE PROCEDURE GetStudentByID(IN student_id INT)
BEGIN
    SELECT StudentID, FirstName, LastName, Age, CourseID
    FROM Students
    WHERE StudentID = student_id;
END;

-- Execute the stored procedure with an input parameter
CALL GetStudentByID(1);

4. Stored Procedure with Output Parameters:

   • Define a stored procedure that returns output parameters.

sql

-- Create a stored procedure to get the total number of students in a specific course
CREATE PROCEDURE GetTotalStudentsByCourse(IN course_id INT, OUT total_students INT)
BEGIN
    SELECT COUNT(*) INTO total_students
    FROM Students
    WHERE CourseID = course_id;
END;

-- Execute the stored procedure with input and output parameters
CALL GetTotalStudentsByCourse(101, @total_students);
SELECT @total_students AS TotalStudents;

Practice Exercise:

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

2. Write a query to create a stored procedure named GetAllStudents that retrieves all student details.

3. Write a query to execute the GetAllStudents stored procedure.

4. Write a query to create a stored procedure named GetStudentByID that retrieves details of a specific student by StudentID.

5. Write a query to execute the GetStudentByID stored procedure with an input parameter.

6. Write a query to create a stored procedure named GetTotalStudentsByCourse that returns the total number of students in a specific course using an output parameter.

sql

-- Create a stored procedure to retrieve all student details
CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT StudentID, FirstName, LastName, Age, CourseID
    FROM Students;
END;

-- Execute the GetAllStudents stored procedure
CALL GetAllStudents();

-- Create a stored procedure to retrieve details of a specific student by StudentID
CREATE PROCEDURE GetStudentByID(IN student_id INT)
BEGIN
    SELECT StudentID, FirstName, LastName, Age, CourseID
    FROM Students
    WHERE StudentID = student_id;
END;

-- Execute the GetStudentByID stored procedure with an input parameter
CALL GetStudentByID(1);

-- Create a stored procedure to get the total number of students in a specific course
CREATE PROCEDURE GetTotalStudentsByCourse(IN course_id INT, OUT total_students INT)
BEGIN
    SELECT COUNT(*) INTO total_students
    FROM Students
    WHERE CourseID = course_id;
END;

-- Execute the GetTotalStudentsByCourse stored procedure with input and output parameters
CALL GetTotalStudentsByCourse(101, @total_students);
SELECT @total_students AS TotalStudents;

Important Tips:

• Stored procedures can encapsulate complex logic and improve performance by reducing network traffic.

• Use parameters to pass input values and return output values, making stored procedures flexible and reusable.

• Regularly review and optimize stored procedures to ensure they run efficiently.

Understanding and using stored procedures allows you to encapsulate logic and improve the performance and maintainability of your database applications.