探索GaussDB(DWS)的过程化SQL语言能力

news/2024/10/31 3:28:45/

摘要:在当前GaussDB(DWS)的能力中主要支持两种过程化SQL语言,即基于PostgreSQL的PL/pgSQL以及基于Oracle的PL/SQL。本篇文章我们通过匿名块,函数,存储过程向大家介绍一下GaussDB(DWS)对于过程化SQL语言的基本能力。

本文分享自华为云社区《GaussDB(DWS) SQL进阶之PLSQL(一)-匿名块、函数和存储过程》,原文作者:xxxsql123 。

前言

GaussDB(DWS)中的PLSQL语言,是一种可载入的过程语言,其创建的函数可以被用在任何可以使用内建函数的地方。例如,可以创建复杂条件的计算函数并且后面用它们来定义操作符或把它们用于索引表达式。

SQL被大多数数据库用作查询语言。它是可移植的并且容易学习。但是每一个SQL语句必须由数据库服务器单独执行。

这意味着客户端应用必须发送每一个查询到数据库服务器、等待它被处理、接收并处理结果、做一些计算,然后发送更多查询给服务器。如果客户端和数据库服务器不在同一台机器上,所有这些会引起进程间通信并且将带来网络负担。

通过PLSQL语言,可以将一整块计算和一系列查询分组在数据库服务器内部,这样就有了一种过程语言的能力并且使SQL更易用,同时能节省的客户端/服务器通信开销。

  • 客户端和服务器之间的额外往返通信被消除。
  • 客户端不需要的中间结果不必被整理或者在服务器和客户端之间传送。
  • 多轮的查询解析可以被避免。

在当前GaussDB(DWS)的能力中主要支持两种过程化SQL语言,即基于PostgreSQL的PL/pgSQL以及基于Oracle的PL/SQL。本篇文章我们通过匿名块,函数,存储过程向大家介绍一下GaussDB(DWS)对于过程化SQL语言的基本能力。

匿名块的使用

匿名块(Anonymous Block)一般用于不频繁执行的脚本或不重复进行的活动。它们在一个会话中执行,并不被存储。

在GaussDB(DWS)中通过针对PostgreSQL和Oracle风格的整合,目前支持以下两种方式调用,对于Oracle迁移到GaussDB(DWS)的存储过程有了很好的兼容性支持。

√ Oracle风格-以反斜杠结尾:

语法格式:

[DECLARE [declare_statements]] 
BEGIN
execution_statements
END;
/

执行用例:

postgres=# DECLARE
postgres-#      my_var VARCHAR2(30);
postgres-# BEGIN
postgres$#      my_var :='world';
postgres$#      dbms_output.put_line('hello '||my_var);
postgres$# END;
postgres$# /
hello world
ANONYMOUS BLOCK EXECUTE

√ PostgreSQL风格-以DO开头,匿名块用包起来:

语法格式:

DO [ LANGUAGE lang_name ] code;

执行用例:

postgres=# DO $$DECLARE
postgres$#      my_var char(30);
postgres$# BEGIN
postgres$#      my_var :='world';
postgres$#      raise info 'hello %' , my_var;
postgres$# END$$;
INFO:  hello world
ANONYMOUS BLOCK EXECUTE

这时细心的小伙伴们就会发现,GaussDB(DWS)不仅支持了Oracle的PL/SQL的兼容性支持,对于Oracle高级包中的dbms_output.put_line函数也做了支持。所以我们也可以将两个风格混用,发现也是支持的。(^-^)V

postgres=# DO $$DECLARE
postgres$#      my_var VARCHAR2(30);
postgres$# BEGIN
postgres$#      my_var :='world';
postgres$#      dbms_output.put_line('hello '||my_var);
postgres$# END$$;
hello world
ANONYMOUS BLOCK EXECUTE

函数的创建

既然匿名块GaussDB支持了Oracle和PostgreSQL两种风格的创建,函数当然也会支持两种啦。

下面我们一起来看看具体的使用吧!(。ì _ í。)

√ PostgreSQL风格:

语法格式:

