Skip to end of metadata
Go to start of metadata

Overview

Simply, the usages of ALTIBASE Stored Procedure(ALTIBASE PSM) has so many resemblances to those of ORACLE.

Whereas It has a few differences with ORACLE's one.

In summation, The differences between them are as follows.

  • LOB Type is not available as a datatype of the local variable or ALTIBASE Stored Procedure parameter.
  • Geometry type is not available in the ALTIBASE Stored Procedure.
  • Stored Package is not supported.
  • only support ref cursor using Dynamic SQL not Static SQL.
  • Commit statement explicitly at fetch loop body in the ALTIBASE Stored Procedure can't be used. (select.. for update)
Icon

"FETCH ACROSS COMMIT" and "STORED PACKAGE" functionalities will be adopted as new features at ALTIBASE HDB V7.


LOB Type in ALTIBASE Stored Procedure

 LOB type variables cannot be declared in the declare section of a stored procedure.

Additionally, the %TYPE and %ROWTYPE attributes cannot be used to declare variables when the underlying column
in the actual database object is a LOB type column.
Because LOB type variables cannot be declared within stored procedures, data in LOB type columns
cannot be fetched using cursors.

Therefore, LOB type columns cannot be referenced using cursor
control statements.

Geometry type in ALTIBASE Stored Procedure

Same as above, Geometry type is unusable in ALTIBASE Stored Procedure.

Commit/Rollback with a cursor open

A user can not issue a commit or rollback statement with a cursor open.

ORACLE

ALTIBASE HDB

Icon

This functionality will be adopted in ALTIBASE HDB V7 as a new one.


Stored Package

 ALTIBASE HDB does not support Stored Package(called as a Package).

Icon

This functionality will be adopted in ALTIBASE HDB V7 as a new one as well.


Example

ORACLE

ALTIBASE HDB

Comments

create or replace procedure proc1(b1 integer, e1 integer)
is --> equal to as
begin
for i in b1 .. e1 loop
insert into t1 values(i, sysdate);
end loop;
end;
/

create or replace procedure proc1(b1 integer, e1 integer)
is -- as is equal
begin
for i in b1 .. e1 loop
insert into t1 values(i, sysdate);
end loop;
end;
/

Simple usage of ALTIBASE Stored Procedure and PL/SQ;.

DECLARE
TYPE MY_ARR IS TABLE OF INTEGER
INDEX BY VARCHAR(10);
V1 MY_ARR;
V2 INTEGER;
BEGIN
V1('FSDGADS') := 1;
V1('AA') := 2;
V1('7G65') := 3;
V1('N887K') := 4;
V1('KU') := 5;
V1('34') := 6;
dbms_output.put_line( 'V1 COUNT IS : '||V1.COUNT() );
dbms_output.put_line( 'V1 COUNT IS : '||V1.COUNT() );
END;
/

CREATE OR REPLACE PROCEDURE PROC3(
P1 IN VARCHAR(10),
P2 IN VARCHAR(10) )
AS
TYPE MY_ARR IS TABLE OF INTEGER
INDEX BY VARCHAR(10);
V1 MY_ARR;
V2 INTEGER;
BEGIN
V1['FSDGADS'] := 1;
V1['AA'] := 2;
V1['7G65'] := 3;
V1['N887K'] := 4;
V1['KU'] := 5;
V1['34'] := 6;
PRINTLN( 'V1 COUNT IS : '||V1.COUNT() );
PRINTLN( 'V1 COUNT IS : '||V1.COUNT() );
END;
/

Associative Array(Nested Table)






  • No labels