SQLite Databae에 대한 일곱번째 포스트입니다. 이번 포스트에서는 SQLite의 Trigger에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

CREATE TRIGGER

CREATE TRIGGER 문으로 트리거를 생성할 수 있습니다. 다들 아시겠지만, 트리거는 INSERT, UPDATE, DELETE 문장이 실행될 때 자동으로 실행되는 코드입니다. 현재 SQLite는 FOR EACH ROW (각 레코드 마다 트리거 코드가 실행됨) 만 지원합니다. 즉, SQL 문장 단위의 트리거는 지원하지 않습니다.

트리거 코드에서는 각 레코드가 INSERT/UPDATE/DELETE 될때 OLD 및 NEW 키워드를 사용하여 변경전 데이터 혹은 변경되는 데이터를 참조할 수 있습니다. 물론 INSERT에서는 NEW 키워드만, DELETE에서는 OLD 키워드만 유효합니다.

특이하게도 SQLite에서는 View에도 트리거를 설정할 수 있는데, INSTEAD OF 트리거로 만들 수 있습니다. SQLite에서는 View가 Read Only이므로 뷰에 대한 I/U/D 작업이 불가능 하지만,  View에 INSTEAD OF 트리거를 만들어서 트리거에서 I/U/D 작업을 수행하는 것은 가능합니다.

SQLite에서는 트리거를 I/U/D에 대해 각각 만들어야 합니다. 오라클 같은 경우는 하나의 트리거에서 모든 것을 처리할 수 있지만 SQLite에서는 그렇지 않아서 조금 귀챦기는 할 것 같습니다.

샘플 코드를 보면서 트리거에 대해서 확인하도록 하겠습니다.

/* SQLite Trigger Test Code */

-- Trigger test를 위해서 user table과 user_log  table을 생성합니다.
create table user (
  id integer primary key autoincrement,
  name varchar(10),
  password varchar(10)
);

create table user_log (
  log_no integer primary key autoincrement,
  id integer,
  name_before varchar(10),
  name_after varchar(10),
  password_before varchar(10),
  password_after varchar(10),
  change_date text, 
  flag varchar(1)
); 

-- update에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_update BEFORE UPDATE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, new.name, old.password, new.password, datetime('now','localtime'),'U');
END;

-- insert에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_insert BEFORE INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- delete에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_delete BEFORE DELETE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, null, old.password, null, datetime('now','localtime'),'D');
END;

-- test를 위한 데이터 insert
INSERT INTO USER(name, password) VALUES('overoid','1234');
INSERT INTO USER(name, password) VALUES('test','12345');
UPDATE USER SET NAME = '보고픈'  WHERE ID = 2;
UPDATE USER SET password = 'abcd' WHERE ID =1;
DELETE FROM USER WHERE id = 2;

-- 데이터 확인 (null 데이터를 명확하게 표기하기 위해서..ifnull 함수를 사용함)
SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D

-- 데이터는 정상적으로 나옵니다.
-- 다만, autoincrement로 생성한 컬럼에 대해서 insert 작업시 실행되는 before trigger에서 NEW.ID 값을
-- 제대로 가져오지 못하고 -1로 입력되는 것을 볼 수 있습니다.

-- 위 문제를 해결하기 위해서 INSERT에 대해서 AFTER 트리거를 생성합니다.
CREATE TRIGGER user_trigger_before_after AFTER INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- 데이터를 삽입합니다.
INSERT INTO USER(name, password) VALUES('after','1234');

SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D
6 -1 <null> after <null> 1234 2010-08-25 17:03:26 I
<null> after <null> 1234 2010-08-25 17:03:26 I

-- after trigger로 변경했더니, autoincrement 컬럼에 대해서 값을 제대로 가져오는 군요.

이것으로 SQLite Trigger에 대한 일곱번째 포스트를 마칩니다. 다음번 포스트에서는 SQLite의 Tranaction 및 Lock에 기능에 대해서 살펴보도록 하겠습니다.

 

