编写-无参存储过程用于查询每个客户购买产品的情况(包括客户编号、产品编号、喜户名称、产品名称、价格、购买日期、购买数里),然后调用该存储过程。
时间: 2023-07-10 08:06:51 浏览: 107
假设有以下两张表:
客户表 Customer:
| customer_id | customer_name |
| ----------- | ------------- |
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
产品表 Product:
| product_id | product_name | price |
| ---------- | ------------ | ----- |
| 1 | iPhone | 999 |
| 2 | iPad | 799 |
| 3 | MacBook | 1299 |
购买记录表 Purchase:
| purchase_id | customer_id | product_id | purchase_date | quantity |
| ----------- | -----------| ----------| --------------| ---------|
| 1 | 1 | 1 | 2021-01-01 | 2 |
| 2 | 1 | 2 | 2021-02-01 | 1 |
| 3 | 2 | 1 | 2021-03-01 | 1 |
| 4 | 3 | 3 | 2021-04-01 | 3 |
则可以编写以下无参存储过程来查询每个客户购买产品的情况:
```mysql
DELIMITER //
CREATE PROCEDURE customer_purchase_info()
BEGIN
SELECT Purchase.customer_id, Product.product_id, Customer.customer_name, Product.product_name,
Product.price, Purchase.purchase_date, Purchase.quantity
FROM Purchase
INNER JOIN Customer ON Purchase.customer_id = Customer.customer_id
INNER JOIN Product ON Purchase.product_id = Product.product_id
ORDER BY Purchase.customer_id, Purchase.purchase_date;
END //
DELIMITER ;
```
该存储过程使用了 INNER JOIN 连接三张表,然后按照客户编号和购买日期排序。调用该存储过程可以使用以下语句:
```mysql
CALL customer_purchase_info();
```
执行后会返回每个客户购买产品的情况,例如:
| customer_id | product_id | customer_name | product_name | price | purchase_date | quantity |
| -----------| ----------| --------------| ------------ | ----- | -------------| ---------|
| 1 | 1 | Alice | iPhone | 999 | 2021-01-01 | 2 |
| 1 | 2 | Alice | iPad | 799 | 2021-02-01 | 1 |
| 2 | 1 | Bob | iPhone | 999 | 2021-03-01 | 1 |
| 3 | 3 | Charlie | MacBook | 1299 | 2021-04-01 | 3 |