2014/08/12 - [DB/MSSQL] - MS-SQL 계층형 트리구조 쿼리 (재귀호출) - with ...(col 1, col 2 ...,col n) as ... union .

이번 포스팅은 계층형 트리구조 쿼리를 이요하여 트리구조 출력을 하려한다.

오라클에서 계층형 트리는

START WITH .. CONNECT BY PRIOR 구문을 이용하여 트리구조를 심플하게 출력할 수 있다.

데이터 예를 들도록 해보자

create table tree_table(
	id number not null,
    parent_id number not null,
    name nvarchar2(30) not null
);

-- 1depth 
insert into tree_table(id,parent_id,name)
values(1,0,'1depth 1');
insert into tree_table(id,parent_id,name)
values(2,0,'1depth 2');
insert into tree_table(id,parent_id,name)
values(3,0,'1depth 3');

-- 2depth 1
insert into tree_table(id,parent_id,name)
values(4,1,'2depth 1-1');
insert into tree_table(id,parent_id,name)
values(5,1,'2depth 1-2');
insert into tree_table(id,parent_id,name)
values(6,1,'2depth 1-3');

-- 2depth 2
insert into tree_table(id,parent_id,name)
values(7,2,'2depth 2-1');
insert into tree_table(id,parent_id,name)
values(8,2,'2depth 2-2');
insert into tree_table(id,parent_id,name)
values(9,2,'2depth 2-3');

-- 3depth 1
insert into tree_table(id,parent_id,name)
values(10,4,'3depth 1-1');
insert into tree_table(id,parent_id,name)
values(11,4,'3depth 1-2');
insert into tree_table(id,parent_id,name)
values(12,4,'3depth 1-3');

SELECT id,parent_id,name, LTRIM (SYS_CONNECT_BY_PATH (name, ' > '), ' > ') AS depth_fullname
 FROM tree_table 
 START WITH parent_id = 0
 CONNECT BY PRIOR id=parent_id

위의 재귀쿼리 실행결과를 확인해 보도록 하자 

WITH tree_query(id,parent_id,name,sort, depth_fullname)  AS 
(
  SELECT  
         id
       , parent_id
       , name
       , ''||id as sort
       , ''||name as depth_fullname
    FROM tree_table
    WHERE parent_id = 0
    UNION ALL 
    SELECT
          B.id
        , B.parent_id
        , B.name
        , C.sort || ' > ' || B.id as sort
        , C.depth_fullname || ' > ' || B.name as  depth_fullname
    FROM  tree_table B, tree_query C
    WHERE B.parent_id = C.id
) 
SELECT id, parent_id, name,depth_fullname FROM tree_query order by sort

위와같이 트리구조로 잘 조회되었다.

   1depth 1

   |

   -------- 2depth 1-1

   |                 |

   |                 ---- 3depth 1-1

   |                 |

   |                 ---- 3depth 1-2

   |                 |

   |                 ---- 3depth 1-3

   -------- 2depth 1-2

   |

   -------- 2depth 1-3

   |

   1depth 2

   |

   -------- 2depth 2-1

   |

   -------- 2depth 2-2

   |

   -------- 2depth 2-2

   |   

   1depth 3

위와같은 구조로 출력된 셈이다.

잘 활용해서 실무에 적용해보도록 합시다~ 

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/11 - [DB/MSSQL] - MS-SQL 시퀀스 identity 생성 및 초기화하기

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

CREATE SEQUENCE  시퀀스명  
        START WITH n   
        INCREMENT BY n 
        MAXVALUE n |  NOMAXVALUE 
        MINVALUE n | NOMINVALUE

위의 옵션에 대해서 추가 설명을 드리자면, 

START WITH n ▶ n = 초기화 값

INCREMENT BY n ▶ n = 증가값

MAXVALUE n ▶ n = 최대값 또는 NOMAXVALUE = 무한대 값

MINVALUE n ▶ n = 최소값 또는 NOMINVALUE = 무한대 값

을 의미한다.

그렇다면 이제 시퀀스 생성을 해보도록 하자 

create sequence test_seq
  start with 1
  increment BY 1
  maxvalue 10000

위와같이 정상적으로 성공한 메시지가 출력되었다. 