SQLite Databae에 대한 여섯번째 포스트입니다. 이번 포스트에서는 SQLite의 DateTime 관련 함수 및 Formatting 에 대해서 다뤄보도록 하겠습니다. 이 부분은 다른 DBMS와 많이 다르고 자주 사용하는 함수들이라 몇가지 표현법은 꼭 익혀야 될 점인것 같습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)


1. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

2. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22




 

  1. 김승배 2011.03.19 15:18 신고

    DateTime 쿼리 관련해서..
    Select date(birthday) From TableA 와 같이 테이블의 DateTime column을 넣어주면 값이 안나오던데요..
    혹시 해결 방법을 아시나요?

아래 내용은 제가 2008년 프로젝트 당시에 작성한 문서입니다. 당시, 자바나 닷넷은 Code Style Guide 혹은 Code Convention등의 문서가 많았지만, SQL 및 PL/SQL에 대한 문서는 거의 없어서 제가 작성했던 문서입니다. 블로그 만든 기념으로 그 때 문서를 공유합니다. 문서가 길어서 Style Guide 와 Naming Convention 편을 분리해서 작성했습니다. 실제 내용은 프로젝트 개발자를 위한 가이드라서 좀 딱딱한 문체로 기술되었습니다.

프로젝트 팀간의 Formatting 공유를 위해 제가 사용중인 Toad용 Formatting 파일을 함께 첨부합니다. 첨부파일은 Toad Version별로 커스터마이징 되어 있습니다. 자신의 버전에 맞는것을 다운 받아 덮어쓰시면 됩니다. 모, 직접 Toad Formatter를 하나하나 설정해서 사용하셔도 됩니다.

본  문서는 SQL 작성시 필요한 기본적인 Coding Style을 제공함으로써 SQL의 가독성을 높이고 유지보수 편의성을 제공하고자 한다.

Basic Layout

 

Tabs

기본적인 탭 사이즈는 4로 한다. 또한, 탭을 나타낼 때 Tab을 사용하지 않고 Space를 사용한다.

Margin

출력을 위해서는 80컬럼을 기본적으로 사용하나, SQL은 복잡한 Subquery 사용시 라인이 많이 늘어나므로 기본적으로 120 컬럼을 사용한다.

Indenting

기본적인 Indent 사이즈는 4로 Tab 사이즈와 동일하게 한다.

Case

 

SQL은 대문자를 사용하여 작성함을 원칙으로 한다. 아래에 특별히 언급하지 않은 모든 텍스트는 대문자(Uppercase)를 사용하여 작성한다.

Keywords

SQL의 모든 키워드 (예:SELECT, INSERT, BEGIN 등)는 모두 대문자(Uppercase)를 사용한다.

Built-ins / Built-in packages

DBMS에 내장된 함수 (DECODE, SUBSTR, NVL 등) 및 내장 패키지 (DBMS_OUTPUT, DBMS_SQL, UTL_FILE 등)는 모두 대문자(Uppercase)를 사용한다.

Table Name / Column Name

일반적으로 테이블 명 및 컬럼명, Alias 명, User 함수명 등은 소문자를 주로 사용하나 국내에서 테이블명 및 컬럼명이 약자를 주로 사용하므로 대문자가 가독성이 높아 모두 대문자(Uppercase)로 기술하는 것을 원칙으로 한다.

 

PL/SQL Variables

PL/SQL의 변수명은 예외적으로 대문자를 사용하지 않으며, 접두어 및 Camel 표기법을 사용한다. 상세한 내용은 Naming Rule 부분을 참조한다.

Lists and Operators

 

Variable Declarations

변수선언은 변수명과 변수타입명의 정렬방식은 세로줄라인을 맞추는 Fixed 방식을 사용한다.

Alignment

(Fixed)

var1                        NUMBER(20);
my_string                 VARCHAR2(15);
v_C22 CONSTANT VARCHAR2(20) := 'GLOBAL';

(Compact)

var1 number(20);
my_string varchar2(15);
v_C22 CONSTANT VARCHAR2(20) := 'GLOBAL'; 

 

Parameter Declarations

함수, 프로시저, 패키지 등의 PL/SQL에서의 파라미터 선언 스타일은 각 변수별로 한 라인씩 작성하는 Stacked 방식을 사용한다. 정렬방식은 각 변수별로 변수명, 타입등의 세로줄을 맞추는 Fixed 방식을 사용한다.

