Oracleお試しコマンド

はじめに

勉強も兼ねてDockerでOracle環境を用意してそこから色々試してみる

環境構築

environment.sql

-- Oracle接続(コマンド プロンプト)
-- $ sqlplus sys/Oradoc_db1@ORCLCDB as sysdba

-- データベースキャラクタセット確認
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
-- 文字化け対応
-- $ echo 'export NLS_LANG=Japanese_Japan.AL32UTF8' >>~/.bash_profile
-- $ source ~/.bash_profile

-- システム時間(UTC)　※油断してたらJSTじゃなかった...
SQL> select SYSTIMESTAMP from dual;
-- OSのタイムゾーン変更(UTC⇒JST)
-- # ln -sf /usr/share/zoneinfo/Asia/Tokyo /etc/localtime


-- セッション変更(PDB)
SQL> alter session set container=ORCLPDB1;

-- ユーザー作成
SQL> create user developer identified by password default tablespace users temporary tablespace temp;

-- 権限作成
SQL> grant dba to developer;
SQL> grant unlimited tablespace to developer;

-- ログイン
$ sqlplus developer/password@ORCLPDB1

-- テーブル作成
SQL> CREATE TABLE MSTKIND (
 KIND_CD CHAR(2) NOT NULL,
 KIND_NAME VARCHAR2(40),
 KIND_DATE DATE,
 PRIMARY KEY (KIND_CD)
);

SQL> CREATE TABLE MSTKIND2 (
 KIND_CD CHAR(2) NOT NULL,
 KIND_NAME VARCHAR2(40),
 KIND_DATE DATE,
 PRIMARY KEY (KIND_CD)
);


-- データ設定
SQL> INSERT INTO MSTKIND VALUES ('01', 'キジトラ', SYSDATE);
SQL> INSERT INTO MSTKIND VALUES ('02', 'キジトラ2', SYSDATE-1);
SQL> INSERT INTO MSTKIND VALUES ('04', '2021/03/04', SYSDATE-1);
SQL> INSERT INTO MSTKIND2 VALUES ('01', 'キジトラ', SYSDATE);
-- ★☆★昨日の日付をYYYY/MM/DD形式で挿入★☆★
SQL> INSERT INTO MSTKIND VALUES ('03', 'キジトラ3', TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'), 'YYYY/MM/DD'));

-- 日付形式のデータ取得
SQL> select * from MSTKIND
 WHERE KIND_NAME LIKE '____/__/__'
;

-- 別テーブルの値を使って設定
UPDATE MSTKIND T1
 SET T1.KIND_DATE =
  (SELECT KIND_DATE FROM MSTKIND2)
;

【参考サイト】
　◆Dockerを使って環境を汚さずOracle Databaseを触ってみる
　　https://qiita.com/kawakawaryuryu/items/ae51bee866a57f178fe5

ユーザ操作

oraora_operation_user.sql

-- ユーザ削除
DROP USER [ユーザ名];

-- ユーザ削除　※テーブル作成していた場合、こちらでまとめて削除
DROP USER [ユーザ名] CASCADE;

【参考サイト】
　◆Oracleのユーザーを削除するDROP USER【SQL】
　　https://sql-oracle.com/?p=232

キー情報操作

oraora_operation_key.sql

-- プライマリキーの削除
ALTER TABLE [テーブル名] DROP CONSTRAINT [プライマリキー名] CASCADE DROP INDEX

-- 外部キーの削除

【参考サイト】
　◆FOREIGN KEY 外部キー ON DELETE CASCADE の有無での動作確認
　　https://qiita.com/Papageno/items/0c685bfbce2d3af60fd4
　◆ORACLE | かなめのロジック
　　https://kanamelogic.com/category/oracle/page/2/

お蔵入り

oraora.sql

-- 表領域作成
CREATE TABLESPACE USERS01 DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\USERS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED;

-- ユーザ作成
CREATE USER USER01 IDENTIFIED BY "USER01" DEFAULT TABLESPACE USERS01 TEMPORARY TABLESPACE TEMP;

-- 権限作成
GRANT DBA TO USER01;
GRANT UNLIMITED TABLESPACE TO USER01;

-- テーブル作成
CREATE TABLE MSTKIND (
 KIND_CD CHAR(2) NOT NULL,
 KIND_NAME VARCHAR2(40),
 KIND_DATE DATE,
 PRIMARY KEY (KIND_CD)
);

-- データ設定
INSERT INTO MSTKIND VALUES ('01', 'キジトラ', SYSDATE);
INSERT INTO MSTKIND VALUES ('02', 'キジトラ2', SYSDATE-1);
-- ★☆★昨日の日付をYYYY/MM/DD形式で挿入★☆★
INSERT INTO MSTKIND VALUES ('03', 'キジトラ3', TO_DATE(TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'), 'YYYY/MM/DD'));

【参考サイト】
　◆Oracle Database Express Edition 11g Release 2 をインストールしてテーブルを作成してみる
　　https://www.doraxdora.com/blog/2017/08/31/post-2304/