数据库的存储过程、函数与触发器

2024-07-17 10:04| 来源: 网络整理| 查看: 265

在这里插入图片描述

使用下面的场景来引入

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

员工编号idint姓名userNamevarchar(225)出生日期birthDatedate身份证号idCardvarchar(225)登录名称loginNamevarchar(225)登录密码passwordvarchar(225)手机号mobilevarchar(225)电子邮件emailvarchar(225)部门编号deptIdint员工级别levelint员工头像avatarblob备注remarktext

存储过程名称为：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','[email protected]',3,1,NULL,'新员工'); call insert_employee(2,'小橙','2002-02-14','411423200203241511','xiaocheng','123','15238790677','[email protected]',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建立索引。

CREATE INDEX index_userName ON employee(username);

6.建立员工部门工资视图（包含员工名称，部门名称，基本工资,应发工资,奖金,缺勤扣钱）

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.利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。

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

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章