단, 파라미터 변수가 2~3개이어서 한 라인을 넘지 않는 경우에는 변수를 라인별로 작성하지 않고 한 라인에 기술할 수 있다.

Style

(Stacked)

P1 IN NUMBER,
P2 OUT VARCHAR2,
P3_VAR OUT NUMBER

(Wrapped)

P1 IN NUMBER, P2 OUT VARCHAR2, P3_VAR OUT NUMBER, P4 IN VARCHAR2,
P5 IN NUMBER

 

Alignment

(Fixed)

P1           IN     NUMBER,
P2           OUT VARCHAR2,
P3_VAR  OUT NUMBER

(Compact)

P1 IN NUMBER,
P2 OUT VARCHAR2,
P3_VAR OUT NUMBER

 

Parameters

함수나 패키지등을 호출할 때 파라미터 변수를 입력하는 방식은 기본적으로는 Wrapped 방식 – 모든 파라미터 변수를 한줄에 입력하여 호출하도록 한다.

단, 파라미터로 입력하는 내용이 너무 길어질 경우 여러줄로 입력하여 한눈에 파악할 수 있도록 작성한다.

Named Parameters의 경우에는 여러줄로 나누어서 호출하는 것을 원칙으로 하며, 변수명과 인자값은 각 세로줄을 맞추는 Fixed 방식을 사용한다.

Style

(Wrapped)

MY_PROCEDURE((3 + 4) * A, VAR1, VAR2, VAR3);

(Stacked)

MY_PROCEDURE((3 + 4) * A
                               ,VAR1 
                               ,VAR2 
                               ,VAR3));

(Stack only on line overflow)

SELECT REPLACE(SYS_CONNECT_BY_PATH(PRD_IA_NAME, '>'), '>', P_DELIM)
INTO v_Path
FROM CATEGORY
WHERE PRD_IA_CD = :p_ProductIACode
AND PRD_IA_CD = GET_PIA_CD(p_SiteCode
                                                        ,p_ProductIACode
                                                        ,p_StartIATypeCode
                                                        ,p_ProductIaCode);

 

Aligment for Named Parameters (p => x)

(Fixed)

VAR1             => 1,
VARIABLE2   => 2 + A,
X                    => SIN(P2)

(Compact)

VAR1 => 1,
VARIABLE2 => 2 + A,
X => SIN(P2)

 

Parentheses

함수 호출시 괄호 및 스페이스 처리에 대한 규칙은 호출하는 인자들 간에만 공백을 주고, 함수명, 프로지서명과는 공백을 주지 않는다. 또한, 첫번째 변수와 마지막 변수와 괄호 사이에도 공백을 넣지 않는다.

Parentheses

MY_PROC(PART1, PART2)

Compact

MY_PROC (PART1, PART2)

파라미터, 컬럼리스트 이전에 공백 넣기

MY_PROC( PART1, PART2 )

괄호 안쪽에 공백 넣기

MY_PROC ( PART1, PART2 )

전부 공백 주기

 

Commas

컬럼리스트, 파라미터 리스트 등에서 ,(콤마)는 컬럼 앞에 둔다. 일반적으로 컬럼 뒤에 많이 기술하나 ,(콤마)를 앞쪽에 두면 콤마의 누락을 쉽게 파악 할 수 있다.

Commas

 ITEM1, 
,ITEM2, 
,ITEM3

콤마 + 리스트

(정렬은 단어기준으로 정렬, 첫번째 컬럼은 한칸 들여쓰기)

ITEM1, 
ITEM2,
ITEM3

리스트 + 콤마

  ITEM1
, ITEM2
, ITEM3

콤마 + 공백 + 리스트

        ITEM1
,       ITEM2
,       ITEM3

콤마 + 공백(여러 ) + 리스트

- CDM Style

 

And – Or

Where 조건에서 AND, OR 연산자를 사용할 때 기본적으로 각 연산자를 기준으로 한라인씩 기술하는 Stacked 방식을 사용하여 기술한다.

