做网站 就上凡科建站,视频网站建设费用明细,网站开发实现电脑版和手机版的切换,网络服务器品牌排名postgresql-存储过程 简述PL/pgSQL 代码块结构示例嵌套子块 声明与赋值控制结构IF 语句CASE 语句简单case语句搜索 CASE 语句 循环语句continuewhilefor语句遍历查询结果 foreach 游标游标传参 错误处理报告错误和信息检查断言 捕获异常自定义函数重载VARIADIC 存储过程示例事务… postgresql-存储过程 简述PL/pgSQL 代码块结构示例嵌套子块 声明与赋值控制结构IF 语句CASE 语句简单case语句搜索 CASE 语句 循环语句continuewhilefor语句遍历查询结果 foreach 游标游标传参 错误处理报告错误和信息检查断言 捕获异常自定义函数重载VARIADIC 存储过程示例事务管理 简述
除了标准 SQL 语句之外PostgreSQL 还支持使用各种过程语言例如 PL/pgSQL、C、PL/Tcl、 PL/Python、PL/Perl、PL/Java 等 创建复杂的过程和函数称为存储过程Stored Procedure 和自定义函数User-Defined Function。存储过程支持许多过程元素例如控制结构、循环和 复杂的计算。
使用存储过程带来的好处包括
减少应用和数据库之间的网络传输。所有的 SQL 语句都存储在数据库服务器中应用程 序只需要发送函数调用并获取除了结果避免了发送多个 SQL 语句并等待结果提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。可重用性。存储过程和函数的功能可以被多个应用同时使用。 当然使用存储过程也可能带来一些问题导致软件开发缓慢。因为存储过程需要单独学习而且很多开发人员并不具备这种技能。不易进行版本管理和代码调试。不同数据库管理系统之间无法移植语法存在较大的差异。 本文主要介绍 PL/pgSQL 存储过程它和 Oracle PL/SQL 非常类似是 PostgreSQL 默认支 持的存储过程。使用 PL/pgSQL 的原因包括PL/pgSQL 简单易学无论是否具有编程基础都能够很快学会。PL/pgSQL 是 PostgreSQL 默认支持的过程语言PL/pgSQL 开发的自定义函数可以和内置 函数一样使用。PL/pgSQL 提高了许多强大的功能例如游标可以实现复杂的函数。
PL/pgSQL 代码块结构
/** label 是一个可选的代码块标签可以用于 EXIT 语句退出指定的代码块或者限定变量的名称* DECLARE 是一个可选的声明部分用于定义变量* BEGIN 和 END 之间是代码主体也就是主要的功能代码所有的语句都使用分号;结束* END 之后的分号表示代码块结束。* */
[ label ]
[ DECLAREdeclarations ]
BEGINstatements;...
END [ label ];
示例
/** 个匿名块与此相对的是命名块也就是存储过程和函数。其中DO 语句用于* 执行匿名块我们定义了一个字符串变量 name然后给它赋值并输出一个信息RAISE NOTICE用于输出通知消息。* $$用于替换单引号* */
do $$
declare name text;
beginname :PL/pgSQL;raise notice Hello %!,name; end $$;/** $$用于替换单引号因为 PL/pgSQL 代码主体必须是字符串文本* 意味着代码中所有的单引号都必须转义重复写两次
*/
DO
DECLAREname text;
BEGINname : PL/pgSQL;RAISE NOTICE Hello %!, name;
END ;嵌套子块
PL/pgSQL 支持代码块的嵌套也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代 码块被称为子块subblock包含子块的代码块被称为外部块subblock。子块可以将代码 进行逻辑上的拆分子块中可以定义与外部块重名的变量而且在子块内拥有更高的优先级
DO $$
outer_block
declarename text;
BEGINname : outer_block;RAISE NOTICE This is %, name;DECLAREname text : sub_block;BEGINRAISE NOTICE This is %, name;RAISE NOTICE The name FROM the outer block is %, outer_block.name;END;RAISE NOTICE This is %, name;END outer_block $$;声明与赋值
/*variable_name 是变量的名称通常需要指定一个有意义的名称data_type 是变量的
类型可以是任何 SQL 数据类型如果指定了 NOT NULL必须使用后面的表达式为变量指定
初始值。赋值使用:*/
variable_name data_type [ NOT NULL ] [ { DEFAULT | : | } expression ];DO $$
declare
id integer;
-- price默认值是0
price numeric(5,2) default 0.0;
name text;
url varchar :http://mysite.com;
--行类型的变量可以存储查询语句返回的数据行数据行的结构要和 employees相同
myrow employees%rowtype;
-- myfield 的数据类型取决于 empoyees.first_name 字段的定义
myfield employees.first_name%type;
-- myprice 和 price 的类型一致。
myprice price%type;
-- 记录类型变量
-- 记录类型的变量没有预定义的结构只有当变量被赋值时才确定而且可以在运行时被改变。
-- 记录类型的变量可以用于任意查询语句或者 FOR 循环变量
arow RECORD;
-- 使用 ALIAS 定义一个变量别名
myprice1 ALIAS FOR myprice;
-- 在定义变量时指定了 CONSTANT 关键字意味着定义的是常量。常量的值需要在声明时初始化并且不能修改
PI CONSTANT NUMERIC : 3.14159265;
beginid : 1;name :tony;raise notice id %,id;raise notice price %,price;raise notice name %,name;raise notice url %,url;raise notice 常量PI%,PI;
end $$;控制结构
IF 语句
IF … THEN … END IFIF … THEN … ELSE … END IFIF … THEN … ELSIF … THEN … ELSE … END IF
-- 简单的if语句
DO $$
beginif 21 thenraise notice 2大于1;end if;
end $$;-- if ... else
DO $$
beginif 23 thenraise notice 2大于3;elseraise notice 2不大于3;end if;
end $$;-- if ... else 多个条件分支
DO $$
beginif 23 thenraise notice 2大于3;elseif 23 thenraise notice 2等于3;elseraise notice 2不大于3;end if;
end $$;CASE 语句
CASE 语句同样可以根据不同的条件执行不同的分支语句。CASE 语句分为两种简单 CASE 语句和搜索 CASE 语句。
简单case语句
-- 语法
CASE search-expressionWHEN expression [, expression [ ... ]] THENstatements[ WHEN expression [, expression [ ... ]] THENstatements... ][ ELSEstatements ]
END CASE;-- case 简单语句
DO $$
declare i integer : 1;
begincase iwhen 1,2 thenraise notice 1或者2;when 3 thenraise notice 3;elseraise notice 其他值;end case;
end $$;搜索 CASE 语句
-- 语法
CASEWHEN boolean-expression THENstatements[ WHEN boolean-expression THENstatements... ][ ELSEstatements ]
END CASE;-- 搜索case 简单语句
DO $$
declare i integer : 25;
begincase when i between 1 and 10 thenraise notice [1-10];when i between 11 and 20 thenraise notice [11-20];elseraise notice 其他值;end case;
end $$;循环语句
PostgreSQL 提供 4 种循环执行命令的语句LOOP、WHILE、FOR 和 FOREACH 循环以 及循环控制的 EXIT 和 CONTINUE 语句。
-- LOOP 用于定义一个无限循环语句
-- 一般需要使用 EXIT 或者 RETURN 语句退出循环label 可以用于 EXIT 或者 CONTINUE 语
-- 句退出或者跳到执行的嵌套循环中
[ label ]
LOOPstatements
END LOOP [ label ];-- loop循环
DO $$
declare i integer : 1;
beginloop-- exit退出循环exit when i 5;raise notice 第%次执行!,i;i :i1;end loop;
end $$;continue
-- loop循环
DO $$
declare i integer : 0;
beginloop-- exit退出循环exit when i 10;i : i1;-- continue 忽略后面的语句直接进入下一次循环-- mod(i,2) 0;偶数跳过执行continue when mod(i,2) 0;raise notice 第%次执行!,i;end loop;
end $$;while
-- 语法
-- 当表达式 boolean-expression 的值为真时循环执行其中的语句然后重新计算表达式的值
-- 当表达式的值假时退出循环
[ label ]
WHILE boolean-expression LOOPstatements
END LOOP [ label ];-- while语句
DO $$
declare i integer : 0;
beginwhile i 10 loopi : i1;raise notice 第%次执行,i;end loop;
end $$;for语句
-- 语法
-- FOR 循环默认从小到大进行遍历REVERSE 表示从大到小遍历BY 用于指定每次的增量
-- 默认为 1
[ label ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOPstatements
END LOOP [ label ];-- for语句
DO $$
begin-- 变量 i 不需要提前定义可以在 FOR 循环内部使用。-- by 2每次增量为2for i in reverse 20..10 by 2 loopraise notice 第%次循环!,i;end loop;
end $$;-- for语句
DO $$
begin-- 变量 i 不需要提前定义可以在 FOR 循环内部使用。-- by 2每次增量为2for i in 1..10 by 2 loopraise notice 第%次循环!,i;end loop;
end $$;遍历查询结果
-- 语法
-- target 可以是一个 RECORD 变量、行变量或者逗号分隔的标量列表。在循环中target
-- 代表了每次遍历的行数据。
[ label ]
FOR target IN query LOOPstatements
END LOOP [ label ];-- for语句遍历查询结果
DO $$
declare
emp record;
beginfor emp in (select * from employees limit 5) loopraise notice 员工信息: %, %, %,emp.first_name,emp.last_name,emp.salary;end loop;
end $$;foreach
-- 语法
[ label ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOPstatements
END LOOP [ label ];如果没有指定 SLICE 或者指定 SLICE 0FOREACH 将会变量数组中的每个元素
do $$
declare
x int;
beginforeach x in array (array[[1,2,3],[4,5,6]]) loopraise notice x %, x;end loop;
end $$;do $$
declare
x int[];
begin
-- 如果指定了一个正整数的 SLICEFOREACH 将会变量数组的切片SLICE 不能大于数组的维度。foreach x slice 1 in array (array[[1,2,3],[4,5,6]]) loopraise notice x %, x;end loop;
end $$;游标
PL/pgSQL 游标允许我们封装一个查询然后每次处理结果集中的一条记录。游标可以将大 结果集拆分成许多小的记录避免内存溢出另外我们可以定义一个返回游标引用的函数然 后调用程序可以基于这个引用处理返回的结果集 使用游标的步骤大体如下
声明游标变量打开游标从游标中获取结果判断是否存在更多结果。如果存在执行第 3 步否则执行第 5 步关闭游标
do $$
declare
-- 声明变量类型为recordemp record;emp_cur cursor for select * from employees limit 10;
begin
-- 打开游标open emp_cur;loop-- 获取 fetchfetch emp_cur into emp;-- 退出循环条件 when not found 没有找到数据exit when not found ;raise notice 员工信息:% ,%,emp.first_name,emp.last_name;end loop;-- 关闭游标close emp_cur;
end $$;游标传参
do $$
declare
-- 声明变量类型为recordemp record;emp_cur cursor(dept_id integer) for select * from employees where department_id dept_id limit 10;
begin
-- 打开游标open emp_cur(90);loop-- 获取 fetchfetch emp_cur into emp;-- 退出循环条件 when not found 没有找到数据exit when not found ;raise notice 员工信息:% ,%,emp.first_name,emp.last_name;end loop;-- 关闭游标close emp_cur;
end $$;声明了一个游标 emp_cur并且绑定了一个查询语句通过一个参数 dept_id 获取指 定部门的员工然后使用 open 打开游标接着在循环中使用 fetch 语句获取游标中的记录 如果没有找到更多数据退出循环语句变量 emp 用于存储游标中的记录最后使用 close 语句关闭游标释放资源 官网介绍
错误处理
报告错误和信息
PL/pgSQL 提供了 RAISE 语句用于打印消息或者抛出错误
RAISE level format;不同的 level 代表了错误的不同严重级别包括
DEBUGLOGNOTICEINFOWARNINGEXCEPTION 我们经常使用 NOTICE 输出一些信息。如果不指定 level默认为 EXCEPTION 将会抛出异常并且终止代码运行
format 是一个用于提供信息内容的字符串可以使用百分号%占位符接收参数的值 两 个连写的百分号%%表示输出百分号自身
DO $$
BEGINRAISE DEBUG This is a debug text.;RAISE INFO This is an information.;RAISE LOG This is a log.;RAISE WARNING This is a warning at %, now();RAISE NOTICE This is a notice %%;
END $$;从结果可以看出并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参 数 client_min_messages和 log_min_messages 进行设置。 对于 EXCEPTION 级别的错误可以支持额外的选项
RAISE [ EXCEPTION ] format USING option expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE sqlstate USING option expression [, ... ];
RAISE [ EXCEPTION ] USING option expression [, ... ];其中option 可以是以下选项
MESSAGE设置错误消息。如果 RAISE 语句中已经包含了 format 字符串不能再使用该选项。DETAIL指定错误详细信息。HINT设置一个提示信息ERRCODE指定一个错误码SQLSTATE。可以是文档中的条件名称或者五个字符组成的SQLSTATE 代码。COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA返回相关对象的名称。
do $$
beginraise info This s an info.;raise debug This s an debug.;raise warning This s an warning.;
end $$;检查断言
PL/pgSQL 提供了 ASSERT 语句用于调试存储过程和函数
ASSERT condition [ , message ];其中condition 是一个布尔表达式如果它的结果为真ASSERT 通过如果结果为假或者 NULL将会抛出 ASSERT_FAILURE 异常。message 用于提供额外的错误信息默认为“assertion failed”。例如
DO $$
DECLAREi integer : 1;
BEGINASSERT i 0, i 的初始值应该为 0;
END $$;ASSERT 只适用于代码调试输出错误信息使用 RAISE 语句。
捕获异常
默认情况下PL/pgSQL 遇到错误时会终止代码执行同时撤销事务。我们也可以在代码块 中使用 EXCEPTION 捕获错误并继续事务
[ label ]
[ DECLAREdeclarations ]
BEGINstatements
EXCEPTIONWHEN condition [ OR condition ... ] THENhandler_statements[ WHEN condition [ OR condition ... ] THENhandler_statements... ]
END;如果代码执行出错程序将会进入 EXCEPTION 模块依次匹配 condition找到第一个匹 配的分支并执行相应的 handler_statements如果没有找到任何匹配的分支继续抛出错误
DO $$
DECLAREi integer : 1;
BEGINi : i / 0;
EXCEPTIONWHEN division_by_zero THENRAISE NOTICE 除零错误;WHEN OTHERS THENRAISE NOTICE 其他错误;
END $$;OTHERS 用于捕获未指定的错误类型。 PL/pgSQL 还提供了捕获详细错误信息的 GET STACKED DIAGNOSTICS 语句具体可以参 考官方文档
自定义函数
创建一个自定义的 PL/pgSQL 函数可以使用 CREATE FUNCTION 语句
CREATE [ OR REPLACE ] FUNCTIONname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | } default_expr ]
[, ...] ] )RETURNS rettype
AS $$
DECLAREdeclarations
BEGINstatements;...
END; $$
LANGUAGE plpgsql;CREATE 表示创建函数 OR REPLACE 表示替换函数定义 name 是函数名括号内是参数多个参数使用逗号分隔 argmode 可以是 IN输入、OUT输出、 INOUT输入输出或者 VARIADIC数量可变默认为 IN argname 是参数名称argtype 是参数的类型 default_expr是参数的默认值rettype 是返回数据的类型 AS 后面是函数的定义和上文中的匿名块相同 最后LANGUAGE 指定函数实现的语言也可以是其他过程语言
-- 函数创建
create or replace function get_emp_count(p_deptid integer)
returns integer
as $$
declare ln_count integer;
begin if p_deptid 0 thenraise exception 部门编号不能小于等于0!p_deptid:%,p_deptid;end if;select count(*) into ln_countfrom employeeswhere department_id p_deptid; return ln_count;
end $$
language plpgsql;-- 函数使用
select get_emp_count(90) as total;select get_emp_count(-1) as total;重载
PL/pgSQL 函数支持重载Overloading也就是相同的函数名具有不同的函数参数
CREATE OR REPLACE FUNCTION public.get_emp_count(p_deptid integer,p_hire_date varchar)RETURNS integerLANGUAGE plpgsql
AS $function$
declare ln_count integer;
begin if p_deptid 0 thenraise exception 部门编号不能小于等于0!p_deptid:%,p_deptid;end if;select count(*) into ln_countfrom employeeswhere department_id p_deptidand hire_date p_hire_date;return ln_count;
end $function$
;VARIADIC
VARIADIC参数的数量是多个可变的
-- 数组 nums的下标索引的产生1维数组
-- generate_subscripts(nums,1)
create or replace function sum_num(variadic nums numeric[])
returns numeric
as $$
declare total numeric;
begin select sum(nums[i]) into total-- t(i)返回的下标索引变量为ifrom generate_subscripts(nums,1) t(i);return total;
end $$
language plpgsql;
-- 数组内元素为1和2的和
-- 参数 nums 是一个数组可以传入任意多个参数然后计算它们的和值
select sum_num(1,2);存储过程
PostgreSQL 11 增加了存储过程使用 CREATE PROCEDURE 语句创建
CREATE [ OR REPLACE ] PROCEDUREname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | } default_expr ]
[, ...] ] )
AS $$
DECLAREdeclarations
BEGINstatements;...
END; $$
LANGUAGE plpgsql;-- 调用存储过程
call 过程名(参数1参数2...);示例
create or replace procedure transfer(p_from integer,p_to integer,p_amount numeric)
as $$
declare l_count integer;
begin select count(*) into l_countfrom accountswhere id p_from;if l_count 0 thenraise exception 原账户不存在%,p_from;end if;select count(*) into l_countfrom accountswhere id p_to;if l_count 0 thenraise exception 目标账户不存在%,p_to;end if;if p_amount 0 thenraise exception 转账金额错误%,p_amount;end if;-- 1.扣款update accounts set balance balance - p_amountwhere id p_from;-- 2.存款update accounts set balance balance p_amountwhere id p_to;-- 3.记录流水
end $$
language plpgsql;
-- 调用存储过程
call public.transfer(1,2,3000);select * from public.accounts a ;事务管理
在存储过程内部可以使用 COMMIT 或者 ROLLBACK 语句提交或者回滚事务
create table test2(col integer);-- 创建存储过程
create or replace procedure transaction_test()
as $$
begin for i in 0..10 loopinsert into test2 values(i);--mod 求余-- 偶数提交if mod(i,2) 0 thencommit;else-- 奇数回滚rollback;end if;end loop;end $$
language plpgsql;
-- 调用存储过程
call transaction_test();select * from test2;
