存儲過程、觸發器和函數在數據庫中具有重要的作用，它們可以帶來以下幾個方面的重要性：
-
數據一致性和完整性：
- 觸發器和存儲過程可以用于實現數據一致性和完整性約束。通過在數據庫操作（如插入、更新、刪除）發生時自動執行相關邏輯，可以確保數據的正確性，減少錯誤和數據異常。
-
業務邏輯封裝：
- 存儲過程和函數可以封裝復雜的業務邏輯，使其在數據庫層面執行。這樣可以減少應用程序的負擔，提高系統性能，并且可以確保在數據庫操作中應用相同的業務規則。
-
性能優化：
- 存儲過程和函數在數據庫中預編譯，可以提高執行效率。通過減少與數據庫服務器之間的通信次數和數據傳輸量，可以降低系統的負載，提高響應速度。
-
安全性：
- 存儲過程和函數可以實現數據庫訪問的安全性控制。通過限制用戶對數據庫的直接訪問，并通過存儲過程和函數來執行特定操作，可以有效防止惡意操作和未經授權的訪問。
-
代碼重用和維護：
- 存儲過程和函數可以被多個應用程序或者查詢重復使用，提高了代碼的重用性。當需要修改業務邏輯時，只需修改存儲過程或函數的代碼，而不需要修改所有調用它們的代碼。
1.創建表
CREATE DATABASE staff;
USE staff;
CREATE TABLE employee(id INT NOT NULL AUTO_INCREMENT,userName VARCHAR(255),birthDate DATE,idCard VARCHAR(255),loginName VARCHAR(255),PASSWORD VARCHAR(255),mobile VARCHAR(255),email VARCHAR(255),deptId INT,LEVEL INT,avatar BLOB,remark TEXT,PRIMARY KEY(id)
);CREATE TABLE dept(id INT NOT NULL AUTO_INCREMENT,deptName VARCHAR(255),manageId INT,remark VARCHAR(255),PRIMARY KEY(id)
);CREATE TABLE payroll(id INT NOT NULL AUTO_INCREMENT,empId INT,baseSalary DOUBLE,actualSalary DOUBLE,bonus DOUBLE,deductMoney DOUBLE,grantDate DATE,PRIMARY KEY(id)
);CREATE TABLE ask_leave(id INT NOT NULL AUTO_INCREMENT,empId INT,leaveReason TEXT,beginDate DATE,endDate DATE,submitDate DATE,auditId INT,STATUS INT,auditOpinion TEXT,PRIMARY KEY(id)
);
2.編寫存儲過程實現插入員工表：參數為：
| 員工編號 | id | int |
|---|---|---|
| 姓名 | userName | varchar(225) |
| 出生日期 | birthDate | date |
| 身份證號 | idCard | varchar(225) |
| 登錄名稱 | loginName | varchar(225) |
| 登錄密碼 | password | varchar(225) |
| 手機號 | mobile | varchar(225) |
| 電子郵件 | varchar(225) | |
| 部門編號 | deptId | int |
| 員工級別 | level | int |
| 員工頭像 | avatar | blob |
| 備注 | remark | text |
存儲過程名稱為：insert_employee
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_employee`(IN `id` int,IN `username` varchar(225),IN `birthDate` date,IN `idCard` varchar(225),IN `loginName` varchar(225),IN `password` varchar(225),IN `mobile` varchar(225),IN `email` varchar(225),IN `deptId` int,IN `level` int,IN `avatar` blob,IN `remark` text
)
BEGINDECLARE cnt INT;SELECT COUNT(*) INTO cnt FROM employee WHERE employee.id = id;IF cnt = 0 THEN INSERT INTO employee(`id`,`username`,`birthDate`,`idCard`,`loginName`,`password`,`mobile`,`email`,`deptId`,`level`,`avatar`,`remark` )VALUES(`id`,`username`,`birthDate`,`idCard`,`loginName`,`password`,`mobile`,`email`,`deptId`,`level`,`avatar`,`remark`);END IF;
END
3.利用存儲過程在員工表中插入記錄.
call insert_employee(1,'小紅','2002-03-14','411423200203141510','xiaohong','123','15238790678','1625376859@qq.com',3,1,NULL,'新員工');
call insert_employee(2,'小橙','2002-02-14','411423200203241511','xiaocheng','123','15238790677','1625376858@qq.com',2,2,NULL,'新員工');
4.創建觸發器。
插入
CREATE TRIGGER `insert_payroll` BEFORE
INSERT ON `payroll`
FOR EACH ROW SET new.actualSalary = new.baseSalary + new.bonus - new.deductMoney;
更新
CREATE DEFINER = `root`@`localhost`
TRIGGER `update_payroll` BEFORE
UPDATE ON `payroll`
FOR EACH ROW SET new.actualSalary = new.baseSalary + new.bonus - new.deductMoney;
5.在員工表中依據姓名userName建立索引。索引名為：index_userName。
CREATE INDEX index_userName
ON employee(username);
6.建立員工部門工資視圖（包含員工名稱，部門名稱，基本工資,應發工資,獎金,缺勤扣錢）視圖名稱：v_employee_dept_payroll。
CREATE VIEW v_employee_dept_payroll AS
SELECT username AS 姓名,deptName AS 部門名稱,baseSalary AS 基本工資,actualSalary AS 應發工資,bonus AS 獎金,deductMoney AS 缺勤扣錢
FROM employee,dept,payroll
WHERE employee.id = payroll.empId AND employee.deptId = dept.id;
7.利用觸發器實現插入請假信息時，審核人編號自動填入請假人所在部門的部門經理編號。觸發器名稱為：insert_ask_leave。
CREATE DEFINER = `root`@`localhost`
TRIGGER `insert_ask_leave` BEFORE
INSERT ON `ask_leave`
FOR EACH ROW SET new.auditId = (
SELECT manageId
FROM employee,dept
WHERE employee.deptid = dept.id AND new.empid = employee.id
);
)
