Oracle PL/SQL/PL SQL/Table of Type
Содержание
- 1 A package to manage a list of employees
- 2 Create a function to convert string type variable to date type variable
- 3 Define a nested table type for each column
- 4 Extend once, outside the loop for better performance
- 5 Fetch a bulk into a table structure
- 6 Fill table of custom type and use it in for loop to insert
- 7 FIRST & LAST Table Attributes
- 8 How to do a bulk collect into an associative array
- 9 How to do a bulk collect into a nested table.
- 10 NEXT & PRIOR Table Attributes
- 11 Reference type attribute through index
- 12 Select bulk collect into
- 13 Table collection indexed by column type
- 14 Table of custome type indexed by BINARY_INTEGER
- 15 The EXISTS Table Attribute
- 16 Use for loop to fill a table collection
- 17 Use for loop to insert value to table collection and then use table collection in another insert statement
- 18 Uses the COUNT method to display the number of rows contained in a table collection
A package to manage a list of employees
<source lang="sql">
SQL> SQL> CREATE TABLE employee(
2 emp_id INTEGER, 3 emp_name VARCHAR2(32), 4 supervised_by INTEGER, 5 pay_rate NUMBER(9,2), 6 pay_type CHAR);
Table created. SQL> SQL> CREATE TABLE department
2 (dept_id INTEGER, 3 dept_name VARCHAR2(32));
Table created. SQL> SQL> CREATE TABLE emp_dept (emp_id INTEGER, dept_id INTEGER); Table created. SQL> SQL> SQL> CREATE OR REPLACE package emp_dept_procs AS
2 PROCEDURE init_list; 3 PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE,dept_id IN emp_dept.dept_id%TYPE); 4 FUNCTION get_count RETURN NUMBER; 5 PROCEDURE get_from_list (to_get IN BINARY_INTEGER,emp_id OUT emp_dept.emp_id%TYPE,dept_id OUT emp_dept.dept_id%TYPE); 6 END emp_dept_procs; 7 /
Package created. SQL> SQL> CREATE OR REPLACE package body emp_dept_procs AS
2 listx BINARY_INTEGER; 3 TYPE emp_dept_pk IS RECORD (emp_id emp_dept.emp_id%TYPE,dept_id emp_dept.dept_id%TYPE); 4 TYPE emp_dept_list_type IS TABLE OF emp_dept_pk INDEX BY BINARY_INTEGER; 5 6 emp_dept_list emp_dept_list_type; 7 8 PROCEDURE init_list is 9 BEGIN 10 listx := 0; 11 END; 12 13 PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE ,dept_id IN emp_dept.dept_id%TYPE) IS 14 BEGIN 15 listx := listx + 1; 16 emp_dept_list(listx).emp_id := emp_id; 17 emp_dept_list(listx).dept_id := dept_id; 18 END; 19 20 FUNCTION get_count RETURN NUMBER IS 21 BEGIN 22 RETURN listx; 23 END; 24 25 PROCEDURE get_from_list (to_get IN BINARY_INTEGER ,emp_id OUT emp_dept.emp_id%TYPE ,dept_id OUT emp_dept.dept_id%TYPE) IS 26 BEGIN 27 emp_id := emp_dept_list(to_get).emp_id; 28 dept_id := emp_dept_list(to_get).dept_id; 29 END; 30 31 END emp_dept_procs; 32 /
Package body created. SQL> SQL> drop table department; Table dropped. SQL> drop table employee; Table dropped. SQL> --
</source>
Create a function to convert string type variable to date type variable
<source lang="sql">
SQL> SQL> create or replace
2 function my_to_date( p_string in varchar2 ) return date
3 as
4 type fmtArray is table of varchar2(25);
5
6 l_fmts fmtArray := fmtArray( "dd-mon-yyyy", "dd-month-yyyy",
7 "dd/mm/yyyy",
8 "dd/mm/yyyy hh24:mi:ss" );
9 l_return date;
10 begin
11 for i in 1 .. l_fmts.count
12 loop
13 begin
14 l_return := to_date( p_string, l_fmts(i) );
15 exception
16 when others then null;
17 end;
18 EXIT when l_return is not null;
19 end loop;
20
21 if ( l_return is null )
22 then
23 l_return :=
24 new_time( to_date("01011970","ddmmyyyy") + 1/24/60/60 *
25 p_string, "GMT", "EST" );
26 end if;
27
28 return l_return;
29 end;
30 /
Function created. SQL> SQL> select my_to_date("12-02-2008") from dual; MY_TO_DATE("12-02-20
12-FEB-2008 00:00:00 1 row selected. SQL> SQL> --
</source>
Define a nested table type for each column
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_depts IS 3 SELECT dept_id, dept_name FROM department ORDER BY dept_name; 4 5 TYPE dept_id IS TABLE OF department.dept_id%TYPE; 6 TYPE dept_name IS TABLE OF department.dept_name%TYPE; 7 8 dept_ids dept_id; 9 dept_names dept_name; 10 inx1 PLS_INTEGER; 11 BEGIN 12 OPEN all_depts; 13 FETCH all_depts BULK COLLECT INTO dept_ids, dept_names; 14 CLOSE all_depts; 15 16 FOR inx1 IN 1..dept_ids.count LOOP 17 dept_names(inx1) := UPPER(dept_names(inx1)); 18 DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) ||" " || dept_names(inx1)); 19 END LOOP; 20 21 FORALL x IN dept_ids.first..dept_ids.last 22 UPDATE department 23 SET dept_name = dept_names(x) 24 WHERE dept_id = dept_ids(x); 25 END; 26 /
10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table department; Table dropped. SQL> --
</source>
Extend once, outside the loop for better performance
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 TYPE dept_id IS TABLE OF department.dept_id%TYPE; 3 TYPE dept_name IS TABLE OF department.dept_name%TYPE; 4 5 dept_ids dept_id; 6 dept_names dept_name; 7 inx1 PLS_INTEGER; 8 BEGIN 9 dept_ids := dept_id(); 10 dept_names := dept_name(); 11 12 dept_ids.extend(10); 13 dept_names.extend(10); 14 15 FOR inx1 IN 1..10 LOOP 16 dept_ids(inx1) := inx1 + 10; 17 dept_names(inx1) := "Dept #" || TO_CHAR(inx1+10); 18 END LOOP; 19 20 FORALL x IN dept_ids.first..dept_ids.last 21 INSERT INTO department (dept_id, dept_name) 22 VALUES (dept_ids(x), dept_names(x)); 23 END; 24 /
PL/SQL procedure successfully completed. SQL> SQL> drop table department; Table dropped. SQL> SQL> --
</source>
Fetch a bulk into a table structure
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_depts IS 3 SELECT dept_id, dept_name FROM department ORDER BY dept_name; 4 5 TYPE dept_id IS TABLE OF department.dept_id%TYPE; 6 TYPE dept_name IS TABLE OF department.dept_name%TYPE; 7 8 dept_ids dept_id; 9 dept_names dept_name; 10 inx1 PLS_INTEGER; 11 BEGIN 12 OPEN all_depts; 13 FETCH all_depts BULK COLLECT INTO dept_ids, dept_names; 14 CLOSE all_depts; 15 16 FOR inx1 IN 1..dept_ids.count LOOP 17 DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) || " " || dept_names(inx1)); 18 END LOOP; 19 END; 20 /
10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table department; Table dropped. SQL> --
</source>
Fill table of custom type and use it in for loop to insert
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Chars IS TABLE OF MyTable.char_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Numbers t_Numbers; 7 v_Chars t_Chars; 8 BEGIN 9 FOR v_Count IN 1..50 LOOP 10 v_Numbers(v_Count) := v_Count; 11 v_Chars(v_Count) := "Row number " || v_Count; 12 END LOOP; 13 14 FOR v_Count IN 1..50 LOOP 15 INSERT INTO MyTable VALUES 16 (v_Numbers(v_Count), v_Chars(v_Count)); 17 END LOOP; 18 END; 19 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1.00 Row number 1 2.00 Row number 2 3.00 Row number 3 4.00 Row number 4 5.00 Row number 5 6.00 Row number 6 7.00 Row number 7 8.00 Row number 8 9.00 Row number 9 10.00 Row number 10 11.00 Row number 11 NUM_COL CHAR_COL
------------------------------------------------------------
12.00 Row number 12 13.00 Row number 13 14.00 Row number 14 15.00 Row number 15 16.00 Row number 16 17.00 Row number 17 18.00 Row number 18 19.00 Row number 19 20.00 Row number 20 21.00 Row number 21 22.00 Row number 22 NUM_COL CHAR_COL
------------------------------------------------------------
23.00 Row number 23 24.00 Row number 24 25.00 Row number 25 26.00 Row number 26 27.00 Row number 27 28.00 Row number 28 29.00 Row number 29 30.00 Row number 30 31.00 Row number 31 32.00 Row number 32 33.00 Row number 33 NUM_COL CHAR_COL
------------------------------------------------------------
34.00 Row number 34 35.00 Row number 35 36.00 Row number 36 37.00 Row number 37 38.00 Row number 38 39.00 Row number 39 40.00 Row number 40 41.00 Row number 41 42.00 Row number 42 43.00 Row number 43 44.00 Row number 44 NUM_COL CHAR_COL
------------------------------------------------------------
45.00 Row number 45 46.00 Row number 46 47.00 Row number 47 48.00 Row number 48 49.00 Row number 49 50.00 Row number 50
50 rows selected. SQL> SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
FIRST & LAST Table Attributes
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> DECLARE
2 type t_LastNameTable IS TABLE OF lecturer.last_name%TYPE 3 INDEX BY BINARY_INTEGER; 4 v_LastNames t_LastNameTable; 5 v_Index BINARY_INTEGER; 6 BEGIN 7 v_LastNames(43) := "Mason"; 8 v_LastNames(50) := "Junebug"; 9 v_LastNames(47) := "Taller"; 10 11 v_Index := v_LastNames.FIRST; 12 13 v_Index := v_LastNames.LAST; 14 END; 15 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>
</source>
How to do a bulk collect into an associative array
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> CREATE TABLE myTable
2 (id NUMBER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> DECLARE
2 3 TYPE number_table IS TABLE OF myTable.id%TYPE INDEX BY BINARY_INTEGER; 4 5 6 number_list NUMBER_TABLE; 7 8 BEGIN 9 10 11 FOR i IN 1..10000 LOOP 12 13 number_list(i) := i; 14 15 END LOOP; 16 17 18 FORALL i IN 1..number_list.COUNT 19 INSERT INTO myTable VALUES (number_list(i)); 20 21 COMMIT; 22 23 END; 24 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
How to do a bulk collect into a nested table.
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> -- Create a table for the example. SQL> CREATE TABLE myTable
2 (id NUMBER NOT NULL 3 ,CONSTRAINT id_pk PRIMARY KEY (id));
Table created. SQL> SQL> SQL> SQL> DECLARE
2 TYPE number_table IS TABLE OF myTable.id%TYPE; 3 4 number_list NUMBER_TABLE := number_table(); 5 6 BEGIN 7 8 9 number_list.EXTEND(10000); 10 11 12 FOR i IN 1..10000 LOOP 13 14 15 number_list(i) := i; 16 17 END LOOP; 18 19 20 FORALL i IN 1..number_list.COUNT 21 INSERT INTO myTable VALUES (number_list(i)); 22 END; 23 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped.
</source>
NEXT & PRIOR Table Attributes
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 TYPE t_MajorTable IS TABLE OF lecturer.major%TYPE 3 INDEX BY BINARY_INTEGER; 4 v_Majors t_MajorTable; 5 v_Index BINARY_INTEGER; 6 BEGIN 7 v_Majors(-7) := "Computer Science"; 8 v_Majors(4) := "History"; 9 v_Majors(5) := "Economics"; 10 11 v_Index := v_Majors.FIRST; 12 LOOP 13 INSERT INTO MyTable (num_col, char_col) 14 VALUES (v_Index, v_Majors(v_Index)); 15 EXIT WHEN v_Index = v_Majors.LAST; 16 v_Index := v_Majors.NEXT(v_Index); 17 END LOOP; 18 END; 19 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
-7 Computer Science
4 History
5 Economics
SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Reference type attribute through index
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> DECLARE
2 TYPE StudentTab IS TABLE OF lecturer%ROWTYPE 3 INDEX BY BINARY_INTEGER; 4 v_lecturer StudentTab; 5 BEGIN 6 SELECT * 7 INTO v_lecturer(10001) 8 FROM lecturer 9 WHERE id = 10001; 10 11 v_lecturer(1).first_name := "Larry"; 12 v_lecturer(1).last_name := "Lemon"; 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
Select bulk collect into
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_Numbers IS VARRAY(10) OF MyTable.num_col%TYPE; 3 v_Numbers t_Numbers; 4 BEGIN 5 SELECT num_col 6 BULK COLLECT INTO v_Numbers 7 FROM MyTable; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Table collection indexed by column type
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp
2 /
ID FNAME LNAME 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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
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> CREATE OR REPLACE PROCEDURE myProcedure
2 AS
3 CURSOR myCursor IS SELECT id, fname FROM emp;
4 TYPE firstNameType IS TABLE OF emp.fname%type INDEX BY emp.id%type;
5 ch firstNameType;
6 i integer := 0;
7 imax integer;
8 BEGIN
9 FOR j IN myCursor LOOP
10 i := i + 1;
11 ch(i) := j.fname;
12 END LOOP;
13 dbms_output.put_line(ch("James"));
14 END myProcedure;
15 /
Procedure created. SQL> SQL> exec myProcedure; SQL> SQL> SQL> SQL> -- clean the table SQL> drop table emp
2 /
Table dropped. SQL> SQL> SQL>
</source>
Table of custome type indexed by BINARY_INTEGER
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Chars IS TABLE OF MyTable.char_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Numbers t_Numbers; 7 v_Chars t_Chars; 8 BEGIN 9 -- Fill up the arrays with 50 rows. 10 FOR v_Count IN 1..50 LOOP 11 v_Numbers(v_Count) := v_Count; 12 v_Chars(v_Count) := "Row number " || v_Count; 13 END LOOP; 14 15 -- And insert them into the database using bulk binds. 16 FORALL v_Count IN 1..50 17 INSERT INTO MyTable VALUES 18 (v_Numbers(v_Count), v_Chars(v_Count)); 19 END; 20 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1.00 Row number 1 2.00 Row number 2 3.00 Row number 3 4.00 Row number 4 5.00 Row number 5 6.00 Row number 6 7.00 Row number 7 8.00 Row number 8 9.00 Row number 9 10.00 Row number 10 11.00 Row number 11 NUM_COL CHAR_COL
------------------------------------------------------------
12.00 Row number 12 13.00 Row number 13 14.00 Row number 14 15.00 Row number 15 16.00 Row number 16 17.00 Row number 17 18.00 Row number 18 19.00 Row number 19 20.00 Row number 20 21.00 Row number 21 22.00 Row number 22 NUM_COL CHAR_COL
------------------------------------------------------------
23.00 Row number 23 24.00 Row number 24 25.00 Row number 25 26.00 Row number 26 27.00 Row number 27 28.00 Row number 28 29.00 Row number 29 30.00 Row number 30 31.00 Row number 31 32.00 Row number 32 33.00 Row number 33 NUM_COL CHAR_COL
------------------------------------------------------------
34.00 Row number 34 35.00 Row number 35 36.00 Row number 36 37.00 Row number 37 38.00 Row number 38 39.00 Row number 39 40.00 Row number 40 41.00 Row number 41 42.00 Row number 42 43.00 Row number 43 44.00 Row number 44 NUM_COL CHAR_COL
------------------------------------------------------------
45.00 Row number 45 46.00 Row number 46 47.00 Row number 47 48.00 Row number 48 49.00 Row number 49 50.00 Row number 50
50 rows selected. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
The EXISTS Table Attribute
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 TYPE t_FirstNameTable IS TABLE OF lecturer.first_name%TYPE
3 INDEX BY BINARY_INTEGER;
4 FirstNames t_FirstNameTable;
5 BEGIN
6 FirstNames(1) := "Scott";
7 FirstNames(3) := "Joanne";
8
9 IF FirstNames.EXISTS(1) THEN
10 INSERT INTO MyTable (char_col) VALUES
11 ("Row 1 exists!");
12 ELSE
13 INSERT INTO MyTable (char_col) VALUES
14 ("Row 1 doesn""t exist!");
15 END IF;
16 IF FirstNames.EXISTS(2) THEN
17 INSERT INTO MyTable (char_col) VALUES
18 ("Row 2 exists!");
19 ELSE
20 INSERT INTO MyTable (char_col) VALUES
21 ("Row 2 does n""t exist!");
22 END IF;
23 END;
24 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
Row 1 exists!
Row 2 does n"t exist!
SQL> SQL> drop table lecturer; Table dropped. SQL> SQL> drop table MyTable; Table dropped.
</source>
Use for loop to fill a table collection
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> set serveroutput on format wrapped SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE; 3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE; 4 v_Numbers t_Numbers := t_Numbers(1); 5 v_Strings t_Strings := t_Strings(1); 6 7 BEGIN 8 v_Numbers.EXTEND(1000); 9 v_Strings.EXTEND(1000); 10 FOR v_Count IN 1..1000 LOOP 11 v_Numbers(v_Count) := v_Count; 12 v_Strings(v_Count) := "Element #" || v_Count; 13 END LOOP; 14 15 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Use for loop to insert value to table collection and then use table collection in another insert statement
<source lang="sql">
SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Chars IS TABLE OF MyTable.char_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Numbers t_Numbers; 7 v_Chars t_Chars; 8 BEGIN 9 FOR v_Count IN 1..50 LOOP 10 v_Numbers(v_Count) := v_Count; 11 v_Chars(v_Count) := "Row number " || v_Count; 12 END LOOP; 13 14 FORALL v_Count IN 1..50 15 INSERT INTO MyTable VALUES 16 (v_Numbers(v_Count), v_Chars(v_Count)); 17 END; 18 /
PL/SQL procedure successfully completed. SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Uses the COUNT method to display the number of rows contained in a table collection
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp
2 /
ID FNAME LNAME 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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
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
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
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> SQL> DECLARE
2 TYPE emp_table_struct IS TABLE OF emp.fname%TYPE INDEX BY BINARY_INTEGER;
3 emp_table emp_table_struct;
4 CURSOR emp_cursor IS SELECT fname FROM emp ORDER BY id;
5 v_row NUMBER := 1;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO emp_table(v_row);
10 EXIT WHEN emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(emp_table(v_row));
12 v_row := v_row + 1;
13 END LOOP;
14 CLOSE emp_cursor;
15 DBMS_OUTPUT.PUT_LINE("Total rows: "||emp_table.COUNT);
16 END;
17 /
PL/SQL procedure successfully completed. SQL> SQL> -- clean the table SQL> drop table emp
2 /
Table dropped.
</source>
