存储过程 定义变量list类型, 存储某个字段的值,以及存储多行数据 批量更新、批量插入

先粘贴文言一心上的代码 是存储多个值的示例
下面会有实际应用代码,包含存储多个值以及多行数据 批量更新、批量插入

CREATE OR REPLACE FUNCTION update_based_on_multiple_values()  
RETURNS void AS 
$$
  
DECLARE  
    my_values varchar[];  -- 声明一个数组变量来存储多个值  
    current_value varchar; -- 声明一个变量来遍历数组中的每个值  
BEGIN  
    -- 查询 my_field 的多个值，并将它们存储在数组中  
    SELECT ARRAY_AGG(my_field) INTO my_values FROM my_table WHERE some_condition; -- some_condition 是您的查询条件  
  
    -- 遍历数组中的每个值，并根据它更新 another_field  
    FOREACH current_value IN ARRAY my_values  
    LOOP  
        UPDATE my_table  
        SET another_field = another_field + 1  -- 这里只是一个示例，您可以根据需要更改更新逻辑  
        WHERE my_field = current_value;  
    END LOOP;  
END;  

$$
 LANGUAGE plpgsql;

实际应用代码

CREATE OR REPLACE PROCEDURE public.data_read_zao0100_m(p_org_id integer, p_uid character varying, p_user_id character varying)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
  p_result_count integer;
  current_row RECORD;  
 
--  m_code_tab IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;  
  current_rows varchar[];
  current_value varchar;
begin
--	     查询不存在的型号 插入接口导入结果表
	    perform 
			m.*
		from
			plant_if_sap_zao0100_m m
			where
			not exists (
			select
				*
			from
				plant_m_model mo
			where
				mo.del_flag = '0'
				and m.del_flag = '0'
				and mo.m_code = m.m_code);

			   get diagnostics p_result_count = ROW_COUNT;
			   raise notice '错误数量: [%] ',p_result_count::integer;

			    -- 新增操作
			    if p_result_count > 0 then
				
			   INSERT INTO public.plant_if_result
				(org_id, uid, user_id, row_num, inf_type, error_code,  create_by,  modify_by)

				    select
				    p_org_id ,
				    p_uid ,
				    p_user_id ,
					m.row_num row_num,
					'3' inf_type,
					'型号不存在' error_code,
					 p_user_id ,
					 p_user_id 
				from
					plant_if_sap_zao0100_m m
					where
					not exists (
					select
						*
					from
						plant_m_model mo
					where
						mo.del_flag = '0'
						and m.del_flag = '0'
						and mo.m_code = m.m_code);
			     
		   		 get diagnostics p_result_count = ROW_COUNT;
		 	     raise notice 'insert: [%] ',p_result_count::integer;
					
--		 	    return;
				end if;
-- =========================================================================================================================	
	  --查询型号价格表中存在的数据,有的话则更改  循环更改
	  //这里是使用变量存储多行数据, 注意需要loop联用，有兴趣需要百度下语法
   FOR current_row IN 
   (select
		m.* 
		from
			plant_if_sap_zao0100_m m
		where
			 exists (
			select
				*
			from
				plant_m_code_price p
			where
				m.del_flag = '0'
				and p.del_flag = '0'
				and m.m_code = p.m_code
				and p.bo_code = ''
				and p.group_code = ''
				and p.cust_code = ''
				and p.price_type = '2')
				and exists 
				(
			select
				*
			from
				plant_m_model mo
			where
				mo.del_flag = '0'
				and m.del_flag = '0'
				and mo.m_code = m.m_code)) LOOP 

		 -- 循环更改中,获取行数不生效		
--		 get diagnostics p_result_count = ROW_COUNT;				
--		 raise notice 'update plant_m_code_price: [%] ',p_result_count::integer;
							
    UPDATE public.plant_m_code_price
		SET 
		effect_date =
			CONCAT(  
					        SUBSTRING(current_row.valid_from, 1, 4), '-',  
					        SUBSTRING(current_row.valid_from, 5, 2), '-',  
					        SUBSTRING(current_row.valid_from, 7, 2)  
					    ) ,
--		current_row.valid_from  ,
		lapse_date = 
		CONCAT(  
					        SUBSTRING(current_row.valid_to, 1, 4), '-',  
					        SUBSTRING(current_row.valid_to, 5, 2), '-',  
					        SUBSTRING(current_row.valid_to, 7, 2)  
					    ) ,
