當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle12c与Oracle11g采用触发器与存储过程实现同步更新（代码+图解）

發布時間：2024/10/14 编程问答 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle12c与Oracle11g采用触发器与存储过程实现同步更新（代码+图解）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一、保證兩臺筆記本網絡互通

關于連接，詳情見之前寫的：WIFI網絡，兩臺筆記本互聯Oracle，一臺是11g,一臺是12c

現在測試的ip地址：

A: 10.65.252.84
B: 10.82.156.248

二、用戶簡單架構圖及權限設計

A機的DBA賬戶是shj_dba，B機的DBA賬戶是c##hachi
中間的為連接名，標明連接的名字，對應權限的用戶使用對方跟自己權限相同的賬號，通過連接名訪問對方數據庫。

各個用戶的權限

三、表結構

這里雙方都采用以下語句創建表，表就建立在各自sys創建的DBA的模式下，不建立任何外鍵和索引，以免發生同步觸發器更新異常，初學先入個門吧！

/*==============================================================*/ /* Table: "part" */ /*==============================================================*/ create table "part" ("p_id" INTEGER not null,"p_name" VARCHAR2(10),"p_size" VARCHAR2(10),"p_price" FLOAT(10),"p_desc" VARCHAR2(50),constraint PK_PART primary key ("p_id") );/*==============================================================*/ /* Table: "project" */ /*==============================================================*/ create table "project" ("pj_id" INTEGER not null,"pj_money" FLOAT(10),"pj_date" DATE,constraint PK_PROJECT primary key ("pj_id") );/*==============================================================*/ /* Table: "staff" */ /*==============================================================*/ create table "staff" ("sf_id" INTEGER not null,"s_id" INTEGER,"sta_sf_id" INTEGER,"sf_name" VARCHAR2(10),"sf_age" SMALLINT,"sf_xname" VARCHAR2(10),constraint PK_STAFF primary key ("sf_id") );/*==============================================================*/ /* Table: "storage" */ /*==============================================================*/ create table "storage" ("s_id" INTEGER not null,"s_area" FLOAT(10),"s_phone" VARCHAR2(11),constraint PK_STORAGE primary key ("s_id") );/*==============================================================*/ /* Table: "storage_info" */ /*==============================================================*/ create table "storage_info" ("s_id" INTEGER not null,"p_id" INTEGER not null,"sf_num" INTEGER,constraint PK_STORAGE_INFO primary key ("s_id", "p_id") );/*==============================================================*/ /* Table: "supplier" */ /*==============================================================*/ create table "supplier" ("sp_id" INTEGER not null,"sp_name" VARCHAR2(10),"sp_address" VARCHAR2(30),"sp_phone" VARCHAR2(11),"sp_acount" VARCHAR2(15),constraint PK_SUPPLIER primary key ("sp_id") );/*==============================================================*/ /* Table: "supply_info" */ /*==============================================================*/ create table "supply_info" ("p_id" INTEGER not null,"pj_id" INTEGER not null,"sp_id" INTEGER not null,"sp_num" INTEGER,constraint PK_SUPPLY_INFO primary key ("p_id", "pj_id", "sp_id") );

四、sys授予系統權限，創建DBA賬戶

-------------------------- 一些可能用得上的查詢調試語句 ------------------------------------------ -- 命令行查看是否有遠程連接權限 select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); -- 命令行窗口使用system管理員去grant授權遠程連接 grant CREATE DATABASE LINK to public; -- 查看遠程連接 select * from dba_db_links; -- 創建同義詞 create public synonym stu for student; select * from stu; --刪除同義詞 drop synonym remote ; -- 刪除遠程連接 drop database link linkname; -- ORACLE 查找所有同義詞 SELECT * FROM SYS.ALL_SYNONYMS t WHERE t.owner in ('C##HACHI'); ---------------------------------------------------------------------------------- ------------------------------------ A機 ---------------------------------------------- conn sys/a123456 as sysdba; -- 授予所有用戶連接權限 grant connect to public; -- 授予所有用戶創建遠程連接權限，這樣所有用戶都可以創建和刪除遠程連接 database link grant CREATE DATABASE LINK to public; -- 授予所有用戶創建同義詞的權限,這樣所有用戶都可以創建和刪除同義詞 synonym grant CREATE synonym to public; -- DBA用戶創建及授權 create user shj_dba IDENTIFIED by a123456; grant dba,connect,resource to shj_dba; commit;------------------------------------ B機 ---------------------------------------------- conn sys/a123456 as sysdba; -- 授予所有用戶連接權限 grant connect to public; -- 授予所有用戶創建遠程連接權限，這樣所有用戶都可以創建和刪除遠程連接 database link grant CREATE DATABASE LINK to public; -- 授予所有用戶創建同義詞的權限,這樣所有用戶都可以創建和刪除同義詞 synonym grant CREATE synonym to public; -- DBA用戶創建及授權 create user c##hachi IDENTIFIED by a123456; grant dba,connect,resource to c##hachi;

