MySQL 檢視、函數和儲存過程詳解

2023-11-01 18:01:15

一、檢視

檢視是一種虛擬表，它是根據 SQL 語句檢索出的結果集，與物理表不同的是，它並不在資料庫中實際存在。檢視的作用是為了簡化複雜的查詢，將多表關聯和過濾操作集中到一個檢視中，然後通過查詢該檢視來獲取需要的結果。檢視具有以下幾個特點：

檢視不儲存資料，而是根據 SELECT 語句的結果動態生成的；
檢視只能讀取，不能寫入；
檢視可以基於一個或多個表建立。
下面是一個檢視的建立範例：

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;

該語句建立了一個名為 vw_employee 的檢視，它包含了 employees 和 departments 兩個表中的資料，可以用以下語句查詢該檢視：

SELECT * FROM vw_employee;

二、函數

函數是一種可重用的程式單元，它封裝了一段特定的邏輯，可以用於完成特定的任務。在 MySQL 中，函數分為兩類：標量函數和聚合函數。標量函數返回單個值，而聚合函數返回一個聚合值，如 COUNT、SUM、AVG 等。函數具有以下幾個特點：

函數具有輸入和輸出，可以接收引數並返回結果；
函數可以呼叫其他函數；
函數可以巢狀呼叫。
下面是一個標量函數的建立範例：

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;

該語句建立了一個名為 get_employee_name 的標量函數，它接收一個員工編號，返回該員工的姓名。
呼叫該函數：

SELECT get_employee_name (100001);

三、儲存過程

儲存過程是一組預定義的 SQL 語句集合，它們被封裝在一個單元內，可以被重複呼叫。儲存過程可以接收輸入引數和輸出引數，它們具有以下幾個特點：

儲存過程可以包含多條 SQL 語句，可以完成複雜的任務；
儲存過程可以在伺服器端執行，減少網路傳輸的開銷；
儲存過程可以被多個應用程式共用。
下面是一個使用儲存過程的範例，假設我們有以下三個表：

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 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 表：儲存部門的基本資訊

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

插入一些資料：

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

dept_emp 表：儲存員工與部門的關係

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

插入一些資料：

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

現在，我們可以建立一個儲存過程來查詢某個部門中的員工數量和員工詳細資訊：

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;

該語句建立了一個名為 get_employee_by_dept 的儲存過程，它接收一個部門名稱作為輸入引數，並返回該部門中的員工數量和員工詳細資訊。

呼叫該函數

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

在實際應用中，檢視、函數和儲存過程都可以發揮重要的作用。例如，在一個複雜的企業應用中，可能需要從多個表中獲取資料，並對其進行過濾和聚合操作，這時可以使用檢視來簡化複雜的查詢。另外，如果有一些常用的業務邏輯需要重複使用，可以將其封裝為函數或儲存過程，以提高程式碼的重用性和可維護性。

總之，檢視、函數和儲存過程都是 MySQL 中非常重要的資料庫物件，它們可以提高資料庫的效率和可重用性，使開發人員更加高效地處理資料。在實際應用中，可以根據具體的業務需求，靈活地使用這些物件，以提高資料處理的效率和質量。

到此這篇關於MySQL 檢視、函數和儲存過程的文章就介紹到這了,更多相關MySQL 檢視、函數和儲存過程內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL 檢視、函數和儲存過程詳解

目錄

一、檢視

二、函數

三、儲存過程

熱門文章