Abstract:Currently, two procedural SQL languages are supported in the capabilities of GausSDB (DWS), namely PostgreSQL based PL/ PGSQL and Oracle based PL/SQL. In this article, we introduce the basic capabilities of GausSDB (DWS) for procedural SQL through anonymous blocks, functions, and stored procedures.
This article is from the Huawei cloud community “GausSDB (DWS) SQL advanced PLSQL (I) – anonymous blocks, functions and stored procedures”, the original author: XXXSQL123.
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 complex conditional evaluation functions and use them later to define operators or to use them for 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 the database server are not on the same machine, all of this can cause interprocess communication and burden the network.
With PLSQL, you can group a block of computations and a series of queries within the database server, which gives you a procedural language capability and makes SQL easier to use with reduced client/server communication overhead.
- Extra round trips between the client and server are eliminated.
- Intermediary results that are not needed by the client need not be collated or sent between the server and the client.
- Multiple rounds of query parsing can be avoided.
Currently, two procedural SQL languages are supported in the capabilities of GausSDB (DWS), namely PostgreSQL based PL/ PGSQL and Oracle based PL/SQL. In this article, we introduce the basic capabilities of GausSDB (DWS) for procedural SQL through anonymous blocks, functions, and stored procedures.
Use of anonymous blocks
Anonymous blocks are typically used for scripts that are not executed frequently or for 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 style, the following two methods are now supported, and there is good compatibility support for stored procedures migrated from Oracle to GausSDB (DWS).
√ Oracle style – ends with a backslash:
Syntax format:
[DECLARE [declare_statements]]
BEGIN
execution_statements
END;
/Execute use cases:
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, anonymous blocks are wrapped:
Syntax format:
DO [ LANGUAGE lang_name ] code; Execute use cases:
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 EXECUTEAs you can see, GaussDB(DWS) not only supports PL/SQL compatibility for Oracle, but also supports DBMS_OUTPUT_LINE in Oracle’s advanced package. So we can also mix the two styles and find that it is 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 EXECUTEFunction creation
Since anonymous block GausSDB supports both Oracle and PostgreSQL creation styles, the function will also support both styles.
Let’s take a look at the specific use of it! (. I _ I.
√ 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' }Execute use cases:
Define a function as an 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:
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 a 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 /Execute use cases:
PL/ SQL-style functions defined by 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)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 define the OUT parameter using Oracle’s PL/SQL style, you need to use the stored procedure, see the following section.
The creation of stored procedures
Stored procedures and functions are basically similar, both belong to the procedural SQL language, the difference is that stored procedures do not return a value.
※ Note that currently, GAUSSDB (DWS) only supports Oracle’s CREATE PROCEDURE syntax. PostgreSQL’s CREATE PROCEDURE syntax is not supported at this time.
* PostgreSQL style:
Not yet 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 /Execute use cases:
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 the above brief introduction to procedural SQL language in GausSDB (DWS), we have a general understanding of anonymous block (DWS) in GausSDB (DWS), the creation of functions, stored procedures, the following will be a brief introduction to some simple syntax in procedural SQL language introduction.
Introduction to Basic Grammar
Assignment:
Supports the use of both = and := assignments. Both of the following assignments are supported.
a = b;
a := b + 1;Conditional statement: Support IF… THEN … END IF; IF … THEN … ELSE … END IF; IF … THEN … ELSEIF … THEN … ELSE … END IF; Where ELSEIF can also be written as ELSIF.
Introduction to grammar:
-- Case 1: IF THEN -- END IF; -- Case 2: IF condition expression THEN -- ELSE -- END IF; - Case 3: IF conditional expression 1 THEN - expression will execute the statement ELSEIF conditions after 1 to TRUE expression THEN 2-2 to TRUE will execute the statement after the ELSE - the above expression to TRUE will execute after the IF statement END;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 for while, for, and foreach. The loop control statement continue, break can also be added appropriately during the loop.
Introduction to grammar:
WHILE condition 1 THEN -- the statement that needs to be executed within the LOOP 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 statements:
Support for the use of goto syntax.
Introduction to grammar:
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:
Introduction to grammar:
[<<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 TABLEConclusion:
The support for procedural SQL in GausSDB (DWS) is primarily PostgreSQL compatible with Oracle, as well as some Oracle advanced packages and some Oracl-specific syntax support. When migrating Oracle or PostgreSQL, the migration of functions or stored procedures can reduce the additional effort required for compatibility.
This has covered the creation and basic usage of anonymous blocks, functions, and stored procedures in GausSDB (DWS). Of course, GausSDB (DWS) supports procedural SQL. In the next few days, we will introduce cursors, user-defined types, and more
Want to know more information about GUASSDB (DWS), welcome WeChat search “GAUSSDB DWS” pay attention to WeChat public number, and you share the latest and most complete PB series silo black technology, background can also obtain many learning materials Oh ~
Click on the attention, the first time to understand Huawei cloud fresh technology ~