How to Use Views and Stored Procedures in MySQL

Introduction

SQL views and stored procedures allow storing SQL statements as an object in the database. You can reference the stored statements using the object name as an alias in your application. When referenced, the statements are directly executed on the database engine, saving the amount of information transferred. Using these objects, you can offload some logic from your application to the database engine, creating an abstraction layer for complex queries or lookups and data manipulation.

The SQL view object consists of a single SELECT statement used for complex queries and selecting columns from one or more tables to form a virtual table. You can reference it using the object name like other tables stored in your database.

The SQL stored procedure object is a set of SQL statements for executing tasks and complex queries. It supports parameters allowing the object to act based on the input values.

These objects help you adhere to the Don't Repeat Yourself (DRY) principle, reducing code repetition by offloading frequently used queries and tasks as database objects.

You can restrict the user by letting them only access the objects containing predefined queries and tasks, hiding the complexity, and securing the sensitive data in the database.

This guide walks you through the differences between SQL views and stored procedures database objects, how to create, update and delete these objects and set up user access limits in your MySQL database.

Prerequisites

Before you begin, make sure you have the following:

• An Ubuntu 20.04 Server
• A MySQL database server.

Create a Sample Database

Create a sample database named sample_db to understand the implementation of SQL views and stored procedures. The database contains two tables: states and state_population. The states table has three columns: id, state, and country, and the state_population table consists of three columns: id, state_id, and population. Finally, you populate these tables with sample values. This database serves as a workbench to test the SQL views and stored procedures database objects.

1. Log in to your MySQL server as root.

    # mysql -u root -p
   

2. Create a sample database named sample_db.

    mysql> CREATE DATABASE sample_db;
   

3. Switch to the sample_db database.

    mysql> USE sample_db;
   

4. Create a new table named states.

    mysql> CREATE TABLE states
           (
              id BIGINT PRIMARY KEY AUTO_INCREMENT,
              name VARCHAR(255),
              country VARCHAR(255)
           ) ENGINE = InnoDB;
   

5. Create a new table named state_population.

    mysql> CREATE TABLE state_population
           (
              id BIGINT PRIMARY KEY AUTO_INCREMENT,
              state_id BIGINT,
              population BIGINT
           ) ENGINE = InnoDB;
   

6. Populate the states table with sample values.

    mysql> INSERT INTO states(name, country) VALUES ('Massachusetts', 'USA');
           INSERT INTO states(name, country) VALUES ('Wisconsin', 'USA');
           INSERT INTO states(name, country) VALUES ('Oregon', 'USA');
           INSERT INTO states(name, country) VALUES ('Western Australia', 'Australia');
           INSERT INTO states(name, country) VALUES ('Texas','USA');
           INSERT INTO states(name, country) VALUES ('Victoria','Australia');
           INSERT INTO states(name, country) VALUES ('South Australia', 'Australia');
           INSERT INTO states(name, country) VALUES ('Tasmania', 'Australia');
           INSERT INTO states(name, country) VALUES ('Nevada', 'USA');
           INSERT INTO states(name, country) VALUES ('Queensland', 'Australia');
   

7. Populate the state_population table with sample values.

    mysql> INSERT INTO state_population(state_id, population) VALUES (1, 7029917); 
           INSERT INTO state_population(state_id, population) VALUES (2, 5893718); 
           INSERT INTO state_population(state_id, population) VALUES (3, 4237256); 
           INSERT INTO state_population(state_id, population) VALUES (4, 2685165); 
           INSERT INTO state_population(state_id, population) VALUES (5, 29145505); 
           INSERT INTO state_population(state_id, population) VALUES (6, 6643062); 
           INSERT INTO state_population(state_id, population) VALUES (7, 1772787); 
           INSERT INTO state_population(state_id, population) VALUES (8, 540839); 
           INSERT INTO state_population(state_id, population) VALUES (9, 3104614); 
           INSERT INTO state_population(state_id, population) VALUES (10, 5240520);

Note: Following this guide, you must execute all SQL commands inside the MySQL console logged in as the root user by default.

Implement SQL Views

The SQL view object allows storing predefined queries for making a virtual table consisting of columns from one or more tables in the database. It consists of a single SELECT statement to create an abstraction layer over tables.

By using view objects in your database, you can represent a subset of data, limit the degree of exposure, join multiple tables into a single virtual table and hide the complexity of the data in your database.

