/*下面的例子变换一个表的行与列*/ DECLARE TYPE phoneNum IS TABLE OF tb_phone_no%ROWTYPE INDEX BY BINARY_INTEGER; /*声明索引表类型*/ selPart phoneNum; /*声明索引表类型变量*/ 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; /*关闭游标*/ DBMS_OUTPUT.PUT_LINE('正常显示:'); loopCount:=selPart.FIRST; LOOP DBMS_OUTPUT.PUT_LINE(selPart(loopCount).phone_no || ' ' || selPart(loopCount).city_code || ' ' || selPart(loopCount).city_name || ' ' || selPart(loopCount).state || ' ' || selPart(loopCount).type); EXIT WHEN loopCount=selPart.LAST; loopCount:=selPart.NEXT(loopCount); END LOOP; DBMS_OUTPUT.PUT_LINE('------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('行列转换:'); FOR loopCount IN 0..colTotal-1 LOOP DBMS_OUTPUT.PUT(selPart(loopCount).phone_no || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(''); FOR loopCount IN 0..colTotal-1 LOOP DBMS_OUTPUT.PUT(selPart(loopCount).city_code || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(''); FOR loopCount IN 0..colTotal-1 LOOP DBMS_OUTPUT.PUT(selPart(loopCount).city_name || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(''); FOR loopCount IN 0..colTotal-1 LOOP DBMS_OUTPUT.PUT(selPart(loopCount).state || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(''); FOR loopCount IN 0..colTotal-1 LOOP DBMS_OUTPUT.PUT(selPart(loopCount).type || ' '); END LOOP; DBMS_OUTPUT.PUT_LINE(''); END;
http://community.csdn.net/Expert/topic/4949/4949777.xml?temp=.7287866
DECLARE
TYPE phoneNum IS TABLE OF tb_phone_no%ROWTYPE INDEX BY BINARY_INTEGER; /*声明索引表类型*/
selPart phoneNum; /*声明索引表类型变量*/
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; /*关闭游标*/ DBMS_OUTPUT.PUT_LINE('正常显示:');
loopCount:=selPart.FIRST;
LOOP
DBMS_OUTPUT.PUT_LINE(selPart(loopCount).phone_no ||
' ' || selPart(loopCount).city_code ||
' ' || selPart(loopCount).city_name ||
' ' || selPart(loopCount).state ||
' ' || selPart(loopCount).type);
EXIT WHEN loopCount=selPart.LAST;
loopCount:=selPart.NEXT(loopCount);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('行列转换:'); FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).phone_no || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).city_code || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).city_name || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).state || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).type || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END;