postgreSQL之存储过程 – 记忆角落

postgreSQL之存储过程

/ 4评 / 5

以下是自己结合postgreSQL文档手册总结整理的入门手札,如有不严谨,请到官方文档手册查看:http://www.postgres.cn/docs/10/index.html


基于SQL的存储过程

什么是sql函数?
  1. SQL函数包体是一些可执行的SQL语言。同时包含1条以上的查询,但是函数只返回最后一个查询(必须是SELECT)的结果。
  2. 简单情况下,返回最后一条查询结果的第一行。
  3. 如果最后一个查询正好根本不返回行,将会返回空值。
  4. 除非SQL函数声明为返回void,否则最后一条语句必须是SELECT
  5. 如果需要该函数返回最后一条SELECT语句的所有行,可以将函数的返回值定义为集合,即SETOF sometype,或者等效地声明它为RETURNS TABLE(columns)

创建sql函数,如:

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

接下来用一个例子,如最简单的SQL函数没有参数并且简单地返回一个基本类型:

CREATE or replace FUNCTION one() 
	RETURNS  integer AS 
$body$
    SELECT 1;
$body$ 
LANGUAGE SQL;
----------
select one();
------
one
1

如创建一个add函数,返回两者的入参值:

CREATE or replace FUNCTION add(a integer,b integer) 
	RETURNS  integer AS 
$body$
    SELECT a+b;
$body$ 
LANGUAGE SQL;
------
select add(1,2)
----
add
3

还有另外一种方式,即能省掉参数名而使用数字$n,$n这样的标识符来使用参数,如:

CREATE or replace FUNCTION add(a integer,b integer) 
	RETURNS  integer AS 
$body$
    SELECT $1+$2;
$body$ 
LANGUAGE SQL;

但个人觉得这种方式不怎么友好,就是可读性鸡巴的差~

上面的两个例子,都是只有入参,而没出参,接下来这个例子将同时包含输入参数和输出参数;由于存在输出参数;这里不需要returns部分,如:

CREATE OR REPLACE FUNCTION add1(in a integer, in b integer,out c integer)
AS $body$
    SELECT a+b;
$body$ 
LANGUAGE SQL;
-----------------
select add1(1,2);
---------------
add1
3

还记得之前sql函数的介绍吗?sql函数包体是一些可执行的SQL语言。同时包含1条以上的查询,但是函数只返回最后一个查询(必须是SELECT)的结果。所以,在sql函数包体中,不一定是SELECT语句,可以是其它任意合法的SQL。但最后一条SQL必须是SELECT语句并且该SQL的结果将作为该函数的输出结果。接下来就做一个测试,如:

CREATE OR REPLACE FUNCTION add2(in a integer, in b integer,out c integer,out d integer)
AS 
$body$
	SELECT a+b,a*b;
	delete from test_array where id =1;
	SELECT a+b+a,a*b*a;
		
$body$ 
LANGUAGE SQL;
--------
select * from add2(1,2);
---------
c	d
4	2

继续上面的sql函数介绍-->除非SQL函数声明为返回void,否则最后一条语句必须是SELECT,接下来我这边就直接返回void,所以就可以没有select,如:

CREATE OR REPLACE FUNCTION delete_array(id integer)
RETURNS void AS 
$body$
	delete from test_array where id =id;
$body$ 
LANGUAGE SQL;

还有就是,当一个表里面含有多条记录咋办呢?带着疑问看例子吧骚年:

CREATE OR REPLACE FUNCTION select_array_er()
RETURNS integer AS 
$body$
	select id from test_array_er;
$body$ 
LANGUAGE SQL;
------
select  select_array_er();//我这边id就只有两条,1和2
-------
select_array_er
2

所以,实践的真理告诉我们,是返回最后结果行。 等等,你肯定会问,那么我要返回所有id怎么弄?来,叔叔带你去看金鱼:

CREATE OR REPLACE FUNCTION select_array_er2()
RETURNS setof INTEGER AS 
$body$
	select id from test_array_er;
$body$ 
LANGUAGE SQL;
-----
select  select_array_er2();
-----
select_array_er2
2
1

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

基于PL/PgSQL的存储过程

PL/pgSQL是一种用于PostgreSQL数据库系统的可载入的过程语言。PL/pgSQL的设计目标是创建一种这样的可载入过程语言:

PL/PgSQL的存储过程的块结构

PL/pgSQL是一个块结构语言。函数定义的所有文本都必须是一个块。 一个块用下面的方法定义:

PL/pgSQ结构如:

