建站 备案,建设网站的流程可分为哪几个阶段,wordpress纯代码回顶部,最新新闻事件摘抄postgresql 存储过程 循环插入 根据插入的值判断插入相应的字段
在PostgreSQL中#xff0c;您可以使用PL/pgSQL语言编写函数#xff0c;该函数可以在循环中执行插入操作#xff0c;并根据插入的值判断应该插入哪些字段。但是#xff0c;请注意#xff0c;PostgreSQL通常不…postgresql 存储过程 循环插入 根据插入的值判断插入相应的字段
在PostgreSQL中您可以使用PL/pgSQL语言编写函数该函数可以在循环中执行插入操作并根据插入的值判断应该插入哪些字段。但是请注意PostgreSQL通常不支持动态字段插入因为表的结构是固定的。您通常需要在插入时提供所有字段的值即使某些字段是NULL。
不过您可以通过逻辑判断来确保只插入非NULL或符合特定条件的值。以下是一个示例函数它使用循环来插入数据并根据条件判断哪些字段应该被包含在INSERT语句中
CREATE OR REPLACE FUNCTION loop_insert_based_on_value()
RETURNS void AS
$$DECLARE row_record RECORD; -- 用于存储从源表读取的行 should_insert_field1 BOOLEAN : false; should_insert_field2 BOOLEAN : false; should_insert_field3 BOOLEAN : false;
BEGIN -- 假设我们有一个源表source_data从中读取数据 FOR row_record IN SELECT * FROM source_data LOOP -- 根据row_record中的值判断哪些字段应该被插入 IF row_record.some_column IS NOT NULL AND row_record.some_column 10 THEN should_insert_field1 : true; END IF; IF row_record.another_column value THEN should_insert_field2 : true; END IF; -- 添加更多条件判断... -- 构建INSERT语句只包含需要插入的字段 INSERT INTO target_table ( field1, field2, field3 ) VALUES ( CASE WHEN should_insert_field1 THEN row_record.value_for_field1 ELSE NULL END, CASE WHEN should_insert_field2 THEN row_record.value_for_field2 ELSE NULL END, CASE WHEN should_insert_field3 THEN row_record.value_for_field3 ELSE NULL END ); -- 重置字段插入标志为下一次循环做准备 should_insert_field1 : false; should_insert_field2 : false; should_insert_field3 : false; END LOOP;
END; $$LANGUAGE plpgsql;在这个函数中我们首先声明了一些变量来跟踪哪些字段应该被插入。然后我们使用一个FOR循环来遍历源表source_data中的每一行。对于每一行我们检查某些条件并设置相应的标志变量。最后我们构建一个INSERT语句只包括那些其标志变量为TRUE的字段。我们使用CASE语句在INSERT中为这些字段提供值或者如果不需要插入该字段则提供NULL。
请注意这个示例假设source_data表中包含用于决定哪些字段应该被插入的列如some_column和another_column以及用于插入到target_table中的实际值如value_for_field1value_for_field2和value_for_field3。您需要根据您的实际表结构和业务需求来调整这些列名。
此外虽然这种方法可以实现基于值的字段插入但它并不是最优的因为它在每次循环迭代时都执行一个完整的INSERT语句。如果可能最好优化您的数据库模式或业务逻辑以避免这种动态字段插入的需求。
实战sql批量新增
CREATE OR REPLACE PROCEDURE public.data_read_zao0070(p_org_id integer, p_uid character varying, p_user_id character varying)LANGUAGE plpgsql
AS $procedure$
DECLAREp_result_count integer;current_row RECORD; current_row2 RECORD; current_rows varchar[];current_value varchar;-- 定义判断需要增加的字段为true需要增加,false不需要增加。
inventory_used_field1 BOOLEAN : false;
inventory_used_amount_field2 BOOLEAN : false;
inventory_nonused_field3 BOOLEAN : false;
inventory_nonused_amount_field4 BOOLEAN : false; -- 循环插入 FOR current_row2 IN (selectm.* fromplant_if_sap_zao0070 mwherenot exists (select*fromplant_actual pwherem.del_flag 0and p.del_flag 0and trim(m.m_code) trim(p.m_code)and to_char((date_trunc(MONTH, to_date(m.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) p.plt_shp_dtand to_char((date_trunc(MONTH, to_date(m.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) p.shipping_dt)and exists (select*fromplant_m_model mowheremo.del_flag 0and m.del_flag 0and trim(mo.m_code) trim(m.m_code))and m.org_id p_org_id) LOOP IF trim(current_row2.warehouse_code) BLK THEN inventory_nonused_field3 : true; inventory_nonused_amount_field4 : true; END IF; IF trim(current_row2.warehouse_code) URG THEN inventory_used_field1 : true; inventory_used_amount_field2 : true; END IF; raise notice ;raise notice 标记: [%] ,inventory_nonused_field3;raise notice 标记: [%] ,inventory_nonused_amount_field4;raise notice 标记: [%] ,inventory_used_field1;raise notice 标记: [%] ,inventory_used_amount_field2;raise notice ;INSERT INTO public.plant_actual( org_id, m_code, bo_code, plt_shp_dt, shipping_dt, currency, inventory_used, inventory_used_amount, inventory_nonused, inventory_nonused_amount,create_by, modify_by)values(p_org_id,trim(current_row2.m_code),null,to_char((date_trunc(MONTH, to_date(current_row2.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD),to_char((date_trunc(MONTH, to_date(current_row2.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD),CNY,CASE WHEN inventory_used_field1 THEN CAST(CONCAT(current_row2.q_qty_sign,current_row2.q_qty_number) AS NUMERIC) ELSE NULL END, CASE WHEN inventory_used_amount_field2 THEN CAST(CONCAT(current_row2.q_amount_sign,current_row2.q_amount_number, ., current_row2.q_amount_point) AS NUMERIC) ELSE NULL END, CASE WHEN inventory_nonused_field3 THEN CAST(CONCAT(current_row2.r_qty_sign,current_row2.r_qty_number) AS NUMERIC) ELSE NULL END, CASE WHEN inventory_nonused_amount_field4 THEN CAST(CONCAT(current_row2.r_amount_sign,current_row2.r_amount_number, ., current_row2.r_amount_point) AS NUMERIC) ELSE NULL END, p_user_id,p_user_id ); -- 循环结束 重置变量inventory_used_field1 : false; inventory_used_amount_field2 : false; inventory_nonused_field3 : false; inventory_nonused_amount_field4 : false; END LOOP; 实战sql批量更新 具体逻辑 思路仿照上面新增的思路
CREATE OR REPLACE PROCEDURE public.data_read_zao0070(p_org_id integer, p_uid character varying, p_user_id character varying)LANGUAGE plpgsql
AS $procedure$
DECLAREp_result_count integer;current_row RECORD; current_row2 RECORD; current_rows varchar[];current_value varchar;inventory_used_field1 BOOLEAN : false;
inventory_used_amount_field2 BOOLEAN : false;
inventory_nonused_field3 BOOLEAN : false;
inventory_nonused_amount_field4 BOOLEAN : false; FOR current_row IN (select m.* fromplant_if_sap_zao0070 mwhereexists (select*fromplant_actual pwherem.del_flag 0and p.del_flag 0and trim(m.m_code) trim(p.m_code)and p.plt_shp_dt to_char((date_trunc(MONTH, to_date(m.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) and p.shipping_dt to_char((date_trunc(MONTH, to_date(m.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) )and exists (select*fromplant_m_model mowheremo.del_flag 0and m.del_flag 0and trim(mo.m_code) trim(m.m_code))and m.org_id p_org_id ) LOOP raise notice 更改数据 : [%] ,current_row; IF trim(current_row.warehouse_code) BLK THEN inventory_nonused_field3 : true; inventory_nonused_amount_field4 : true; END IF; IF trim(current_row.warehouse_code) URG THEN inventory_used_field1 : true; inventory_used_amount_field2 : true; END IF; raise notice --------- ;raise notice 标记: [%] ,inventory_nonused_field3;raise notice 标记: [%] ,inventory_nonused_amount_field4;raise notice 标记: [%] ,inventory_used_field1;raise notice 标记: [%] ,inventory_used_amount_field2;raise notice --------- ;UPDATE public.plant_actualSET inventory_usedCASE WHEN inventory_used_field1 THEN CAST(CONCAT(current_row.q_qty_sign,current_row.q_qty_number) AS NUMERIC) ELSE NULL END, inventory_used_amountCASE WHEN inventory_used_amount_field2 THEN CAST(CONCAT(current_row.q_amount_sign,current_row.q_amount_number, ., current_row.q_amount_point) AS NUMERIC) ELSE NULL END, inventory_nonusedCASE WHEN inventory_nonused_field3 THEN CAST(CONCAT(current_row.r_qty_sign,current_row.r_qty_number) AS NUMERIC) ELSE NULL END, inventory_nonused_amountCASE WHEN inventory_nonused_amount_field4 THEN CAST(CONCAT(current_row.r_amount_sign,current_row.r_amount_number, ., current_row.r_amount_point) AS NUMERIC) ELSE NULL END, modify_byp_user_idWHERE trim(m_code)trim(current_row.m_code) AND plt_shp_dt to_char((date_trunc(MONTH, to_date(current_row.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) and shipping_dt to_char((date_trunc(MONTH, to_date(current_row.stock_years, YYYYMM) interval 1 month) - interval 1 day)::date, YYYY-MM-DD) and del_flag0 and org_idp_org_id; -- 增加一步修改状态为1update plant_if_sap_zao0070 set del_flag 1where trim(m_code) trim(current_row.m_code) and org_id p_org_id;inventory_used_field1 : false; inventory_used_amount_field2 : false; inventory_nonused_field3 : false; inventory_nonused_amount_field4 : false; END LOOP;
