Explore the procedural SQL language capabilities of GaussDB (DWS)

Abstract: mainly supports two procedural SQL languages in the current capabilities of GaussDB (DWS), namely PostgreSQL-based PL/pgSQL and Oracle-based PL/SQL. In this article, we introduce to you the basic capabilities of GaussDB (DWS) for the procedural SQL language through anonymous blocks, functions, and stored procedures.

This article is shared from the Huawei Cloud Community " GaussDB (DWS) Advanced SQL PLSQL (1)-Anonymous Blocks, Functions and Stored Procedures ", the original author: xxxsql123.

Preface

The PLSQL language in GaussDB (DWS) is a loadable procedural language. The functions created by it can be used wherever built-in functions can be used. For example, you can create calculation functions with complex conditions and later use them to define operators or use them in index expressions.

SQL is used as a query language by most databases. It is portable and easy to learn. But each SQL statement must be executed separately by the database server.

This means that the client application must send each query to the database server, wait for it to be processed, receive and process the results, do some calculations, and then send more queries to the server. If the client and database server are not on the same machine, all these will cause inter-process communication and will bring network burden.

Through the PLSQL language, a whole block of calculations and a series of queries can be grouped inside the database server, so that it has the ability of a procedural language and makes SQL easier to use, while saving the client/server communication overhead.

• The extra round-trip communication between the client and the server is eliminated.
• Intermediate results that are not required by the client need not be sorted or transmitted between the server and the client.
• Multiple rounds of query parsing can be avoided.

The current GaussDB (DWS) capabilities mainly support two procedural SQL languages, namely PostgreSQL-based PL/pgSQL and Oracle-based PL/SQL. In this article, we introduce to you the basic capabilities of GaussDB (DWS) for the procedural SQL language through anonymous blocks, functions, and stored procedures.

Use of anonymous blocks

Anonymous Block is generally used for scripts that are not frequently executed or activities that are not repeated. They are executed in a session and are not stored.

In GaussDB (DWS), through the integration of PostgreSQL and Oracle styles, the following two methods of invocation are currently supported, and there is a good compatibility support for the stored procedures of Oracle migration to GaussDB (DWS).

√ Oracle style-end with a backslash:

Syntax format:

[DECLARE [declare_statements]] 
BEGIN
execution_statements
END;
/

Implementation use case:

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 style-start with DO and wrap anonymous blocks with:

Syntax format:

DO [ LANGUAGE lang_name ] code;
Implementation use case:

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

At this time, careful friends will find that GaussDB (DWS) not only supports Oracle's PL/SQL compatibility support, but also supports the dbms_output.put_line function in the Oracle advanced package. So we can also mix the two styles and find that it is also supported. (＾－＾)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

Function creation

Since the anonymous block GaussDB supports the creation of two styles of Oracle and PostgreSQL, the function will of course also support both.

Let's take a look at the specific use together! (｡Ì _ í｡)

√ PostgreSQL style:

Syntax format:

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'
    }

Implementation use case:

Define the function as a form of SQL query:

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)

Define the function in the form of plpgsql language:

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)

Define the function that returns as 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 style:

Syntax format:

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
/

Implementation use case:

Defined as Oracle's PL/SQL style function:

Example 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)

Example 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)

If you want to use Oracle's PL/SQL style to define OUT parameters, you need to use stored procedures, please see the following chapters.

Creation of stored procedures

The functions of the stored procedure and the function are basically similar, both belong to the procedural SQL language, the difference is that the stored procedure does not return a value.

※ It should be noted that currently GaussDB (DWS) only supports Oracle's CREATE PROCEDURE syntax support, and temporarily does not support PostgreSQL's CREATE PROCEDURE syntax support.

× PostgreSQL style:

Not currently supported.

√ Oracle style:

Syntax format:

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 
/

Implementation use case:

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)

After a brief introduction to the GaussDB (DWS) procedural SQL language above, we have a general understanding of the creation of anonymous blocks, functions, and stored procedures in GaussDB (DWS). The following will briefly introduce some simple in the procedural SQL language Grammar introduction.

Basic grammar introduction

Assignment:

Supports the use of the two types of assignments: = and:=. The following two assignment methods are supported.

a = b;
a := b + 1;

Conditional statements:
Support IF ... THEN ... END IF; IF ... THEN ... ELSE ... END IF; IF ... THEN ... ELSEIF ... THEN ... ELSE ... END IF; ELSEIF can also be written as ELSIF.

Grammar introduction:

-- 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;

Example:

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)

loop statement:

Support the use of while, for, foreach. You can also add loop control statements continue, break appropriately during the loop.

Grammar introduction:

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

Example:

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 statement:

Support the use of goto syntax.

Grammar introduction:

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

Example:

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)

Exception handling:

Grammar introduction:

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

Example:

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

to sum up:

GaussDB (DWS) supports the procedural SQL language mainly for compatibility between PostgreSQL and Oracle. At the same time, it also supports some advanced packages of Oracle and some unique grammars of Oracle. When migrating Oracle or PostgreSQL, the migration of functions or stored procedures can reduce the extra workload for compatibility.

So far, the creation and basic use of anonymous blocks, functions, and stored procedures in GaussDB (DWS) have been introduced. Of course, GaussDB (DWS) supports more than that for the procedural SQL language. In the coming time, I will gradually introduce you to chapters such as cursors and user-defined types~

If you want to know more about GuassDB (DWS), welcome to search "GaussDB DWS" on WeChat and follow the WeChat official account, and share with you the latest and most complete PB-level digital warehouse black technology. You can also get a lot of learning materials in the background~

Click to follow and learn about Huawei Cloud's fresh technology for the first time~