菜鸟学oracle：用PLSQL画直方图 电脑资料

菜鸟学oracle：用PLSQL画直方图 电脑资料 现在用的库里有一个MV是统计按小时的访问量的,数据如下代码: SQL select * from mv_time_stat; TIME T - 0 187 1 51 2 34 3 19 4 19 5 20 6 50 7 107 8 682 9 1342 10 1854 11 1292 12 1416 13 1180 14 1217 15 1573 16 1785 17 1469 18 1892 19 1907 20 1602 21 1540 22 1013 23 441 24 rows selected. - 某年月日,突发奇想,打算在sqlplus下面按上面的数据画个直方图出来,首先写了个竖列的(因为好写): 代码:- SQL COL Time FORMAT A7 SQL SELECT LPAD(DECODE(TIME,TO_CHAR(SYSDATE,HH24),NOW: |TO_CHAR(TIME),TO_CHAR(TIME),7, ) AS Time, 2 SUBSTR(|LPAD( |T,CEIL(T/A.TOTAL*300)+LENGTH(TO_CHAR(T)+1,-),1,35) AS Count per hour 3 FROM MV_TIME_STAT,(SELECT SUM(T) AS TOTAL FROM MV_TIME_STAT) A; COL Time CLEAR Time Count per hour - - 0 |- 187 1 | 51 2 | 34 3 | 19 4 | 19 5 | 20 6 | 50 7 |- 107 NOW: 8 |- 682 9 |- 1342 10 |- 1854 11 |- 1292 12 |- 1416 13 |- 1180 14 |- 1217 15 |- 1573 16 |- 1785 17 |- 1469 18 |- 1892 19 |- 1907 20 |- 1602 21 |- 1540 22 |- 1013 23 |- 441 24 rows selected.- 然后又想,怎么样把它横过来,到网上查资料,又突击学习了下分析函数,写了一个过程是这个样子的: 代码: - CREATE OR REPLACE PROCEDURE HISTOGRAPH(HEIGHT NUMBER DEFAULT 15) AS MAX_ONE NUMBER; STR_LINE VARCHAR(120); STR_TEMP VARCHAR(120); I NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE(LPAD(,14, ); SELECT MAX(T) INTO MAX_ONE FROM MV_TIME_STAT; FOR I IN 1 . HEIGHT+1 LOOP STR_LINE:=LPAD(TO_CHAR(MAX_ONE-(I-1)*MAX_ONE/HEIGHT,99999)|-|TO_CHAR(MAX_ONE-(I-2)*MAX_ONE/HEIGHT,99999)|,14,0); SELECT MAX(SYS_CONNECT_BY_PATH( DECODE(SIGN(T-MAX_ONE+(I-1)*MAX_ONE/HEIGHT),-1, DECODE(SIGN(T-MAX_ONE+(I-2)*MAX_ONE/HEIGHT),-1,*), |), ) INTO STR_TEMP FROM MV_TIME_STAT START WITH TIME=0 CONNECT BY PRIOR TIME=TIME-1; STR_LINE:=STR_LINE|STR_TEMP; DBMS_OUTPUT.PUT_LINE(REPLACE (STR_LINE, ); END LOOP; SELECT MAX(SYS_CONNECT_BY_PATH(TO_CHAR(TIME,00),) INTO STR_TEMP FROM MV_TIME_STAT START WITH TIME=0 CONNECT BY PRIOR TIME=TIME-1; DBMS_OUTPUT.PUT_LINE(LPAD(0,14, )|LPAD(,LENGTH(REPLACE(STR_TEMP, )+3,-); DBMS_OUTPUT.PUT_LINE(REPLACE(LPAD(,14, )|REPLACE(STR_TEMP, ), ); END; / Procedure created. SQL exec histograph; 1907- 2034| | 1780- 1907| | | | | 1653- 1780| | | | | 1526- 1653| | | | | | | | 1398- 1526| | | | | | | | | | 1271- 1398| | | | | | | | | | | | 1144- 1271| | | | | | | | | | | | | | 1017- 1144| | | | | | | | | | | | | | 890- 1017| | | | | | | | | | | | | | | 763- 890| | | | | | | | | | | | | | | 636- 763| | | | | | | | | | | | | | | | 509- 636| | | | | | | | | | | | | | | | 381- 509| | | | | | | | | | | | | | | | | 254- 381| | | | | | | | | | | | | | | | | 127- 254| | | | | | | | | | | | | | | | | | 0- 127| | | | | | | | | | | | | | | | | |