2021-10-25

Oracle DB 基本文法 sqlplus

Oracle SQL 基本文法

前提

sqlplusがインストールされている、Oracleクライアント、もしくはOracleサーバーで作業するものとする。

※OracleサーバにEM(Enterprise Manager)がインストールされている場合、GUI操作できるのでそちらを使った方が楽。
　EMの接続：
　　https://サーバー名:1158/em
　ユーザー名とパスワードを入力してDBに接続する。

　右下の方に「SQLワークシート」のリンクがあるので、それを開く。（Oracle11gの場合）
　ブラウザで直接SQLコマンドを入力でき、結果も画面上で確認できる。

SQLPLUSの操作

以下は基本的にコマンドラインでの操作を前提とする。

データベースへの接続

$ sqlplus ユーザー名/パスワード@サーバー名:ポート/インスタンス名

※「/パスワード」を省略した場合、接続時にパスワードを聞かれる。
※サーバー上で実行する場合、サーバー名、ポート名は省略できる。
※サーバー上で実行する場合、インスタンス名は事前に、「set ORACLE_SID=インスタンス名」
　を宣言することで省略できる。 ※SYSDBA(管理者)でつなぐときは「ユーザー名/パスワード as SYSDBA」
　そのあとの文とのつなぎはよくわかんない。
※sqlplusの起動文は結構パターンがあって把握しきれいていない。嘘書いてたらごめんなさい。
　間違ってたらエラーコードORA-xxxxxが出るので、それでググれば割と解決する。（他人任せ）

ORACLE DBの構造

インスタンス

メモリ上に展開されて、コマンド実行を直接受け付けるところ。
DBのフロントエンドみたいなイメージ。
ORACLE_SID=で指定するのがインスタンス。

データベース

実際にディスク上に実体があるのがデータベース。
インスタンスはデータベースを「マウント」し、
ユーザーはインスタンス経由でデータベースにアクセスする。

シンプルなシステムでは1つのインスタンスと1つのデータベースを用いる。
インスタンス名とデータベース名は同じになっていることが多い。
複数のインスタンスが1つのデータベースをマウントすることもできる。
インスタンスの冗長化になる。

インスタンス一覧の取得

select * from v$instance;

テーブル一覧の取得

--テーブル名だけでいいとき
select table_name from user_tables;

--細かい情報も欲しい時
select * from user_table;

コマンド

基本的にMySQLと変わらないんだけど、違うところを書いてく。

上位X行を表示

limit とか top が使えないので、副問い合わせで表示する。

select * from
  (select * from テーブル名 order by ソートするカラム asc)
where ROWNUM <= 10;

↑カラムを昇順に並べ替え、上の10レコード（ROWNUM=行番号が10以下のもの）を表示。

コマンド表示幅の変更

デフォルトでは幅が狭く、改行で見にくい時は広げる。

set linesize 240

※linesizeはlineに省略可能。

現在のlinesizeの確認

show linesize

各カラムの表示幅変更

column カラム名 format a20;

20のところが幅。任意に変える。

テーブルのカラム定義の確認

--簡易
desc テーブル名

--詳細
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE from ALL_TAB_COLUMN where TABLE_NAME='テーブル名' order by COLUMN_ID ASC;

別ユーザーのテーブルは参照できない。
テーブルのオーナー（スキーマ）がだれか調べる

select table_name, owner from dba_tables;

別ユーザーのテーブルを参照する場合はスキーマを指定する。

select * from スキーマ名.テーブル名;

セッション内でデフォルトのスキーマを変更することも可能。

alter session set current_schema = スキーマ名;

ビュー定義の確認

select * from USER_VIEWS where VIEW_NAME='ビュー名';

権限

GRANTコマンドで変える（あとで書く）

確認は、

select * from dba_sys_privs where grantee = 'ユーザー名';

2021-10-25

AWSでEC2からRDSのOracleに接続する

AWS Oracle

前提

EC2
　OSはRed Hat Enterprise Linux (RHEL) 8.4
　ec2-userで接続
RDS
　Oracle19.0
　Port1521
※重要
　EC2とRDSは同じVPC上に存在する。
　EC2とRDSは同じセキュリティグループ内にあるか、
　もしくはセキュリティグループ間でポート1521の通信を許可していること。
　（ここ、引っかかって接続できなかった。）
　

EC2にOracle Instant Clientをインストールする。

sqlplusを使うために必要。

インストーラはOracleのサイトにある。
rpm形式のファイルを使う。
クライアントバージョンはなるべくサーバーに合わせた方が無難かと思う。

