Que sont les vues, fonctions et procédures stockées MySQL

1. Vue

La soi-disant vue fait référence à l'ensemble de résultats récupéré par l'instruction de requête SQL, qui apparaît sous la forme d'une table virtuelle, contrairement à la table physique réelle, elle n'existe pas dans la base de données. Le rôle d'une vue est de simplifier les requêtes complexes en concentrant les opérations d'association et de filtrage multi-tables dans une seule vue, puis en interrogeant la vue pour obtenir les résultats requis. Les vues ont les caractéristiques suivantes :

Les vues ne stockent pas de données, mais sont générées dynamiquement en fonction des résultats des instructions SELECT ;
Les vues peuvent uniquement être lues, pas écrites ;
Les vues peuvent être créées sur la base d'une ou plusieurs tables.
Ce qui suit est un exemple de création d'une vue :

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;

Cette instruction crée une vue nommée vw_employee, qui contient les données des tables des employés et des services. Vous pouvez interroger la vue avec l'instruction suivante :

SELECT * FROM vw_employee;

2. Fonction

. Une fonction est une unité de programme réutilisable qui encapsule un élément logique spécifique et peut être utilisée pour accomplir une tâche spécifique. Dans MySQL, les fonctions sont divisées en deux catégories : les fonctions scalaires et les fonctions d'agrégation. Les fonctions scalaires génèrent une valeur unique, tandis que les fonctions d'agrégation génèrent des valeurs globales telles que COUNT, SUM, AVG, etc. Les fonctions ont les caractéristiques suivantes :

les fonctions ont des entrées et des sorties, peuvent recevoir des paramètres et renvoyer des résultats ;
les fonctions peuvent appeler d'autres fonctions ;
les fonctions peuvent être des appels imbriqués ;
Ce qui suit est un exemple de création d'une fonction scalaire :

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;

Cette instruction crée une fonction scalaire nommée get_employee_name, qui reçoit un numéro d'employé et renvoie le nom de l'employé.
Appelez cette fonction :

SELECT get_employee_name (100001);

3. Procédures stockées

Une procédure stockée est un ensemble d'instructions SQL prédéfinies qui sont encapsulées dans une unité et peuvent être appelées à plusieurs reprises. Les procédures stockées peuvent recevoir des paramètres d'entrée et des paramètres de sortie, et elles ont les caractéristiques suivantes :

Les procédures stockées peuvent contenir plusieurs instructions SQL et peuvent effectuer des tâches complexes ;
Les procédures stockées peuvent être exécutées côté serveur, réduisant ainsi la surcharge de transmission réseau ; les procédures peuvent être partagées par plusieurs applications.
Ce qui suit est un exemple d'utilisation de procédures stockées, en supposant que nous disposons des trois tables suivantes :

table des employés : stocke les informations de base des employés

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

Insérez des données :

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

table des départements : stocke les informations de base des départements

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

Insérer des données :

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

table dept_emp : stocke la relation entre les employés et les départements

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

Insérer des données :

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

Maintenant, nous pouvons créer une procédure stockée pour interroger le nombre d'employés et les détails des employés dans un certain département :

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;

Cette instruction crée une procédure stockée nommée get_employee_by_dept, qui reçoit un nom de département comme paramètre d'entrée et renvoie le nombre d'employés dans le département et les détails de l'employé.

Appelez cette fonction

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

Dans les applications pratiques, les vues, les fonctions et les procédures stockées peuvent toutes jouer un rôle important. Par exemple, dans une application d'entreprise complexe, vous devrez peut-être obtenir des données de plusieurs tables et y effectuer des opérations de filtrage et d'agrégation. Dans ce cas, les vues peuvent être utilisées pour simplifier les requêtes complexes. De plus, si une logique métier commune doit être réutilisée, elle peut être encapsulée sous forme de fonctions ou de procédures stockées pour améliorer la réutilisabilité et la maintenabilité du code.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!