또한, AND, OR가 동시에 사용되는 경우에는 OR 부분을 명확히 하기 위해서 ( )를 사용하여 표기한다. 그리고 연산자를 좌측에 두고 컬럼리스트가 좌측 정렬되도록 정렬한다.

Style

(Stacked)

A > B
AND C < F(U,V)
AND X =Z

 

(Wrapped)

A > B AND C < F(U,V)
AND X =Z

 

 

Arrangement

         A > B
AND C < F(U,V)
AND X =Z

연산자를 좌측에, 컬럼 기준으로 정렬

A > B
AND C < F(U,V)
AND X =Z

좌측 정렬, 연산자를 좌측에

A > B AND
C < F(U,V) AND
X =Z

연산자를 우측에

A > B          AND
C < F(U,V) AND
X =Z

연산자를 우측, 연산자를 기준으로 정렬

 

Sample (AND, OR 동시 사용시)

SELECT PRD_IA_CD 
  FROM MODEL
 WHERE SITE_CD = 'kr'
   AND  PRD_MDL_CD = 'CODE'
       OR PRD_MDL_NAME = 'NAME')

SELECT PRD_IA_CD
  FROM MODEL
 WHERE SITE_CD = 'kr'
       AND PRD_MDL_CD = 'CODE'
   OR PRD_MDL_NAME = 'NAME'

SELECT PRD_IA_CD
  FROM MODEL
 WHERE SITE_CD = 'kr'
  AND(PRD_MDL_CD = 'CODE'
  OR PRD_MDL_NAME = 'NAME')

 

Plus-Minus-Mul-Div-Conat

사칙연산 및 문자열 연결 연산자의 경우 연산자를 기준으로 여러줄로 작성하지 않고, 한 줄로 기술한다.

Style

(Wrapped)

EXPR1 – F(ARG200) + EXPR3 – EXPR4

(Stacked)

EXPR1
- F(ARG200)
+ EXPR3

 

Specific Statements

 

:= Assignments

변수에 값을 할당할 때는 할당연산자(:=)를 기준으로 정렬한다.

Alignment

(Fixed)

v_SiteCode                 := 'kr'
g_ProductModelCode := 'AAAAB';
x                                 := func(p_Val);

(Compact)

v_SiteCode := 'kr'
g_ProductModelCode := 'XXXXX';
x := func(p_Val);_

 

SELECT / FETCH / EXECUTE

SELECT 문을 기술할 때 SELECT, INTO, FROM WHERE 등의 키워드는 우측 정렬을 원칙으로 한다.

Alignment

SELECT COL1, COL2
    INTO VAR1, VAR2
  FROM MY_TAB1, MY_TAB2;

SELECT COL1, COL2
INTO      VAR1, VAR2
FROM    MY_TAB1, MY_TAB2;

 

SELECT문의 컬럼 리스트는 한줄에 하나의 컬럼을 기술하는 것을 원칙으로 한다. 단, SQL의 조회하고자 하는 컬럼의 개수가 적어 한 줄에 기술이 가능한 경우에는 한 줄에 써도 무방하다.

Style of SELECT / INTO and FETCH / EXECUTE lists

(Stacked)

SELECT FIRST_COL
              ,SECOND_COL 
             ,THIRD_COL …

(Wrapped)

SELECT FIRST_COL, SECOND_COL,
THIRD_COL …

 

SELECT 컬럼 리스트와 동일하게 FROM, ORDER BY, GROUP BY RETURNING 리스트 도 한 줄에 하나의 컬럼을 기술하는 것을 원칙으로 한다. SELECT 문과 동일하게 해당절의 컬럼 개수가 적어 한 줄에 기술이 가능한 경우에는 한 줄에 써도 무방하다.

Style of FROM, ORDER BY , GROUP BY and RETURNING lists

(Stacked)

ORDER BY FIRST_COL
                 , SECOND_COL
                 ,THIRD_COL

(Wrapped)

ORDER BY FIRST_COL, SECOND_COL,
THIRD_COL

 

INSERT