今回はEC2に直接インストーラをダウンロードした。
ダウンロードするためにwgetを使うのでインストールする。

$ sudo yum install wget

wgetがインストールできたら、Oracle Instant Clientのインストーラをダウンロードする。
適当なディレクトリを作って移動する。
今回は「/home/ec2-user/oracle_client_installer」のディレクトリに保存した。

以下コマンドでダウンロード（バージョンによってパスは異なる。）
BasicとSqlplusが必要。

$ wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
$ wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm

ダウンロードできたらインストールする。

$ sudo yum install -y oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
$ sudo yum install -y oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm

sqlplusのパスが通るように、環境変数を設定する。
19.6の場合は、sqlplusは「/usr/lib/oracle/19.6/client64/bin」にあるので、

export PATH=/usr/lib/oracle/19.6/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.6/client64/bin:$LD_LIBRARY_PATH

これでOK
sqlplusが使えることを確認する。

$ sqlplus64 -v

なんかエラーが出る

error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory

なにやら「libnsl.so.1」というライブラリが不足しているみたい。
（RHEL8だとデフォルトではインストールされていない？？？）

ファイルはあるみたいなので、以下コマンドでインストールできる。

$ sudo yum install /lib/libnsl.so.1

これでsqlplusが使えるようになる。

$ sqlplus64 -v

RDSのエンドポイント名とポート番号を確認する。
＞RDS＞データベース＞（対象のデータベース）＞接続とセキュリティ
　・エンドポイント　をコピーする。（xxxxxx.ap-northeast-1.rds.amazonaws.com みたいなやつ）
　・ポートは変えてなければ1521のはず。

＞RDS＞データベース＞（対象のデータベース）＞設定　・DB名　を確認する（デフォルトだとORCL）

あと、RDS作成時に設定したユーザー名（デフォルトだとadmin）と、パスワードが必要。

以下コマンドで接続する。（デフォルトの場合）

$ sudo sqlplus64 admin@エンドポイント名:1521/ORCL

パスワードを聞かれるので、RDS作成時に設定したパスワードを入力。これでつながるはず。

以下エラーが出るときは、EC2-RDS間でポート1521が通ってない可能性がある。
AWSのセキュリティグループ設定を見直す。
「ORA-12162: TNS:net service name is incorrectly specified」

日本語（全角文字）を扱えるようにする

Linux側のbashに追加する。
Linux上のユーザーフォルダ直下にあるbash_profileをviで編集する。

$ vi ~/.bash_profile

以下の1行を末尾に追加する。

export NLS_LANG=Japanese_Japan.AL32UTF8

ESC :wqで保存。

bash読み込みはログイン時なので、一度exitしてコンソールに繋ぎ直すこと。

2021-10-20

SQL ストアドプロシージャ

SQL ストアド

※MySQLを前提に記載しています。

ストアドの作成

create procedure ストアド名()

select * from テーブル名;

作成済みのストアドの定義文の確認

show create procedure ストアド名;

ストアドの削除

drop procedure ストアド名;

登録されているストアド一覧の確認

「information_schema.ROUTINES」テーブルに格納されている。

select ROUTINE_NAME, ROUTINE_TYPE from information_schema.ROUTINES;

⇒ROUTINE_TYPE = PROCEDUREのものが該当

複数行のストアド処理を登録する。

1.デリミタの変更