생성한 시퀀스를 정상적으로 확인 하기 위해서 테이블을 하나 생성하여 시퀀스적용을 해보도록 하자

테이블 구조는 지난 포스팅인 MySQL에서의 시퀀스 샘플링 테이블과 같은구조로 생성 하겠다.

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

CREATE TABLE test_board(
  seq NUMBER NOT NULL PRIMARY KEY,
  title NVARCHAR2(20) NOT NULL
);

위와 같이 생성하였다면 seq 컬럼에 시퀀스를 적용해보도록 하겠다.

insert문으로 시퀀스 + 데이터를 삽입한 후, select문을 통해 삽입된 데이터를 확인해보도록 하자

INSERT INTO test_board(seq,title)
VALUES(test_seq.nextval,'hello');

INSERT INTO test_board(seq,title)
VALUES(test_seq.nextval,'hello2');

INSERT INTO test_board(seq,title)
VALUES(test_seq.nextval,'hello3');

select * From test_board;

위와같이 정상적으로 seq 컬럼이 자동증가 하였다.

INSERT INTO test_board(seq,title)
VALUES(test_seq.nextval,'hello4');
select test_seq.currval from dual;

위와같은 방식으로 주어야한다.

select test_seq.currval from dual;

라고 줄경우에는 

ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

라는 오류 메시지가 나오는것을 확인 할 수 있을것이다.

   SELECT * 

     FROM USER_SEQUENCES 

   WHERE SEQUENCE_NAME = UPPER('시퀀스명')

select * 
from user_sequences 
where sequence_name=upper('test_seq');

라고 주면 다음과 같은 시퀀스 정보에 대해서 확인 할 수 있을 것이다.

LAST_NUMBER 컬럼이 현재 시퀀스 값을 의미 한다.

시퀀스 초기화 할시 현재 시퀀스를 DROP 하고 다시 생성을 해야 한다고 하더라...

물론 초기화(?) 는 가능하는데 일종의 꼼수라고 한다. 

시간이 나면 이부분에 대해서 포스팅을 하도록 하겠음.

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/11 - [DB/Oracle] - 오라클(Oracle) sequence 생성, 등록, 최종 시퀀스값 조회하기

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

CREATE TABLE test_board(
  seq INT NOT NULL identity(1,1) PRIMARY KEY,
  title VARCHAR(20) NOT NULL
);

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');

위와같이 실행 후 SELECT문으로 데이터 조회

해당 테이블 구조 및 데이터는 

2014/08/11 - [DB/MySQL] - MySQL 시퀀스 auto_increment 생성 및 초기화하기

과 동일함

그럼 이제 모든 데이터를 지우고 다시 같은 insert 쿼리를 동작 후 SELECT문으로 조회를 해보도록 

하자 

이처럼 seq 컬럼에 데이터가 지워진 시퀀스 데이터에 이어져서 자동증가하는 것을 확인하였다.

시퀀스값을 깔끔하게 1부터 시작하기 원하는 경우가 존재한다.

다음을 이용하여 MS-SQL 시퀀스를 초기화 해보도록 하자

시퀀스 초기화를 하고자 한다면 다음과 같은 명령어를 주도록 하자

DBCC CHECKIDENT('test_board', RESEED, 0)

위와같은 DBCC CHECKIDENT 함수(?) 실행후 'test_board' 에 초기화 하고자 하는 테이블을 정의 한 후 세번째 파라미터 값에 초기화 값을 지정해주도록 하자

정상적으로 초기화가 되는지 시나리오 작성을 해보도록 하겠음

1) 테이블의 data all delete

2) 시퀀스 초기화(DBCC CHECKIDENT('xxxxxx','RESEED',x))

3) insert문을 디용한 데이터 추가

4) select문을 통한 table data 확인

-- 1. 데이터 모두 삭제
DELETE FROM TEST_BOARD;

-- 2. 시퀀스 초기화
DBCC CHECKIDENT('test_board', RESEED, 0)

-- 3. INSERT문 실행

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');


-- 4. SELECT문 조회

SELECT * FROM test_board

위와같이 정상적으로 seq 컬럼의 시퀀스가 초기화 되는것을 확인 할 수 있었다.

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

2014/08/11 - [DB/MSSQL] - MS-SQL 시퀀스 identity 생성 및 초기화하기

