PLSQL--集合
来源：互联网 发布：百香坊沉香淘宝 编辑：程序博客网 时间：2024/05/22 12:37
1.联合数组：
DECLARE
CURSOR name_cur IS
SELECT last_name FROM student WHERE rownum <= 10;
TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;
last_name_tab last_name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_counter := v_counter + 1;
last_name_tab(v_counter) := name_rec.last_name;
dbms_output.put_line('last_name(' || v_counter || '): ' ||
last_name_tab(v_counter));
END LOOP;
END;
引用不存在的数据行会抛出错误：
DECLARE
CURSOR name_cur IS
SELECT last_name FROM student WHERE rownum <= 10;
TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;
last_name_tab last_name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_counter := v_counter + 1;
last_name_tab(v_counter) := name_rec.last_name;
dbms_output.put_line('last_name(' || v_counter || '): ' ||
last_name_tab(v_counter));
END LOOP;
dbms_output.put_line('last_name(11)||: ' || last_name_tab(11));
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('NO_DATA');
19 END;
20 /
last_name(1): Crocitto
last_name(2): Landry
last_name(3): Enison
last_name(4): Moskowitz
last_name(5): Olvsade
last_name(6): Mierzwa
last_name(7): Sethi
last_name(8): Walter
last_name(9): Martin
last_name(10): Noviello
NO_DATA
PL/SQL procedure successfully completed.
嵌套表：
DECLARE
CURSOR course_cur IS
SELECT description FROM course;
TYPE course_type IS TABLE OF course.description%TYPE;
course_tab course_type := course_type(); --要初始化
v_counter INTEGER := 0;
BEGIN
FOR course_rec IN course_cur
LOOP
v_counter := v_counter + 1;
course_tab.extend;
course_tab(v_counter) := course_rec.description;
END LOOP;
END;
DECLARE
CURSOR course_cur IS
SELECT description FROM course;
TYPE course_type IS TABLE OF course.description%TYPE;
course_tab course_type := course_type(); --要初始化
v_counter INTEGER := 0;
BEGIN
FOR course_rec IN course_cur
LOOP
v_counter := v_counter + 1;
course_tab.extend;
course_tab(v_counter) := course_rec.description;
END LOOP;
/* course_tab.delete(30);*/ --使用DELETE方法删除最后一个元素，delete后会保持被删除元素的占位符，因此不会抛出错误。
course_tab.trim; --PL/SQL不会保持被裁剪元素的占位符，结果，引用被裁剪的元素会导致subscript beyond count错误
course_tab(30) := 'New Course';
END;
DECLARE
CURSOR course_cur IS
SELECT description FROM course;
TYPE course_type IS TABLE OF course.description%TYPE;
course_tab course_type := course_type(); --要初始化
v_counter INTEGER := 0;
BEGIN
FOR course_rec IN course_cur
LOOP
v_counter := v_counter + 1;
course_tab.extend;
course_tab(v_counter) := course_rec.description;
END LOOP;
/* course_tab.delete(30);*/
course_tab.trim;
course_tab.extend; --增加集合尺寸大小，不会抛出错误
course_tab(30) := 'New Course';
END;
变长数组：
DECLARE
CURSOR name_cur IS
SELECT last_name FROM student WHERE rownum <= 10;
TYPE last_name_type IS VARRAY(10) OF student.last_name%TYPE;
last_name_varray last_name_type := last_name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_counter := v_counter + 1;
last_name_varray.extend;
last_name_varray(v_counter) := name_rec.last_name;
dbms_output.put_line('last_name(' || v_counter || '): ' ||
last_name_varray(v_counter));
END LOOP;
END;
DECLARE
TYPE varray_type IS VARRAY(10) OF NUMBER;
VARRAY varray_type := varray_type(1, 2, 3, 4, 5, 6);
BEGIN
dbms_output.put_line('varray.count=' || varray.count);
dbms_output.put_line('varray.limit=' || varray.limit);
dbms_output.put_line('varray.fist=' || varray.first);
dbms_output.put_line('varray.last=' || varray.last);
END;
DECLARE
CURSOR city_cur IS
SELECT city FROM zipcode WHERE rownum <= 10;
TYPE city_type IS VARRAY(10) OF zipcode.city%TYPE;
city_varray city_type := city_type(); --初始化
v_counter INTEGER := 0;
BEGIN
FOR city_rec IN city_cur
LOOP
v_counter := v_counter + 1;
city_varray.extend;--引用单个元素之前，用extend
city_varray(v_counter) := city_rec.city;
dbms_output.put_line('city_varray(' || v_counter || '): ' ||
city_varray(v_counter));
END LOOP;
END;
TYPE city_type IS VARRAY(20) OF zipcode.city%TYPE;
city_varray city_type := city_type();
v_counter INTEGER := 0;
BEGIN
FOR city_rec IN city_cur
LOOP
v_counter := v_counter + 1;
city_varray.extend;
city_varray(v_counter) := city_rec.city;
END LOOP;
FOR i IN 1 .. v_counter
LOOP
--extend the size of varray by 1 and copy the current element to the last elemant
city_varray.extend(1, i);
END LOOP;
FOR i IN 1 .. 20
LOOP
dbms_output.put_line('city_varray(' || i || '): ' ||
city_varray(i));
END LOOP;
END;
多层集合：
DECLARE
TYPE table_type1 IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
TYPE table_type2 IS TABLE OF table_type1 INDEX BY BINARY_INTEGER;
table_tab1 table_type1;
table_tab2 table_type2;
BEGIN
FOR i IN 1 .. 2
LOOP
FOR j IN 1 .. 3
LOOP
IF i = 1
THEN
table_tab1(j) := j;
ELSE
table_tab1(j) := 4 - j;
END IF;
table_tab2(i)(j) := table_tab1(j);
dbms_output.put_line('table_tabq2(' || i || ')(' || j || '): ' ||
table_tab2(i) (j));
END LOOP;
END LOOP;
END;
练习：
1.创建如下脚本：创建一个联合数组，使用教师的全名来填充它。也就是说，联合数组的每行数据都应该包含教师的第一个名字，中间名和姓氏。
DECLARE
CURSOR name_cur IS
SELECT first_name || ' ' || last_name NAME FROM instructor;
TYPE name_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
name_tab name_type;
v_counter NUMBER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := name_rec.name;
dbms_output.put_line('name(' || v_counter || '): ' ||
name_tab(v_counter));
END LOOP;
END;
2.修改以上脚本，改成变长数组：
DECLARE
CURSOR name_cur IS
SELECT first_name || ' ' || last_name NAME FROM instructor;
TYPE name_type IS VARRAY(15) OF VARCHAR2(50);
name_varray name_type := name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur
LOOP
v_counter := v_counter + 1;
name_varray.extend;
name_varray(v_counter) := name_rec.name;
dbms_output.put_line('name(' || v_counter || '): ' ||
name_varray(v_counter));
END LOOP;
END;
在脚本的这个版本中，定义了一个包含15个元素的数组。要特别注意，在引用数组的元素之前，要初始化这个数组，除此之外，往该数组添加新的元素之前，必须扩展这个数组。
3.修改以上脚本，创建另一个变长数组，使用每个教师所教授的课程的课程编号来填充。请在屏幕上显示教师的名字，已经所教授课程的列表；
DECLARE
CURSOR instructor_cur IS
SELECT instructor_id, first_name || ' ' || last_name NAME
FROM instructor;
CURSOR course_cur(p_instructor_id NUMBER) IS
SELECT UNIQUE course_no course
FROM section
WHERE instructor_id = p_instructor_id;
TYPE name_type IS VARRAY(15) OF VARCHAR2(50);
name_varray name_type := name_type();
TYPE course_type IS VARRAY(11) OF NUMBER;
course_varray course_type;
v_counter1 INTEGER := 0;
v_counter2 INTEGER;
BEGIN
FOR instructor_rec IN instructor_cur
LOOP
v_counter1 := v_counter1 + 1;
name_varray.extend;
name_varray(v_counter1) := instructor_rec.name;
dbms_output.put_line('name(' || v_counter1 || '): ' ||
name_varray(v_counter1));
--Initialize and populate course_varray
v_counter2 := 0;
course_varray := course_type();
FOR course_rec IN course_cur(instructor_rec.instructor_id)
LOOP
v_counter2 := v_counter2 + 1;
course_varray.extend;
course_varray(v_counter2) := course_rec.course;
dbms_output.put_line('course(' || v_counter2 || '): ' ||
course_varray(v_counter2));
END LOOP;
dbms_output.put_line('=========================================');
END LOOP;
END;
DECLARE
TYPE varray_type1 IS VARRAY(7) OF INTEGER;
TYPE table_type2 IS TABLE OF varray_type1 INDEX BY BINARY_INTEGER;
varray1 varray_type1 := varray_type1(1, 2, 3);
table2 table_type2;
--table2 table_type2:=table_type2(varray2,varray_type1(8,9,10));
BEGIN
--There statements populate associate array
table2(1) := varray1;
table2(2) := varray_type1(8, 9, 10);
dbms_output.put_line('table2(1)(2): ' || table2(1) (2));
FOR i IN 4 .. 7
LOOP
varray1.extend;
varray1(i) := i;
END LOOP;
--display element of the varray
FOR i IN 1 .. 7
LOOP
dbms_output.put_line('varray1(' || i || '): ' || varray1(i));
END LOOP;
END;
- PLSQL--集合
- plsql中的集合
- PLSQL:oracle 集合类型
- 『PLSQL』plsql编程 一 集合
- Oracle PLSQL的集合类型
- Oracle PLSQL的集合类型
- plsql中的集合与记录
- Oracle 集合 plsql记录表
- PLSQL:集合类型作为表列
- PLSQL总结——13.集合
- PLSQL集合类型的使用总结
- PLSQL集合类型的使用总结
- PLSQL
- PLSQL
- PLSQL
- plsql
- plsql
- plsql
- lvs安装与配置3
- poj 1164 简单地广搜
- UIImageView处理, 图片处理
- CSA 第五届讨论会 畅想
- 数据库的ACID（酸性）总结
- PLSQL--集合
- memcached client -- memcached client for java使用
- ACdream OJ 1153 (k-GCD)
- weblogic 理解MBean
- C++中构造函数的执行顺序
- cgroups
- kkjcre1p: unable to spawn jobq slave process的排查思路
- C++类和对象的大小
- 最值得学习阅读的10个C语言开源项目代码
