▲多维数组(表)
声明PL/SQL表的语法如下:
TYPE PL/SQL表名 IS TABLE OF 数据类型 INDEX BY BINARY_INTEGER;
/*下面的例子变换一个表的行与列*/
DECLARE
TYPE phoneNum IS RECORD (
phone_no VARCHAR(13) NOT NULL DEFAULT '----',
city_code VARCHAR(4) NOT NULL DEFAULT '----',
city_name VARCHAR(8) NOT NULL DEFAULT '----',
state VARCHAR(6) NOT NULL DEFAULT '----',
type VARCHAR(6) NOT NULL DEFAULT '----'
); /*声明记录类型数据*/
TYPE emp_type_array IS TABLE OF phoneNum INDEX BY BINARY_INTEGER;/*声明多维数组*/
selPart emp_type_array; /*声明多维数组变量*/
loopCount INTEGER;
colTotal INTEGER:=5; /*总计列数*/
CURSOR curSel IS /*声明游标*/
SELECT phone_no,city_code,city_name,state,type
FROM tb_phone_no
WHERE phone_no in ('1333241','1339414','1334237','1333222','1332230');
BEGIN
loopCount:=0;
OPEN curSel; /*打开游标*/
FETCH curSel INTO selPart(loopCount); /*从游标中检索数据行*/
WHILE curSel%FOUND LOOP /*当不再能找到数据时,终止查询*/
loopCount:=loopCount+1;
FETCH curSel INTO selPart(loopCount);
END LOOP;
CLOSE curSel; /*关闭游标*/
DECLARE
--定义动态光标
TYPE TotalCurTyp IS REF CURSOR;
shuju TotalCurTyp;
--记录集(存储单条查询结果)
TYPE tmp111 IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
TYPE tmp2 IS RECORD ( zhi tmp111 );
vtab tmp2;
--单维数组
TYPE arr2 IS TABLE OF tmp2 INDEX BY BINARY_INTEGER;
vary arr2;
BEGIN
--数组初始化
FOR i IN 0..vcou LOOP
vtab.zhi(i):=0;
END LOOP;
FOR i IN 1..12 LOOP
vary(i):=vtab;
END LOOP;
OPEN shuju FOR
SELECT substrb(to_char(MONTH,'yyyy-mm-dd'),6,2),
Sum(SHUI),Sum(DIAN),Sum(YOU),Sum(MEI),Sum(QI)
FROM oldtable GROUP BY substrb(to_char(MONTH,'yyyy-mm-dd'),6,2);
LOOP
FETCH shuju INTO vtab.zhi(0),vtab.zhi(1),vtab.zhi(2),vtab.zhi(3),vtab.zhi(4),vtab.zhi(5);
vary(vtab.zhi(0)):=vtab;
EXIT WHEN shuju%NOTFOUND;
END LOOP;