PostgreSQL创建存储过程

1，添加公告

-- PROCEDURE: public.pr_notice_info_add(character varying, text, integer, integer, integer, integer, character varying, character varying, character varying, character varying, integer, integer)

-- DROP PROCEDURE public.pr_notice_info_add(character varying, text, integer, integer, integer, integer, character varying, character varying, character varying, character varying, integer, integer);

CREATE OR REPLACE PROCEDURE public.pr_notice_info_add(
	noticetitle character varying,
	noticecontent text,
	provinceid integer,
	cityid integer,
	districtid integer,
	streetid integer,
	isshow integer,
	INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
	declare stack text;
	declare provincename character varying;--省名称
	declare cityname character varying;--市名称
	declare districtname character varying;--县区名称
	declare streetname character varying;--街道名称
begin
	returnvalue=0;
  	provincename='';
  	cityname='';
 	districtname='';
	streetname='';
	select region_name into provincename from t_region_info where region_id=provinceid;
	select region_name into cityname from t_region_info where region_id=cityid;
	select region_name into districtname from t_region_info where region_id=districtid;
	select region_name into streetname from t_region_info where region_id=streetid;
	--添加公告
    insert into t_notice_info(notice_title,notice_content,province_id,city_id,
							  district_id,street_id,province_name,city_name,district_name,street_name,is_show,add_time)
    values(noticetitle,noticecontent,provinceid,cityid,
		   districtid,streetid,
		   provincename,cityname,districtname,streetname,
		   isshow,floor(EXTRACT(epoch FROM (now()::timestamp with time zone))*1000));
    returnvalue:=1;
    exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
end;
$BODY$;

2，删除公告

-- PROCEDURE: public.pr_notice_info_delete(integer, integer)

-- DROP PROCEDURE public.pr_notice_info_delete(integer, integer);

CREATE OR REPLACE PROCEDURE public.pr_notice_info_delete(
	noticeid integer,
	INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
BEGIN
	-- 删除公告
	delete  from t_notice_info where notice_id=noticeid;
    returnvalue=1;
   exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
END
$BODY$;

3，修改公告

-- PROCEDURE: public.pr_notice_info_edit(integer, character varying, text, integer, integer, integer, integer, integer, integer)

-- DROP PROCEDURE public.pr_notice_info_edit(integer, character varying, text, integer, integer, integer, integer, integer, integer);

CREATE OR REPLACE PROCEDURE public.pr_notice_info_edit(
	noticeid integer,
	noticetitle character varying,
	noticecontent text,
	provinceid integer,
	cityid integer,
	districtid integer,
	streetid integer,
	isshow integer,
	INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
declare provincename character varying;--省名称
	declare cityname character varying;--市名称
	declare districtname character varying;--县区名称
	declare streetname character varying;--街道名称
BEGIN
	returnvalue=0;
  	provincename='';
  	cityname='';
 	districtname='';
	streetname='';
	select region_name into provincename from t_region_info where region_id=provinceid;
	select region_name into cityname from t_region_info where region_id=cityid;
	select region_name into districtname from t_region_info where region_id=districtid;
	select region_name into streetname from t_region_info where region_id=streetid;
	-- 修改公告
	 update t_notice_info
    set notice_title=noticetitle,notice_content=noticecontent,province_id=provinceid,city_id=cityid,district_id=districtid,street_id=streetid,
	province_name=provincename,city_name=cityname,district_name=districtname,street_name=streetname,is_show=isshow
    where notice_id=noticeid;
    returnvalue:=1;
	 exception
    WHEN RAISE_EXCEPTION THEN
    begin
        GET STACKED DIAGNOSTICS stack = MESSAGE_TEXT;
        RAISE NOTICE E'--- Call Stack ---\n%', stack ;
        returnvalue = 0;
    end;
END
$BODY$;