[大数据]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,
	provincename character varying,
	cityname character varying,
	districtname character varying,
	streetname character varying,
	isshow integer,
	INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
begin
	--添加公告
    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)

-- DROP PROCEDURE public.pr_notice_info_edit(integer, character varying, text, 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,
	INOUT returnvalue integer)
LANGUAGE 'plpgsql'
AS $BODY$
declare stack text;
BEGIN
	-- 修改公告
	 update t_notice_info
    set notice_title=noticetitle,notice_content=noticecontent,province_id=provinceid,city_id=cityid,district_id=districtid,
	street_id=streetid
	--order_weight=orderweight
    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$;

?