|
|||
|
'ORACLE > SQL' 카테고리의 다른 글
| DB 최적화를 고려한 다건조회 페이징처리 (0) | 2010.01.03 |
|---|---|
| SQL TIP (0) | 2010.01.03 |
| UNION (0) | 2010.01.03 |
| case (0) | 2010.01.03 |
| NVL,DECODE (0) | 2010.01.03 |
'ORACLE/SQL'에 해당되는 글 52건
- 2010.01.03 nvl2
- 2010.01.03 UNION
- 2010.01.03 case
- 2010.01.03 NVL,DECODE
- 2009.11.02 constraint 제약조건 (primary, foreign , unique,check, default)
- 2009.09.11 char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2
- 2009.09.02 ROLLUP , CUBE , GROUPING
- 2009.08.21 Oracle 널값(null)에 대하여 정리
- 2009.08.10 SELECT문 및 연산자
- 2009.08.07 DBMS에 따른 날짜포맷 변환
- 유니온(UNION)
SELECT * FROM A
UNION (ALL)
SELECT * FROM B
A 와 B 의 테이블의 해당하는 컬럼들을 연결하여 보여줍니다.
OR과 유사하다고 생각하면 되며 실제로 OR을 사용하는 쿼리를 UNION ALL으로 대체시 수행속도를 향상할 수 있습니다.
UNION은 중복된 데이타를 제거하며 UNION ALL은 중복된 데이타를 모두 보여 줍니다.
가능하다면 UNION ALL을 사용하는 것이 좋습니다.
(DISTINCT를 사용하는 것보다는 UNION만 쓰는것이 더 효율적이라 생각됩니다.)
SELECT *
FROM(
SELECT
ug_lim_mbdy_dsc
,ug_lim_mbdc
,apl_st_dt
,apl_ed_dt
,sp_rgn_dsc
,sp_rgn_tpc
,mcht_mtalnm
,mcht_bzcnm
,provnm
,ccwnm
FROM (
-- 가맹점일 경우
SELECT
a.ug_lim_mbdy_dsc
,a.ug_lim_mbdc
,a.apl_st_dt
,a.apl_ed_dt
,' ' AS sp_rgn_dsc
,' ' AS sp_rgn_tpc
,b.mcht_mtalnm
,' ' AS mcht_bzcnm
,' ' AS provnm
,' ' AS ccwnm
FROM tb_cs_jh_ch_cduglimbrk a
,tb_cs_mc_cm_bsc b
,(
SELECT -- 현재일자 이후건 모두 조회
,apl_st_dt
FROM tb_cs_jh_ch_cduglimbrk
WHERE tup_c = :tup_c
AND apl_ed_dt >= :now_dt /* 현재일자 */
AND a.tup_c = c.tup_c
AND a.ug_lim_mbdy_dsc = c.ug_lim_mbdy_dsc
AND a.ug_lim_mbdc = c.ug_lim_mbdc
AND a.apl_st_dt = c.apl_st_dt
AND a.ug_lim_mbdc = b.mcht_no
AND a.ug_lim_mbdy_dsc = '1'
UNION ALL
-- 업종일 경우
SELECT
a.ug_lim_mbdy_dsc
,a.ug_lim_mbdc
,a.apl_st_dt
,a.apl_ed_dt
,' ' AS sp_rgn_dsc
,' ' AS sp_rgn_tpc
,' ' AS mcht_mtalnm
,b.mcht_bzcnm
,' ' AS provnm
,' ' AS ccwnm
FROM tb_cs_jh_ch_cduglimbrk a
,tb_cs_mc_cc_bzc_c b
,(
SELECT -- 현재일자 이후건 모두 조회
tup_c
AND a.tup_c = c.tup_c
AND a.ug_lim_mbdy_dsc = c.ug_lim_mbdy_dsc
AND a.ug_lim_mbdc = c.ug_lim_mbdc
AND a.apl_st_dt = c.apl_st_dt
AND a.ug_lim_mbdc = b.mcht_bzcc
AND a.ug_lim_mbdy_dsc = '2'
UNION ALL
-- 특정지역일 경우
SELECT
a.ug_lim_mbdy_dsc
,a.ug_lim_mbdc
,a.apl_st_dt
,a.apl_ed_dt
,a.sp_rgn_dsc
,a.sp_rgn_tpc
,' ' AS mcht_mtalnm
,' ' AS mcht_bzcnm
,a.provnm
,a.ccwnm
FROM tb_cs_jh_ch_cduglimbrk a
,(
SELECT -- 현재일자 이후건 모두 조회
WHERE a.tup_c = :tup_c
WHERE ug_lim_mbdy_dsc >= :ug_lim_mbdy_dsc
AND ((ug_lim_mbdy_dsc > :ug_lim_mbdy_dsc)
OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc > :sp_rgn_dsc)
OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm > :provnm)
OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm = :provnm AND ug_lim_mbdc > :ug_lim_mbdc)
OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm = :provnm AND ug_lim_mbdc = :ug_lim_mbdc AND apl_st_dt >= :apl_st_dt)
)
ORDER BY ug_lim_mbdy_dsc, sp_rgn_dsc, provnm, ug_lim_mbdc, apl_st_dt
)
WHERE ROWNUM <= 16
길지만 하나의 조회 쿼리문 입니다. (이때까지 사용해본 조회 쿼리 중에서 가장 길군요.^^)
조회시 유용한 쿼리가 많이 포함되어 있으므로 천천히 읽어보시고
유용하게 활용하시기 바랍니다.
보시고 이해가 가지 않는 부분은 댓글로 질문해 주시면 성의껏 답변해 드리겠습니다.
'ORACLE > SQL' 카테고리의 다른 글
| SQL TIP (0) | 2010.01.03 |
|---|---|
| nvl2 (0) | 2010.01.03 |
| case (0) | 2010.01.03 |
| NVL,DECODE (0) | 2010.01.03 |
| constraint 제약조건 (primary, foreign , unique,check, default) (0) | 2009.11.02 |
다만 개별값이 아닌 SELECT를 이용한 조회된 그룹 혹은 조회값들이라면 아래와 같이 NOT EXISTS 쿼리문을 WHERE 절에 사용해 줌으로써 여집합의 결과를 얻을 수 있습니다.
SELECT
DISTINCT(b.tup_c) AS tup_c
FROM tb_cs_jh_ch_tupcodesvc b
WHERE substr(b.cd_svc_c,1,2) = '60'
AND A.tup_c = b.tup_c
)
위와 같은 역할을 하는 IS NULL을 사용한 다른 사용법입니다.
AND (
SELECT DISTINCT(b.tup_c) AS tup_c
FROM tb_cs_jh_ch_tupcodesvc b
WHERE substr(b.cd_svc_c,1,2) = '60'
AND A.tup_c = b.tup_c) IS NULL
SELECT를 사용한 단건 혹은 다건조회시 여러가지 옵션을 제공하며 그 옵션을 체크하여 적용시키는 경우.
즉, 여러가지 조건을 동시에 만족하는 것만의 결과값을 구해야 하는 경우 CASE 함수를 활용하여 구할 수 있습니다.
먼저 CASE 함수의 기본 형식을 알아두세요.
* CASE 함수
[ 형식 ]
CASE 컬럼명|표현식 WHEN 조건식1 THEN 결과1
WHEN 조건식2 THEN 결과2
......
WHEN 조건식n THEN 결과n
ELSE 결과
END
아래는 CASE WHEN 조건 분기를 사용하여 여러조건을 동시에 만족하는(교집합) 특정 값들을 얻고, NOT EXISTS 를 써서 특정 결과를 뺀(여집합) 결과를 구하는 쿼리 예제 입니다.
A.tup_c AS tup_c
FROM
(
SELECT A.tup_c AS tup_c
, MAX(CASE WHEN B.svc_bzcc = '00' AND :io_all_mcht = '1' THEN '1' ELSE '0' END) AS io_all_mcht
, MAX(CASE WHEN B.svc_bzcc = '01' AND :io_oiling = '1' THEN '1' ELSE '0' END) AS io_oiling
, MAX(CASE WHEN B.svc_bzcc = '02' AND :io_movie = '1' THEN '1' ELSE '0' END) AS io_movie
, MAX(CASE WHEN B.svc_bzcc = '99' AND :io_etc = '1' THEN '1' ELSE '0' END) AS io_etc
FROM TB_CS_JH_CH_TUPCODESVC A
,TB_CS_JH_CM_CARDSVC B
WHERE A.tup_c >= :tup_c
AND NOT EXISTS (
SELECT
DISTINCT(b.tup_c) AS tup_c
FROM tb_cs_jh_ch_tupcodesvc b
WHERE substr(b.cd_svc_c,1,2) = '60'
AND A.tup_c = b.tup_c
)
GROUP BY A.tup_c
) A
WHERE a.tup_c >= :tup_c
AND A.io_all_mcht = :io_all_mcht
AND A.io_oiling = :io_oiling
AND A.io_movie = :io_movie
AND A.io_golf = :io_golf
AND A.io_etc = :io_etc
GROUP BY A.tup_c
ORDER BY A.tup_c
유용하게 사용하시기 바랍니다.
'ORACLE > SQL' 카테고리의 다른 글
| nvl2 (0) | 2010.01.03 |
|---|---|
| UNION (0) | 2010.01.03 |
| NVL,DECODE (0) | 2010.01.03 |
| constraint 제약조건 (primary, foreign , unique,check, default) (0) | 2009.11.02 |
| char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2 (0) | 2009.09.11 |
|
||||
|
'ORACLE > SQL' 카테고리의 다른 글
| UNION (0) | 2010.01.03 |
|---|---|
| case (0) | 2010.01.03 |
| constraint 제약조건 (primary, foreign , unique,check, default) (0) | 2009.11.02 |
| char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2 (0) | 2009.09.11 |
| ROLLUP , CUBE , GROUPING (0) | 2009.09.02 |
1.무결성 제약조건 6가지
1) primarty key
2)foreign key
3)unique
4)check
5)default 정의
6)null 값허용
2.primary key(기본키)
1)crate 시 생성
ㄱ) create table usertb1 (userid nchar(8) not null primary key, ........)--제약조건 자동생성됨
ㄴ) create table usertb1(userid nchart(8) not null constraint pk_userid primary key, ....) -- 제약조건 이름 강제 설정
2) alter (수정)시 생성
alter table usertb1
add constraint pk_userid --제약조건 이름 설정
primary key (userid) --userid를 기본키로 설정
단, userid(기본키 설정할려는 항목)이 null일경우 에러가남
그럴경우 먼저. not null로 변경해줘야함
예) alter table usertb1
alter column userid nchar(8) not null
cf)identity 속성으로 지정한경우 자동 not null 임
3.foreign key(외래키)
1) craete 시 생성
ㄱ)create table buytb1 ( num int not null primary key, userid nchar(8) not null foreign key reperences usertb1(userid), ...)
ㄴ)create table buytb1 ( num int not null primary key, userid nchar(8) not null constraint fk_usertb1_buytb1 foreign key reperences usertb1(userid)..)
2)alter 사용
alter table buytb1
add constraint fk_usertb1_buytb1--제약조건 이름 설정
foreign key (userid) - 참조키 설정(buytb1의 항목)
references usertb1(userid) -- 참조할 기본키
on update cascade-- usertb1의 기본키가 변경될시 buytb1의 userid도 자동변경
on delete cascade -- 기준테이블에 삭제일어날경우 외래키테이불도 삭제가 일어나도록 설정
단, 만약 usertb1 의 userid 와 buytb1의 userid 에 일치하지 않은 항목이 있으면. 에러남
그런경우 동일하지 않은 기존데이터를 무시하고 키설정
alter table buytb1 with nocheck -- with nocheck 속성 추가
4.unique 제약조건
중복되지 않는 유일한 값을 입력해야함 (null허용 , 단 중복되면 안됨으로 1개의 null만 허용)
1)create 시 생성
create table usertb1 (userid ............addr nchar(30) null unique)
create table usertb1(userid........ addr nchar(30) null constraint ak_addr unique)
create table usertb1(userid.... addr nchar(30) null, constraint ak_addr unique(addr)) -- 먼저 생성후 별도로 제약조건 추가
2)alter 사용
alter table usertb1
add constraint ak_addr --제약조건 이름 설정
unique(addr)
5.check 제약조건
check 제약조건은 입력되는 데이터를 점검하는 기능을 수행한다
(예 전화번호 국번, 출생년도 조절)
1) 예제1
출생년도가 1900년 이후 그리고 현재의 연도 이전
alter table usertb1
add constraint ck_birthYear
check
(birthyear >= 1900 and birthyear <= year(getdate()))
2)예제2
전화번호 국번 제약
alter table usertb1
add constraint ck_mobile1
check
(mobile1 in ('010', '011', '016', '017', '018', '019'))
3) with nocheck 옵션
전화번호 국번 제약조건을 걸때 이미 012라는 번호가 들어있는경우
국번체크 제약조건에 위배되지만 . 무시하고 넘어갈때 사용
alter table usertb1
with nocheck
add constraint ck_mobile1
check(mobile in ('010', '011', '016', '017', '018', '019'))
6.default 정의
default 정의는 데티너를 입력하지않았을때 자동으로 입력되는 디폴드 값을 정의 하는 방법이다
1)create시 정의
create table usertb1( userid .... birthYear int not null default year(getdate()), addr nchar(2) not null default '서울')
2)alter사용(for 문 사용해야함)
alter table usrtb1
add constraint cd_addr
default year(getdate()) for birthYear
go
3)insert시 default사용
insert into usertb1 valuse('wjn',.......default, defalut) --디폴트값사용
insert into usertb1 (userid, name ) values('wtw'.'우태운') --열이름이 명시되지않으면 default로 설정된값이 해당열에 자동입력됨
insert into usertb1valuse('hyh',......'1965','경기') -- 값이 직접 명기되면 default 값은 무시됨
7.제약조건 삭제
alter table usertb1
drop constraint 제약조건이름
단,pk는 fk먼저 삭제해야함
cf)현재 table의 constraint 보기 : exec sp_help table이름
'ORACLE > SQL' 카테고리의 다른 글
| case (0) | 2010.01.03 |
|---|---|
| NVL,DECODE (0) | 2010.01.03 |
| char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2 (0) | 2009.09.11 |
| ROLLUP , CUBE , GROUPING (0) | 2009.09.02 |
| Oracle 널값(null)에 대하여 정리 (0) | 2009.08.21 |
char[(n)]
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
유니코드가 아니고, 길이가 n바이트인 고정 길이 문자 데이터입니다. n은 1에서 8,000 사이의 값이어야 하고 저장소 크기는 n바이트입니다. char의 SQL-92 동의어는 character입니다.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
varchar[(n)]
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
유니코드가 아니고, 길이가 n바이트인 가변 길이 문자 데이터입니다. n은 1에서 8,000 사이의 값이어야 하고 저장소 크기는 n바이트가 아니라 입력한 데이터의 실제 바이트 길이입니다. 입력한 데이터의 길이는 0일 수 있습니다. varchar의 SQL-92 동의어는 char varying 또는 character varying입니다.
비고
데이터 정의나 변수 선언문에서 n을 지정하지 않으면 기본 길이는 1입니다. CAST 함수에 n을 지정하지 않으면 기본 길이는 30입니다.
char이나 varchar을 사용하는 개체는 COLLATE 절을 사용하여 특정 데이터 정렬을 할당하지 않는 한 데이터베이스의 기본 데이터 정렬이 할당됩니다. 데이터 정렬은 문자 데이터를 저장하는 데 사용하는 코드 페이지를 제어합니다.
여러 언어를 지원하는 사이트는 문자 변환 문제를 최소화하기 위해 유니코드 nchar 또는 nvarchar 데이터 형식을 사용하는 것을 고려해야 합니다. char 또는 varchar을 사용하는 경우,
* 열의 데이터 값이 크기가 비슷할 경우 char를 사용합니다.
* 열의 데이터 값이 크기가 다를 경우 varchar를 사용합니다.
CREATE TABLE 또는 ALTER TABLE을 실행할 때 SET ANSI_PADDING이 OFF면 NULL로 정의된 char 열이 varchar로 처리됩니다.
데이터 정렬 코드 페이지에서 더블바이트 문자를 사용할 경우 저장소 크기는 계속 n바이트입니다. 문자열에 따라 n바이트의 저장소 크기가 n자보다 작을 수도 있습니다.
자료출처 : http://cafe.naver.com/q69.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=22995
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
VARCHAR2 와 NVARCHAR2
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
VARCHAR2 타입은, 데이터베이스의 character set 데이터를 가변 길이로 저장하고, NVARCHAR2 타입은 데이터베이스의 national character set 데이터를 가변 길이로 저장하는데 사용되는 데이터 타입입니다.
NVARCHAR2 타입도 NCHAR 타입과 같이 Oracle 9i 이상 버전에서는 UNICODE를 사용하여야만 합니다. 따라서 UTF8이나 AL16UTF16만 가능합니다.
VARCHAR2 나 NVARCHAR2 의 주된 특징은, 컬럼 정의 시 지정된 max length보다 적은 길이의 문자열을 저장하는 경우, Empty Byte가 SPACE로 추가 (padding) 되지 않고, 실제 data만 저장되는 가변 길이라는 것입니다. 그래서 CHAR/NCHAR에 비해 불필요한 자원 낭비를 막고, 테이블에 대한 Full Scan시에도 작은블록을 읽어 성능상 장점이 있으며, Empty Block이 PADDING되지 않아 정확한 비교를 하는데 유의해야 할사항이 적습니다.
VARCHAR2 Data Type은 최대 4000byte까지 저장이 가능하며 , 자릿수는 반드시 지정해야 합니다.
한 편 NVARCHAR2 Data Type도 최대 4000byte까지 저장이 가능한데, 자릿수를 지정하지 않을 경우 한글자가 지정됩니다. 저장되는 National Character Set에 따라 AL16UTF16의 경우엔 2 BYTE, UTF8의 경우는 3BYTE가 기본입니다.
내부 저장방식을 dump() function으로 확인해 보면, VARCHAR2 및 NVARCHAR2 의 내부 데이터 코드값은 1 입니다. 이 내부코드 값으로 Data Type에 대한 식별이 가능합니다.
Length는 실제 data를 insert한 양에 따라 유동적입니다. CHAR/NCHAR에 비해 불필요한 space가 추가되지 않았음을 확인할 수 있습니다.
실제로 저장된 문자data는 각 문자의 ASCII CODE값만 저장되었음을 확인이 하실 수 있습니다.
참고>
참고로 VARCHAR2와 NVARCHAR2의 dump결과 내부코드가 같으나, 구별은 table description으로
column에 정의된 Data Type에 대한 확인이 가능하며, 또한 한글의 경우 저장된 byte로도 확인이 가능합니다.
예를 들어 DB characterset이 KO16KSC5601이고 national characterset이 UTF8인 경우,
“가”를 저장한다고 보면, varchar2 Data Type의 length는 2byte이나 nvarchar2 Data Type은 3byte로 보입니다.
물론 DB characterset과 national characterset이 같은 UTF8인 경우엔 column에 정의된 Data
Type으로만 확인이 가능합니다.
자료출처 : http://blog.naver.com/redfreek2c?Redirect=Log&logNo=120028930188
'ORACLE > SQL' 카테고리의 다른 글
| NVL,DECODE (0) | 2010.01.03 |
|---|---|
| constraint 제약조건 (primary, foreign , unique,check, default) (0) | 2009.11.02 |
| ROLLUP , CUBE , GROUPING (0) | 2009.09.02 |
| Oracle 널값(null)에 대하여 정리 (0) | 2009.08.21 |
| SELECT문 및 연산자 (0) | 2009.08.10 |
* ROLLUP 연산자
- GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
각 그룹에 계산함수를 적용한다.
- GROUP BY절의 결과는 누적 계산 결과이다.
* CUBE 연산자
- GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
* GROUPING 함수
- 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
- 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
- GROUP BY절에 나타나는 컬럼에 적용된다.
사용 예)
-- table생성(사원이름,급여,부서,직위,입사년도)
CREATE TABLE roll_test (
name VARCHAR2(10),
sal NUMBER,
dept VARCHAR2(10),
duty VARCHAR2(10),
entYear NUMBER(4)
);
INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;
SELECT * FROM roll_Test;
-- 1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
-- (하나의 Column Grouping)
-----일반-----------
SELECT dept, SUM(sal)
FROM roll_Test
GROUP BY dept;
---------------------
----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY ROLLUP(dept);
-----------------------
----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------
-- 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
-- ROLLUP과 CUBE 차이점 없음
-- 2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
-- (두개의Column Grouping)
-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM roll_Test
GROUP BY dept, duty;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------
----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM roll_Test
GROUP BY CUBE(dept, duty);
-----------------------
-- ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
-- CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
-- GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨
-- 3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고, 총계를 구하라
-- (세개의 Column Grouping)
-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM roll_Test
GROUP BY dept, duty, entYear;
-----------------------
----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------
----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------
- ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
(부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성
- CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
(부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
※ 생성 가능한 모든 경우를 그룹 생성
사용 예)
* 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;
'ORACLE > SQL' 카테고리의 다른 글
| constraint 제약조건 (primary, foreign , unique,check, default) (0) | 2009.11.02 |
|---|---|
| char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2 (0) | 2009.09.11 |
| Oracle 널값(null)에 대하여 정리 (0) | 2009.08.21 |
| SELECT문 및 연산자 (0) | 2009.08.10 |
| DBMS에 따른 날짜포맷 변환 (0) | 2009.08.07 |
1. NULL값 정의
RDBMS에서 NULL은 0 이나 공백이 아닌 값을 알수가 없다의 의미입니다.
이말은 값이 없다 입니다. 이는 Oracle 상에서 수치를 계산할때 문제가 됩니다.
null이 포함된 수식 계산에서는 무조건 null이 출려되어 잘못된 결과가 나오기 때문입니다.
특히 수치계산일때는 테이블 생성시 not null로 해주거나 nvl()함수로 제대로 치환해줘야
합니다.
2. NULL값 잘못된 처리
숫자의 경우
create table jun1(
a number,
b number);
insert into jun1 values(null,12);
select a+b from jun1;
라고 한다면 12가 나올것 같지만 null값이 포함된 계산식은 무조건 null이 나옵니다.
그래서 nvl()를 사용하거나 not null을 사용하는게 좋습니다.
문자열의 경우
create table jun2(
a varchar2(10),
b varchar2(10));
insert into jun2 values(null,'11');
select concat(a,b) from jun2;
라고 한다면 11이 나옵니다. mysql에서는 문자열도 null이지만 oracle에서는
문자열은 null이 들어가도 null로 출력되지 않고 그대로 연결됩니다.
''(빈공백)을 null로 인식하는 오라클 테스트 예제
create table test
(
a varchar2(10),
b varchar2(10)
)
insert into test values ('',null);
insert into test values ('test','test');
commit;
select count(*) from test where a = '';
--0개출력
select count(*) from test where a is null;
--1개출력
select count(*) from test where b = '';
--0개출력
select count(*) from test where b is null;
--1개출력
※ 오라클에서는 ''(빈공백)을 NULL값으로 인식합니다.
※ 오라클에서는 '' 을 null로 인식하며 '' 는 = '' 가 아닌 IS NULL 로 조회해야 검색가능하다.
※ 오라클에서는 NULL값이나 ''(빈공백)값은 널연산자외에 연산자로는 조회 불가능합니다.
이말은 널연산자외에 연산자에서는 널값을 조회대상에서 제외한다는 애기입니다.
3.많은 양의 null처리
상당히 많은 양의 레코드에 null이 있다면 계산하는데 문제가 많다.
update table_name set num=0 where num is null; -- 정수의 경우
update table_name set num=' ' where num is null; -- 문자열의 경우
와 같이 null값을 지정된 숫자로 일괄로 바꾸어준다.
table을 만들시에 모든컬럼에 not null 제약조건을 주는게 좋습니다.
※nvl()함수를 이용한다.
null이면 지정된 값으로 출력합니다. 실제 데이타는 바뀌지 않으며 단지 출력용입니다.
select nvl(comm,0) from emp;
--comm컬럼의 값이 null이면 0을 출력하고 값이 있다면 해당 값을 출력한다.
select nvl(hiredate,'01-JAN-97') from emp;--hiredate컬럼의 값이 null이면 01-JAN-97을 출력하고 값이 있다면 해당 값을 출력한다.
select nvl(job,'not') from emp;
--job컬럼의 값이 null이면 job을 출력하고 값이 있다면 해당 값을 출력한다.
select ename,sal,comm,(sal*12)+nvl(comm,0) from emp;
--수치계산에서는 null이 포함되면 결과는 null이지만 nvl()로 처리되어 모두 계산되어진다.
select ename,sal,comm,(sal*12)+comm from emp;
--수치계산에서는 null이 포함되면 결과는 null이다 그래서 comm에 null이 포함된 레코드는
--결과값이 null로 나옵니다.
4.mysql과 다른 oracle의 범위
mysql에서는 select * from table_name where name is null하면 실제 컬럼에 null이 들어가고 공백이나 실제로 값이 있는 컬럼은 빼고 검색하지만
oracle에서는 select * from table_name where name is null하면 실제컬럼에 공백이나 빈공간이 null로 인식되어 검색됩니다.
※name은 예를든 컬럼
mysql null범위
1.데이타값이 null인값 -> ''의 문자값은 = ''로 비교를 해야하고 name is null로 하면 안됩니다.
oracle null범위
1.데이타값이 빈공간이나('') 값 -> '' 의 문자값은 = ''로 비교가 안되고 name is null로 합니다.
5. 널값은 비교대상이 될수 없다.
SELECT '널값' test FROM dual WHERE '' <> '널값';
-- 아무것도 출력안됨
-- 오라클에서 ''은 null입니다. null은 비교 대상이 될수 없으므로 is not null 연산자를
-- 이용해서 비교해야 정상적으로 자료가 출력됩니다.
-- 한마디로 널값은 =, <> 등으로 비교할 수 없습니다.
SELECT '널값' test FROM dual WHERE nvl('','널값') <> '널값';
-- 널값
-- 널값을 nvl 함수로 치환후 비교하면 정상출력됨
SELECT '널값' test FROM dual WHERE '' is null;
-- 널값
-- is null 연산자를 이용해서 정상출력됨
'ORACLE > SQL' 카테고리의 다른 글
| char 와 varchar 그리고 VARCHAR2 와 NVARCHAR2 (0) | 2009.09.11 |
|---|---|
| ROLLUP , CUBE , GROUPING (0) | 2009.09.02 |
| SELECT문 및 연산자 (0) | 2009.08.10 |
| DBMS에 따른 날짜포맷 변환 (0) | 2009.08.07 |
| CUBE 함수 (0) | 2009.07.08 |
|
||||||||||||||||||||||||||||||||||||||||||
|
'ORACLE > SQL' 카테고리의 다른 글
| ROLLUP , CUBE , GROUPING (0) | 2009.09.02 |
|---|---|
| Oracle 널값(null)에 대하여 정리 (0) | 2009.08.21 |
| DBMS에 따른 날짜포맷 변환 (0) | 2009.08.07 |
| CUBE 함수 (0) | 2009.07.08 |
| ROLLUP 함수 (0) | 2009.07.08 |
Oracle - MS SQL - DB2 UDB 의 서로 다른 날짜 형식을 맞추기위한 SQL문
DBMS 별 시간, 날짜 조회 쿼리
|
Oracle |
select sysdate from dual; 날짜+시분초 까지 조회가능 select current_timestamp from dual; 날짜+밀리초+시간존 까지 조회 |
|
MS SQL |
select getdate() 날짜 + 밀리초 단위까지 조회가능
|
|
DB2 UDB |
select current timestamp from sysibm.sysdummy1 날짜+밀리초까지 조회 가능 select current date from sysibm.sysdummy1 날짜만 조회 select current time from sysibm.sysdummy1 밀리초 단위의 시간만 조회 |
DBMS 별 default date format
|
Oracle |
YY/MM/DD (한글) DD-MON-YYYY (영어) |
|
MS SQL |
YYY/MM/DD HH:MI:SS (한글) MM-DD-YYYY HH:MI:SS (영어) |
|
DB2 UDB |
YYYY-MM-DD-HH:MI:SS.MMMMMM (TIMESTAMP 타입) YYYY-MM-DD (DATE 타입) HH:MI:SS.MMMMMM (TIME 타입) |
날짜 포맷 변환표
|
형식 |
RDBMS |
변환 문법 |
|
|
Oracle |
TO_CHAR(date_exp, 'YYYY.MM.DD') |
|
'YYYY.MM.DD' |
MSSQL |
CONVERT(VARCHAR, date_exp, 102) |
|
|
DB2 |
REPLACE(CHAR(DATE(date_exp),ISO), '-', '.') |
|
|
Oracle |
TO_CHAR(date_exp, 'HH:MI:SS') |
|
'HH:MI:SS' |
MSSQL |
CONVERT(VARCHAR, date_exp, 108) |
|
|
DB2 |
CHAR(TIME(date_exp) , JIS ) |
|
|
Oracle |
TO_CHAR(date_exp, 'YYYY/MM/DD') |
|
'YYYY/MM/DD' |
MSSQL |
CONVERT(VARCHAR, date_exp, 111) |
|
|
DB2 |
REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') |
|
|
Oracle |
TO_CHAR(date_exp, 'YYYYMMDD') |
|
'YYYYMMDD' |
MSSQL |
CONVERT(VARCHAR, date_exp, 112) |
|
|
DB2 |
CHAR(DATE(date_exp)) |
|
|
Oracle |
TO_CHAR(date_exp, 'HH24:MI:SS') |
|
'HH24:MI:SS' |
MSSQL |
CONVERT(VARCHAR(8), date_exp, 114) |
|
|
DB2 |
CHAR(TIME(date_exp) ) |
|
|
Oracle |
TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI') |
|
'YYYY.MM.DD HH24:MI' |
MSSQL |
CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114) |
|
|
DB2 |
REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5)) |
|
|
Oracle |
TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS') |
|
'YYYY/MM/DD HH24:MI:SS' |
MSSQL |
CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114) |
|
|
DB2 |
REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp)) |
'ORACLE > SQL' 카테고리의 다른 글
| Oracle 널값(null)에 대하여 정리 (0) | 2009.08.21 |
|---|---|
| SELECT문 및 연산자 (0) | 2009.08.10 |
| CUBE 함수 (0) | 2009.07.08 |
| ROLLUP 함수 (0) | 2009.07.08 |
| Oracle sum() over() - 누적계산 (0) | 2009.06.12 |
