Working with Netezza Stored Procedures

  • Post author:
  • Post last modified:June 6, 2018
  • Post category:Netezza
  • Reading time:6 mins read

Netezza stored procedures are used to encapsulate the business logic and same time handle the exceptions. SQL provides the power to get and update the database information on the host server, and the procedure language provides the logic for if-then-else branching and application processing on the data.

Read:

Netezza Stored Procedures

For example, you may want to check the table if its existed in database before dropping it. You achieve this by creating stored procedure. e.g. CALL DROP_IF_EXIST(table_name);

if you want to verify the particular input data is in date format, then you can encapsulate the logic to check date format into stored procedure. You can make decision such as if input data is in date format then return same otherwise return null.

Read:

Netezza Procedural Language (NZPLSQL)

You implement stored procedures creating applications using the NZPLSQL language. NZPLSQL is a scripting language embedded in SQL based on Postgres PL/pgSQL language and designed for the Netezza host environment. As procedural language, it has branch, loop, subprogram structures. Subprograms known as procedures take arguments and declare internal variables. These procedures once stored in database can be called multiple times within same Netezza hosts.

NZPLSQL Supported procedural logics

NZPLSQL language provides following procedural logic:

  • Conditionals (if/else)
  • Looping (while, for)
  • Execution of SQL (including dynamic SQL)
  • Variables
  • Returning a scalar result or a result set
  • Input arguments
  • Execution in the calling context (session and transaction)
  • Extending functionality (adding NZPLSQL library routines)

Related reading:

Netezza Stored Procedure Block Quoting Support

To make the content more readable for debugging and learning, Netezza provides a block-quoting mechanism. Stored producers are defined as a block or body or numerous lines of text.

A section of text bounded by BEGIN_PROC and END_PROC is a block quote.

CREATE OR REPLACE PROCEDURE procedure_name() RETURNS INT4 LANGUAGE NZPLSQL
AS
BEGIN_PROC
DECLARE
string varchar;
BEGIN
string:= 'This is Sample Netezza Stored Procedure';
END;
END_PROC;

Exceptions and Error Handing in Netezza Stored Procedures

Any error occurring in a NZPLSQL procedure aborts the execution of the procedure, and aborts the surrounding transaction too. The system returns to the main loop to pick the next query from the client applications.

The NZPLSQL supports two clauses for exception processing:

TRANSACTION_ABORTED

You can use this clause to add statements that you want to run in case of errors that cause transaction to abort. ROLLBACK is required to continue, and ROLLBACK should be the fist statement.

OTHERS

You can use the OTHERS clause to specify the statements to run when any error occurs within the procedure block.

You can handle some of the exceptions by adding exception adding section in stored procedures. You cannot handle exception that can leave Netezza system or database in bad state.

The NZPLSQL language uses the EXCEPTION statement to process exceptions:

EXCEPTION
WHEN TRANSACTION_ABORTED THEN
ROLLBACK;
statements1
RAISE ERROR 'failed: %', sqlerrm;
WHEN OTHERS THEN
statements2
RAISE NOTICE 'Caught Exception, Execution continuing %', sqlerrm;

You can place above statement at the end of the block. This section will not execute if there are no errors.

Netezza Stored Procedure Returning a Result Set

Netezza stored procedures returns a unique value. It can also return the results set in the form of a tables.

Read:

In order to create stored procedure that returns results set, follow below steps;

  • Define the stored procedure with a return value of “RETURNS REFTABLE (<table- name>)” to indicate that it returns a result set that looks like the specified table.
  • Inside the body of the procedure, use the variable REFTABLENAME to refer to the results table.
  • Insert records to REFTABLENAME inside stored procedure

For example. Below stored procedure can return result set using reference tables.

CREATE OR REPLACE PROCEDURE return_result(timestamp) RETURNS REFTABLE(tbl) LANGUAGE NZPLSQL AS
BEGIN_PROC
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
RETURN REFTABLE;
END;
END_PROC;

Executing Netezza stored Procedures

You can execute the stored procedure by using below commands

SELECT procedure()
CALL procedure()
EXECUTE procedure()

Advantages of using Netezza Stored Procedures

  • Improved performance: Because the stored procedures resides on the host, there there are performance benefits because it avoids the network time between an application client system and the Netezza host
  • Easy to Maintain: It resides only one location i.e. Netezza host. Hence versioning and updates need only be made in one place to keep the application up-to-date for all users
  • Security: You can grant a user permission to run a stored procedure without granting that user explicit access to underlying tables or views

 

Tags: , ,