Skip to main content

Features of PL

PL combines the data processing capabilities of SQL with the processing capabilities of procedural languages and supports direct processing of SQL statements without the need to learn a new API.

Like other procedural programming languages, PL supports the declaration of constants and variables, control of program flow, definition of subprograms, and capture of runtime errors.

Exception handling​

PL can easily detect and handle errors.

When an error occurs, PL reports an exception, stops the current execution task of the database, and performs exception handling for the corresponding PL block. Therefore, when using PL, you do not need to troubleshoot each operation like in a C program.

PL blocks​

The basic unit of a PL source program is a block, which consists of declarations and statements.

A PL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into the declaration section, the executable section, and the exception handling section. Only the executable section is required.

Declarations in a PL block are local attributes that no longer exist after the block is executed. This prevents namespace conflicts between variables and subprograms.

Blocks can be nested within other blocks. Since blocks are executable statements, they can appear within the executable statements of other blocks. Each PL block can have a label.

PL blocks also include anonymous blocks. You can submit anonymous blocks to interactive tools or load them into programs. These blocks run only once in the interactive tool or program and are not stored in the database.

Variables and constants​

PL supports the declaration of variables and constants, which can be used in expressions.

During program execution, the value of a variable can change, but the value of a constant cannot.

Packages​

A package consists of logically related PL types, variables, constants, subprograms, cursors, and exceptions.

Packages are compiled and stored in the database, so they can be considered as applications.

You can write your own packages or use the system packages provided by seekdb.

Triggers​

A trigger is a named PL unit stored in the database that responds to and executes events occurring in the database, known as trigger events.

You can specify whether a trigger is executed before or after an event, and whether it runs for each event or for each row affected by the event. For example, you can create a trigger that runs every time an INSERT statement is executed on the t1 table.

Input and output​

Most PL input and output (I/O) operations are performed through SQL statements that store data in tables or query tables. The remaining PL I/O operations are handled by seekdb's PL system packages.

The only PL I/O system package supported in the current version is DBMS_OUTPUT. To display the output information of DBMS_OUTPUT, you need to use the mysql client and execute the command SET SERVEROUTPUT ON first.

Data abstraction​

seekdb supports data abstraction features such as cursors and composite variables, allowing you to handle the basic properties of data without delving into the details.

Cursors​

When seekdb executes an SQL statement, it stores the result set and processing information in an unnamed private memory area and sets a pointer to the start of this memory area. This pointer is known as a cursor. Using a cursor, you can retrieve one record at a time, and the cursor attributes return the status information of the cursor.

Composite variables​

PL includes two types of composite variables: collections and records. An entire composite variable can be passed as a parameter to a subprogram.

The internal components of a collection are always of the same data type and are referred to as elements. Each element can be accessed using a unique index. Collections generally include lists and arrays.

The internal components of a record can be of different data types and are referred to as fields. You can access each field by name. A record variable can store a row from a table or specific columns from a row.

Using the %ROWTYPE attribute​

The %ROWTYPE attribute can be used to declare a record type that represents a complete or partial row from a table or view in the database.

For each column in a complete or partial row, the record has a field with the same name and data type. The structure of the record changes as the structure of the row changes.

Using the %TYPE attribute​

The %TYPE attribute can be used to declare a data item with the same data type as a previously declared variable or column, without knowing what the data type is.

If the declaration of the referenced item changes, the declaration of the referencing item will also change. The %TYPE attribute can be used to declare a variable that stores a database value.

Abstract data types​

Abstract data types (ADTs) consist of data structures and subprograms. ADTs are stored in the database. Instances of ADTs can be stored in tables and used as PL variables.

Variables that form a data structure are called attributes. Subprograms that manipulate attributes are called methods.

In the dictionary view * _OBJECTS, the OBJECT_TYPE of an ADT is TYPE. In the dictionary view * _TYPES, the TYPECODE of an ADT is OBJECT.

Control statements​

Control statements in PL are the most important extension to SQL statements.

PL includes three types of control statements:

  • Conditional control statements: execute different statements based on different data values.

  • Loop control statements: repeat the same statement with different data values.

  • Sequential control statements: jump to the statement corresponding to a specified label or perform no action.

Processing multiple rows of query results at once​

PL can send an SQL query and process multiple rows of results at once.

You can use basic loop control statements or complete the entire process of running a query, retrieving results, and processing them with a single statement.

Contents

Edit this page