根据标题类型显示内容

Question

I am new to SQL. My doubt is how to display the output inside the procedure like this.
I have various databases and its type and the current size all in one table 'predict_storage'
I want to display the output as:

    UAT:
        TMAP: 100G
        TCIG: 200G

    QA:
        QMAP: 100G
        QCIG: 200G

    DR:
        DRMAP: 100G
        DRCIG: 200G

其中，UAT是数据库类型，TMAP、TCIG是数据库，当前大小为100g。因此，我希望输出像这样根据数据库类型进行分类。

代码如下：

    CREATE OR REPLACE PROCEDURE test11 IS

        db_original_name_var varchar2(30);
        db_type varchar2(20);
        db_name varchar2(40);
        Used_space_var NUMBER;

        MAX_Used_space_monthvar NUMBER;
        RATE NUMBER;
        avg_space_future_min NUMBER;
        avg_space_future_max NUMBER;
        avgsp NUMBER;
        avg_space_3m NUMBER;
        avg_space_6m Number;
        avg_space_yr NUMBER;

        CURSOR db_list_cur is
        select original_db_name,database_type
        from database_list;

    Begin
      open db_list_cur;
      LOOP
      fetch db_list_cur
      into db_original_name_var,db_type;
     EXIT WHEN db_list_cur%NOTFOUND;

           select substr(avg(ave_used_space),0,6) INTO Used_space_var from month_space where db_original_name_var=database_name;
          select substr(max(ave_used_space),0,6) INTO MAX_Used_space_monthvar from month_space where db_original_name_var=database_name;

       --rate calc
     RATE := (MAX_Used_space_monthvar-Used_space_var)/Used_space_var;

     avg_space_future_max:=(Used_space_var)+(Used_space_var* RATE);
    avg_space_future_min:=(Used_space_var)-(Used_space_var* RATE);
    avgsp := (avg_space_future_max + avg_space_future_min)/2;

    avg_space_3m :=(avgsp)+(avgsp* rate* 3);
    avg_space_6m :=(avgsp)+(avgsp* rate* 6);
    avg_space_yr :=(avgsp)+(avgsp* rate* 12);

    insert into Predict_report
    (
    database_name ,
    Grwoth_rate ,
    Current_AVERAGE,
    Space_3mn,
    SPACE_6mn,
    Space_yr,
    database_type
    )
    values
    (
    db_original_name_var,
    RATE,
    round(avgsp,3),
    round(avg_space_3m,3),
    round(avg_space_6m,3),
    round(avg_space_yr,3),
    db_type
    );
    commit;

        loop
        dbms_output.put_line(db_type||':');
        select database_name into db_name from predict_report  where database_type=db_type;
        dbms_output.put_line(db_name);
        END LOOP;

       END LOOP;
       close db_list_cur;


    END;
    /

我拥有的表是1)存储信息

    DB_ID               NUMBER(38)
    DATABASE_NAME       VARCHAR2(50)
    DATABASE_SIZE       NUMBER
    TIME_STAMP          DATE
    FREE_SPACE          VARCHAR2(50)
    DATA_LINK_NAME      VARCHAR2(50)
    CUSTOMER_SPACE      NUMBER
    DATABASE_TYPE       VARCHAR2(30)
    USED_SPACE          NUMBER

2) Database_list

    DB_ID               NUMBER
    DB_NAME             VARCHAR2(50)
    DATALINK_NAME       VARCHAR2(50)
    DATABASE_TYPE       VARCHAR2(20)
    ORIGINAL_DB_NAME    VARCHAR2(30)

3)预测报告

    DATABASE_NAME       NOT NULL VARCHAR2(50)
    GRWOTH_RATE         NUMBER
    CURRENT_AVERAGE     NUMBER
    SPACE_3MN           NUMBER
    SPACE_6MN           NUMBER
    SPACE_YR            NUMBER
    DATABASE_TYPE       VARCHAR2(20)

4) Month_space

    DATABASE_NAME       NOT NULL VARCHAR2(50)
    GRWOTH_RATE         NUMBER
    CURRENT_AVERAGE     NUMBER
    SPACE_3MN           NUMBER
    SPACE_6MN           NUMBER
    SPACE_YR            NUMBER
    DATABASE_TYPE       VARCHAR2(20)

-- sql文件是

        set echo off numf 999G999G999G999 lin 32000 trims on pages 50000 head on feed off     markup html off
    alter session set nls_numeric_characters='.''' nls_date_format='Day DD. Month, YYYY';
    spool /tmp/report.html
--prompt To: jialin.zhu@synchronoss.com
    prompt TO: varun.jain@synchronoss.com
    prompt cc: varun.jain@synchronoss.com
    prompt From: varun.jain@synchronoss.com
    prompt Subject: Daily space report
    prompt Content-type: text/html
    prompt MIME-Version: 1.0
    set markup html on entmap off table 'BORDER="2" BGCOLOR="white" FONTCOLOR="black"'
    prompt <i>Good morning, </i>
    prompt <i>Here is the Space report as on &_DATE</i>
    prompt <i>Kind Regards, </i>

------------------------------------------------------------------------------------------------------------------------------------------------
    prompt <br/><h3>Database Space Report</h3>

    set serveroutput on
    CLEAR COLUMNS
    Set HEADING ON
    Set COLSEP ,
    SET PAGESIZE 20000
    SET timing off feedback off verify off echo off

    prompt <br/><h3> Environment Space Summary</h3>
    column database_type heading 'Database Type'
    column Sum(current_average) format 9999 HEADING 'Total Space in GB'
    select database_type,Sum(current_average) from predict_report group by database_type;
