以下介绍在navicat中如何创建pgsql的存储过程
一、创建
在左边菜单的函数-》新建函数,输入存储过程名字,会自动生成存储过程模板代码
此时直接保存,并点击运行,可以看到存储过程可以正常执行,不过此时我们没有写任何执行内容,定义的输出类型是void,所以输出结果也是空的。执行结果:
二、执行具体操作语句
以上语句保存后重新打开设计函数时,会自动重构语句,设计工具会自动添加一些语句比如COST 100,不影响我们的原执行语句。
以下添加入参和update语句,并把返回值修改为 integer,最后一句为return 1;代码如下:
CREATE OR REPLACE FUNCTION "public"."mydemo"("vname" varchar)
RETURNS "pg_catalog"."int4" AS $BODY$BEGIN
UPDATE t_sys_oper SET bz=vname
WHERE fid=651;
RETURN 1;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
运行函数后,update语句被执行,输出结果为1。
以上完成了最简单的存储过程示例。
如何调用存储过程
前端可以像查询表一样调用存储过程,如下:
select * from mydemo(‘abc’);
三、返回数据集结果
上述示例返回的是integer这样的基本类型,如果我们要返回数据集的话,有以下几种方式。
方式1.使用“SETOF 类型名”
CREATE OR REPLACE FUNCTION "public"."mydemo"("vname" varchar)
RETURNS SETOF "public"."t_sys_oper" AS $BODY$BEGIN
RETURN query
SELECT * FROM t_sys_oper;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
返回值设置为 “SETOF 类型名”方式,其中类型名是已经定义的类型,比如表、视图或在类型列表中自定义的类型。
在查询语句前面加上 “RETURN QUERY” 即可返回查询语句结果。
执行后结果如下:
可以看到执行结果中数据都是以逗号间隔输出,并不是以表字段的方式。没有关系,实际调用时是
我们是可以按正常字段取值方式获取字段值就可以了。
方式2.动态输出表字段
例1的输出类型是已存在的表或视图类型,但我们经常需要动态输出字段,此时可以定义TABLE类型,注意定义的字段类型和个数必须与select查询结果一致。并且需要设置表别名,否则会出现: 字段关联 "fid" 是不明确的错误提示。
CREATE OR REPLACE FUNCTION "public"."mydemo"("vname" varchar)
RETURNS TABLE("fid" int4) AS $BODY$BEGIN
RETURN query
SELECT ts.fid FROM t_sys_oper ts;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
以上存储过程的简单示例介绍完毕。