--		current_row.valid_to ,
--		sale_price =  CAST(current_row.sales_price_integer AS TEXT) || '.' || CAST(current_row.sales_price_decimal AS TEXT) ,
		sale_price =CAST(CONCAT(current_row.sales_price_integer, '.', current_row.sales_price_decimal) AS NUMERIC),
		currency_no= current_row.currency_code ,
		modify_by=p_user_id
	--	inv_price=0, purchase_price=0, sale_price=0, divis_plan_price=0, currency_no='', 
		WHERE org_id=p_org_id AND m_code=current_row.m_code AND bo_code = '' AND group_code = ''
		AND cust_code = '' and  del_flag='0' and price_type = '2';	
	
	-- 增加一步修改状态为1
	update plant_if_sap_zao0100_m set del_flag = '1'
	where m_code = current_row.m_code;
	
 END LOOP;  

-- =========================================================================================================================
--     return;
--    end if;
 
-- =========================================================================================================================
     --查询型号价格表中不存在的数据,有的话则新增  批量插入
	   perform
		m.* 
		from
			plant_if_sap_zao0100_m m
		where
			not exists (
			select
				*
			from
				plant_m_code_price p
			where
				m.del_flag = '0'
				and p.del_flag = '0'
				and m.m_code = p.m_code
				and p.bo_code = ''
				and p.group_code = ''
				and p.cust_code = '')
				and exists (
					select
						*
					from
						plant_m_model mo
					where
						mo.del_flag = '0'
						and m.del_flag = '0'
						and mo.m_code = m.m_code);
			
				 get diagnostics p_result_count = ROW_COUNT;				
				 raise notice 'insert plant_m_code_price: [%] ',p_result_count::integer;

		     --查询型号价格表中不存在的数据,有的话则新增
		       if p_result_count > 0 then

		       -- 新增要更改的型号别
		       select
					ARRAY_AGG(m.m_code) INTO current_rows
					from
						plant_if_sap_zao0100_m m
					where
						not exists (
						select
							*
						from
							plant_m_code_price p
						where
							m.del_flag = '0'
							and p.del_flag = '0'
							and m.m_code = p.m_code
							and p.bo_code = ''
							and p.group_code = ''
							and p.cust_code = '')
							and exists (
								select
									*
								from
									plant_m_model mo
								where
									mo.del_flag = '0'
									and m.del_flag = '0'
									and mo.m_code = m.m_code); 

								
			INSERT INTO public.plant_m_code_price
			(org_id, m_code, bo_code, group_code, cust_code, price_type, effect_date, lapse_date, inv_price,
			purchase_price,sale_price, divis_plan_price, currency_no, del_flag, create_by, modify_by)	
			   select
			        p_org_id org_id,
					m.m_code,
					'' bo_code,
					'' group_code,
					'' cust_code,
					'2' price_type,
					CONCAT(  
				        SUBSTRING(m.valid_from, 1, 4), '-',  
				        SUBSTRING(m.valid_from, 5, 2), '-',  
				        SUBSTRING(m.valid_from, 7, 2)  
				    ) AS effect_date 	,
--					m.valid_from,
				    CONCAT(  
				        SUBSTRING(m.valid_to, 1, 4), '-',  
				        SUBSTRING(m.valid_to, 5, 2), '-',  
				        SUBSTRING(m.valid_to, 7, 2)  
				    ) AS lapse_date 	,
--					m.valid_to,	
					0 inv_price,
					0 purchase_price,
					CAST(CONCAT(sales_price_integer, '.', sales_price_decimal) AS NUMERIC) AS sale_price  ,
--					CAST(m.sales_price_integer AS TEXT) || '.' || CAST(m.sales_price_decimal AS TEXT) AS sale_price ,
					0 divis_plan_price,
					m.currency_code ,	
					m.del_flag,
					m.create_by ,
					m.create_by 
				from
					plant_if_sap_zao0100_m m
				where
					not exists (
					select
						*
					from
						plant_m_code_price p
					where
						m.del_flag = '0'
						and p.del_flag = '0'
						and m.m_code = p.m_code
						and p.bo_code  = ''
						and p.group_code = ''
						and p.cust_code = '')
						and exists (
							select
								*
							from
								plant_m_model mo
							where
								mo.del_flag = '0'
								and m.del_flag = '0'
								and mo.m_code = m.m_code);
							
							
				raise notice '更改新增的m_code: [%] ',current_rows;	
				//这里是定义变量存储多行值
				FOREACH current_value IN ARRAY current_rows  Loop 
--				FOR i IN 1..current_rows LOOP  
					update plant_if_sap_zao0100_m set del_flag = '1'
					where m_code = current_value;
				  END LOOP;  

	    end if;
-- =========================================================================================================================
	
--	rollback;  --回滚
    return;
end
$procedure$
;

使用变量存储多行数据, 注意需要loop联用，有兴趣需要百度下语法