PL/pgSQL编写postgresql函数之基本语句
oygpt 2024-09-23 12:17 136 浏览 0 评论
目录
基本语句
1 赋值
赋值运算符 := 或 =
2 单一行结果返回
SELECT...INTO 语法赋值
更新操作结果返回
3 多行结果返回
方式一:使用表充当容器
方式二:使用自定义TYPE充当容器
方式三:return query进行结果整体写入容器
方式四:return setof record返回
4 无结果执行
5 执行动态命令
2.1 语法
2.2示例准备
2.3 例子
6 打印输出语法
基本语句
1 赋值
赋值运算符 := 或 =
语法:variable { := | = } expression;
例子:
create or replace function set_value() returns void as
$
declare
v_id int;
v_name varchar;
begin
v_id := 100;
raise notice 'v_id=%',v_id;
v_name = '瀚高' ;
raise notice 'v_name=%',v_name;
end
$
language plpgsql
打印输出结果:
NOTICE: 00000: v_id=100
NOTICE: 00000: v_name=瀚高
2 单一行结果返回
SELECT...INTO 语法赋值
语法: SELECT select_expressisons INTO target FROM ...;
SELECT INTO target select_expressisons FROM ...;
SELECT select_expressisons FROM ... INTO target ;
其中,变量target可以为基本数据变量、行变量与记录变量。
例子:
create or replace function fn_record(v_id int) returns text as
$
declare
v_record RECORD;
begin
select id,name,age into v_record from t_test where id=v_id ;
raise notice 'v_record=%',v_record; --1号位
select into v_record id,name,age from t_test where id=v_id ;
raise notice 'v_record=%',v_record; --2号位
select id,name,code from t_test where id=v_id into v_record;
raise notice 'v_record=%',v_record; --3号位
return v_record.id ||v_record.name;
end
$
language plpgsql
打印输出结果:
NOTICE: 00000: v_record=(1,张三,18) --1号位
NOTICE: 00000: v_record=(1,张三,18) --2号位
NOTICE: 00000: v_record=(1,张三,10010-11) --3号位
更新操作结果返回
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
例子:
create or replace function fn_into() returns void as
$
declare
v_record RECORD;
begin
insert into t_test(name,age,code) values ('瀚高1',15,'10010-01')
returning name,age,code into v_record;
raise notice 'insert--v_record=%',v_record;
update t_test set name='瀚高2' where name='张三'
returning name,age,code into v_record;
raise notice 'update--v_record=%',v_record;
delete from t_test where name='李四'
returning name,age,code into v_record;
raise notice 'delete--v_record=%',v_record;
end
$
language plpgsql
打印执行结果:
NOTICE: 00000: insert--v_record=(瀚高1,15,10010-01) 插入的数据
NOTICE: 00000: update--v_record=(瀚高2,18,10010-11) 更新后的数据
NOTICE: 00000: delete--v_record=(李四,20,10010-12) 被删除的数据
3 多行结果返回
关键字 setof
例子:
create table t_test(id serial,name varchar,age int,code text);
insert into t_test(name,age,code) values('张三',18,'10010-11'),
('李四',20,'10010-12'),
('王五',22,'10010-13');
方式一:使用表充当容器
create table v_user as select * from t_test limit 0; --表类型容器
create or replace function fn_get_values() returns setof v_user as
$
declare
var_user v_user%rowtype;
begin
for var_user in select * from t_test where id != 1 loop
return next var_user;
end loop;
return ;
end
$
language plpgsql
方式二:使用自定义TYPE充当容器
create type v_users as (id int,name varchar,code text); --自定义类型容器
create or replace function fn_get_value() returns setof v_users as
$
declare
var_user v_users%rowtype;
begin
for var_user in select id,name,code from t_test where id != 1 loop
return next var_user;
end loop;
return ;
end
$
language plpgsql
方式三:return query进行结果整体写入容器
create unlogged table v_user as select * from t_test limit 0; --表类型容器
create type v_user as (id int,name varchar,code text); --自定义类型容器
create or replace function fn_manyrow() returns setof v_user as
$
begin
return query select * from t_test where id != 1;
end
$
language plpgsql
方式四:return setof record返回
例子
create or replace function fn_manyrow1()
RETURNS SETOF RECORD as $
declare
r record;
begin
return query(select id,name,age from t_test);
end;
$language plpgsql;
调用
select * from fn_manyrow1() as t(id int,name varchar ,age int);
调用函数执行结果
test_db=# select * from fn_manyrow1() as t(id int,name varchar ,age int);
id | name | age
----+-------+-----
3 | 王五 | 22
4 | 瀚高1 | 15
1 | 瀚高2 | 18
(3 行记录)
注意使用record返回时函数调用,不在是之前的select * from XXX,而是 select * from XXX as(col1 datatype1,col2 datatype2)这种。我们之前讲过,record没有固定结构,所以我们需要在查询时指定record结果以一定格式展示。
要求:as 后字段类型顺序与record中存放的结果数据类型顺序一致。
4 无结果执行
如果不需要接收执行结果,使用PERFORM 替换select。否则有执行结果返回会报错。
例子:
create or replace function fn_perform() returns void as
$
begin
select current_time;
end
$
language plpgsql
报错如下:原因是select current_time; 有返回值,但没有使用变量接收。
test_db=# select fn_perform();
ERROR: 42601: query has no destination for result data
提示: If you want to discard the results of a SELECT, use PERFORM instead.
背景: PL/pgSQL function fn_perform() line 4 at SQL statement
上述例子修改如下:使用perform 替换select
create or replace function fn_perform() returns void as
$
begin
perform current_time;
end
$
language plpgsql
5 执行动态命令
2.1 语法
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
关键字:EXECUTE
command-string 执行命令串 (SQL语句也是一条命令)
USING 表达式提供插入到该命令中的值
2.2示例准备
create table t_user(
id varchar default sys_guid() ,name varchar,
age int,code text)
partition by list(substring(code,1,2));
create table t_user_10 partition of t_user for values in ('10');
create table t_user_11 partition of t_user for values in ('11');
create table t_user_12 partition of t_user for values in ('12');
alter table t_user_10 add constraint pk_t_user_10_id primary key(id);
alter table t_user_11 add constraint pk_t_user_11_id primary key(id);
alter table t_user_12 add constraint pk_t_user_12_id primary key(id);
insert into t_user(name,age,code) values('张三',18,'10010-11');
insert into t_user(name,age,code) values('李四',18,'11010-11');
insert into t_user(name,age,code) values('王五',18,'12010-11');
2.3 例子
create or replace function fn_execute(partition_code varchar,uid varchar) returns text as
$
declare
var_name text;
var_sql text;
begin
raise notice '=====%','select * from t_user_'||partition_code||' where id = '''||uid||''';';
var_sql := 'select name from t_user_'||partition_code||' where id = '''||uid||''';';
EXECUTE var_sql into var_name;
return var_name;
end
$
language plpgsql
在上面例子准备中我们知道有一张用户基表,它有三张分区子表,分区条件是code字段值得前两位。
需求:根据分区标识值及数据主键快速定位某条数据。
如右函数:
输入参数为分区数值parttion_code和数据主键值uid。由于我们创建分区表时表名是有规律的,每个分区子表表名就是基表表名加上了分区值,所以编写一个统一函数进行动态SQL执行。
注意:使用EXECUTE时,如果要与INTO写值连用,语法为 EXECUTE sql_string INTO var_value
6 打印输出语法
语法: RAISE level 'format' [, expression [, ...]];
RAISE 是报告信息和抛出错误的语句
level 表示消息级别(debug、log、info、notice、warning、exception)
‘format’ 消息内容使用’’引起来,format是消息内容,其中使用%作为占位符(如果要在format中表示%本身,需写成%%)。
[, expression [, ...]] 为%占位符的变量,依次用“,”排列。
例子:
例子:
RAISE NOTICE 'Calling cs_create_job(%)',v_job_idr;
--v_job_id变量的值将替换format中的%。
raise log '这是个日志消息'; --输出在日志文件中
raise info '这个是一个信息'; --以下几个信息打印在控制台
raise notice '这是个提示消息';
raise warning '这是个警告消息';
raise exception '这是个异常消息';
————————————————
版权声明:本文为CSDN博主「darion@Postgresql」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_39727113/article/details/111201450
相关推荐
- PLSQL 命令行模式常见错误(plsql执行命令行)
-
日常运维过程中,经常使用PLSQL的command模式运行SQL脚本,对于一些常见的错误,你知道原因在哪里吗?1.SQL脚本执行后弹出输入框原因:SQL*PLUS默认环境里会把'&字符'...
- 了解 PL/SQL 的异常处理(sql数据库异常处理)
-
6.PL/SQL的异常处理在程序运行时出现的镇误,称为异常。发生异常后语句将停止执行,PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQ...
- PL/SQL 泛型编程详解(泛型调用)
-
PL/SQL中的通用函数,也称为泛型函数,是一种可以接受任意数据类型参数的函数。这使得开发者能够编写可重用的代码,以处理不同的数据类型,而无需为每种数据类型编写专门的函数。PL/SQL的泛型函数通过使...
- PL/pgSQL编写postgresql函数之基本语句
-
目录基本语句1赋值赋值运算符:=或=2单一行结果返回SELECT...INTO语法赋值更新操作结果返回3多行结果返回方式一:使用表充当容器方式二:使用自定义TYPE充当容器方式三:ret...
- PLSQL安装教程(plsql安装教程及配置)
-
2.安装,双击上图Plsqldev.exe文件;3.单击确定,进行下一步安装;4.软件询问是否遵守协议,单击“IAgree”,进行下一步安装;5.选择软件安装在计算机中的路径,(客户端的安装...
- PL/SQL字符函数概览(sqlplus 字符集)
-
PL/SQL提供了一系列内置的字符函数,这些函数可以对字符串进行各种操作,如转换、比较、搜索和替换等。以下是一些常用的PL/SQL字符函数及其用法示例:CONCAT:连接两个或多个字符串。示例:DEC...
- instantclient + PLSQL安装与配置小结
-
一、软件1、instantclient-basic-windows.x64-11.2.0.4.0.zip到官网下载。2、PLSQLDeveloper13.rar到网上下载,找破解版的,网上有V...
- 如何使用 PL/SQL 块 ?(pl/sql 使用教程)
-
2.PL/SQL块PL/SQL是一种块结构的诺言,一个PL/SQL程序包含了一个或者多个逻辑块,逻辑块中可以声明变量,变量在使用之前必须先声明。除了正常的执行程序外,PL/SQL还提供了专门的异...
- PL/SQL(Procedural Language(procedural objects)
-
PL/SQL(ProceduralLanguage/StructuredQueryLanguage)是由OracleCorporation开发的一种用于与Oracle数据库配合使用的编...
- Oracle数据库扩展语言PL/SQL之块结构
-
【本文详细介绍了Oracle数据库扩展语言PL/SQL的块结构,欢迎读者朋友们阅读、转发和收藏!】1基本概念1.1PL/SQL块结构块(block)是PL/SQL的基本程序单元,编写P...
- 记一次生产数据库sql优化案例--with用法改写(11分钟优化到7秒)
-
概述前段时间开发丢了一个超长的sql给我,说需要优化,因为太长,连PL/SQL的美化工具都美化不了...下面简单记录一下优化的过程。with改写WITHAS短语,也叫做子查询部分(subquery...
- 如何在生产库与测试库做数据结构对比--PL/SQL工具
-
概述领导要求做个数据库之间的数据结构对比,这里我简单用PL/SQL工具来实现,下面介绍下使用过程。功能PLSQLDeveloperTools菜单下有CompareUserObjects和Com...
- PLSQL使用教程——(1)基本使用教程
-
一、登录1、在这里配置好数据库服务,之后就可以登录了2、输入用户名和密码,并选择之前配置好的数据库服务。我这服务名取为localhost。(这个名字随意起。)二、创建表空间1、在SQL窗口中执行以下S...
- PL/SQL调试存储过程?看这篇就够了
-
概述虽然现在存储过程相对比较少用了,但是平时接触不可避免的要跟存储过程打交道,当需要自己写的时候总会碰到这或那的错误,这个时候一般要怎么调试呢?PL/SQL调试PL/SQL中提供了【调试存储过程】的功...
- IT运维基础篇之oracle sqlldr数据批量导入,比plsqldev还简单
-
在oralce中导入数据的方式有很多,比如:PL/SQL文本导入器、对表forupdate之后直接复制粘贴等等,导入方式有很多,今天我们介绍另一种大批量数据导入方式:sqlldr,具体其用法可以上网查...