如果集合有至少一个元素，则FIRST和LAST分别返回第一个和最后一个元素索引。如
果集合只有一个元素，first和last返回的索引值是相同的。如果集合为empty，则FIRST和
LAST返回null。
l
关联数组的FIRST and LAST
对于以PLS_INTEGER索引的关联数组，第1个和最后一个索引分别是最大和最小索
引。对于以字符串为索引的关联数组，第一个和最后一个索引分别是排序的最大值和最小
值。索引值已经被排好序了。
CREATE OR REPLACE PROCEDURE P_5_19 AS
TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa_int aa_type_int;
BEGIN
aa_int(1) := 3;
aa_int(2) := 6;
aa_int(3) := 9;
aa_int(4) := 12;
DBMS_OUTPUT.PUT_LINE('Before deletions:');
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
aa_int.DELETE(1);
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 76 -
aa_int.DELETE(4);
DBMS_OUTPUT.PUT_LINE('After deletions:');
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
END;
--Result:
--Before deletions:
--FIRST = 1
--LAST = 4
--After deletions:
--FIRST = 2
--LAST = 3
CREATE OR REPLACE PROCEDURE P_5_20 AS
TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
aa_str aa_type_str;
BEGIN
aa_str('Z') := 26;
aa_str('A') := 1;
aa_str('K') := 11;
aa_str('R') := 18;
DBMS_OUTPUT.PUT_LINE('Before deletions:');
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
aa_str.DELETE('A');
aa_str.DELETE('Z');
DBMS_OUTPUT.PUT_LINE('After deletions:');
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
END;
--Result:
--Before deletions:
--FIRST = A
--LAST = Z
--After deletions:
--FIRST = K
--LAST = R
l
可变数组的FIRST and LAST
对于非EMPTY可变数组，FIRST总返回1，LAST总返还COUNT。
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 77 -
CREATE OR REPLACE PROCEDURE P_5_21 AS
TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
team team_type;
PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Team Status:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team := team_type(); -- Team is funded, but nobody is on it.
DBMS_OUTPUT.PUT_LINE('Team Status:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team := team_type('John', 'Mary'); -- Put 2 members on team.
DBMS_OUTPUT.PUT_LINE('Initial Team:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 78 -
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Change team.
DBMS_OUTPUT.PUT_LINE('New Team:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
--Result:
--Team Status:
--Does not exist
---
--Team Status:
--Has no members
---
--Initial Team:
--1. John
--2. Mary
---
--New Team:
--1. Arun
--2. Amitha
--3. Allan
--4. Mae
l
内嵌表的FIRST and LAST
嵌套表的LAST为COUNT，除非你用DELETE删除元素，这时候LAST>COUNT
CREATE OR REPLACE PROCEDURE P_5_22 AS
TYPE team_type IS TABLE OF VARCHAR2(15);
team team_type;
BEGIN
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 79 -
DBMS_OUTPUT.PUT_LINE('Team Status:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT(i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team := team_type(); -- Team is funded, but nobody is on it.
DBMS_OUTPUT.PUT_LINE('Team Status:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT(i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Add members.
DBMS_OUTPUT.PUT_LINE('Initial Team:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 80 -
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT(i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
team.DELETE(2,3); -- Remove 2nd and 3rd members.
DBMS_OUTPUT.PUT_LINE('Current Team:');
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT(i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
--Result:
--Team Status:
--Does not exist
---
--Team Status:
--Has no members
---
--Initial Team:
--1. Arun
--2. Amitha
--3. Allan
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 81 -
--4. Mae
---
--Current Team:
--1. Arun
--2. (to be hired)
--3. (to be hired)
--4. Mae