127.
TRIGGER
특정 테이블에 INSERT, UPDATE, DELETE 와 같은 DML문이 수행되었을 때,
데이터베이스에서 자동으로 동작하도록 작성된 저장 프로그램
(사용자가 직접 호출해서 사용하는 것이 아니고 데이터 베이스에서 자동적으로 수행함)
126.
Trigger에 대한 설명
- Trigger는 데이터베이스에 의해서 자동으로 호출되고 수행된다.
- Trigger는 특정 테이블에서 INSERT, UPDATE, DELETE 문이 수행되었을 때 호출되도록 정의할 수 있다.
- Trigger는 Procedure와 달리 Commit, Rollback 과 같은 TCL을 사용할 수 없다.
- Trigger는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.
125.
절차형 SQL 모듈에 대한 설명
- 저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다.
- 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고,
그 결과를 리턴하는 SQL의 보조적인 역할을 한다.
- 트리거는 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때
데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다.
* 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용하는것은 트리거의 용도 이다.
124.
임시부서(TMP_DEPT) 테이블로부터 부서(DEPT) 테이블에 데이터를 입력하는 PL/SQL
조건: 부서 테이블에 데이터를 입력하기 전, 부서테이블의 모든 데이터를 ROLLBACK이 불가능하도록 삭제가 필요하다.
[PL/SQL]
create or replace procedure insert_dept authid current_user as
begin
execute immediate 'TRUNCATE TABLE DEPT';
INSERT /*+ APPEND */ INTO DEPT (DEPTNO, DNAME, LOC)
SELECT DEPTNO, DNAME, LOC
FROM TMP_DEPT;
commit;
end;
/
> PL/SQL 에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용해야한다.
123.
PL/SQL에 대한 설명
- 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE 절의 조건 등으로 대입할 수 있다.
- Procedure, User Defined Function, Trrigger 객체를 PL/SQL로 작성할 수 있다.
- Procedure 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL 문장은 SQL실행기가 처리한다.
- PL/SQL로 작성된 Procedure, User Define Function은 작성자 기준으로 트랜젝션 분할할 수 있다.
(프로시저 내에서 다른 프로시저 호출할 때 호출 프로시저 트랜젝션과는 별도로
PRAGMA AUTONOMOUS_TRANSACTION 을 선언하여 자율 트랜젝션 처리가 가능하다)
122.
User1 사용자가 릴레이션 R 을 생성하였다.
User1 : GRANT SELECT, INSERT, DELETE ON R TO User2 WITH GRANT OPTION;
User2 : GRANT SELECT, INSERT, DELETE ON R TO User3;
User1 : REVOKE DELETE ON R FROM User2;
User1 : REVOKE INSERT ON R FROM User2 CASCADE;
USER2 는 DELETE, INSERT 권한을 회수당했다.
USER3 은 USER1 의 CASCADE 명령으로 인해 INSERT 권한이 USER2와 함께 삭제되었다.
121.
DBMS 사용자를 생성하면 기본적으로 많은 권한을 부여하는데,
DBMS 관리자의 권한 관리의 부담과 복잡함을 줄이기 위해
다양한 권한을 그룹으로 묶어 관리할 수 있도록 [ROLE] 을 제공한다.
120.
UPDATE A_User.TB_A
SET col1='AAA'
WHERE col2=3
B_User 가 위 작업을 수행할 수 있도록 권한을 부여하는 명령문
GRANT SELECT, UPDATE ON A.User.TB_A TO B_User
119.
GRANT : ROLE 을 DBMS USER 에게 부여하기 위해 사용하는 명령어
REVOKE : ROLE 을 DBMS USER 에게서 회수하기 위해 사용하는 명령어
118.
[ Test - Oracle live server ]
create table TBL (
id varchar2(10),
start_val number,
end_val number
);
insert into TBL values('A', 10, 14);
insert into TBL values('A', 14, 15);
insert into TBL values('A', 15, 15);
insert into TBL values('A', 15, 18);
insert into TBL values('A', 20, 25);
insert into TBL(id, start_val) values('A', 25);
commit;
select * from tbl;
select id, start_val, end_val
from (
select id, start_val, nvl(end_val, 99) end_val,
(case when start_val = lag(end_val) over (partition by id
order by start_val, nvl(end_val, 99)) then 1
else 0
end) flag1,
(case when end_val = lead(start_val) over (partition by id
order by start_val, nvl(end_val, 99)) then 1
else 0
end) flag2
from tbl
)
where flag1 = 0 or flag2 = 0;
핵심:
case1: start_val 가 end_val 이전행의 값이랑 같은지 비교해서 같을때 결과값은 1, 다를때 결과값은 0이다.
case2: end_val 가 start_val 이후행의 값이랑 같은지 비교해서 같을때 결과값은 1, 다를때 결과값은 0이다.
서브에서 생성했던 flag 컬럼 중 0인값들의 행만 메인에서 select 한다.
117.
[ Test - Oracle live server ]
create table 사원(
사원ID number,
부서ID number,
사원명 varchar2(20),
연봉 number
);
insert into 사원 values (001, 100, '홍길동', 2500);
insert into 사원 values (002, 100, '강감찬', 3000);
insert into 사원 values (003, 200, '김유신', 4500);
insert into 사원 values (004, 200, '김선달', 3000);
insert into 사원 values (005, 200, '유학생', 2500);
insert into 사원 values (006, 300, '변사또', 4500);
insert into 사원 values (007, 300, '박문수', 3000);
commit;
select * from 사원;
select Y.사원ID, Y.부서ID, Y.사원명, Y.연봉
from (select 사원ID, max(연봉) over(partition by 부서ID) as 최고연봉
from 사원) X, 사원 Y
where X.사원ID = Y.사원ID
and X.최고연봉 = Y.연봉;
핵심:
Inline-view 를 통해 사원ID와 부서별최고연봉을 X테이블로 생성하여
최고연봉과 같은 연봉을 받는 사람만 출력한다.
116.
select 상품분류코드, AVG(상품가격) as 상품가격,
count(*) over(order by avg(상품가격)
range between 10000 preceding
and 10000 following) as 유사개수
from 상품
group by 상품분류코드;
유사개수 컬럼은 상품분류코드별 평균상품가격을 서로 비교하여
-10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한것이다.
range: 값
preceding: 이전
following: 이후
group by 이후 select 이 실행되기 때문에 상품분류코드별로 계산된다.
115.
[ Test - Oracle live server ]
create table 추천내역 (
추천경로 varchar2(20),
추천인 varchar2(20),
피추천인 varchar2(20),
추천점수 number
);
insert into 추천내역 values ('sns', '나한일', '강감찬', 75);
insert into 추천내역 values ('sns', '이순신', '강감찬', 80);
insert into 추천내역 values ('이벤트응모', '홍길동', '강감찬', 88);
insert into 추천내역 values ('이벤트응모', '저절로', '이순신', 78);
insert into 추천내역 values ('홈페이지', '저절로', '이대로', 93);
insert into 추천내역 values ('홈페이지', '홍두깨', '심청이', 98);
commit;
select * from 추천내역;
select 추천경로, 추천인, 피추천인, 추천점수
from (select 추천경로, 추천인, 피추천인, 추천점수,
row_number() over(partition by 추천경로 order by 추천점수 desc) as rnum
from 추천내역)
where rnum = 1;
- row_number : 동일한 순위더라도 고유한 순위를 부여한다.
114.
| RANK() |
DENSE_RANK() |
ROW_NUMBER() |
| 1 |
1 |
1 |
| 2 |
2 |
2 |
| 2 |
2 |
3 |
| 4 |
3 |
4 |
| 5 |
4 |
5 |
113.
create table 고객(
고객번호 number primary key,
고객명 varchar2(20)
);
create table 월별매출(
월 varchar2(20),
고객번호 number,
매출액 number,
constraint multi_pk primary key (월, 고객번호)
);
insert into 고객 values (001, '홍길동');
insert into 고객 values (002, '이순신');
insert into 고객 values (003, '강감찬');
insert into 고객 values (004, '이상화');
insert into 고객 values (005, '이규혁');
commit;
insert into 월별매출 values ('202301', 001, 200);
insert into 월별매출 values ('202301', 002, 300);
insert into 월별매출 values ('202301', 003, 250);
insert into 월별매출 values ('202301', 004, 300);
insert into 월별매출 values ('202301', 005, 250);
insert into 월별매출 values ('202302', 001, 150);
insert into 월별매출 values ('202302', 002, 150);
insert into 월별매출 values ('202302', 004, 200);
insert into 월별매출 values ('202302', 005, 100);
insert into 월별매출 values ('202303', 002, 100);
insert into 월별매출 values ('202303', 003, 100);
insert into 월별매출 values ('202303', 004, 200);
insert into 월별매출 values ('202303', 005, 350);
commit;
select * from 월별매출;
select 고객번호, 고객명, 매출액, rank() over(order by 매출액 desc) as 순위
from (
select A.고객번호, max(A.고객명) as 고객명, sum(B.매출액) as 매출액
from 고객 A inner join 월별매출 B
on (A.고객번호 = B.고객번호)
group by A.고객번호
)
order by 순위;
- max(A.고객명) : group by 로 묶지 않았기 때문에 단순히 하나의 행으로 표기하려고 max 쓴 것으로 보임
- 동일순서에 대한 정렬은 답지와 위 코드의 실행결과가 약간 다름 (?)
112.
윈도우 함수에 대한 설명
- Partition 과 Group By 구문은 의미적으로 유사하다.
- Partition 구문이 없으면 전체 집합을 하나의 Partition 으로 정의한 것과 동일하다.
- 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.
- 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.
111.
Groupping set 은 다양한 소계 집합을 만들 수 있다.
표시된 인수들에 대한 개별 집계를 구한다.
select 상품ID, 월, sum(매출액) as 매출액
from 월별매출
where 월 between '2014.10' and '2014.12'
group by grouping sets((상품ID, 월));
> (상품ID, 월) 별 집계 데이터를 출력한다. (괄호 주의)
