PL/SQL   «Prev  Next»

Lesson 6Updating rows in a Varray
Objective Write an update command for a varray

Updating Rows in varray

A varray can be updated only as a single column within the main table and not as individual elements. Similarly, the individual elements of a varray cannot be selected directly; you must select the whole column on the main table.
To update all the elements of a varray, you must write a DML statement that specifies the new values that replace the old values. For example, to update the varray data within the CUSTOMER_OBJ_TABLE table, you can use the following DML statement: 

UPDATE CUSTOMER_OBJ_TABLE
SET phone_array = (‘1-800-234-5678’, ‘1-888-987-6543’)
WHERE cust_id = 25;

Replaces Old values with New values

This statement replaces the old values with the new values, which are defined within the DML statement for a particular record within the CUSTOMER_OBJ_TABLE table. To update the individual elements or to add more elements into a varray, you must use PL/SQL. Let us look at an example of updating a varray by adding one more elements to it.
To add an element, we must extend the existing varray, then add an element within the PL/SQL code. In the following example, we will add an element to a record within the CUSTOMER_OBJ_TABLE table:

varray Example

DECLARE
var_phone_array phone_array;
BEGIN
   SELECT phone_list into var_phone_array
   FROM CUSTOMER_OBJ_TABLE
   WHERE cust_id = 26;
 var_phone_array.extend;
   var_phone_array (var_phone_array.last) := '1-800-336-6199’';
   UPDATE CUSTOMER_OBJ_TABLE
   SET phone_list = var_phone_array
   WHERE cust_id = 26;
COMMIT;
END;

In the PL/SQL block above, we defined a variable of type PHONE_ARRAY, then selected the data into the variable. Next, we extended the varray to add one more element to it and to update the CUSTOMER_OBJ_TABLE table with the variable being assigned to the varray.
The following MouseOver explains the syntax of updating data within a varray:
syntax-update-varray
  1. The UPDATE statement with the table name
  2. The SET clause to update the value within a varray
  3. The WHERE clause to select a particular row of the object table
Update <table name>
SET <varray_name>=<variable>
WHERE <clause>;

Syntax To Update Varray
The following MouseOver shows an example of updating data within a varray:
execute-update-varray 
UPDATE CUSTOMER_OBJ_TABLE
SET phone_array =
('1-800-333-5978', '1-888-654-6133')
WHERE cust_id =26;
  1. The UPDATE statement with the TABLE keyword
  2. The SET clause to update the value within a varray
  3. The WHERE clause to select a particular row of the object table

Example Updating Within Varray
Now that you have learned few techniques to update rows within nested tables and varrays, try them with the following tutor-based exercise. In this exercise, you will be asked to write couple of UPDATE statements, one each for updating a nested table and a varray. The first statement will update the data within the CUSTOMER_OBJ_TABLE table. The second statement will update the data within the SALE_HEADER table.
In the next lesson, you will learn about deleting rows from nested tables and varrays.

Updating Rows in Varray - Exercise

Click the Exercise link below to build the UPDATE statements to update a nested table and a varray.
Updating Rows in Varray - Exercise