Skip to end of metadata
Go to start of metadata

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,
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;
/

 

  • No labels