Overview
ALTIBASE Stored Procedure is very similar to the ORACLE's PL/SQL in concept.
ALTIBASE Stored Procedure is one of database object comprising a declaration section, a body section, exception handler section.
A body section can also involve multiple subordinate body sections.
ALTIBASE Stored Procedure is considered to be PSM(Persistent Stored Modules) as well.
Hereafter, we call them as ALTIBASE Stored Procedure(Shortly, Stored Procedure) to put them together.
Anonymous Block
Anonymous block doesn't have any name of procedure which it belongs.
Simply, it can be transformed as below.
Even if you transformed "Anonymous Block" statement as below, the execution of 'exec proc_name' statement should be accompanied after then.
That's why an execution of the Altibase psm statement is not caused by issuing corresponding statement unlike an anynonymous block of oracle.
ORACLE
ALTIBASE HDB
Collections and Records
A collection is an ordered group of elements, all of the same type.
A record is a group of related data items stored in fields, each with its own name and datatype.
- Associative arrays, also known as index-by tables, let you look up elements using
arbitrary numbers and strings for subscript values. These are similar to hash tables. - Nested tables hold an arbitrary number of elements.
- Varrays (short for variable-size arrays) hold a fixed number of elements (although
you can change the number of elements at runtime). Varrays is not supported in Altibase.
DML statements that have RETURNING clause
This clause can be employed at ALTIBASE Stored Procedure and PL/SQL as well.
The manipulating data and retrieving result can be done in a time using RETURNING CLAUSE.
This clause is a efficient way to access a table on account of its dual access to a table in one time.
Returning clause will be applied at ALTIBASE HDB V7.
Just now, it can be transformed as follows.
ORACLE
ALTIBASE HDB
Using FORALL and BULK COLLECT Together
This functionality is not supported in ALTIBASE HDB.
Pragma Option
ALTIBASE HDB does not support this functionality.
Array
VArray(Variable Array) and Associative Array are employed as kinds of collections described above.
ALTIBASE HDB does not support VArray functionality.
Datatypes
The ORACLE PL/SQL has its own datatypes having corresponding type to data types which ALTIBASE HDB supports.
PL/SQL provides many predefined datatypes.
As described below, PL/SQL DATATYPES and ALTIBASE data types are compatible with each other.
ORACLE |
ALTIBASE HDB |
Comments |
---|---|---|
BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DEC, |
INTEGER, FLOAT, DECIMAL, DOUBLE, NUMBER, REAL |
PL/SQL Number Types |
CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2, |
CHAR, VARCHAR, NCHAR, NVARCHAR |
PL/SQL Character and String Types and PL/SQL National Character Types |
BOOLEAN |
|
PL/SQL Boolean Types |
DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, |
DATE |
PL/SQL Date, Time, and Interval Types |
Example
ORACLE |
ALTIBASE HDB |
Comments |
---|---|---|
DECLARE |
CREATE OR REPLACE PROCEDURE PROC1 |
Anonymous block |
UPDATE employees |
UPDATE employees |
DML Returning clause |
DECLARE |
CREATE OR REPLACE PROCEDURE PROC1 |
|