Abstract: GaussDB(DWS) currently supports two procedural SQL languages, postgresQl-based PL/pgSQL and Oracle-based PL/SQL. In this article we introduce the basic capabilities of GaussDB(DWS) for the procedural SQL language through anonymous blocks, functions, and stored procedures.
This article is shared with “GaussDB(DWS) SQL Advanced PLSQL (1) – Anonymous Blocks, Functions, and Stored Procedures” by huawei cloud community.
preface
The PLSQL language in GaussDB(DWS) is a loadable procedural language that creates functions that can be used anywhere built-in functions can be used. For example, you can create evaluation functions for complex conditions and use them later to define operators or to use them in index expressions.
SQL is used as the 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 of this causes interprocess communication and will impose a network burden.
With PLSQL, you can group a whole block of calculations and a series of queries inside the database server, giving you the power of a procedural language and making SQL easier to use while saving client/server communication overhead.
- Additional round-trip communication between the client and server is eliminated.
- Intermediate results that are not needed by the client do not have to be collated or passed between the server and the client.
- Multiple rounds of query parsing can be avoided.
Currently, GaussDB(DWS) supports two procedural SQL languages: POSTgresQl-based PL/pgSQL and Oracle-based PL/SQL. In this article we introduce the basic capabilities of GaussDB(DWS) for the procedural SQL language through anonymous blocks, functions, and stored procedures.
Use of anonymous blocks
Anonymous blocks are typically used for scripts that are infrequently executed or activities that are not repeated. They are executed in a session and are not stored.
GaussDB(DWS) supports the following two invocation methods based on the integration of PostgreSQL and Oracle styles, which is compatible with stored procedures migrated from Oracle to GaussDB(DWS).
√ Oracle-style – ends with a backslash:
Syntax format:
[DECLARE [declare_statements]]
BEGIN
execution_statements
END;
/
Copy the codeExecute the 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
Copy the code√ PostgreSQL style – start with DO and wrap anonymous blocks with $$:
Syntax format:
DO [ LANGUAGE lang_name ] code;
Copy the codeExecute the 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
Copy the codeGaussDB(DWS) not only supports Oracle PL/SQL compatibility, but also supports the dbMS_output.put_line function in Oracle advanced packages. So we can also mix the two styles, and discovery is supported. Lambda to the minus lambda to the 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
Copy the codeFunction creation
Since GaussDB supports both Oracle and PostgreSQL creation styles, the function also supports both.
Let’s have a look at the specific use of it! (. I _ I.
√ PostgreSQL:
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' }Copy the codeExecute the use case:
Define functions in the form of SQL queries:
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)Copy the codeDefine functions in the form of the 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)
Copy the codeDefine the function returned 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)
Copy the code√ 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 /Copy the codeExecute the use case:
Oracle PL/ SQL-style functions defined for Oracle:
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)
Copy the codeExample 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)
Copy the codeTo define OUT parameters using Oracle’s PL/SQL style, you need to use stored procedures, as shown in the following section.
Creation of stored procedures
Stored procedures and functions are similar in function, both belong to procedural SQL language, the difference is that stored procedures do not return values.
Note that GaussDB(DWS) supports only Oracle CREATE PROCEDURE syntax, but does not support PostgreSQL CREATE PROCEDURE syntax.
* PostgreSQL style:
Not supported yet.
√ 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 /Copy the codeExecute the 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)
Copy the codeAfter a brief introduction to GaussDB(DWS) procedural SQL language, we have a general understanding of the creation of anonymous blocks, functions, and stored procedures in GaussDB(DWS). The following is a brief introduction to some simple syntax in procedural SQL language.
Introduction to Basic Syntax
Assignment:
Supports the use of both = and := assignments. Both of the following assignments are supported.
a = b;
a := b + 1;
Copy the codeConditional statement:
Support the 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 conditional expression THEN -- statement to be executed after the expression is TRUE END IF; -- Case 2: IF conditional expression THEN -- IF the expression is TRUE THEN -- IF the expression is FALSE ELSE -- IF the expression is FALSE END IF; ELSEIF; ELSEIF; ELSEIF; ELSE -- END IF;Copy the codeExample:
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)
Copy the codeLoop statement:
Supports while, for, and foreach. Loop control statements continue,break can also be added during the loop.
Grammar introduction:
WHILE conditional expression 1 THEN -- END LOOP; FOR I IN result LOOP -- END LOOP; FOREACH var IN result LOOP END LOOP;Copy the codeExample:
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)Copy the codeGOTO statements:
Supports the use of goto syntax.
Grammar introduction:
GOTO LABEL; -- several statements <<label>>Copy the codeExample:
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)
Copy the codeException handling:
Grammar introduction:
[<<label>>] [DECLARE declarations] BEGIN statements EXCEPTION WHEN condition [OR condition ...] THEN handler_statements [WHEN condition [OR condition ...] THEN handler_statements ...] END;Copy the codeExample:
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 TABLECopy the codeConclusion:
GaussDB’s (DWS) support for procedural SQL is mainly compatible with PostgreSQL and Oracle. It also supports some advanced Oracle packages and some Oracle-specific syntax. When migrating Oracle or PostgreSQL, migrating functions or stored procedures can reduce the extra work required for compatibility.
This has covered the creation and basic use of anonymous blocks, functions, and stored procedures in GaussDB(DWS). Of course, GaussDB(DWS) supports more than just procedural SQL. In the coming days, you will be introduced to cursors, user-defined types and more
Click to follow, the first time to learn about Huawei cloud fresh technology ~