PL combines the data processing capabilities of SQL with the procedural language capabilities, and supports processing SQL statements directly without learning new APIs.
Like other procedural programming languages, PL supports declaring constants and variables, controlling program flow, defining subprograms, and capturing runtime errors.
Exception handling
PL facilitates easy error detection and handling.
When an error occurs, the PL reports the exception, the database stops the current task, and exception handling is performed for the corresponding PL block. Therefore, when using PL, you do not need to debug each operation as 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 a declaration section, an executable section, and an exception handling section, respectively. Only the executable section is mandatory.
PL blocks are declared with local scope and cease to exist after execution, which helps prevent namespace conflicts with variables and subprograms.
Blocks can be nested within other blocks. Since a block is an executable statement, it can contain executable statements of other blocks. Each PL block can be assigned a label.
A PL block can also contain anonymous blocks. You can submit anonymous blocks to interactive tools (such as OBClient) or load them into programs. These anonymous blocks are executed 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 value of a variable can be changed, 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 a type of application.
You can write your own program packages or use the system packages provided by OceanBase AI Database.
Trigger
A trigger is a named PL unit stored in the database that responds to and runs when an event, known as a trigger event, occurs within the database.
You can specify whether the trigger fires before or after an event, and whether the trigger runs for each event or for each row of 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) is performed through SQL statements that store data in tables or query tables. The remaining PL I/O is handled by the PL system package provided by OceanBase AI Database.
The only PL/IO package supported by the current version is DBMS_OUTPUT. Therefore, you need to use OBClient to display the output of DBMS_OUTPUT and execute the command SET SERVEROUTPUT ON first.
Data abstraction
OceanBase AI Database supports data abstraction features such as cursors and composite variables, allowing you to work with basic data attributes without needing to delve too deeply into the details.
Cursors
When OceanBase AI Database executes an SQL statement, it saves the result set and processing information in an unnamed private memory area and sets a pointer to the starting value of this memory area. This pointer is called the cursor. Using a cursor, you can fetch one row of records at a time. Cursor attributes return status information about the cursor.
Compound variables
PL supports two types of composite variables: sets and records. A composite variable as a whole can be passed as a parameter to a subprogram.
The internal components of a collection are always of the same data type and are called 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 called fields. You can access each field by name. Record variables can hold a row of a table or specific columns within a row.
Use the %ROWTYPE attribute
The %ROWTYPE attribute can be used to declare a record type that represents a complete or partial row in a database table or view.
For each column in a complete or partial row, the record has a field with the same name and data type. The structure of a record changes as the structure of the row changes.
Use the %TYPE attribute
The %TYPE attribute can be used to declare a data item that has 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 that referenced item will also be updated accordingly. The %TYPE attribute can be used to declare variables to retain database values.
Abstract data types
Abstract data types (ADTs) consist of data structures and subprograms. ADTs are stored in the database. Instances of an ADT can be stored in a table and used as PL variables.
Variables that form the data structure are called attributes. Subprograms that manipulate attributes are called methods.
In the * _OBJECTS dictionary view, the OBJECT_TYPE of an ADT is TYPE. In the * _TYPES dictionary view, the TYPECODE of an ADT is OBJECT.
Control statements
PL control statements are the most important extension to SQL.
PL contains the following three types of control statements:
Conditional control statements: Execute different statements for data values under different conditions.
Loop control statements: Use different data values to repeatedly execute the same statement.
Sequence control statements: Jump to the statements corresponding to the specified labels, or perform no operation.
Process multiple rows of query results at a time
PLs can send SQL queries and process multiple rows of results at once.
You can use basic loop control statements, or simply execute a single statement to run the query, retrieve the results, and perform processing.