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函数,如:

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

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

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

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

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

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

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

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

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

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

基于PL/PgSQL的存储过程

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

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

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

PL/pgSQ结构如:

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

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

变量声明的一般语法是:

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

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

PL/PgSQL的存储过程的参数

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

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

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

PL/PgSQL的存储过程的注释

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

  1. 第一种是使用 双-(双减号)它的作用范围是只能在一行有效(类似java 单行注释//)
  2. 第二种就是一行或多行注释,使用的是/* */表示
简单的一个PL/PgSQL的存储过程例子

PL/PgSQL的存储过程的异常错误处理

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

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

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

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

例如:

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


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

 


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

4条回应:“postgreSQL之存储过程”

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注