五、雙方各自創建的DBA賬戶再創建其他用戶，以及私有同義詞

------------------------------------ B機 ---------------------------------------------- conn c##hachi/a123456; -- 供應商用戶創建及授權 create user c##supplier IDENTIFIED by a123456 ; grant SELECT,INSERT,DELETE,UPDATE on "supplier" to c##supplier; grant SELECT,INSERT,DELETE,UPDATE on "supply_info" to c##supplier; grant SELECT on "part" to c##supplier; grant SELECT on "project" to c##supplier;-- 領導用戶創建及授權 create user c##leader IDENTIFIED by a123456 ; grant SELECT,INSERT,DELETE,UPDATE on "staff" to c##leader; grant SELECT on "supplier" to c##leader; grant SELECT on "supply_info" to c##leader; grant SELECT on "project" to c##leader; grant SELECT,INSERT,DELETE,UPDATE on "part" to c##leader; grant SELECT,UPDATE on "storage" to c##leader; grant SELECT,INSERT,DELETE,UPDATE on "storage_info" to c##leader;-- 職工用戶創建及授權 create user c##staff IDENTIFIED by a123456; grant SELECT on "staff" to c##staff; grant SELECT on "storage" to c##staff;-- 創建4個不同用戶權限的遠程連接conn c##hachi/a123456; -- 本機DBA用戶c##hachi，使用對方的DBA賬戶登錄遠程連接 create database link shj0dba connect to shj_dba identified by "a123456" using '10.65.252.84/orcl'; -- 創建同義詞 create synonym remote_part for "part"@shj0dba; create synonym remote_project for "project"@shj0dba; create synonym remote_staff for "staff"@shj0dba; create synonym remote_storage for "storage"@shj0dba; create synonym remote_storage_info for "storage_info"@shj0dba; create synonym remote_supplier for "supplier"@shj0dba; create synonym remote_supply_info for "supply_info"@shj0dba; select * from remote_supply_info; commit;conn c##leader/a123456; -- 本機領導用戶c##leader，使用對方的領導賬戶登錄遠程連接 create database link shj0leader connect to shj_leader identified by "a123456" using '10.65.252.84/orcl'; create synonym remote_part for shj_dba."part"@shj0leader; create synonym remote_project for shj_dba."project"@shj0leader; create synonym remote_staff for shj_dba."staff"@shj0leader; create synonym remote_storage for shj_dba."storage"@shj0leader; create synonym remote_storage_info for shj_dba."storage_info"@shj0leader; create synonym remote_supplier for shj_dba."supplier"@shj0leader; create synonym remote_supply_info for shj_dba."supply_info"@shj0leader; select * from remote_supply_info;commit;conn c##supplier/a123456; -- 本機供應商用戶c##supplier，使用對方的供應商賬戶登錄遠程連接 create database link shj0supplier connect to shj_supplier identified by "a123456" using '10.65.252.84/orcl'; create synonym remote_part for shj_dba."part"@shj0supplier; create synonym remote_project for shj_dba."project"@shj0supplier; create synonym remote_supplier for shj_dba."supplier"@shj0supplier; create synonym remote_supply_info for shj_dba."supply_info"@shj0supplier; select * from remote_supply_info;commit;conn c##staff/a123456; -- 本機員工用戶c##staff，使用對方的員工賬戶登錄遠程連接 create database link shj0staff connect to shj_staff identified by "a123456" using '10.65.252.84/orcl'; create synonym remote_staff for shj_dba."staff"@shj0staff ; create synonym remote_storage for shj_dba."storage"@shj0staff; select * from remote_staff; commit;

