原型:
package app_calculate is
procedure running_total(event varchar2,
source_field varchar2,
total_field varchar2);
-- Maintains a running total field.
-- Arguments
-- event Name of trigger event
-- source_field Name of field to total
-- total_field Name of field to contain running total
end app_calculate;
package body app_calculate is
procedure running_total(event varchar2,
source_field varchar2,
total_field varchar2) is
Last_Val_Field VARCHAR2(61) := Source_Field || '_RTOT_OLD';
DB_Source_Field VARCHAR2(61) := Source_Field || '_RTOT_DB';
DB_Total_Field VARCHAR2(61) := Total_Field || '_RTOT_DB';
Last_Value NUMBER;
Source_Value NUMBER;
DB_Source_Value NUMBER;
Total_Value NUMBER := NVL(name_in(Total_Field),0);
DB_Total_Value NUMBER := NVL(name_in(DB_Total_Field),0);
New_Total NUMBER;
Form_Id FormModule;
begin
if (event != 'WHEN-CLEAR-BLOCK') then
Last_Value := NVL(name_in(Last_Val_Field),0);
Source_Value := NVL(name_in(Source_Field),0);
DB_Source_Value := NVL(name_in(DB_Source_Field),0);
end if;
if (event = 'POST-QUERY') then
copy(to_char(Source_Value), DB_Source_Field);
copy(to_char(Source_Value), Last_Val_Field);
elsif (event = 'WHEN-CLEAR-BLOCK') then
if (Total_Value != DB_Total_Value) then
copy(to_char(DB_Total_Value), Total_Field);
end if;
elsif (event = 'WHEN-VALIDATE-ITEM') then
if (Source_Value != Last_Value) then
New_Total := Total_Value - Last_Value + Source_Value;
copy(to_char(New_Total), Total_Field);
copy(to_char(Source_Value), Last_Val_Field);
end if;
elsif (event = 'KEY-DELREC') then
if (Last_Value != 0) then
New_Total := Total_Value - Last_Value;
copy(to_char(New_Total), Total_Field);
end if;
copy(to_char(Source_Value), Last_Val_Field);
ELSIF (event IN ('POST-INSERT', 'POST-UPDATE')) THEN
if (Source_Value != DB_Source_Value) then
copy(to_char(DB_Source_Value), Last_Val_Field);
copy(to_char(Source_Value), DB_Source_Field);
end if;
ELSIF (event = 'PRE-RECORD') THEN
if (Source_Value != Last_Value) THEN
Form_Id := Find_Form(
Get_Application_Property(Current_Form_Name));
if (Name_In( 'GLOBAL.RTOT_'
||substr(source_field,1,least(instr(source_field,'.') - 1, 15))
||to_char(Form_id.id))
= 'N'
) THEN
copy(to_char(Last_Value), DB_Source_Field);
end if;
copy(to_char(Source_Value), Last_Val_Field);
end if;
ELSIF (event = 'PRE-COMMIT') THEN
Form_Id := Find_Form(
Get_Application_Property(Current_Form_Name));
copy('N', 'GLOBAL.RTOT_'
||substr(source_field,1,least(instr(source_field,'.') -
1, 15))
||to_char(Form_id.id));
ELSIF (event = 'POST-FORMS-COMMIT') THEN
Form_Id := Find_Form(
Get_Application_Property(Current_Form_Name));
copy('Y', 'GLOBAL.RTOT_'
||substr(source_field,1,least(instr(source_field,'.') -
1, 15))
||to_char(Form_id.id));
if (Total_Value != DB_Total_Value) then
copy(to_char(Total_Value), DB_Total_Field);
end if;
ELSIF (event = 'UNDELETE') THEN
New_Total := Total_Value + Source_Value;
copy(to_char(New_Total), Total_Field);
copy(to_char(Source_Value), Last_Val_Field);
ELSIF (event = 'KEY-DUPREC') THEN
New_Total := Total_Value + Source_Value;
copy(to_char(New_Total), Total_Field);
copy(to_char(Source_Value), Last_Val_Field);
copy(null, DB_Source_Field);
ELSIF (event = 'KEY-CLRREC') THEN
IF (Last_Value != DB_Source_Value) THEN
New_Total := Total_Value - Last_Value + DB_Source_Value;
copy(to_char(New_Total), Total_Field);
END IF;
else
message('Invalid event '||event||' in app_calulate.running_total');
end if;
end running_total;
end app_calculate;
用法:
假如要统计和的block为TRANSACTION,ITEM为AMOUNT。用于保存统计结果值得BLOCK为DISPLAYBLOCK,ITEM为SUM。
1、 在TRANSACTION新建2个非数据库项,分别命名为'AMOUNT_RTOT_OLD','AMOUNT_RTOT_DB'。
2、在DISPLAYBLOCK新建2个非数据库项,分别命名 'SUM_RTOT_OLD','SUM_RTOT_DB'。
3、新建类似如下的过程
PROCEDURE TOTAL(evt varchar2) is
begin
APP_CALCULATE.RUNNING_TOTAL(evt,
'TRANSACTION.AMOUNT',
DISPLAYBLOCK.SUM');
end;
3、在AMOUNT下添加WHEN-VALIDATE-ITEM触发器如下:
TOTAL('WHEN-VALIDATE-ITEM');
当然,在其他相应的地方也需要调用,才能充分发挥它的作用。
本文作者:网友 来源:网络