pgsql游标批量插入数据，id基于最大值自增

最新推荐文章于 2024-07-18 17:16:08 发布

GodSure0914

最新推荐文章于 2024-07-18 17:16:08 发布

阅读量1k

点赞数 2

分类专栏： sql 文章标签： sql postgresql mysql 数据库

本文链接：https://blog.csdn.net/GodSure0914/article/details/122070374

版权

sql 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE FUNCTION bach_save_config(c_code text,c_name text,c_type text,c_value text,c_business_type_name text,c_memo text)
RETURNS refcursor AS
$BODY$
declare
c_org_id int8;
orgid_list refcursor;
c_id_plus  int8 DEFAULT 1 ;
isexist int8;
  BEGIN
    OPEN orgid_list for EXECUTE 'select his_org_id from "comm"."hospital" group by his_org_id';
  loop
  fetch orgid_list into c_org_id;
    if found then
		select config_id into isexist from "comm"."config" where config_code=c_code and his_org_id = c_org_id LIMIT 1;
		if isexist is  null then 	
    INSERT INTO "comm"."config"("config_id", "business_type_id", "config_code", "config_name", "display_flag", "maintain_flag", "config_type", "input_code", "full_code", "precondition", "memo", "sort_order", "version", "config_value", "his_org_id", "his_creater_id", "his_creater_name", "his_create_time", "his_updater_id", "his_update_time") VALUES ((SELECT max(config_id) FROM comm.config)+c_id_plus,(SELECT business_type_id FROM comm.business_type WHERE business_type_name =c_business_type_name LIMIT 1), c_code, c_name, '1', '0', c_type, NULL, NULL, NULL, c_memo, 1, 0, c_value, c_org_id, -1, '系统管理员', now(), -1, now());
		else
		          raise notice '配置编码已经存在 ...%', c_code;  --打印消息 ---  
		end if;
    else
      exit;
    end if;
    c_id_plus = c_id_plus +1;
  end loop;
  close orgid_list;
    raise notice '配置插入完毕';
  return c_code;
  exception when others then
      raise exception 'error--(%)',sqlerrm;
end;
$BODY$
LANGUAGE plpgsql;