------------------------------------ A機 ---------------------------------------------- conn shj_dba/a123456; -- 供應商用戶創建及授權 create user shj_supplier IDENTIFIED by a123456 ; grant SELECT,INSERT,DELETE,UPDATE on "supplier" to shj_supplier; grant SELECT,INSERT,DELETE,UPDATE on "supply_info" to shj_supplier; grant SELECT on "part" to shj_supplier; grant SELECT on "project" to shj_supplier;-- 領導用戶創建及授權 create user shj_leader IDENTIFIED by a123456 ; grant SELECT,INSERT,DELETE,UPDATE on "staff" to shj_leader; grant SELECT on "supplier" to shj_leader; grant SELECT on "supply_info" to shj_leader; grant SELECT on "project" to shj_leader; grant SELECT,INSERT,DELETE,UPDATE on "part" to shj_leader; grant SELECT,UPDATE on "storage" to shj_leader; grant SELECT,INSERT,DELETE,UPDATE on "storage_info" to shj_leader;-- 職工用戶創建及授權 create user shj_staff IDENTIFIED by a123456; grant SELECT on "staff" to shj_staff; grant SELECT on "storage" to shj_staff;commit;-- 刪除同名同義詞與連接 DROP SYNONYM remote_part; DROP SYNONYM remote_project; DROP SYNONYM remote_staff; DROP SYNONYM remote_storage; DROP SYNONYM remote_storage_info; DROP SYNONYM remote_supplier; DROP SYNONYM remote_supply_info; drop database link c##0hachi;-- 創建4個不同用戶權限的遠程連接conn shj_dba/a123456;-- 本機DBA用戶shj_dba，使用對方的DBA賬戶登錄遠程連接 create database link c##0hachi connect to "c##hachi" identified by "a123456" using '10.82.156.248/orcl'; -- 創建同義詞 create synonym remote_part for "part"@c##0hachi; create synonym remote_project for "project"@c##0hachi; create synonym remote_staff for "staff"@c##0hachi; create synonym remote_storage for "storage"@c##0hachi; create synonym remote_storage_info for "storage_info"@c##0hachi; create synonym remote_supplier for "supplier"@c##0hachi; create synonym remote_supply_info for "supply_info"@c##0hachi; select * from remote_supply_info; commit;conn shj_leader/a123456; -- 本機領導用戶shj_leader，使用對方的領導賬戶登錄遠程連接 create database link c##0leader connect to "c##leader" identified by "a123456" using '10.82.156.248/orcl'; create synonym remote_part for c##hachi."part"@c##0leader; create synonym remote_project for c##hachi."project"@c##0leader; create synonym remote_staff for c##hachi."staff"@c##0leader; create synonym remote_storage for c##hachi."storage"@c##0leader; create synonym remote_storage_info for c##hachi."storage_info"@c##0leader; create synonym remote_supplier for c##hachi."supplier"@c##0leader; create synonym remote_supply_info for c##hachi."supply_info"@c##0leader; select * from remote_supply_info;commit;conn shj_supplier/a123456; -- 本機供應商用戶shj_supplier，使用對方的供應商賬戶登錄遠程連接 create database link c##0supplier connect to "c##supplier" identified by "a123456" using '10.82.156.248/orcl'; create synonym remote_part for c##hachi."part"@c##0supplier; create synonym remote_project for c##hachi."project"@c##0supplier; create synonym remote_supplier for c##hachi."supplier"@c##0supplier; create synonym remote_supply_info for c##hachi."supply_info"@c##0supplier; select * from remote_supply_info;commit;conn shj_staff/a123456; -- 本機員工用戶shj_staff，使用對方的員工賬戶登錄遠程連接 create database link c##0staff connect to "c##staff" identified by "a123456" using '10.82.156.248/orcl'; create synonym remote_staff for c##hachi."staff"@c##0staff ; create synonym remote_storage for c##hachi."storage"@c##0staff; select * from remote_staff; commit;

因為使用的wifi網絡，采用DHCP協議動態分配IP地址，所以每一次兩臺筆記本的IP地址是不一樣的，所以連接得刪了再重新建立，同義詞不用刪了重新建立，重新創建一下database link連接就行了。
我這里建的都是私有連接，私有同義詞，只有創建該連接的用戶才可以使用，其他用戶沒有使用權限，是透明的，所以設置同名的同義詞不影響。

