Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents

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)
Info

"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

Code Block
CREATE OR REPLACE PROCEDURE cur_proc1
(in_val IN INTEGER, out_val OUT INTEGER)
IS
  CURSOR cur1 IS SELECT c1 FROM t1 WHERE c1>in_val;
  col1 INTEGER;
  r1 INTEGER;
BEGIN
  r1 := 0;
  OPEN cur1;
  LOOP
    FETCH cur1 into col1;
    EXIT WHEN cur1%NOTFOUND;
    INSERT INTO t2 VALUES(col1);
    COMMIT;  -- 허용됨.
     r1 := r1+1;
    DBMS.PUT_LINE(col1);
  END LOOP;
  CLOSE cur1;
  DBMS.PUT_LINE('# of insert: '||r1);
  out_val := r1;
END;
/

ALTIBASE HDB

Code Block
CREATE OR REPLACE PROCEDURE cur_proc1
(in_val IN INTEGER, out_val OUT INTEGER)
IS
  CURSOR cur1 IS SELECT c1 FROM t1 WHERE c1>in_val;
  col1 INTEGER;
  r1 INTEGER;
BEGIN
  r1 := 0;
  OPEN cur1;
  LOOP
    FETCH cur1 into col1;
    EXIT WHEN cur1%NOTFOUND;
    INSERT INTO t2 VALUES(col1);
    COMMIT;          (X)
    r1 := r1+1;
    PRINTLN(col1);
  END LOOP;
  CLOSE cur1;
  COMMIT;            (O)
  PRINTLN('# of insert: '||r1);
  out_val := r1;
END;
/
Info

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).

Info

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)