・summryの追加
・テーブル名で変数化
・関数の作成(select,update,insert,creat,drop)
・case文の追加、変数化
■VScode拡張機能
・Oracle Developer Tools for VS Code
VSCode DB接続
で検索
■Visual studio拡張機能
・Toggle Comment
Ctrl＋/でコメントアウト
・Oracle Developer Tools for Visual Studio
■参考**
超倍速！？複数のレコードの更新を1回で実施するbulk update！バルクアップデート
で検索
■C#(一度VScodeに貼り付け)
public Form1()
{
InitializeComponent();
}
string strSQL;
//表示SQL
string selTN = "DEPT_BK"; //表示テーブル名
string strSQL_SEL = "select * from ";
//string strSQL_SEL2 = " order by DEPTNO";
//INSERT SQL文
string strSQL_INS =
"INSERT INTO DEPT_BK SELECT * FROM DEPT D " +
"WHERE NOT EXISTS(SELECT * FROM DEPT_BK DBK WHERE D.DEPTNO = DBK.DEPTNO)";
//新規追加SQL(テーブル複製)
string creTN = "DEPT_BK"; //複製テーブル名
string strSQL_CRET;
string strSQL_CRET1 = "create table ";
string strSQL_CRET2 = " as select * from DEPT";
//削除SQL
string drpTN = "DEPT_BK"; //削除テーブル名
string strSQL_DRP = "drop table ";
//更新SQL
//string upTN = DEPT_BK3;
///
/// select table:DEPT
///
private void button1_Click_1(object sender, EventArgs e)
{
strSQL = strSQL_SEL + selTN;
dataGridView1.DataSource = OraTableSelect(strSQL);
/* Console.WriteLine("開始");
strSQL =
"select * " +
"from DEPT";
dataGridView1.DataSource = DBA2(strSQL);
Console.WriteLine("完了");*/
}
///
/// insert
///
///
///
private void button2_Click_1(object sender, EventArgs e)
{
OraTableInsert();
strSQL = strSQL_SEL + selTN;
dataGridView1.DataSource = OraTableSelect(strSQL);
}
///
///
///
/// update
///
private void button4_Click(object sender, EventArgs e)
{
OraTableUpdate();
strSQL = strSQL_SEL + selTN;
/* DataTable dt = new DataTable();
OraTableSelect(strSQL);
dataGridView1.DataSource = dt;*/
dataGridView1.DataSource = OraTableSelect(strSQL);
}
///
/// create table：新規テーブル追加(複製)
///
///
///
private void button5_Click(object sender, EventArgs e)
{
strSQL_CRET = strSQL_CRET1 + creTN + strSQL_CRET2;
string msg = strSQL_CRET + " を実行しますか？";
string msg_title = "新規テーブル作成";
// メッセージボックスを表示
DialogResult result = MessageBox.Show(msg, msg_title, MessageBoxButtons.YesNo);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Console.WriteLine(creTN + " creat実行...");
strSQL = strSQL_CRET;
DBA3(strSQL);
Console.WriteLine(creTN + " create完了");
strSQL = strSQL_SEL + creTN;
//strSQL = "select * from " + creTN + " order by DEPTNO";
dataGridView1.DataSource = OraTableSelect(strSQL);
}
else if (result == System.Windows.Forms.DialogResult.No)
{
//MessageBox.Show("いいえ");
}
}
///
/// drop table：テーブル削除
///
///
///
private void button6_Click(object sender, EventArgs e)
{
strSQL_DRP = strSQL_DRP + drpTN;
string msg = strSQL_DRP + " を実行しますか？";
string msg_title = "テーブル削除";
// メッセージボックスを表示
DialogResult result = MessageBox.Show(msg, msg_title, MessageBoxButtons.YesNo);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Console.WriteLine(drpTN + " drop実行...");
strSQL = strSQL_DRP;
DBA3(strSQL);
Console.WriteLine(drpTN + " drop完了");
}
else if (result == System.Windows.Forms.DialogResult.No)
{
//MessageBox.Show("いいえ");
}
}
///
/// 関数
///
///
///
public DataTable OraTableSelect(string strSQL)
{
Console.WriteLine(selTN + " select実行...");
DataTable dt = new DataTable();
dt = DBA2(strSQL);
Console.WriteLine(selTN + " select完了");
return dt;
}
public void OraTableUpdate()
{
Console.WriteLine("update開始...");
DataTable dt = new DataTable();
//データテーブル取得
strSQL = strSQL_SEL + selTN;
MessageBox.Show(strSQL, "確認", MessageBoxButtons.YesNo);
dt = OraTableSelect(strSQL);
//update実行
foreach (DataColumn dc in dt.Columns)
{
//strSQL = "UPDATE DEPT_BK DBK SET DBK.DNAME = 'AAAAA' ";
string upTN = " DEPT_BK DBK ";
string cpTN = " DEPT D ";
string sql_upWhere = " where DBK.DEPTNO = D.DEPTNO ";
string sql_upExis = "where exists (select * from " + cpTN + sql_upWhere + "and D.KOUSHINBI > DBK.KOUSHINBI)";
string sql_upCase =
"case when DBK.KOUSHINBI < (select D.KOUSHINBI from" + cpTN + sql_upWhere + ")" +
"then (select D." + dc.ColumnName + " from" + cpTN + sql_upWhere + ") END " + sql_upExis;
strSQL ="update" + upTN + "set DBK." + dc.ColumnName + " = " + sql_upCase;
//MessageBox.Show(strSQL, "確認", MessageBoxButtons.YesNo);
//case when [更新日比較] then [更新する値代入] end
//where exist(select * ～：対象行のみを条件とする
//参考：
//https://dream-target.jp/2018/02/17/130/
//https://dxo.co.jp/blog/archives/8971
Console.WriteLine(strSQL);
//strSQL = strSQL_UP;
dt = DBA2(strSQL);
Console.WriteLine(dc.ColumnName);
}
Console.WriteLine("update完了");
}
public void OraTableInsert()
{
Console.WriteLine("insert実行...");
DataTable dt = new DataTable();
strSQL = strSQL_INS;
dt = DBA2(strSQL);
Console.WriteLine("insert完了");
}
///
/// DBA2 SQL実行(dt)
///
public DataTable DBA2(string strSQL)
{
//接続文字列
string strConn =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";
DataTable dt = new DataTable();//データテーブル
using (OracleConnection conn = new OracleConnection(strConn))
using (OracleCommand cmd = new OracleCommand(strSQL, conn))
using (var da = new OracleDataAdapter(cmd))
{
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行");
da.Fill(dt);
}
return dt;
}
///
/// DBA3 SQL実行ExecuteNonQuery
///
public void DBA3(string strSQL)
{
//接続文字列
string strConn =
"User Id=SCOTT;" +
"Password=tiger;" +
"Data Source=" +
"(DESCRIPTION = " +
"(ADDRESS = (PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" +
"(CONNECT_DATA = " +
"(SERVER = DEDICATED) " +
"(SERVICE_NAME = XEPDB1)))";
//DataTable dt = new DataTable();//データテーブル
using (OracleConnection conn = new OracleConnection(strConn))
using (OracleCommand cmd = new OracleCommand(strSQL, conn))
//using (var da = new OracleDataAdapter(cmd))
{
conn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("SQL実行");
//da.Fill(dt);
}
//return dt;
//dataGridView1.DataSource = dt; //グリッドビューにデータ表示
}
■SQL
-
- 【OK】--
-
- 複数のテーブルをSELECTして作成する
CREATE TABLE DEPT_BK2 AS
SELECT
*
FROM
DEPT
-- https://www.projectgroup.info/tips/Oracle/SQL/SQL000027.html
CREATE TABLE NEW_TABLE_NAME AS
SELECT
TAB_A.COL1,
TAB_A.COL2,
TAB_B.COL1
FROM
TAB_A
LEFT OUTER JOIN TAB_B
ON TAB_B.KEY = TAB_A.KEY
WHERE
TAB_A.KEY = 'aaa'
-- 2つの表を比較して存在しない行をINSERTする
-- https://www.projectgroup.info/tips/SQLServer/SQL/SQL000001.html
INSERT INTO DEPT_BK
SELECT
*
FROM
DEPT D
WHERE
NOT EXISTS(
SELECT
'X' -- 'X'：これがキーワード '1'での可能
FROM
DEPT_BK DBK
WHERE
D.DEPTNO = DBK.DEPTNO
)
-- 【OK】--
UPDATE DEPT_BK DBK SET DBK.DNAME = CASE
WHEN DBK.KOUSHINBI < (
SELECT
D.KOUSHINBI
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
) THEN
(
SELECT
D.DNAME
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
AND D.KOUSHINBI > DBK.KOUSHINBI
)
END
WHERE
EXISTS (
SELECT
*
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
AND D.KOUSHINBI > DBK.KOUSHINBI
)
-- 【OK】--
-- 【OK】--
UPDATE DEPT_BK DBK SET DBK.DNAME = CASE
WHEN DBK.KOUSHINBI < (
SELECT
D.KOUSHINBI
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
) THEN
(
SELECT
D.DNAME
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
AND D.KOUSHINBI > DBK.KOUSHINBI
)
END
WHERE
EXISTS (
SELECT
*
FROM
DEPT D
WHERE
DBK.DEPTNO = D.DEPTNO
AND D.KOUSHINBI > DBK.KOUSHINBI
)
-- 【OK】--
STRSQL = "UPDATE DEPT_BK DBK SET DBK.DNAME = 'AAAAA' " STRSQL = "update DEPT_BK DBK " + "set DBK." + DC.COLUMNNAME + " = " + "case when DBK.KOUSHINBI < (select D.KOUSHINBI from DEPT D where DBK.DEPTNO = D.DEPTNO) " + "then (select D." + DC.COLUMNNAME + " from DEPT D where DBK.DEPTNO = D.DEPTNO) END " + "where exists (select * from DEPT D where DBK.DEPTNO = D.DEPTNO and D.KOUSHINBI > DBK.KOUSHINBI)";
