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 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 report an exception. In this case, the current task is terminated and PL handles the exception for 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. You can set labels for PL blocks.
Declarations are local properties that disappear after a block is executed. This avoids mixture of variable namespaces with subprogram namespaces.
You can nest blocks. Blocks are executable statements. Therefore, you can nest them in executable statements of other blocks.
You can upload a block to an interactive tool such as OBClient or migrate it to a program. The block runs only once in the interactive tool or the program, and is not stored in the database, so the block is also referred to as an anonymous block.
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 package consists of logically related 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 compile your own package, or use the system package that comes with 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.
I/O
Most input/output (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 package 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
Data abstraction allows you to process basic properties of data without concerning details.
For example, you can design a data structure and then design its algorithms.
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 referred to as 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. You can access the elements based on the unique index of each element. 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 table rows in a table and columns of the rows.
Use the ％ROWTYPE attribute
You can use the ％ROWTYPE attribute to declare the record type that represents a row in a database table or in a view.
For the columns of a full or partial row, the records have a field with the same name and data type. When the structure of rows changes, the structure of the records changes as well.
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 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 that manipulate data.
The variables that form the data structure are referred to as attributes. The subprograms that manipulate the attributes are referred to as methods. ADTs are stored in the database. ADT instances can be stored in a table and used as PL variables.
You can divide a large system into multiple logic components based on ADTs, to reduce complexity. These logic components can be reused.
In * _OBJECTS static data dictionary views, OBJECT_TYPE of an ADT is TYPE. In * _TYPES static data dictionary views, TYPECODE of an ADT is OBJECT.
Control statements
Control statements are the most important PL extension of SQL.
PL comes with three types of control statements:
Conditional statement: runs different statements for different data values.
Loop statement: uses a series of different data values to repeat the same statement.
Sequential control statement: transfers to specified labeled statements 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 statements or use single statements to run the queries, retrieve results, and perform processing.