通常、「;」を入力すると処理が走る。
この「;」をデリミタ(DELIMITER)と呼ぶ。 (delimit = 区切り）

ストアドで複数行処理を登録する場合は、プロセス内に「;」を複数回含める必要があるが、1度目の「;」で処理が実行されてしまうため、ストアドの登録時のみ一時的にデリミタを変更して登録を行う。

一般的にデリミタを「;」から「//」に変更する。

DELIMITER //

登録完了後、デリミタを元に戻す。

DELIMITER ;

2.ストアドの登録

複数行登録するときは、「BEGIN」と「END;」ではさむ

> DELIMITER //

> create procedure ストアド名()
-> BEGIN
-> 処理1;
-> 処理2;
-> 処理3;
-> END;
-> //

> DELIMITER ;

ストアドの入出力（引数、返り値）

入力引数をIN、出力引数をOUTで定義する。型を指定する必要あり。

create procedure ストアド名(IN 引数1 CHAR(10), OUT 引数2 CHAR(10)）
BEGIN
  select 出力カラム名
  into 引数2
  from テーブル名
  where 条件カラム名 = 引数1;
END;
//

例えば、以下のようなテーブルがあったとして、

・テーブル「name_table」

ID	NAME
1	Pochi
2	Tama

ここから、IDで検索してNAMEを取得する場合は、

・ストアド

create procedure find_name (IN in_id CHAR(4), OUT out_name CHAR(16))
    --入力にIDを指定して、出力で結果の名前を受け取る

BEGIN
  select NAME
  into out_name    --NAMEカラムから取得した結果を、出力変数のout_nameに格納する
  from name_table
  where ID = in_id;    --where条件に、入力したIDを指定して絞り込む
END;
//

・呼び出し

>call find_name('1', @name);
    --入力で、ID=1を指定、結果を受け取る変数を@nameとしている。（変数は@で宣言）

>select @name;    --結果を表示する

@name
Pochi

2021-09-29

AWS操作覚え書き

AWS

VPC、サブネット、ルートテーブル

VPC

管理単位でVPCを切る。
IPアドレス範囲を切る（例：10.0.0.0/16）
管理単位が分かれないならVPCは1つで良い。

サブネット

VPCに紐づけて、アクセス区分単位でサブネットを切る。
サブネット事にルートテーブルを切ることが可能。
　例：
　　パブリック　10.0.1.0/24
　　プライベート　10.0.2.0/24

ルートテーブル

　例：
　　パブリックルートテーブル
　　　10.0.0.0/16　local
　　　0.0.0.0/0　インターネットゲートウェイ

EC2インスタンスの作成

・EC2より、インスタンスの起動
・OSを選択
　（特に要件なければ、とりあえずAmazon Linux 2 AMIか）
・インスタンスを選択
　検証機ならとりあえずt2.microで。
・インスタンスの詳細設定
　ネットワーク（VPC）、サブネットを選択。
　自動割り当てパブリックIPを「有効」に。
　ネットワークインターフェース設定にて、プライマリIPアドレスを設定。（サブネットのIP範囲から選択）
・ストレージの追加
　特に要件なければデフォルトで。
・タグの追加
　わかりやすい識別名を付けておく。
　例：
　　キー「Name」、値「Test_WebServer」
・セキュリティグループの設定
　既存に使えるものがあれば選択する。　なければ新規作成する。・キーペアの設定　初回起動のタイミングで、キーペアの確認画面が開く。
　既存のキーペアを使うか、新規に作成する。
　新規の場合は分かりやすい名前を付けておく。
・（備考）パブリックIPの固定
　固定する場合は、EC2＞Elastic IPよりIPを取得し、対象のインスタンスに割り当てる。

セキュリティグループ

よく入れるインバウンドルール設定
・SSH　TCP Port22　　#デフォルト
・すべてのICMP　　#ping応答
・HTTP　TCP Port80　　#WebServer
・HTTPS　TCP Port443　　#WebServer
・MySQL/Aurora　TCP Port3306　　#DB
その他サービスが利用するPort（TCP/UDP）

2021-09-28

Linux 基本操作

Linux

前提
・AWS上のEC2
・Linux AMI使用
・ログインユーザはec2-user

ディレクトリ移動

最初に出てくるところはユーザーのホームディレクトリ

[ ~ ] $

絶対パスはここ↓
/home/ec2-user/
（「ec2-user」部分は、ログインユーザー名によって異なる）

・今いるディレクトリの確認

$ pwd

（※pwd : Print Working Directory）

・ホームディレクトリ（最初の場所）への移動

$ cd

（※cd : Change Directory）

・ルートディレクトリへの移動

$ cd /

・一階層上に戻る

$ cd ..

cdと..の間にスペースが必要なので注意

・現在のディレクトリのファイル、フォルダ一覧を表示

$ ls

（※ls : List Segment）

・パーミッションやファイルサイズ、更新日時の詳細情報を含め表示

$ ls -l

※パーミッションについては後述

・隠しファイルを含め表示

$ ls -a

・絶対パスを指定しての移動

$ cd /var/www/html

（最初に/が入る）相対パスの場合は「cd フォルダ名」でOK

ディレクトリ、ファイル操作

・ディレクトリ作成

$ mkdir hogehoge

・ディレクトリ削除空ディレクトリの場合

$ rmdir hogehoge

中身ごと削除する場合

$ rm -r hogehoge

（-r はディレクトリ削除を指すオプション）

・ファイルのコピー

$ cp コピー元ファイル コピー先ディレクトリ

・ディレクトリのコピー
※-rオプションが必要

$ cp -r コピー元ディレクトリ コピー先ディレクトリ

・ファイルの移動

$ mv ファイル名 移動先ディレクトリ

・ファイルの名前変更も、mvを使う

$ mv 変更前のファイル名 変更後のファイル名

ファイルの内容の確認

・catコマンドはファイルの全文を表示する。

$ cat ファイル名

・lessコマンドは画面に収まる範囲の文を上から表示する。

$ less ファイル名

表示後は以下で操作
・↑、↓で1行送り、戻り
・スペースキーで1ページ送り
・Ctrl+Bで1ページ戻り
・qで終了

・編集する場合はviで開く

$ vi ファイル名

※viの操作は別記事で記載。
＜リンクを貼る＞

パーミッション

確認コマンド

$ ls-l

-rw-r--r--r--. 1 hoge users 0 test.txt

読み方

[-][rw-][r--][r--].1 [hoge][users]
[①ファイルタイプ][②所有者権限][③グループ権限][④その他ユーザ権限].1 [⑤所有者][⑥グループ]

⇒①最初の1文字目の[-]
ファイルタイプを示す。
　- ：通常ファイル
　d ：ディレクトリ
　l ：リンクファイル

⇒②次の3文字 [rw-]
　「所有者」の権限を示す。
　r ：読み取り可能 (Readable)
　w ：書き込み可能 (Writable)
　x ：実行可能 (eXecutable)

⇒③その次の3文字 [r--]
　「グループ」の権限を示す。読み方は②と同様

⇒④その次の3文字 [r--]
　「それ以外のユーザー」の権限を示す。読み方は②と同様

⇒[hoge]　⑤最初に出てくるこの人が所有者
⇒[users]　⑥次に書いてるこれがグループ

パーミッションを変更する

$ chmod 所有権限 ファイル名

所有権限は数字で表すことが多い（他の書き方もある）
2進数で、rが4、wが2、xが1
それを、所有者、グループ、その他の分の3桁で表す。
例えば、[rwx][rw-][r--]にしたい場合は764なので、

$ chmod 744 test.txt

所有者の変更をする場合は以下を使う。

$ chown fuga test.txt

これで、「text.txt」の所有権がユーザー「fuga」になる。

圧縮・解凍

以下パターンのコマンドがある。

・gzip
　gzip ファイル名　　で圧縮
　gunzip ファイル名　　で解凍
　※圧縮・解凍前のファイルは残らない　※ディレクトリの圧縮はできない

・zip
　zip ファイル名　　で圧縮
　unzip ファイル名　　で解凍
　※圧縮・解凍前のファイルも残る
　※-r オプションを付けるとディレクトリを圧縮できる

・tar（アーカイブ）
　tar cvf ファイル名　　でアーカイブ

2021-08-10

vi基本操作

vi Linux

ファイルの新規作成、編集

vi {ファイル名}

vi test.txt

※ファイルがなければ新規作成。あれば編集。

・読み取り専用で開く
view {ファイル名}

view test.txt

モードの切替

初期はコマンドモード。

入力	動作
i	編集モードに入る。(insert)
ESC	コマンドモードに戻る。

終了、保存

コマンドモードで、

入力	動作
:w	上書き保存（write）
:wq	上書き保存して閉じる（write quit）
:q	閉じる（変更ありの場合は閉じられない。）
:q!	変更を破棄して閉じる

カット、コピー、ペースト

コマンドモードで、

入力	動作
dd	一行カット（delete）
yy	一行コピー（yank）
dw	一語カット（delete word）
yw	一語コピー（yank word）
p	貼り付け（paste）

範囲選択する場合は、

入力	動作
m*	現在の行をマーク（*に入れた記号でマークする）
d`*	マーク行*から、現在の行までをカット
y`*	マーク行*から、現在の行までをコピー

Startのsを使って、開始行でms、終了行でy`sを入力し、
pで貼り付けるような使い方。

入力	動作
o	1行改行されて、入力モードに移行する。
x	1文字削除
u	取り消し(undo)

カーソル操作

入力	動作
h	左
j	下
k	上
l	右
G	ファイル末尾に移動
{数字}G	{数字}行目に移動
%	文頭へ（Home）
0	文末へ（End）
w	次のワード
b	前のワード
Ctrl+F	ページダウン
Ctrl+B	ページアップ

Ctrl+sを押しちゃった時

（viじゃなくて、Linuxの仕様だけど、テキスト編集中にやっちゃいがちなのでこちらに書く。）
癖で保存しようとしてCtrl+sを押すと、スクロールロック状態になり、
入力ができない（入力した内容が画面に表示されない）状態になる。
Ctrl+qで解除する。

行数の表示非表示

デフォルトでは非表示。
コマンドモードで、

入力	動作
:set number	行数表示
:set nonumber	行数非表示

2021-08-08

Windowsのキーボード配列をUS配列に変更する。

Windows

PC全体のキーボード配列を変更する

レジストリエディタで、
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters
を開く。
初期設定の日本語配列から、以下の通りUS配列に変更する。

日本語配列
LayerDriver JPN：kbd106.dll
OverrideKeyboardIdentifier：PCAT_106KEY
OverrideKeyboardSubtype：2
OverrideKeyboardType：7

↓

US配列
LayerDriver JPN：kbd101.dll
OverrideKeyboardIdentifier：PCAT_101KEY
OverrideKeyboardSubtype：0
OverrideKeyboardType：7

PCを再起動する。

外付けキーボードのみ英語配列にする。

日本語配列のノートPCで、本体のキーボードはそのまま日本語で生かしたい場合を想定。
全体設定は英語配列にし、本体のキーボードをデバイス名指しで日本語でオーバーライドする。

全体設定

レジストリエディタで、
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters
を開く。

以下パラメータは、全体変更時と同様、日本語から英語に変更。

日本語配列
LayerDriver JPN：kbd106.dll
OverrideKeyboardIdentifier：PCAT_106KEY

↓

US配列
LayerDriver JPN：kbd101.dll
OverrideKeyboardIdentifier：PCAT_101KEY

以下パラメータのエントリを削除する。

OverrideKeyboardSubtype
OverrideKeyboardType

各デバイスのインスタンスパスを調べる。

デバイスマネージャを開き、「キーボード」を選択。
一度外付けキーボードなどをすべて取り外し、内蔵キーボードだけの状態にする。
残っているデバイスのプロパティを開き、詳細＞デバイスインスタンスパスを確認する。出てきた値をコピーして控えておく。

各デバイスのキーボード配列を設定

レジストリエディタで、
\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\（デバイスインスタンスパス）\DeviceParameters
を開く。

新規作成＞DWORD(32ビット)値にて、以下の2パラメータを追加する。

日本語配列
OverrideKeyboardSubtype：2
OverrideKeyboardType：7

なお、US配列でオーバーライドする場合は以下
（今回の場合、全体設定をUS配列としたため、外付けキーボードごとに個別でUS配列を設定する必要はない。）
US配列
OverrideKeyboardSubtype：0
OverrideKeyboardType：7

すべての内臓キーボードに本設定を適用する。

PCを再起動する。

[番外編]CapsLockキーをCtrlキーとして扱う。

こちらのサイトから、「Ctrl2Cap」をダウンロードし、圧縮ファイルを解凍する。

コマンドプロンプトを管理者権限で開き、ダウンロードしたフォルダに移動する。

以下コマンドを実行し、Ctrl2Capをインストールする。

ctrl2cap.exe /install

なお、アンインストールする場合は以下。

ctrl2cap.exe /uninstall

PCを再起動する。

[番外編]Ctrl+Spaceで半角/全角の切り替えを行う。

Window標準IMEの場合

タスクトレイの言語バー（「A」「あ」などのところ）を右クリックし、プロパティを開く。
「詳細設定」を開き、「全般」タブにある、編集操作のキー設定の「変更」をクリックする。
キー設定タブにて、以下の項目を設定する。
*キー： Ctrl+Space 入力/変換済み文字なし：IME-オン/オフそれ以外の列：半角空白

~~設定を適用する。~~

どこかのバージョンから、Windows設定に統一されている。
設定自体は簡単になった。

タスクトレイの言語バー（「A」「あ」などのところ）を右クリックし、「設定(s)」を開く。
「キーとタッチのカスタマイズ」を選び、「各キーに好みの機能を割り当てる」をオン。
Ctrl+Spaceのドロップダウンから、「IME-オン/オフ」を選択する。

Google IMEの場合

タスクトレイの言語バー（「A」「あ」などのところ）を右クリックし、プロパティを開く。
「一般」タブのキー設定の、キー設定の選択の「編集」をクリックする。
以下の2エントリを追加する。
１．
　モード：入力文字なし
　入力キー：Ctrl Space
　コマンド：IMEを無効化
２．
　モード：直接入力
　入力キー：Ctrl Space
　コマンド：IMEを有効化

設定を適用する。