2014/08/11 - [DB/Oracle] - 오라클(Oracle) sequence 생성, 등록, 최종 시퀀스값 조회하기

CREATE TABLE test_board(
  seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(20) NOT NULL
);

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');

위와같이 실행후 SELECT문 실행

확인결과 title 컬럼에만 데이터를 insert 하였는데 seq 컬럼은 자동적으로 값이 증가 하여 

insert 되는것을 확인 할 수 있었다.

위와같이 자동으로 컬럼의 숫자값을 일정하게 증가하고 싶다면 auto_increment 를 시퀀스로 사용할 컬럼에 지정을 해주면 된다.

그럼 이제 모든 데이터를 지우고 다시 같은 insert 쿼리를 동작 후 똑같이 조회를 해보도록 하자 

delete from test_board;

INSERT INTO test_board(title)
VALUES('hello');

INSERT INTO test_board(title)
VALUES('hello2');

INSERT INTO test_board(title)
VALUES('hello3');

데이터를 지우고 다시 같은 내용의 insert 구문을 넣어주었다

다시한번 데이터 조회를 해보도록 하자

위와 같이 seq가 지워진 시퀀스 값에 이어서 자동 증가하는 현상이 발생하였다.

물론 중복이 되지 않으므로 상관은 없으나 모든 데이터를 삭제 후 시퀀스값을 초기화 하길 원할때가 

있으므로 시퀀스를 초기화 해주기로 해보자

시퀀스 초기화 하고자 할 경우는 하단의 명령어를 주면 간단하게 시퀀스값이 초기화가 된다.

alter table test_board auto_increment=1;

위와같이 alter table 후 test_board(시퀀스컬럼을 초기화 하고자 하는 테이블명) 을 지정 후 auto_increment=1(자동증가값 update를 해준다)

그럼 초기화가 정상적으로 되는지 임의의 시나리오를 작성하도록 하자

1) test_board의 데이터 모두 delete

2) 시퀀스 초기화 작업(alter table xxx auto_increment=x)

3) 다시한번 insert 쿼리 

4) select 구문을 통화 데이터 확인

위와같이 시나리오를 실행한 결과 다음과 같이 시퀀스 컬럼이 초기화 된 후, 

자동증가하여 컬럼에 추가되는것을 확인 할 수 있었다.

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!

MySQL로 간혹가다 작업 도중 분명히 테이블이 존재하는데

 Table '테이블명' doesn't exist

라는 문구가 나올때가 존재한다.

윈도우에서는 대부분 대소문자 구분없이 설치가 되지만, 

간혹가다가 리눅스용 MySQL은 대소문자 구분하는 형식으로 설치가 되있는 경우가 있다 .

 show variables like 'lower_case_table_names';

다음과 같은 명령을 실행하고 확인 하였을 경우

lower_case_table_names의 값이 0인지 1인지 2인지를 확인해야 한다.

본인의 테스트 서버는 0이어서 오류가 났었던 것이다.

보통 2는본적이 없어서 모르겠고 0 또는 1인것을 보았었다.

0일 경우 : 대소문자 구분함

1일 경우 : 대소문자 구분안함

본인은 대소문자 구분을 하지 않기위하여  lower_case_table_names의 값을 1로 설정을 해주고자 한다.

MySQL의 my.cnf 파일을 건드려 주어야 하는데

보통은 /etc/my.cnf에 존재한다.

vi로 my.cnf 파일을 보도록 하자

접속하다보면 [mysqld] << 이영역이 있을텐데

영역의 맨밑에 

lower_case_table_names = 1

라고 설정을 해주도록 하자

비교를 하기 위해 한줄 내려서 작성하였다.

붙여서 상관은 없다

위치는 [mysqld] < 밑에 나열된 명령어들 그리고 다음 영역의 [] 가 나오기 전에만 작성해 주면 

되겠다.

저장하고 나온다음에 mysqld 서비스를 재시작 해주었다.

쿼리에서 다시 명령어를 실행하여 대소문자 구분값이 변경되었는지 확인해보자

본인은 1로 잘 변경이 되었다.

한번 다시 아까 오류난 쿼리를 재실행 해보도록 하겠다.

정상적으로 소문자 테이블인대 대문자로 명령어를 실행해도 잘나온다

유의하도록 하자...

도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!