- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

GaussDB(DWS) SQL进阶之PLSQL（一）-匿名块、函数和存储过程

xxxsql123 发表于 2021/05/09 11:50:44 2021/05/09

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

前言

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: 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-# 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: 65
 func_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>>]
[DECLARE
    declarations]
BEGIN
    statements
EXCEPTION
    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 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_zero
 fun_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级数仓黑科技，后台还可获取众多学习资料哦~

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

GaussDB(DWS) SQL进阶之PLSQL（一）-匿名块、函数和存储过程

前言

匿名块的使用

函数的创建

存储过程的创建

基本语法介绍

赋值：

条件语句：

循环语句：

GOTO语句：

异常处理：

总结：

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

GaussDB(DWS) SQL进阶之PLSQL（一）-匿名块、函数和存储过程

前言

匿名块的使用

函数的创建

存储过程的创建

基本语法介绍

赋值：

条件语句：

循环语句：

GOTO语句：

异常处理：

总结：

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品