devbox
我家小花儿
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL期末答辩—仓库管理系统

作者：我家小花儿 | 2024-07-05 02:42:34

赞

踩

MySQL期末答辩—仓库管理系统
仓库管理系统：仓库管理系统是一种基于互联网对实际仓库的管理平台，旨在提供一个方便、快捷、安全的存取货物和查询商品信息平台。该系统通过在线用户登录查询，可以线上操作线下具体出/入库操作、查询仓库商品信息、提高仓库运作效率，优化仓库使用流程等功能，实现了用户在网上对仓库操作的全流程。

ER图和数据库模型图

DDL语句

  1. CREATE TABLE products (-- 产品表  
  2.     product_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',  
  3.     product_name VARCHAR(255) NOT NULL COMMENT '商品名称',  
  4.     product_category VARCHAR(100) NOT NULL COMMENT '商品类别',  
  5.     specification VARCHAR(255) COMMENT '规格',  
  6.     unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',  
  7.     stock_quantity INT NOT NULL DEFAULT 0 COMMENT '库存数量'  
  8. );
  9. CREATE TABLE warehouses (-- 仓库表  
  10.     warehouse_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '库房ID',  
  11.     warehouse_name VARCHAR(255) NOT NULL COMMENT '库房名称',  
  12.     location VARCHAR(255) NOT NULL COMMENT '位置',  
  13.     area DECIMAL(10, 2) NOT NULL COMMENT '面积'  
  14. ); CREATE TABLE suppliers (-- 供应商表   
  15.     supplier_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '供应商ID',  
  16.     supplier_name VARCHAR(255) NOT NULL COMMENT '供应商名称',  
  17.     contact_person VARCHAR(100) COMMENT '联系人',  
  18.     phone_number VARCHAR(20) COMMENT '联系电话',  
  19.     email VARCHAR(100) COMMENT '电子邮箱',  
  20.     address VARCHAR(255) COMMENT '地址'  
  21. );CREATE TABLE orders (-- 订单表   
  22.     order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',  
  23.     customer_name VARCHAR(255) NOT NULL COMMENT '客户名称',  
  24.     order_date DATE NOT NULL COMMENT '订单日期',  
  25.     total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',  
  26.     status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL COMMENT '订单状态'  
  27. );
  28. CREATE TABLE employees (  
  29.   employee_id int(11) NOT NULL AUTO_INCREMENT COMMENT '员工序号',  
  30.   employee_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',  
  31.   department varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属部门',  
  32.   inventory_responsibility varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工职责',  
  33.   inventory_count int(11) NULL DEFAULT NULL COMMENT '记录员工的库存数量',  
  34.   employee_phone VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工电话',  
  35.   PRIMARY KEY (employee_id) USING BTREE  
  36. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  37. CREATE TABLE inventory_transactions (-- 库存表  
  38.     transaction_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '出入库记录ID',  
  39.     product_id INT,  
  40.     warehouse_id INT,  
  41.     employee_id INT,  
  42.     transaction_type ENUM('In', 'Out') NOT NULL COMMENT '出入库类型',  
  43.     quantity INT NOT NULL COMMENT '数量',  
  44.     transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '交易时间',  
  45.     FOREIGN KEY (product_id) REFERENCES products(product_id),  
  46.     FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id),  
  47.     FOREIGN KEY (employee_id) REFERENCES employees(employee_id)  );DROP TABLE IF EXISTS `users`;-- 用户表
  48. CREATE TABLE `users` (
  49.   `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  50.   `username` varchar(255) DEFAULT NULL,
  51.   `email` varchar(100) DEFAULT NULL COMMENT '电子邮箱',
  52.   `phone_number` varchar(20) DEFAULT NULL COMMENT '联系电话',
  53.   `role` enum('Admin','User') NOT NULL COMMENT '角色',
  54.   PRIMARY KEY (`user_id`),
  55.   UNIQUE KEY `email` (`email`),
  56.   UNIQUE KEY `username` (`username`(50))
  57. ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
  58. CREATE TABLE return_orders (-- 退货单表  
  59.     return_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '退货单ID',  
  60.     order_id INT NOT NULL COMMENT '原始订单ID',  
  61.     customer_name VARCHAR(255) NOT NULL COMMENT '客户名称',  
  62.     return_date DATE NOT NULL COMMENT '退货日期',  
  63.     status ENUM('Pending', 'Refunded', 'Rejected') NOT NULL COMMENT '退货状态',  
  64.     FOREIGN KEY (order_id) REFERENCES orders(order_id)  );
  65. CREATE TABLE return_details (-- 退货明细表  
  66.     detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '退货明细ID',  
  67.     return_id INT NOT NULL COMMENT '退货单ID',  
  68.     product_id INT NOT NULL COMMENT '商品ID',  
  69.     quantity INT NOT NULL COMMENT '退货数量',  
  70.     FOREIGN KEY (return_id) REFERENCES return_orders(return_id),  
  71.     FOREIGN KEY (product_id) REFERENCES products(product_id)  );
  72. CREATE TABLE order_details (-- 订单明细表  
  73.     detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单明细ID',  
  74.     order_id INT NOT NULL,  
  75.     product_id INT NOT NULL,  
  76.     quantity INT NOT NULL COMMENT '数量',  
  77.     unit_price DECIMAL(10, 2) NOT NULL COMMENT '单价',  
  78.     FOREIGN KEY (order_id) REFERENCES orders(order_id),  
  79.     FOREIGN KEY (product_id) REFERENCES products(product_id)  );
  80. CREATE TABLE purchase_orders (-- 采购单表  
  81.     po_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '采购单ID',  
  82.     supplier_id INT NOT NULL,  
  83.     purchase_date DATE NOT NULL COMMENT '采购日期',  
  84.     total_amount DECIMAL(10, 2) NOT NULL COMMENT '采购总金额',  
  85.     status ENUM('Pending', 'Received', 'Cancelled') NOT NULL COMMENT '采购单状态',  
  86.     FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)  );
  87. CREATE TABLE inventory (-- 库存表  
  88.     inventory_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '库存记录ID',  
  89.     product_id INT NOT NULL COMMENT '商品ID',  
  90.     warehouse_id INT NOT NULL COMMENT '库房ID',  
  91.     quantity INT NOT NULL DEFAULT 0 COMMENT '库存数量',  
  92.     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',  
  93.     FOREIGN KEY (product_id) REFERENCES products(product_id),  
  94.     FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)  );
  95.  
  96.  
  97.  
  98.

DML语句

  1.  INSERT INTO products (product_name, product_category, specification, unit_price, stock_quantity)   
  2. VALUES ('iPhone 13', '电子产品', '6.1英寸, 5G', 999.99, 100);  
  3. INSERT INTO products (product_name, product_category, specification, unit_price, stock_quantity)   
  4. VALUES ('MacBook Pro', '电子产品', '16英寸, M1芯片', 2499.99, 50);    
  5. INSERT INTO products (product_name, product_category, specification, unit_price, stock_quantity)   
  6. VALUES ('Nike Air Max', '运动鞋', '男款, 42码', 149.99, 200);   
  7. INSERT INTO products (product_name, product_category, specification, unit_price, stock_quantity)   
  8. VALUES ('Adidas Ultra Boost', '运动鞋', '女款, 38码', 129.99, 150);    
  9. INSERT INTO products (product_name, product_category, specification, unit_price, stock_quantity)   
  10. VALUES ('Sony WH-1000XM4', '音频设备', '无线蓝牙耳机, 降噪', 349.99, 80);   
  11. INSERT INTO warehouses (warehouse_name, location, area)   
  12. VALUES ('一号仓库', '北京市朝阳区', 1000.50);   
  13. INSERT INTO warehouses (warehouse_name, location, area)   
  14. VALUES ('二号仓库', '上海市浦东区', 1500.25);   
  15. INSERT INTO warehouses (warehouse_name, location, area)   
  16. VALUES ('三号仓库', '广州市天河区', 800.75);   
  17. INSERT INTO warehouses (warehouse_name, location, area)   
  18. VALUES ('四号仓库', '深圳市南山区', 1200.00);   
  19. INSERT INTO warehouses (warehouse_name, location, area)   
  20. VALUES ('五号仓库', '杭州市西湖区', 950.30);  
  21. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  22. VALUES ('张三', '销售部', '库存管理', 500);   
  23. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  24. VALUES ('李四', '物流部', '库存盘点', 300);   
  25. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  26. VALUES ('王五', '生产部', '库存补货', 800);  
  27. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  28. VALUES ('赵六', '财务部', '库存成本核算', NULL);   
  29. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  30. VALUES ('孙七', '技术部', '库存系统维护', NULL); 
  31. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  32. VALUES ('周八', '销售部', '库存管理', 650);  
  33. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  34. VALUES ('吴九', '采购部', '库存采购', 400);   
  35. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  36. VALUES ('郑十', '物流部', '库存出库', 250);  
  37. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  38. VALUES ('陈十一', '生产部', '库存监控', 900);   
  39. INSERT INTO employees (employee_name, department, inventory_responsibility, inventory_count)   
  40. VALUES ('卫十二', '行政部', NULL, NULL);  
  41. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  42. VALUES (1, 1, 1, 'In', 10); -- 假设产品ID为1，仓库ID为1，员工ID为1，入库10个产品   
  43. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  44. VALUES (2, 1, 2, 'Out', 5); -- 假设产品ID为2，仓库ID为1，员工ID为2，出库5个产品   
  45. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  46. VALUES (1, 2, 3, 'In', 8); -- 假设产品ID为1，仓库ID为2，员工ID为3，入库8个产品 
  47. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  48. VALUES (3, 2, 4, 'Out', 3); -- 假设产品ID为3，仓库ID为2，员工ID为4，出库3个产品    
  49. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  50. VALUES (2, 3, 5, 'In', 15); -- 假设产品ID为2，仓库ID为3，员工ID为5，入库15个产品   
  51. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  52. VALUES (1, 3, 6, 'Out', 7); -- 假设产品ID为1，仓库ID为3，员工ID为6，出库7个产品  
  53. INSERT INTO inventory_transactions (product_id, warehouse_id, employee_id, transaction_type, quantity)   
  54. VALUES (4, 1, 7, 'In', 20); -- 假设产品ID为4，仓库ID为1，员工ID为7，入库20个产品
  55. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  56. VALUES ('供应商A', '张三', '1234567890', 'supplierA@example.com', '北京市朝阳区');   
  57. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  58. VALUES ('供应商B', '李四', '0987654321', 'supplierB@example.com', '上海市黄浦区');   
  59. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  60. VALUES ('供应商C', '王五', '1112223333', 'supplierC@example.com', '广州市天河区');   
  61. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  62. VALUES ('供应商D', '赵六', '2223334444', 'supplierD@example.com', '深圳市南山区');  
  63. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  64. VALUES ('供应商E', '孙七', '3334445555', 'supplierE@example.com', '杭州市西湖区');   
  65. INSERT INTO suppliers (supplier_name, contact_person, phone_number, email, address)   
  66. VALUES ('供应商F', '周八', '4445556666', 'supplierF@example.com', '成都市武侯区');
  67. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  68. VALUES ('王五', '2023-01-03', 220.75, 'Delivered');   
  69. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  70. VALUES ('赵六', '2023-01-04', 75.25, 'Pending');   
  71. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  72. VALUES ('孙七', '2023-01-05', 300.00, 'Shipped’);   
  73. INSERT INTO orders (customer_name, order_d
  74. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  75. VALUES ('张三', '2023-01-01', 100.00, 'Pending');   
  76. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  77. VALUES ('李四', '2023-01-02', 150.50, 'Shipped');  
  78. ate, total_amount, status)   
  79. VALUES ('周八', '2023-01-06', 125.50, 'Delivered');    
  80. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  81. VALUES ('吴九', '2023-01-07', 80.00, 'Cancelled');    
  82. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  83. VALUES ('陈十', '2023-01-08', 180.25, 'Pending’); 
  84. INSERT INTO orders (customer_name, order_date, total_amount, status)   
  85. VALUES ('郑十一', '2023-01-09', 250.00, 'Shipped');  
  86. INSERT INTO order_details (order_id, product_id, quantity, unit_price)   
  87. VALUES (6, 1001, 1, 50.00);
  88. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  89. VALUES (1, '2023-01-01', 500.00, 'Pending');  
  90. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  91. VALUES (2, '2023-01-05', 700.50, 'Pending');  
  92. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  93. VALUES (1, '2023-01-10', 300.25, 'Received');   
  94. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  95. VALUES (3, '2023-01-15', 800.00, 'Pending');  
  96. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  97. VALUES (2, '2023-01-20', 650.75, 'Received');  
  98. INSERT INTO purchase_orders (supplier_id, purchase_date, total_amount, status)   
  99. VALUES (1, '2023-01-25', 400.00, 'Cancelled’);  
  100. INSERT INTO inventory (product_id, warehouse_id, quantity)   
  101. VALUES (1001, 1, 10);  
  102. INSERT INTO inventory (product_id, warehouse_id, quantity)   
  103. VALUES (1002, 2, 5);    
  104. INSERT INTO inventory (product_id, warehouse_id, quantity)   
  105. VALUES (1003, 1, 20);  
  106. INSERT INTO inventory (product_id, warehouse_id, quantity)   
  107. VALUES (1004, 3, 8);  
  108. INSERT INTO inventory (product_id, warehouse_id, quantity)   
  109. VALUES (1001, 2, 15);
  110. INSERT INTO users (username, password, email, phone_number, role)   
  111. VALUES ('adminuser1', 'encrypted_password1', 'adminuser1@example.com', '1234567890', 'Admin');  
  112. INSERT INTO users (username, password, email, phone_number, role)   
  113. VALUES ('standarduser1', 'encrypted_password2', 'standarduser1@example.com', '0987654321', 'User');   
  114. INSERT INTO users (username, password, email, phone_number, role)   
  115. VALUES ('alice', 'encrypted_password3', 'alice@alice.com', '5551234567', 'User');  
  116. INSERT INTO users (username, password, email, phone_number, role)   
  117. VALUES ('bob', 'encrypted_password4', 'bob@bob.com', '5557654321', 'User');    
  118. INSERT INTO users (username, password, email, phone_number, role)   
  119. VALUES ('charlie', 'encrypted_password5', 'charlie@charlie.com', '5556543210', 'Admin');   
  120. INSERT INTO users (username, password, email, phone_number, role)   
  121. VALUES ('david', 'encrypted_password6', 'david@david.com', '5551112222', 'User');   
  122. INSERT INTO users (username, password, email, phone_number, role)   
  123. VALUES ('eve', 'encrypted_password7', 'eve@example.net', '5559876543', 'Admin');   
  124. INSERT INTO users (username, password, email, phone_number, role)   
  125. VALUES ('frank', 'encrypted_password8', 'frank@example.org', '5553334444', 'User');    
  126. INSERT INTO users (username, password, email, phone_number, role)   
  127. VALUES ('george', 'encrypted_password9', 'george@george.com', '5558887777', 'User');   
  128. INSERT INTO users (username, password, email, phone_number, role)   
  129. VALUES ('helen', 'encrypted_password10', 'helen@helen.com', '5555678901', 'Admin');    
  130. INSERT INTO users (username, password, email, phone_number, role)   
  131. VALUES ('ian', 'encrypted_password11', 'ian@ian.com', '5552345678', 'User');  
  132. INSERT INTO users (username, password, email, phone_number, role)   
  133. VALUES ('jane', 'encrypted_password12', 'jane@jane.com', '5559012345', 'User’);
  134. INSERT INTO return_orders (order_id, customer_name, return_date, status)   
  135. VALUES (1001, 'John Doe', '2023-01-01', 'Pending');  
  136. INSERT INTO return_orders (order_id, customer_name, return_date, status)   
  137. VALUES (1002, 'Jane Smith', '2023-01-05', 'Refunded');   
  138. INSERT INTO return_orders (order_id, customer_name, return_date, status)   
  139. VALUES (1003, 'Bob Johnson', '2023-01-10', 'Rejected');  
  140. INSERT INTO return_orders (order_id, customer_name, return_date, status)   
  141. VALUES (1004, 'Alice Brown', '2023-01-15', 'Pending');   
  142. INSERT INTO return_orders (order_id, customer_name, return_date, status)   
  143. VALUES (1005, 'Mike Williams', '2023-01-20', 'Refunded');
  144. INSERT INTO return_details (return_id, product_id, quantity)   
  145. VALUES (1, 1001, 2); -- 假设退货单ID为1，商品ID为1001，退货数量为2  
  146. INSERT INTO return_details (return_id, product_id, quantity)   
  147. VALUES (1, 1002, 1); -- 假设退货单ID为1，商品ID为1002，退货数量为1   
  148. INSERT INTO return_details (return_id, product_id, quantity)   
  149. VALUES (2, 1001, 3); -- 假设退货单ID为2，商品ID为1001，退货数量为3  
  150. INSERT INTO return_details (return_id, product_id, quantity)   
  151. VALUES (2, 1003, 1); -- 假设退货单ID为2，商品ID为1003，退货数量为1   
  152. INSERT INTO return_details (return_id, product_id, quantity)   
  153. VALUES (3, 1004, 2); -- 假设退货单ID为3，商品ID为1004，退货数量为2

查询

  1. --基础查询：
  2. SELECT username AS '姓名', phone_number AS '手机号' FROM users;
  3. --模糊查询：
  4. CREATE INDEX idx_product_name ON products(product_name);
  5. --统计用户订单信息，查询所有用户的下单数量，并进行倒序排列
  6. SELECT customer_name, COUNT(order_id) AS order_count FROM orders GROUP BY customer_name ORDER BY order_count DESC;
  7. --查询用户的基本信息使用多表联合查询
  8. SELECT user_id, username, email, phone_number, role FROM users;
  9. --查看订单中下单最多的产品对应的类别
  10. SELECT p.product_category, SUM(od.quantity) AS total_quantity FROM products p JOIN order_details od ON p.product_id = od.product_id GROUP BY p.product_category ORDER BY total_quantity DESC LIMIT 1;
  11. --查询下单总金额最多的用户
  12. SELECT u.*  
  13. FROM users u  
  14. JOIN (  
  15.     SELECT customer_name, SUM(total_amount) AS total_spent  
  16.     FROM orders  
  17.     GROUP BY customer_name  
  18.     ORDER BY total_spent DESC  
  19.     LIMIT 1  
  20. ) AS top_spender ON u.username = top_spender.customer_name;

Trigger触发器

  1. -- 触发器：当新增用户时，在inventory_transactions表中为该用户创建一个初始的库存记录（假设）  
  2. DELIMITER //  
  3. CREATE TRIGGER after_user_insert  
  4. AFTER INSERT ON users  
  5. FOR EACH ROW  
  6. BEGIN  
  7.     INSERT INTO inventory_transactions (employee_id, transaction_type, quantity, transaction_date)  
  8.     VALUES (NEW.user_id, 'In', 0, NOW());  -- 这里假设员工ID和用户ID是相同的，并且只是作为一个演示，数量为0  
  9. END;  
  10. //  
  11. DELIMITER ;
  12. --产品表修改语句添加触发器
  13. DELIMITER //  
  14. CREATE TRIGGER before_product_update  
  15. BEFORE UPDATE ON products  
  16. FOR EACH ROW  
  17. BEGIN  
  18.     IF NEW.unit_price < OLD.unit_price * 0.9 OR NEW.unit_price > OLD.unit_price * 1.1 THEN  
  19.         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '商品售价上下浮动不能超过10%';  
  20.     END IF;  
  21. END;  
  22. //  
  23. DELIMITER ;
  24. -- 触发器：当删除订单时，先删除订单详情表中的相关记录  
  25. DELIMITER //  
  26. CREATE TRIGGER before_order_delete  
  27. BEFORE DELETE ON orders  
  28. FOR EACH ROW  
  29. BEGIN  
  30.     DELETE FROM order_details WHERE order_id = OLD.order_id;  
  31. END;  
  32. //  
  33. DELIMITER ;

存储过程

  1. --查询产品库存是否符合所需产品数量，并统计订单总金额
  2. DELIMITER //  
  3.   CREATE PROCEDURE create_order_infos(  
  4.     IN p_customer_name VARCHAR(255),  
  5.     IN p_product_id INT,  
  6.     IN p_quantity INT,  
  7.     IN p_warehouse_id INT  )  
  8. BEGIN  
  9.     -- 声明变量  
  10.     DECLARE v_order_id INT;  
  11.     DECLARE v_product_stock INT;  
  12.     DECLARE v_remaining_stock INT;  
  13.    -- 开始事务  
  14.     START TRANSACTION;  
  15.   -- 1. 插入订单记录到orders表  
  16.     INSERT INTO orders (customer_name, order_date, total_amount, status)  
  17.     VALUES (p_customer_name, NOW(), 0, 'Pending');  
  18.   -- 获取新订单的ID  
  19.     SET v_order_id = LAST_INSERT_ID();  
  20.   -- 2. 检查产品库存是否足够  
  21.     SELECT quantity INTO v_product_stock FROM inventory WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id FOR UPDATE;  
  22.   IF v_product_stock >= p_quantity THEN  
  23.         -- 3. 更新库存数量  
  24.         SET v_remaining_stock = v_product_stock - p_quantity;  
  25.         UPDATE inventory SET quantity = v_remaining_stock WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;  
  26.         -- 假设这里还需要更新产品表的库存（不常见，但为了示例）  
  27.         -- UPDATE products SET stock_quantity = stock_quantity - p_quantity WHERE product_id = p_product_id;  
  28.         -- 4. 插入订单明细到order_details表  
  29.         INSERT INTO order_details (order_id, product_id, quantity, unit_price)  
  30.         SELECT v_order_id, p.product_id, p_quantity, p.unit_price  
  31.         FROM products p  
  32.         WHERE p.product_id = p_product_id;  
  33.         -- 5. 计算订单总金额并更新orders表（假设单价不会变）  
  34.         UPDATE orders o  
  35.         JOIN (  
  36.             SELECT order_id, SUM(quantity * unit_price) AS total_amount  
  37.             FROM order_details  
  38.             WHERE order_id = v_order_id  
  39.         ) od ON o.order_id = od.order_id  
  40.         SET o.total_amount = od.total_amount  
  41.         WHERE o.order_id = v_order_id;  
  42. -- 提交事务  
  43.         COMMIT;  
  44.         SELECT 'Order created successfully.' AS message;  
  45.     ELSE  
  46.         -- 库存不足，回滚事务  
  47.         ROLLBACK;  
  48.         SELECT 'Insufficient stock for the order.' AS message;  
  49.     END IF;  
  50. END //  
  51.   
  52. DELIMITER ;
  53.

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/788952
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号