Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Stored Procedures/ DECLARE Syntax/ Basic Structure
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Basic Structure

Structure

A PL/SQL block can contain a sub-block which can be placed in any section. The following describes the architecture of a PL/SQL block:

  • Declaration section: declares variables, types, cursors, and regional stored procedures and functions used in the PL/SQL block. 
    DECLARE

    This section is optional if no variables need to be declared.

    • An anonymous block may omit the DECLARE keyword if no variable needs to be declared.
    • For a stored procedure, AS is used, which is equivalent to DECLARE. The AS keyword must be reserved even if there is no variable declaration section.
  • Execution section: specifies procedure and SQL statements. It is the main section of a program and is mandatory. 
    BEGIN
  • Exception-handling section: processes errors. It is optional. 
    EXCEPTION
  • End. Mandatory. 
    END;
/

    You are not allowed to use consecutive tabs in the PL/SQL block because they may result in an exception when the gsql tool is executed with the -r parameter specified.

Types

PL/SQL blocks are classified into the following types:

  • Anonymous block: a dynamic block that can be executed only for once. For details about the syntax, see Figure 1.
  • Subprogram: a stored procedure, function, operator, or advanced package stored in a database. A subprogram created in a database can be called by other programs.
Parent topic: DECLARE Syntax