[ <<label>> ]
[ DECLARE
    declarations ]
    --声明部分: 在此声明PL/SQL用到的变量,类型及游标.
BEGIN
    statements
    -- 执行部分:  过程及SQL语句,即程序的主要部分
EXCEPTION
   -- 执行异常部分: 错误处理    
END [ label ];

在一个块中的每一个声明和每一个语句都由一个分号终止。如上所示,出现在另一个块中的块必须有一个分号在END之后。不过最后一个结束函数体的END不需要一个分号。

PL/PgSQL的存储过程的变量声明

变量声明的一般语法是:

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

PL/pgSQL变量可以是任意 SQL 数据类型,例如integer、varchar和char。

这跟oracle中的存储过程声明变量是一样的 例如:

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;--行类型
myfield tablename.columnname%TYPE;--复制类型
arow RECORD;--记录类型
PL/PgSQL的存储过程的参数

声明一个参数的语法如下:

name [in|out|in out] type

如果只指定输入参数类型,不指定参数名,则函数体里一般用$1,$n这样的标识符来使用参数。如:

CREATE OR REPLACE FUNCTION demo(NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN $1 * 0.8;
END;
$$ 
LANGUAGE PLPGSQL;

但该方法可读性不好,最后还是在声明参数类型时同时声明相应的参数名,如:

CREATE OR REPLACE FUNCTION demo(total NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN total * 0.8;
END;
$$ 
LANGUAGE PLPGSQL;
PL/PgSQL的存储过程的注释

在PL/SQL里,可以使用两种符号来写注释,即:

  1. 第一种是使用 双-(双减号)它的作用范围是只能在一行有效(类似java 单行注释//)
  2. 第二种就是一行或多行注释,使用的是/* */表示
简单的一个PL/PgSQL的存储过程例子
CREATE OR REPLACE FUNCTION somefunc() 
	RETURNS integer AS $body$
DECLARE
    somenum integer := 30;
BEGIN
    -- 这是单行注释
    RAISE NOTICE '这是一条提示语 %', somenum;
    somenum := 50;
	/*
		这是多行注释
	*/
    RAISE NOTICE 'somenum here is %', somenum;
    RETURN somenum;
	--错误捕获
	EXCEPTION
		WHEN others THEN    
		RAISE EXCEPTION '(%)', SQLERRM;
END;
$body$
LANGUAGE plpgsql;
PL/PgSQL的存储过程的异常错误处理

在上面例子,其实已经有了处理异常错误的语句了。具体它是干嘛的呢?

其实阿,在PL/pgSQL函数中,如果没有异常捕获,函数会在发生错误时直接退出,与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式:

[ <> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
  WHEN condition [ OR condition ... ] THEN
            handler_statements
  WHEN condition [ OR condition ... ] THEN
         handler_statements
END;

如果没有错误发生,只有BEGIN块中的statements会被正常执行,然而一旦这些语句中有任意一条发生错误,其后的语句都将被跳过,直接跳转到 EXCEPTION块的开始处。此时系统将搜索异常条件列表,寻找匹配该异常的第一个条件,如果找到匹配,则执行相应的 handler_statements,之后再执行END的下一条语句。如果没有找到匹配,该错误就会被继续向外抛出,其结果与没有EXCEPTION子 句完全等同。如果此时handler_statements中的语句发生新错误,它将不能被该EXCEPTION子句捕获,而是继续向外传播,交由其外层 的EXCEPTION子句捕获并处理。

在PostgreSQL中可以利用RAISE语句报告信息和抛出错误,其声明形式为:

RAISE level 'format' [, expression [, ...]];

例如:

raise notice 'My name is %, I am a %.', 'glj', 'Handsome boy';

更多可到:https://199604.com/1339 查看


今晚就先写到这里吧...写得太多了...

 


码字挺辛苦哒~要不打个赏吧

《“postgreSQL之存储过程”》 有 4 条评论

  1. 大致说道:

    FEED里有警告,说函数未定义:
    Warning: Use of undefined constant is_robots – assumed ‘is_robots’ (this will throw an Error in a future version of PHP) in /www/wwwroot/199604.com/wp-content/themes/dount/functions.php on line 124

  2. 博主的网站还是很不错的,不过更新要有节奏。还有就是博主,我建议你,网站的界面可以适当修整一下,例如,块的边框可以去掉,这样整站看起来才不会花,才会大气。今天看了你一篇文章解决了一些问题,谢谢。

发表评论

您的电子邮箱地址不会被公开。