Oracle PL/SQL/PL SQL/Table of Varchar2
Содержание
- 1 Allocate space as you increment the index.
- 2 A NULL table, and a table with a NULL element.
- 3 Assign the character index to a variable.
- 4 Assign the first character index to a variable.
- 5 Assign to three elements of the table. Note that the key values are not sequential
- 6 Assign values to subscripted members of the nested table.
- 7 Assign value to table record after delete method call
- 8 Assign value to table records
- 9 Assign value to varchar2 collection by index
- 10 Assing an empty table record to non-empty table record
- 11 behavior of NULL nested tables.
- 12 Call delete method on table record
- 13 Call delete method with number on table record
- 14 Clear the table of varchar2
- 15 dbms_sql.varchar2_table type
- 16 Declare a nested table with null values.
- 17 Define "table of varchar2" as data type and insert data
- 18 Delete element in table of varchar2
- 19 Dynamic initialization and assignment in the execution section.
- 20 first last next
- 21 index by binary integer or by varchar2
- 22 Insert elements into table of varchar2
- 23 Reference count property of table record
- 24 Table of varchar2 delete all
- 25 Table of varchar2 element count
- 26 Varchar table indexed by BINARY_INTEGER
- 27 VARRAYs, nested tables, index-by tables
- 28 You cannot traverse an associative array until elements are initialized.
Allocate space as you increment the index.
SQL>
SQL> DECLARE
2
3 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
4
5
6 charArray charArrayType := charArrayType();
7 BEGIN
8 FOR i IN 1..3 LOOP
9 charArray.EXTEND;
10 IF i = 1 THEN
11 charArray(i) := "A";
12 ELSIF i = 2 THEN
13 charArray(i) := "B";
14 ELSIF i = 3 THEN
15 charArray(i) := "C";
16 END IF;
17 END LOOP;
18
19 FOR i IN 1..3 LOOP
20 dbms_output.put (i||":");
21 dbms_output.put_line(charArray(i));
22 END LOOP;
23 END;
24 /
1:A
2:B
3:C
PL/SQL procedure successfully completed.
A NULL table, and a table with a NULL element.
SQL>
SQL> DECLARE
2 TYPE wordTableType IS TABLE OF VARCHAR2(50);
3
4 myTable1 wordTableType;
5
6 myTable2 wordTableType := wordTableType();
7 BEGIN
8 IF myTable1 IS NULL THEN
9 DBMS_OUTPUT.PUT_LINE("myTable1 is NULL");
10 ELSE
11 DBMS_OUTPUT.PUT_LINE("myTable1 is not NULL");
12 END IF;
13
14 IF myTable2 IS NULL THEN
15 DBMS_OUTPUT.PUT_LINE("myTable2 is NULL");
16 ELSE
17 DBMS_OUTPUT.PUT_LINE("myTable2 is not NULL");
18 END IF;
19 END;
20 /
myTable1 is NULL
myTable2 is not NULL
PL/SQL procedure successfully completed.
SQL>
SQL>
Assign the character index to a variable.
SQL>
SQL> DECLARE
2 current VARCHAR2(9 CHAR);
3 element INTEGER;
4
5 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
6 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
7
8 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June");
9
10 calendar CALENDAR_TABLE;
11 BEGIN
12 IF calendar.COUNT = 0 THEN
13 FOR i IN month.FIRST..month.LAST LOOP
14 calendar(month(i)) := TO_CHAR(i);
15 DBMS_OUTPUT.PUT_LINE("Index ["||month(i)||"] is ["||i||"]");
16 END LOOP;
17
18
19 END IF;
20 END;
21 /
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
PL/SQL procedure successfully completed.
Assign the first character index to a variable.
SQL>
SQL> DECLARE
2 current VARCHAR2(9 CHAR);
3 element INTEGER;
4
5 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
6 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
7
8 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December");
9
10 calendar CALENDAR_TABLE;
11 BEGIN
12 IF calendar.COUNT = 0 THEN
13 FOR i IN month.FIRST..month.LAST LOOP
14 calendar(month(i)) := TO_CHAR(i);
15 DBMS_OUTPUT.PUT_LINE("Index ["||month(i)||"] is ["||i||"]");
16 END LOOP;
17
18
19 END IF;
20 END;
21 /
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
Index [July] is [7]
Index [August] is [8]
Index [September] is [9]
Index [October] is [10]
Index [November] is [11]
Index [December] is [12]
PL/SQL procedure successfully completed.
Assign to three elements of the table. Note that the key values are not sequential
SQL>
SQL> DECLARE
2 TYPE CharacterTab IS TABLE OF VARCHAR2(10)
3 INDEX BY BINARY_INTEGER;
4 v_Characters CharacterTab;
5 BEGIN
6 v_Characters(0) := "H";
7 v_Characters(-7) := "S";
8 v_Characters(3) := "S";
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Assign values to subscripted members of the nested table.
SQL>
SQL> DECLARE
2 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
3 charArray charArrayType := charArrayType(NULL,NULL,NULL);
4 BEGIN
5
6 charArray(1) := "A";
7 charArray(2) := "B";
8 charArray(3) := "C";
9
10 FOR i IN 1..3 LOOP
11 dbms_output.put_line(i||":"||charArray(i));
12 END LOOP;
13 END;
14 /
1:A
2:B
3:C
PL/SQL procedure successfully completed.
SQL>
Assign value to table record after delete method call
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 dbms_output.put_line(myText_table.count);
13
14 myText_table.DELETE;
15 dbms_output.put_line(myText_table.count);
16
17 myText_table(15) := "some text";
18 myText_table(25) := "some more text";
19 dbms_output.put_line(myText_table.count);
20 end;
21 /
3
0
2
PL/SQL procedure successfully completed.
SQL>
SQL>
Assign value to table records
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>
Assign value to varchar2 collection by index
SQL>
SQL> DECLARE
2 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
3
4 charArray charArrayType := charArrayType(NULL,NULL,NULL);
5 BEGIN
6
7 FOR i IN 1..3 LOOP
8 dbms_output.put ("charArray Varray:"||i);
9 dbms_output.put_line(":"||charArray(i));
10 END LOOP;
11
12 charArray(1) := "A";
13 charArray(2) := "B";
14 charArray(3) := "C";
15
16 FOR i IN 1..3 LOOP
17 dbms_output.put_line(i||":"||charArray(i));
18 END LOOP;
19 END;
20 /
charArray Varray:1:
charArray Varray:2:
charArray Varray:3:
1:A
2:B
3:C
PL/SQL procedure successfully completed.
SQL>
Assing an empty table record to non-empty table record
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 dbms_output.put_line(myText_table.count);
13
14 myText_table.DELETE;
15 dbms_output.put_line(myText_table.count);
16
17 myText_table(15) := "some text";
18 myText_table(25) := "some more text";
19 dbms_output.put_line(myText_table.count);
20
21 myText_table := l_empty_table;
22 dbms_output.put_line(myText_table.count);
23
24 end;
25 /
3
0
2
0
PL/SQL procedure successfully completed.
behavior of NULL nested tables.
SQL>
SQL> DECLARE
2 TYPE wordTableType IS TABLE OF VARCHAR2(50);
3
4
5 myTable1 wordTableType;
6
7
8 myTable2 wordTableType := wordTableType();
9 BEGIN
10 IF myTable1 IS NULL THEN
11 DBMS_OUTPUT.PUT_LINE("myTable1 is NULL");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("myTable1 is not NULL");
14 END IF;
15
16 IF myTable2 IS NULL THEN
17 DBMS_OUTPUT.PUT_LINE("myTable2 is NULL");
18 ELSE
19 DBMS_OUTPUT.PUT_LINE("myTable2 is not NULL");
20 END IF;
21 END;
22 /
myTable1 is NULL
myTable2 is not NULL
PL/SQL procedure successfully completed.
SQL>
SQL>
Call delete method on table record
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 dbms_output.put_line(myText_table.count);
13
14 myText_table.DELETE;
15 dbms_output.put_line(myText_table.count);
16
17 end;
18 /
3
0
PL/SQL procedure successfully completed.
SQL>
Call delete method with number on table record
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 dbms_output.put_line(myText_table.count);
13
14 myText_table.DELETE(20);
15 dbms_output.put (myText_table.count);
16
17 end;
18 /
3
PL/SQL procedure successfully completed.
SQL>
Clear the table of varchar2
SQL>
SQL> -- delete Pl/SQL table records
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11
12 l_text_table := l_empty_table;
13 dbms_output.put ("Once we assign our populated table to an empty ");
14 dbms_output.put_line("table, we end up with " || l_text_table.count);
15 dbms_output.put_line(" varchar2s ");
16
17 end;
18 /
Once we assign our populated table to an empty table, we end up with 0
varchar2s
PL/SQL procedure successfully completed.
SQL>
dbms_sql.varchar2_table type
SQL>
SQL>
SQL> declare
2 l_names dbms_sql.varchar2_table;
3 begin
4 l_names(1) := "W";
5 l_names(2) := "J";
6 l_names(3) := "C";
7
8 for idx in 1 .. l_names.COUNT loop
9 dbms_output.put_line("Name (" || idx || ") is " || l_names(idx));
10 end loop;
11 end;
12 /
Name (1) is W
Name (2) is J
Name (3) is C
PL/SQL procedure successfully completed.
SQL>
Declare a nested table with null values.
SQL>
SQL> DECLARE
2 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
3
4 charArray charArrayType := charArrayType(NULL,NULL,NULL);
5 BEGIN
6
7 FOR i IN 1..3 LOOP
8 dbms_output.put_line("charArray:"||i||":"||charArray(i));
9 END LOOP;
10 END;
11 /
charArray:1:
charArray:2:
charArray:3:
PL/SQL procedure successfully completed.
SQL>
Define "table of varchar2" as data type and insert data
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11 l_text_table(15) := "some text";
12 l_text_table(25) := "some more text";
13 dbms_output.put ("After some assignments, we end up with ");
14 dbms_output.put_line(l_text_table.count || " varchar2s ");
15 dbms_output.put_line("-");
16
17 end;
18 /
After some assignments, we end up with 5 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
Delete element in table of varchar2
SQL> -- delete Pl/SQL table records
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11 l_text_table.DELETE(20);
12 dbms_output.put ("After using the DELETE operator on the second ");
13 dbms_output.put ("record (ie, DELETE(20), we have "||l_text_table.count);
14 dbms_output.put_line(" varchar2s");
15 dbms_output.put_line("-");
16
17 end;
18 /
After using the DELETE operator on the second record (ie, DELETE(20), we have 2 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>
Dynamic initialization and assignment in the execution section.
SQL> DECLARE
2 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
3
4 charArray charArrayType := charArrayType();
5 BEGIN
6 FOR i IN 1..3 LOOP
7 charArray.EXTEND;
8 IF i = 1 THEN
9 charArray(i) := "A";
10 ELSIF i = 2 THEN
11 charArray(i) := "B";
12 ELSIF i = 3 THEN
13 charArray(i) := "C";
14 END IF;
15 END LOOP;
16
17 FOR i IN 1..3 LOOP
18 dbms_output.put (i||":");
19 dbms_output.put_line(charArray(i));
20 END LOOP;
21 END;
22 /
1:A
2:B
3:C
PL/SQL procedure successfully completed.
first last next
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 myIndex number;
7 begin
8 for emp_rec in (select * from emp) loop
9 myText_table(emp_rec.empno) := emp_rec.ename;
10 end loop;
11
12 myIndex := myText_table.first;
13 loop
14 exit when myIndex is null;
15 dbms_output.put_line(myIndex ||":"|| myText_table(myIndex));
16 myIndex := myText_table.next(myIndex);
17 end loop;
18 end;
19 /
7369:SMITH
7499:ALLEN
7521:WARD
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7839:KING
7844:TURNER
7876:ADAMS
7900:JAMES
7902:FORD
7934:MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
index by binary integer or by varchar2
SQL> declare
2 type charListType is table of number index by varchar2(100);
3 vc charListType;
4 type numberListType is table of number index by binary_integer;
5 n numberListType;
6 t number;
7 begin
8 t := dbms_utility.get_time;
9 for i in 1 .. 1000 loop
10 n(i*1000) := i;
11 end loop;
12 dbms_output.put_line("Index by Number : "||(dbms_utility.get_time-t));
13 t := dbms_utility.get_time;
14 for i in 1 .. 1000 loop
15 vc(i*1000) := i;
16 end loop;
17 dbms_output.put_line("Index by Varchar2: "||(dbms_utility.get_time-t));
18 end;
19 /
Index by Number : 0
Index by Varchar2: 1
PL/SQL procedure successfully completed.
SQL>
SQL>
Insert elements into table of varchar2
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11 l_text_table(15) := "some text";
12 l_text_table(25) := "some more text";
13 dbms_output.put ("After some assignments, we end up with ");
14 dbms_output.put_line(l_text_table.count || " varchar2s ");
15 dbms_output.put_line("-");
16
17 end;
18 /
After some assignments, we end up with 5 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>
Reference count property of table record
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200)
3 index by binary_integer;
4
5 myText_table myTextTableType;
6 l_empty_table myTextTableType;
7 begin
8 myText_table(10) := "A";
9 myText_table(20) := "AA";
10 myText_table(30) := "AAA";
11
12 dbms_output.put_line(myText_table.count);
13
14 end;
15 /
3
PL/SQL procedure successfully completed.
SQL>
Table of varchar2 delete all
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11 l_text_table.DELETE;
12 dbms_output.put ("After using the DELETE operator, we have ");
13 dbms_output.put_line(l_text_table.count || " varchar2s ");
14 dbms_output.put_line("-");
15
16 end;
17 /
After using the DELETE operator, we have 0 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>
Table of varchar2 element count
SQL>
SQL>
SQL> declare
2 type myTextTableType is table of varchar2(200) index by binary_integer;
3
4 l_text_table myTextTableType;
5 l_empty_table myTextTableType;
6 begin
7 l_text_table(10) := "A value";
8 l_text_table(20) := "Another value";
9 l_text_table(30) := "Yet another value";
10
11 dbms_output.put_line("We start with " || l_text_table.count || " varchar2s ");
12 dbms_output.put_line("-");
13
14 end;
15 /
We start with 3 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>
Varchar table indexed by BINARY_INTEGER
SQL>
SQL> DECLARE
2 TYPE CharacterTab IS TABLE OF VARCHAR2(10)
3 INDEX BY BINARY_INTEGER;
4 v_Characters CharacterTab;
5 BEGIN
6 v_Characters(0) := "H";
7 v_Characters(-7) := "S";
8 v_Characters(3) := "S";
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
VARRAYs, nested tables, index-by tables
-- VARRAYs, nested tables, index-by tables
DECLARE
TYPE name_nested_typ IS TABLE OF VARCHAR2(20);
first_name_table name_nested_typ;
TYPE name_index_by_typ IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
last_name_table name_index_by_typ;
BEGIN
first_name_table := name_nested_typ("A", "B","C");
first_name_table.EXTEND;
first_name_table(4) := "Ringo";
last_name_table(3) := "Smith";
DBMS_OUTPUT.PUT_LINE(first_name_table(4));
END;
You cannot traverse an associative array until elements are initialized.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
2
3
4 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
5
6
7 charArray charArrayType;
8
9 BEGIN
10
11
12 DBMS_OUTPUT.PUT_LINE(charArray(1));
13
14 END;
15 /