PLSQL 4

From bd_en
Jump to: navigation, search

Translated by Bogdan Gospodaru on 16.03.2018.

Collections and Records

Useful links:

To use arrays of elements in SQL there are 2 data types: TABLE and VARRAY.

Collections - each element of a collection has an index which uniquely identifies it inside the collection. In PLSQL there are 3 types of collections:

  • associative arrays
  • nested tables
  • varrays

To use these data types you must first define a type and then declare variables (of the defined type) inside the declaration section (anonymous block, function/procedure).

A collection can be passed as a parameter (to send multiple data at the same time).

Associative arrays

An associative array (also called an index-by table - known in other programming languages as hash table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string. The purpose of this type of data is temporary storage (they are available only inside the PL/SQL app - they are not stored on disk, to do this you must generate a special code which will save them inside a database).

If the key doesn't exist when an assign instruction is executed, the key will be automatically created.

The definition of an associative array:

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size)];

After the definition, you must declare a variable of that type. "type_name" is an identifier for the new type of data, "element_type" is the type of the elements that will be stored (you can specify a rule for the value to never be null) and in the end the type of the key is specified (numeric type or string).

An example where the variables inside the associative array are integers and the keys are strings:


DECLARE 
    TYPE MyTab IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    age MyTab;
BEGIN
   age('Gigel') := 3;
   age('Ionel') := 4;
   DBMS_OUTPUT.PUT_LINE('Gigel's age: ' || age('Gigel'));
   DBMS_OUTPUT.PUT_LINE('Ionel's age: ' || age('Ionel'));
END;

In the previous example you may observe the assignment of a key with a value as well as the way of extracting the value when knowing the key (in the output operation).

If you want to add another child with the key already in the array (for example 'Ionel' with age=7), it cannot be accomplished, instead it will change the value of the existing (key, value) pair.

You can build an associative array in which the keys are integers, and the values are lines in a table.

DECLARE 
    TYPE MyTab IS TABLE OF students%ROWTYPE INDEX BY PLS_INTEGER;
    lines MyTab;
BEGIN
    SELECT * INTO lines(0) FROM students WHERE ROWNUM = 1;
    DBMS_OUTPUT.PUT_LINE(lines(0).fname);
END;

If the key does not have the correct type (or it cannot be casted - incompatible - by SGBD) it will show an error. In case the types are compatible, the code will run correctly. Example: If you provide a date instead of a varchar2 for the key (if the key's data type is a varchar2) it will not show any error.

DECLARE 
    TYPE MyTab IS TABLE OF number INDEX BY varchar2(20);
    lines MyTab;  
BEGIN
    lines(sysdate) := 123;
    DBMS_OUTPUT.PUT_LINE(lines(sysdate));
END;

Pay attention to the DATE type, TO_CHAR can return different results on different computers (thus getting different (key, value) pairs on different computers).

Functions that can be used on an associative array (you can test this on your own to observe the execution - explications about these functions are at the bottom of this page):


DECLARE 
    TYPE MyTab IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    age MyTab;
BEGIN
   age('Gigel') := 3;
   age('Ionel') := 4;
   age('Maria') := 6;
   
   DBMS_OUTPUT.PUT_LINE('Array size: ' || age.COUNT);
   
   DBMS_OUTPUT.PUT_LINE('First key: ' || age.FIRST);
   DBMS_OUTPUT.PUT_LINE('Last key`: ' || age.LAST);

   DBMS_OUTPUT.PUT_LINE('Before Ionel: ' || age.PRIOR('Ionel'));
   DBMS_OUTPUT.PUT_LINE('After Ionel: ' || age.NEXT('Ionel'));
      
   age.DELETE('Maria');   
   DBMS_OUTPUT.PUT_LINE('After Ionel: ' || age.NEXT('Ionel'));   
END;

Nested tables

Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements (similar to a table in the database where data is more like a set rather than organized). This type of data is widely used for storing a lot of data associated with a single line (row). Because the elements are not organized, you cannot know which is the first one or the second one etc. However, when you want to iterate it, a temporary number can be assigned to each row (rownum). You can create tables in which the elements are also tables (a matrix).

Within the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.

To access the elements, use the technique from associative arrays. There are, however, different ways to store the data by SGBD, and they are also easier to pass around as parameters to functions or procedures.

Nested tables data types can be stored as a field inside a table in the database - you can create tables which contain nested tables fields.

The definition of a nested table:


TYPE type_name IS TABLE OF elemenet_type [NOT NULL];

Then, declare a variable of type "type_name" in which you can store elements of type "element_type" Example:


DECLARE
    TYPE fname IS TABLE OF varchar2(10);
    student fname;
BEGIN
    student := fname('Gigel', 'Ionel');
    for i in student.first..student.last loop
       DBMS_OUTPUT.PUT_LINE(i||' - '||student(i));    
    end loop;
END;

Let's declare a table with 3 elements, add 4 more elements by copying the second element and then delete it.


DECLARE
    TYPE fname IS TABLE OF varchar2(10);
    student fname;
BEGIN
    student := fname('Gigel', 'Ionel', 'Maria');  
    student.EXTEND(4,2); -- copy the 2nd element 4 times
    student.delete(2); -- delete the 2nd element
    for i in student.first..student.last loop
        if student.exists(i) then -- check the existence, otherwise it will throw an error
           DBMS_OUTPUT.PUT_LINE(i||' - '||student(i)); -- print
        end if;
    end loop;
END;

As you can see, deleting the 2nd element didn't shift the whole table (positions 3..7), instead that position is now empty. Trying to print or access it will throw an error.

The value of element_type can be something else (other than varchar2). You can try to give it the type of an existent column from a table (students.fname%type), a row from a table (students%ROWTYPE) or a row from a cursor (cursor%ROWTYPE).

Example which loads inside a table data type all the elements of the students table:


DECLARE 
   CURSOR course IS SELECT * FROM students;
   TYPE student_line IS TABLE OF course%ROWTYPE;
   student_list student_line;
BEGIN
   open course;
   SELECT * BULK COLLECT INTO student_list FROM studenti;
   close course;
    for i in student_list.first..student_list.last loop
        if student_list.exists(i) then
           DBMS_OUTPUT.PUT_LINE(i||' - '||student_list(i).nume);
        end if;
    end loop;   
    DBMS_OUTPUT.PUT_LINE('Number of students: '||student_list.COUNT);
END;

The example can be refactored to use the NEXT function to obtain the key of the next element. In this case, you don't need to check the existence of the element (the if condition).

Example creating tables with a field of type 'nested table':



GRANT CREATE TYPE TO STUDENTS;

CREATE OR REPLACE TYPE fname_list AS TABLE OF VARCHAR2(10);
/
CREATE TABLE people (nume varchar2(10), 
       fname fname_list)
       NESTED TABLE fname STORE AS lista;
/       

INSERT INTO people VALUES('Popescu', fname_list('Ionut', 'Razvan'));
INSERT INTO people VALUES('Ionescu', fname_list('Elena', 'Madalina'));
INSERT INTO people VALUES('Rizea', fname_list('Mircea', 'Catalin'));
/
SELECT * FROM people;



PL/SQL function for inserting a person in the above table:


DECLARE    
    fname_line people.fname%type;
BEGIN
    fname_line := lista_prenume('Cristi', 'Tudor', 'Virgil');
    INSERT INTO people VALUES ('Gurau', fname_line);
    DBMS_OUTPUT.PUT_LINE('Done');
END;

Do not forget to test the tables' specific functions (from later in this lab).

Varrays

Varray is an array with a variable size. The definition of a varray:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size) OF element_type [NOT NULL];

Example (the last element is deleted and then 2 new elements are added):

DECLARE
  TYPE varr IS VARRAY(5) OF varchar2(10);
  towns varr;
BEGIN
  towns := varr('Iasi', 'Bacau', 'Suceava', 'Botosani');
  DBMS_OUTPUT.PUT_LINE('Number of towns: '||towns.COUNT);
  towns.TRIM;
  FOR i IN towns.FIRST..towns.LAST LOOP
     DBMS_OUTPUT.PUT_LINE(towns(i));
  END LOOP;
  
  towns.EXTEND(2);
  towns(4):='Sibiu';
  towns(5):='Brasov';
  DBMS_OUTPUT.PUT_LINE('After add:');
  FOR i IN towns.FIRST..towns.LAST LOOP
     DBMS_OUTPUT.PUT_LINE(towns(i));
  END LOOP;  
END;

Do not forget to test the other functions that can be applied to the variables of type varray (from the list of collection functions inside this page).

Functions that can be applied to collections

  • FIRST - returns the value of the key (or the index) of the first element;
  • LAST - returns the value of the key (or the index) of the last element;
  • PRIOR(key) - returns the key of the element that is before the parameter in the collection
  • NEXT(key) - return the key of the element that is after the parameter in the collection
  • EXISTS(key) - returns true if the there is a value assigned to the key, false otherwise
  • COUNT - returns the number of elements;
  • varray.LIMIT - how many elements can be added in a variable of type varray;
  • EXTEND [(n[,i])] - for nested tables and varray: to add n positions in the structure (optional: equal to the value of the element at position i). In case of no parameter, it will add just one element (doesn't apply to associative arrays).
  • TIRM [(n)] - for nested tables and varrays: deletes n elements from the end. In case of no parametere, it will delete the last element.
  • DELETE [(n,[m])] - for associative arrays and nested tables: if no parameter, deletes all elements, otherwise delete the element at position n (or n, n+1, ..., m)


Records

PL/SQL lets you define records that contain objects, collections, and other records (called nested records). However, records cannot be attributes of object types. Every field of a record has a name (which can identify it) and a type.

You encountered a RECORD variable when you declared them as ROWTYPE (inside a cursor for example).

The definition of a record:


TYPE type_name IS RECORD (field_name field_type[,field_name field_type]...);

Example:

CREATE TABLE minions (color varchar2(20), nr_of_eyes number(3), name varchar2(20));
 /
DECLARE
   TYPE minion IS RECORD(
      color varchar2(20) := 'Yellow', 
      nr_of_eyes number(3), 
      name varchar2(20)
      );
   v_minion minion;   
BEGIN      
   v_minion.color:='Yellow';
   v_minion.nr_of_eyes := 2;
   v_minion.name:='Kevin';
   INSERT INTO MINIONS VALUES V_MINION;
   DBMS_OUTPUT.PUT_LINE(v_minion.color);
END;


Exercice

Ex. 1 (10pt) Build a package that:

  • (3pt) Has a stored procedure (or function) that receives as a parameter a list of records that contains the IDs of the students and a percentage that will be used to increase the scholarship. If the student has no scholarship, you will add a minimum value of 100 before increasing it with the percentage.
  • (4pt) Modify the table students in order to have a new field that will be able to store a list of scholarships (a history of what the student had). Modify the exercice at 1 to store those values
  • (2pt) For every student that had at least one scholarship modification, show the history of the scholarship.
  • (1pt) Create an anonymous block that will modify the scholarship of 5 students (based on their IDs - you will send a list of records, the type should be defined in the package). Also in this anonymous block call the procedure that will display the history.