— 创建测试表
CREATE TABLE fgedu_business_data (
id INT PRIMARY KEY,
name TEXT,
value NUMERIC,
processed BOOLEAN DEFAULT false,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_business_data (id, name, value) VALUES
(1, ‘Product A’, 100.0),
(2, ‘Product B’, 200.0),
(3, ‘Product C’, 300.0),
(4, ‘Product D’, 400.0),
(5, ‘Product E’, 500.0);

— 创建自定义函数
CREATE OR REPLACE FUNCTION fgedu_calculate_stats()
RETURNS TABLE(
total_count INT,
total_value NUMERIC,
avg_value NUMERIC,
max_value NUMERIC,
min_value NUMERIC
)
AS $$
BEGIN
RETURN QUERY
SELECT
COUNT(*)::INT AS total_count,
SUM(value) AS total_value,
AVG(value) AS avg_value,
MAX(value) AS max_value,
MIN(value) AS min_value
FROM fgedu_business_data;
END;
$$ LANGUAGE plpgsql;

— 测试函数
SELECT * FROM fgedu_calculate_stats();

— 创建自定义类型
CREATE TYPE fgedu_customer AS (
id INT,
name TEXT,
email TEXT,
phone TEXT,
address TEXT
);

— 创建函数使用自定义类型
CREATE OR REPLACE FUNCTION fgedu_create_customer(
p_id INT,
p_name TEXT,
p_email TEXT,
p_phone TEXT,
p_address TEXT
)
RETURNS fgedu_customer
AS $$
DECLARE
v_customer fgedu_customer;
BEGIN
v_customer := (p_id, p_name, p_email, p_phone, p_address);
RETURN v_customer;
END;
$$ LANGUAGE plpgsql;

— 测试自定义类型
SELECT * FROM fgedu_create_customer(
1,
‘John Doe’,
‘john.doe@fgedu.net.cn’,
‘123-456-7890’,
‘123 Main St’
);

— 创建存储过程处理订单
CREATE OR REPLACE PROCEDURE fgedu_process_order(
p_order_id INT,
p_customer_id INT,
p_amount NUMERIC
)
AS $$
BEGIN
— 开始事务
BEGIN
— 插入订单记录
INSERT INTO fgedu_orders (order_id, customer_id, amount, status)
VALUES (p_order_id, p_customer_id, p_amount, ‘PENDING’);

— 扣减库存
UPDATE fgedu_inventory
SET quantity = quantity – 1
WHERE product_id IN (SELECT product_id FROM fgedu_order_items WHERE order_id = p_order_id);

— 更新订单状态
UPDATE fgedu_orders
SET status = ‘COMPLETED’
WHERE order_id = p_order_id;

— 提交事务
COMMIT;
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
ROLLBACK;
— 记录错误
RAISE EXCEPTION ‘Error processing order: %’, SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 创建测试表
CREATE TABLE fgedu_orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount NUMERIC,
status TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_order_items (
item_id INT PRIMARY KEY,
order_id INT REFERENCES fgedu_orders(order_id),
product_id INT,
quantity INT,
price NUMERIC
);

CREATE TABLE fgedu_inventory (
product_id INT PRIMARY KEY,
quantity INT,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_inventory (product_id, quantity) VALUES
(1, 10),
(2, 20),
(3, 30);

INSERT INTO fgedu_order_items (item_id, order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1, 100.0),
(2, 1, 2, 2, 200.0);

— 测试存储过程
CALL fgedu_process_order(1, 1, 500.0);

— 验证结果
SELECT * FROM fgedu_orders WHERE order_id = 1;
SELECT * FROM fgedu_inventory WHERE product_id IN (1, 2);