Block quoting support

Stored procedures are defined as a block or body of numerous lines of text. A block-quoting mechanism is provided to help ease the definition of the procedure body and to make the content more readable for debugging and learning.

A section of text bounded by BEGIN_PROC and END_PROC is a block quote. The following is an example:

CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    string varchar;
BEGIN
    string := 'This string is quoted';
END;
END_PROC;

As shown in the example, quotation marks are not escaped within the block-quoted string. The string content is always written literally. Backslashes have no special “escape” meaning.

The stored procedure body is in clear text format by default. Privileged users can use the SHOW PROCEDURE VERBOSE command to review the stored procedure. If necessary, you can “hide” the procedure code when you create the procedure.

Block quotes are intended for use only in NZPLSQL body quoting or stored procedure invocation. The execution commands CALL and EXEC[UTE[ PROCEDURE]] support them, although SELECT does not. They can be used inside a stored procedure body to build DDL statements where they are allowed, but they have no quoting implications inside the body. If they are used inside the body, make sure that there are an equal number of both keywords and that they match (a BEGIN_PROC appears before the corresponding END_PROC) to avoid errors. If your program logic dictates that they are not matching, they must be broken up (that is, 'BEGIN_' || 'PROC').

Both BEGIN_PROC and END_PROC can appear inside a single or double-quoted string in normal SQL statements if the SQL statements are not inside a block quote. For example:

select "BEGIN_PROC" from <table>;
insert into <table> values ('BEGIN_PROC');

If you want to do this inside a block quote body, then you must have a matching END_PROC. For example:

CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    string varchar;
BEGIN
    string := 'This string is quoted';
    -- This comment is the match for below BEGIN_PROC
    insert into va values ('END_PROC');
END;
END_PROC;

Because nested BEGIN_PROC and END_PROC keywords have no quoting implications, the following example is not supported:

CREATE OR REPLACE PROCEDURE name() RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    string varchar;
BEGIN
    -- The next statement is a syntax error since it is not quoted
    string := BEGIN_PROC This string is not quoted END_PROC;
END;
END_PROC;