The general use case of a view object is making an alias for redundant queries in the code base, placing the logic in a single place. After defining a view, you can reference it like other tables stored in the database.

Example: You request state and population count from the database in multiple parts of your application using the following SQL statement.

SELECT name, population 
FROM states LEFT JOIN state_population 
ON states.id = state_population.state_id;

You can store this SQL statement as a view object and reference it using the following SQL statement. This way, you reduce code repetition and traffic between your application and the database engine.

SELECT * FROM population_stats;

Similarly, you can convert complex queries used in your application into view objects. In the following section, you learn how to create, update and delete view objects in your MySQL database.

Create a SQL View

To create a new view object, you use the CREATE VIEW statement followed by the desired name of the object and the SELECT statement used for abstraction. Here, you create a new view object.

mysql> CREATE VIEW population_stats AS 
       SELECT 
           name,
           population
       FROM states 
       LEFT JOIN state_population 
       ON states.id = state_population.state_id;

Run the view object.

mysql> SELECT * FROM population_stats;

Output.

+-------------------+------------+
| name              | population |
+-------------------+------------+
| Massachusetts     |    7029917 |
| Wisconsin         |    5893718 |
| Oregon            |    4237256 |
| Western Australia |    2685165 |
| Texas             |   29145505 |
| Victoria          |    6643062 |
| South Australia   |    1772787 |
| Tasmania          |     540839 |
| Nevada            |    3104614 |
| Queensland        |    5240520 |
+-------------------+------------+
10 rows in set (0.01 sec)

Update a SQL View

To update a view object, you use the ALTER VIEW statement followed by the name of the existing view object and the updated SELECT statement. Here, you update the population_stats view object to return the country field in addition to previously selected fields.

mysql> CREATE OR REPLACE VIEW population_stats AS 
       SELECT 
           name,
           country,
           population
       FROM states 
       LEFT JOIN state_population 
       ON states.id = state_population.state_id;

Run the view object.

mysql> SELECT * FROM population_stats;

With the changes made to the view object, the output now has a country column.

+-------------------+------------+------------+
| name              | country    | population |
+-------------------+------------+------------+
| Massachusetts     | USA        |    7029917 |
| Wisconsin         | USA        |    5893718 |
| Oregon            | USA        |    4237256 |
| Western Australia | Australia  |    2685165 |
| Texas             | USA        |   29145505 |
| Victoria          | Australia  |    6643062 |
| South Australia   | Australia  |    1772787 |
| Tasmania          | Australia  |     540839 |
| Nevada            | USA        |    3104614 |
| Queensland        | Australia  |    5240520 |
+-------------------+------------+------------+
10 rows in set (0.01 sec)

Delete a SQL View

To delete a view object, you use the DROP VIEW statement followed by the name of the view object. For example, here, you delete the population_stats view object.

mysql> DROP VIEW population_stats;

Implement SQL Stored Procedures

The SQL stored procedure object allows storing a set of SQL statements in the database. Unlike the view object, the stored procedure object accepts parameters, can store multiple SQL statements, and does not act as a virtual table. Instead, you execute them using the CALL statement.

A stored procedure object can have multiple parameters for input, output, or both combined. The support for parameters in stored procedure objects allows it to act based on passed parameter values. For example, you can use the input parameter values as variables in the object body for conditional SQL expressions.

The general use of a stored procedure object is storing a group of SQL statements to execute tasks or complex queries. Storing logic as an object in your database increases the performance and reduces the traffic between your application and the database engine.

Example: You request population count for individual states from the database in multiple parts of your application using the following SQL statement.

SELECT population FROM state_population WHERE state_id = value;

You can store this SQL statement as a stored procedure object and execute it using the following SQL statement. This way, you can request population count for a state using minimal code, reduce code repetition in your application and reduce the amount of information transferred between your application and the database engine.

CALL fetch_population(state_id);

Similarly, you can convert tasks and complex queries used in your application into stored procedure objects. In the following section, you learn how to create and delete stored procedures in your MySQL database.

Note: You can not update the body of a stored procedure object after creation. To update the logic stored in an object, you must delete it and create it again with the same object name.

Create a SQL Stored Procedure

A stored procedure object can have multiple parameters and use any parameter mode in combination. The following are parameter modes supported by SQL stored procedure object in MySQL.