同義詞創建后就可以使用同義詞去增刪改查對方的表了，這里貼兩張圖，各自私有建的連接，不影響各自的同名同義詞。

六、創建同步觸發器與存儲過程

可能由于版本問題，B機12C可以創建觸發器同步備份A機的上的表，而A機不行。同時B機創建存儲過程，執行存儲過程，會觸發B機的觸發器去同步更新A機上的表。

A機可以使用連接更新B機的數據，同時觸發觸發器更新A機自己的表。

下面以圖文結合方式說明以上兩點。

這里觸發器和存儲過程都建在c##hachi用戶模式下。

6.1 觸發器

觸發器代碼：

------------------------------------ B機 c##hachi模式下創建 ---------------------------------------------- -- 增刪改part drop trigger insert_update_delete_part;create or replace trigger insert_update_delete_part after insert or update or delete on "part" for each row beginif inserting theninsert into remote_part("p_id","p_name","p_size","p_price","p_desc") values(:new."p_id",:new."p_name",:new."p_size",:new."p_price",:new."p_desc");elsif updating thenupdate remote_part set remote_part."p_name"=:new."p_name",remote_part."p_size"=:new."p_size",remote_part."p_price"=:new."p_price",remote_part."p_desc"=:new."p_desc" where remote_part."p_id"=:new."p_id";elsif deleting then delete from remote_part where remote_part."p_id"=:old."p_id";end if; end;insert into "part" values(9,'eeeee',22,12,'eeeeee'); update "part" set "p_name"='abc',"p_size"=11,"p_price"=11,"p_desc"='abc' where "p_id"=9; delete from "part" where "p_id"=9;-- 增刪改project drop trigger insert_update_delete_project;create or replace trigger insert_update_delete_project after insert or update or delete on "project" for each row beginif inserting theninsert into remote_project("pj_id","pj_money","pj_date") values(:new."pj_id",:new."pj_money",:new."pj_date");elsif updating thenupdate remote_project set remote_project."pj_money"=:new."pj_money",remote_project."pj_date"=:new."pj_date" where remote_project."pj_id"=:new."pj_id";elsif deleting then delete from remote_project where remote_project."pj_id"=:old."pj_id";end if; end;insert into "project" values(1,100,null); update "project" set "pj_money"=200,"pj_date"=null where "pj_id"=1; delete from "project" where "pj_id"=1;-- 增刪改staff drop trigger insert_update_delete_staff;create or replace trigger insert_update_delete_staff after insert or update or delete on "staff" for each row beginif inserting theninsert into remote_staff("sf_id","s_id","sta_sf_id","sf_name","sf_age","sf_xname") values(:new."sf_id",:new."s_id",:new."sta_sf_id",:new."sf_name",:new."sf_age",:new."sf_xname");elsif updating thenupdate remote_staff set remote_staff."sf_name"=:new."sf_name",remote_staff."sf_age"=:new."sf_age",remote_staff."sf_xname"=:new."sf_xname" where remote_staff."sf_id"=:new."sf_id";elsif deleting then delete from remote_staff where remote_staff."sf_id"=:old."sf_id";end if; end;insert into "staff" values(1,1,1,'Hachi',10,'Bit'); update "staff" set "sf_name"='BitHachi',"sf_xname"='bithachi' where "sf_id"=1; delete from "staff" where "sf_id"=1;-- 增刪改storage drop trigger insert_update_delete_storage;create or replace trigger insert_update_delete_storage after insert or update or delete on "storage" for each row beginif inserting theninsert into remote_storage("s_id","s_area","s_phone") values(:new."s_id",:new."s_area",:new."s_phone");elsif updating thenupdate remote_storage set remote_storage."s_area"=:new."s_area",remote_storage."s_phone"=:new."s_phone" where remote_storage."s_id"=:new."s_id";elsif deleting then delete from remote_storage where remote_storage."s_id"=:old."s_id";end if; end;insert into "storage" values(1,100,'17683738511'); update "storage" set "s_area"=200,"s_phone"='17683838555' where "s_id"=1; delete from "storage" where "s_id"=1;-- 增刪改storage_info drop trigger insert_update_delete_storage_info;create or replace trigger insert_update_delete_storage_info after insert or update or delete on "storage_info" for each row beginif inserting theninsert into remote_storage_info("s_id","p_id","sf_num") values(:new."s_id",:new."p_id",:new."sf_num");elsif updating thenupdate remote_storage_info set remote_storage_info."sf_num"=:new."sf_num" where remote_storage_info."s_id"=:new."s_id" and remote_storage_info."p_id"=:new."p_id";elsif deleting then delete from remote_storage_info where remote_storage_info."s_id"=:old."s_id" and remote_storage_info."p_id"=:old."p_id";end if; end;insert into "storage_info" values(1,1,100); update "storage_info" set "sf_num"=200 where "s_id"=1 and "p_id"=1; delete from "storage_info" where "s_id"=1 and "p_id"=1;-- 增刪改supplier drop trigger insert_update_delete_supplier;create or replace trigger insert_update_delete_supplier after insert or update or delete on "supplier" for each row beginif inserting theninsert into remote_supplier("sp_id","sp_name","sp_address","sp_phone","sp_acount") values(:new."sp_id",:new."sp_name",:new."sp_address",:new."sp_phone",:new."sp_acount");elsif updating thenupdate remote_supplier set remote_supplier."sp_name"=:new."sp_name" where remote_supplier."sp_id"=:new."sp_id";elsif deleting then delete from remote_supplier where remote_supplier."sp_id"=:old."sp_id" ;end if; end;insert into "supplier" values(1,'BitHachi','中國湖北','17683738511','1000001'); update "supplier" set "sp_name"='Hachi' where "sp_id"=1 ; delete from "supplier" where "sp_id"=1;-- 增刪改supply_info drop trigger insert_update_delete_supply_info;create or replace trigger insert_update_delete_supply_info after insert or update or delete on "supply_info" for each row beginif inserting theninsert into remote_supply_info("p_id","pj_id","sp_id","sp_num") values(:new."p_id",:new."pj_id",:new."sp_id",:new."sp_num");elsif updating thenupdate remote_supply_info set remote_supply_info."sp_num"=:new."sp_num" where remote_supply_info."p_id"=:new."p_id" and remote_supply_info."pj_id"=:new."pj_id" and remote_supply_info."sp_id"=:new."sp_id";elsif deleting then delete from remote_supply_info where remote_supply_info."p_id"=:old."p_id" and remote_supply_info."pj_id"=:old."pj_id" and remote_supply_info."sp_id"=:old."sp_id";end if; end;insert into "supply_info" values(1,1,1,100); update "supply_info" set "sp_num"=200 where "p_id"=1 and "pj_id"=1 and "sp_id"=1 ; delete from "supply_info" where "p_id"=1 and "pj_id"=1 and "sp_id"=1 ;

