MySQL Cursor | Database Management | Programming Tutorials

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

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.

Start the MySQL server:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
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.

Create a new file named getSection.sql in the /home/labex/project directory.
In the getSection.sql file, define the delimiter as //:
```
DELIMITER //
```

Create the getSectionProcude() stored procedure:

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

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.

In the MySQL prompt, run the following command to execute the subquery.sql script:
```
SOURCE ~/project/getSection.sql
```

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.

Stored Procedure Cursor Database Retrieval