Procedural language (PL) combines the data processing capabilities of SQL with the processing capability of other procedural languages and supports the direct processing of SQL statements. You do not need to learn new APIs to use PL.
As with other procedural languages, PL supports the declaration of constants and variables, program flow control, subprogram definition, and runtime error capture.
Exception handling
PL supports fast exception detection and handling.
When an error occurs, PL will raise an exception, stop the ongoing task, and troubleshoot the corresponding PL block. In this way, you do not need to verify each operation as with the C language.
PL blocks
The basic unit of the PL source program is block, which consists of declarations and statements.
A PL block is defined by the DECLARE, BEGIN, EXCEPTION, and END keywords. A PL block is divided into a declarative part, an executable part, and an exception-handling part based on these keywords. Only the executable part is required.
The declarations of a PL block are local properties that disappear after the block is executed. This avoids mixing variable namespaces with subprogram namespaces.
PL blocks can be nested. Blocks are executable statements. Therefore, you can nest them in executable statements of other blocks. You can set a label for each PL block.
A PL block contains anonymous blocks. You can submit anonymous blocks to an interactive tool such as OceanBase Client (OBClient) or a program. An anonymous block runs in the interactive tool or program only once and is not stored in the database.
Variables and constants
PL allows you to declare variables and constants and use them in expressions.
When a program runs, the values of the variables can be modified, but the values of the constants cannot be modified.
Packages
A program package consists of logically relevant PL types, variables, constants, subprograms, cursors, and exceptions.
It is compiled and stored in a database, and therefore, can be regarded as an application program.
You can write your own packages or use system packages provided by OceanBase Database.
Triggers
A trigger is a named PL unit. It is stored in the database and fires for events occurred in the database.
You can specify whether a trigger fires before or after an event and whether the trigger fires for each event or each row of the event. For example, you can create a trigger and set the trigger to fire each time an INSERT statement is executed on the t1 table.``
Input/Output
Most I/O operations of PL are completed by using SQL statements that store data in a table or query a table. Other I/O operations of PL are completed by the PL system packages provided by OceanBase Database.
In the current version, PL supports only the DBMS_OUTPUT package for processing I/O operations. Therefore, you must run the SET SERVEROUTPUT ON command to display the output of DBMS_OUTPUT on OBClient.
Data abstraction
OceanBase Database supports data abstraction features such as cursors and composite variables, which can be used to process basic data attributes without considering the details.
Cursors
When OceanBase Database 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 value of this memory area. This pointer is called a cursor. You can get one row of data each time by using the cursor, and the cursor state is returned in the cursor attribute.
Composite variables
PL has two types of composite variables: collection and record. Composite variables can be passed to subprograms as parameters.
Internal components of a collection must use the same data type and are referred to as elements. Each element can be accessed by using a unique index. A collection generally includes lists and arrays.
Internal components of a record may use different data types and are referred to as fields. You can access the fields based on the field names. Record variables can retain rows in a table or some columns of rows.
Use the ％ROWTYPE attribute
You can use the ％ROWTYPE attribute to declare the record type that represents an entire row or part of rows in a database table or a view.
For the columns of a full or partial row, the records have a field with the same name and data type. The record structure varies based on the row structure.
Use the %TYPE attribute
You can use the ％TYPE attribute to declare a data item that has the same data type as the previously declared variable or column, without the need to know the data type.
When the declaration of the referenced items changes, the declaration of the referencing items changes accordingly. You can use the ％TYPE attribute to declare variables to reserve values in a database.
Abstract data types
An abstract data type (ADT) consists of a data structure and subprograms. ADTs are stored in the database. ADT instances can be stored in a table and used as PL variables.
The variables that form the data structure are referred to as attributes. The subprograms that manipulate the attributes are referred to as methods.
In * _OBJECTS dictionary views, OBJECT_TYPE of an ADT is TYPE. In * _TYPES dictionary views, TYPECODE of an ADT is OBJECT.
Control statements
PL control statements are the most important extension of SQL statements.
PL comes with three types of control statements:
Conditional control statement: runs different statements for different data values.
Loop control statement: repeatedly executes the same statement by using different data values.
Sequential control statement: transfers to the statement corresponding to the specified label or does not execute any operation.
Process multiple rows of query results concurrently
PL can send SQL queries and concurrently process multiple rows of results.
You can use basic loop control statements or use single statements to run the queries, retrieve results, and perform processing.