CREATE TABLE IF NOT EXISTS fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(15,2) NOT NULL,
subtotal NUMERIC(15,2) GENERATED ALWAYS AS (quantity * price) STORED
);

— 创建订单处理存储过程
CREATE OR REPLACE PROCEDURE fgedu_create_order(
p_customer_id INTEGER,
p_items JSONB,
OUT p_order_id INTEGER
)
AS $$
DECLARE
v_order_no VARCHAR(50);
v_item JSONB;
BEGIN
— 生成订单号
v_order_no := ‘ORD’ || TO_CHAR(NOW(), ‘YYYYMMDDHH24MISS’) || LPAD(p_customer_id::TEXT, 6, ‘0’);

— 创建订单
INSERT INTO fgedu_orders(order_no, customer_id, status)
VALUES(v_order_no, p_customer_id, ‘created’)
RETURNING id INTO p_order_id;

— 插入订单明细
FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
LOOP
INSERT INTO fgedu_order_items(order_id, product_id, quantity, price)
VALUES(
p_order_id,
(v_item->>’product_id’)::INTEGER,
(v_item->>’quantity’)::INTEGER,
(v_item->>’price’)::NUMERIC
);
END LOOP;

— 更新订单总金额
UPDATE fgedu_orders o
SET total_amount = (
SELECT SUM(subtotal) FROM fgedu_order_items WHERE order_id = o.id
)
WHERE id = p_order_id;

COMMIT;
RAISE NOTICE ‘订单创建成功，订单号:%，订单ID:%’, v_order_no, p_order_id;
END;
$$ LANGUAGE plpgsql;

IF v_stock < v_item.quantity THEN RAISE EXCEPTION '产品%库存不足，当前库存:%，需要:%', v_item.product_name, v_stock, v_item.quantity; END IF; -- 扣减库存 UPDATE fgedu_inventory SET stock = stock - v_item.quantity, update_time = NOW() WHERE product_id = v_item.product_id; RAISE NOTICE '产品%库存扣减成功，扣减数量:%', v_item.product_name, v_item.quantity; END LOOP; -- 更新订单状态 UPDATE fgedu_orders SET status = 'confirmed' WHERE id = p_order_id; COMMIT; END; $$ LANGUAGE plpgsql;

— 输出结果
NOTICE: 订单创建成功，订单号:ORD202604071530251001，订单ID:1
CALL

— 步骤2：扣减库存
CALL fgedu_deduct_inventory(v_order_id);

— 步骤3：生成支付记录
INSERT INTO fgedu_payments(order_id, amount, status, create_time)
SELECT v_order_id, total_amount, ‘pending’, NOW()
FROM fgedu_orders WHERE id = v_order_id;

COMMIT;
RAISE NOTICE ‘订单处理完成，订单ID:%’, v_order_id;
END;
$$ LANGUAGE plpgsql;

— 调用完整流程
CALL fgedu_complete_order_process(
1002,
‘[
{“product_id”: 201, “quantity”: 1, “price”: 599.00},
{“product_id”: 202, “quantity”: 2, “price”: 149.00}
]’::jsonb
);

— 输出结果
NOTICE: 订单创建成功，订单号:ORD202604071531001002，订单ID:2
NOTICE: 产品PostgreSQL实战教程库存扣减成功，扣减数量:1
NOTICE: 产品数据库运维手册库存扣减成功，扣减数量:2
NOTICE: 订单处理完成，订单ID:2
CALL

— 输出结果
procedure_name | definition
———————-+————————————————————–
fgedu_create_order | CREATE OR REPLACE PROCEDURE public.fgedu_create_order…
fgedu_deduct_inventory | CREATE OR REPLACE PROCEDURE public.fgedu_deduct_inventory…
fgedu_complete_order_process | CREATE OR REPLACE PROCEDURE public.fgedu_complete_order_process…
(3 rows)

— 查看存储过程参数信息
SELECT
p.proname AS procedure_name,
p.pronargs AS arg_count,
p.proargnames AS arg_names,
p.proargtypes::regtype[] AS arg_types
FROM pg_proc p
WHERE p.proname = ‘fgedu_create_order’;

— 输出结果
procedure_name | arg_count | arg_names | arg_types
——————–+———–+—————————-+——————————
fgedu_create_order | 3 | {p_customer_id,p_items,p_order_id} | {integer,jsonb,integer}
(1 row)

INSERT INTO fgedu_orders(order_no, customer_id, status)
VALUES(v_order_no, p_customer_id, ‘created’)
RETURNING id INTO p_order_id;

FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
LOOP
INSERT INTO fgedu_order_items(order_id, product_id, quantity, price)
VALUES(
p_order_id,
(v_item->>’product_id’)::INTEGER,
(v_item->>’quantity’)::INTEGER,
(v_item->>’price’)::NUMERIC
);
END LOOP;

UPDATE fgedu_orders o
SET total_amount = (SELECT SUM(subtotal) FROM fgedu_order_items WHERE order_id = o.id),
remark = p_remark
WHERE id = p_order_id;

