Postgresql的pl/pgql用法--将多条执行语句作为一个事务

原创于 2018-05-09 15:03:29 发布 · 2.9k 阅读

0 ·

CC 4.0 BY-SA版权

psql 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一个PL/SQL过程的实现案例，其中包括插入记录、选择特定ID、删除相关记录及再次插入的操作步骤。该过程展示了如何使用PL/SQL进行数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DO $$  
DECLARE  
	v_id bigint;  
begin  
	--1.
	INSERT INTO r_test_a (name, value, unit, mode, uid, create_ts)  
	SELECT 't_t','','',2,0, extract(epoch from now())::bigint 
	WHERE NOT EXISTS (SELECT id FROM r_test_a WHERE name = 't_t' AND value = '' AND unit = '' AND uid =0);
	--2.
	SELECT id into v_id from r_test_a WHERE name = 't_t' AND value = '' AND unit = '' AND uid = 0;
	--3.
	DELETE FROM r_test_b a WHERE a.obj_id = 'xxx' AND a.file_id = 'ooo' AND a.id IN (SELECT id FROM r_test_a b  WHERE b.name = 't_t' AND b.uid=0); 
	--4.
	INSERT INTO r_test_b (id, obj_id, file_id, create_ts) SELECT v_id,'xxx','ooo', extract(epoch from now())::bigint ;
end 
$$;