大家都知道Oracle cdc异步模式配置比较复杂，而且每次都是但张表去配置，如果要配置多张表这样就很复杂了，虽然不难，但是工作强度很大，要仔细核对每个字段，这样真的很心累，因此创建了一个Java项目用来根据Oracle用户自动生成用户下所有表信息的cdc同步脚本。
主要核心代码如下：
1.系统参数类Params.java
package com.goldenbridge.dbcenter.generatesql.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import com.goldenbridge.dbcenter.generatesql.db.DBUtils;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class Params {
private static Map<String,String> keyMap = new HashMap<String,String>();
public static String publisherAccount = "cdc_publisher";//默认发布者账号
public static String publisherPasswd = "cdc_publisher";
public static String subscriberAccount = "cdc_subscriber";//默认订阅者账号
public static String subscriberPasswd = "cdc_subscriber";
public static String getValue(String key) {
return keyMap.get(key);
}
static {
Properties properties = new Properties();
InputStream inputStream = DBUtils.class.getResourceAsStream("/jdbc.properties");
try {
properties.load(inputStream);
Set<Object> keySet = properties.keySet();
for(Object key:keySet) {
keyMap.put(key.toString(), properties.getProperty(key.toString()));
}
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} 2.系统配置文件jdbc.properties
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url = jdbc:oracle:thin:@192.168.31.90:1521:ORCL
jdbc.username = scott
jdbc.password = tiger
dblink = sycn_db3.项目入口文件Main.java
package com.goldenbridge.dbcenter.generatesql;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import com.goldenbridge.dbcenter.generatesql.bean.TableAttr;
import com.goldenbridge.dbcenter.generatesql.core.TableAttrFactory;
import com.goldenbridge.dbcenter.generatesql.core.TableFactory;
import com.goldenbridge.dbcenter.generatesql.script.DeleteAllFactory;
import com.goldenbridge.dbcenter.generatesql.script.ProcedureFactory;
import com.goldenbridge.dbcenter.generatesql.script.ScriptFactory;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class Main {
public static void main(String[] args) throws Exception{
List<String> tableList = TableFactory.getTableList();
Map<String, List<TableAttr>> mapTable = TableAttrFactory.getTableAttr(tableList);
Map<String,String> primaryKeyMap = TableAttrFactory.getTablePrimaryKey(tableList);
ScriptFactory.getPreSQLInfo();//获取前置数据库配置信息配置
ScriptFactory.getSQLFile(mapTable, primaryKeyMap); //自动生成CDC发布订者脚本文件
ScriptFactory.getSubscriberSQLInfo(mapTable, primaryKeyMap); //自动生成CDC订阅脚本文件
ProcedureFactory.getProcedureSql(mapTable, primaryKeyMap);//自动生成数据同步存储过程脚本
DeleteAllFactory.generatorSql(mapTable);//自动生成删除CDC环境脚本
ScriptFactory.close();
}
}
package com.goldenbridge.dbcenter.generatesql.bean;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class TableAttr {
private String tableName;
private String ColumnName;
private String dataType;
private String nullable;
private String dataDefault;
private String ColumnId;
private String comments;
private int data_length;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getColumnName() {
return ColumnName;
}
public void setColumnName(String columnName) {
ColumnName = columnName;
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
public String getNullable() {
return nullable;
}
public void setNullable(String nullable) {
this.nullable = nullable;
}
public String getDataDefault() {
return dataDefault;
}
public void setDataDefault(String dataDefault) {
this.dataDefault = dataDefault;
}
public String getColumnId() {
return ColumnId;
}
public void setColumnId(String columnId) {
ColumnId = columnId;
}
public String getComments() {
return comments;
}
public void setComments(String comments) {
this.comments = comments;
}
public int getData_length() {
return data_length;
}
public void setData_length(int data_length) {
this.data_length = data_length;
}
}
package com.goldenbridge.dbcenter.generatesql.core;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.goldenbridge.dbcenter.generatesql.db.DBUtils;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class TableFactory {
public static List<String> getTableList() throws Exception{
List<String> tList = new ArrayList<String>();
String SQL = "select * from tab where tabtype ='TABLE'";
Connection conn = DBUtils.getConnection();
PreparedStatement ps = conn.prepareStatement(SQL);
ResultSet rs =ps.executeQuery();
while(rs.next()) {
String name = rs.getString("TNAME");
if(!name.startsWith("BIN$"))
tList.add(rs.getString("TNAME"));
}
DBUtils.close(null, null, conn);
return tList;
}
public static void main(String[] args)throws Exception {
System.out.println(getTableList());
}
}
package com.goldenbridge.dbcenter.generatesql.core;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.goldenbridge.dbcenter.generatesql.bean.TableAttr;
import com.goldenbridge.dbcenter.generatesql.db.DBUtils;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class TableAttrFactory {
public static Map<String,List<TableAttr>> getTableAttr(List<String> tableList) throws Exception{
Connection conn = DBUtils.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
Map<String,List<TableAttr>> map = new HashMap<String,List<TableAttr>>();
for(String tableName : tableList) {
String tempSql = "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,COLUMN_ID from user_tab_columns where table_name =UPPER(?)";
ps = conn.prepareStatement(tempSql);
ps.setString(1, tableName);
rs = ps.executeQuery();
List<TableAttr> list = new ArrayList<TableAttr>();
while(rs.next()) {
TableAttr attr = new TableAttr();
attr.setColumnName(rs.getString("COLUMN_NAME"));
attr.setColumnId(rs.getString("COLUMN_ID"));
attr.setDataType(rs.getString("DATA_TYPE"));
attr.setData_length(rs.getInt("DATA_LENGTH"));
attr.setNullable(rs.getString("NULLABLE"));
list.add(attr);
}
map.put(tableName, list);
}
DBUtils.close(rs, ps, conn);
return map;
}
public static Map<String,String> getTablePrimaryKey(List<String> tableList) throws Exception{
Connection conn = DBUtils.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
Map<String,String> map = new HashMap<String,String>();
for(String tableName : tableList) {
String tempSql = "select COLUMN_NAME from user_cons_columns " +
"where constraint_name = (select constraint_name from user_constraints " +
" where table_name = ? and constraint_type='P')";
ps = conn.prepareStatement(tempSql);
ps.setString(1, tableName);
rs = ps.executeQuery();
String primaryKey = null;
while(rs.next()) {
primaryKey = rs.getString("COLUMN_NAME");
map.put(tableName, primaryKey);
}
}
DBUtils.close(rs, ps, conn);
return map;
}
public static void main(String[] args) throws Exception {
//System.out.println(getTableAttr());
}
}
7.简单的数据库工具类DBUtils.java
package com.goldenbridge.dbcenter.generatesql.db;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.goldenbridge.dbcenter.generatesql.utils.Params;
public class DBUtils {
private static String url;
private static String user;
private static String passwd;
private static String driverClass;
static {
Properties properties = new Properties();
InputStream inputStream = DBUtils.class.getResourceAsStream("/jdbc.properties");
try {
properties.load(inputStream);
driverClass = properties.getProperty("jdbc.driverClassName");
url = properties.getProperty("jdbc.url");
user = properties.getProperty("jdbc.username");
passwd = properties.getProperty("jdbc.password");
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(ResultSet rs,Statement st,Connection conn) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(st != null) {
st.close();
st = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url,user,passwd);
return connection;
}
/**
* pengweikang 20180329 获取发布者连接
* @return
* @throws SQLException
*/
public static Connection getPublisherConnection () throws SQLException {
Connection connection = DriverManager.getConnection(url,Params.publisherAccount,Params.publisherPasswd);
return connection;
}
/**
* pengweikang 20180329 获取订阅者连接
* @return
* @throws SQLException
*/
public static Connection getSubscriberConnection () throws SQLException {
Connection connection = DriverManager.getConnection(url,Params.subscriberAccount,Params.subscriberPasswd);
return connection;
}
}
package com.goldenbridge.dbcenter.generatesql.script;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.goldenbridge.dbcenter.generatesql.bean.TableAttr;
import com.goldenbridge.dbcenter.generatesql.utils.Params;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class ScriptFactory {
private static final String TABLE_INSTANTIATION = "EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => '?');";
private static final String CHANGE_SET = "EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(" +
" change_set_name => '?'," +
" description => 'Change set for CDC ?'," +
" change_source_name => 'HOTLOG_SOURCE'," +
" stop_on_ddl => 'y'," +
" begin_date => sysdate," +
" end_date => sysdate+5);";
private static final String CHANGE_TABLE = "BEGIN\r\n"
+ " DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(\r\n" +
" owner => '?1',\r\n" +
" change_table_name => '?2', \r\n" +
" change_set_name => '?3',\r\n" +
" source_schema => '?4',\r\n" +
" source_table => '?5',\r\n" +
" column_type_list => '?6',\r\n" +
" capture_values => 'both',\r\n" +
" rs_id => 'y',\r\n" +
" row_id => 'n',\r\n" +
" user_id => 'n',\r\n" +
" timestamp => 'n',\r\n" +
" object_id => 'n',\r\n" +
" source_colmap => 'n',\r\n" +
" target_colmap => 'y',\r\n" +
" options_string => 'TABLESPACE ?7');\r\n"
+ "END;\r\n" +
" /\r\n";
private static final String ALTER_CHANGE_SET = "EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => '?',enable_capture => 'Y');";
private static final String ACCOUNT = Params.getValue("jdbc.username");
private static String tableSpace = "RECOGNIZE_DATA";
private static String GRANT_TABLE_SQL="grant all on ? to "+Params.publisherAccount+";";
private static String GRANT_TABLE_TO_SUB = "grant select on ? to "+Params.subscriberAccount+";";
private static String COMMIT_SQL = "commit;";
private static List<String> CHANGE_SET_LIST = new ArrayList<String>();
private static BufferedWriter writerBuffer = null;
public static void getSQLFile(Map<String,List<TableAttr>> tableMap,Map<String,String> primaryKey) throws Exception {
Set<String> tableSet = tableMap.keySet();
for(String tableName : tableSet) {
String Sql = GRANT_TABLE_SQL.replaceAll("\\?", ACCOUNT+"."+tableName);
write(Sql);
}
write(COMMIT_SQL); //提交
for(String tableName : tableSet) {
List<TableAttr> attrList = tableMap.get(tableName);
String startCode = tableName.substring(0,1);
//*********准备源表(Source Table)*******//
String table_initSQL = TABLE_INSTANTIATION.replaceAll("\\?",ACCOUNT+"."+tableName);
write(table_initSQL);
//*********创建变更集(Data Set)*******//
String DataSetName = "CDC_"+ACCOUNT+"_"+startCode;
boolean contain = CHANGE_SET_LIST.contains(DataSetName);
if(!contain) {
CHANGE_SET_LIST.add(DataSetName);
String change_set_SQL = CHANGE_SET.replaceAll("\\?", DataSetName);
write(change_set_SQL);
}
//*********创建变更表*******//
String change_table_sql = CHANGE_TABLE.replaceAll("\\?1", Params.publisherAccount);
change_table_sql = change_table_sql.replaceAll("\\?2", "CDC_"+tableName);
change_table_sql = change_table_sql.replaceAll("\\?3", DataSetName);
change_table_sql = change_table_sql.replaceAll("\\?4", ACCOUNT);
change_table_sql = change_table_sql.replaceAll("\\?5", tableName);
String tableAttrStr = "";
for(TableAttr attr: attrList) {
if(attr.getDataType().equals("DATE")) {
tableAttrStr+=attr.getColumnName()+" "+attr.getDataType()+",";
}else {
tableAttrStr+=attr.getColumnName()+" "+attr.getDataType()+"("+attr.getData_length()+"),";
}
}
tableAttrStr = tableAttrStr.substring(0,tableAttrStr.length() - 1);
change_table_sql = change_table_sql.replaceAll("\\?6", tableAttrStr);
change_table_sql = change_table_sql.replaceAll("\\?7", tableSpace);
write(change_table_sql);
String alter_change_set = ALTER_CHANGE_SET.replaceAll("\\?", DataSetName);
write(alter_change_set);
String grant_table_to_subscrib = GRANT_TABLE_TO_SUB.replaceAll("\\?", "CDC_"+tableName);//授权查询角色给订阅者
write(grant_table_to_subscrib);
}
}
private static final String create_subscription = "BEGIN\r\n" +
" dbms_cdc_subscribe.create_subscription(\r\n" +
" change_set_name=>'?1',\r\n" +
" description=>'cdc ?2', \r\n" +
" subscription_name=>'?3');\r\n" +
" END;\r\n" +
" /";
private static final String subscribe = "BEGIN\r\n" +
" dbms_cdc_subscribe.subscribe(\r\n" +
" subscription_name=>'?1', \r\n" +
" source_schema=>'?2', \r\n" +
" source_table=>'?3',\r\n" +
" column_list=>'?4',\r\n" +
" subscriber_view=>'?5');\r\n" +
" END;\r\n" +
" /";
private static final String activate_subscription = "EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'?');";
public static void getSubscriberSQLInfo(Map<String,List<TableAttr>> tableMap,Map<String,String> primaryKey) throws Exception {
Set<String> tableSet = tableMap.keySet();
for(String tableName: tableSet) {
String startCode = tableName.substring(0,1);
List<TableAttr> attrList = tableMap.get(tableName);
//*********创建订阅集*******//
String DataSetName = "CDC_"+ACCOUNT+"_"+startCode;
String create_subscription_sql = create_subscription.replaceAll("\\?1", DataSetName);
create_subscription_sql = create_subscription_sql.replaceAll("\\?2", "cdc "+ ACCOUNT+" subx");
create_subscription_sql = create_subscription_sql.replaceAll("\\?3",tableName+"_SUB");
write(create_subscription_sql);
String subscribe_sql = subscribe.replaceAll("\\?1",tableName+"_SUB");
subscribe_sql = subscribe_sql.replaceAll("\\?2", ACCOUNT );
subscribe_sql = subscribe_sql.replaceAll("\\?3", tableName );
String attr_sql = "";
for(TableAttr attr: attrList) {
attr_sql +=attr.getColumnName() +",";
}
attr_sql = attr_sql.substring(0,attr_sql.length() - 1);
subscribe_sql = subscribe_sql.replaceAll("\\?4", attr_sql );
subscribe_sql = subscribe_sql.replaceAll("\\?5", tableName+"_TEMP" );
write(subscribe_sql);
String activate_subscription_sql = activate_subscription.replaceAll("\\?",tableName+"_SUB");
write(activate_subscription_sql);
}
}
public static void getPreSQLInfo() throws Exception{
writerBuffer = new BufferedWriter(new FileWriter(new File("/opt/sql.txt")));
InputStream inputStram = ScriptFactory.class.getResourceAsStream("/init.sql");
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStram));
String Info = "-- Start Auto Compile SQL -- \r\n"
+ " -- GoldenBridge --\r\n";
writerBuffer.write(Info);
while( Info != null) {
Info = bufferedReader.readLine();
if(Info != null)
writerBuffer.write(Info+"\r\n");
}
bufferedReader.close();
}
public static void close() throws Exception{
writerBuffer.flush();
writerBuffer.close();
}
public static void write(String info) throws Exception {
writerBuffer.write(info +"\r\n");
}
public static void getDeleteSQLFile() {
String sql1="cdc_change_tables";//查询改变的表
String sql2="all_change_sets";//查询改变的集合
String sql3="dba_capture_prepared_tables";//查询实例化的表
//cdc_subscribers$ 订阅者表信息
}
public static void main(String[] args) throws Exception{
String result = TABLE_INSTANTIATION.replaceAll("\\?", "TEST");
System.out.println(result);
}
}9.自动生成存储过程核心类ProcedureFactory.java
/**
*
*/
package com.goldenbridge.dbcenter.generatesql.script;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.goldenbridge.dbcenter.generatesql.bean.TableAttr;
import com.goldenbridge.dbcenter.generatesql.utils.Params;
/**
*@author create by pengweikang
*@date 2018年3月29日--下午4:20:15
*@problem
*@answer
*@action
*/
public class ProcedureFactory {
public static BufferedWriter bufferedWriter =null;
public static String dbLink = Params.getValue("dblink");
private String procedure_module = "create or replace procedure sycn_test is\n" +
" type t_cur is REF CURSOR;\n" +
" v_id test_temp.id%type;\n" +
" errorException exception;\n" +
" v_name test_temp.name%type;\n" +
" v_mark test_temp.mark%type;\n" +
" v_operation$ test_temp.operation$%type;\n" +
" \n" +
" tp_id test_temp.id%type;\n" +
" tp_name test_temp.name%type;\n" +
" tp_mark test_temp.mark%type;\n" +
" usrs t_cur;\n" +
"begin\n" +
" begin\n" +
" dbms_cdc_subscribe.purge_window(subscription_name=>'CDC_SCOTT_SUB');\n" +
" end;\n" +
" begin\n" +
" dbms_cdc_subscribe.extend_window(subscription_name=>'CDC_SCOTT_SUB');\n" +
" end;\n" +
" open usrs for select t.operation$,t.id,t.name,t.mark from test_temp t order by t.commit_timestamp$ asc,t.operation$ desc; \n" +
" loop\n" +
" fetch usrs into v_operation$,v_id,v_name,v_mark;\n" +
" exit when usrs%notfound;\n" +
" if v_operation$ = 'I' then --执行新增\n" +
" dbms_output.put_line(v_id|| ',' ||v_name || ',' || v_mark);\n" +
" insert into test_@sycn_db(id,name,mark) values(v_id,v_name,v_mark);\n" +
" elsif v_operation$ = 'UO' then --获取更新信息\n" +
" tp_id :=v_id;\n" +
" tp_name := v_name;\n" +
" tp_mark := v_mark;\n" +
" elsif v_operation$ = 'UN' then --执行更新\n" +
" update test_@sycn_db set id=v_id,name=v_name,mark=v_mark where id=tp_id and name=tp_name and mark=tp_mark;\n" +
" elsif v_operation$ = 'D' then --执行删除\n" +
" delete from test_@sycn_db where id=v_id and name=v_name and mark=v_mark;\n" +
" end if;\n" +
" end loop;\n" +
" close usrs;\n" +
" commit;\n" +
" exception \n" +
" when errorException then\n" +
" dbms_output.put_line('数据异常');\n" +
"end sycn_test;";
private static final String ACCOUNT = Params.getValue("jdbc.username");
public static void getProcedureSql(Map<String,List<TableAttr>> tableMap,Map<String,String> primaryKey) throws Exception{
bufferedWriter = new BufferedWriter(new FileWriter(new File("/opt/sql_procedure.txt")));
Set<String> tableSet = tableMap.keySet();
for(String tableName : tableSet) {
List<TableAttr> tableAttrs = tableMap.get(tableName);
String startCode = tableName.substring(0,1);
String DataSetName = "CDC_"+ACCOUNT+"_"+startCode;
StringBuffer strBuffer = new StringBuffer();
strBuffer.append( "create or replace procedure CDC_"+ACCOUNT+"_"+tableName+" is\r\n");
strBuffer.append("type t_cur is REF CURSOR;\r\n");
strBuffer.append("errorException exception;\r\n");
String countList = "";
String column_attr_List = "";
String update_column_List = "";
String v_column_attr_List = "";
String column_list = "";
String newOldUpdateColumn = "";
String setupdateSql= "";
String updateWhereSql = "";
for(TableAttr attr : tableAttrs) {
column_attr_List += "V_"+attr.getColumnName()+" "+tableName+"_TEMP."+attr.getColumnName()+"%type;\r\n";
update_column_List += "TP_"+attr.getColumnName()+" "+tableName+"_TEMP."+attr.getColumnName()+"%type;\r\n";
countList += "t."+attr.getColumnName()+",";
v_column_attr_List += "V_"+attr.getColumnName()+",";
column_list += attr.getColumnName()+",";
newOldUpdateColumn += " TP_"+attr.getColumnName()+" := V_"+attr.getColumnName()+";\r\n";
setupdateSql += attr.getColumnName()+"=V_"+attr.getColumnName()+",";
updateWhereSql += attr.getColumnName()+"=TP_"+attr.getColumnName()+",";
}
String updateOrDeleteWhereSql = "";
if(primaryKey.get(tableName)!= null) {
updateOrDeleteWhereSql = primaryKey.get(tableName)+"=TP_"+primaryKey.get(tableName);
}else {
updateOrDeleteWhereSql = updateWhereSql.substring(0,updateWhereSql.length() - 1);
}
column_list = column_list.substring(0,column_list.length() - 1);
setupdateSql = setupdateSql.substring(0,setupdateSql.length() - 1);
countList = countList.substring(0,countList.length() - 1);
v_column_attr_List = v_column_attr_List.substring(0,v_column_attr_List.length() - 1);
strBuffer.append(column_attr_List);
strBuffer.append(" v_operation$ "+tableName+"_TEMP.operation$%type;\r\n");
strBuffer.append(update_column_List);
strBuffer.append("usrs t_cur;\r\n");
strBuffer.append("begin\r\n" +
" begin\r\n" +
" dbms_cdc_subscribe.purge_window(subscription_name=>'"+tableName+"_SUB"+"');\r\n" +
" end;\r\n" +
" begin\r\n" +
" dbms_cdc_subscribe.extend_window(subscription_name=>'"+tableName+"_SUB"+"');\r\n" +
" end;");
strBuffer.append(" open usrs for select t.operation$,"+countList+" from "+tableName+"_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc; \r\n");
strBuffer.append(" loop\r\n" +
" fetch usrs into v_operation$,"+v_column_attr_List+";\r\n" +
" exit when usrs%notfound;\r\n");
strBuffer.append(" if v_operation$ = 'I' then --执行新增\r\n" +
" insert into "+tableName+"@"+dbLink+"("+column_list+") values("+v_column_attr_List+");\r\n" +
" elsif v_operation$ = 'UO' then --获取更新信息\r\n" +
newOldUpdateColumn+
" elsif v_operation$ = 'UN' then --执行更新\r\n" +
" update "+tableName+"@"+dbLink+" set "+setupdateSql+" where "+updateOrDeleteWhereSql+";\r\n" +
" elsif v_operation$ = 'D' then --执行删除\r\n" +
" delete from "+tableName+"@"+dbLink+" where "+updateOrDeleteWhereSql+";\r\n" +
" end if;\r\n");
strBuffer.append(" end loop;\r\n" +
" close usrs;\r\n" +
" commit;\r\n" +
" exception \r\n" +
" when errorException then\r\n" +
" dbms_output.put_line('数据异常');\r\n" +
"end CDC_"+ACCOUNT+"_"+tableName+";\r\n\r\n");
bufferedWriter.write(strBuffer.toString());
}
close();
}
public static void close() throws IOException {
bufferedWriter.flush();
bufferedWriter.close();
}
}
/**
*
*/
package com.goldenbridge.dbcenter.generatesql.script;
/**
*@author create by pengweikang
*@date 2018年3月30日--下午4:11:58
*@problem
*@answer
*@action
*/
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.goldenbridge.dbcenter.generatesql.bean.TableAttr;
import com.goldenbridge.dbcenter.generatesql.utils.Params;
public class DeleteAllFactory {
public static BufferedWriter writerBuffer = null;
private static final String ACCOUNT = Params.getValue("jdbc.username");
private static List<String> CHANGE_SET_LIST = new ArrayList<String>();
public static void generatorSql(Map<String, List<TableAttr>> mapTable) throws Exception {
writerBuffer = new BufferedWriter(new FileWriter(new File("/opt/delete.txt")));
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("conn "+Params.subscriberAccount +"/"+Params.subscriberPasswd+"\r\n");
Set<String> keySet = mapTable.keySet();
String abort_table_instantiation = "";
String drop_change_table = "";
String drop_change_set = "";
for(String tableName : keySet) {
strBuffer.append("exec dbms_cdc_subscribe.drop_subscription('"+tableName+"_SUB');\r\n");
abort_table_instantiation += "exec dbms_capture_adm.abort_table_instantiation('"+ACCOUNT+"."+tableName+"');\r\n";
drop_change_table+= "exec dbms_cdc_publish.drop_change_table('"+Params.publisherAccount+"', 'CDC_"+tableName+"', 'Y');\r\n";
String startCode = tableName.substring(0,1);
String DataSetName = "CDC_"+ACCOUNT+"_"+startCode;
boolean contain = CHANGE_SET_LIST.contains(DataSetName);
if(!contain) {
CHANGE_SET_LIST.add(DataSetName);
drop_change_set += "exec dbms_cdc_publish.drop_change_set('"+DataSetName+"');\r\n";
}
}
strBuffer.append("conn / as sysdba\r\n");
strBuffer.append(abort_table_instantiation);
strBuffer.append(drop_change_table);
strBuffer.append(drop_change_set);
writerBuffer.write(strBuffer.toString());
writerBuffer.flush();
writerBuffer.close();
}
}
11.到此就完成了所有的功能，接下来我们进入测试，首先我们查看一下SCOTT用户中有哪些表，如下图：
执行Main.java文件后生成三个txt脚本文件如下图：
sql.txt文件内容如下：
-- Start Auto Compile SQL --
-- GoldenBridge --
select * from v$version;
show parameter job_que;
show parameter streams_pool_size;
show parameter sga_targ;
show parameter memory_targ;
show parameter java_pool;
alter system set streams_pool_size=512m;
show parameter java_pool;
alter system set java_pool_size=512m;
show parameter undo_ret;
alter system set undo_retention=3600;
show parameter streams_pool;
show parameter java_pool;
show parameter undo_re;
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database add supplemental log data;
alter database open;
grant all on scott.TEST to cdc_publisher;
grant all on scott.BONUS to cdc_publisher;
grant all on scott.EMP to cdc_publisher;
grant all on scott.DEPT to cdc_publisher;
grant all on scott.SALGRADE to cdc_publisher;
commit;
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'scott.TEST');
EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_scott_T', description => 'Change set for CDC CDC_scott_T', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5);
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'CDC_TEST',
change_set_name => 'CDC_scott_T',
source_schema => 'scott',
source_table => 'TEST',
column_type_list => 'ID NUMBER(22),NAME VARCHAR2(30),MARK VARCHAR2(50)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE RECOGNIZE_DATA');
END;
/
EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => 'CDC_scott_T',enable_capture => 'Y');
grant select on CDC_TEST to cdc_subscriber;
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'scott.BONUS');
EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_scott_B', description => 'Change set for CDC CDC_scott_B', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5);
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'CDC_BONUS',
change_set_name => 'CDC_scott_B',
source_schema => 'scott',
source_table => 'BONUS',
column_type_list => 'ENAME VARCHAR2(10),JOB VARCHAR2(9),SAL NUMBER(22),COMM NUMBER(22)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE RECOGNIZE_DATA');
END;
/
EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => 'CDC_scott_B',enable_capture => 'Y');
grant select on CDC_BONUS to cdc_subscriber;
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'scott.EMP');
EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_scott_E', description => 'Change set for CDC CDC_scott_E', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5);
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'CDC_EMP',
change_set_name => 'CDC_scott_E',
source_schema => 'scott',
source_table => 'EMP',
column_type_list => 'EMPNO NUMBER(22),ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(22),HIREDATE DATE,SAL NUMBER(22),COMM NUMBER(22),DEPTNO NUMBER(22)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE RECOGNIZE_DATA');
END;
/
EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => 'CDC_scott_E',enable_capture => 'Y');
grant select on CDC_EMP to cdc_subscriber;
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'scott.DEPT');
EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_scott_D', description => 'Change set for CDC CDC_scott_D', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5);
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'CDC_DEPT',
change_set_name => 'CDC_scott_D',
source_schema => 'scott',
source_table => 'DEPT',
column_type_list => 'DEPTNO NUMBER(22),DNAME VARCHAR2(14),LOC VARCHAR2(13)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE RECOGNIZE_DATA');
END;
/
EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => 'CDC_scott_D',enable_capture => 'Y');
grant select on CDC_DEPT to cdc_subscriber;
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'scott.SALGRADE');
EXEC DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_scott_S', description => 'Change set for CDC CDC_scott_S', change_source_name => 'HOTLOG_SOURCE', stop_on_ddl => 'y', begin_date => sysdate, end_date => sysdate+5);
BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
owner => 'cdc_publisher',
change_table_name => 'CDC_SALGRADE',
change_set_name => 'CDC_scott_S',
source_schema => 'scott',
source_table => 'SALGRADE',
column_type_list => 'GRADE NUMBER(22),LOSAL NUMBER(22),HISAL NUMBER(22)',
capture_values => 'both',
rs_id => 'y',
row_id => 'n',
user_id => 'n',
timestamp => 'n',
object_id => 'n',
source_colmap => 'n',
target_colmap => 'y',
options_string => 'TABLESPACE RECOGNIZE_DATA');
END;
/
EXEC DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(change_set_name => 'CDC_scott_S',enable_capture => 'Y');
grant select on CDC_SALGRADE to cdc_subscriber;
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_scott_T',
description=>'cdc cdc scott subx',
subscription_name=>'TEST_SUB');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'TEST_SUB',
source_schema=>'scott',
source_table=>'TEST',
column_list=>'ID,NAME,MARK',
subscriber_view=>'TEST_TEMP');
END;
/
EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'TEST_SUB');
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_scott_B',
description=>'cdc cdc scott subx',
subscription_name=>'BONUS_SUB');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'BONUS_SUB',
source_schema=>'scott',
source_table=>'BONUS',
column_list=>'ENAME,JOB,SAL,COMM',
subscriber_view=>'BONUS_TEMP');
END;
/
EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'BONUS_SUB');
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_scott_E',
description=>'cdc cdc scott subx',
subscription_name=>'EMP_SUB');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'EMP_SUB',
source_schema=>'scott',
source_table=>'EMP',
column_list=>'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO',
subscriber_view=>'EMP_TEMP');
END;
/
EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'EMP_SUB');
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_scott_D',
description=>'cdc cdc scott subx',
subscription_name=>'DEPT_SUB');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'DEPT_SUB',
source_schema=>'scott',
source_table=>'DEPT',
column_list=>'DEPTNO,DNAME,LOC',
subscriber_view=>'DEPT_TEMP');
END;
/
EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'DEPT_SUB');
BEGIN
dbms_cdc_subscribe.create_subscription(
change_set_name=>'CDC_scott_S',
description=>'cdc cdc scott subx',
subscription_name=>'SALGRADE_SUB');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe(
subscription_name=>'SALGRADE_SUB',
source_schema=>'scott',
source_table=>'SALGRADE',
column_list=>'GRADE,LOSAL,HISAL',
subscriber_view=>'SALGRADE_TEMP');
END;
/
EXEC dbms_cdc_subscribe.activate_subscription(subscription_name=>'SALGRADE_SUB');
delete.txt文件内容如下：
conn cdc_subscriber/cdc_subscriber
exec dbms_cdc_subscribe.drop_subscription('TEST_SUB');
exec dbms_cdc_subscribe.drop_subscription('BONUS_SUB');
exec dbms_cdc_subscribe.drop_subscription('EMP_SUB');
exec dbms_cdc_subscribe.drop_subscription('DEPT_SUB');
exec dbms_cdc_subscribe.drop_subscription('SALGRADE_SUB');
conn / as sysdba
exec dbms_capture_adm.abort_table_instantiation('scott.TEST');
exec dbms_capture_adm.abort_table_instantiation('scott.BONUS');
exec dbms_capture_adm.abort_table_instantiation('scott.EMP');
exec dbms_capture_adm.abort_table_instantiation('scott.DEPT');
exec dbms_capture_adm.abort_table_instantiation('scott.SALGRADE');
exec dbms_cdc_publish.drop_change_table('cdc_publisher', 'CDC_TEST', 'Y');
exec dbms_cdc_publish.drop_change_table('cdc_publisher', 'CDC_BONUS', 'Y');
exec dbms_cdc_publish.drop_change_table('cdc_publisher', 'CDC_EMP', 'Y');
exec dbms_cdc_publish.drop_change_table('cdc_publisher', 'CDC_DEPT', 'Y');
exec dbms_cdc_publish.drop_change_table('cdc_publisher', 'CDC_SALGRADE', 'Y');
exec dbms_cdc_publish.drop_change_set('CDC_scott_T');
exec dbms_cdc_publish.drop_change_set('CDC_scott_B');
exec dbms_cdc_publish.drop_change_set('CDC_scott_E');
exec dbms_cdc_publish.drop_change_set('CDC_scott_D');
exec dbms_cdc_publish.drop_change_set('CDC_scott_S');
sql_procedure.txt文件内容如下：
create or replace procedure CDC_scott_TEST is
type t_cur is REF CURSOR;
errorException exception;
V_ID TEST_TEMP.ID%type;
V_NAME TEST_TEMP.NAME%type;
V_MARK TEST_TEMP.MARK%type;
v_operation$ TEST_TEMP.operation$%type;
TP_ID TEST_TEMP.ID%type;
TP_NAME TEST_TEMP.NAME%type;
TP_MARK TEST_TEMP.MARK%type;
usrs t_cur;
begin
begin
dbms_cdc_subscribe.purge_window(subscription_name=>'TEST_SUB');
end;
begin
dbms_cdc_subscribe.extend_window(subscription_name=>'TEST_SUB');
end; open usrs for select t.operation$,t.ID,t.NAME,t.MARK from TEST_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc;
loop
fetch usrs into v_operation$,V_ID,V_NAME,V_MARK;
exit when usrs%notfound;
if v_operation$ = 'I' then --执行新增
insert into TEST@sycn_db(ID,NAME,MARK) values(V_ID,V_NAME,V_MARK);
elsif v_operation$ = 'UO' then --获取更新信息
TP_ID := V_ID;
TP_NAME := V_NAME;
TP_MARK := V_MARK;
elsif v_operation$ = 'UN' then --执行更新
update TEST@sycn_db set ID=V_ID,NAME=V_NAME,MARK=V_MARK where ID=TP_ID,NAME=TP_NAME,MARK=TP_MARK;
elsif v_operation$ = 'D' then --执行删除
delete from TEST@sycn_db where ID=TP_ID,NAME=TP_NAME,MARK=TP_MARK;
end if;
end loop;
close usrs;
commit;
exception
when errorException then
dbms_output.put_line('数据异常');
end CDC_scott_TEST;
create or replace procedure CDC_scott_BONUS is
type t_cur is REF CURSOR;
errorException exception;
V_ENAME BONUS_TEMP.ENAME%type;
V_JOB BONUS_TEMP.JOB%type;
V_SAL BONUS_TEMP.SAL%type;
V_COMM BONUS_TEMP.COMM%type;
v_operation$ BONUS_TEMP.operation$%type;
TP_ENAME BONUS_TEMP.ENAME%type;
TP_JOB BONUS_TEMP.JOB%type;
TP_SAL BONUS_TEMP.SAL%type;
TP_COMM BONUS_TEMP.COMM%type;
usrs t_cur;
begin
begin
dbms_cdc_subscribe.purge_window(subscription_name=>'BONUS_SUB');
end;
begin
dbms_cdc_subscribe.extend_window(subscription_name=>'BONUS_SUB');
end; open usrs for select t.operation$,t.ENAME,t.JOB,t.SAL,t.COMM from BONUS_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc;
loop
fetch usrs into v_operation$,V_ENAME,V_JOB,V_SAL,V_COMM;
exit when usrs%notfound;
if v_operation$ = 'I' then --执行新增
insert into BONUS@sycn_db(ENAME,JOB,SAL,COMM) values(V_ENAME,V_JOB,V_SAL,V_COMM);
elsif v_operation$ = 'UO' then --获取更新信息
TP_ENAME := V_ENAME;
TP_JOB := V_JOB;
TP_SAL := V_SAL;
TP_COMM := V_COMM;
elsif v_operation$ = 'UN' then --执行更新
update BONUS@sycn_db set ENAME=V_ENAME,JOB=V_JOB,SAL=V_SAL,COMM=V_COMM where ENAME=TP_ENAME,JOB=TP_JOB,SAL=TP_SAL,COMM=TP_COMM;
elsif v_operation$ = 'D' then --执行删除
delete from BONUS@sycn_db where ENAME=TP_ENAME,JOB=TP_JOB,SAL=TP_SAL,COMM=TP_COMM;
end if;
end loop;
close usrs;
commit;
exception
when errorException then
dbms_output.put_line('数据异常');
end CDC_scott_BONUS;
create or replace procedure CDC_scott_EMP is
type t_cur is REF CURSOR;
errorException exception;
V_EMPNO EMP_TEMP.EMPNO%type;
V_ENAME EMP_TEMP.ENAME%type;
V_JOB EMP_TEMP.JOB%type;
V_MGR EMP_TEMP.MGR%type;
V_HIREDATE EMP_TEMP.HIREDATE%type;
V_SAL EMP_TEMP.SAL%type;
V_COMM EMP_TEMP.COMM%type;
V_DEPTNO EMP_TEMP.DEPTNO%type;
v_operation$ EMP_TEMP.operation$%type;
TP_EMPNO EMP_TEMP.EMPNO%type;
TP_ENAME EMP_TEMP.ENAME%type;
TP_JOB EMP_TEMP.JOB%type;
TP_MGR EMP_TEMP.MGR%type;
TP_HIREDATE EMP_TEMP.HIREDATE%type;
TP_SAL EMP_TEMP.SAL%type;
TP_COMM EMP_TEMP.COMM%type;
TP_DEPTNO EMP_TEMP.DEPTNO%type;
usrs t_cur;
begin
begin
dbms_cdc_subscribe.purge_window(subscription_name=>'EMP_SUB');
end;
begin
dbms_cdc_subscribe.extend_window(subscription_name=>'EMP_SUB');
end; open usrs for select t.operation$,t.EMPNO,t.ENAME,t.JOB,t.MGR,t.HIREDATE,t.SAL,t.COMM,t.DEPTNO from EMP_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc;
loop
fetch usrs into v_operation$,V_EMPNO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DEPTNO;
exit when usrs%notfound;
if v_operation$ = 'I' then --执行新增
insert into EMP@sycn_db(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(V_EMPNO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DEPTNO);
elsif v_operation$ = 'UO' then --获取更新信息
TP_EMPNO := V_EMPNO;
TP_ENAME := V_ENAME;
TP_JOB := V_JOB;
TP_MGR := V_MGR;
TP_HIREDATE := V_HIREDATE;
TP_SAL := V_SAL;
TP_COMM := V_COMM;
TP_DEPTNO := V_DEPTNO;
elsif v_operation$ = 'UN' then --执行更新
update EMP@sycn_db set EMPNO=V_EMPNO,ENAME=V_ENAME,JOB=V_JOB,MGR=V_MGR,HIREDATE=V_HIREDATE,SAL=V_SAL,COMM=V_COMM,DEPTNO=V_DEPTNO where EMPNO=TP_EMPNO;
elsif v_operation$ = 'D' then --执行删除
delete from EMP@sycn_db where EMPNO=TP_EMPNO;
end if;
end loop;
close usrs;
commit;
exception
when errorException then
dbms_output.put_line('数据异常');
end CDC_scott_EMP;
create or replace procedure CDC_scott_DEPT is
type t_cur is REF CURSOR;
errorException exception;
V_DEPTNO DEPT_TEMP.DEPTNO%type;
V_DNAME DEPT_TEMP.DNAME%type;
V_LOC DEPT_TEMP.LOC%type;
v_operation$ DEPT_TEMP.operation$%type;
TP_DEPTNO DEPT_TEMP.DEPTNO%type;
TP_DNAME DEPT_TEMP.DNAME%type;
TP_LOC DEPT_TEMP.LOC%type;
usrs t_cur;
begin
begin
dbms_cdc_subscribe.purge_window(subscription_name=>'DEPT_SUB');
end;
begin
dbms_cdc_subscribe.extend_window(subscription_name=>'DEPT_SUB');
end; open usrs for select t.operation$,t.DEPTNO,t.DNAME,t.LOC from DEPT_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc;
loop
fetch usrs into v_operation$,V_DEPTNO,V_DNAME,V_LOC;
exit when usrs%notfound;
if v_operation$ = 'I' then --执行新增
insert into DEPT@sycn_db(DEPTNO,DNAME,LOC) values(V_DEPTNO,V_DNAME,V_LOC);
elsif v_operation$ = 'UO' then --获取更新信息
TP_DEPTNO := V_DEPTNO;
TP_DNAME := V_DNAME;
TP_LOC := V_LOC;
elsif v_operation$ = 'UN' then --执行更新
update DEPT@sycn_db set DEPTNO=V_DEPTNO,DNAME=V_DNAME,LOC=V_LOC where DEPTNO=TP_DEPTNO;
elsif v_operation$ = 'D' then --执行删除
delete from DEPT@sycn_db where DEPTNO=TP_DEPTNO;
end if;
end loop;
close usrs;
commit;
exception
when errorException then
dbms_output.put_line('数据异常');
end CDC_scott_DEPT;
create or replace procedure CDC_scott_SALGRADE is
type t_cur is REF CURSOR;
errorException exception;
V_GRADE SALGRADE_TEMP.GRADE%type;
V_LOSAL SALGRADE_TEMP.LOSAL%type;
V_HISAL SALGRADE_TEMP.HISAL%type;
v_operation$ SALGRADE_TEMP.operation$%type;
TP_GRADE SALGRADE_TEMP.GRADE%type;
TP_LOSAL SALGRADE_TEMP.LOSAL%type;
TP_HISAL SALGRADE_TEMP.HISAL%type;
usrs t_cur;
begin
begin
dbms_cdc_subscribe.purge_window(subscription_name=>'SALGRADE_SUB');
end;
begin
dbms_cdc_subscribe.extend_window(subscription_name=>'SALGRADE_SUB');
end; open usrs for select t.operation$,t.GRADE,t.LOSAL,t.HISAL from SALGRADE_TEMP t order by t.commit_timestamp$ asc,t.operation$ desc;
loop
fetch usrs into v_operation$,V_GRADE,V_LOSAL,V_HISAL;
exit when usrs%notfound;
if v_operation$ = 'I' then --执行新增
insert into SALGRADE@sycn_db(GRADE,LOSAL,HISAL) values(V_GRADE,V_LOSAL,V_HISAL);
elsif v_operation$ = 'UO' then --获取更新信息
TP_GRADE := V_GRADE;
TP_LOSAL := V_LOSAL;
TP_HISAL := V_HISAL;
elsif v_operation$ = 'UN' then --执行更新
update SALGRADE@sycn_db set GRADE=V_GRADE,LOSAL=V_LOSAL,HISAL=V_HISAL where GRADE=TP_GRADE,LOSAL=TP_LOSAL,HISAL=TP_HISAL;
elsif v_operation$ = 'D' then --执行删除
delete from SALGRADE@sycn_db where GRADE=TP_GRADE,LOSAL=TP_LOSAL,HISAL=TP_HISAL;
end if;
end loop;
close usrs;
commit;
exception
when errorException then
dbms_output.put_line('数据异常');
end CDC_scott_SALGRADE;
存在问题：
1.没有自动创建定时任务。
2.目前有些属性参数为写死状态，没有动态话。
githup地址：GITHUP地址
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
