转载自:http://blog.oracle.com.cn/html/86/t-126886.html
Change Notification
ORACLE提供的Databas Change Notification特性让我们可以通过Register的形式告诉数据库,
用户对某些表的内容改变感兴趣。最常用的地方是,在数据发生变化后,刷新Mid-Tier的数据
Cache.摘录一段文档内容
--------------------------------------------------------------------------------
Database Change Notification is a feature that enables client applications to register
queries with the database and receive notifications in response to DML or DDL
changes on the objects associated with the queries. The notifications are published by
the database when the DML or DDL transaction commits.
During registration, the application specifies a notification handler and associates a set
of interesting queries with the notification handler. A notification handler can be either
a server side PL/SQL procedure or a client side C callback. Registrations are created
on all objects referenced during the execution of the queries. The notification handler
is invoked when a transaction subsequently changes any of the registered objects and
commits.
--------------------------------------------------------------------------------
使用的对象以及关系
sys.chnf$_desc <-- sys.chnf$_tdesc <-- sys.chnf$_rdesc
SQL> desc sys.chnf$_desc
Element Type
---------------- ---------------------
REGISTRATION_ID NUMBER
TRANSACTION_ID RAW(8)
DBNAME VARCHAR2(30)
EVENT_TYPE NUMBER
NUMTABLES NUMBER
TABLE_DESC_ARRAY SYS.CHNF$_TDESC_ARRAY
SQL> desc sys.chnf$_tdesc
Element Type
-------------- ---------------------
OPFLAGS NUMBER
TABLE_NAME VARCHAR2(64)
NUMROWS NUMBER
ROW_DESC_ARRAY SYS.CHNF$_RDESC_ARRAY
SQL> desc sys.chnf$_rdesc
Element Type
------- --------------
OPFLAGS NUMBER
ROW_ID VARCHAR2(2000)
--------------------------------------------------------------------------------
下面提供一个简单的例子(首先满足系统权限要求)
CONNECT / AS SYSDBA;
GRANT CHANGE NOTIFICATION TO T2;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO T2;
-- 如果有必要,设置参数job_queue_processes为非零(默认就是非零)
ALTER SYSTEM SET job_queue_processes=4;
------------------------------------------------------------------------------
-- create sample data
------------------------------------------------------------------------------
create table ntfn_batch as select rb.* from rps_batch rb where rb.id = 2730006;
create table ntfn_trans as
select rt.*
from rps_transaction rt
where rt.original_rps_batch_id = 2730006
order by rt.id;
------------------------------------------------------------------------------
-- create message table
------------------------------------------------------------------------------
drop table ntfn_msg;
create table ntfn_msg
(
ora_trans raw(8),
tab_name varchar2(30),
row_id varchar2(2000),
dt date default sysdate
);
------------------------------------------------------------------------------
-- create procedure to process the callback when notification occurs
------------------------------------------------------------------------------
/*
You can create a PL/SQL stored procedure that the database server invokes in
response to a change to a registered object. The procedure that receives the notification
must have the following signature, where schema_name is the name of the database
schema and proc_name is the name of the stored procedure:
PROCEDURE schema_name.proc_name( ntfnds IN SYS.CHNF$_DESC
)
经过测试,这里的参数名称都必须一致 ntfnds
*/
create or replace procedure ntfs_callback(ntfnds in sys.chnf$_desc) is
l_event_type number;
l_ora_trans_id raw(8);
l_numtables number;
l_op_flag number;
l_tab_name varchar2(100);
l_numrows number;
l_row_id varchar2(2000);
begin
l_event_type := ntfnds.event_type;
l_ora_trans_id := ntfnds.transaction_id;
l_numtables := ntfnds.numtables;
-- only concern table change
if l_event_type = dbms_change_notification.EVENT_OBJCHANGE then
-- loop each table
for i in 1 .. l_numtables loop
--------------------------------------------------
-- usually we call UTL_HTTP to send a notification
-- to WEB Server, and here we only use some tables
--------------------------------------------------
l_op_flag := ntfnds.table_desc_array(i).opflags;
l_tab_name := ntfnds.table_desc_array(i).table_name;
-- get table name without schema name
l_tab_name := substr(l_tab_name, instr(l_tab_name, '.') + 1);
if bitand(l_op_flag, dbms_change_notification.ALL_ROWS) = 1 then
-- no certain rowid
insert into ntfn_msg
(ora_trans, tab_name)
values
(l_ora_trans_id, l_tab_name);
else
-- loop each row in this table
l_numrows := ntfnds.table_desc_array(i).numrows;
for j in 1 .. l_numrows loop
l_row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
insert into ntfn_msg
(ora_trans, tab_name, row_id)
values
(l_ora_trans_id, l_tab_name, l_row_id);
end loop; -- end loop row
end if;
end loop; -- end loop table
end if;
commit;
end;
/
------------------------------------------------------------------------------
-- procession for registration and deregister
------------------------------------------------------------------------------
-- registration
declare
l_reg_info sys.chnf$_reg_info;
l_reg_id number;
l_qosflags number;
l_temp number;
begin
l_qosflags := dbms_change_notification.QOS_ROWIDS;
l_reg_info := sys.chnf$_reg_info('ntfs_callback',l_qosflags,0,0,0);
l_reg_id := dbms_change_notification.NEW_REG_START(l_reg_info);
-- register tables
select count(1) into l_temp from ntfn_batch b, ntfn_trans t where b.id = t.original_rps_batch_id;
dbms_change_notification.REG_END;
end;
/
-- check register result
select r.regid,r.table_name from user_change_notification_regs r;
-- deregister
declare
l_reg_id number := 23;
begin
dbms_change_notification.DEREGISTER(l_reg_id);
end;
/
------------------------------------------------------------------------------
-- do some change and check result after commit
------------------------------------------------------------------------------
update ntfn_batch b set b.rps_application_id=b.rps_application_id where rownum = 1;
commit;
update ntfn_batch b set b.rps_application_id=b.rps_application_id;
commit;
update ntfn_trans t set t.t2_identifier = t.t2_identifier;
commit;
select to_char(m.dt, 'yyyy-mm-dd hh24:mi:ss') dt,
m.ora_trans,
m.tab_name,
m.row_id
from ntfn_msg m
order by 1, 3, 4;
SQL>
SQL> select to_char(m.dt, 'yyyy-mm-dd hh24:mi:ss') dt,
2 m.ora_trans,
3 m.tab_name,
4 m.row_id
5 from ntfn_msg m
6 order by 1, 3, 4;
DT ORA_TRANS TAB_NAME ROW_ID
------------------- ---------------- ------------------------------
--------------------------------------------------------------------------------
2007-10-26 17:23:16 08004100D20D0100 NTFN_BATCH AAFCIbAAHAAAM5AAAA
2007-10-26 17:23:46 0A0061003ED50000 NTFN_BATCH AAFCIbAAHAAAM5AAAA
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAA
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAB
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAC
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAD
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAE
2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS AAFCIcAAHAAAM8IAAF
8 rows selected
从这个结果可以分析出
执行了3个事务(简称1,2,3)
事务1,2更改了NTFN_BATCH,事务3更改了NTFN_TRANS
分享到:
相关推荐
Registered State Change Notification
oracle 通知服务(DCN) oracle data change notification,可用
使用android做的小例子 可以在通知栏显示消息和小图标 点击进去可以进入一个页面 主要用于notification的学习 基于android2.3开发
android notification 通知 源码 例子
演示Android平台上的Notification的简单使用。如有不足,请指正,我们一起学习进步!谢谢!
疯狂Android中有关Notification的简单例子
json2notification - 一个多功能方便好用的notification通知栏通知开源库
这个例子演示Android 在状态栏添加Notification信息图标及提示,相信大家对这个功能已经不陌生了,手机中安装的APP,一般都会在后台运行,时不时会在手机顶部的状态栏中显示应用的图标,滑出状态栏会看到详细的信息...
Notification
一个小试验,主要说清楚delegate与Notification的区别,及相应的用法
Notification最新用法、实现Notification的通知栏常驻、Notification的各种样式big View、解决Notification点击无效
调用getSystemService(NOTIFICATION_SERVICE)方法获取系统的NotifacationManager服务 通过构造器创建一个Notification对象 为Notification设置各种属性 通过NotificationManager发送Notification。
使用javascript 封装实现 Notification 消息通知框
各种Notification的使用技巧
示例功能包含普通Notification、折叠式Notification和悬挂式Notification,并且实现了Notification的显示等级。
android notification 通知 demo