/*下面的例子变换一个表的行与列*/
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;
/*运行示例*/
SQL> @d:\temp\oracle.sql
61 /
正常显示:
1332230 0417 营口 辽宁 CDMA
1333222 0411 大连 辽宁 CDMA
1334237 0427 盘锦 辽宁 CDMA
1339414 0414 本溪 辽宁 CDMA
1333241 024 沈阳 辽宁 CDMA
------------------------------------------------
行列转换:
1332230 1333222 1334237 1339414 1333241
0417 0411 0427 0414 024
营口 大连 盘锦 本溪 沈阳
辽宁 辽宁 辽宁 辽宁 辽宁
CDMA CDMA CDMA CDMA CDMA PL/SQL 过程已成功完成。
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;
/*运行示例*/
SQL> @d:\temp\oracle.sql
61 /
正常显示:
1332230 0417 营口 辽宁 CDMA
1333222 0411 大连 辽宁 CDMA
1334237 0427 盘锦 辽宁 CDMA
1339414 0414 本溪 辽宁 CDMA
1333241 024 沈阳 辽宁 CDMA
------------------------------------------------
行列转换:
1332230 1333222 1334237 1339414 1333241
0417 0411 0427 0414 024
营口 大连 盘锦 本溪 沈阳
辽宁 辽宁 辽宁 辽宁 辽宁
CDMA CDMA CDMA CDMA CDMA PL/SQL 过程已成功完成。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货