OBチュートリアル（Oracle入門2_SQL操作編）

2022.09.06 株式会社システムインテグレータ

Object Browserの使い方を覚えながらSQLを勉強しよう

このブログはハンズオン形式でSI Object Browser for Oracle（以下、OB）を操作しながらOracleのキホンを学ぶチュートリアルです。今回はデータベース操作言語(SQL)のキホンを学びます。ハンズオンで学ぶ方はチュートリアル1をやった前提でストーリーを進めますが、環境のない方でも、読んで理解できるように図や画面を使って説明していますので大丈夫です。

チュートリアル1の復習

チュートリアル1では、次の操作を行いました。今回はこのうちaとbの環境を使って操作します。

　a.ユーザー（スキーマ）「ERP」を作成し、DBA権限を付与

　b.テーブル「EMP」をERPスキーマ内に作成

　c.ユーザー「MIZUNO」を作成し、「CONNECT」ロールと「EMP」のオブジェクト権限を付与

　d.ユーザー「YAMADA」を作成し、「CONNECT_DML」ロールを付与

テーブル「EMP」にデータを挿入

１．ユーザーERPでログインし、EMPテーブルのデータタブを開く

SI Object Browser（以下、OB）にユーザー「ERP」でログインし、左覧のオブジェクトリストのTABLEを選び、右に表示されるオブジェクト一覧から「EMP」テーブルをダブルクリックしてください。上部に6つのタブが表示されます。左の3つは次のような内容です。

　「定義情報」…テーブル定義情報(DDL)を表形式で表示

　「ソース」…テーブル定義情報(DDL)そのもの

　「データ」…テーブルに格納されているデータを表形式で表示・変更（DMLで操作）

【NOTE】SQLの分類（DMLとDDLとDCL）

SQL(Structured Query Language)は、RDBMS（リレーショナルデータベース管理システム）のための専用言語で、ISO（国際標準化機構）により標準化が行われています。SQLは、その役割別に3つに類されます。OracleなどのRDBMSは、JavaやPythonなどの言語は理解できず、図1のようにかならずSQLで操作されます。

