Oracle Form按客户统计Dr/Cr金额仅返回单条数据问题排查

• Oracle Form：12
• Weblogic：12.0.01
• Oracle DB：19c

代码位于Oracle Form的When new form instance触发器中

declare
CURSOR c1 IS

SELECT id, Customer, SUM(DEBIT ) as Debit, abs(SUM(Payment )) as PAYMENT
FROM
(
SELECT
Cust_id as id,
cust_name as customer,
OPENING_BLNC as Debit,
0 as PAYMENT FROM Customer
union all
select
c.Cust_id as id,
c.cust_name as customer,
i.total_amount as Debit,
0 as PAYMENT
FROM Customer c,transaction t,invoice i where t.tran_id=i.inv_tran_id and c.cust_id=t.cust_id
union all
select
c.Cust_id as id,
c.cust_name as customer,
0 as Debit,
a.cr as PAYMENT
FROM Customer c,accounts a where c.cust_id=a.cust_id
)
GROUP BY id, Customer order by id desc ;

begin

FOR lop1 IN c1
loop
:block28.id:=lop1.id;
:block28.customer:= lop1.customer;
:block28.debit:=lop1.debit;
:block28.payment:= lop1.payment;
END LOOP;

end;

ID Customer Total dues Total Payment

1 KARIMULLAH 68697.04 40000

ID Customer Total dues Total Payment

3 AZAD MEDIICE 21519.62 5000

2 NEW KARIM AGENCY 0 10000

1 KARIMULLAH 68697.04 40000

现有代码意图按客户统计借方（Dr）和贷方（Cr）金额总和，但当前仅返回单条客户数据，与期望的三条客户统计结果不符，需排查并修正代码以实现正确输出。

1. 核心问题：Form块未插入新行 #

原代码循环中只是反复给:block28的字段赋值，没有在块中创建新行，导致最终仅显示循环的最后一条记录。Oracle Form要展示多条数据，必须在每次循环时插入新行。

2. 修正后的完整代码 #

declare
CURSOR c1 IS
SELECT id, Customer, SUM(DEBIT ) as Debit, abs(SUM(Payment )) as PAYMENT
FROM
(
SELECT
Cust_id as id,
cust_name as customer,
OPENING_BLNC as Debit,
0 as PAYMENT FROM Customer
union all
select
c.Cust_id as id,
c.cust_name as customer,
i.total_amount as Debit,
0 as PAYMENT
FROM Customer c,transaction t,invoice i where t.tran_id=i.inv_tran_id and c.cust_id=t.cust_id
union all
select
c.Cust_id as id,
c.cust_name as customer,
0 as Debit,
a.cr as PAYMENT
FROM Customer c,accounts a where c.cust_id=a.cust_id
)
GROUP BY id, Customer order by id desc ;

begin
-- 清空块中原有数据，避免旧数据干扰
GO_BLOCK('BLOCK28');
CLEAR_BLOCK(NO_VALIDATE);

FOR lop1 IN c1
loop
  -- 插入新行，准备写入当前客户数据
  CREATE_RECORD;
  :block28.id:=lop1.id;
  :block28.customer:= lop1.customer;
  :block28.debit:=lop1.debit;
  :block28.payment:= lop1.payment;
END LOOP;

-- 导航到第一条记录，确保完整展示所有数据
FIRST_RECORD;
end;

3. 额外优化建议 #

• 原SQL使用的是旧版逗号连接语法，建议改用ANSI标准JOIN语法，提升代码可读性和维护性：

SELECT id, Customer, SUM(DEBIT ) as Debit, abs(SUM(Payment )) as PAYMENT
FROM
(
SELECT
Cust_id as id,
cust_name as customer,
OPENING_BLNC as Debit,
0 as PAYMENT FROM Customer
union all
select
c.Cust_id as id,
c.cust_name as customer,
i.total_amount as Debit,
0 as PAYMENT
FROM Customer c
JOIN transaction t ON c.cust_id = t.cust_id
JOIN invoice i ON t.tran_id = i.inv_tran_id
union all
select
c.Cust_id as id,
c.cust_name as customer,
0 as Debit,
a.cr as PAYMENT
FROM Customer c
JOIN accounts a ON c.cust_id = a.cust_id
)
GROUP BY id, Customer 
order by id desc ;

• 提前确认Customer表中存在ID为2、3的客户，且对应交易/账户数据无误，避免SQL逻辑过滤掉目标数据。

内容的提问来源于stack exchange，提问作者Zeeshan Fazal