菜鳥(niǎo)學(xué)oracle

2024-08-29 13:33:28

字體：大中小

供稿：網(wǎng)友

現(xiàn)在用的庫(kù)里有一個(gè)MV是統(tǒng)計(jì)按小時(shí)的訪問(wèn)量的,數(shù)據(jù)如下代碼:



SQL> select * from mv_time_stat;

      TIME        CNT

                                                                                            ---------- ----------

         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.

某年月日,突發(fā)奇想,打算在sqlplus下面按上面的數(shù)據(jù)畫(huà)個(gè)直方圖出來(lái),首先寫(xiě)了個(gè)豎列的(因?yàn)楹脤?xiě)):

代碼:
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('> 'CNT,CEIL(CNT/A.TOTAL*300)+ LENGTH(TO_CHAR(CNT))+1,'-'),1,35) AS " Count per hour" 3 FROM MV_TIME_STAT,(SELECT SUM(CNT) 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.

然后又想,怎么樣把它橫過(guò)來(lái),到網(wǎng)上查資料,又突擊學(xué)習(xí)了下分析函數(shù),寫(xiě)了一個(gè)過(guò)程是這個(gè)樣子的:

代碼:
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(CNT) 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(CNT-MAX_ONE+(I-1)*MAX_ONE/HEIGHT),-1, DECODE(SIGN(CNT-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_LINESTR_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 0--------------------------------------------------------------------------> 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 PL/SQL procedure sUCcessfully completed.