COMMIT;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE PROCEDURE

— 删除存储过程
DROP PROCEDURE IF EXISTS fgedu_old_procedure(INTEGER);

— 输出结果
DROP PROCEDURE

— 步骤1：生成订单号
v_order_no := ‘ORD’ || TO_CHAR(NOW(), ‘YYYYMMDDHH24MISS’) || LPAD(p_customer_id::TEXT, 6, ‘0’);

— 步骤2：检查库存
FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
LOOP
v_product_id := (v_item->>’product_id’)::INTEGER;
v_quantity := (v_item->>’quantity’)::INTEGER;

SELECT stock INTO v_stock
FROM fgedu_inventory
WHERE product_id = v_product_id
FOR UPDATE;

IF v_stock IS NULL THEN
p_result := jsonb_build_object(
‘success’, false,
‘error’, ‘产品不存在’,
‘product_id’, v_product_id
);
RETURN;
END IF;

IF v_stock < v_quantity THEN p_result := jsonb_build_object( 'success', false, 'error', '库存不足', 'product_id', v_product_id, 'available', v_stock, 'required', v_quantity ); RETURN; END IF; END LOOP; -- 步骤3：创建订单 INSERT INTO fgedu_orders(order_no, customer_id, status) VALUES(v_order_no, p_customer_id, 'created') RETURNING id INTO v_order_id; -- 步骤4：插入订单明细 FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP INSERT INTO fgedu_order_items(order_id, product_id, quantity, price) VALUES( v_order_id, (v_item->>’product_id’)::INTEGER,
(v_item->>’quantity’)::INTEGER,
(v_item->>’price’)::NUMERIC
);

— 扣减库存
UPDATE fgedu_inventory
SET stock = stock – (v_item->>’quantity’)::INTEGER
WHERE product_id = (v_item->>’product_id’)::INTEGER;
END LOOP;

— 步骤5：计算并更新总金额
SELECT SUM(subtotal) INTO v_total_amount
FROM fgedu_order_items WHERE order_id = v_order_id;

UPDATE fgedu_orders SET total_amount = v_total_amount WHERE id = v_order_id;

— 步骤6：创建支付记录
INSERT INTO fgedu_payments(order_id, amount, payment_method, status)
VALUES(v_order_id, v_total_amount, p_payment_method, ‘pending’);

— 步骤7：更新订单状态
UPDATE fgedu_orders SET status = ‘confirmed’ WHERE id = v_order_id;

— 返回成功结果
p_result := jsonb_build_object(
‘success’, true,
‘order_id’, v_order_id,
‘order_no’, v_order_no,
‘total_amount’, v_total_amount,
‘payment_method’, p_payment_method
);

COMMIT;
END;
$$ LANGUAGE plpgsql;

— 执行订单处理
CALL fgedu_process_order_complete(
1003,
‘[
{“product_id”: 301, “quantity”: 2, “price”: 299.00},
{“product_id”: 302, “quantity”: 1, “price”: 599.00}
]’::jsonb,
‘alipay’,
NULL
);

— 输出结果
p_result
————————————————————————————————————————————
{“success”: true, “order_id”: 3, “order_no”: “ORD202604071535001003”, “total_amount”: 1197.00, “payment_method”: “alipay”}
(1 row)

CASE p_operation
WHEN ‘insert’ THEN
FOR v_record IN SELECT * FROM jsonb_array_elements(p_data)
LOOP
INSERT INTO fgedu_products(name, price, category, stock)
VALUES(
v_record->>’name’,
(v_record->>’price’)::NUMERIC,
v_record->>’category’,
(v_record->>’stock’)::INTEGER
);
v_count := v_count + 1;

— 每100条提交一次
IF v_count % 100 = 0 THEN
COMMIT;
RAISE NOTICE ‘已处理%条记录’, v_count;
END IF;
END LOOP;

WHEN ‘update’ THEN
FOR v_record IN SELECT * FROM jsonb_array_elements(p_data)
LOOP
UPDATE fgedu_products
SET price = (v_record->>’price’)::NUMERIC,
update_time = NOW()
WHERE id = (v_record->>’id’)::INTEGER;
v_count := v_count + 1;
END LOOP;

WHEN ‘delete’ THEN
FOR v_record IN SELECT * FROM jsonb_array_elements(p_data)
LOOP
DELETE FROM fgedu_products
WHERE id = (v_record->>’id’)::INTEGER;
v_count := v_count + 1;
END LOOP;

ELSE
RAISE EXCEPTION ‘不支持的操作类型:%’, p_operation;
END CASE;

COMMIT;

RAISE NOTICE ‘批量处理完成，操作:%，处理数量:%，耗时:%’,
p_operation, v_count, clock_timestamp() – v_start_time;
END;
$$ LANGUAGE plpgsql;

