通过View修改/插入数据,View是通过DBLink连接的表

最新推荐文章于 2024-07-01 19:39:18 发布

最新推荐文章于 2024-07-01 19:39:18 发布 · 361 阅读

文章标签：

#dblink

本文介绍如何使用DBLink在数据库中创建视图，并通过创建规则实现数据的插入与更新操作。具体步骤包括定义视图结构、创建视图、设置插入与更新规则等。

通过DBLink,创建视图

DROP VIEW
IF EXISTS auth_mst;

CREATE VIEW auth_mst AS SELECT
	auth_id,
	auth_nm,
	note_txt,
	data_create_user_id,
	data_create_program,
	data_create_dt,
	last_data_updt_user_id,
	last_data_updt_program,
	last_data_updt_dt,
	updt_seg_typ,
	versionno
FROM
	dblink(
		'dbname=mkmst',
		'select
auth_id,
auth_nm,
note_txt,
data_create_user_id,
data_create_program,
data_create_dt,
last_data_updt_user_id,
last_data_updt_program,
last_data_updt_dt,
updt_seg_typ,
versionno
from mkmst.auth_mst '
	)AS t1(
		auth_id CHARACTER VARYING(10),
		auth_nm CHARACTER VARYING(40),
		note_txt CHARACTER VARYING(100),
		data_create_user_id CHARACTER VARYING(10),
		data_create_program CHARACTER VARYING(40),
		data_create_dt TIMESTAMP(0)WITHOUT TIME ZONE,
		last_data_updt_user_id CHARACTER VARYING(10),
		last_data_updt_program CHARACTER VARYING(40),
		last_data_updt_dt TIMESTAMP(0)WITHOUT TIME ZONE,
		updt_seg_typ CHARACTER VARYING(1),
		versionno INTEGER
	);

创建规则,用于View

CREATE or replace RULE notify_me AS ON insert TO auth_mst DO 
instead

SELECT dblink_exec('host=10.247.0.223 dbname=mkmst user=mkadmin password=mkadmin',  'insert into auth_mst(auth_id, auth_nm) VALUES(''' ||new.auth_id  ||''', ''' ||new.auth_nm|| ''');'  , true);


insert into auth_mst(  auth_id, auth_nm) VALUES ( 'lilao91', 'lilao19');


CREATE or replace RULE notify_me AS ON update TO auth_mst DO 
instead

SELECT dblink_exec('host=10.247.0.223 dbname=mkmst user=mkadmin password=mkadmin', ' update auth_mst set auth_nm=''' ||new.auth_nm  ||''' where auth_id=''' ||new.auth_id  ||'''; ' , true);



update  auth_mst set auth_nm='update_test' where auth_id='lilao91';
--update into auth_mst(  auth_id, auth_nm) VALUES ( 'lilao91', 'lilao19');

select * from auth_mst  where auth_id='lilao91';