--------------------------------------------方案1
CREATE OR REPLACE FUNCTION InsertFromJSON(p_json_data TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
user_record JSON;
product_record JSON;
BEGIN
-- 开始事务
BEGIN
-- 清空表数据（如果需要保留历史数据，请删除这两行）
DELETE FROM products;
DELETE FROM users;
-- 插入用户数据
FOR user_record IN
SELECT * FROM json_array_elements(p_json_data::json->'users')
LOOP
INSERT INTO users (id, name, email, isActive)
VALUES (
(user_record->>'id')::INTEGER,
user_record->>'name',
user_record->>'email',
(user_record->>'isActive')::BOOLEAN
);
END LOOP;
-- 插入产品数据
FOR product_record IN
SELECT * FROM json_array_elements(p_json_data::json->'products')
LOOP
INSERT INTO products (productId, productName, price, category)
VALUES (
product_record->>'productId',
product_record->>'productName',
(product_record->>'price')::DECIMAL,
product_record->>'category'
);
END LOOP;
-- 提交事务
RETURN 1; -- 成功返回1
EXCEPTION
WHEN OTHERS THEN
-- 回滚事务
RAISE NOTICE '错误: %', SQLERRM;
RETURN 0; -- 失败返回0
END;
END;
$$;
--------------------------------------------方案2
CREATE OR REPLACE FUNCTION InsertFromJSON(p_json_data TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
-- 开始事务
BEGIN
-- 清空表数据（如果需要保留历史数据，请删除这两行）
DELETE FROM products;
DELETE FROM users;
-- 批量插入用户数据（更高效的方式）
INSERT INTO users (id, name, email, isActive)
SELECT
(value->>'id')::INTEGER,
value->>'name',
value->>'email',
(value->>'isActive')::BOOLEAN
FROM json_array_elements(p_json_data::json->'users');
-- 批量插入产品数据（更高效的方式）
INSERT INTO products (productId, productName, price, category)
SELECT
value->>'productId',
value->>'productName',
(value->>'price')::DECIMAL,
value->>'category'
FROM json_array_elements(p_json_data::json->'products');
-- 提交事务
RETURN 1; -- 成功返回1
EXCEPTION
WHEN OTHERS THEN
-- 回滚事务（由于是异常块，会自动回滚）
RAISE NOTICE '插入数据时发生错误: %', SQLERRM;
RETURN 0; -- 失败返回0
END;
END;
$$;
上面那种方案插入速度快 原因是什么？
方案2的插入速度更快，主要原因如下：
性能差异分析
方案1：循环插入（慢）
FOR user_record IN SELECT * FROM json_array_elements(...)
LOOP
INSERT INTO users (...) VALUES (...);
END LOOP;
性能瓶颈：
N次单独INSERT操作：每条记录都执行一次独立的INSERT
频繁的事务开销：每次INSERT都有事务管理开销
网络往返延迟：在循环中多次与数据库交互
锁竞争：频繁获取和释放表锁
方案2：批量插入（快）
INSERT INTO users (...)
SELECT ... FROM json_array_elements(...);
性能优势：
单次操作：整个数据集一次INSERT完成
最小化事务开销：只有一个事务边界
优化器优势：查询优化器可以制定更有效的执行计划
减少锁竞争：批量操作锁表时间更短
性能对比数据（估算）
数据量
方案1（循环）
方案2（批量）
性能提升
100条
~500ms
~50ms
10倍
1000条
~5s
~200ms
25倍
10000条
~50s
~2s
25倍
推荐的最佳实践
-- 进一步优化的版本
CREATE OR REPLACE FUNCTION InsertFromJSON(p_json_data TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
-- 使用单个事务块
BEGIN
-- 批量删除（比逐条删除快）
TRUNCATE users, products RESTART IDENTITY;
-- 批量插入用户数据
INSERT INTO users (id, name, email, isActive)
SELECT
(value->>'id')::INTEGER,
value->>'name',
value->>'email',
COALESCE((value->>'isActive')::BOOLEAN, true) -- 默认值处理
FROM json_array_elements(p_json_data::json->'users');
-- 批量插入产品数据
INSERT INTO products (productId, productName, price, category)
SELECT
value->>'productId',
value->>'productName',
(value->>'price')::DECIMAL,
value->>'category'
FROM json_array_elements(p_json_data::json->'products');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '错误: %', SQLERRM;
RETURN 0;
END;
END;
$$;
总结：方案2的批量操作方式在数据库插入性能上显著优于方案1的循环插入。
INSERT INTO users (...)
SELECT ... FROM json_array_elements(...); 为什么是整个数据集一次INSERT完成? 他不需要内部循环吗?
很好的问题！这里有一个重要的概念区别：SQL是声明式语言，不是命令式语言。
SQL的声明式特性
当执行：
INSERT INTO users (id, name, email)
SELECT (value->>'id')::INTEGER, value->>'name', value->>'email'
FROM json_array_elements('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]');
实际上发生的是：
方案1：PL/pgSQL循环（命令式）
-- 这是明确的循环执行
FOR user_record IN SELECT * FROM json_array_elements(...)
LOOP
INSERT INTO users VALUES (...); -- 执行N次
END LOOP;
执行流程：
获取第一条记录
执行INSERT
获取第二条记录
执行INSERT
...循环N次
方案2：SQL批量插入（声明式）
-- 这是声明要做什么，不是怎么做
INSERT INTO users (...)
SELECT ... FROM json_array_elements(...);
执行流程：
解析整个SELECT语句，生成执行计划
一次性处理所有数据
单次INSERT操作完成
数据库内部的优化
虽然从逻辑上看，数据库"内部"确实要处理多条记录，但关键区别在于：
执行层面的优化：
单次事务提交
单次WAL日志写入
批量页面操作
优化的内存使用
类比理解：
方案1（循环） 像：
去超市10次，每次买1件商品
每次都要：出门→走路→结账→返回
方案2（批量） 像：
去超市1次，用购物车买10件商品
只需要：一次出门→一次结账→一次返回
执行计划对比
方案1的执行计划：
N次循环，每次：
Insert on users (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
方案2的执行计划：
Insert on users (cost=0.00..5.00 rows=1000 width=0)
-> Function Scan on json_array_elements (cost=0.00..5.00 rows=1000 width=0)
性能差异的关键因素
操作类型
方案1（循环）
方案2（批量）
事务提交
N次
1次
解析优化
N次
1次
锁获取
N次
1次
网络通信
N次
1次
WAL写入
N次
1次
验证示例
你可以通过EXPLAIN ANALYZE来验证：
-- 方案1：会显示N次插入操作
EXPLAIN ANALYZE
FOR i IN 1..1000 LOOP
INSERT INTO test_table VALUES (i);
END LOOP;
-- 方案2：显示单次批量插入
EXPLAIN ANALYZE
INSERT INTO test_table
SELECT generate_series(1, 1000);
总结： SQL的INSERT ... SELECT是声明式的批量操作，虽然逻辑上处理多条记录，但在执行层面是作为单个原子操作优化的，这才是性能差异的根本原因。
什么是声明式? 什么是命令式 为什么方案一是命令式 而方案二是声明式 用通俗一点的语言解释一下 方便我理解
很好的问题！我用一个生活中的比喻来解释，这样更容易理解：
🍳 做饭的比喻
命令式（方案1）：像菜谱一样一步步指挥
FOR user_record IN ... LOOP
INSERT INTO users VALUES (...);
END LOOP;
就像你对助手说：
"拿起第一个鸡蛋"
"把鸡蛋打进碗里"
"拿起第二个鸡蛋"
"把鸡蛋打进碗里"
"拿起第三个鸡蛋"
"把鸡蛋打进碗里"
...重复100次
特点： 你明确指定了每一步怎么做
声明式（方案2）：像点外卖一样描述结果
INSERT INTO users (...)
SELECT ... FROM json_array_elements(...);
就像你对助手说：
"给我准备一碗有100个鸡蛋的蛋液"
特点： 你只描述想要什么结果，不关心具体怎么做
🎯 核心区别
命令式（Imperative）：关注"过程"
重点： 怎么做（How）
思维： 一步步的指令
例子： 编程语言中的for循环、if判断
声明式（Declarative）：关注"结果"
重点： 要什么（What）
思维： 描述最终状态
例子： SQL、HTML、配置文件
💻 代码对比理解
方案1：命令式（微观管理）
-- 你像"监工"一样盯着每个步骤：
开始循环
取第一条数据 → 插入 → 提交
取第二条数据 → 插入 → 提交
取第三条数据 → 插入 → 提交
...
结束循环
方案2：声明式（目标管理）
-- 你像"老板"一样只关心结果：
"把这些数据全部插入到表中"
-- 数据库自己决定最优的执行方式
🏃 性能比喻
方案1像：快递员送100个包裹
回快递站取1个包裹 → 送货 → 返回
回快递站取1个包裹 → 送货 → 返回
重复100次
效率低： 大量重复路程
方案2像：快递员用卡车一次送100个包裹
开卡车装100个包裹 → 一次性送货
效率高： 一次完成
🔧 技术层面的简单理解
方案1：数据库被迫"笨拙地工作"
"好吧，用户要我一次插一条，我就照做"
每次插入都要：检查约束+写日志+更新索引...
方案2：数据库可以"智能优化"
"用户要批量插入，我可以优化一下："
批量检查约束+批量写日志+批量更新索引...
📊 总结表格
方面
命令式（方案1）
声明式（方案2）
关注点
过程步骤
最终结果
控制权
程序员控制每一步
数据库优化器控制
性能
差（N次操作）
好（1次操作）
代码量
多（要写循环）
少（一行搞定）
类比
教人一步步做菜
告诉厨师要什么菜
简单记：
🎯 声明式：我要什么（What）
🔧 命令式：我怎么要（How）
这就是为什么SQL被称为"声明式语言" - 你告诉数据库你想要什么数据，它自己决定最有效的方式去获取。
浙公网安备 33010602011771号