这篇文章主要介绍了PostgreSQL存储过程源码分析的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇PostgreSQL存储过程源码分析文章都会有所收获,下面我们一起来看看吧。
PL/pgSQL 游标允许我们封装一个查询,然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录,避免内存溢出;另外,我们可以定义一个返回游标引用的函数,然后调用程序可以基于这个引用处理返回的结果集。
使用游标的步骤大体如下:
声明游标变量;
打开游标;
从游标中获取结果;
判断是否存在更多结果。如果存在,执行第 3 步;否则,执行第 5 步;
关闭游标。
我们直接通过一个示例演示使用游标的过程:
DO $$ DECLARE rec_emp RECORD; cur_emp CURSOR(p_deptid INTEGER) FOR SELECT first_name, last_name, hire_date FROM employees WHERE department_id = p_deptid; BEGIN -- 打开游标 OPEN cur_emp(60); LOOP -- 获取游标中的记录 FETCH cur_emp INTO rec_emp; -- 没有找到更多数据时退出循环 EXIT WHEN NOT FOUND; RAISE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date; END LOOP; -- Close the cursor CLOSE cur_emp; END $$; NOTICE: Alexander,Hunold hired at:2006-01-03 NOTICE: Bruce,Ernst hired at:2007-05-21 NOTICE: David,Austin hired at:2005-06-25 NOTICE: Valli,Pataballa hired at:2006-02-05 NOTICE: Diana,Lorentz hired at:2007-02-07
首先,声明了一个游标 cur_emp,并且绑定了一个查询语句,通过一个参数 p_deptid 获取指定部门的员工;然后使用 OPEN 打开游标;接着在循环中使用 FETCH 语句获取游标中的记录,如果没有找到更多数据退出循环语句;变量 rec_emp 用于存储游标中的记录;最后使用 CLOSE 语句关闭游标,释放资源。
游标是 PL/pgSQL 中的一个强大的数据处理功能,更多的使用方法可以参考官方文档。
PL/pgSQL 提供了 RAISE 语句,用于打印消息或者抛出错误:
RAISE level format;
不同的 level 代表了错误的不同严重级别,包括:
DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTION
在上文示例中,我们经常使用 NOTICE 输出一些信息。如果不指定 level,默认为 EXCEPTION,将会抛出异常并且终止代码运行。
format 是一个用于提供信息内容的字符串,可以使用百分号(%)占位符接收参数的值, 两个连写的百分号(%%)表示输出百分号自身。
以下是一些 RAISE 示例:
DO $$ BEGIN RAISE 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 $$; INFO: This is an information. WARNING: This is a warning at 2020-05-16 11:27:06.138569+08 NOTICE: This is a notice %
从结果可以看出,并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参数 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,返回相关对象的名称。
以下是一些示例:
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505'; RAISE division_by_zero; RAISE SQLSTATE '22012';
PL/pgSQL 提供了 ASSERT 语句,用于调试存储过程和函数:
ASSERT condition [ , message ];
其中,condition 是一个布尔表达式;如果它的结果为真,ASSERT 通过;如果结果为假或者 NULL,将会抛出 ASSERT_FAILURE 异常。message 用于提供额外的错误信息,默认为“assertion failed”。例如:
DO $$ DECLARE i integer := 1; BEGIN ASSERT i = 0, 'i 的初始值应该为 0!'; END $$; ERROR: i 的初始值应该为 0! CONTEXT: PL/pgSQL function inline_code_block line 5 at ASSERT
⚠️注意,ASSERT 只适用于代码调试;输出错误信息使用 RAISE 语句。
默认情况下,PL/pgSQL 遇到错误时会终止代码执行,同时撤销事务。我们也可以在代码块中使用 EXCEPTION 捕获错误并继续事务:
[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;
如果代码执行出错,程序将会进入 EXCEPTION 模块;依次匹配 condition,找到第一个匹配的分支并执行相应的 handler_statements;如果没有找到任何匹配的分支,继续抛出错误。
以下是一个除零错误的示例:
DO $$ DECLARE i integer := 1; BEGIN i := i / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE '除零错误!'; WHEN OTHERS THEN RAISE NOTICE '其他错误!'; END $$; NOTICE: 除零错误! OTHERS 用于捕获未指定的错误类型。
PL/pgSQL 还提供了捕获详细错误信息的 GET STACKED DIAGNOSTICS 语句,具体可以参考官方文档。
要创建一个自定义的 PL/pgSQL 函数,可以使用 CREATE FUNCTION 语句:
CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) RETURNS rettype AS $$ DECLARE declarations BEGIN statements; ... END; $$ LANGUAGE plpgsql;
CREATE 表示创建函数,OR REPLACE 表示替换函数定义;name 是函数名;括号内是参数,多个参数使用逗号分隔;argmode 可以是 IN(输入)、OUT(输出)、INOUT(输入输出)或者 VARIADIC(数量可变),默认为 IN;argname 是参数名称;argtype 是参数的类型;default_expr 是参数的默认值;rettype 是返回数据的类型;AS 后面是函数的定义,和上文中的匿名块相同;最后,LANGUAGE 指定函数实现的语言,也可以是其他过程语言。
以下示例创建一个函数 get_emp_count,用于返回指定部门中的员工数量:
CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer) RETURNS integer AS $$ DECLARE ln_count integer; BEGIN select count(*) into ln_count from employees where department_id = p_deptid; return ln_count; END; $$ LANGUAGE plpgsql;
创建该函数之后,可以像内置函数一样在 SQL 语句中进行调用:
select department_id,department_name,get_emp_count(department_id) from departments d; department_id|department_name |get_emp_count| -------------|--------------------|-------------| 10|Administration | 1| 20|Marketing | 2| 30|Purchasing | 6| ...
PL/pgSQL 函数支持重载(Overloading),也就是相同的函数名具有不同的函数参数。例如,以下语句创建一个重载的函数 get_emp_count,返回指定部门指定日期之后入职的员工数量:
CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date) RETURNS integer AS $$ DECLARE ln_count integer; BEGIN select count(*) into ln_count from employees where department_id = p_deptid and hire_date >= p_hiredate; return ln_count; END; $$ LANGUAGE plpgsql;
查询每个部门 2005 年之后入职的员工数量:
select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01') from departments d; department_id|department_name |get_emp_count|get_emp_count| -------------|--------------------|-------------|-------------| 10|Administration | 1| 0| 20|Marketing | 2| 1| 30|Purchasing | 6| 4| ...
我们再来看一个 VARIADIC 参数的示例:
CREATE OR REPLACE FUNCTION sum_num( VARIADIC nums numeric[]) RETURNS numeric AS $$ DECLARE ln_total numeric; BEGIN SELECT SUM(nums[i]) INTO ln_total FROM generate_subscripts(nums, 1) t(i); RETURN ln_total; END; $$ LANGUAGE plpgsql;
参数 nums 是一个数组,可以传入任意多个参数;然后计算它们的和值。例如:
SELECT sum_num(1,2), sum_num(1,2,3); sum_num|sum_num| -------|-------| 3| 6|
如果函数不需要返回结果,可以返回 void 类型;或者直接使用存储过程。
PostgreSQL 11 增加了存储过程,使用 CREATE PROCEDURE 语句创建:
CREATE [ OR REPLACE ] PROCEDURE name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) AS $$ DECLARE declarations BEGIN statements; ... END; $$ LANGUAGE plpgsql;
存储过程的定义和函数主要的区别在于没有返回值,其他内容都类似。以下示例创建了一个存储过程 update_emp,用于修改员工的信息:
CREATE OR REPLACE PROCEDURE update_emp( p_empid in integer, p_salary in numeric, p_phone in varchar) AS $$ BEGIN update employees set salary = p_salary, phone_number = p_phone where employee_id = p_empid; END; $$ LANGUAGE plpgsql;
调用存储过程使用 CALL 语句:
call update_emp(100, 25000, '515.123.4560');
在存储过程内部,可以使用 COMMIT 或者 ROLLBACK 语句提交或者回滚事务。例如:
create table test(a int); CREATE PROCEDURE transaction_test() LANGUAGE plpgsql AS $$ BEGIN FOR i IN 0..9 LOOP INSERT INTO test (a) VALUES (i); IF i % 2 = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END $$; CALL transaction_test(); select * from test; a| -| 0| 2| 4| 6| 8|
只有偶数才会被最终提交。
关于“PostgreSQL存储过程源码分析”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“PostgreSQL存储过程源码分析”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。