Category Archives: oracle

「Oracle」triggerでカラムを更新するコード

サンプルコード

CREATE OR REPLACE TRIGGER trg_update_terminal_addr_hex

BEFORE UPDATE

ON r_tmnl_run

FOR EACH ROW

DECLARE

BEGIN

if(:old.TERMINAL_ID=:new.TERMINAL_ID)then

:new.terminal_addr_hex:=substr(:new.terminal_addr,1,4)|| lpad(trim(to_hex(substr(:new.terminal_addr,5,5))),4,’0′) ;

end if;

END;

「Oracle」triggerでカラムを追加するコード

サンプルコード

create or replace trigger trg_insert_terminal_addr_func

before insert

on r_tmnl_run

for each row

begin

:new.terminal_addr_hex:=substr(:new.terminal_addr,1,4)|| lpad(trim(to_hex(substr(:new.terminal_addr,5,5))),4,’0′) ;

END;

「Oracle」テーブルを排他モードでロックするサンプル

サンプル:
LOCK TABLE STUDENT_TBL
IN EXCLUSIVE MODE;

「Oracle」UPPER()、LOWER()の使用サンプル

サンプルコード
1.UPPER使用例

SELECT * FROM STUTBL WHERE UPPER(COLUMN1)= XXX
AND UPPER(COLUMN2)=XXX  ORDER BY XXX;

2.LOWER使用例

SELECT * FROM STUTBL WHERE LOWER(COLUMN1)= XXX
AND LOWER(COLUMN2)=XXX  ORDER BY XXX;

「oracle」テーブルの列制約の追加/削除メモ

1.テーブル「company」の列「company_name」にNOT NULL制約を追加する
alter table company modify company_name not null

2.テーブル「company」の列「manage_id」にDEFAULT値「119」を追加する
alter table company modify manage_id default ‘119’

3.テーブル「company」にプライマリキー「company_id」を追加する
alter table company add constraint pk_company primary key(company_id)

4.テーブル「company」の列「company_name」からNOT NULL制約を削除する
alter table company modify company_name null

5.テーブル「company」のプライマリキー(制約名「pk_company」)を削除する
alter table company drop pk_company

「Oracle」DBの文字コード、バージョンを確認するメモ

1.DBの文字コード確認
SELECT
PARAMETER,
VALUE
FROM
NLS_DATABASE_PARAMETERS
WHERE
PARAMETER IN (‘NLS_CHARACTERSET’, ‘NLS_NCHAR_CHARACTERSET’);

実行結果
——————————————————————————–
PARAMETER VALUE
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

2.バージョン確認
select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
“CORE 11.2.0.4.0 Production”
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production

DebianでOracle instant Clientクライアントをインストールする

1.ダウンロード
http://www.oracle.com/technetwork/topics/linuxsoft-082809.html

oracle-instantclient-basic-10.2.0.5-1.i386.rpm
oracle-instantclient-devel-10.2.0.5-1.i386.rpm
oracle-instantclient-jdbc-10.2.0.5-1.i386.rpm
oracle-instantclient-sqlplus-10.2.0.5-1.i386.rpm

2.alienのインストール
# apt-get install alien

3.rpmをインストール
#alien -i xxx.rpm

ORA_HOMEは「/usr/lib/oracle/10.2.0.5/client/」です。

4. /etc/ld.so.conf の末尾下記追加
/usr/lib/oracle/10.2.0.5/client/lib

実行コマンド
ldconfig
sqlplus

5. /etc/profileの末尾下記追加:
export ORACLE_HOME=/usr/lib/oracle/10.2.0.5/client/
export LD_LIBRARY_PATH=:$ORACLE_HOME/lib:/usr/local/lib:$LD_LIBRARY_PATH:.
export TNS_ADMIN=$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin:.

実行コマンド
source /etc/profile
echo $ORACLE_HOME

Oracle入門–select構文で不等号(<>)とNullの検索結果

SQLコマンド
select * from scott.user t;  —-All data

select * from scott.user t where t.ctm != 800;–Not equal 800(Not contain null)

select * from scott.user t where t.ctm != 800 or t.ctm is null;–Not equal 800(Does contain null)

OracleでSIGN(n)とDEDECODE(VALUE, if1,then1,xx)の使い方

1.SIGN(n)
形式
n < 0 — -1
n = 0 — 0
n > 0 — 1

SQL構文
select sign(-8),sign(0),sign(8) from dual
—————————–
-1 0 1

2.DECODE(VALUE, if1,then1, if2,then2, if3,then3, DEFAULT)
SQL構文
select
level
, decode(
level
, 2
, ‘two’
, 5
, ‘five’
, 8
, ‘eight’
, 11
, ‘huhu’
, ‘student’
)
from
dual
CONNECT BY
LEVEL < 9
実行結果
———- ——-
LEVEL DECODE(LEVEL,2,’TWO’,5,’FIVE’,8,’EIGHT’,11,’HUHU’,’STUDENT’)
1 student
2 two
3 student
4 student
5 five
6 student
7 student
8 eight

3.SIGN()とDECODE()連合
LEVEL<6 –不合格
LEVEL<8 –普通
ELSE –優秀

SQL構文
SELECT
LEVEL
, DECODE(
– 1
, SIGN(LEVEL – 6)
, ‘不合格’
, SIGN(LEVEL – 8)
, ‘普通’
, ‘優秀’
)
FROM
DUAL
CONNECT BY
LEVEL < 10

実行結果
———————————————–
LEVEL DECODE(-1,SIGN(LEVEL-6),’不合格’,SIGN(LEVEL-8),’普通’,’優秀’)
1 不合格
2 不合格
3 不合格
4 不合格
5 不合格
6 普通
7 普通
8 優秀
9 優秀

Oracleで日付関数sysdateの使い方

SQL構文

select to_char(sysdate,’YYYY/MM/DD’) FROM DUAL; — 2016/09/02
select to_char(sysdate,’YYYY’) FROM DUAL; — 2016
select to_char(sysdate,’YYY’) FROM DUAL; — 016
select to_char(sysdate,’YY’) FROM DUAL; — 16
select to_char(sysdate,’MM’) FROM DUAL; — 09
select to_char(sysdate,’DD’) FROM DUAL; — 02
select to_char(sysdate,’D’) FROM DUAL; — 6
select to_char(sysdate,’DDD’) FROM DUAL; — 246
select to_char(sysdate,’WW’) FROM DUAL; — 36
select to_char(sysdate,’W’) FROM DUAL; — 1
select to_char(sysdate,’YYYY/MM/DD HH24:MI:SS’) FROM DUAL; — 2016/09/02 13:51:09
select to_char(sysdate,’YYYY/MM/DD HH:MI:SS’) FROM DUAL; — 2016/09/02 01:51:37
select to_char(sysdate,’J’) FROM DUAL; — 2457634
select to_char(sysdate,’RR/MM/DD’) FROM DUAL; –16/09/02

1 / 3123