20230115

internal class Program
{
string strSQL;

//表示SQL
string selTN = "DEPT_BK"; //表示テーブル名
string strSQL_SEL = "select * from ";
//string strSQL_SEL2 = " order by DEPTNO";

//新規追加SQL(テーブル複製)

string creTN = "DEPT_BK"; //複製テーブル名
string strSQL_CRET;
string sql_creTbl = "create table ";
string sql_asSel = " as select * from DEPT";

//削除SQL
string drpTN = "DEPT_BK"; //削除テーブル名
string strSQL_DRP = "drop table ";

//distinct
string strSQL_DIST;

//更新SQL
string strSQL_UP;
string dbkTN = "DEPT_BK DBK";
string dTN = "DEPT D";
string sql_ON = "(DBK.DEPTNO = D.DEPTNO and DBK.KOUSHINBI < D.KOUSHINBI)";

string col1;
string col2;
string col3;
string col4;
string col5;
string col6;

string whereON;

string RTN;
string XTN;

string strSQL_DTCHK;
string strSQL_MRG;
string strSQL_INS; //INSERT SQL文

string strSQL_MINUS;

//DataView dv = new DataView(dt);
//DataTable dt2 = dv.ToTable(true);
//int i = 0;
//int j = 0;

///

/// MAIN
///

///
static void Main(string[] args)
{
Console.WriteLine("--- STRAT ---\r\n" + DateTime.Now + "\r\n");

Program pg = new Program();
DataTable dt = new DataTable();

//★実行内容★
pg.B_TableMarge();
//pg.OraTableDrop(); //テーブル削除
//pg.OraCopyCreate(); //テーブル複製

//pg.Count();
//pg.MINUS();
//pg.TableDistinct();
//dt = pg.OraMarge();

Console.WriteLine(DateTime.Now + "\r\n--- END ---\r\n");
Console.ReadKey();
}

public void B_TableMarge()
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn();

string repTN = "DEPT_BK R";
string xbbTN = "DEPT X";

col1 = "DEPTNO";

whereON =
"R.KOUSHINBI < X.KOUSHINBI" +
" and R." + col1 + " = X." + col1;

int colNum = 1;//★テーブルごとに設定★

strSQL_INS = "insert into " + repTN +
" select * from " + xbbTN +
" where not exists(select 'X' from " + repTN +
" where R." + col1 + " = X." + col1 +
")";

//全件数カウント
CNT(repTN,xbbTN);

//MARGE実行(差分抽出→項目更新→日付更新)
MRG(repTN, xbbTN, colNum);

//INSERTt実行
INS();
}

public void CNT(string repTN,string xbbTN)
{
DataTable dt = new DataTable();

Console.WriteLine("■件数カウント開始");

//レプリカテーブル全件数
strSQL = "select count(*) from " + repTN; ;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(repTN + " 全" + dt.Rows[0][0] + "行");

//元テーブル全件数
strSQL = "select count(*) from " + xbbTN; ;
dt = DBA(strSQL); //SQL実行
Console.WriteLine(xbbTN + " 全" + dt.Rows[0][0] + "行\r\n");
}

public void MRG(string repTN, string xbbTN, int colNum)
{
DataTable dt = new DataTable();
int i = 1;

Console.WriteLine("■MAREGE開始");

//差分抽出
dt = TableMinus(repTN, xbbTN);

if (dt.Rows.Count != 0)
{
Console.WriteLine("実行...\r\n");

foreach (DataColumn dc in dt.Columns)

{
strSQL_MRG =
"merge into " + repTN +
" using " + xbbTN +
" on (" + whereON + ")" +
" when matched then update set " +
"R." + dc.ColumnName + " = X." + dc.ColumnName;

strSQL = strSQL_MRG;

switch (colNum)
{
case 1:

if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1)
{
Console.WriteLine(i + ":" + dc.ColumnName + " ON句条件は更新不可\r\n");
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");
dt = DBA(strSQL);
}
i++; //次の列へ

break;

case 2:

if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1 || dc.ColumnName == col2)
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新不可\r\n");
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");
dt = DBA(strSQL);
}
i++; //次の列へ

break;
}
}

//日付を更新
switch (colNum)
{

case 1:

whereON = "R." + col1 + " = X." + col1;
UpdtMarge(repTN, xbbTN, whereON);

break;

case 2:

whereON = "R." + col1 + " = X." + col1 +
" and R." + col2 + " = X." + col2;

UpdtMarge(repTN, xbbTN, whereON);

break;

case 3:
break;
}
}

else
{
Console.WriteLine("更新なし\r\n");
}

}

public DataTable TableMinus(string repTN, string xbbTN)
{
DataTable dt = new DataTable();

Console.WriteLine("差分抽出開始...\r\n");

strSQL_MINUS =
"select * from " + repTN +
" minus select * from " + xbbTN;

strSQL = strSQL_MINUS;
dt = DBA(strSQL); //SQL実行

Console.WriteLine(dt.Rows.Count + "行が更新対象\r\n");

return dt;
}

public void UpdtMarge(string repTN,string xbbTN,string whereON)
{
DataTable dt = new DataTable();

Console.WriteLine("■日付を更新...");

strSQL_MRG =
"merge into " + repTN +//strign dbkTN
" using " + xbbTN +//dTN
" on (" + whereON + ")" + //sql_ON
" when matched then update set " +
"R.KOUSHINBI = X.KOUSHINBI";

strSQL = strSQL_MRG;
Console.WriteLine(strSQL);
Console.WriteLine("更新完了\r\n");
dt = DBA(strSQL);

}

public DataTable INS()
{
Console.WriteLine("■Insert実行");

string repTN = "DEPT_BK R";
string xbbTN = "DEPT X";

DataTable dt = new DataTable();
strSQL = " select * from " + xbbTN +
" where not exists(select 'X' from " + repTN +
" where R." + col1 + " = X." + col1 +
")";
dt = DBA(strSQL);

Console.WriteLine("対象 " + dt.Rows.Count + "件\r\n");

if(dt.Rows.Count== 0)
{
Console.WriteLine("新規追加なし\r\n");
}
else
{
strSQL = strSQL_INS;
dt = DBA(strSQL);
Console.WriteLine("完了\r\n");
}

return dt;
}