— 执行批量插入
CALL fgedu_batch_process_data(‘insert’, ‘
[
{“name”: “PostgreSQL入门”, “price”: 59.00, “category”: “book”, “stock”: 100},
{“name”: “PostgreSQL进阶”, “price”: 89.00, “category”: “book”, “stock”: 80},
{“name”: “PostgreSQL实战”, “price”: 129.00, “category”: “book”, “stock”: 50}
]’::jsonb);

— 输出结果
NOTICE: 批量处理完成，操作:insert，处理数量:3，耗时:00:00:00.002
CALL

— 统计热销产品TOP10
SELECT jsonb_agg(row_to_json(t))
INTO v_top_products
FROM (
SELECT
p.name as product_name,
SUM(oi.quantity) as total_quantity,
SUM(oi.subtotal) as total_amount
FROM fgedu_order_items oi
JOIN fgedu_products p ON oi.product_id = p.id
JOIN fgedu_orders o ON oi.order_id = o.id
WHERE o.create_time::DATE BETWEEN p_start_date AND p_end_date
AND o.status IN (‘confirmed’, ‘completed’)
GROUP BY p.id, p.name
ORDER BY total_quantity DESC
LIMIT 10
) t;

— 统计每日销售数据
SELECT jsonb_agg(row_to_json(t))
INTO v_daily_stats
FROM (
SELECT
create_time::DATE as sale_date,
COUNT(*) as order_count,
SUM(total_amount) as daily_amount
FROM fgedu_orders
WHERE create_time::DATE BETWEEN p_start_date AND p_end_date
AND status IN (‘confirmed’, ‘completed’)
GROUP BY create_time::DATE
ORDER BY sale_date
) t;

— 构建报表结果
p_report := jsonb_build_object(
‘report_date’, NOW()::DATE,
‘period’, jsonb_build_object(
‘start_date’, p_start_date,
‘end_date’, p_end_date
),
‘summary’, jsonb_build_object(
‘total_orders’, v_total_orders,
‘total_amount’, v_total_amount,
‘avg_order_amount’, v_avg_order_amount
),
‘top_products’, COALESCE(v_top_products, ‘[]’::jsonb),
‘daily_stats’, COALESCE(v_daily_stats, ‘[]’::jsonb)
);

END;
$$ LANGUAGE plpgsql;

— 执行报表统计
CALL fgedu_generate_sales_report(‘2026-04-01’, ‘2026-04-07’, NULL);

— 输出结果
p_report
—————————————————————————————————————————————————————————————————–
{“report_date”: “2026-04-07”, “period”: {“start_date”: “2026-04-01”, “end_date”: “2026-04-07”}, “summary”: {“total_orders”: 156, “total_amount”: 125680.50, “avg_order_amount”: 805.64}, “top_products”: [{“product_name”: “PostgreSQL实战教程”, “total_quantity”: 89, “total_amount”: 53110.00}, {“product_name”: “数据库运维手册”, “total_quantity”: 67, “total_amount”: 32165.00}], “daily_stats”: [{“sale_date”: “2026-04-01”, “order_count”: 23, “daily_amount”: 18560.00}, {“sale_date”: “2026-04-02”, “order_count”: 28, “daily_amount”: 22450.00}]}
(1 row)

— 参数验证
IF p_param1 IS NULL THEN
RAISE EXCEPTION ‘参数p_param1不能为空’;
END IF;

— 业务逻辑处理
BEGIN
— 明确指定字段，避免SELECT *
SELECT COUNT(*) INTO v_count
FROM fgedu_table
WHERE id = p_param1;

— 记录处理日志
INSERT INTO fgedu_proc_logs(proc_name, params, row_count, exec_time, create_time)
VALUES(
‘fgedu_best_practice_example’,
jsonb_build_object(‘p_param1’, p_param1, ‘p_param2’, p_param2),
v_count,
clock_timestamp() – v_start_time,
NOW()
);

EXCEPTION
WHEN OTHERS THEN
— 记录错误日志
INSERT INTO fgedu_error_logs(proc_name, error_message, create_time)
VALUES(‘fgedu_best_practice_example’, SQLERRM, NOW());
RAISE;
END;

COMMIT;
END;
$$;

— 检查数据
SELECT COUNT(*) INTO v_count FROM fgedu_orders WHERE customer_id = p_id;
RAISE NOTICE ‘找到%条订单记录’, v_count;

— 处理数据
FOR v_data IN SELECT * FROM fgedu_orders WHERE customer_id = p_id
LOOP
RAISE NOTICE ‘处理订单ID=%，订单号=%’, v_data.id, v_data.order_no;
— 业务处理逻辑
END LOOP;

RAISE NOTICE ‘处理完成’;
END;
$$ LANGUAGE plpgsql;

— 执行调试
CALL fgedu_debug_example(1001);

— 输出结果
NOTICE: 开始处理，参数p_id=1001
NOTICE: 找到3条订单记录
NOTICE: 处理订单ID=1，订单号=ORD202604071530251001
NOTICE: 处理订单ID=4，订单号=ORD202604071532001001
NOTICE: 处理订单ID=7，订单号=ORD202604071534001001
NOTICE: 处理完成
CALL