Oracle PL/SQL Tutorial/Collections/Associative Arrays — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 13:04, 26 мая 2010
Содержание
- 1 Assign value to PL/SQL table
- 2 Associative Arrays demo
- 3 Associative arrays (index-by tables)
- 4 Change PL/SQL table element by index
- 5 Clear the salaries table by assigning the empty version to it
- 6 FIRST and LAST Table Attributes
- 7 Index by string
- 8 Indexing Associative Arrays
- 9 Insert data in PL/SQL table to a real table
- 10 Nested Table with Table row elements inside
- 11 NEXT and PRIOR Table Attributes
- 12 Place some values into the salaries table
- 13 PL/SQL Table
- 14 PL/SQL table of cursor
- 15 Select data into PL/SQL table of cursor
- 16 TABLE.Exist
- 17 The COUNT Table Attribute
- 18 The DELETE Table Attribute
- 19 The EXISTS Table Attribute
- 20 Use for all loop to loop through the PL/SQL table
- 21 Use For loop to output data in a PL/SQL table of cursor
- 22 Uses the COUNT method to display the number of rows contained in an index-by table
- 23 Using INDEX BY BINARY_INTEGER
Assign value to PL/SQL table
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 --Define an index-by table type.
3 TYPE num_table IS TABLE OF NUMBER
4 INDEX BY BINARY_INTEGER;
5
6 nums num_table;
7 some_num NUMBER;
8 BEGIN
9 nums(10) := 11;
10
11 BEGIN
12 some_num := nums(11);
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 DBMS_OUTPUT.PUT_LINE("Element 11 does not exist.");
16 END;
17
18 END;
19 /
Element 11 does not exist. PL/SQL procedure successfully completed.</source>
Associative Arrays demo
The associative array behaves like an array (although it is called a TABLE or INDEX-BY TABLE).
The "associative" part of the object comes from the PL/SQL ability to use nonnumeric subscripts.
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE myProc0
2 AS
3 CURSOR ccur is SELECT first_name, city FROM employee;
4 TYPE nametab IS TABLE OF employee.first_name%type;
5 ch nametab;
6 i integer := 0;
7 imax integer;
8 BEGIN
9 FOR j IN ccur LOOP
10 i := i + 1;
11 ch(i) := j.first_name;
12 END LOOP;
13 imax := i;
14 i := 0;
15 dbms_output.put_line("number of values read: "||imax);
16 FOR k IN 1..imax LOOP
17 dbms_output.put_line("Name ... "||ch(k));
18 END LOOP;
19 END myProc0;
20 /
Procedure created. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
Associative arrays (index-by tables)
An associative array is a collection of elements that use arbitrary numbers and strings for subscript values.
You cannot use an associative array in SQL.
<source lang="sql">
declare
type AssocArray is table of ElementType index by binary_integer|pls_integer|VARCHAR2(size);</source>
Change PL/SQL table element by index
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 TYPE emp_id IS TABLE OF employee.id%TYPE; 3 TYPE emp_name IS TABLE OF employee.first_name%TYPE; 4 5 emp_ids emp_id; 6 emp_names emp_name; 7 inx1 PLS_INTEGER; 8 BEGIN 9 emp_ids := emp_id(); 10 emp_names := emp_name(); 11 12 emp_ids.extend(10); 13 emp_names.extend(10); 14 15 FOR inx1 IN 1..10 LOOP 16 emp_ids(inx1) := inx1 + 1100; 17 emp_names(inx1) := "emp #" || TO_CHAR(inx1+1100); 18 END LOOP; 19 20 FORALL x IN emp_ids.first..emp_ids.last 21 INSERT INTO employee (id, first_name) VALUES (emp_ids(x), emp_names(x)); 22 END; 23 /
PL/SQL procedure successfully completed. SQL> SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 1101 emp #1101 1102 emp #1102 1103 emp #1103 1104 emp #1104 1105 emp #1105 1106 emp #1106 1107 emp #1107 1108 emp #1108 1109 emp #1109 1110 emp #1110 18 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Clear the salaries table by assigning the empty version to it
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SQL> DECLARE
2 TYPE myTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 3 4 salaries myTable; 5 salaries_empty myTable; 6 BEGIN 7 8 salaries(20) := 50550; 9 salaries(40) := 50550; 10 salaries(60) := 50550; 11 12 salaries := salaries_empty; 13 END; 14 /
PL/SQL procedure successfully completed. SQL></source>
FIRST and LAST Table Attributes
<source lang="sql">
SQL> DECLARE
2 Type t_LastNameTable IS TABLE OF VARCHAR(20) 3 INDEX BY BINARY_INTEGER; 4 v_LastNames t_LastNameTable; 5 v_Index BINARY_INTEGER; 6 BEGIN 7 -- Insert rows in the table. 8 v_LastNames(43) := "A"; 9 v_LastNames(50) := "B"; 10 v_LastNames(47) := "C"; 11 12 -- Assigns 43 to v_Index. 13 v_Index := v_LastNames.FIRST; 14 15 DBMS_OUTPUT.put_line(v_Index); 16 17 -- Assigns 50 to v_Index. 18 v_Index := v_LastNames.LAST; 19 DBMS_OUTPUT.put_line(v_Index); 20 21 END; 22 /
43 50 PL/SQL procedure successfully completed. SQL> SQL></source>
Index by string
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE myProc
2 AS
3 CURSOR ccur IS SELECT first_name, city FROM employee;
4 TYPE nametab IS TABLE OF employee.first_name%type
5 INDEX BY employee.city%type;
6 ch nametab;
7 i integer := 0;
8 imax integer;
9 BEGIN
10 FOR j IN ccur LOOP
11 /* i := i + 1; */
12 ch(j.first_name) := j.first_name;
13 END LOOP;
14 /* imax := i;
15 i := 0;
16 dbms_output.put_line("number of values read: "||imax); */
17 dbms_output.put_line("Name ... "||ch("Vancouver"));
18 END myProc;
19 /
SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL> SQL></source>
Indexing Associative Arrays
Generate a list of employees grouped by two parameters
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> declare
2 type list_aa is table of VARCHAR2(2000) index by VARCHAR2(256); 3 v_list_aa list_aa; 4 5 cursor c_dept is select id from employee; 6 cursor c_emp is select first_name, id,to_char(start_Date,"q") q_nr from employee; 7 v_subscript_tx VARCHAR2(256); 8 begin 9 for r_dept in c_dept loop 10 v_list_aa(r_dept.id||"|1"):="Q1 Dept#" ||r_dept.id||":"; 11 v_list_aa(r_dept.id||"|2"):="Q2 Dept#" ||r_dept.id||":"; 12 v_list_aa(r_dept.id||"|3"):="Q3 Dept#" ||r_dept.id||":"; 13 v_list_aa(r_dept.id||"|4"):="Q4 Dept#" ||r_dept.id||":"; 14 end loop; 15 16 for r_emp in c_emp loop 17 v_list_aa(r_emp.id||"|"||r_emp.q_nr):= v_list_aa(r_emp.id||"|"||r_emp.q_nr)|| " "||r_emp.first_Name; 18 end loop; 19 20 v_subscript_tx:=v_list_aa.first; 21 loop 22 DBMS_OUTPUT.put_line(v_list_aa(v_subscript_tx)); 23 v_subscript_tx:=v_list_aa.next(v_subscript_tx); 24 exit when v_subscript_tx is null; 25 end loop; 26 end; 27 /
Q1 Dept#01: Q2 Dept#01: Q3 Dept#01: Jason Q4 Dept#01: Q1 Dept#02: Alison Q2 Dept#02: Q3 Dept#02: Q4 Dept#02: Q1 Dept#03: Q2 Dept#03: Q3 Dept#03: Q4 Dept#03: James Q1 Dept#04: Q2 Dept#04: Q3 Dept#04: Q4 Dept#04: Celia Q1 Dept#05: Robert Q2 Dept#05: Q3 Dept#05: Q4 Dept#05: Q1 Dept#06: Q2 Dept#06: Q3 Dept#06: Linda Q4 Dept#06: Q1 Dept#07: Q2 Dept#07: Q3 Dept#07: Q4 Dept#07: David Q1 Dept#08: Q2 Dept#08: Q3 Dept#08: James Q4 Dept#08: PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Insert data in PL/SQL table to a real table
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 TYPE emp_id IS TABLE OF employee.id%TYPE; 3 TYPE emp_name IS TABLE OF employee.first_name%TYPE; 4 5 emp_ids emp_id; 6 emp_names emp_name; 7 inx1 PLS_INTEGER; 8 BEGIN 9 emp_ids := emp_id(); 10 emp_names := emp_name(); 11 12 emp_ids.extend(10); 13 emp_names.extend(10); 14 15 FOR inx1 IN 1..10 LOOP 16 emp_ids(inx1) := inx1 + 1100; 17 emp_names(inx1) := "emp #" || TO_CHAR(inx1+1100); 18 END LOOP; 19 20 FORALL x IN emp_ids.first..emp_ids.last 21 INSERT INTO employee (id, first_name) VALUES (emp_ids(x), emp_names(x)); 22 END; 23 /
PL/SQL procedure successfully completed. SQL> SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 1101 emp #1101 1102 emp #1102 1103 emp #1103 1104 emp #1104 1105 emp #1105 1106 emp #1106 1107 emp #1107 1108 emp #1108 1109 emp #1109 1110 emp #1110 18 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Nested Table with Table row elements inside
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_emps IS 3 SELECT * 4 FROM employee 5 ORDER BY first_name; 6 7 --Define an index-by table type. 8 TYPE emp_table IS TABLE OF employee%ROWTYPE 9 INDEX BY BINARY_INTEGER; 10 11 --Declare an index-by table variable to 12 --hold the employee records that we read in. 13 emps emp_table; 14 emps_max BINARY_INTEGER; 15 BEGIN 16 emps_max := 0; 17 18 FOR emp IN all_emps LOOP 19 emps_max := emps_max + 1; 20 21 emps(emps_max).id := emp.id; 22 emps(emps_max).first_name := emp.first_name; 23 emps(emps_max).last_name := emp.last_name; 24 emps(emps_max).salary := emp.salary; 25 END LOOP; 26 END; 27 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
NEXT and PRIOR Table Attributes
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE t_MajorTable IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER; 3 v_Majors t_MajorTable; 4 v_Index BINARY_INTEGER; 5 BEGIN 6 -- Insert values into the table. 7 v_Majors(-7) := "A"; 8 v_Majors(4) := "B"; 9 v_Majors(5) := "C"; 10 11 -- Loop over all the rows in the table, and insert them into 12 -- temp_table. 13 v_Index := v_Majors.FIRST; 14 LOOP 15 DBMS_OUTPUT.put_line(v_Index||" " || v_Majors(v_Index)); 16 EXIT WHEN v_Index = v_Majors.LAST; 17 v_Index := v_Majors.NEXT(v_Index); 18 END LOOP; 19 END; 20 /
-7 A 4 B 5 C PL/SQL procedure successfully completed. SQL></source>
Place some values into the salaries table
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> SET ECHO ON SQL> SQL> DECLARE
2 TYPE myTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 3 4 salaries myTable; 5 salaries_empty myTable; 6 BEGIN 7 8 salaries(20) := 50550; 9 salaries(40) := 50550; 10 salaries(60) := 50550; 11 12 salaries := salaries_empty; 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL></source>
PL/SQL Table
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> declare
2 type dept_rty is record(id VARCHAR(30), extra_tx VARCHAR2(2000)); 3 type dept_nt is table of dept_rty; 4 v_dept_nt dept_nt:=dept_nt(); 5 cursor c_emp is select first_Name, id from employee; 6 begin 7 v_dept_nt.extend(3); 8 v_dept_nt(1).id :="10"; 9 v_dept_nt(2).id :="20"; 10 v_dept_nt(3).id :="30"; 11 12 for r_emp in c_emp loop 13 for i in v_dept_nt.first..v_dept_nt.last loop 14 if v_dept_nt(i).id =r_emp.id then 15 v_dept_nt(i).extra_tx:= v_dept_nt(i).extra_tx||" "||r_emp.first_Name; 16 end if; 17 end loop; 18 end loop; 19 end; 20 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
PL/SQL table of cursor
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_emps IS 3 SELECT * 4 FROM employee 5 ORDER BY first_name; 6 7 TYPE emp_table IS TABLE OF employee%ROWTYPE; 8 9 emps emp_table; 10 emps_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 emps_max := 0; 14 15 emps := emp_table (); 16 17 FOR emp IN all_emps LOOP 18 emps_max := emps_max + 1; 19 emps.extend; 20 emps(emps_max).id := "99"; 21 emps(emps_max).first_name := "new"; 22 emps(emps_max).salary := 1111; 23 END LOOP; 24 25 emps.extend(5,1); 26 27 FOR inx1 IN 1..emps_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name); 29 END LOOP; 30 END; 31 /
99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new 99 new PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
Select data into PL/SQL table of cursor
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_emps IS 3 SELECT * 4 FROM employee 5 ORDER BY first_name; 6 7 TYPE emp_table IS TABLE OF employee%ROWTYPE; 8 9 emps emp_table; 10 emps_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 emps_max := 0; 14 15 emps := emp_table (); 16 17 FOR emp IN all_emps LOOP 18 emps_max := emps_max + 1; 19 emps.extend; 20 emps(emps_max).id := emp.id; 21 emps(emps_max).first_name := emp.first_name; 22 emps(emps_max).salary := emp.salary; 23 END LOOP; 24 25 emps.extend(5,1); 26 27 FOR inx1 IN 1..emps_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE(emps(inx1).id ||" " || emps(inx1).first_name); 29 END LOOP; 30 emps.trim(5); 31 32 emps.delete(1); 33 34 DBMS_OUTPUT.PUT_LINE(emps.count); 35 36 FOR inx1 IN 1..emps_max+5 LOOP 37 IF emps.exists(inx1) THEN 38 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name); 39 END IF; 40 END LOOP; 41 END; 42 /
02 Alison 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert 02 Alison 02 Alison 02 Alison 02 Alison 02 Alison 7 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
TABLE.Exist
<source lang="sql">
SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 --Define an index-by table type.
3 TYPE num_table IS TABLE OF NUMBER
4 INDEX BY BINARY_INTEGER;
5
6 nums num_table;
7 some_num NUMBER;
8 BEGIN
9 nums(10) := 11;
10
11 IF nums.EXISTS(11) THEN
12 some_num := nums(11);
13 ELSE
14 DBMS_OUTPUT.PUT_LINE("Element 11 still does not exist.");
15 END IF;
16
17 END;
18 /
Element 11 still does not exist. PL/SQL procedure successfully completed. SQL></source>
The COUNT Table Attribute
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE t_NumberTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 3 v_Numbers t_NumberTable; 4 v_Total NUMBER; 5 BEGIN 6 -- Insert 50 rows into the table. 7 FOR v_Counter IN 1..50 LOOP 8 v_Numbers(v_Counter) := v_Counter; 9 END LOOP; 10 11 v_Total := v_Numbers.COUNT; 12 END; 13 /
PL/SQL procedure successfully completed. SQL> SQL></source>
The DELETE Table Attribute
<source lang="sql">
SQL> DECLARE
2 TYPE t_ValueTable IS TABLE OF VARCHAR2(10) 3 INDEX BY BINARY_INTEGER; 4 v_Values t_ValueTable; 5 BEGIN 6 -- Insert rows into the table. 7 v_Values(1) := "One"; 8 v_Values(3) := "Three"; 9 v_Values(-2) := "Minus Two"; 10 v_Values(0) := "Zero"; 11 v_Values(100) := "Hundred"; 12 13 v_Values.DELETE(100); -- Removes "Hundred" 14 v_Values.DELETE(1,3); -- Removes "One" and "Three" 15 v_Values.DELETE; -- Removes all remaining values 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL></source>
The EXISTS Table Attribute
<source lang="sql">
SQL> SQL> DECLARE
2 Type t_FirstNameTable IS TABLE OF VARCHAR(20)
3 INDEX BY BINARY_INTEGER;
4 FirstNames t_FirstNameTable;
5 BEGIN
6 -- Insert rows into the table.
7 FirstNames(1) := "Scott";
8 FirstNames(3) := "Joanne";
9
10 -- Check to see if rows exist.
11 IF FirstNames.EXISTS(1) THEN
12 DBMS_OUTPUT.put_line("Row 1 exists!");
13 ELSE
14 DBMS_OUTPUT.put_line("Row 1 doesn""t exist!");
15 END IF;
16 IF FirstNames.EXISTS(2) THEN
17 DBMS_OUTPUT.put_line("Row 2 exists!");
18 ELSE
19 DBMS_OUTPUT.put_line("Row 2 doesn""t exist!");
20 END IF;
21 END;
22 /
Row 1 exists! Row 2 doesn"t exist! PL/SQL procedure successfully completed. SQL> SQL></source>
Use for all loop to loop through the PL/SQL table
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_emps IS 3 SELECT id, first_name 4 FROM employee 5 ORDER BY first_name; 6 7 TYPE emp_id IS TABLE OF employee.id%TYPE; 8 TYPE emp_name IS TABLE OF employee.first_name%TYPE; 9 10 emp_ids emp_id; 11 emp_names emp_name; 12 inx1 PLS_INTEGER; 13 BEGIN 14 OPEN all_emps; 15 FETCH all_emps BULK COLLECT INTO emp_ids, emp_names; 16 CLOSE all_emps; 17 18 FOR inx1 IN 1..emp_ids.count LOOP 19 emp_names(inx1) := UPPER(emp_names(inx1)); 20 DBMS_OUTPUT.PUT_LINE (emp_ids(inx1) ||" " || emp_names(inx1)); 21 END LOOP; 22 23 FORALL x IN emp_ids.first..emp_ids.last 24 UPDATE employee SET first_name = emp_names(x) WHERE id = emp_ids(x); 25 END; 26 /
02 ALISON 04 CELIA 07 DAVID 03 JAMES 08 JAMES 01 JASON 06 LINDA 05 ROBERT PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Use For loop to output data in a PL/SQL table of cursor
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_emps IS 3 SELECT * 4 FROM employee 5 ORDER BY first_name; 6 7 TYPE emp_table IS TABLE OF employee%ROWTYPE; 8 9 emps emp_table; 10 emps_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 emps_max := 0; 14 15 emps := emp_table (); 16 17 FOR emp IN all_emps LOOP 18 emps_max := emps_max + 1; 19 emps.extend; 20 emps(emps_max).id := emp.id; 21 emps(emps_max).first_name := emp.first_name; 22 emps(emps_max).salary := emp.salary; 23 END LOOP; 24 25 emps.extend(5,1); 26 27 FOR inx1 IN 1..emps_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE(emps(inx1).id ||" " || emps(inx1).first_name); 29 END LOOP; 30 emps.trim(5); 31 32 emps.delete(1); 33 34 DBMS_OUTPUT.PUT_LINE(emps.count); 35 36 FOR inx1 IN 1..emps_max+5 LOOP 37 IF emps.exists(inx1) THEN 38 DBMS_OUTPUT.PUT_LINE (emps(inx1).id ||" " || emps(inx1).first_name); 39 END IF; 40 END LOOP; 41 END; 42 /
02 Alison 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert 02 Alison 02 Alison 02 Alison 02 Alison 02 Alison 7 04 Celia 07 David 03 James 08 James 01 Jason 06 Linda 05 Robert PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
Uses the COUNT method to display the number of rows contained in an index-by table
<source lang="sql">
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> -- Uses the COUNT method to display the number of rows contained in an index-by table: SQL> SQL> DECLARE
2 TYPE employee_table_struct IS TABLE OF employee.first_name%TYPE INDEX BY BINARY_INTEGER;
3
4 employee_table employee_table_struct;
5
6 CURSOR employee_cursor IS
7 SELECT first_name
8 FROM employee
9 ORDER BY id;
10
11 v_row NUMBER := 1;
12 BEGIN
13
14 OPEN employee_cursor;
15
16 LOOP
17 FETCH employee_cursor
18 INTO employee_table(v_row);
19
20 EXIT WHEN employee_cursor%NOTFOUND;
21
22 DBMS_OUTPUT.PUT_LINE(employee_table(v_row));
23
24 v_row := v_row + 1;
25 END LOOP;
26
27 CLOSE employee_cursor;
28
29 /* Use the table method COUNT to find out how many rows are in the index-by table employee_table */
30 DBMS_OUTPUT.PUT_LINE("Total rows: "||employee_table.COUNT);
31 END;
32 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
Using INDEX BY BINARY_INTEGER
There is a way of using a FOR loop with the associative arrays INDEX BY BINARY_INTEGER
<source lang="sql">
SQL> SQL> declare
2 type dept_rty is record(id number, extra_tx VARCHAR2(2000)); 3 type dept_aa is table of dept_rty index by binary_integer; 4 v_dept_aa dept_aa; 5 begin 6 v_dept_aa(10).id :=10; 7 v_dept_aa(20).id :=20; 8 9 for i in v_dept_aa.first..v_dept_aa.last loop 10 if v_dept_aa.exists(i) then 11 DBMS_OUTPUT.put_line(v_dept_aa(i).id); 12 end if; 13 end loop; 14 end; 15 /
10 20 PL/SQL procedure successfully completed. SQL></source>
