PL combines the data processing capabilities of SQL with the capabilities of a procedural language and supports direct processing of SQL statements without the need to learn a new API.
Like other procedural programming languages, PL supports declaring constants and variables, controlling program flow, defining subprograms, and capturing runtime errors.
Exception handling
PL can easily detect and handle errors.
When an error occurs, PL reports an exception, stops the current execution task, and performs exception handling on the corresponding PL block. Therefore, when using PL, you do not need to troubleshoot each operation like you would 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 properties that no longer exist after the block is executed. This helps avoid 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 (such as OBClient) 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 declaring variables and constants, which can be used in expressions.
During program execution, the values of variables can be changed, but the values of constants cannot be changed.
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 OceanBase Database.
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 is executed for each event or for each row affected by the event. For example, you can create a trigger that executes 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 the PL system packages provided by OceanBase Database.
The only PL I/O system package supported in the current version is DBMS_OUTPUT. Therefore, you need to use OBClient to display the output of DBMS_OUTPUT and execute the SET SERVEROUTPUT ON command first.
Data abstraction
OceanBase Database supports data abstraction features such as cursors and composite variables, which can be used to process the basic properties of data without delving into 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 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 have different data types and are referred to as fields. You can access each field by name. A record variable can store entire rows of a table or specific columns from a table's rows.
Using the %ROWTYPE attribute
The %ROWTYPE attribute can be used to declare a record type that represents a complete or partial row in a table or view of 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 that 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 variables that retain database values.
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 are the most important extension feature of PL over SQL statements.
PL includes three types of control statements:
Conditional control statements: Execute different statements based on different conditions.
Loop control statements: Repeat the execution of the same statement with different data values.
Sequential control statements: Jump to the statement corresponding to a specified label or perform no operation.
Processing multiple rows of query results
PL can send an SQL query and process multiple rows of results at once.
You can use basic loop control statements or execute a single statement to run the query, retrieve the results, and process them.