时间戳

首页 > 解决方案 > 在一个包中使用多个过程和多个游标

sql - 在一个包中使用多个过程和多个游标

问题描述

我创建了一个包含两个过程和两个游标的包，但是在执行过程时，它执行成功，但同一记录执行了多次并发生缓冲区溢出。

我还尝试从光标中删除循环，但对于 1 条记录会很好，而对于多条记录，它不会按预期工作。

预期的

我只需要从我要多次执行同一记录的过程中删除同一记录的多次执行

对于单个过程和单个游标，它工作正常，但对于多个游标和多个过程，我在这里遇到问题，这也导致缓冲区溢出，我需要不同的记录

有没有其他方法可以解决问题？

CREATE OR REPLACE PACKAGE test.report AS
    PROCEDURE distribution (
        code_in   IN             user.test.code%TYPE,
        fromdate      date,
        todate          date
    );

    PROCEDURE tdvalue (
    id   IN          user.test.custid%TYPE
    );

END report;
/

包体

CREATE OR REPLACE PACKAGE BODY test.report as

----------VARIABLE DECLARATION----------------

    code_in             user.test.code%TYPE;
    custidin                user.test.custid%TYPE;
    fromdate                 DATE;
    todate                   DATE;
    diff                    number(17,2);
---------------CURSOR DECLARATION--------------


CURSOR td_data(code_in  user.test.code%TYPE,
                fromdate date,
                todate date
) IS

    ( SELECT
        test.code,
        COUNT(test.code) AS count,
        SUM(test2.Deposit_amount) AS total,
        test.currency
    FROM
        user.test2
        JOIN user.test ON test2.acid = test.acid
    WHERE
        user.test2.open_effective_date BETWEEN TO_DATE(fromdate, 'dd-mm-yyyy') AND TO_DATE(todate, 'dd-mm-yyyy')
        and
        user.test.code = code_in
    GROUP BY
        test.code,test.currency
    );

    td__data        td_data%rowtype;


CURSOR C_DATA(custidin   user.test.custid%TYPE)  IS   SELECT
            test.custid,
            test2.id,
            TO_DATE(test2.initial_date, 'dd-mm-yyyy') - TO_DATE(test2.end_date, 'dd-mm-yyyy') AS noofdays,
            round(((test2.deposit_amount *((TO_DATE(test2.initial_date, 'dd-mm-yyyy') - TO_DATE(test2.end_date, 'dd-mm-yyyy'
            )) / 365) * test4.interest_rate) / 100), 2) + test2.deposit_amount AS calculated_amount,
            SUM(test.flow_amt) + test2.deposit_amount AS system_amount
        FROM
            user.test
            JOIN user.test2 ON test3.entity_id = test2.id
        WHERE
            test.custid = custidin
        GROUP BY
            test.custid,
            test2.id;

    c__data         c_data%ROWTYPE;


PROCEDURE distribution 
(
    code_in   IN             user.test.code%TYPE,
    fromdate in date,
    todate in  date
)

AS
BEGIN
    OPEN td_data(code_in,fromdate,todate);
    loop
        FETCH td_data INTO td__data;
        dbms_output.put_line(td__data.code
                             || '             '
                             || td__data.count
                             || '                '
                             ||td__data.currency
                             ||' '
                             ||td__data.total
                             );
            end loop;                
    CLOSE td_data;
END distribution;

PROCEDURE tdvalue (
    custidin   IN          user.test.custid%TYPE
    )
AS
BEGIN   
    open c_data(custidin);
    fetch c_data into c__data;
    loop
    diff:= c__data.calculated_amount- c__data.system_amount;        
        dbms_output.put_line(c__data.custid
                             || '   '
                             || c__data.noofdays
                             || '          '
                             || c__data.end_date
                             || '               '
                             || c__data.initial_date
                             || '                 '
                             || c__data.calculated_amount
                             ||'     '
                             ||diff
                             );
    end loop;
    close c_data;
END tdvalue;
END report;
/

跑步

ALTER SESSION set nls_date_format='dd-mm-yyyy';
SET SERVEROUTPUT ON;

EXEC REPORT.DISTRIBUTION('872328','01-02-2016','08-02-2019');
/
EXEC REPORT.tdvalue('S9292879383SS53');

标签： sqloracleplsqldbms-output

解决方案

当通过 DBMS_OUTPUT 显示的总字节数超过服务器输出缓冲区的大小时，会发生缓冲区溢出 - ORU-10027。默认只有 20000 字节（谁知道为什么？）。由于您启用serveroutput. 显然，一条记录少于 2000 条，只有在运行多条记录时才会达到该限制。

要解决这个问题，试试这个

SET SERVEROUTPUT ON size unlimited

它实际上不是无限的，但上限是 PGA 限制（会话内存），您真的不应该使用 DBMS_OUTPUT 达到该限制。除了其他任何东西，谁会阅读所有这些内容？

因此，正如@piezol 指出的那样，您的代码的另一个问题是您的循环没有退出点。您应该测试 FETCH 是否实际获取任何内容，如果没有则退出：

loop
    FETCH td_data INTO td__data;
    exit when td_data%notfound; 

    dbms_output.put_line(td__data.code
                         || '             '
                         || td__data.count
                         || '                '
                         ||td__data.currency
                         ||' '
                         ||td__data.total
                         );
 end loop;

记住这样做只是隐式游标和循环游标优于显式游标的原因之一。

第二个游标循环更糟糕，因为它不仅没有存在点，而且 fetch在循环之外。这就是为什么您为同一记录重复输出的原因。

所以让我们重写这个......

open c_data(custidin);
fetch c_data into c__data;  -- should be inside 
loop
diff:= c__data.calculated_amount- c__data.system_amount;

…作为循环的光标：

PROCEDURE tdvalue (
    custidin   IN          user.test.custid%TYPE
    )
AS
BEGIN   
    for c__data in c_data(custidin)
    loop
        diff:= c__data.calculated_amount- c__data.system_amount;        
        dbms_output.put_line(c__data.custid
                             || '   '
                             || c__data.noofdays
                             || '          '
                             || c__data.end_date
                             || '               '
                             || c__data.initial_date
                             || '                 '
                             || c__data.calculated_amount
                             ||'     '
                             ||diff
                             );
    end loop;
END tdvalue;

无需 OPEN、CLOSE 或 FETCH，也无需检查游标何时耗尽。

推荐阅读