记忆角落

  • {{ item.name }}
  • 基于SQL的存储过程
  • 什么是sql函数?
  • 基于PL/PgSQL的存储过程
  • PL/PgSQL的存储过程的块结构
  • PL/PgSQL的存储过程的变量声明
  • PL/PgSQL的存储过程的参数
  • PL/PgSQL的存储过程的注释
  • 简单的一个PL/PgSQL的存储过程例子
  • PL/PgSQL的存储过程的异常错误处理
  • 首页
  • 关于
  • 归档
  • 邻居
  • 捐赠
  • 速查表

postgreSQL之存储过程

  • 郭良俊只狗
  • 2019-10-23
  • 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的设计目标是创建一种这样的可载入过程语言：

  • 可以被用来创建函数和触发器过程
  • 对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 查看

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

 

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

© 2012 - 2024 记忆角落 网站统计    由又拍云CDN赞助 提供云储存服务
Theme by Wing 粤ICP备14056850号