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

关注

发布于: 2021 年 05 月 24 日

摘要：在当前 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]] BEGINexecution_statementsEND;/

复制代码

执行用例：

postgres=# DECLAREpostgres-#      my_var VARCHAR2(30);postgres-# BEGINpostgres$#      my_var :='world';postgres$#      dbms_output.put_line('hello '||my_var);postgres$# END;postgres$# /hello worldANONYMOUS BLOCK EXECUTE

复制代码

√ PostgreSQL 风格-以 DO 开头，匿名块用 $$包起来：

语法格式：

DO [ LANGUAGE lang_name ] code;

复制代码

执行用例：

postgres=# DO $$DECLAREpostgres$#      my_var char(30);postgres$# BEGINpostgres$#      my_var :='world';postgres$#      raise info 'hello %' , my_var;postgres$# END$$;INFO:  hello worldANONYMOUS BLOCK EXECUTE

复制代码

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

postgres=# DO $$DECLAREpostgres$#      my_var VARCHAR2(30);postgres$# BEGINpostgres$#      my_var :='world';postgres$#      dbms_output.put_line('hello '||my_var);postgres$# END$$;hello worldANONYMOUS 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 integerpostgres-#     AS 'select $1 + $2;'postgres-#     LANGUAGE SQLpostgres-#     IMMUTABLEpostgres-#     RETURNS NULL ON NULL INPUT;CREATE FUNCTIONpostgres=# 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$#     BEGINpostgres$#             RETURN a + b;postgres$#     END;postgres$# $$ LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# 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 RECORDpostgres-# as $$postgres$# beginpostgres$#     sum = a + b;postgres$#     product = a * b;postgres$#     return next;postgres$# end;postgres$# $$language plpgsql;CREATE FUNCTIONpostgres=# 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 integerpostgres-# ASpostgres$# BEGINpostgres$# RETURN a + b;postgres$# END;postgres$# /CREATE FUNCTIONpostgres=# 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 integerpostgres-# ASpostgres$#     sum integer;postgres$# BEGINpostgres$#     sum := a + b;postgres$#     return sum;postgres$# END;postgres$# /CREATE FUNCTIONpostgres=# 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_addpostgres-# (postgres(#     param1    IN   INTEGER,postgres(#     param2    IN OUT  INTEGERpostgres(# )postgres-# ASpostgres$# BEGINpostgres$#    param2:= param1 + param2;postgres$#    dbms_output.put_line('result is: '||to_char(param2));postgres$# END;postgres$# /CREATE PROCEDUREpostgres=# call prc_add(1, 2);result is: 3 param2--------      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-# ASpostgres$# BEGINpostgres$#     IF i>5 AND i<10 THENpostgres$#         dbms_output.put_line('This is if test.');postgres$#     ELSEIF i>10 AND i<15 THENpostgres$#         dbms_output.put_line('This is elseif test.');postgres$#     ELSEpostgres$#         dbms_output.put_line('This is else test.');postgres$#     END IF;postgres$# END;postgres$# /CREATE PROCEDUREpostgres=# 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 integerpostgres-# ASpostgres$#     sum integer;postgres$#     var integer;postgres$# BEGINpostgres$#     sum := a;postgres$#     WHILE sum < 10 LOOPpostgres$#         sum := sum + 1;postgres$#     END LOOP;postgres$#postgres$#     RAISE INFO 'current sum: %', sum;postgres$#     FOR i IN 1..10 LOOPpostgres$#         sum := sum + i;postgres$#     END LOOP;postgres$#postgres$#     RAISE INFO 'current sum: %', sum;postgres$#     FOREACH var IN ARRAY ARRAY[1, 2, 3, 4] LOOPpostgres$#         sum := sum + var;postgres$#     END LOOP;postgres$#postgres$#     RETURN sum;postgres$# END;postgres$# /CREATE FUNCTIONpostgres=# call func_loop(1);INFO:  current sum: 10INFO:  current sum: 65 func_loop-----------        75(1 row)

复制代码

GOTO 语句：

支持 goto 语法的使用。

语法介绍：

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

复制代码

示例：

postgres=# CREATE OR REPLACE FUNCTION goto_while_goto()postgres-# RETURNS TEXTpostgres-# AS $$postgres$# DECLAREpostgres$#     v0 INT;postgres$#     v1 INT;postgres$#     v2 INT;postgres$#     test_result TEXT;postgres$# BEGINpostgres$#     v0 := 1;postgres$#     v1 := 10;postgres$#     v2 := 100;postgres$#     test_result = '';postgres$#     WHILE v1 < 100 LOOPpostgres$#         v1 := v1+1;postgres$#         v2 := v2+1;postgres$#         IF v1 > 25 THENpostgres$#             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 FUNCTIONpostgres=#postgres=# SELECT goto_while_goto();              goto_while_goto------------------------------------------- GOTO_base=> v0: (1)  v1: (26)  v2: (116)(1 row)

复制代码

异常处理：

语法介绍：

[<<label>>][DECLARE    declarations]BEGIN    statementsEXCEPTION    WHEN condition [OR condition ...] THEN        handler_statements    [WHEN condition [OR condition ...] THEN        handler_statements    ...]END;

复制代码

示例：

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

复制代码

总结：

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

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

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

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

发布于: 2021 年 05 月 24 日阅读数: 239

原文链接:【http://xie.infoq.cn/article/441ff8a1ec0bf66214be0c37e】。文章转载请联系作者。

华为云开发者社区

关注

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！传送门：https://bbs.huaweicloud.com/

发布

暂无评论

创作场景

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

前言

匿名块的使用

​函数的创建

存储过程的创建

基本语法介绍

赋值：

条件语句：

循环语句：

GOTO 语句：

异常处理：

总结：

华为云开发者社区

评论

函数的创建