• IN parameter: used for passing arguments to the object, the value of this parameter remains unchanged even if altered inside the object body.
• OUT parameter: used for passing output from the object. You can set the value of this parameter inside the object body. The initial value of this parameter is not accessible in the object.
• INOUT parameter: a combination of IN and OUT mode, the initial value of this parameter is accessible in the object, which allows passing arguments to the object, and unlike IN mode, the value of this parameter not protected and you can change it to pass output from the object.

Parameter Syntax:

MODE name TYPE

Here, MODE defines parameter mode, name defines parameter name and TYPE defines the value type.

Stored Procedure with IN Parameter

Create a stored procedure object named population_with_in with one IN parameter named state used for the state match case.

mysql> DELIMITER //
       CREATE PROCEDURE population_with_in (IN state INT)
       BEGIN 
           SELECT 
               population
           FROM state_population
           WHERE state_id = state;
       END//
       DELIMITER ;

The above code snippet uses the DELIMITER statement to differentiate between SQL statements inside the object body and the SQL statement used for creating the object.

Run the stored procedure object.

mysql> CALL population_with_in(1);

Output.

+------------+
| population |
+------------+
|    7029917 |
+------------+
1 row in set (0.00 sec)

Stored Procedure with IN and OUT Parameters

Create a stored procedure object named population_with_in_and_out with one IN parameter named state used for the state match case and a OUT parameter for population count value.

mysql> DELIMITER //
       CREATE PROCEDURE population_with_in_and_out (IN state INT, OUT population_output INT)
       BEGIN 
           SELECT 
               population INTO population_output
           FROM state_population
           WHERE state_id = state;
       END//
       DELIMITER ;

Run the stored procedure object.

mysql> CALL population_with_in_and_out(2, @population);
       SELECT @population;

Output.

+-------------+
| @population |
+-------------+
|     5893718 |
+-------------+
1 row in set (0.00 sec)

Stored Procedure with INOUT Parameter

Create a stored procedure object named population_with_inout with one INOUT parameter named state used for the state match case and population count output value.

mysql> DELIMITER //
       CREATE PROCEDURE population_with_inout (INOUT state INT)
       BEGIN 
           SELECT 
               population INTO state
           FROM state_population
           WHERE state_id = state;
       END//
       DELIMITER ;

Run the stored procedure object.

mysql> SET @state = 3;
       CALL population_with_inout(@state);
       SELECT @state;

Output.

+---------+
| @state  |
+---------+
| 4237256 |
+---------+
1 row in set (0.00 sec)

Delete a SQL Stored Procedure

To delete a stored procedure object, you use the DROP procedure statement followed by the object name. Here, you delete all the objects created in the previous section.

mysql> DROP PROCEDURE population_with_in;
       DROP PROCEDURE population_with_in_and_out;
       DROP PROCEDURE population_with_inout;

Set Up User Access Limit

You can limit database exposure by restricting the user's access to the predefined view or stored procedure objects that returns only required data. The abstraction layer created by these objects allows you to hide sensitive data, complex queries, or business logic. In this section, you learn how to create a new user and grant permission to access the view or stored procedure objects.

Create a New User

Create a new user using the CREATE USER statement followed by the username, hostname, and password.

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testPassword@123';

The password used is strictly for demonstration purposes. Ensure that you use a stronger password when deploying in production to protect your database.

Grant View Object Access

Allow the MySQL user to access the view object using the GRANT SELECT statement followed by the view object name and username.

1. Grant view object access.

    mysql> GRANT SELECT ON sample_db.population_stats TO 'testuser'@'localhost';
   

2. Log in to your MySQL server as testuser in a new terminal.

    # mysql -u testuser -p
   

3. Verify view object access.

    mysql> SELECT * FROM sample_db.population_stats;

Grant Stored Procedure Object Access

Allow the MySQL user to access the stored procedure object using the GRANT EXECUTE statement followed by the stored procedure object name and username.

1. Grant stored procedure object access.

    mysql> GRANT EXECUTE ON PROCEDURE sample_db.population_with_in TO 'testuser'@'localhost';
   

2. Log in to your MySQL server as testuser in a new terminal.

    # mysql -u testuser -p
   

3. Verify stored procedure object access.

    mysql> CALL sample_db.population_with_in(1);

Conclusion

You learned how to create, update & delete SQL views and stored procedures database objects and set up user access limits in MySQL. You can integrate SQL views and stored procedures into your database using the demonstrated operations. Visit the MySQL stored objects documentation for more information.