CREATE [ OR REPLACE  ] FUNCTION function_name ( [  { argname [ argmode  ] argtype [  { DEFAULT  | :=  | =  } expression  ]}  [, ...]  ] )[ RETURNS rettype [ DETERMINISTIC  ]  | RETURNS TABLE (  { column_name column_type  }  [, ...] )]LANGUAGE lang_name [ {IMMUTABLE  | STABLE  | VOLATILE }| {SHIPPABLE | NOT SHIPPABLE}| WINDOW| [ NOT  ] LEAKPROOF  | {CALLED ON NULL INPUT  | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL  ] SECURITY INVOKER | [ EXTERNAL  ] SECURITY DEFINER | AUTHID DEFINER  | AUTHID CURRENT_USER} | {fenced | not fenced}| {PACKAGE}| COST execution_cost| ROWS result_rows| SET configuration_parameter { {TO | =} value | FROM CURRENT }}][...]{AS 'definition'| AS 'obj_file', 'link_symbol'}

执行用例:

定义函数为SQL查询的形式:

postgres=# CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
postgres-#     AS 'select $1 + $2;'
postgres-#     LANGUAGE SQL
postgres-#     IMMUTABLE
postgres-#     RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
postgres=# select func_add_sql(1, 2);func_add_sql
--------------3
(1 row)

定义函数为plpgsql语言的形式:

postgres=# CREATE OR REPLACE FUNCTION func_add_sql2(a integer, b integer) RETURNS integer AS $$
postgres$#     BEGIN
postgres$#             RETURN a + b;
postgres$#     END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select func_add_sql2(1, 2);func_add_sql2
---------------3
(1 row)

定义返回为SETOF RECORD的函数:

postgres=# CREATE OR REPLACE FUNCTION func_add_sql3(a integer, b integer, out sum bigint, out product bigint)
postgres-# returns SETOF RECORD
postgres-# as $$
postgres$# begin
postgres$#     sum = a + b;
postgres$#     product = a * b;
postgres$#     return next;
postgres$# end;
postgres$# $$language plpgsql;
CREATE FUNCTION
postgres=# select * from  func_add_sql3(1, 2);sum | product
-----+---------3 |       2
(1 row)

√ Oracle风格:

语法格式:

CREATE [ OR REPLACE  ] FUNCTION function_name ( [  { argname [ argmode  ] argtype [  { DEFAULT | := | =  } expression  ] }  [, ...]  ] )RETURN rettype [ DETERMINISTIC  ][ {IMMUTABLE  | STABLE  | VOLATILE } | {SHIPPABLE | NOT SHIPPABLE}| {PACKAGE}| {FENCED | NOT FENCED}| [ NOT  ] LEAKPROOF  | {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | {[ EXTERNAL  ] SECURITY INVOKER  | [ EXTERNAL  ] SECURITY DEFINER |
AUTHID DEFINER | AUTHID CURRENT_USER
} | COST execution_cost  | ROWS result_rows  | SET configuration_parameter { {TO | =} value  | FROM CURRENT][...] { IS  | AS
} plsql_body
/

执行用例:

定义为Oracle的PL/SQL风格的函数:

实例1:

postgres=# CREATE FUNCTION func_add_sql2(a integer, b integer) RETURN integer
postgres-# AS
postgres$# BEGIN
postgres$# RETURN a + b;
postgres$# END;
postgres$# /
CREATE FUNCTION
postgres=# call func_add_sql2(1, 2);func_add_sql2
---------------3
(1 row)

实例2:

postgres=# CREATE OR REPLACE FUNCTION func_add_sql3(a integer, b integer) RETURN integer
postgres-# AS
postgres$#     sum integer;
postgres$# BEGIN
postgres$#     sum := a + b;
postgres$#     return sum;
postgres$# END;
postgres$# /
CREATE FUNCTION
postgres=# call func_add_sql3(1, 2);func_add_sql3
---------------3
(1 row)

若想使用Oracle的PL/SQL风格定义OUT参数需要使用到存储过程,请看下面章节。

存储过程的创建

存储过程与函数功能基本相似,都属于过程化SQL语言,不同的是存储过程没有返回值。

※ 需要注意的是目前GaussDB(DWS)只支持Oracle的CREATE PROCEDURE的语法支持,暂时不支持PostgreSQL的CREATE PROCEDURE语法支持。

× PostgreSQL风格:

   暂不支持。

√ Oracle风格:

语法格式:

CREATE [ OR REPLACE ] PROCEDURE procedure_name[ ( {[ argmode ] [ argname ] argtype [ { DEFAULT | := | = } expression ]}[,...]) ][{ IMMUTABLE | STABLE | VOLATILE }| { SHIPPABLE | NOT SHIPPABLE }| {PACKAGE}| [ NOT ] LEAKPROOF| { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }| {[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AUTHID DEFINER | AUTHID CURRENT_USER}| COST execution_cost| ROWS result_rows| SET configuration_parameter { [ TO | = ] value | FROM CURRENT }][ ... ]{ IS | AS } 
plsql_body 
/

执行用例:

postgres=# CREATE OR REPLACE PROCEDURE prc_add
postgres-# (
postgres(#     param1    IN   INTEGER,
postgres(#     param2    IN OUT  INTEGER
postgres(# )
postgres-# AS
postgres$# BEGIN
postgres$#    param2:= param1 + param2;
postgres$#    dbms_output.put_line('result is: '||to_char(param2));
postgres$# END;
postgres$# /
CREATE PROCEDURE
postgres=# call prc_add(1, 2);
result is: 3param2
--------3
(1 row)

经过以上对GaussDB(DWS)过程化SQL语言的简单介绍,我们大致了解了在GaussDB(DWS)中匿名块,函数,存储过程的创建,下面将简单介绍一下在过程化SQL语言中的一些简单的语法介绍。

基本语法介绍

赋值:

支持 = 与 := 两种赋值符合的使用。下面两种赋值方式都是支持的。

a = b;
a := b + 1;

条件语句:

支持IF ... THEN ... END IF;   IF ... THEN  ...  ELSE ... END IF; IF ... THEN  ...  ELSEIF ... THEN ...  ELSE ... END IF;其中ELSEIF也可以写成ELSIF。

语法介绍:

-- Case 1:
IF 条件表达式 THEN--表达式为TRUE后将执行的语句
END IF;-- Case 2:
IF 条件表达式 THEN--表达式为TRUE后将执行的语句
ELSE--表达式为FALSE后将执行的语句
END IF;-- Case 3:
IF 条件表达式1 THEN--表达式1为TRUE后将执行的语句
ELSEIF 条件表达式2 THEN--表达式2为TRUE 后将执行的语句
ELSE--以上表达式都不为TRUE 后将执行的语句
END IF;

示例:

postgres=# CREATE OR REPLACE PROCEDURE pro_if_then(IN i INT)
postgres-# AS
postgres$# BEGIN
postgres$#     IF i>5 AND i<10 THEN
postgres$#         dbms_output.put_line('This is if test.');
postgres$#     ELSEIF i>10 AND i<15 THEN
postgres$#         dbms_output.put_line('This is elseif test.');
postgres$#     ELSE
postgres$#         dbms_output.put_line('This is else test.');
postgres$#     END IF;
postgres$# END;
postgres$# /
CREATE PROCEDURE
postgres=# call pro_if_then(1);
This is else test.pro_if_then
-------------(1 row)postgres=# call pro_if_then(6);
This is if test.pro_if_then
-------------(1 row)postgres=# call pro_if_then(11);
This is elseif test.pro_if_then
-------------(1 row)

循环语句:

支持while,for, foreach的使用。循环期间也可以适当添加循环控制语句continue, break。

语法介绍:

WHILE 条件表达式1 THEN--循环内需要执行的语句
END LOOP;FOR i IN result LOOP--循环内需要执行的语句
END LOOP;FOREACH var IN result LOOP--循环内需要执行的语句
END LOOP;

示例:

postgres=# CREATE OR REPLACE FUNCTION func_loop(a integer) RETURN integer
postgres-# AS
postgres$#     sum integer;
postgres$#     var integer;
postgres$# BEGIN
postgres$#     sum := a;
postgres$#     WHILE sum < 10 LOOP
postgres$#         sum := sum + 1;
postgres$#     END LOOP;
postgres$#
postgres$#     RAISE INFO 'current sum: %', sum;
postgres$#     FOR i IN 1..10 LOOP
postgres$#         sum := sum + i;
postgres$#     END LOOP;
postgres$#
postgres$#     RAISE INFO 'current sum: %', sum;
postgres$#     FOREACH var IN ARRAY ARRAY[1, 2, 3, 4] LOOP
postgres$#         sum := sum + var;
postgres$#     END LOOP;
postgres$#
postgres$#     RETURN sum;
postgres$# END;
postgres$# /
CREATE FUNCTION
postgres=# call func_loop(1);
INFO:  current sum: 10
INFO:  current sum: 65func_loop
-----------75
(1 row)

GOTO语句:

支持goto语法的使用。

语法介绍:

GOTO LABEL;--若干语句
<<label>>

示例:

postgres=# CREATE OR REPLACE FUNCTION goto_while_goto()
postgres-# RETURNS TEXT
postgres-# AS $$
postgres$# DECLARE
postgres$#     v0 INT;
postgres$#     v1 INT;
postgres$#     v2 INT;
postgres$#     test_result TEXT;
postgres$# BEGIN
postgres$#     v0 := 1;
postgres$#     v1 := 10;
postgres$#     v2 := 100;
postgres$#     test_result = '';
postgres$#     WHILE v1 < 100 LOOP
postgres$#         v1 := v1+1;
postgres$#         v2 := v2+1;
postgres$#         IF v1 > 25 THEN
postgres$#             GOTO pos1;
postgres$#         END IF;
postgres$#     END LOOP;
postgres$#
postgres$# <<pos1>>
postgres$#     /* OUTPUT RESULT */
postgres$#     test_result := 'GOTO_base=>' ||
postgres$#                    ' v0: (' || v0 || ') ' ||
postgres$#                    ' v1: (' || v1 || ') ' ||
postgres$#                    ' v2: (' || v2 || ') ';
postgres$#     RETURN test_result;
postgres$# END;
postgres$# $$
postgres-# LANGUAGE 'plpgsql';
CREATE FUNCTION
postgres=#
postgres=# SELECT goto_while_goto();goto_while_goto
-------------------------------------------GOTO_base=> v0: (1)  v1: (26)  v2: (116)
(1 row)

异常处理:

语法介绍:

[<<label>>]
[DECLAREdeclarations]
BEGINstatements
EXCEPTIONWHEN condition [OR condition ...] THENhandler_statements[WHEN condition [OR condition ...] THENhandler_statements...]
END;

示例:

postgres=# CREATE TABLE mytab(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) DISTRIBUTE BY hash(id);
CREATE TABLE
postgres=# INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
INSERT 0 1
postgres=# CREATE FUNCTION fun_exp() RETURNS INT
postgres-# AS $$
postgres$# DECLARE
postgres$#     x INT :=0;
postgres$#     y INT;
postgres$# BEGIN
postgres$#     UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
postgres$#     x := x + 1;
postgres$#     y := x / 0;
postgres$# EXCEPTION
postgres$#     WHEN division_by_zero THEN
postgres$#         RAISE NOTICE 'caught division_by_zero';
postgres$#         RETURN x;
postgres$# END;$$
postgres-# LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# call fun_exp();
NOTICE:  caught division_by_zerofun_exp
---------1
(1 row)postgres=# select * from mytab;id | firstname | lastname
----+-----------+----------| Tom       | Jones
(1 row)postgres=# DROP FUNCTION fun_exp();
DROP FUNCTION
postgres=# DROP TABLE mytab;
DROP TABLE

总结:

GaussDB(DWS)对于过程化SQL语言的支持主要在PostgreSQL与Oracle上做了兼容,同时针对Oracle的一些高级包以及一些Oracle独有的语法也做了一定支持。在迁移Oracle或者PostgreSQL时,对于函数或存储过程的迁移可以减少为了兼容导致的额外工作量。

至此已经将GaussDB(DWS)中的匿名块,函数,存储过程的创建以及基本使用介绍的差不多了。当然GaussDB(DWS)对于过程化SQL语言的支持不止如此,在接下来的时间里,还将逐步向大家介绍游标,用户自定义类型等章节哟~

想了解GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的PB级数仓黑科技,后台还可获取众多学习资料哦~

 

点击关注,第一时间了解华为云新鲜技术~


http://www.ppmy.cn/news/523526.html

相关文章

基于DGUS II 实现高性能GUI

前言 2019年迪文推出了T5L系列芯片&#xff0c;同时还更新了针对它的第二代DGUS软件——因为大多数功能以及设置都与T5平台相通&#xff0c;所以T5L系列所使用的DGUS软件依然是第二代的范畴。 跟T5与K600平台之间差别的情况不同&#xff0c;因为采用了相近的底层逻辑&#xff0…

修普诺斯游戏测评报告

修普诺斯测评报告 目录 1.游戏简介 2.美术表现 2.1.开场cg登陆、加载界面 2.2.音乐音效 2.3.角色模型 2.4.UI 2.5.战斗场景战斗表现 3.游戏内容 3.1.养成系统 3.2.PVE玩法 3.3.PVP玩法 4.核心玩法 5.付费点 5.1.首充破冰后续付费 5.2.成长基金等其他付费点 5.3.限时礼包 …

NYOJ-20 吝啬的国度【深搜】

描述 在一个吝啬的国度里有N个城市&#xff0c;这N个城市间只有N-1条路把这个N个城市连接起来。现在&#xff0c;Tom在第S号城市&#xff0c;他有张该国地图&#xff0c;他想知道如果自己要去参观第T号城市&#xff0c;必须经过的前一个城市是几号城市&#xff08;假设你不走重…

克诺斯虚拟化服务器,超强遗传 VA1 克拉诺斯Kronos

原标题&#xff1a;超强遗传 VA1 克拉诺斯Kronos 狼王 VA1 克拉诺斯Kronos von Nrburgring 2013 和 2014年意大利本部展都是VA。 2014年9月 SV本部展获得VA。 2014年引进中国后参加中国CSV本部展(与SV本部展相隔一个月&#xff0c;没有时间在国内适应环境训练等情况下直接参赛)…

DjVu,版式文档领域一颗璀璨的流星

早上起来看新闻&#xff0c;被一则勒索病毒的报道震惊了&#xff1a; 其中的Read Me.txt文件指出&#xff1a;受害人的文件&#xff08;照片&#xff0c;文档&#xff0c;数据库和所有其他数据&#xff09;已被加密成.djvu文件&#xff0c;只能通过购买BigBobRoss提供的特定工具…

无线洗地机好用吗?旗舰洗地机品牌实测体验分享

近年来清洁类智能家电非常流行&#xff0c;面对脏乱的室内地面&#xff0c;扫地机器人、无线吸尘器、家用洗地机都是很好的帮手。本人有幸都使用过&#xff0c;但如果要我挑选出最喜欢的清洁家电&#xff0c;那我肯定会毫不犹豫的选择家用洗地机。因为它拥有着强大的清洁能力&a…

开源:基于迪文T5L智能屏的PID温控器方案

——来自迪文开发者论坛用户 该温控器方案基于 DMG80480C043_02W 智能屏设计&#xff0c;通过 DS18B20、MLX90614 分别采集设备入、出口温度值给到 T5L 芯片处理&#xff0c;并将温度值实时显示在屏幕上。同时采用了 PID 控温算法控制加热装置&#xff0c;使出口温度稳定在设定…

涉密信息系统服务器管理制度

第一条 为加强对涉密信息系统服务器的保密管理&#xff0c;特制定本管 理制度。 第二条 本制度适用于内网服务器的保密管理。 第三条 本制度所指服务器包括连入涉密内网并提供网络服务 的工作站、服务器、台式 PC 机等。 第四条 所有服务器的设备管理&#xff0c;包括…