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.
| Section |
|---|
| Column |
|---|
| ORACLE | Code Block |
|---|
Declare
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/ |
|
| Column |
|---|
| ALTIBASE HDB | Code Block |
|---|
CREATE OR REPLACE PROCEDURE PROC1
AS
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b;
END;
/ |
|
|
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.
| Section |
|---|
| Column |
|---|
| ORACLE | Code Block |
|---|
UPDATE employees
SET emp_job ='SA_MAN', salary = salary + 1000, dno = 140
WHERE e_lastname = 'Jones'
RETURNING salary*0.25, e_lastname, dno
INTO :bnd1, :bnd2, :bnd3; |
|
| Column |
|---|
| ALTIBASE HDB | Code Block |
|---|
UPDATE employees
SET emp_job ='SA_MAN', salary = salary + 1000, dno = 140
WHERE e_lastname = 'Jones';
SELECT salary*0.25, last_name, dno
INTO :bnd1, :bnd2, :bnd
FROM employees
WHERE e_lastname = 'Jones'; |
|
|
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,
DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL,
NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE,
POSITIVEN, REAL, SIGNTYPE, SMALLINT | INTEGER, FLOAT, DECIMAL, DOUBLE, NUMBER, REAL | PL/SQL Number Types |
CHAR, CHARACTER, LONG, LONG RAW, NCHAR, NVARCHAR2,
RAW, ROWID, STRING, UROWID, VARCHAR, VARCHAR2 | 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,
TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH,
INTERVAL DAY TO SECOND | DATE | PL/SQL Date, Time, and Interval Types |
Example
ORACLE | ALTIBASE HDB | Comments |
|---|
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'manager';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE emp_job = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.e_lastname || ' Job Title: ' || emp_rec.emp_job);
END LOOP;
CLOSE emp_cv;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
sql_stmt VARCHAR2(200);
v_job VARCHAR2(10) := 'manager';
BEGIN
sql_stmt := 'SELECT * FROM employees WHERE emp_job = :j';
OPEN emp_cv FOR sql_stmt USING v_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
PRINTLN('Name: ' || emp_rec.e_lastname || ' Job Title: ' || emp_rec.emp_job);
END LOOP;
CLOSE emp_cv;
END;
/ | Anonymous block |
UPDATE employees
SET emp_job ='SA_MAN', salary = salary + 1000, dno = 140
WHERE e_lastname = 'Jones'
RETURNING salary*0.25, e_lastname, dno
INTO :bnd1, :bnd2, :bnd3; | UPDATE employees
SET emp_job ='SA_MAN', salary = salary + 1000, dno = 140
WHERE e_lastname = 'Jones';
SELECT salary*0.25, last_name, dno
INTO :bnd1, :bnd2, :bnd
FROM employees
WHERE e_lastname = 'Jones'; | DML Returning clause
will be applied at ALTIBASE HDB V7. |
DECLARE
dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
dept_info.dno := 300;
dept_info.dname := 'Personnel';
dept_info.dep_location := 1700;
-- Using the %ROWTYPE means we can leave out the column list
-- (department_id, department_name, and location_id) from the INSERT statement
INSERT INTO departments VALUES dept_info;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
dept_info.dno := 300;
dept_info.dname := 'Personnel';
dept_info.dep_location := 1700;
-- Using the %ROWTYPE means we can leave out the column list
-- (department_id, department_name, and location_id) from the INSERT statement
INSERT INTO departments(dno, dname, dep_location) VALUES (dept_info.dno, dept_info.dname, dept_info.dep_location);
END;
/ | |