暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GaussDB(DWS) SQL进阶之PLSQL(一)-匿名块、函数和存储过程

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

GaussDB DWS 2022-02-22
3151

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函数也做了支持。所以我们也可以将两个风格混用，发现也是支持的。
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)

语法格式：


向上滑动阅览

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-# 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 integer

postgres-# 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_bod

 /

执行用例：


向上滑动阅览

postgres=# CREATE OR REPLACE PROCEDURE prc_add

postgres-# (

postgres(#     param1    IN   INTEGER,

postgres(# param2    IN OUT  INTEGE

Rpostgres(# )

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;
条件语句：


向上滑动阅览

-- 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$# BEGINpostgres$#     IF i>5 AND i<10 THEN

postgres$#         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$#     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 THENpostgres$#         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语言的支持不止如此，下一期将为大家介绍游标有关内容，敬请关注~

做个题测试一下掌握程度

(单选题）GaussDB(DWS)支持Oracle中的dbms_output.put_line函数吗？

A

不支持

B

支持

（点击选项查看答案）

A

往期精彩回顾




gaussdb
文章转载自GaussDB DWS，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
1708次阅读
2025-04-09 15:33:27
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
579次阅读
2025-04-10 15:35:48
北京市公安局数据库大单：华为云GaussDB 1427万、金仓数据库 510万！
通讯员
269次阅读
2025-04-11 12:33:24
新疆维吾尔自治区行政事业单位数据库2025年框架协议采购（二次）入围结果公布
通讯员
133次阅读
2025-04-14 12:21:01
2687万！上海浦东发展银行采购GaussDB软件许可和服务
通讯员
67次阅读
2025-04-23 11:36:39
GaussDB 嵌入式SQL：OPEN命令深度解析与实战指南
Gauss松鼠会
46次阅读
2025-03-28 16:35:13
GaussDB嵌入式SQL命令EXECUTE IMMEDIATE
Gauss松鼠会
42次阅读
2025-03-28 16:35:39
GaussDB执行SQL语句
Gauss松鼠会
38次阅读
2025-03-27 15:30:48
GaussDB 数据库连接技术全解析：从基础连接到高性能集群
Gauss松鼠会
37次阅读
2025-03-27 15:30:04
GaussDB 嵌入式SQL：DEALLOCATE DESCRIPTOR深度解析与实战指南
Gauss松鼠会
36次阅读
2025-03-28 16:34:12