Oracle Stored Procedure

A data synchronization function is required in the recent project to regularly synchronize the information in a database to another database. In this way, the storage process trigger timer and dblink are used.

I have never written about the storage process before. I just used this opportunity to train my hands and close up this blog for future reference:

 

Serial number	Object Name	Type	Description
1	Synbasedatatemp	Table	Temporary table
2	Synbasedatahistory	Table	Synchronize historical records
3	Synbasedatatemp_sequence	Sequence	Set field ID auto-Increment
4	Tri_synbasedatatemp	Trigger	When a new record exists in the temporary table, the ID is automatically increased by 1.
5	Tri _ basic data table name	Trigger	When the CCS basic data table has an add, delete, and modify operation, it is automatically triggered on the ECCS basic data Add records to the table (32 records in total)
6	Basedatasynproc	Procedure	Synchronize the information recorded in the temporary table to ECCS and synchronize the records. Added to synbasedatahistory and deleted synbasedatatemp information.

Create a synchronization table:

 

/* Create Table synbasedatatemp */create table synbasedatatemp (ID number (15, 0) not null, condition varchar2 (300) not null, tablename varchar2 (40) not null, operatetype varchar2 (20) not null, operatedate date, failflag varchar2 (1), failtimes integer, primary key (ID); Comment on table synbasedatatemp is 'synchronize temporary table'; Comment on column synbasedatatemp. ID is 'auto-incrementing column, primary key '; Comment on column synbasedatatemp. condition is 'table primary key condition'; Comment on column synbasedatatemp. tablename is 'table name'; Comment on column synbasedatatemp. operatetype is 'Operation type'; Comment on column synbasedatatemp. operatedate is 'Operation time'; Comment on column synbasedatatemp. failflag is 'failure flag, 1 failed'; Comment on column synbasedatatemp. failtimes is 'failed';/* Create Table synhistory */create table synbasedatahistory (ID number (15, 0) not null, condition varchar2 (300) not null, tablename varchar2 (40) not null, operatetype varchar2 (20) not null, operatedate date, primary key (ID); Comment on table synbasedatahistory is 'synchronize history records'; Comment on column synbasedatahistory. ID is 'auto-incrementing column, primary key '; Comment on column synbasedatahistory. condition is 'table primary key condition'; Comment on column synbasedatahistory. tablename is 'table name'; Comment on column synbasedatahistory. operatetype is 'Operation type'; Comment on column synbasedatahistory. operatedate is 'Operation time';/* creates an auto-incrementing column for the ID of the table synbasedatatemp */-- creates an auto-incrementing sequence create sequence synbasedatatemp_sequence increment by 1 start with 1 nomaxvalue nocycle Cache 10; -- create trigger create or replace trigger tri_synbasedatatemp before insert on synbasedatatemp for each row begin select synbasedatatemp_sequence.nextval into: New. ID from dual; end;

 

 

Create a trigger for each table. There are 32 tables in total and one table is used as an instance:

 

/* Create a trigger for the ggsystem table. When there are related operations, store related information in synbasedatatemp */create or replace trigger tri_ggsystem after insert or delete or update -- specify the trigger time to trigger on ggsystem referencing new as new_value for each -- indicates that a row-Level Trigger is created. begin -- Insert the data before modification to the temporary table synbasedatatemp, for calling stored procedures. If inserting then insert into synbasedatatemp (condition, tablename, operatetype, operatedate) values ('systemcode = '| CHR (39) |: new_value.systemcode | CHR (39 ), 'ggsystem', 'insert', sysdate); elsif updating then insert into synbasedatatemp (condition, tablename, operatetype, operatedate) values ('systemcode = '| CHR (39) |: new_value.systemcode | CHR (39), 'ggsystem', 'update', sysdate); elsif deleting then insert into synbasedatatemp (condition, tablename, operatetype, operatedate) values ('systemcode = '| CHR (39) |: Old. systemcode | CHR (39), 'ggsystem', 'delete', sysdate); end if; end;

 

After a trigger is created, the stored procedure is as follows:

 

-- Baotou create or replace package basedatasyn is -- Author: zhengfazhen -- created: 2011-12-31 procedure basedatasynproc; -- synchronize data from CCS to eccsend basedatasyn; -- create or replace package body basedatasyn is -- Author: zhengfazhen -- created: 2011-12-31 -- synchronization Stored Procedure procedure basedatasynproc is v_id number (15, 0); -- temporary table ID condition varchar2 (400 ); -- temporary table primary key condition tablename varchar2 (50); -- Name of the table stored in the temporary table operatetype varchar2 (10); -- operation type: insert update Delete insertsql varchar2 (400 ); -- insert SQL updatesql varchar2 (2000); -- Update SQL deletesql varchar2 (500); -- delete SQL synlogsql varchar2 (500); -- SQL columstr varchar2 (1000 ); -- non-primary key column name string ggusercolum varchar2 (500); -- gguser is not a primary key and removed (password, passwordsetdate, passwordexpiredate, lockstatus, updatepwdind, loginerrtimes) Field ggusersql varchar2 (500 ); -- assign the default value to gguser: SQL failsql varchar2 (500); -- Exception Handling SQL flag varchar2 (1); -- Query all records in the temporary table: cursor c_syntemp is select C. ID, C. condition, C. tablename, C. operatetype, C. operatedate from synbasedatatemp C where 1 = 1 order by operatedate ASC; c_syntemp_row c_syntemp % rowtype; -- Return non-primary key column name cursor c_columname (tablename varchar2) is select UC. column_name from user_tab_columns UC where UC. column_name not in (select Col. column_name from user_constraints con, user_cons_columns Col where con. constraint_name = Col. constraint_name and con. constraint_type = 'p' and Col. table_name = tablename) and UC. table_name = tablename; c_row c_columname % rowtype; begin for c_syntemp_row in c_syntemp loop begin v_id: = c_syntemp_ro1_id; condition: = condition; tablename: = condition; operatetype: = condition; flag: = '1';/* The non-primary key column name returned by splicing start */For c_row in c_columname (tablename) loop if flag = '1' then columstr: = c_row.column_name; flag: = '0'; else columstr: = columstr | ',' | c_row.column_name; end if; end loop; /* The end column name of the non-primary key column returned by splicing * // * synonym ECCS _ table name */insertsql: = 'insert into ECCS _ '| tablename |' (select * from' | tablename | 'where' | condition | ')'; updatesql: = 'Update ECCS _ '| tablename | 'set (' | columstr | ') = (select '| columstr | 'from' | tablename | 'where' | condition |') where' | condition; deletesql: = 'delete from ECCS _ '| tablename | 'where' | condition; synlogsql: = 'insert into synbasedatahistory (select St. ID, St. condition, St. tablename, St. operatetype, St. operatedate from synbasedatatemp st where id =: 1) '; failsql: = 'Update synbasedatatemp set failflag =' | CHR (39) | '1' | CHR (39) | 'where id = '| v_id;/* special processing of the gguser table */ggusersql: = 'Update ECCS _ '| tablename | 'set lockstatus =' | CHR (39) | '1' | CHR (39) | ', updatepwdind = '| CHR (39) | '1' | CHR (39) |', loginerrtimes = '| CHR (39) | '0' | CHR (39) | 'where' | condition; ggusercolum: = 'usercname, usertname, userename, seal, companycode, issuecompany, accountcode, phone, mobile, '|' address, postcode, email, userind, loginsystem, creatorcode, createtime, updatercode, '| 'updatetime, validind, remark, flag, sex, aliascname, aliastname, aliasename, uwinitial '; If tablename = 'gguser' then updatesql: = 'Update ECCS _' | tablename | 'set ('| ggusercolum | ') = (select '| ggusercolum | 'from' | tablename | 'where' | condition |') where' | condition; end if; if operatetype = 'insert' then execute immediate insertsql; -- synchronize data if tablename = 'gguser' then execute immediate ggusersql; -- assign default value: end if; execute immediate synlogsql using v_id; -- Insert the delete from synbasedatatemp where id = v_id; -- delete the information in the temporary table commit; elsif operatetype = 'updat' then execute immediate updatesql; execute immediate synlogsql using v_id; delete from synbasedatatemp where id = v_id; commit; elsif operatetype = 'delete' then execute immediate deletesql; execute immediate synlogsql using v_id; delete from synbasedatatemp where id = v_id; commit; end if; exception when others then rollback; execute immediate failsql; -- Update the exception flag commit; end loop; end basedatasynproc; end basedatasyn;

Create a job and regularly execute the stored procedure:

 

Begin dbms_scheduler.create_job (job_name => 'jobsynproc', -- job name, set job_type => 'stored _ procedure ', -- type: Stored Procedure job_action => 'ccs _ synproc ', -- The stored procedure name is proc start_date => to_date ('30-12-2011 00:00:00 ', 'dd-mm-yyyy hh24: MI: ss '), -- enabled => true, -- auto_drop => false, repeat_interval => 'freq = monthly; interval = 1'); end;

Before that, you must authorize the user with the permission to add, delete, modify, and create synonyms so that the storage process can be smoothly executed:

 

1. log on to the DBA and run the SQL command: grant create synonym to the authorized user;
2. If you need the permission to modify a table, you need to execute: grant select, insert, update on monitor_sys_log to the authorized user;

3. Use "authorized user" to log on to create a synonym: Create synonym monitor_sys_log (Synonym name) for owning table users. monitor_sys_log (Table Name );