PostgreSQL存储过程(1)-基于SQL的存储过程

1|0什么是SQL函数？

• SQL函数包体是一些可执行的SQL语言。同时包含1条以上的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。
• 除非SQL函数声明为返回void，否则最后一条语句必须是SELECT
• 在简单情况下，返回最后一条查询结果的第一行。
• 如果最后一个查询不返回任何行，那么该函数将返回NULL值。
• 如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。

2|0模板

CREATE OR REPLACE FUNCTION function_name([ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ])
[RETURNS rettype]
AS $$
    $BODY$;
$$ LANGUAGE SQL;

3|0案例讲解

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

CREATE OR REPLACE FUNCTION add(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
    SELECT a+b;
$$ LANGUAGE SQL;

1|0调用方法

lottu=# select add(2,3);
 add 
-----
   5
(1 row)

lottu=# select * from add(2,3);
 add 
-----
   5
(1 row)

1|0知识点：

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

CREATE OR REPLACE FUNCTION add3(NUMERIC,NUMERIC)
RETURNS NUMERIC
AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;

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

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

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

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

1|0调用方法

lottu=# select add1(7,4);
 add1 
------
   11

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

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

CREATE OR REPLACE FUNCTION plus_and_minus(IN a INTEGER, IN b 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; 

1|0调用方法

lottu=# select * from  plus_and_minus(7,5);
 c | d  
---+----
 2 | 12

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

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

CREATE OR REPLACE FUNCTION delete_t()
RETURNS void
AS $$
    delete from t where id > 999;
$$ LANGUAGE SQL;

1|0调用方法

lottu=# select delete_t();
 delete_t 
----------

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

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

CREATE OR REPLACE FUNCTION select_emp_1()
RETURNS numeric
AS $$
    select empno from emp;
$$ LANGUAGE SQL;

1|0调用方法

lottu=# select select_emp_1();
 select_emp_1 
--------------
         7369

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

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

CREATE OR REPLACE FUNCTION select_emp_2()
RETURNS setof numeric
AS $$
    select empno from emp;
$$ LANGUAGE SQL;

1|0调用方法

lottu=# select select_emp_2();
 select_emp_2 
--------------
         7369
         7499
         7521
         7566
         7654
         7698
         7782
         7788
         7839
         7844
         7876
         7900
         7902
         7934

__EOF__

本文作者：lottu
本文链接：https://www.cnblogs.com/lottu/p/7404722.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！