How to Use Views and Stored Procedures in MySQL?

SQL views and stored procedures are actual objects stored inside the database. You can save your SQL statements inside them and use them again without writing them every time.

Well, you give these statements a name when you save them like this. Later, you can use that name like a shortcut in your application. The database engine understands that name and runs the saved SQL directly. This saves a lot of back-and-forth between your application and the database.

Moreover, these objects help you put some logic inside the database itself. Also, you do not need to handle everything in your application code. This keeps your app lighter and your database smarter.

Further, you can use these objects to hide complex steps and make things look simple from the outside.

About SQL views and stored procedures

A SQL view is one such object that stores a single SELECT query that can take data from one table or more than one. The view shows a virtual table that you can use like any normal table. But the actual data remains safe in the original tables.

Furthermore, a stored procedure is another kind of object. It holds many SQL steps inside it and runs them by calling the procedure. You can also give input values to it. The procedure understands those values and works based on them. This makes it flexible.

All in all, these tools help you avoid repeating the same queries again and again. You write them once and use them whenever you want. This follows a good rule in coding. It says you should not repeat yourself when you can avoid it (Don’t Repeat Yourself (DRY) principle).

You can also use views and procedures to give limited access to others for what is needed. This keeps your main data safe as the users cannot see or touch the hidden parts. This gives more control and more safety.

This guide will help you understand how SQL views and stored procedures work.

Getting Started with SQL Views and Stored Procedures in MySQL

Before you start, you must have MySQL server installed. You need a server that runs Ubuntu 20.04. You also need MySQL installed and working fine. Make sure you can log in as the root user because that user can create and manage everything in MySQL without restrictions.

To get inside your MySQL console, you just type this command:

mysql -u root -p

The system will ask for your root password. Type it and press Enter.

Make a Sample Database for Practice

Let us now build a small test setup to understand views and stored procedures. First, we will make a new database.
```
CREATE DATABASE sample_db;
USE sample_db;
```

Now we will create two tables inside it. One table will hold states, and the other will hold population values.

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

And here’s the second one:

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

Let us now insert some sample values into these tables.

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');

Now, let us add population details:

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);

Your tables are ready and have enough data to start playing with views and procedures.

Create and Use SQL Views

Views help you save a query and reuse it later. Instead of writing the same JOIN or SELECT again and again, you create a view and run it like a normal table.
Suppose you want to know the names of states and their populations. You can write:
```
SELECT name, population
FROM states
LEFT JOIN state_population
ON states.id = state_population.state_id;
```

But why write this every time? Just save it as a view:

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

Now, you can simply run:
```
SELECT * FROM population_stats;
```
You will get the same result.

Update a View

Let us say now you want to include the country also. You will just replace the old view like this:
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 again:
```
SELECT * FROM population_stats;
```
You will now see the name, country and population.

Delete a View

If you don’t need a view anymore, you can remove it like this:

DROP VIEW population_stats;

Create and Use SQL Stored Procedures

With stored procedures, you can group multiple SQL steps together. You give it some input, and it gives you the result. It is very useful when you want to run the same logic over and over again.

A stored procedure is like a mini program that runs inside your MySQL database. It can take inputs and give outputs. Also, it helps when you want to run many SQL steps together with some logic.
You use CALL to run a procedure.

Let us create a basic one with one input:

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

To run it, use:
```
CALL population_with_in(1);
```
This will give the population of the first state.

Use IN and OUT Together

Stored procedures can have different types of parameters:

IN parameters: These are used to pass values into the procedure.
OUT parameters: These are used to return values from the procedure.
INOUT parameters: These can pass values in and return values out.

Now we will create a stored procedure that takes a state ID and gives back the population through an output variable.

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 ;
To use this, i.e. to call this stored procedure and get the output:
CALL population_with_in_and_out(2, @population);
SELECT @population;

Use INOUT for Both In and Out

Sometimes you want to give a value and also get a new value in return using the same variable. Use INOUT for that.

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

Now try this:

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

You will see the population of state 3 in @state.

Delete Stored Procedures

To remove any of the above procedures:

DROP PROCEDURE population_with_in;
DROP PROCEDURE population_with_in_and_out;
DROP PROCEDURE population_with_inout;

Give Limited Access to Other Users

You may not want everyone to access full data or run every command. You can control this using views and stored procedures.

Create a New MySQL User

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

Give Access to a View

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

Now log in as testuser and run:
```
mysql -u testuser -p
```

Then run:

SELECT * FROM sample_db.population_stats;

Give Access to a Stored Procedure

GRANT EXECUTE ON PROCEDURE sample_db.population_with_in TO ‘testuser’@’localhost’;

Conclusion

Now you know how to create views and stored procedures in MySQL. You also learnt how to use them properly and safely. These tools help your app become faster and your code cleaner. Now, you can protect your data better and reduce the extra load on your application. Explore more in MySQL Reference Manual for Stored Objects