Oracle
★少量のデータをINSERTする場合
_Insert_InitData.bat或いは_Insert_InitData.sh
sqlplus <USER>/<PASSWORD>@<INSTANCE> @_Insert_InitData.sql
_Insert_InitData.sql
SET ECHO OFF;
SPOOL _Insert_InitData.log;
@Insert_TABLE_A.sql
@Insert_TABLE_B.sql
...
@Insert_TABLE_Z.sql
COMMIT;
SPOOL OFF;
EXIT;
Insert_TABLE_A.sql
INSERT INTO TABLE_A (ID, NAME, REGIST_DATE) VALUES ('01', 'Andy', TO_DATE('2012/04/11 09:20:46', 'YYYY/MM/DD HH24:MI:SS'));
...
定義済みテーブル
使用例：テーブルのカラム名を取得
SELECT C.*
FROM USER_TAB_COLUMNS C, USER_TABLES T
WHERE C.TABLE_NAME = T.TABLE_NAME
ORDER BY C.TABLE_NAME
ORDER BY
AAA DESC NULLS FIRST -- NULLは最初
BBB ASC NULLS LAST -- NULLは最後
★重複行を最速に削除
DELETE
FROM
EMP E
WHERE
E.ROWID > (
SELECT
MIN(X.ROWID)
FROM
EMP X
WHERE
X.EMP_NO = E.EMP_NO
);
★削除について
DROP TABLE XXX CASCADE CONSTRAINTS; 制約(PK,FKなど)も同時削除
DROP TABLE XXX CASCADE CONSTRAINTS PURGE; ごみ箱に入れず即座に削除
FLASHBACK TABLE XXX TO BEFORE DROP; 消したテーブルを復元
SHOW RECYCLEBIN
PURGE RECYCLEBIN; ユーザー「ごみ箱」の全体を空にする
PURGE DBA_RECYCLEBIN; システム「ごみ箱」の全体を空にする
サンプル
DROP TABLE XXX CASCADE CONSTRAINTS;
-- Oracle 10g以降
PURGE TABLE XXX;
CREATE TABLE XXX
(
USER_ID VARCHAR2(8) NOT NULL,
DEPT_NO VARCHAR2(3),
USER_NAME VARCHAR2(32),
CREATED_DATE DATE DEFAULT SYSDATE,
MODIFIED_DATE DATE
);
ALTER TABLE XXX ADD CONSTRAINT XXX_PK PRIMARY KEY (USER_ID);
★sqlplusについて
バッチ実行
XXX.bat↓
sqlplus /nolog @XXX.sql > log.txt
XXX.sql↓
connect <USER>/<PASSWORD>@//localhost:1521/xe
@YYY.sql
...
commit;
exit
YYY.sql↓
set echo on
set define off
set sqlblanklines on
...
ユーザ設定
SQL>conn system/oracle
SQL>create user andy identified by "andy" default tablespace users temporary tablespace temp quota unlimited on users;
SQL>grant connect to andy;
SQL>grant create andy table to andy;
SQL>grant create andy view to andy;
SQL>grant create andy index to andy;
SQL>grant create andy synonym to andy;
SQL>grant create andy sequence to andy;
SQL>drop user andy cascade;
★多量のデータをINSERTする場合
_start.dat
PAUSE
CALL _Insert_InitData.bat user password testdb
PAUSE
_Insert_InitData.bat
SET CTL_DIR=C:\data\ctl
SET LOG_DIR=C:\data\log
sqlldr userid=%1/%2@%3 control='%CTL_DIR%\TABLE_A.ctl' log='%LOG_DIR%\TABLE_A.log'
sqlldr userid=%1/%2@%3 control='%CTL_DIR%\TABLE_B.ctl' log='%LOG_DIR%\TABLE_B.log'
...
sqlldr userid=%1/%2@%3 control='%CTL_DIR%\TABLE_Z.ctl' log='%LOG_DIR%\TABLE_Z.log'
TABLE_A.ctl
OPTIONS
(
MULTITHREADING=TRUE,
PARALLEL=TRUE,
ERRORS=-1,
)
LOAD DATA
INFILE 'C:\data\csv\TABLE_A.csv'
BADFILE 'C:\data\log\TABLE_A.bad'
DISCARDFILE 'C:\data\log\TABLE_A.dis'
APPEND INTO TABLE TABLE_A
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ID
, NAME CHAR NULLIF (NAME=BLANKS)
, REGIST_DATE DATE "YYYY/MM/DD HH24:MI:SS" NULLIF (REGIST_DATE=BLANKS)
)
TABLE_A.csv
01,Andy,2012/04/11 09:20:46
...
★CASE vs DECODE
CASE：WHEN句でEXISTS、比較条件、IN、BETWEEN、LIKEなどの演算子は使用可能
DECODE：演算子は使用不可
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '不明'
END
DECODE(sex, '1', '男', '2', '女', '不明')
CASE address
WHEN address IS NULL THEN '未登録'
ELSE '登録済'
END
DECODE(address, NULL, '未登録', '登録済')
★Oracleエラー対策
>ORA-12638:資格証明の取り出しに失敗しました
１．sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
↓
SQLNET.AUTHENTICATION_SERVICES=(NONE)
２．ORA_DBAグループにWindowsログインユーザが属する必要
★ソート関数
①：rownum或いはrow_number() OVER(ORDER BY item)
②：row_number() OVER(PARTITION BY item ORDER BY item)
③：rank() OVER(ORDER BY item)
④：dense_rank() OVER(ORDER BY item)