這里觸發器很多，我就演示一個。見下圖：

這張圖是B機創建一個觸發器實現remote_part同義詞表示的A機的part表的同步更新。

現在我們在A機上使用同義詞進行插入、修改、刪除，會觸發B機的觸發器同步更新B機和A機的表數據。

6.3 存儲過程

這里B機創建了一個存儲過程，B機調用存儲過程可以實現B機和A機的同步更新。

先貼代碼再放截圖流程

-- 查詢數據 drop procedure select_data; create procedure select_data (var_datas out sys_refcursor) AS begin OPEN var_datas for select * from "part"; end select_data; var datas refcursor; exec select_data(:datas); print:datas;-- 增加數據 drop procedure insert_data; create procedure insert_data (var_id "part"."p_id"%TYPE,var_name "part"."p_name"%TYPE,var_size "part"."p_size"%TYPE,var_price "part"."p_price"%TYPE,var_desc "part"."p_desc"%TYPE) AS begin INSERT INTO "part" values(var_id,var_name,var_size,var_price,var_desc); end insert_data; exec insert_data(2,'ANIVJO',22,15,'DFGDG');-- 修改數據 drop procedure update_data; create procedure update_data (var_id "part"."p_id"%TYPE,var_name "part"."p_name"%TYPE) AS begin UPDATE "part" SET "p_name"=var_name WHERE "p_id"=var_id; end update_data; exec update_data(2,'BitHachi');-- 刪除數據 drop procedure delete_data; create procedure delete_data (var_id "part"."p_id"%TYPE) AS begin DELETE FROM "part" WHERE "p_id"=var_id; end delete_data; exec delete_data(2);