ChatGPTを活用したOracleDB技術検証

__ChatGPTを活用すればSQLが書けなくてもOracleDBの技術検証ができるのか！__

# １. ルール
* ChatGPT3.5(無償版)が出力したコマンドのみを実行
* OracleDB 19c
* 前提となる設定やオブジェクトは無し


# ２．検証内容

AWRレポートの分析でも重要なSQLの実行回数（executions）や実行時間（elapsed_time）。
これはどのタイミングでカウントされるのか。

![3_1_実行回数.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3491580/a8878f53-37e0-568a-f030-f15899041b9e.png)

 1. 実行開始時
 1. 実行完了時
 1. コミット時
 1. 実行中にロック待ちで待機したら？
 1. 実行がエラー・キャンセルされたら？
 1. 実行完了後にロールバックされたら？

# ３．検証結果
最初に結果。
Chat-GPTを利用すれば、SQLやSQL*Plusのコマンドが分からなくても技術検証が可能。

| No. | 処理 | 実行回数 | 実行時間 |
|:-:|:-:|:-:|:-:|
| 1  | 実行開始時  | ― | ― |
| 2  | 実行完了時  | ○ | ○ |
| 3  | コミット時  | ― | ― |
| 4  | ロック待ち  | ― | ○ |
| 5  | キャンセル  | ○ | ○ |
| 6  | ロールバック| ― | ― |

（○：カウントアップ、－：変化なし）


# ４．検証
## 凡例
::: note
Chat-GPTに対する質問
:::

::: note warn
Chat-GPTからの回答
:::

``` sql
OracleDBに対するコマンド実行
```
## 検証結果
### 初期状態準備

::: note warn
OracleDBで以下2カラムを持つt1表を作成するSQLを作成してください。
・c1：数値型・主キー
・c2：時刻型
:::

::: note 
OracleDBで以下のSQL文を使用して、2つのカラム（c1とc2）を持つt1テーブルを作成できます。c1は数値型の主キーであり、c2は時刻型です。

```sql
CREATE TABLE t1 (
  c1 NUMBER PRIMARY KEY,
  c2 TIMESTAMP
);
```

このSQL文によって、t1テーブルが作成されます。c1カラムは数値型の主キーとなりますので、重複した値を持つことができません。c2カラムは時刻型であり、日付と時間の情報を格納することができます。

:::
``` sql
SQL> CREATE TABLE t1 (
  2    c1 NUMBER PRIMARY KEY,
  3    c2 TIMESTAMP
  4  );

表が作成されました。
```

::: note warn
t1表に以下のデータを挿入するSQLを作成してください。
c1：1
c2：実行時点の時間
:::

::: note
以下のSQL文を使用して、t1テーブルに指定されたデータを挿入できます。実行時点の時間は`SYSTIMESTAMP`を使用して取得します。

```sql
INSERT INTO t1 (c1, c2)
VALUES (1, SYSTIMESTAMP);
```

このSQL文によって、c1カラムに1が、c2カラムに現在の実行時点の時間が挿入されます。SYSTIMESTAMP関数はデータベースのシステム日付と時間を返します。
:::

``` sql
SQL> INSERT INTO t1 (c1, c2)
  2  VALUES (1, SYSTIMESTAMP);

1行が作成されました。
```

::: note warn
SQLPLUSで以下の通り設定するコマンドを作成してください。
横幅：300文字
表示行数：1000行
:::

::: note
SQL*Plusで横幅を300文字、表示行数を1000行に設定するコマンドは以下のようになります。

```sql
SET LINESIZE 300
SET PAGESIZE 1000
```

これにより、SQL*Plusの出力結果が300文字幅で表示され、1ページに1000行まで表示されるようになります。
:::

``` sql
SQL> SET LINESIZE 300
SQL> SET PAGESIZE 1000
```

::: note warn
t1表の全てのデータを表示するSQLを作成してください。
:::

::: note
t1表のすべてのデータを表示するためのSQL文は次の通りです。

```sql
SELECT * FROM t1;
```

このSQL文を実行すると、t1表内のすべての行と列が表示されます。ただし、実際のデータベース内に多くの行がある場合、すべてのデータを表示すると出力が大きくなることに注意してください。必要な情報だけを表示するために、適切なカラムを選択するか、適切な条件を使用してデータをフィルタリングすることも考慮してください。
:::

``` sql
SQL> SELECT * FROM t1;

        C1 C2
---------- ---------------------------------------------------------------------------
         1 23-05-01 17:58:03.087448
```

## 検証用SQL

::: note warn
t1表に対し、c1が1である行のc2を実行時の時間に更新するSQLを作成してください。
:::

::: note
以下のSQL文を使用して、t1表においてc1が1である行のc2を実行時の時間に更新できます。

```sql
UPDATE t1
SET c2 = SYSTIMESTAMP
WHERE c1 = 1;
```