・DML(Data Manipulation Language)…データ操作言語(SELECT、INSERT、UPDATE、DELETE）

　表やビューなどのデータを照会、作成、変更、削除するための文

・DDL(Data Definition Language)…データ定義言語（CREATE、ALTER、DROP）

　表やビューなどのオブジェクトを作成、変更、削除するための文

・DCL(Data Control Language)…データ制御言語GRANT、REVOKE、COMMIT、ROLLBACK）

　アクセス権限制御やトランザクション制御などを行う文

OBチュートリアル（Oracle入門2_SQL操作編） 1

図1：SQLはRDBMSを操作する唯一の標準言語

２．テーブルコメントとカラムコメント

テーブル「EMP」の「定義情報」タブ、「ソース」タブはチュートリアル1で確認しました。今回は項目（カラム）の意味を理解しやすくするためカラムコメントに日本語名を追加します。Oracleにはテーブルコメントとカラムコメントが付けられますので、画面1のようにテーブルコメント「社員」とカラムコメント「社員コード」～「最終更新日」を付けてください。

OBチュートリアル（Oracle入門2_SQL操作編） 2 　画面1：テーブルコメントとカラムコメント（テーブル定義情報）

３．デフォルト(Default）制約

画面1の「省略時値」覧は、Oracleのデフォルト制約です。データがない(null)場合にここで指定したデフォルト値が入ります。例えば最終更新日にはsysdateを設定していますので、値を入れなかった場合は現在の日時がセットされます。

【NOTE】SQL標準で定義されている制約(Constraint)

制約とは、RDBMSに格納されるデータに想定外の値が入らないように制限をかけるものです。SQL標準で規定されている制約には次のようなものがあります。制約を使うとRDBMS自体が制約外のデータの挿入を防いでくれるので、データの整合性を保つことができます。

・主キー制約(Primary Key)：主キーとする

・一意制約(Unique)：値は一意（ユニーク）でなければならない

・Not Null制約：なにか値が入らなければならない

・デフォルト制約(Default)：値を入れない場合は、指定した初期値が入る

・チェック制約(Check)：値は、指定した条件を満たさなければならない

・参照整合性制約(Foreign Key)：他のテーブルの主キーと同じ値でなければならない

４．データタブでデータ入力

続いて「データ」タブを開いてください。画面2のように上部に検索条件覧、下部にデータ一覧が表示されますので1行目に下記のデータを入力してください。

社員コード：EMP1

姓：山田

名：太郎

社員カナ名：ヤマダタロウ

メールアドレス：null

部門コード：DEPT1

有効フラグ：1

最終更新者：山田太郎

最終更新日：null

OBチュートリアル（Oracle入門2_SQL操作編） 3 画面2：テーブル「EMP」のデータタブ

【NOTE】nullと長さ0の文字列

nullとは、項目に値が入っていない状態を示すものです。似たようなものに「長さ0の文字列」というものがあります。これは、項目のデータ型が文字列型だった場合、1文字も入っていない状態を表すものです。SQL Serverなどでは、なぜかnullと長さ0の文字列(通常、""で表現される）を区別するので取り扱いが複雑になりますが、Oracleはこれを区別しませんので楽ちんです。

テーブルオブジェクトとDML

「データ」タブは、テーブル（表）に格納されているデータを表形式で表示します。普段、意識はしていませんが、ここでデータを見たり触ったりする操作は、裏でOBが次のようなSQL(DML)を発行してOracleとやり取りしています。

　SELECT：データを表形式で表示

　INSERT：行（ロウ）を追加

　UPDATE：データを直接変更

　DELETE：行（ロウ）を削除

【NOTE】テーブルと表（英語と日本語）

データはテーブルに格納され、Excelのように行と列で表現されます。もともとの英語をそのまま使わず、下記のような日本語を当てはめています。エンジニアの立場からするとカラム制約とかCREATE TABLEとか元の英語も使いますので、かえってわかりにくい面もありますが…。

　テーブル(Table)：表

　ロウ(Row) 　　：行

　カラム(Column)：列

テーブル「DEPT」の作成

リレーショナルデータベースなので、テーブルが1つだと面白くありませんね。ということでオブジェクトリストのTABLEを右クリックして「新規作成(N)」メニューをクリックし、次のような情報を定義してテーブル「DEPT」を作成してください。

OBチュートリアル（Oracle入門2_SQL操作編） 4 画面3：テーブル「DEPT」の定義画面

データ生成機能でDEPTにデータ挿入

手動でデータ登録するのは大変なので、OBの「データ生成」機能を使ってデータを追加してみましょう。この機能は、本来はシステム開発時にパフォーマンスを確認するためのものです。データ量が本番並みになった場合にパフォーマンスが悪くならないか、インデックスは有効活用されているかなどを確認するため、ダミーで大量データを挿入します。単独のテーブルだけでなく、他のテーブルと連携したデータも挿入できるのが特徴です。

1．データ生成ツールの設定

上記テーブル定義画面の上部にある「生成」メニューをクリックすると、画面4のようなデータ生成ツールの設定画面が表示されます。通常のデータ生成で作成件数100件として、下記のように設定して「実行」をクリックしてください（赤：初期値と変える部分）。

項目	設定値
DEPT_CODE	連番、値範囲 1～、増分値 1、全桁埋めるオフ、接頭句 DEPT
DEPT_NAME	設定値乱数値（文字）、NULL比例(%) 0、設定内容全角文字最大桁数 20
DEPT_KANA_NAME	設定値乱数値（文字）、NULL比例(%) 0、設定内容全角文字最大桁数 20
VALID_FLG	固定値 '1'
UPDATE_USER_NAME	設定値乱数値（文字）、NULL比例(%) 0、設定内容全角文字最大桁数 10
USER_UPDATE_DATE	固定値 sysdate

OBチュートリアル（Oracle入門2_SQL操作編） 5 画面4：テーブル「DEPT」のデータ生成定義

２．生成されたデータの確認

データ生成ツールを終了し、データタブに切り換えると図のように自動生成された100件のデータが確認できます。

OBチュートリアル（Oracle入門2_SQL操作編） 6 画面5：テーブル「DEPT」のデータタブ

データ生成機能でEMPにデータ挿入

DEPT（部門）テーブルにデータが100件作成されましたので、関連あるテーブルEMP（社員）にも1000件のデータを作成してみしょう。

テーブル「EMP」上部メニューの「生成」をクリックすると「データ生成ツール」が表示されます。既に1件手入力しているので作成件数を 999とし、各項目の値を次のように設定します（赤：初期値と変える部分）。

ポイントはDEPT_CODEの設定値を”リンクテーブル値”としていることです。これにより「EMP」テーブルのDEPT_CODEには、「DEPT」テーブルの主キーDEPT_CODEの値がランダムにセットされるので、2つのテーブルを結合したSQLを発行してデータ操作することができるのです。

項目	設定値
EMP_CODE	連番、値範囲 2～、増分値 1、全桁埋めるオフ、接頭句 EMP
EMP_LAST_NAME	設定値テンプレート、テンプレート1 苗字
EMP_FIRST_NAME	設定値テンプレート、テンプレート1 名前
EMP_KANA_NAME	設定値乱数値（文字）、NULL比例(%) 0、設定内容全角文字最大桁数 20
EMAIL_ACCOUNT	設定値乱数値（文字）、NULL比例(%) 0、設定内容英字のみ最大桁数 20
DEPT_CODE	設定値リンクテーブル値、テーブル名 DEPT 項目名 DEPT_CODE
VALID_FLG	固定値 '1'
UPDATE_USER_NAME	設定値乱数値（文字）、NULL比例(%) 0、設定内容全角文字最大桁数 10
USER_UPDATE_DATE	固定値 sysdate

OBチュートリアル（Oracle入門2_SQL操作編） 7 画面6：テーブル「DEMP」のデータ生成設定

DEPTとEMPをジョインしたSELECT文を発行

2つのテーブルをWhere句でジョイン（結合）したSQL文を発行してデータを確認してみましょう。OBメニュー左上のSQLアイコンをクリックして、次のようなSELECT文を発行してみてください。ジョインができてDEPT_NAMEが表示されています。

OBチュートリアル（Oracle入門2_SQL操作編） 8 画面7：SQL実行

カラム名を一括取得してSQLエディタにセット

OBは、SQL文作成の補助機能として複数カラム名を一括取得してエディタにセットすることができます。SQLエディタの右側のオブジェクトツリーで図のように複数カラムを選択して”エディタへ挿入する(A)”をクリックすると、次のようにカンマ区切りでエディタに挿入されます。大量にカラムを記述する際の作業効率をアップし、的で入力ミスを防ぐことができるので使ってみてくだい。

OBチュートリアル（Oracle入門2_SQL操作編） 9 画面8：複数カラムを一括取得してSQLエディタに挿入