pg 事务 存储过程_PostgreSQL存储过程(1)-基于SQL的存储过程

什么是SQL函数？

SQL函数包体是一些可执行的SQL语言。同时包含1条以上的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。

除非SQL函数声明为返回void，否则最后一条语句必须是SELECT

在简单情况下，返回最后一条查询结果的第一行。

如果最后一个查询不返回任何行，那么该函数将返回NULL值。

如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。

模板

CREATE OR REPLACE FUNCTION function_name([[ argmode] [argname] argtype [{ DEFAULT | = } default_expr] [, ...]])[RETURNS rettype]

AS$$

$BODY$;

$$ LANGUAGE SQL;

案例讲解

案例1：编写一个add函数;返回值为参数1+参数2.

CREATE OR REPLACE FUNCTION add(a NUMERIC, b NUMERIC)RETURNSNUMERICAS$$SELECT a+b;

$$ LANGUAGE SQL;

调用方法

lottu=# select add(2,3);add

-----

5(1row)

lottu=# select * from add(2,3);add

-----

5(1 row)

知识点：

声明函数参数;以add函数来演示如果只指定输入参数类型，不指定参数名，则函数体里一般用$1，$n这样的标识符来使用参数。

CREATE OR REPLACE FUNCTIONadd3(NUMERIC,NUMERIC)RETURNSNUMERICAS$$SELECT $1 + $2;

$$ LANGUAGE SQL;

但该方法可读性不好，建议还是采用前面一种的方法。

案例2：编写一个add1函数;返回值为参数1+参数2.

上面这种方式参数列表只包含函数输入参数，不包含输出参数。下面这个例子将同时包含输入参数和输出参数;由于存在输出参数；这里不需要returns部分

CREATE OR REPLACE FUNCTION add1(in a NUMERIC, inb NUMERIC,out c numeric)AS$$SELECT a+b;

$$ LANGUAGE SQL;

调用方法

lottu=# select add1(7,4);

add1------

11

案例3：编写一个plus_and_minus函数;返回值为参数1+参数2,参数1-参数2.

在函数定义中，可以写多个SQL语句，不一定是SELECT语句，可以是其它任意合法的SQL。但最后一条SQL必须是SELECT语句并且该SQL的结果将作为该函数的输出结果。

CREATE OR REPLACE FUNCTION plus_and_minus(IN a INTEGER, INb NUMERIC, OUT c NUMERIC, OUT d NUMERIC)AS$$SELECT a+b, a-b;delete from t where id > 9999;SELECT a-b, a+b;

$$ LANGUAGE SQL;

调用方法

lottu=# select * from plus_and_minus(7,5);

c|d---+----

2 | 12

案例4: 编写一个不需要返回结果的delete_t函数

上面例子都有返回结果；且最后一条SQL必须是SELECT语句。假如需求不需要返回结果；请看下例。

CREATE OR REPLACE FUNCTIONdelete_t()RETURNSvoidAS$$delete from t where id > 999;

$$ LANGUAGE SQL;

调用方法

lottu=# selectdelete_t();

delete_t----------

案例5：若表EMP存在多条记录；结果会是？

返回最后一条查询结果的第一行

CREATE OR REPLACE FUNCTIONselect_emp_1()RETURNSnumericAS$$select empno fromemp;

$$ LANGUAGE SQL;

调用方法

lottu=# selectselect_emp_1();

select_emp_1--------------

7369

案例6：返回表emp所有记录。

若需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。

CREATE OR REPLACE FUNCTIONselect_emp_2()RETURNSsetof numericAS$$select empno fromemp;

$$ LANGUAGE SQL;

调用方法

lottu=# selectselect_emp_2();

select_emp_2--------------

7369

7499

7521

7566

7654

7698

7782

7788

7839

7844

7876

7900

7902

7934