postgresql存储过程

最新推荐文章于 2025-04-13 14:52:50 发布

XavierLe

最新推荐文章于 2025-04-13 14:52:50 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/LGM_lx/article/details/85239557

数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种在数据库中使用存储过程实现自增序列的方法，适用于统计字符出现次数或生成自增序列。通过创建特定的存储过程和表，可以有效地管理和生成基于不同类型的自增序列。

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

通过指定字符串和长度，返回该字符串在数据库的存储次数，可用来统计字符出现的次数，或生成自增序列。

1. 创建存储过程

--
-- Name: gb_code_sequence(character varying, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gb_code_sequence(in_object_type character varying, in_code_length integer, OUT out_sequence character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
  declare
     v_object_type varchar(30);
     v_sequence integer;
  begin
    v_object_type := trim(in_object_type);
  if exists (select 1 from gb_code_sequence where type = v_object_type) then
      update gb_code_sequence set sequence_number = sequence_number + 1 where type = v_object_type;
  else
       begin
      insert into gb_code_sequence(type, sequence_number) values (v_object_type, 1);
       exception when others then
      update gb_code_sequence set sequence_number = sequence_number + 1 where type = v_object_type;
       end;
  end if;
  select sequence_number into v_sequence from gb_code_sequence where type = v_object_type;
  out_sequence:= lpad(v_sequence::varchar,in_code_length,'0');
 -- commit;
  end;
$$;


ALTER FUNCTION public.gb_code_sequence(in_object_type character varying, in_code_length integer, OUT out_sequence character varying) OWNER TO postgres;

2. 建存储过程对应的表

--
-- Name: gb_code_sequence; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE gb_code_sequence (
    type character varying(255) DEFAULT NULL::character varying,
    sequence_number bigint
);


ALTER TABLE gb_code_sequence OWNER TO postgres;