실무를 접하면서 사용했던 SQL문들이다.
substr(aaa, instr(aaa, '|')+1, instr(aaa, '|', 1,2)-instr(aaa, '|', 1, 1)-1)||','||
substr(aaa, instr(aaa, '|', 1, 2)+1, instr(aaa, '|', 1,3)-instr(aaa, '|', 1, 2)-1)
from imsi_temp
where length(aaa) - length(replace(aaa,'|','')) = 3
라인피드 갱신시 replace(content, chr(13)&char(10),'')
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;
License
--------
9XXX999
SQL> DELETE FROM emp a
WHERE rowid > (SELECT MIN(ROWID)
FROM emp b
WHERE b.empno = a.empno);
2) 나중에 들어온 데이터를 살릴경우
SQL> DELETE FROM emp a
WHERE ROWID < (SELECT MAX(ROWID)
FROM emp b
WHERE a.empno = b.empno);
from index_stats;
8 24020 10532 44 613 0
1. index_stats 자료사전에 결과가 저장
analyze index index_name validate structure;
2. rebuild 대상 확인.
select (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 as index_stat
from index_stats;
DEL_LF_ROWS_LEN : 컬럼은 인덱스 블록중 삭제되었던 행들의 전체길이 값
LF_ROWS_LEN : 컬럼은 현재 인덱스가 리퍼 블록에 저장되어 있는 인덱스 테이터의 전체길이
INDEX_STAT
----------
0 ---> 일반적으로 20%를 초과하면 인덱스 rebuild 해주어야 성능을 기대 할수있음.
=> 하나의 insert문에서 여러 개의 테이블에 동시에 하나의 행을 입력할 수 있다
[when 조건절1 then
into [table1] values (col1,col2,...)]
[when 조건절2 then
into [table2] values (col1,col2,...)]
[else
into [table3] values (col1,col2,...)]
[Sub-Query];
1) Unconditional-Insert문
=> Insert문을 실행할 때 어떤 조건도 없는 경우
INSERT ALL
INTO sal_history VALUES (empid, hiredate, sal)
INTO mgr_history VALUES (empid, mgr, sal)
SELECT employee_id empid, hire_date hiredate, salary sal, manager_id mgr
FROM employees
WHERE employee_id > 200 ;
=> Insert문을 실행할 때 어떤 조건을 만족하는 행만을 입력하는 경우
INSERT ALL
WHEN sal > 10000 THEN
INTO sal_history VALUES (empid, hiredate, sal)
WHEN mgr > 200 THEN
INTO mgr_history VALUES (empid, mgr, sal)
SELECT employee_id empid, hire_date hiredate, salary sal, manager_id mgr
FROM employees
WHERE employee_id > 200 ;
=> 서브쿼리에 의해 리턴된 데이타가 INSERT문에 정의된 첫번째 조건을 만족하면
그 조건에 의해 데이터를 입력하고 만족하지 않으면 그 다음에 정의된 조건에 의해
데이터를 입력시키는 방법
WHEN sal > 25000 THEN
INTO special_sal VALUES (deptid, sal)
WHEN hiredate like ('%00%') THEN
INTO hiredate_history VALUES (deptid, hiredate)
WHEN hiredate like ('%99%') THEN
INTO hiredate_history VALUES (deptid, hiredate)
ELSE
INTO hiredate_history VALUES (deptid, hiredate)
SELECT departement_id deptid, sum(salary) sal,
max(hire_date) hiredate
FROM employees
GROUP BY deptid ;
=> 안 쓸 거 같음
=> DML(INSERT, UPDATE, DELETE)문들의 단점은 하나의 SQL문을 작성해야 하고
데이터를 입력할 때 이미 존재하는 데이터가 테이블 내에 존재할 때는 에러가 발생하고
다시 UPDATE문을 실행하여 데이터를 변경해야만 했다.
MERGE문은 새롭게 입력하는 데이터가 테이블 내에 존재하지 않으면 INSERT되고
존재하면 UPDATE문을 실행할 수 있습니다.
USING [대상table/view] [대상table/view별칭]
on [join 조건]
WHEN MATCHED THEN
UPDATE SET col1 = values1 ,
col2 = values2
WHEN NOT MATCHED THEN
INSERT (column명, , ,)
VALUES (value1, value2, ...);
MERGE INTO copy_emp c
USING employee e
on (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name, e.last_name, e.email) ;
오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다. RANK Function는 oracle 8i(8.1.6) 부터 가능합니다. 8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다. plsql 내에서는 oracle 9i 부터 가능합니다. 8.1.6에서는 ORA-900 error가 발생 합니다. -- scott유저로 접속을 합니다. SQLPLUS scott/tiger -- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제입니다. -- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다. SQL>SELECT empno, ename, sal, RANK() OVER( ORDER BY sal DESC ) as rk FROM emp;
|
☞ 그룹별로 순위를 부여 하는 법 -- 위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다. -- 특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다. SQL>SELECT deptno, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk FROM emp ; DEPTNO ENAME SAL RK ------- ---------- ---------- --------- 10 KING 5000 1 10 CLARK 2450 2 10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 3 20 ADAMS 1100 4 20 SMITH 800 5 30 BLAKE 2850 1 30 ALLEN 1600 2 30 TURNER 1500 3 30 WARD 1250 4 30 MARTIN 1250 4 30 JAMES 950 6 |
☞ DENSE_RANK() 함수 DENSE_RANK( ) - 중복 RANK의 수와 무관하게 numbering을 합니다. -- 1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다. SQL>SELECT empno, ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC ) as rk FROM emp; EMPNO ENAME SAL RK --------- ---------- ---------- --------- 7839 KING 5000 1 7788 SCOTT 3000 2 7902 FORD 3000 2 7566 JONES 2975 3 7698 BLAKE 2850 4 7782 CLARK 2450 5 7499 ALLEN 1600 6 7844 TURNER 1500 7 7934 MILLER 1300 8 7521 WARD 1250 9 7654 MARTIN 1250 9 7876 ADAMS 1100 10 7900 JAMES 950 11 7369 SMITH 800 12 |
Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.
예를 들면, 아래와 같이 직원 테이블이 있다고 생각 하자.
직원 직속상사 직급
--------------------
철수 순희 대리
순희 영희 과장
길동 순희 대리
영희 개똥 부장
개똥 사장
기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴과 connect by 를 사용한다면 표현이 가능하다.
재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야한다는 점도 무시 할수 없다.
이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.
기본형식
Select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
From 직원
Start With 직원 = '개똥'
Connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
start with
select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.
정의 : start with <조건>
where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.
ex) start with 직원 ='개똥'and 직원 ='순희'
start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.
예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.
(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원 직원 직급
-------------
순희 과장
철수 대리
길동 대리
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
같은 결과셋이 여러 번 만들어지는 것을 방지하기 위해서는 이러한 조건을 사용해서는 안 된다.
처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.
그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직속상사 is null
connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
Connect by Prior
connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.
현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.
connect by 직속상사 = prior 직원
이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.
쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.
다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데, 결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 직급
-------------
철수 대리
순희 과장
영희 부장
개똥 사장
이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.
prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.
select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사 ,직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 상사 직급
-------------------
철수 대리
순희 철수 과장
영희 순희 부장
개똥 영희 사장
여기서는 직원과 직속상사의 이름을 동시에 선택하였는데, 사실 두 값은 같은 행에 존재하는 것이 아니기 때문에 평범한 방법으로는 이와 같은 결과를 얻을 수 없다. 그래서 예제에서는 두 행을 동시 접근하여 각각 값을 얻어낸 것이다.
Level
level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.
계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.
select 직원,level
from 직원
직원 level
-----------
철수 0
순희 0
길동 0
영희 0
개똥 0
한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수 대리 4
길동 대리 4
트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다.
level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.
selectlpad(' ',(level-1)*2,' ')||직원 직원
또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <= 3
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다.
level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다. 어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.
Order siblings by
계층적쿼리에서 order by로 정렬을 하려고 하면 트리를 만든 다음에 다시 정렬을 하기 때문에 계층적인 순서가 엉커버린다.
이러한 결과를 피하기 위해서 9i 부터 siblings by 절을 사용하면 각 계층내의 데이터만을 정렬시킬수 있다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order by 직원
직원 직급
-------------
길동 대리
철수 대리
순희 과장
영희 부장
개똥 사장
-> 계층을 만든후에 직원이름을 기준으로 정렬된다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order siblings by 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
길동 대리
철수 대리
-> 길동과 철수가 같은 level이기 때문에 정렬이 된다.
Sys_connect_by_path()
9i부터 사용되는 함수로써 계층적인 쿼리를 실행할 때 이 함수를 사용하면 루트에서 끝 노드까지 전체 경로를 리턴할 수 있게 해준다.
형식 : Sys_connect_by_path( <열>,<분리문자> )
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급,Sys_connect_by_path(직원,'-') 경로
from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
order siblings by 직원
직원 직급 경로
----------------------------------
개똥 사장 -개똥
영희 부장 -개똥-영희
순희 과장 -개똥-영희-순희
길동 대리 -개똥-영희-순희-길동
철수 대리 -개똥-영희-순희-철수
grouping sets 함수는 GROUP BY 절에서 그룹 조건을 여러 개 지정할 수 있는 함수이다.
GROUPING SETS 함수의 결과는 각 그룹 조건에 대해 별도로 GROUP BY한 결과를
UNION ALL한 결과와 동일하다.
GROUPING SETS 함수는 하나의 SQL문에 의해 여러 개의 그룹 조건을 한꺼번에 지정하여
복잡한 그룹 처리 과정을 단순하게 구성할 수 있다.
Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는
그룹핑 조건을 여러 개 기술할 수 있으며, grouping sets 함수 사용이 불가능한
이전 버전에서 복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
다시 말해서, grouping sets 함수를 사용하면, group by ... union all을
사용한 것보다 SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
【예제】
| grouping sets를 사용한 경우 | UNION ALL을 사용한 경우 |
|---|---|
SQL> select deptno,job,mgr,sum(sal)
2 from emp
3 group by GROUPING SETS((deptno,job),(deptno,mgr));
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
10 5000
10 7782 1300
10 7839 2450
20 7566 6000
20 7788 1100
20 7839 2975
20 7902 800
30 7698 5600
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 SALEMAN 5600
15 개의 행이 선택되었습니다.
SQL>
| SQL> select deptno,job,null as mgr,sum(sal)
2 from emp
3 group by deptno,job
4 UNION ALL
5 select deptno,null as JOB,mgr,sum(sal)
6 from emp
7 group by deptno,mgr;
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 SALEMAN 5600
10 5000
10 7782 1300
10 7839 2450
20 7566 6000
DEPTNO JOB MGR SUM(SAL)
---------- --------- ---------- ----------
20 7788 1100
20 7839 2975
20 7902 800
30 7698 5600
15 개의 행이 선택되었습니다.
SQL>
|
composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면 표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며 다음 표를 보고 이해하자.【예제】 SQL> select deptno,job,count(*) 2 from emp 3 group by grouping sets((deptno,job),deptno,job); DEPTNO JOB COUNT(*) ---------- --------- ---------- 10 CLERK 1 20 CLERK 2 30 CLERK 1 20 ANALYST 2 10 MANAGER 1 20 MANAGER 1 30 MANAGER 1 30 SALESMAN 4 10 PRESIDENT 1 CLERK 4 ANALYST 2 MANAGER 3 SALESMAN 4 PRESIDENT 1 10 3 20 5 30 6 17 개의 행이 선택되었습니다. SQL>
| composite column 문의 경우 | group by 문의 경우 |
|---|---|
| group by grouping sets(a,b,c) | group by a union all group by b union all group by c |
| group by grouping sets(a,b,(b,c)) | group by a union all group by b union all group by b,c |
| group by grouping sets((a,b,c)) | group by a,b,c |
| group by grouping sets(a,(b),()) | group by a union all group by b union all group by () |
| group by grouping sets(a,rollup(b,c)) | group by a union all group by rollup(b,c) |
| group by rollup(a,b,c) | group by (a,b,c) union all group by (a,b) union all group by (a) union all group by () |
| group by cube(a,b,c) | group by (a,b,c) union all group by (a,b) union all group by (a,c) union all group by (b,c) union all group by (a) union all group by (b) union all group by (c) union all group by () |
'두근두근 공부홀릭' 카테고리의 다른 글
| 시험 잘보는 방법 : 서울대 경제학과 이준구 교수님 (0) | 2011.04.05 |
|---|---|
| OCP11g, OCJP 6.0 자격 취득 후기 (0) | 2011.03.30 |
| Korea DB-Tech Seminar (2011.2.24) (0) | 2011.02.26 |
| VMware 설명 (0) | 2010.11.20 |
| 스터디 시작 [데이터베이스 아키텍쳐] (0) | 2010.11.09 |