PL/SQL - VARRAY

Card Puncher Data Processing

About

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

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

Example

Table

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

Fetch

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:

Bulk Collect

PL/SQL - Bulk Collect - Fetch collection of (records|Collection)





Discover More
Card Puncher Data Processing
PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection (ie table of varray): of records. of a collections Fetch into a collection...
Card Puncher Data Processing
PL/SQL - Collections (Datatype Table of, Varray of)

Many programming techniques use collection types. To support these techniques in database applications, PL/SQL provides three data types of collections: Associative arrays (formerly called “PL/SQL...
Card Puncher Data Processing
PL/SQL - FOR LOOP Statement (On Cursor, Sequence, )

The for statement has two forms. It can be use with: a sequence to loop though a sequence of number or through a collection a cursor You break the for loop with an statement. Sequence Syntax...



Share this page:
Follow us:
Task Runner