我今天写了一个小的procedure,但是遇到一点问题,我写了一个cursor R,cursor是 从一个表中得出了一些首字母然后排序排好,按照题目的意思是要横的输出一行这些字母,竖的一列输出一些字母,但我只实现了一半,下面第一张图是题目要求,在下面一张截图是我的输出结果:
A B H M N P S W
A 0 0 0 0 0 0 0 1
B 0 0 0 1 0 0 0 0
H 0 0 0 0 0 5 0 0
M 0 1 0 1 0 0 0 0
N 0 0 0 0 1 2 0 0
P 1 4 0 1 0 0 15 1
S 0 0 1 1 1 11 0 0
W 0 0 0 1 0 1 0 0
---------------------------------------------------------------------------------------------
这个是我的结果:
A B H M N P S W0 0 0 0 0 0 0 10 0 0 1 0 0 0 00 0 0 0 0 5 0 00 1 0 1 0 0 0 00 0 0 0 1 2 0 01 4 0 1 0 0 15 10 0 1 1 1 11 0 00 0 0 1 0 1 0 0我现在竖的那一列字母不知道该怎么输出!
CREATE OR REPLACE PROCEDURE FINDPATH ISTEMP_DEPARTURE VARCHAR(300);TEMP_DESTINATION VARCHAR(300);TEMP_PRINT VARCHAR(300);TEMP_DISPLAY VARCHAR(300);TEMP_VAR NUMBER;CURSOR P IS SELECT DISTINCT DEPARTURE FROM TRIPLEG;CURSOR Q IS SELECT DISTINCT DESTINATION FROM TRIPLEG;CURSOR R IS SELECT DISTINCT SUBSTR(DEPARTURE,1,1) FROM TRIPLEG ORDER BY SUBSTR(DEPARTURE,1,1) ASC;
BEGINOPEN R;
TEMP_PRINT := ''; LOOP
FETCH R INTO TEMP_DISPLAY;
IF R%NOTFOUND THEN EXIT; END IF; TEMP_PRINT:=TEMP_PRINT||' '||TEMP_DISPLAY;
END LOOP;
TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);
CLOSE R;OPEN P;LOOP
FETCH P INTO TEMP_DEPARTURE;
IF P%NOTFOUND THEN EXIT; END IF;
OPEN Q; TEMP_PRINT := '';
LOOP
FETCH Q INTO TEMP_DESTINATION;
IF Q%NOTFOUND THEN EXIT; END IF; SELECT COUNT(*) INTO TEMP_VAR FROM TRIPLEG WHERE DESTINATION=TEMP_DESTINATION
AND DEPARTURE=TEMP_DEPARTURE; TEMP_PRINT:=TEMP_PRINT||' '||TEMP_VAR;
END LOOP;
CLOSE Q;
TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);END LOOP;
CLOSE P;END FINDPATH;/这个是我的代码,请高手帮我看看,到底在哪里加些什么!
A B H M N P S W
A 0 0 0 0 0 0 0 1
B 0 0 0 1 0 0 0 0
H 0 0 0 0 0 5 0 0
M 0 1 0 1 0 0 0 0
N 0 0 0 0 1 2 0 0
P 1 4 0 1 0 0 15 1
S 0 0 1 1 1 11 0 0
W 0 0 0 1 0 1 0 0
---------------------------------------------------------------------------------------------
这个是我的结果:
A B H M N P S W0 0 0 0 0 0 0 10 0 0 1 0 0 0 00 0 0 0 0 5 0 00 1 0 1 0 0 0 00 0 0 0 1 2 0 01 4 0 1 0 0 15 10 0 1 1 1 11 0 00 0 0 1 0 1 0 0我现在竖的那一列字母不知道该怎么输出!
CREATE OR REPLACE PROCEDURE FINDPATH ISTEMP_DEPARTURE VARCHAR(300);TEMP_DESTINATION VARCHAR(300);TEMP_PRINT VARCHAR(300);TEMP_DISPLAY VARCHAR(300);TEMP_VAR NUMBER;CURSOR P IS SELECT DISTINCT DEPARTURE FROM TRIPLEG;CURSOR Q IS SELECT DISTINCT DESTINATION FROM TRIPLEG;CURSOR R IS SELECT DISTINCT SUBSTR(DEPARTURE,1,1) FROM TRIPLEG ORDER BY SUBSTR(DEPARTURE,1,1) ASC;
BEGINOPEN R;
TEMP_PRINT := ''; LOOP
FETCH R INTO TEMP_DISPLAY;
IF R%NOTFOUND THEN EXIT; END IF; TEMP_PRINT:=TEMP_PRINT||' '||TEMP_DISPLAY;
END LOOP;
TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);
CLOSE R;OPEN P;LOOP
FETCH P INTO TEMP_DEPARTURE;
IF P%NOTFOUND THEN EXIT; END IF;
OPEN Q; TEMP_PRINT := '';
LOOP
FETCH Q INTO TEMP_DESTINATION;
IF Q%NOTFOUND THEN EXIT; END IF; SELECT COUNT(*) INTO TEMP_VAR FROM TRIPLEG WHERE DESTINATION=TEMP_DESTINATION
AND DEPARTURE=TEMP_DEPARTURE; TEMP_PRINT:=TEMP_PRINT||' '||TEMP_VAR;
END LOOP;
CLOSE Q;
TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);END LOOP;
CLOSE P;END FINDPATH;/这个是我的代码,请高手帮我看看,到底在哪里加些什么!
--楼主试试,没测试过,修改地方主要是再遍历一次R,将每个字母放在第一列
--另外,修改了下你的格式,之前你的看起来不舒服,没有逻辑层次感CREATE OR REPLACE PROCEDURE FINDPATH
IS
TEMP_DEPARTURE VARCHAR(300);
TEMP_DESTINATION VARCHAR(300);
TEMP_PRINT VARCHAR(300);
TEMP_DISPLAY VARCHAR(300);
TEMP_VAR NUMBER;
CURSOR P IS SELECT DISTINCT DEPARTURE FROM TRIPLEG;
CURSOR Q IS SELECT DISTINCT DESTINATION FROM TRIPLEG;
CURSOR R IS SELECT DISTINCT SUBSTR(DEPARTURE,1,1) FROM TRIPLEG ORDER BY SUBSTR(DEPARTURE,1,1) ASC;
BEGIN
OPEN R;
TEMP_PRINT := ' '; --修改地方(1)
LOOP
FETCH R INTO TEMP_DISPLAY;
IF R%NOTFOUND THEN EXIT;
END IF;
TEMP_PRINT:=TEMP_PRINT||' '||TEMP_DISPLAY;
END LOOP; TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);
CLOSE R; OPEN R; --修改地方(2)最主要的
LOOP
FETCH R INTO TEMP_DISPLAY;
IF R%NOTFOUND THEN EXIT; END IF;
OPEN P;
LOOP
FETCH P INTO TEMP_DEPARTURE;
IF P%NOTFOUND THEN EXIT; END IF;
OPEN Q;
TEMP_PRINT := TEMP_DEPARTURE||''; --将字母放在第一列的第一个位置
LOOP
FETCH Q INTO TEMP_DESTINATION;
IF Q%NOTFOUND THEN EXIT; END IF;
SELECT COUNT(*) INTO TEMP_VAR FROM TRIPLEG WHERE DESTINATION=TEMP_DESTINATION AND DEPARTURE=TEMP_DEPARTURE;
TEMP_PRINT:=TEMP_PRINT||' '||TEMP_VAR;
END LOOP;
CLOSE Q;
TEMP_PRINT:=TEMP_PRINT||chr(10);
DBMS_OUTPUT.PUT_LINE(TEMP_PRINT);
END LOOP;
END LOOP;
CLOSE P;
CLOSE R; --最后关闭REND FINDPATH;
TEMP_PRINT := TEMP_DEPARTURE||''; --将字母放在第一列的第一个位置
改为:
TEMP_PRINT := TEMP_DISPLAY||''; --将字母放在第一列的第一个位置