Stored Procedure Cursor Database Retrieval

Introduction

In this project, you will learn how to use a cursor to output the course information from the section table where the semester is Spring. You will create a stored procedure to retrieve the course details and then execute the procedure to display the results.

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and import a database
• How to create a stored procedure using a cursor
• How to execute a stored procedure to retrieve data from a database

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use a cursor to retrieve data from a database
• Create a stored procedure to encapsulate database operations
• Execute a stored procedure to display the desired data

Skills Graph

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the edusys.sql database into MySQL.

1. Start the MySQL server:

   sudo /etc/init.d/mysql start

2. Log into the MySQL terminal:

   mysql -uroot

3. Import the edusys.sql database:

   SOURCE ~/project/edusys.sql

Create the Stored Procedure

In this step, you will learn how to create a stored procedure to retrieve the course information from the section table where the semester is Spring.

1. Create a new file named getSection.sql in the /home/labex/project directory.

2. In the getSection.sql file, define the delimiter as //:

   DELIMITER //

3. Create the getSectionProcude() stored procedure:

   CREATE PROCEDURE getSectionProcude()
   BEGIN
       SELECT course_id, year, room_number
       FROM section
       WHERE semester = 'Spring';
   END //

4. Reset the delimiter back to the default:

   DELIMITER ;

Execute the Stored Procedure

In this step, you will learn how to execute the getSectionProcude() stored procedure to retrieve the course information.

1. In the MySQL prompt, run the following command to execute the subquery.sql script:

   SOURCE ~/project/getSection.sql

2. Execute the stored procedure:

   CALL getSectionProcude();

   This will output the course information where the semester is Spring.

   MariaDB [edusys]> CALL getSectionProcude();
   +-----------+------+-------------+
   | course_id | year | room_number |
   +-----------+------+-------------+
   | CS-101    | 2018 | 101         |
   | FIN-201   | 2018 | 101         |
   | MU-199    | 2018 | 101         |
   | HIS-351   | 2018 | 514         |
   | CS-190    | 2017 | 3128        |
   | CS-190    | 2017 | 3128        |
   | CS-319    | 2018 | 3128        |
   | EE-181    | 2017 | 3128        |
   | CS-319    | 2018 | 100         |
   | CS-315    | 2018 | 120         |
   +-----------+------+-------------+
   10 rows in set (0.001 sec)

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.