存储过程、触发器和函数

存储过程、触发器和函数在数据库中具有重要的作用，它们可以带来以下几个方面的重要性：

1. 数据一致性和完整性：

   • 触发器和存储过程可以用于实现数据一致性和完整性约束。通过在数据库操作（如插入、更新、删除）发生时自动执行相关逻辑，可以确保数据的正确性，减少错误和数据异常。

2. 业务逻辑封装：

   • 存储过程和函数可以封装复杂的业务逻辑，使其在数据库层面执行。这样可以减少应用程序的负担，提高系统性能，并且可以确保在数据库操作中应用相同的业务规则。

3. 性能优化：

   • 存储过程和函数在数据库中预编译，可以提高执行效率。通过减少与数据库服务器之间的通信次数和数据传输量，可以降低系统的负载，提高响应速度。

4. 安全性：

   • 存储过程和函数可以实现数据库访问的安全性控制。通过限制用户对数据库的直接访问，并通过存储过程和函数来执行特定操作，可以有效防止恶意操作和未经授权的访问。

5. 代码重用和维护：

   • 存储过程和函数可以被多个应用程序或者查询重复使用，提高了代码的重用性。当需要修改业务逻辑时，只需修改存储过程或函数的代码，而不需要修改所有调用它们的代码。

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)
电子邮件	email	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
)
BEGIN
	DECLARE 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
);

问题查询
https://chat18.aichatos.xyz/#/chat/1710465294397