题目：如需要系统Java源码，请联系作者微信：xinglibao391
如需要系统Java源码，请联系作者微信：xinglibao391
如需要系统Java源码，请联系作者微信：xinglibao391
某工厂的物料管理系统
实现物料的分类管理;
实现部门门和员工信息管理;
实现物料的入库和领用 管理;
实现物料的转仓管理;
创建触发器，实现物料入库和领用时相应物料库存的自动更新;
创建触发器，实现转仓时转入仓库物料增加、转出仓库物料减少
创建存储过程统计各仓库各种物料的现存数量;
创建存储过程统计指定时间段内各种物料的入库数量和领用数量;
建立数据库相关表之间的参 照完整性约束;
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for fm_instore -- ---------------------------- DROp TABLE IF EXISTS `fm_instore`; CREATE TABLE `fm_instore` ( `instore_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '入库编号', `staff_id` int(11) NOT NULL COMMENT '员工编号', `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名', `material_id` int(11) NOT NULL COMMENT '物料编号', `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', `instore_num` int(11) NOT NULL COMMENT '入库数量', `warehouse_id` int(11) NOT NULL COMMENT '仓库编号', `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称', `instore_date` date NULL DEFAULT NULL COMMENT '入库时间', PRIMARY KEY (`instore_id`) USING BTREE, INDEX `fk9`(`staff_id`) USING BTREE, INDEX `fk10`(`material_id`) USING BTREE, INDEX `fk11`(`warehouse_id`) USING BTREE, CONSTRAINT `fk10` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk11` FOREIGN KEY (`warehouse_id`) REFERENCES `fm_warehouse` (`warehouse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk9` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_instore -- ---------------------------- INSERT INTO `fm_instore` VALUES (1, 1, '唐三藏', 2, '铁钉', 4200, 1, '一号仓库', '2022-06-05'); INSERT INTO `fm_instore` VALUES (2, 2, '孙悟空', 2, '铁钉', 3500, 3, '三号仓库', '2022-06-05'); -- ---------------------------- -- Table structure for fm_material -- ---------------------------- DROP TABLE IF EXISTS `fm_material`; CREATE TABLE `fm_material` ( `material_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '物料编号', `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', `material_spec` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料规格', `material_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料描述', `material_clazz` int(11) NULL DEFAULT NULL COMMENT '物料类别', PRIMARY KEY (`material_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_material -- ---------------------------- INSERT INTO `fm_material` VALUES (1, '门锁', '把', '', 1); INSERT INTO `fm_material` VALUES (2, '铁钉', '斤', '', 1); INSERT INTO `fm_material` VALUES (3, '螺丝', '斤', '', 1); INSERT INTO `fm_material` VALUES (4, '电线', '捆', '', 4); INSERT INTO `fm_material` VALUES (5, '插座', '个', '', 4); -- ---------------------------- -- Table structure for fm_matl_whse -- ---------------------------- DROP TABLE IF EXISTS `fm_matl_whse`; CREATE TABLE `fm_matl_whse` ( `material_id` int(11) NULL DEFAULT NULL COMMENT '物料编号', `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', `warehouse_id` int(11) NULL DEFAULT NULL COMMENT '仓库编号', `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称', `material_num` int(11) NULL DEFAULT NULL COMMENT '物料数量', INDEX `fk7`(`material_id`) USING BTREE, INDEX `fk8`(`warehouse_id`) USING BTREE, CONSTRAINT `fk7` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk8` FOREIGN KEY (`warehouse_id`) REFERENCES `fm_warehouse` (`warehouse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_matl_whse -- ---------------------------- INSERT INTO `fm_matl_whse` VALUES (1, '门锁', 1, '一号仓库', 2300); INSERT INTO `fm_matl_whse` VALUES (2, '铁钉', 1, '一号仓库', 9000); INSERT INTO `fm_matl_whse` VALUES (2, '铁钉', 3, '三号仓库', 6000); INSERT INTO `fm_matl_whse` VALUES (3, '螺丝', 1, '一号仓库', 4000); INSERT INTO `fm_matl_whse` VALUES (5, '插座', 2, '二号仓库', 3600); -- ---------------------------- -- Table structure for fm_outstore -- ---------------------------- DROP TABLE IF EXISTS `fm_outstore`; CREATE TABLE `fm_outstore` ( `outstore_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '领用编号', `staff_id` int(11) NOT NULL COMMENT '员工编号', `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名', `material_id` int(11) NOT NULL COMMENT '物料编号', `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', `outstore_num` int(11) NOT NULL COMMENT '领用数量', `warehouse_id` int(11) NOT NULL COMMENT '仓库编号', `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称', `outstore_date` date NULL DEFAULT NULL COMMENT '出库时间', PRIMARY KEY (`outstore_id`) USING BTREE, INDEX `fk5`(`staff_id`) USING BTREE, INDEX `fk6`(`material_id`) USING BTREE, CONSTRAINT `fk5` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk6` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_outstore -- ---------------------------- INSERT INTO `fm_outstore` VALUES (1, 1, '唐三藏', 2, '铁钉', 200, 1, '一号仓库', '2022-06-05'); -- ---------------------------- -- Table structure for fm_section -- ---------------------------- DROP TABLE IF EXISTS `fm_section`; CREATE TABLE `fm_section` ( `section_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门编号', `section_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称', `section_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门联系方式', `section_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址', PRIMARY KEY (`section_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_section -- ---------------------------- INSERT INTO `fm_section` VALUES (1, '人事部', '13921525889', '河北省邯郸市惊蛰机械厂办公楼305'); INSERT INTO `fm_section` VALUES (2, '财务部', '13842452526', '河北省邯郸市惊蛰机械厂办公楼203'); INSERT INTO `fm_section` VALUES (3, '仓库管理部', '13921533289', '河北省邯郸市惊蛰机械厂办公楼315'); INSERT INTO `fm_section` VALUES (4, '安全消防部', '18121511869', '河北省邯郸市惊蛰机械厂办公楼101'); -- ---------------------------- -- Table structure for fm_staff -- ---------------------------- DROP TABLE IF EXISTS `fm_staff`; CREATE TABLE `fm_staff` ( `staff_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号', `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名', `staff_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工联系方式', `staff_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工家庭住址', PRIMARY KEY (`staff_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_staff -- ---------------------------- INSERT INTO `fm_staff` VALUES (1, '唐三藏', '18965201179', '江苏省菏泽市西天区华府庄园253号'); INSERT INTO `fm_staff` VALUES (2, '孙悟空', '19632501179', '江苏省菏泽市西天区华府庄园234号'); INSERT INTO `fm_staff` VALUES (3, '朱八戒', '13921365889', '江苏省菏泽市西天区华府庄园263号'); INSERT INTO `fm_staff` VALUES (4, '朱高煦', '13845626361', '山东省闽南市海州区苍梧路59号'); INSERT INTO `fm_staff` VALUES (5, '朱高燧', '17511401171', '山东省闽南市海州区苍梧路59号'); -- ---------------------------- -- Table structure for fm_staff_section -- ---------------------------- DROP TABLE IF EXISTS `fm_staff_section`; CREATE TABLE `fm_staff_section` ( `staff_id` int(11) NOT NULL COMMENT '员工编号', `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名', `section_id` int(11) NOT NULL COMMENT '部门编号', `section_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称', INDEX `fk1`(`staff_id`) USING BTREE, INDEX `fk2`(`section_id`) USING BTREE, CONSTRAINT `fk1` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk2` FOREIGN KEY (`section_id`) REFERENCES `fm_section` (`section_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_staff_section -- ---------------------------- INSERT INTO `fm_staff_section` VALUES (1, '唐三藏', 1, '人事部'); INSERT INTO `fm_staff_section` VALUES (2, '孙悟空', 3, '仓库管理部'); INSERT INTO `fm_staff_section` VALUES (3, '朱八戒', 2, '财务部'); INSERT INTO `fm_staff_section` VALUES (4, '朱高煦', 4, '安全消防部'); INSERT INTO `fm_staff_section` VALUES (5, '朱高燧', 2, '财务部'); -- ---------------------------- -- Table structure for fm_transfer -- ---------------------------- DROP TABLE IF EXISTS `fm_transfer`; CREATE TABLE `fm_transfer` ( `transfer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '转仓编号', `staff_id` int(11) NOT NULL COMMENT '员工编号', `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名', `material_id` int(11) NOT NULL COMMENT '物料编号', `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称', `transfer_num` int(11) NOT NULL COMMENT '转仓数量', `in_warehouse_id` int(11) NOT NULL COMMENT '入仓编号', `in_warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '入仓名称', `out_warehouse_id` int(11) NOT NULL COMMENT '出仓编号', `out_warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出仓名称', `transfer_date` date NULL DEFAULT NULL COMMENT '转仓时间', PRIMARY KEY (`transfer_id`) USING BTREE, INDEX `fk3`(`staff_id`) USING BTREE, INDEX `fk4`(`material_id`) USING BTREE, CONSTRAINT `fk3` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk4` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_transfer -- ---------------------------- INSERT INTO `fm_transfer` VALUES (1, 1, '唐三藏', 1, '门锁', 300, 3, '三号仓库', 1, '一号仓库', '2022-06-05'); INSERT INTO `fm_transfer` VALUES (2, 2, '孙悟空', 1, '门锁', 400, 4, NULL, 1, '一号仓库', '2022-06-05'); -- ---------------------------- -- Table structure for fm_warehouse -- ---------------------------- DROP TABLE IF EXISTS `fm_warehouse`; CREATE TABLE `fm_warehouse` ( `warehouse_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '仓库编号', `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称', `warehouse_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库地址', `warehouse_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库联系方式', PRIMARY KEY (`warehouse_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of fm_warehouse -- ---------------------------- INSERT INTO `fm_warehouse` VALUES (1, '一号仓库', '河北省邯郸市惊蛰机械厂一号仓库', '13921856889'); INSERT INTO `fm_warehouse` VALUES (2, '二号仓库', '河北省邯郸市惊蛰机械厂二号仓库', '17517542179'); INSERT INTO `fm_warehouse` VALUES (3, '三号仓库', '河北省邯郸市惊蛰机械厂三号仓库', '13845262548'); -- ---------------------------- -- Triggers structure for table fm_instore -- ---------------------------- DROP TRIGGER IF EXISTS `trigger3`; delimiter ;; CREATE TRIGGER `trigger3` BEFORE INSERT ON `fm_instore` FOR EACH ROW begin set new.instore_date = now(); set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id); set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id); set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id); update fm_matl_whse set material_num = material_num + new.instore_num where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id; end ;; delimiter ; -- ---------------------------- -- Triggers structure for table fm_matl_whse -- ---------------------------- DROP TRIGGER IF EXISTS `trigger4`; delimiter ;; CREATE TRIGGER `trigger4` BEFORE INSERT ON `fm_matl_whse` FOR EACH ROW begin set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id); set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id); end ;; delimiter ; -- ---------------------------- -- Triggers structure for table fm_outstore -- ---------------------------- DROp TRIGGER IF EXISTS `trigger5`; delimiter ;; CREATE TRIGGER `trigger5` BEFORE INSERT ON `fm_outstore` FOR EACH ROW begin set new.outstore_date = now(); set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id); set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id); set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id); if (select material_num from fm_matl_whse where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id) >= new.outstore_num then update fm_matl_whse set material_num = material_num - new.outstore_num where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id; end if; end ;; delimiter ; -- ---------------------------- -- Triggers structure for table fm_staff_section -- ---------------------------- DROP TRIGGER IF EXISTS `trigger1`; delimiter ;; CREATE TRIGGER `trigger1` BEFORE INSERT ON `fm_staff_section` FOR EACH ROW begin set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id); set new.section_name = (select fm_section.section_name from fm_section where fm_section.section_id = new.section_id); end ;; delimiter ; -- ---------------------------- -- Triggers structure for table fm_transfer -- ---------------------------- DROp TRIGGER IF EXISTS `trigger6`; delimiter ;; CREATE TRIGGER `trigger6` BEFORE INSERT ON `fm_transfer` FOR EACH ROW begin set new.transfer_date = now(); set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id); set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id); set new.in_warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.in_warehouse_id); set new.out_warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.out_warehouse_id); if (select material_num from fm_matl_whse where fm_matl_whse.warehouse_id = new.out_warehouse_id and fm_matl_whse.material_id = new.material_id) >= new.transfer_num then update fm_matl_whse set material_num = material_num + new.transfer_num where fm_matl_whse.warehouse_id = new.in_warehouse_id and fm_matl_whse.material_id = new.material_id; update fm_matl_whse set material_num = material_num - new.transfer_num where fm_matl_whse.warehouse_id = new.out_warehouse_id and fm_matl_whse.material_id = new.material_id; end if; end ;; delimiter ; SET FOREIGN_KEY_CHECKS = 1;课程设计心得：
理论联系实际才能做好一件事，学习一门课程同样是这样。通过此次课程设计项目实践，受益匪浅，从中学到了许多新知识，这些知识是在课堂中不能学到或者说很难学到的。并且对数据分析这一门课程有了更深一步的理解。在做课程设计项目过程中，可以把课堂上所学的理论知识，和实践联系起来，在所要开发的项目中渐渐学会了融会贯通。
这次课程设计项目，在这次项目中，我们完全投入到了数据分析的世界里。结束后明白了理论和实践要想充分地结合，需要非常扎实的基本功。这就说明学好基础知识是理论付诸实践的前提。在开发过程中我们学到了很多，希望在以后能充分利用实习的机会充实自己，用所学的理论知识充分去实践，在实践中又要努力去巩固理论知识。只有这样，才能把一门课程甚至一门学科学精、学透。
当然，课程设计项目中仍存在许多尚待改进之处，这就需要我们学习更多的知识，进行更深研究。
