What are MySQL views, functions and stored procedures

1. View

The so-called view refers to the result set retrieved by the SQL query statement, which appears in the form of a virtual table. Unlike the actual physical table, it does not exist in the database. The role of a view is to simplify complex queries by concentrating multi-table association and filtering operations into one view, and then querying the view to obtain the required results. Views have the following characteristics:

Views do not store data, but are dynamically generated based on the results of the SELECT statement;
Views can only be read, not written;
Views can be based on a or Multiple tables are created.
The following is an example of creating a view:

CREATE VIEW vw_employee AS SELECT
e.emp_no,
e.first_name,
e.last_name,
d.dept_name 
FROM
	employees e
	JOIN departments d ON e.dept_no = d.dept_no;

This statement creates a view named vw_employee, which contains data from the employees and departments tables. You can query the view with the following statement:

SELECT * FROM vw_employee;

2. Function

A function is a reusable program unit that encapsulates a specific piece of logic and can be used to complete a specific task. In MySQL, functions are divided into two categories: scalar functions and aggregate functions. Scalar functions output a single value, while aggregate functions output aggregate values ​​such as COUNT, SUM, AVG, etc. Functions have the following characteristics:

Functions have input and output, can receive parameters and return results;
functions can call other functions;
functions can be nested calls.
The following is an example of creating a scalar function:

CREATE FUNCTION get_employee_name ( emp_no INT ) RETURNS VARCHAR ( 50 ) BEGIN
	DECLARE
		emp_name VARCHAR ( 50 );
	SELECT
		CONCAT_WS( ' ', first_name, last_name ) INTO emp_name 
	FROM
		employees 
	WHERE
		emp_no = emp_no;
RETURN emp_name;
END;

This statement creates a scalar function named get_employee_name, which receives an employee number and returns the employee's name.
Call this function:

SELECT get_employee_name (100001);

3. Stored procedures

Stored procedures are a set of predefined SQL statements that are encapsulated in a unit and can be called repeatedly. Stored procedures can receive input parameters and output parameters, and they have the following characteristics:

Stored procedures can contain multiple SQL statements and can complete complex tasks;
Stored procedures can be executed on the server side, reducing Network transmission overhead;
Stored procedures can be shared by multiple applications.
The following is an example of using stored procedures, assuming we have the following three tables:

employees table: stores basic information of employees

CREATE TABLE employees (
  emp_no INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  gender ENUM('M', 'F'),
  birth_date DATE,
  hire_date DATE
);

Insert some data:

INSERT INTO employees VALUES
(10001, 'Georgi', 'Facello', 'M', '1953-09-02', '1986-06-26'),
(10002, 'Bezalel', 'Simmel', 'F', '1964-06-02', '1985-11-21'),
(10003, 'Parto', 'Bamford', 'M', '1959-12-03', '1986-08-28'),
(10004, 'Chirstian', 'Koblick', 'M', '1955-01-05', '1986-12-01'),
(10005, 'Kyoichi', 'Maliniak', 'M', '1959-09-12', '1989-09-12'),
(10006, 'Anneke', 'Preusig', 'F', '1953-04-20', '1989-06-02'),
(10007, 'Tzvetan', 'Zielinski', 'F', '1957-05-23', '1989-02-10');

departments table: stores the basic information of the department

CREATE TABLE departments (
  dept_no CHAR(4) PRIMARY KEY,
  dept_name VARCHAR(50)
);

Insert some data:

INSERT INTO departments VALUES
('d001', 'Marketing'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d004', 'Production'),
('d005', 'Development'),
('d006', 'Quality Management'),
('d007', 'Sales'),
('d008', 'Research');

dept_emp table: stores the relationship between employees and departments

CREATE TABLE dept_emp (
  emp_no INT,
  dept_no CHAR(4),
  from_date DATE,
  to_date DATE,
  PRIMARY KEY (emp_no, dept_no)
);

Insert some data:

INSERT INTO dept_emp VALUES
(10001, 'd001', '1986-06-26', '9999-01-01'),
(10002, 'd001', '1985-11-21', '9999-01-01'),
(10003, 'd002', '1986-08-28', '9999-01-01'),
(10004, 'd005', '1986-12-01', '9999-01-01'),
(10005, 'd005', '1989-09-12', '9999-01-01'),
(10006, 'd006', '1989-06-02', '9999-01-01'),
(10007, 'd007', '1989-02-10', '9999-01-01');

Now, we can create a stored procedure to query the number of employees and employee details in a department:

CREATE PROCEDURE get_employee_by_dept ( IN dept_name VARCHAR ( 50 ), OUT employee_count INT ) BEGIN
	SELECT
		COUNT(*) INTO employee_count 
	FROM
		employees e
		JOIN dept_emp de ON e.emp_no = de.emp_no
		JOIN departments d ON de.dept_no = d.dept_no 
	WHERE
		d.dept_name = dept_name;
	SELECT
		e.emp_no,
		e.first_name,
		e.last_name,
		e.gender,
		e.birth_date,
		e.hire_date 
	FROM
		employees e
		JOIN dept_emp de ON e.emp_no = de.emp_no
		JOIN departments d ON de.dept_no = d.dept_no 
	WHERE
	d.dept_name = dept_name;
END;

This statement creates a stored procedure named get_employee_by_dept, which receives a department Name as input parameter and returns the number of employees and employee details in the department.

Call this function

CALL get_employee_by_dept('Development', @employee_count);
SELECT @employee_count;

In practical applications, views, functions and stored procedures can all play an important role. For example, in a complex enterprise application, you may need to obtain data from multiple tables and perform filtering and aggregation operations on it. In this case, views can be used to simplify complex queries. In addition, if there are some common business logic that need to be reused, they can be encapsulated as functions or stored procedures to improve the reusability and maintainability of the code.

The above is the detailed content of What are MySQL views, functions and stored procedures. For more information, please follow other related articles on the PHP Chinese website!