探索 GaussDB(DWS) 的过程化 SQL 语言能力-低调大师

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

2021-05-24 487

摘要：在当前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 world
ANONYMOUS 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 world
ANONYMOUS 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 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 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 i10 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 FUNCTION
postgres=# call func_loop(1);
INFO:  current sum: 10
INFO:  current sum: 65
 func_loop
-----------
        75
(1 row)

GOTO 语句：

支持 goto 语法的使用。

语法介绍：

GOTO 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$# <>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)

异常处理：

语法介绍：

[<>][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 级数仓黑科技，后台还可获取众多学习资料哦~

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

微信关注我们

原文链接：https://blog.51cto.com/u_15214399/2806137

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

BS结构RFID读写器web插件开发之八-----DES和3DES加解密

DES和3DES加解密关于DES和3DES DES是一种标准加密算法,它使用对称密钥加密法.DES算法密钥为8字节.3DES（即Triple DES）是DES向AES过渡的加密算法,3DES算法是指使用双长度（16字节）密钥K=（KL||KR）将8字节明文数据块进行3次DES加密/解密。如下所示：Y = DES( KL[DES-1( KR[DES( KL[X] )] )] )解密方式为：X = DES-1( KL[DES( KR[DES-1( KL[Y] )] )] )其中，DES( KL[X] )表示用密钥K对数据X进行DES加密，DES-1( KR[Y] )表示用密钥K对数据Y进行解密3DES是一种比较安全的加密算法,在IC卡和CPU卡中都得到广泛的应用. IC卡web插件支持的3DES 友我科技IC卡web插件系统支持DES和3DES算法,可以单独使用,也可以直接配合IC卡使用,因为使用的是标准算法, 所以加密的DES或者3DES数据可以在别的设备上解密.兼容性没有问题. DES和3DES接口 IC卡web插件支持的DES和3DES有多种方式, 一种是DES和3DES加解密函数...

2021-05-24

485

前置条件：系统已安装jdk 一、下载jmeter包，通过客户端放在安装目录 1、本次安装的版本为unzip apache-jmeter-5.0.zip unzip apache-jmeter-5.0.zip 2、配置环境变量执行 vim /etc/profile 在后面加入： exportJMETER_HOME=/root/apache-jmeter-5.0 exportCLASSPATH=$JMETER_HOME/lib/ext/ApacheJMeter_core.jar:$JMETER_HOME/lib/jorphan.jar:$JMETER_HOME/lib/logkit-2.0.jar:$CLASSPATH exportPATH=$JMETER_HOME/bin:$PATH:$HOME/bin 3、执行： source /etc/profile ，让配置生效 4、验证安装 jmeter -v 如果出现:-bash: /root/apache-jmeter-5.0/bin/jmeter: Permission denied 则给jmeter执行文件授权：执行：chmod 7...

2021-05-24

687

资源下载

更多资源

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。