INSERT문도 SELECT 문과 동일한 스타일을 따른다. 즉 INSERT문을 기술할 때 INSERT INTO, VALUES 등의 키워드는 우측 정렬을 원칙으로 한다.

Alignment

INSERT INTO MY_TAB (COL1, COL2)
        VALUES (VAL1, VAL2);

INSERT INTO MY_TAB
(COL1, COL2)
VALUES (VAL1, VAL2);

 

INSERT 문의 컬럼이나 VALUES리스트는 SELECT문의 컬럼 리스트와 동일하게 한줄에 하나의 컬럼을 기술하는 것을 원칙으로 한다. 단, SQL의 조회하고자 하는 컬럼의 개수가 적어 한 줄에 기술이 가능한 경우에는 한 줄에 써도 무방하다.

Style of COLUMN and VALUES lists

(Stacked)

INSERT INTO MYTAB
               ( FIRST_COL
                ,SECOND_COL
                ,THIRD_COL …

(Wrapped)

INSERT INTO MYTAB 
               ( FIRST_COL, SECOND_COL
               , THIRD_COL …

 

UPDATE

UPDATE문도 SELECT 문과 동일한 스타일을 따른다. 즉 UPDATE문을 기술할 때 UPDATE, SET, WHERE 등의 키워드는 우측 정렬을 원칙으로 한다.

Alignment

UPDATE MY_TAB 
       SET COL1 = EXPR1 
              ,COL2 = PXPR2

UPDATE MY_TAB
SET COL1 = EXPR1,
        COL2 = PXPR2

 

DELETE

DELETE문도 SELECT 문과 동일한 스타일을 따른다. 즉 DELETE문을 기술할 때 DELETE, WHERE 등의 키워드는 우측 정렬을 원칙으로 한다.

Alignment

DELETE FROM MY_TAB
             WHERE …

DELETE FROM MY_TAB
WHERE …

 

 

Table Alias

 

Join문에서 Table Alias를 부여할 때 일반적으로 A, B, C 순으로 작성하는 경우가 많다. 이것보다는 테이블을 파악하기가 용이한 의미있는 약어를 사용한다. 단, 약어는 3자를 넘기지 않는다. 1글자로 구분이 가능한 경우에는 한 글자로 사용한다.

예)
DEPT => D
MODEL_CATEGORY_INF => M OR MC
HISTORY => H

Table Alias

SELECT COLS
 FROM EMPLOYEES E 
     ,COMPANIES C
     ,PROFILES P
     ,SALES S
 WHERE C.COM_ID = E.EMP_COM_ID
   AND P.PRO_COM_ID = C.COM_ID 
  AND S.SAL_COM_ID(+) = P.PRO_COM_ID

SELECT COLS
 FROM EMPLOYEES EMP
     ,COMPANIES COM
     ,PROFILES PRO
     ,SALES SAL
 WHERE COM.COM_ID = EMP.EMP_COM_ID
   AND PRO.PRO_COM_ID = COM.COM_ID
  AND SAL.SAL_COM_ID(+) = PRO.PRO_COM_ID

SELECT COLS
  FROM EMPLOYEES A 
      ,COMPANIES B
      ,PROFILES C
      ,SALES D
 WHERE B.COM_ID = A.EMP_COM_ID
   AND C.PRO_COM_ID = B.COM_ID
  AND D.SAL_COM_ID(+) = C.PRO_COM_ID

 

Toad Formatting

 

Use Formatting Tool

모든 개발자들이 공통적으로 위에서 정의한 SQL Style을 적용하기 위해서 Toad의 Formatting Tool을 사용한다. 다른 Tool들도 Formatting 기능을 제공하기는 하나, Toad가 포맷팅 기능은 가장 강력하다. 그러므로 개발자들이 다른 툴을 주로 사용하더라도 최종 SQL문에 대해서는 Toad의 포맷팅 기능을 사용하여 SQL 스타일을 맞추면 된다.

 

Formatting Setup

본 문서와 함께 제공되는 FmtPlus.opt 파일을 PC의 동일파일의 경로에 덮어쓴다.
예) C:\Users\Administrator\AppData\Roaming\Quest Software\Toad for Oracle\10.5\User Files\FmtPlus.opt에 존재함. 개인 개발자들의 정확한 위치는 Toad > View > Formatting Options 메뉴를 실행하면 열린 Formatter Window의 타이틀에 전체 경로가 나타난다. 그 부분을 참고하면 된다.

 

