PostgreSQL 存储过程

一、前言

最近在实习期间遇到的一些问题，其实逻辑思路很简单，就是需要把关联表的一些脏数据清除，逻辑就是从原表获得所有id，再根据这个id去关系表中遍历所有数据，根据关系表中的数据进行判断更新。

理想的 sql 如下：

update dt_app_user_rel r2
set r2.is_delete=1
where r2.range_type=2 and r2.data_key not in (
	select r1.data_key
	from dt_app_user_rel r1
	where r1.app_id=r2.app_id and r1.range_type=1 and r1.is_delete=0
)

这过程中存在单表的自查询和更新，但这种情况是不被允许的，这个时候就需要用到存储过程了。

这里先附上存储过程的简单结构：

-- 基础的存储过程语法模板
create or replace procedure public.proc_check_data(check_period character)
language 'plpgsql'
AS $BODY$
 
	-- 1、声明变量
	declare
		-- 1.1、[声明变量]
		dept_name varchar(128);
 
	-- 2、开始事务
    begin
		-- 操作语句
 
	-- 3、结束事务
	end;
$BODY$;

其实会发现相当于只是把好几段 sql 放在一起，中间可以引入一些公共变量，从而完成一些靠单句查询无法完成的工作。

存储过程的优点除了这个，最重要的是可以重复使用，从而可以减少数据库开发人员的工作量。

第二个则是存储过程可以提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。

回到我们之前的问题，那么在依靠存储过程后，sql 如下：

CREATE OR REPLACE PROCEDURE "public"."process_dt_app_user_rel"()
 AS $BODY$
DECLARE
    done BOOLEAN := FALSE;
    app_id_val INT;
    cur CURSOR FOR SELECT DISTINCT app_id FROM dt_app_user_rel;
BEGIN
    -- 临时表，存特定app_id下的所有可见用户id
    CREATE TEMPORARY TABLE temp_data_keys (data_key varchar(255));
    
    -- 打开游标
    OPEN cur;
    
    -- 循环处理每个appId
    LOOP
        -- 读取下一个appId
        FETCH cur INTO app_id_val;
        EXIT WHEN NOT FOUND;
        
        -- 遍历app_id，查询对应的可见用户id
        INSERT INTO temp_data_keys
        SELECT data_key
        FROM dt_app_user_rel
        WHERE range_type = 1
        AND is_delete = 0
        AND app_id = app_id_val;
        
        -- 执行UPDATE操作
        UPDATE dt_app_user_rel
        SET is_delete = 1
        WHERE range_type = 2
        AND app_id = app_id_val
        AND data_key NOT IN (
            SELECT data_key
            FROM temp_data_keys
        );
        
        -- 清空临时表
        TRUNCATE TABLE temp_data_keys;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
    
    -- 删除临时表
    DROP TABLE IF EXISTS temp_data_keys;
END;
$BODY$
  LANGUAGE plpgsql