このSQL文によって、t1表のc1が1である行のc2カラムが現在の実行時点の時間に更新されます。UPDATE文では、SET句で更新対象のカラムと新しい値を指定し、WHERE句で更新する行を絞り込みます。上記のSQL文では、c1が1である行だけが対象となります。
:::

``` sql
SQL> UPDATE t1
  2  SET c2 = SYSTIMESTAMP
  3  WHERE c1 = 1;

1行が更新されました。
```
::: note warn
v$sqlから上記UPDATE文を対象に、以下の情報を出力するSQLを作成してください。
SQL_ID・SQL全文・解析回数・実行回数・実行時間
:::

::: note
以下のSQL文を使用して、v$sqlビューから指定したSQLを対象に、SQL_ID、SQL全文、解析回数、実行回数、実行時間を出力できます。

```sql
SELECT sql_id AS "SQL_ID",
       sql_fulltext AS "SQL全文",
       executions AS "実行回数",
       parse_calls AS "解析回数",
       elapsed_time / 1000000 AS "実行時間（秒）"
FROM v$sql
WHERE sql_fulltext LIKE '%UPDATE t1%SET c2 = SYSTIMESTAMP%WHERE c1 = 1%';
```

このSQL文では、`v$sql`ビューからSQL全文に指定したUPDATE文の部分を含むレコードを検索し、SQL_ID、SQL全文、解析回数、実行回数、実行時間を取得しています。`%`はワイルドカードを表すため、UPDATE文の中間に他の文字列があっても対象となります。ただし、この方法はSQL文が完全に一致しない場合にもマッチする可能性があるため、注意が必要です。必要に応じて正確なSQL文に合わせて適切にパターンマッチを調整してください。
:::

``` sql
SQL> SELECT sql_id AS "SQL_ID",
  2         sql_fulltext AS "SQL全文",
  3         executions AS "実行回数",
  4         parse_calls AS "解析回数",
  5         elapsed_time / 1000000 AS "実行時間（秒）"
  6  FROM v$sql
  7  WHERE sql_fulltext LIKE '%UPDATE t1%SET c2 = SYSTIMESTAMP%WHERE c1 = 1%';

SQL_ID        SQL全文                          実行回数   解析回数 実行時間（秒）
------------- ------------------------------ ---------- ---------- --------------
6g4afgxw08xm0 UPDATE t1                               1          1        .003307
              SET c2 = SYSTIMESTAMP
              WHERE c1 = 1
```
※不要な行は削除
実行後（commit前）に実行回数・実行時間がカウント
その後、commitを実行しても値に変更なし。

## ロールバック時の動作確認
2回ほどupdate文を実行し、ロールバック。
→値はロールバックされるが、実行回数・実行時間は影響なし。

``` sql
SQL_ID        SQL全文                          実行回数   解析回数 実行時間（秒）
------------- ------------------------------ ---------- ---------- --------------
6g4afgxw08xm0 UPDATE t1                               3          3        .003919
              SET c2 = SYSTIMESTAMP
              WHERE c1 = 1
```


## ロック待ちで待機

別セッションで同様のupdate文を実行し、commitしない行ロックで待機します。
待機しているSQLの実行回数は増えませんが、実行時間は増えます。
``` sql
SQL_ID        SQL全文                          実行回数   解析回数 実行時間（秒）
------------- ------------------------------ ---------- ---------- --------------
6g4afgxw08xm0 UPDATE t1                               4          5     239.078055
              SET c2 = SYSTIMESTAMP
              WHERE c1 = 1
```

## 実行キャンセル
Ctrl+C で実行をキャンセル
実行回数・実行時間が増えてます。
正常実行と同様の扱いになるようです。

``` sql
SQL> UPDATE t1
  2  SET c2 = SYSTIMESTAMP
  3  WHERE c1 = 1;
^CUPDATE t1
*
行1でエラーが発生しました。:
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました
```


``` sql
SQL_ID        SQL全文                          実行回数   解析回数 実行時間（秒）
------------- ------------------------------ ---------- ---------- --------------
6g4afgxw08xm0 UPDATE t1                               5          5     404.896718
              SET c2 = SYSTIMESTAMP
              WHERE c1 = 1
```



# ５．まとめ
:::note info 
DB操作にChat-GPTを利用するメリット
* SQL・SQL*Plusコマンド・PL/SQLなどが分からなくてもOracleDBを扱える。
* 分かる場合でも、自身で組むより早くて正確
* 知らない方法や関数を提示されることで新しい発見がある。
:::

:::note alert
注意点
* 誤ったコマンドを提示する場合がある（あった）。
そのため、コマンドの内容や実行結果は要確認。
:::

最後に、
今回、一番苦労したのは 「Chat-GPTとのやり取りをQiita上でどのように表現するか」でした。
画像（スクリーンショット）は分かりやすいですが、
文字情報が落ちてしまうのはイヤだなーということで今回の表記になりました。


## ６．関連リンク

https://chat.openai.com/