存储过程、触发器和函数

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

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

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

   • 业务逻辑封装：

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

     • 性能优化：

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

       • 安全性：

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

         • 代码重用和维护：

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

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.编写存储过程实现插入员工表：参数为：

存储过程名称为：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
);