Skip to end of metadata
Go to start of metadata

Overview

In ORACLE, To create a cursor variable, you define a REF CURSOR type, then declare cursor
variables of corresponding type.

Declaring REF CURSOR Type and Cursor Variables, passing Cursor Variables As Parameters can be declared in a PL/SQL.

A REF CURSOR is commonly declared in the ORACLE package and use it as a OUT PARAMETER of the ORACLE PL/SQL.

However, in case of the ALTIBASE HDB, Since it doesn't provide a package functionality currently, Typeset should be created in advance, and use it.

The use of REF CURSOR

The procedures to use REF CURSOR should be divided into plural categories in ALTIBASE HDB as follows.

  • Create the user-defined type which is a REF CURSOR, and create a typeset which containing a corresponding type
  • Create the stored procedure, which having OUT parameters.
  • Execute a procedure.

TYPE and TYPESET 

Since ALTIBASE HDB doesn't provide a package functionality, Once a typeset is created with user-defined type, it can be passed between procedures.

Thus, a typeset, a combination of user-defined types using "CREATE TYPE" statement, can be made using "CREATE TYPESET" statement.

ORACLE

ALTIBASE HDB


 

If you wish to see further about the method of passing result set which described above in detail, refer to this page(Cursor Variables)

Example

ORACLE

ALTIBASE HDB

Comments

CREATE OR REPLACE PACKAGE ref_cursor_pkg AS
TYPE ref_type IS REF CURSOR;
PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql in VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY ref_cursor_pkg AS
PROCEDURE ref_cursor_pro(v_result OUT ref_type, v_sql IN VARCHAR2) AS
begin
OPEN v_result FOR v_sql;
END;/

CREATE OR REPLACE TYPESET my_type
AS
TYPE my_cur IS REF CURSOR;
END;
/

CREATE OR REPLACE PROCEDURE opencursor
( v_result OUT my_type.my_cur, v_sql IN VARCHAR(200) )
AS
BEGIN
OPEN y_result FOR v_sql;
END;
/

In ORACLE, A declaration of the type is possible
during a creation of the package.

While in ALTIBASE HDB, Typeset, a set of the type, should be created ahead of use.
after then, A type is available might be used in "CREATE PROCEDURE" statement.

  • No labels