1. 2장 1절 표준 조인(standard join)
1) JOIN
👉 두 개이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것이다.
👉 일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립된다. 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.
- EQUL JOIN
2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용한다.
대부분 PK, FK의 관계를 기반으로 한다. - NON EQUI JOIN
2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용한다.
‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용
2) FROM절 JOIN
👉 ANSI/ISL SQL에서 규정한 JOIN 문법은 WHERE절의 검색조건과 테이블 간의 JOIN조건을 구분 없이 사용하던 기존의 방식을 그대로 사용할 수 있으면서, 추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM절에서 명시적으로 사용할 수 있게 되었다.
- FROM절 JOIN형태
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
- INNER JOIN
WHERE절에서 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환한다. DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과 같이 사용할 수 없다. - NATURAL JOIN
INNER JOIN의 하위개념이므로 NATURAL INNER JOIN이라고 표시할 수 있다. - ON 조건절
WHERE절의 JOIN 조건을 FROM절의 ON 조건절로 분리하여 표시함으로써 가독성이 높아져 사용자가 이해하기 쉽도록 돕는다.
칼럼명이 서로 다르더라도 JOIN 조건을 사용할 수 있다.
3) INNER JOIN
👉 OUTER(외부) JOIN과 대비하여 INNER(내부) JOIN이라고 하며, JOIN 조건에서 동일한 값이 있는 행만 반환한다.
👉 INNER JOIN은 그 동안 WHERE절에서 사용하던 JOIN 조건을 FROM절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.
📌 예제) 사원번호와 사원이름, 소속부서코드와 소속부서 이름을 찾아본다.
select emp.deptno, empno, ename, dname -- where절 join
from emp, dept
where emp.deptno = dept.deptno;
select emp.deptno, empno, ename, dname -- from절 join
from emp inner join dept
on emp.deptno = dept.deptno;
select emp.deptno, empno, ename, dname -- inner 키워드 생략
from emp join dept
on emp.deptno = dept.deptno;4) NATURAL JOIN
👉 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다. NATURAL JOIN이 명시되었으면 추가로 USING 조건절, ON 조건절, WHERE절에서 JOIN 조건을 정의할 수 없다.
👉 별도의 JOIN 칼럼을 지정하지 않지만, 두 개의 테이블의 공통된 칼럼을 자동으로 인식하여 JOIN을 처리한다. JOIN에 사용된 칼럼들은 같은 데이터 유형이여야 하고 ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.
(SQL SERVER는 지원안함)
📌 예제) 사원번호와 사원이름, 소속부서코드와 소속부서 이름을 찾아본다.
select deptno, empno, ename, dname
from emp natural join dept;5) USING 조건절
👉 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.
👉 NATURAL JOIN과 마찬가지로 JOIN에 사용된 칼럼들은 같은 데이터 유형이여야 하고 ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.
(SQL SERVER는 지원안함)
📌 예제) DEPTNO, DNAME 2개의 칼럼을 이용해서 DEPT와 DEPT_TEMP 테이블을 조인한다.
select deptno, dname, dept.loc
from dept join dept_temp
using (deptno, dname);6) ON 조건절
👉 JOIN 서술부(ON 조건절)와 비JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.
👉 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다.
👉 ALIAS나 테이블명을 반드시 사용해야 한다.
📌 예제) 사원 테이블과 부서 테이블의 사원번호와 사원이름, 소속부서코드와 소속부서 이름을 찾아본다.
select e.empno, e.name, e.deptno, d.dname
from emp e join dept d
on (e.deptno = d.deptno); -- 괄호는 옵션사항👉 ON 조건절과 WHERE 검색조건은 충돌 없이 사용할 수 있다.
📌 예제) 부서 코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서코드, 부서 이름을 찾아본다.
select e.empno, e.name, e.deptno, d.dname
from emp e join dept d
on (e.deptno = d.deptno)
where e.deptno = 30;📌 예제) 팀과 스타디움 테이블을 스타디움ID로 JOIN하여 팀이름, 스타디움ID, 스타디움이름을 찾아본다.
select team_name, team.stadium_id, stadium_name -- on join 조건
from team join stadium
on team.staduim_id = stadium.stadium_id
order by stadium_id;
select team_name, stadium_id, stadium_name -- using join 조건
from team join stadium
using stadium_id
order by stadium_id;
select team_name, team.stadium_id, stadium_name -- where절 join 조건
from team, stadium
where team.staduim_id = stadium.stadium_id
order by stadium_id;📌 예제) 팀과 스타디움 테이블을 팀ID로 JOIN하여 팀이름, 팀ID, 스타디움이름을 찾아본다. STADIUM에는 팀ID가 HOMETEAM_ID라는 칼럼으로 표시되어 있다.
select team_name, team_id, stadium_name -- on join 조건
from team join stadium
on team.team_id = stadium.hometeam_id
order by team_id;
select team_name, team_id, stadium_name -- where절 join 조건
from team, stadium
where team.team_id = stadium.hometeam_id
order by team_id;
-- 위 SQL은 TEAM_ID와 HOMETEAM_ID라는 다른 이름의 칼럼을 사용하기 때문에 USING 조건절을 사용할 수 없다.📌 예제) 다중조인 - GK포지션의 선수별 연고지명, 팀명, 구장명을 출력한다.
select p.player_name, p.position, t.region_name, t.team_name, s.stadium_name -- on join 조건
from player p join team t
on p.team_id = t.team_id
join stadium s
on t.stadium_id = s.stadium_id
where p.position = 'GK'
order by player_name;
select p.player_name, p.position, t.region_name, t.team_name, s.stadium_name -- where절 join 조건
from player p, team t, stadium s
where p.team_id = t.team_id
and t.stadium_id = s.stadium_id
and p.position = 'GK'
order by player_name;7) CROSS JOIN
👉 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합이다.
👉 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로 결과는 양쪽 집합의 M*N건의 데이터 조합이 발생한다.
📌 예제) 조인조건 없이 사원과 부서 테이블을 조인해본다.
select ename, dname
from emp cross join dept
order by ename;8) OUTER JOIN
👉 INNER(내부) JOIN과 대비하여 OUTER(외부) JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용한다. 동일한 값이 없는 행은 NULL로 출력한다.
👉 USING이나 ON 조건절 반드시 사용해야 한다.
👉 과거 ORACLE에서 OUTER JOIN을 위해 JOIN 칼럼 뒤에 '(+)'를 표시하였다.
① LEFT OUTER JOIN
👉 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
👉 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.
(OUTER 키워드를 생략할 수 있다.)
📌 예제) STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다. STADIUM과 TEAM을 JOIN하되, 홈팀이 없는 경기장의 정보도 같이 출력한다.
select stadium_name, stadium.stadium_id, seat_count, hometeam_id, team_name
from stadium left outer join team
on stadium.hometeam_id = team.team_id
order by hometeam_id;② RIGHT OUTER JOIN
👉 조인 수행시 나중 표기된 우측 테이블에 해당하는 데이터를 읽은 후, 먼저 표기된 좌측 테이블에서 JOIN 대상 데이터를 읽어 온다.
👉 좌측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.
(OUTER 키워드를 생략할 수 있다.)
📌 예제) 부서 및 사원 정보를 출력하되, 사원이 없는 부서의 정보도 같이 출력한다.
select e.ename, e.deptno, d.dname
from emp e right outer join dept d
on e.deptno = d.deptno;③ FULL OUTER JOIN
👉 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다.
👉 TABLE A와 B가 있을 때 RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다. 단, UNION ALL이 아닌 UNION 기능과 같으므로 중복 데이터는 삭제한다.
(OUTER 키워드를 생략할 수 있다.)
📌 예제)
select *
from dept full outer join dept_temp
on dept.deptno = dept_temp.deptno;
select l.deptno, l.dname, l.loc, r.deptno, r.dname, r.loc
from dept l left outer join dept_temp r
on l.deptno = r.deptno
union
select l.deptno, l.dname, l.loc, r.deptno, r.dname, r.loc
from dept l right outer join dept_temp r
on l.deptno = r.deptno2. 2장 2절 집합 연산자
1) 집합 연산자(set operator)
👉 두 개 이상에 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용한다. 테이블의 자료를 '세로', 컬럼 기준으로 합쳐준다. 즉, 집합 연산자는 2개 이상의 SELECT문을 연결하는 것이다.
👉 SELECT절의 칼럼 수가 동일하고 SELECT절의 동일 위치에 존재하는 칼럼의 데이터 타입이 같을 때 사용이 가능하다.
👉 집합 연산자는 사용상의 제약조건을 만족한다면 어떤 형태의 SELECT문이라도 이용할 수 있다. ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한 번만 기술한다.
2) 집합 연산자 종류
① UNION
👉 여러개의 SQL문의 결과에 대한 합집합이다. 결과에서 모든 중복된 행은 하나의 행으로 만든다.
📌 예제) K-리그 소속 선수 중 소속이 삼성블루윙즈인 선수들의 집합과 K-리그 소속 선수 중 소속이 전남드래곤즈인 선수들의 집합의 합집합을 출력한다.
select team_id, player_name, position, back_no, height
from player
where team_id = 'K02'
union
select team_id, player_name, position, back_no, height
from player
where team_id = 'K07';
select team_id, player_name, position, back_no, height
from player
where team_id = 'K02' or team_id = 'K07';
select team_id, player_name, position, back_no, height
from player
where team_id in('K02', 'K07');② UNION ALL
👉 여러개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과에 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다.
👉 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있다.)
👉 중복을 배제한 UNION이 꼭 필요한 경우가 아니라면, UNION ALL을 우선적으로 고려한다.
📌 예제) K-리그 소속 선수 중 소속이 삼성블루윙즈인 선수들의 집합과 K-리그 소속 선수 중 소속이 전남드래곤즈인 선수들의 집합의 합집합을 출력한다.
select team_id, player_name, position, back_no, height
from player
where team_id = 'K02'
union all
select team_id, player_name, position, back_no, height
from player
where team_id = 'K07';③ INTERSECT
👉 여러개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
📌 예제) K-리그 소속 선수 중 소속이 삼성블루윙즈인 선수들의 집합과 K-리그 소속 선수 중 포지션이 골기퍼(GK)인 선수들의 집합의 교집합을 출력한다.
select team_id, player_name, position, back_no, height
from player
where team_id = 'K02'
intersect
select team_id, player_name, position, back_no, height
from player
where position = 'GK';
select team_id, player_name, position, back_no, height --IN 서브쿼리
from player
where team_id = 'K02'
and player_id in(select player_id
from player
where position = 'GK');
select team_id, player_name, position, back_no, height -- EXISTS 서브 쿼리
from player x
where x.team_id = 'K02'
and exists(select 1
from player y
where y.player_id=x.player_id and position = 'GK');④ EXCEPT(MINUS)
👉 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다. (ORACLE은 MINUS)
📌 예제) K-리그 소속 선수 중 소속이 삼성블루윙즈인 선수들의 집합에서 K-리그 소속 선수 중 포지션이 미드필더(MF)가 아닌 선수들의 집합을 출력한다.
select team_id, player_name, position, back_no, height
from player
where team_id='K02'
minus
select team_id, player_name, position, back_no, height
from player
where position = 'MF';
select team_id, player_name, position, back_no, height -- 논리 연산자
from player
where team_id='K02' and position <> 'MF';
select team_id, player_name, position, back_no, height -- NOT EXISTS 또는 NOT IN 서브쿼리
from player
where team_id='K02'
and not in (select player_id
from player
where position = 'MF');3. 2장 3절 계층형 질의와 셀프 조인
1) 계층형 질의(Hierarchical Query)
👉 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용한다.
👉 계층형 데이터란
동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
- 형태
SELECT 칼럼명
FROM 테이블명
WHERE 조건
START WITH 조건
CONNECT BY PRIOR 조건;✔ SATRT WITH : 계층 구조 전개의 시작 위치 지정
✔ CONNECT BY : 다음에 전개될 자식 데이터 지정
✔ PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정
👉 PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모->자식) 방향으로 내려가는 순방향으로 전개를 한다.
👉 PRIOR 부모 = 자식 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식->부모) 방향으로 올라가는 역방향으로 전개를 한다.
✔ NOCYCLE : 동일한 데이터가 전개되지 않음
✔ ORDER SIBLINGS BY : 형제 노드간의 정렬 수행
✔ WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)
✔ LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
✔ CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면1, 그렇지 않으면 0
✔ CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)
✔ SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
✔ CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시하는 단항 연산자
① 순방향 전개
📌 예제)
select empno, mgr
from emp
start with mgr is null
connect by prior empno = mgr;
select level, empno, mgr
from emp
start with mgr is null
connect by prior empno = mgr;
select level, lpad(' ', 4*(level-1)) || empno, mgr,
connect_by_isleaf isleaf
from emp
start with mgr is null
connect by prior empno = mgr;
select level, lpad(' ', 4*(level-1)) || empno, mgr,
connect_by_root empno, sys_connect_by_path(empno,'/') path
from emp
start with mgr is null
connect by prior empno = mgr;② 역방향 전개
📌 예제)
select level, lpad(' ', 4*(level-1)) || empno, mgr
from emp
start with empno='7566'
connect by prior mgr = empno;2) 셀프 조인
👉 동일 테이블 사이의 조인을 말한다. 따라서 FROM절에 동일 테이블이 두번 이상 나타난다.
👉 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.
👉 동일한 테이블이지만, 개녑적으로는 두 개의 서로 다른 테이블을 사용하는 것과 동일하다.
📌 예제)
select a.empno, a.ename, b.empno, b.ename
from emp a, emp b
where a.mgr = b.empno
order by a.empno;
select a.empno, a.ename, b.empno, b.ename
from emp a left outer join emp b
on a.mgr = b.empno;📌 예제) 구구단
select t1 || '*' || t2 || '=' || t1*t2
from (select level t1 from dual connect by level<=9) a,
(select level t2 from dual connect by level<=9) b;4. 2장 4절 서브쿼리
1) 서브 쿼리(Subquery)
👉 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다. 즉, 서브쿼리가 메인쿼리에 포함되는 종속적인 관계이다.
👉 큰 개념의 조인에 포함시킬 수도 있으나, 보통은 조인과 구분한다.
- 주의사항
- 서브쿼리를 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관없다.
- 인라인뷰를 제외한 서브쿼리에서는 ORDER BY를 사용하지 못한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용가능하다.
- 서브쿼리 위치에 따른 분류
where절: nested subquery / 연산자에 따라 단일(=,<등),다중 (in,any,all등)
from절: inline view / 다중행,다중열
select : scalar subquery / 단일행,단일열 - 동작하는 방식에 따른 서브쿼리 종류
연관 : 메인쿼리의 값에 따라 ‘연관’됨
비연관 : 단독으로 동작 - 서브쿼리와 조인
👉 조인과 사브쿼리를 논리적으로 구분하는 가장 좋은 방법은 두 개의 테이블의 위치를 바꿔보는 것이다.
👉 조인은 두 개의 테이블 위치를 바꾸어 보더라도 같은 결과가 나오며, 서브쿼리의 경우는 주종의 관계이므로 일반적으로 다른 결과가 나오게 된다.
👉 서브쿼리에서 두 개의 테이블 위치를 바꿨는데도 같은 결과가 나온다면, 조인으로 바꿀 수 있다는 것이다. 그래서 일반적으로 서브쿼리를 집합적 개념을 사용할 수 있는 조인으로 바꾼다.
2) 서브쿼리 종류 - 반환되는 데이터의 형태에 따라
① 단일행 서브쿼리
👉 서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리를 의미한다. 단일행 서브쿼리는 단일행 비교연산자와 함께 사용된다. 단일행 비교연산자에는 =, <, <=, >, >=, <>이 있다.
👉 서브쿼리가 단일행 비교연산자(=, <, <=, >, >=, <>)와 함께 사용될 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다. 만약, 서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간(Runtime)오류가 발생한다
👉 서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중행 비교연산자와 함께 사용해야 한다.
📌 예제)
select player_name, position, back_no
from player_t
where team_id = (select team_id
from player_t
where player_name = '정남일')
order by player_name;❗ 정남일 선수가 동명이인이 없다면 정상적인 수행이 되지만 동명이인이 다른 팀에 있다면 두개 이상의 team_id가 반환되므로 런타임 오류가 발생한다.
② 다중행 서브쿼리
👉 서브쿼리의 실행결과가 여러 건인 서브쿼리를 의마한다. 다중행 서브쿼리는 다중행 비교연산자와 함께 사용된다. 다중행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다.
- 다중행 비교 연산자
✔ IN : 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR조건)
✔ ALL : 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교연산자로 ">"를 사용했다면 메인 쿼리는 서브 쿼리의 모든 결과 값을 만족해야 하므로 서브 쿼리 결과의 최대값보다 큰 모든 건 이 조건을 만족한다.
✔ ANY : 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로 서브쿼리의 결과의 최소값 보다 큰 모든 건이 조건을 만족한다.
✔ EXISTS : 서브쿼리릐 결과를 만족하는 값이 존재하는지를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러건이라도 1건만 찾으면 더이상 검색하지 않는다.
📌 예제) 선수들 중에서 '정현수'라는 선수가 소속되어있는 팀 정보를 출력하는 서브쿼리 작성
select region_name, team_name, e_team_name
from team
where team_id = (select team_id
from player
where player_name = '정현수')
order by team_name;
select region_name, team_name, e_team_name
from team
where team_id in(select team_id -- in으로 바꿔서 작성하면 동명이인을 확인할 수 있음
from player
where player_name = '정현수')
order by team_name;③ 다중칼럼 서브쿼리
👉 서브쿼리의 실행결과로 여러칼럼을 반환한다.
👉 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다.
👉 서브쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.
📌 예제) 소속팀별 키가 가장 작은 사람들의 정보
select team_id, player_name, position, back_no, height
from player
where (team_id, height) in (select team_id, min(height)
from player
group by team_id)
order by team_id, player_name;3) 서브쿼리 종류 - 동작하는 방식에 따라
① un-correlated(비연관) 서브쿼리
👉 서브쿼리가 메인쿼리 칼럼을 가지고 있지않는 형태의 서브쿼리이다. 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용한다.
② correlated(연관) 서브쿼리
👉 서브쿼리가 메인쿼리의 칼럼을 사용하는 형태의 서브쿼리이다. 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용된다.
📌 예제) 가비 선수는 삼성블루윙즈 소속이므로 삼성블루윙즈 소속의 평균키를 구하고 그 평균키와 가비선수의 키를 비교하여 적을 경우에 선수에 대한 정보를 출력한다. 만약 평균키보다 선수의 키가 크거나 같으면 조건에 맞지 않기 때문에 해당 데이터는 출력되지 않는다.
select t.team_name, m.player_name, m.position, m.back_no, m.height
from player m, team t
where m.team_id = t.team_id
and m.height < (select avg(s.height)
from player s
where s.team_id = m.team_id
and s.height is not null
group by s.team_id)
order by player_name;- EXISTS 서브쿼리
👉 EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다. 아무리 조건을 만족하는 건이 여러건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.
👉 현업에서 조건을 만족하는지를 묻는 로직이 많이 사용되는데 성능에 부담이 적은 EXISTS절을 우선적으로 검토할 필요가 높다.
👉 왼쪽에는 칼럼명이나 상수가 표시되지 않는다.
👉 서브쿼리의 데이터가 필요한 경우가 아니라면 1, 'X'같은 업무적으로 의미없는 상수값을 선택하는 것이 좋다. - 📌 예제) EXISTS 서브쿼리를 사용하여 '20120501'부터 '20120521'사이에 경기가 있는 경기장을 조회한다.
select stadium_id, stadium_name
from stadium
where exists(select 1
from schedule
where sche_date between '20120501' and '20120521'
and stadium.stadium_id = schedule.stadium_id);4) INLINE VIEW
👉 FROM절에서 사용되는 서브쿼리이다. 따라서 테이블 명이 올 수 있는 곳에서 사용할 수 있다. FROM절에 사용된 서브쿼리의 결과가 마치 실행 시에 동작으로 생성된 테이블인 것처럼 사용할 수 있다.
👉 인라인 뷰는 SQL문이 실행될때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(Static View) 라고 하고 인라인 뷰를 동적 뷰(Dynamic view) 라고도 한다.
👉 중첩되어 사용할 수 있으며 일반적으로 가장 안의 인라인뷰부터 수행이 된다.
📌 예제) 인라인 뷰에 정의된 select칼럼을 메인쿼리에서 사용할 수 있다.
select empno
from(select empno from emp order by mgr);📌 예제) 인라인 뷰에 미정의된 select칼럼을 메인쿼리에서 사용할 수 없다.
select mgr
from(select empno from emp order by mgr);👉 인라인 뷰는 동적으로 생성된 테이블이기 때문에 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다. 결과적으로 인라인 뷰에서 정의된 칼럼은 SQL문에서 자유롭게 참조할 수 있다.
📌 예제) 인라인 뷰를 활용하여 K-리그 선수들 중에서 포지션이 미드필더(MF) 선수들의 소속팀명 및 선수 정보를 출력한다.
select t.team_name, p.player_name, p.back_no
from (select team_id, player_name, back_no
from player
where position = 'MF') p, team t
where p.team_id = t.team_id
order by player_name;👉 인라인 뷰의 중요 용도 중에 하나응 집합 간의 조인 횟수를 줄이는 것이다.
👉 조인 집합 간의 관계가 1:1이 아닌 1:M의 관계일 때 M쪽의 데이터를 인라인 뷰에서 먼저 group by 연산을 수행하여 메인쿼리의 집합과 1:1조인을 수행할 수 있다.
📌 예제) 부서 기준으로 사원들의 평균 급여와 최대 급여를 구하고, 부서명 기준으로 정렬한다.
select d.dname, e.avg_sal, e.max_sal
from (select deptno, avg(sal) avg_sal, max(sal) max_sal
from emp
group by deptno) e, dept d
where e.deptno = d.deptno
order by dname;5) VIEW
👉 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 가진다.
👉 뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상테이블(Virtual Table)이라고도 한다.
👉 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성하여 질의를 수행한다.
👉 Oracle의 경우 USER_VIEWS에서 해당 뷰에 사용된 SQL을 확인할 수 있다.
- 장점
- 생성 및 삭제
create view v_player_team as -- 생성
select p.player_name, p.position, p.back_no, p.team_id, t.team_name
from player p, team t
where p.team_id = t.team_id;
create view v_player_team_filter as -- 생성
select player_name, position, back_no, team_name
from v_player_team
where position in('GK', 'MF');drop view v_player_team; -- 삭제
drop view v_player6) 스칼라 서브쿼리
👉 한 행, 한 칼럼(1 row, 1 column)만을 반환하는 서브쿼리이다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
👉 스칼라 서브쿼리도 일종의 함수이므로 중첩해서 사용할 수 있지만, 많은 input이 있더라도 output은 일반적으로 하나만 나오게 된다.
👉 대량의 데이터 처리 시 같은 결과를 얻을 수 있다면 스칼라 서브퀄리가 아니라 조인으로 대체하는 것이 좋다.
- select list 항목
👉 만약 조회된 결과가 없다면(공집합일때), select list의 값은 집계함수와 같이 null로 표시된다. 두 row이상의 결과가 출력되면 DBMS는 에러를 출력하게 된다.
select empno, ename, (select dname from dept where deptno = a.deptno) dname
from emp a;select empno, ename, (select grade from salgrade where emp.sal between losal and hisal) as grade
from emp;- 함수의 인자
select empno, ename, substr((select dname from dept where deptno = e.deptno), 1, 3) as department_name
from emp e;- where절의 조건
select empno, ename
from emp e
where (select dname from dept where deptno = e.deptno)
= (select dname from dept_2 where deptno = e.deptno);- order by절
select empno, ename, deptno
from emp e
order by (select dname from dept where deptno = e.deptno)- case 조건 절
select empno, ename, deptno, (case when deptno in (select deptno from dept)
then 'USA'
else 'OTHER COUNTRY'
end) location
from emp e;- case 결과 절
select empno, ename, deptno, (case when deptno = 20
then (select dname from dept where deptno = 10)
else (select dname from dept where deptno = 30)
end) new_department
from emp e;- having 절
select p.team_id, t.team_name, avg(p.height)
from player p, team t
where p.team_id =t.team_id
group by p.team_id, t.team_name
having avg(p.height) < (select avg(height) from player where team_id = 'K02')5. 2장 5절 그룹 함수
1) 데이터 분석 함수
- Aggregate function
group function의 한 부분이다. count, sum, avg, max, min외 각종 집계 함수를이 포함된다. - group function
집계함수를 제외하면 소그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목들 간의 다차원 소계를 계산할 수 있는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있다. - window function
데이터웨어하우스에서 발전한 기능이며 분석함수(analytic function), 순위함수(rank function)로도 알려져 있다. 6절에서 자세하게 배운다.
2) Group function
❗ ROLLUP, CUBE, GROUPING SETS 사용시 정렬이 필요한 경우는 ORDER BY절에 정렬 칼럼을 명시해야 된다.
① ROLLUP
👉 GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능하다. 계층적 분류를 포함하고 있는 데이터 집계에 적합하다.
📌 STEP 1) 일반적인 GROUP BY절 사용
부서명과 업무명을 기준으로 사원 수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장을 수행한다.
select dname, job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by dname, job;📌 STEP 1-2) GROUP BY절 + ORDER BY절 사용
부서명과 업무명을 기준으로 사원 수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY절을 사용함으로써 부서, 업무별로 정렬한다.
select dname, job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by dname, job
order by dname, job;📌 STEP 2) ROLLUP 함수 사용
부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다.
select dname, job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by rollup(dname, job);📌 STEP 2-2) ROLLUP 함수 + ORDER BY절 사용
부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용한다. 추가로 ORDER BY절을 사용해서 부서, 업무별로 정렬한다.
select dname, job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by rollup(dname, job)
order by dname, job;📌 STEP 3) GROUPING 함수 사용
ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고, 그 외의 결과에는 GROUPING (EXPR) = 0이 표시된다.
select dname, grouping(dname), job, grouping(job), count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by rollup(dname, job);② CUBE
👉 결합 가능한 모든 값에 대하여 다차원 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻을 수 있는 장점을 가지고 있다. 하지만, ROLLUP에 비해 시스템에 부하를 많이 주는 단점이 있다.
👉 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우, 행 간에 정렬 순서는 바뀔 수 있어도 데이터는 같다.
📌 STEP 4) CUBE 함수 사용
위의 조건에서 ROLLUP을 CUBE로 변경하여 수행한다.
select case grouping(dname) when 1 then 'All dept' else dname end as dname,
nvl(job, '소계'), grouping(job), count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by cube(dname, job);📌 STEP 5-2) UNION ALL 사용 SQL
CUBE SQL과 결과는 같지만 여러 문장을 연결하여 작성한다.
select dname, job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by dname, job
union all
select dname, 'All jobs', count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by dname
union all
select 'All dept', job, count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno
group by job
union all
select 'All dept', 'All jobs', count(*) "Total empl", sum(sal) "Total sal"
from emp, dept
where dept.deptno = emp.deptno;③ GROUPING SETS
👉 원하는 부분의 소계만 손쉽게 추출할 수 있다.
6. 2장 6절 윈도우 함수
1) 윈도우 함수(window fuction)
👉 기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면, 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어려운 문제였다.
👉 따라서 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수이다. 윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결할 수 있다.
- 형식
select WINDOW_FUNCTION (arguments) OVER ([partition by 칼럼] [order by절] [windowing정])
from 테이블명
2) Rank 관련 함수
👉 그룹 내 순위 관련 함수로 rank, dense_rank, row_number 함수가 있으며 대부분의 DBMS에서 지원하고 있다.
① RANK 함수
👉 order by를 포함한 쿼리문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다. 이때 특정 범위(partition)내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다.
👉 동일한 값에 대해서는 동일한 순위를 부여하게 된다.
📌 예제) 사원 데이터에서 급여가 높은 순서와 job별로 급여가 높은 순서를 같이 출력한다.
select job, ename, sal,
rank() over (order by sal desc) all_rank,
rank() over (partition by job order by sal desc) job_rank
from emp;② DENSE_RANK 함수
👉 RANK 함수와 흡사하지만, 동일한 순위를 하나의 건수로 취급하는 것이 다르다.
📌 예제) 사원 데이터에서 급여가 높은 순서와 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력한다.
select job, ename, sal,
rank() over (order by sal desc) all_rank,
dense_rank() over (order by sal desc) dense_rank
from emp;③ ROW_NUMBER 함수
👉 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동이한 순위를 부여하는데 반해, ROW_NUMBER 함수는 동일한 값이라도 유니크한 순위를 부여한다.
📌 예제) 사원 데이터에서 급여가 높은 순서와 동일한 순위를 인정하지 않는 등수도 같이 출력한다.
select job, ename, sal,
rank() over (order by sal desc) all_rank,
row_number() over (order by sal desc) row_number
from emp;3) Aggreage 관련 함수
👉 그룹 내 집계 관련 함수로 max, min, avg, count 함수가 있으며 대부분 DBMS에서 지원하고 있는데 SQL SERVER의 집계함수는 over절 내의 order by 구문을 지원하지 않는다.
① MAX 함수
👉 파티션별 윈도우의 최대값을 구할 수 있다.
📌 예제) 사원들의 급여와, 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 구한다.
select mgr, ename, sal, max(sal) over(partition by mgr) as mgr_max
from emp;📌 예제) 부서별로 가장 많은 연봉을 받는 사람의 금액을 구한 후, 부서번호, 직원 이름, 직원 연봉, 팀내최대연봉을 출력한다.
select mgr, ename, sal
from (select mgr, ename, sal, -- inline view (파티션별 최대값 가진 행만 추출)
max(sal) over (partition by mgr) as msal
from emp)
where sal = msal;② SUM 함수
👉 파티션별 윈도우의 합을 구할 수 있다.
📌 예제) over절 내에 order by절을 추가해 파티션 내 데이터를 정렬하고 이전 salary 데이터까지 누적값을 출력한다.
select mgr, ename, sal,
sum(sal) over (partition by mgr order by sal range unbounded preceding)
as mgr_sum
from emp;③ AVG 함수
👉 파티션별 윈도우의 평균을 구할 수 있다.
📌 예제) emp테이블에서 같은 매니저를 두고 있는 사원들의 평균 salary를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
select mgr, ename, empno, sal,
round(avg(sal) over (partition by mgr order by empno
rows between 1 preceding and 1 following)) as ma
-- 현재 행을 기준으로 파티션내에서 앞의 한건, 현재행, 뒤의 한건을 범위로 지정한다.
from emp;④ COUNT 함수
👉 COUNT 함수와 파티션별 rows 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다.
📌 예제) 사원들의 급여 기준으로 정렬하고 본인의 급여보다 급여가 50이하로 적거나 150 이하로 많은 인원수를 출력한다.
select ename, sal,
count(*) over (order by sal range between 50 preceding and 150 following) as mov_count
-- 현재 행의 값을 ㅣㄱ준으로 파티션 내에서 -50에서 +150까지 값을 가진 윈도우 내의 모든 행이 대상이 된다.
from emp;4) 이외에도
① 그룹 내 행순서 관련 함수
👉 first_value, last_value, lag, lead 함수가 있으며 ORACLE에서만 지원되는 함수이다.
② 그룹 내 비율 관련 함수
👉 CUME_DIST, PERCENT_RANK 함수는 대부분의 DBMS에서 지원한다. NTILE 함수는 ORACLE, SQL SERVER에서 지원한다.
7. 2장 7절 DCL
1) DCL(Data Control Langauge)
👉 유저를 생성하고 권한을 제어할 수 있는 명령어이다. 대부분의 데이터 베이스는 데이터 보호와 보안을 위해서 유저와 권한을 관리하고 있다. oracle에는 기본적으로 아래와 같은 유저가 제공된다.
- oracle
유저를 통해 DB를 접속하는 형태이며, ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여 받게 된다. - SQL SERVER
인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후, 로그인과 유저를 매핑해주어야 한다. windows 인증방식과 혼합 모드 방식이 존재한다.
2) 시스템 권한
👉 사용자가 SQL문을 실행하기 위해 필요한 적절한 권한이다.
👉 oracle의 DBA권한을 가지고 있는 system유저로 접속하면 유저 생성 권한(create user)을 다른 유저에게 부여할 수 있다.
✔ GRANT : 권한 부여
✔ REVOKE : 권한 취소
📌 예제) SCOTT우저에게 유저 생성 권한을 부여한 후, SCOTT으로 접속한 뒤 RHJ유저를 생성한다.
GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER(ID/PW)
CREATE USER RHJ IDENTIFIED BY KOREA7;
GRANT CREATE SESSION TO RHJ;
GRANT CREATE TABLE TO RHJ;
REVOKE CREATE TABLE FROM RHJ;3) SESSION 생성 권한
👉 oracle의 RHJ 유저가 생성되었지만 아무런 권한을 받지 못했기 때문에 로그인을 하면 create session 권한이 없다는 오류가 발생할 것이다. 따라서 유저가 로그인을 하려면 create session 권한을 부여받아야 한다.
4) TABLE 생성 권한
👉 oracle의 RHJ 유저는 로그인 권한만 부여되었기 때문에 테이블을 생성하려면 테이블 생성(create table) 권한이 불충분하다는 오류가 발생할 것이다. 따라서 유저가 테이블을 생성하려면 create table 권한을 부여받아야 한다.
5) 오브젝트 권한
👉 oracle의 오브젝트(객체) 권한은 특정 오브젝트인 테이블, 뷰에 대한 select, insert, delete, update 작업 명령어를 의미한다.
👉 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.
👆 오브젝트 권한과 오브젝트와의 관계
6) ROLL
👉 유저에게 알맞은 권한들을 한번에 부여하기 위해 사용하는 것이다.
CREATE ROLE LOGIN_TABLE;
GRANT CREATE TABLE TO LOGIN_TABLE;
DROP USER RHJ CASCADE; --CASCASD : 하위 오브젝트까지 삭제8. 2장 8절 절차형 SQL
1) 절차형 SQL
👉 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.
👉 Procedure, user defined functio, trigger 등이 있다.
👉 저장 모듈이란
PL/SQL 문장을 DB 서버에 저장하여 사용자와 어플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이다. 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.
👉 일반적인 개발 언어처러 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL(ProceduralLangauge)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL SERVER) 등의 절차형 SQL을 제공하고 있다.
2) PL/SQL
- 특징
- Block 구조로 되어있어 각 기능별로 모듈화 가능
- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함
- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능
- PL/SQL은 Oracle에 내장되어 있어 호환성이 좋음
- 응용 프로그램의 성능을 향상시킴
- Bolck단위로 처리해 통신량 줄일 수 있음
- PL/SQL 엔진
👉 PL/SQL 엔진은 PL/SQL Block프로그램을 입력 받으면 SQL 문장과 프로그램 문장을 구분하여 처리한다.
👉 즉, 프로그램 문장은 PL/SQL 엔진이 처리하고 SQL 문장은 Oracle서버의 SQL statement executor가 실행하도록 작업을 분리하여 처리한다.
- PL/SQL 블록 구조
- PL/SQL 기본 문법
CREATE [OR REPLACE] Procedure [Procedure_name]
(argument1 [mode] date_type1,
argument2 [mode] date_type2,
...)
IS [AS]
...
BEGIN
...
EXCEPTION
...
END;
/ -- 데이터베이스에게 프로시저를 컴파일하라는 명령어✔ 선언(DECLARE) : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부
✔ BEGIN~END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리
✔ EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부
3) Procedure
- 생성
📌 예제) SCOTT 유저가 소유하고 있는 dept테이블에 새로운 부서를 등록하는 procedure를 작성한다.
CREATE OR REPLACE Procedure p_DEPT_insert
( v_DEPTNO in number,
v_dname in varchar2,
v_loc in varchar2)
IS
v_result varchar2(100);
cnt number := 0; -- := 대입연산자
BEGIN
SELECT COUNT(*) INTO CNT
FROM DEPT
WHERE DEPTNO = v_DEPTNO;
if cnt > 0 then
v_result := '이미 등록된 부서번호이다';
else
INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (v_DEPTNO, v_dname, v_loc);
COMMIT;
v_result := '입력 완료!!';
end if;
DBMS_OUTPUT.PUT_LINE(v_result);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_result := 'ERROR 발생';
DBMS_OUTPUT.PUT_LINE(v_result);
END;
/- 실행
SET SERVEROUTPUT ON;
execute p_DEPT_insert('90','안녕','몰라');
select * from DEPT;4) 사용자 정의 함수(User Defined Function)
👉 Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합이다. 앞에서 학습한 SUM, SUBSTR, NVL 등의 함수는 벤더에서 미리 만들어둔 내장함수이고 사용자가 별도의 함수를 만들 수도 있다.
👉 Procedure와 다른점은 RETURN을 사용해서 일반적으로 하나의 값을 반드시 되돌려 줘야한다는 것이다.
👉 즉, Function은 Procedure과 달리 SQL문장에서 특정 작업을 수행하고 반드시 수행 결과값을 리턴한다.
- 함수
CREATE OR REPLACE FUNCTION fn_dname(
i_deptno varchar) RETURN varchar
IS
rt varchar(100);
BEGIN
select dname into rt from dept where deptno=i_deptno;
RETURN rt;
END;
/- 실행
select fn_dname('10') from dual;
select empno,ename,job,deptno,fn_dname(deptno) from emp;5) Trigger
👉 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 사용자가 직접 호출하여 사용하는 것이 아니고, 생성 이후 조건이 맞으면 데이터 베이스에서 자동적으로 수행하게 된다.
👉 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.
- 프로시저와 트리거의 차이점
👉 프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, DB 트리거는 BEGIN~END 절 내에 사용 불가능하다.
👉 ROLLBACK을 하면 원 트랜잭션뿐만 아니라 Trigger로 입력된 정보까지 하나의 트랜잭션으로 인식하여 두 테이블 모두 입력 취소가 된다.
- 실습 테이블 생성
CREATE TABLE order_list
(
order_date CHAR(8),
product VARCHAR2(10),
qty NUMBER,
amount NUMBER
);
CREATE TABLE sales_per_date
(
sale_date CHAR(8),
product VARCHAR2(10),
qty NUMBER,
amount NUMBER
);
/- Trigger 생성
CREATE OR replace TRIGGER summary_sales
AFTER INSERT ON order_list
FOR EACH ROW
DECLARE
o_date order_list.order_date%TYPE;
o_prod order_list.product%TYPE;
BEGIN
o_date := :NEW.order_date;
o_prod := :NEW.product;
UPDATE sales_per_date
SET qty = qty + :NEW.qty,
amount = amount + :NEW.amount
WHERE sale_date = o_date
AND product = o_prod;
IF SQL%NOTFOUND THEN
INSERT INTO sales_per_date
VALUES (o_date,
o_prod,
:NEW.qty,
:NEW.amount);
END IF;
END ;
/ - 활용
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',10,300000);
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK2',10,300000);
commit;
SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',6,500000);
commit;
SELECT * FROM ORDER_LIST;
SELECT * FROM SALES_PER_DATE;'자격증 > SQLD' 카테고리의 다른 글
| [SQLD] 비전공자 합격 후기 (0) | 2024.04.02 |
|---|---|
| [SQLD] SQL 기본 (0) | 2024.03.15 |
| [SQLD] 데이터 모델과 SQL (2) | 2024.03.15 |
| [SQLD] 정규화 (Normalization) (1) | 2024.03.15 |
| [SQLD] 데이터 모델링의 이해 (2) | 2024.03.15 |