postgreSQL之存储过程 发布于 6 个月前 (10月23日) – 1524 次检阅

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

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