如何将结果转储到表中 [英] how to dump the result in a table

问题描述

CREATE TABLE TEST_SUM
(
USERID VARCHAR2(10),
DEBIT_AMT NUMBER(19,4),
CREDIT_AMT NUMBER(19,4),
BALANCE_AMT NUMBER(19,4)
)

注意： 表中的金额字段是动态的，即这里只有3个amunt字段但是可以有任意数量的 NUMBER 字段。

note: the amount fields in the table is dynamic i.e. here there are only 3 amunt fields but there can be any number of "NUMBER" fields.

INSERT ALL  
INTO TEST_SUM VALUES ('001',100,200,300)
INTO TEST_SUM VALUES ('005',500,200,700)
INTO TEST_SUM VALUES ('006',300,200,500)
INTO TEST_SUM VALUES ('003',400,300,700)
SELECT * FROM DUAL;

T他在脚本下方通过光标给出了金额字段的总和。

The below script gives the sum of the amount fields via cursor.

CREATE OR REPLACE PROCEDURE GET_SUM_AMT(VTBL VARCHAR2) AS
TBLCURSOR    SYS_REFCURSOR;
TBLSQLSTR    VARCHAR2(1000);
IMPORTEDROWS VARCHAR2(1000);
TOTAL_AMT VARCHAR2(1000);
BEGIN
TBLSQLSTR := 'SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME= :VAL_BND
              AND
                (COLUMN_NAME LIKE UPPER(''%AMT%'') OR COLUMN_NAME LIKE UPPER(''%AMOUNT%''))
              AND
                DATA_TYPE = ''NUMBER''';
OPEN TBLCURSOR FOR TBLSQLSTR USING VTBL;
LOOP
    FETCH TBLCURSOR INTO IMPORTEDROWS;
    EXIT WHEN TBLCURSOR%NOTFOUND;
    EXECUTE IMMEDIATE 'SELECT SUM( '||IMPORTEDROWS||') FROM '||VTBL INTO TOTAL_AMT;
    DBMS_OUTPUT.PUT_LINE(''||IMPORTEDROWS||' = '||TOTAL_AMT);
END LOOP;
CLOSE TBLCURSOR;
EXCEPTION
WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,'AN ERROR WAS ENCOUNTERED - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

------------------------------------------------------------------------------------------
--  EXEC GET_SUM_AMT('TEST_SUM');
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

结果是：



PL / SQL块，以0.266秒执行。

DEBIT_AMT = 1300

CREDIT_AMT = 900

BALANCE_AMT = 2200

总执行时间1.066秒。

------- *************** * ---------------



但我想要结果表格形式与我们从此查询得到的结果相同：

SELECT Sum（DEBIT_AMT），Sum（CREDIT_AMT），Sum（BALANCE_AMT）FROM TEST_SUM;

。我怎么能这样做？

The result is:

PL/SQL block, executed in 0.266 sec.
DEBIT_AMT = 1300
CREDIT_AMT = 900
BALANCE_AMT = 2200
Total execution time 1.066 sec.
-------****************---------------

But I want the result in tabular form same as we get the result from this query:
SELECT Sum(DEBIT_AMT),Sum(CREDIT_AMT),Sum(BALANCE_AMT) FROM TEST_SUM;
. How can I do it?

推荐答案

我想你忘记了 GROUP BY 声明。< br $>

I think you forgot about GROUP BY statement.

SELECT UserId, Sum(DEBIT_AMT) AS SumOfDebit,Sum(CREDIT_AMT) AS SumOfCredit, Sum(BALANCE_AMT) AS SumOfBalance
FROM TEST_SUM
GROUP BY UserId

更多信息：

GROUP BY子句 [ ^ ]

HAVING子句 [ ^ ]

这篇关于如何将结果转储到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！