postgreSQL之存储过程
- 196 次检阅

以下是自己结合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的设计目标是创建一种这样的可载入过程语言:

  • 可以被用来创建函数和触发器过程
  • 对SQL语言增加控制结构
  • 可以执行复杂计算
  • 继承所有用户定义类型、函数和操作符
  • 可以被定义为受服务器信任
  • 便于使用
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条评论

  1. NO:3044 / 發布於 - 2019-10-24 #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. NO:3050 / 發布於 - 2019-11-8 #2 回复

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

    • 發布於 - 2019-11-8 回复
      郭良俊只狗

      广东羊城技工学校 噗~牛逼阿..算了一看就是大神了,说实话,自己写博客就是为了记录而不是强迫自己写多少。还有就是关于界面问题的话,一个字懒

发表评论