--------------------------------------------------------------------------------------------------------------------------------------------------

    prompt <br/><h3> DR database Summary</h3>
    COLUMN ('THETOTALSPACEINDRDATABASES:'||SUM(CURRENT_AVERAGE)||''||'GB') format 9999 heading     'total Space in DR database'
    select ('The total space in DR databases :' ||Sum(current_average)||' '||'GB') from     predict_report where DATABASE_TYPE not in ('UAT','QA');
------------------------------------------------------------------------------------------------------------------------------------------
    prompt <br/><h3> databases  Summary</h3>
    column database_name format a30 heading ' DATABASE NAME'
    column round(GRWOTH_RATE*100,0)||'%'  heading 'GROWTH RATE'
    COLUMN CURRENT_AVERAGE FORMAT 9999 HEADING 'TODAYS SPACE in GB'
    COLUMN SPACE_3MN FORMAT 9999 HEADING 'SPACE AFT 3 MONTHS in GB'
    COLUMN SPACE_6MN FORMAT 9999 HEADING 'SPACE AFT 6 MONTHS in GB'
    COLUMN SPACE_YR FORMAT 9999 HEADING 'SPACE AFT A YEAR in GB'
    truncate table Predict_report;
    set serveroutput on
    exec report;
    SELECT         database_name,round(GRWOTH_RATE*100,0)||'%',current_average,space_3mn,space_6mn,space_yr     FROM Predict_report;

---------------------------------------------------------------------------------
    prompt <br/><h3>Database Space Summary</h3>
    column sum(current_average) FORMAT 9999 heading 'Total Space in GB'
    column sum(space_3mn) FORMAT 9999  heading 'Total Space in 3 months in GB'
    column sum(space_6mn) FORMAT 9999  heading 'Total Space in 6 months in GB'
    column sum(space_yr) FORMAT 9999 heading 'Total Space in 1 year in GB'
    select sum(current_average),sum(space_3mn),sum(space_6mn),sum(space_yr) from             Predict_report ;
    spool off;

---------to send the mail----------------

    host /usr/sbin/sendmail -t </tmp/report.html

收到的电子邮件中的输出如下所示(它在电子邮件中是表格形式)

DATABASE NAME   GROWTH RATE     TODAYS SPACE in GB  SPACE AFT 3 MONTHS in GB    SPACE AFT 6 MONTHS in GB    SPACE AFT A YEAR in GB 
CLNK    0%  199     200     200     202 
CCIG    0%  562     563     563     565 
DTXN    5%  330     377     424     518 
DCIG    0%  414     416     418     422 
QMAP    0%  16  16  17  17 
QLNP    0%  44  44  44  44 
QHTS    1%  32  32  33  34 
QFKP    1%  37  38  39  41 
QSAG    0%  168     169     170     172 
CSAG    0%  812     815     818     824 
LTATG   0%  25  25  25  25 
QCIG    0%  208     209     209     211 
TLNP    0%  341     341     341     341 
TMAP    0%  60  61  61  62 
TSAG    0%  223     226     228     

Answer 1

您现在拥有的用于输出的额外循环永远不会终止，因此执行该过程将显示为挂起，至少在dbms_output缓冲区空间耗尽之前是如此；并且不会检索当前值。而且它放在错误的位置，因为它会在每次循环时显示该类型的所有数据库的数据，所以您会得到重复的数据(充其量)。

尽管这看起来不是一种明智的方法，尤其是因为您假设该过程总是由可以显示输出并启用了输出的东西运行，但在close db_list_cur之后，您可以这样做

db_type := null;
for r in (select database_type, database_name, current_average
    from predict_report
    order by database_type, database_name)
loop
    if db_type is null or db_type != r.database_type then
        dbms_output.put_line(r.database_type || ':');
        db_type := r.database_type;
    end if;
    dbms_output.put_line(r.database_name ||' '||
        to_char(nvl(r.current_average, 0), '999990.00') ||'G');
end loop;

..。或者基于要插入的数据在主循环中执行，而不是在末尾查询表(因为它们应该匹配)；但是无论您使用循环还是使用简单得多的insert into predict_report select ...，这种方法都可以。

您不需要使用游标来计算统计数据或填充表；您实际上根本不需要PL/SQL。您可以将这两个表连接在一起，并根据返回值计算出数字。类似下面这样的代码就可以做到：

select database_name,
    rate as grwoth_rate,
    avg_used_space as current_average,
    avg_used_space + (avg_used_space * rate * 3) as space3mn,
    avg_used_space + (avg_used_space * rate * 6) as space6mn,
    avg_used_space + (avg_used_space * rate * 12) as spaceyr,
database_type
from (
    select database_name,
        database_type,
        avg_used_space,
        max_used_space,
        (max_used_space - avg_used_space)/avg_used_space as rate
    from (
        select dl.original_db_name as database_name,
            dl.database_type,
            trunc(avg(ave_used_space), 2) as avg_used_space,
            trunc(max(ave_used_space), 2) as max_used_space
        from database_list dl
        join month_space ms on ms.database_name = dl.original_db_name
        group by dl.original_db_name,
            dl.database_type
    )
);

如果您只是在过程中生成输出，那么您可以使用它来插入到新表中，尽管您甚至不确定您是否真的需要这样做-不清楚是否是这样，尽管您似乎不想显示您正在计算的所有统计数据。

不过，我也不确定你的统计数据有多大意义。您似乎正在计算任意几个月的增长率，然后通过将该增长率应用于数据库生命周期内的平均值而不是它的当前大小来预测未来的大小，您似乎没有收集-您有当前的平均值，这不是一回事。