菜鸟学oracle

代码:

---


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.


---

代码:

---


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.


---