PL/SQL - VARRAY

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

VARRAY (variable-size arrays) is a collection, a variable-size arrays (varrays for short)

Advertising

3 - Constructor

  • Type
-- Database 
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);
-- PLSQL
TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);
  • PLSQL
DECLARE
   TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);
   hobbies Hobbies;
BEGIN
   hobbies := Hobbies('golf', 'quilting', 'rock climbing');
END;
/

4 - Example

4.1 - Table

CREATE TABLE tabHobbies (hobbs Hobbies);
INSERT INTO tabHobbies(hobbs) VALUES (Hobbies('golf', 'quilting', 'rock climbing'));

4.2 - Fetch

4.2.1 - Loop

DECLARE
    TYPE RefCurTyp IS REF CURSOR;
    cv RefCurTyp;
    h  Hobbies;
BEGIN
   OPEN cv FOR 'SELECT hobbs FROM TabHobbies';
   LOOP
      FETCH cv INTO h;
      EXIT WHEN cv%NOTFOUND;
      -- print attributes of 'h' with the collection methods
   END LOOP;
   CLOSE cv;
END;

where:

4.2.2 - Bulk Collect

Advertising
lang/plsql/varray.txt · Last modified: 2017/09/06 22:18 by gerardnico