프로젝트 수행시 프로젝트에 맞는Formatting 옵션을 설정하고자 한다면, Toad Formatter에서 프로젝트에 적합하도록 수정한 후 해당 파일을 개발자에게 공유해서 동일한 포맷팅을 사용하도록 하면 된다.

 

그림 1 Toad Formatter 실행 화면

Toad에서 Formatting하기

아래 그림과 같이 Formatting을 하고자 하는 블록을 선택한 후 툴바를 클릭하면 SQL이 포맷팅 된다.
Edit > Format Code 를 선택해도 동일하다. 단축키는 Shift + Ctrl + F 이다.

 

그림 2 Formatting 이전 SQL

 

그림 3 Formatting 실행 이후

Exceptions

Toad로 대부분 필요한 수준의 Formatting이 되어지긴 하나, 일부 SQL에 대해서는 Formatting 한 결과가 오히려 한눈에 파악하기가 어려운 경우도 있으며, Toad v9.x에서는 MERGE 문 같은 경우는 제대로 처리조차 못하고 있다. 이런 경우에는 일반적인 관습을 따라서 개발자가 한눈에 파악하기 좋게 SQL을 작성하도록 한다.

 

Connect By 구문 작성시

CONNECT BY 구문 작성시 키워드의 글자 길이가 길어서 Formatting 툴을 사용하면 키워드를 기준으로 우측정렬 하는 현재 스타일에서 오히려 보기에 안 좋을 수도 있다. 이 경우에는 예외적으로 CONNECT BY의 Full Keyword를 기준으로 정렬을 해도 되고, CONNECT 키워드만을 기준으로 정렬을 해도 무방하다.

Connect By 등 Keyword의 글자 길이가 긴 경우의 예외처리

before

SELECT PRD_IA_CD
FROM CATEGORY
WHERE PRD_IA_TYP_CD = '02'
START WITH SITE_CD = :SITE AND PRD_IA_CD = :IA_CDODE
CONNECT BY PRIOR SITE_CD = SITE_CD
AND PRIOR UPPER_PRD_IA_CD = PRD_IA_CD;

(포맷팅후)

    SELECT PRD_IA_CD
      FROM CATEGORY
     WHERE PRD_IA_TYP_CD = '02'
START WITH SITE_CD = :SITE
       AND PRD_IA_CD = :IA_CDODE
CONNECT BY PRIOR SITE_CD = SITE_CD
       AND PRIOR UPPER_PRD_IA_CD = PRD_IA_CD;

(예외허용)

SELECT PRD_IA_CD
  FROM CATEGORY 
 WHERE PRD_IA_TYP_CD = '02'
 START WITH SITE_CD = :SITE
        AND PRD_IA_CD = :IA_CDODE
 CONNECT BY PRIOR SITE_CD = SITE_CD
        AND PRIOR UPPER_PRD_IA_CD = PRD_IA_CD;

 

MERGE 문 등의 예외 처리

Merge문 작성시 Toad v.9.x버전에서는 Formatting 툴을 이용하면 WHEN MATCHED THEN 구문 이후 UPDATE 문에 대해 기본적인 UPDATE 문에서 정한 Style이 적용되지 않는다. WHN NOT MATCHED THEN 이후의 INSERT 문도 마찬가지다. Toad v.10.x 에서는 제대로 포맷팅 된다.

이처럼 오라클의 버전업에 따른 신규 기능의 경우, 제대로 포맷팅이 안될 경우에는 Formatting 툴을 이용하지 않고 보기 좋게 스타일 가이드에 맞게 작성한다.

'데이터베이스 > SQL' 카테고리의 다른 글

SQL & PL/SQL Naming Convention  (3) 2010.08.06
SQL Style Guide  (1) 2010.08.06
  1. KimSean 2011.02.15 16:04 신고

    좋은 자료네요^^;

+ Recent posts