디지털 컨버전스/Oracle

[Oracle] TCL , savepoint, rollback, grant, revoke, View, Sub-Query ( IN / ANY / ALL )

gimyeondong 2020. 3. 20. 08:55

TCL

Transaction Control Language

Transaction

  • 더 이상 나눌 수 없는 작업의 최소단위
  • 오라클에서는 하나의 임시공간으로 상상해보자

 

delete from cafe;
select * from cafe;
--삭제된 것으로 보이지만 롤백 가능

rollback;
--트랜젝션에서 일어난 DML 작업 취소

commit
--진짜로 DBMS에 적용, rollback 불가

 

작업의 원자성 : 작업이 더이상 쪼개지지 않음

savepoint
rollback으로 돌아갈 위치를 태그

 

savepoint beforeDelete;
--rollback으로 돌아갈 위치를 태그

delete from cafe;

rollback to beforeDelete;
savepoint beforeDelete;
delete from cafe;

savepoint beforeInsert;
insert into cafe values(cafe_seq.nextval,'Milk Tea',3000);

select * from cafe;

rollback to beforeDelete;

rollback to beforeInsert;
--"savepoint '%s' never established in this session or is invalid"
--더 오래된 과거로 롤백하면 그후의 세이브포인트도 사라짐

 

commit을 안해놓으면 나는 insert했는데 프로그램은 데이터를 못받는 상황 발생

"값이 안나와요"->"commit 했나요?"

DCL

Data Control Language
Grant / Revoke

-- DCL 연습용 임시 계정 생성

--AdminConnection 계정 연결
create user temp identified by temp;
grant resource, connect to temp;
-- 권한들이 모여서 Role이 된다.

--temp 계정 연결
select * from employee;
select * from kh.employee;
--"table or view does not exist"
--kh 계정의 table에 접근 권한이 없다.
-- 권한 주기 Grant 

--kh 계정 연결
grant select on employee to temp;
--role이 아니라 권한
--employee 테이블을 select할 권한을 사용자 temp에게 부여
--DCL이라 commit 없어도 동작

--temp 계정 연결
select * from kh.employee;
--성공
-- 권한 뺏기 Revoke

--kh 계정 연결
revoke select on employee from temp;

--temp 계정 연결
select * from kh.employee;
--"table or view does not exist"

객체
User / Table / Sequence / 

 

View

하나 이상의 테이블에서 일부 데이터를 선택하여 가상의 테이블을 생성한 것

 

  • view를 쓰는 이유
    • 사용자temp에 employee테이블에 대한 select권한을 부여하면
      employee테이블의 전체정보에 접근, 일부만 보여주고 싶다면
      employee 테이블의 내용 중 일부를 view로 만든뒤
      만들어진 view의 select 권한을 부여
  • 임시로 table을 만들지 않고 view를 쓰는 이유
    • table은 저장소에 실제로 존재,
      복사본을 만들 경우 독립적인 객체 2개가 존재.
      실시간으로 변경되는 테이블의 경우 view는 바로 반영

      자바에서는 '깊은복사', '얕은복사' 라고 함

 

create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--as: ~로서
--"insufficient privileges"

view 생성 권한 부여

 

--	kh계정
create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--as: ~로서
--"insufficient privileges"
-- view 만들 권한 없음

create table emp_table
as
select emp_id, emp_name, emp_no from employee;
select * from emp_table;
--Table EMP_TABLE이(가) 생성되었습니다.
--부여된 resource 권한에는 table생성만 가능, view생성 권한은 별도
--	admin 계정
grant create view to kh;
--	kh계정
create view emp_view
as
select emp_id, emp_name, emp_no from employee;
--View EMP_VIEW이(가) 생성되었습니다.
select * from emp_view;

table 사본과 view 비교 : 변동사항 없을 때

--  kh 계정
grant select on emp_table to temp;
grant select on emp_view to temp;
--  temp 계정
select * from kh.employee;
--"table or view does not exist"

select * from kh.emp_table;
select * from kh.emp_view;
-- 변동 사항 없을 때는 같은 결과

table 사본과 view 비교 : 변동사항 있을 때

-- 변동사항 : 사원이름 '선동일'이 '선동이'로 변경

--  kh 계정
update employee
    set emp_name = '선동이'
  where emp_id = 200;
--  temp 계정

select * from kh.emp_table;
-- '선동일'
select * from kh.emp_view;
-- '선동일'

--kh세션에서 변동사항 commit을 안하면 다른세션에서는 적용되지 않음

문제 해결 : 변동사항 commit을 안하면 다른세션에서는 적용되지 않음

-- 변동사항 commit

--	kh 계정
commit;
--  temp 계정

select * from kh.emp_table;
-- '선동일'
select * from kh.emp_view;
-- '선동이'

예제 : 사번, 이름, 부서명 view 만들기

--  kh 계정

create view emp_view2
as
select emp_id, emp_name, dept_title 
from employee e, department d
	where e.dept_code = d.dept_id;

grant select on emp_view2 to temp;
--  temp 계정
select * from kh.emp_view2;

데이터베이스 시작~ 여기까지 시험 범위

 

DQL : sub-Query

select manager_id from employee where emp_name='전지연';
--214

select emp_name from employee where emp_id=214;

--214 부분에 대입
select
    emp_name
from employee
    where emp_id=(select manager_id from employee where emp_name='전지연');
-- 회사의 평균 급여보다 급여를 더 많이 받는 사원의
--  사번, 이름, 직급코드, 급여

select
    emp_id,
    emp_name,
    job_code,
    salary
from employee
    where salary >= avg(salary);
	--"group function is not allowed here"
	--where절은 그룹함수 쓸 수 없음


--Sub-Query
 
--안에 넣을 값 : 평균 급여
select
    avg(salary)
from employee;

--Sub-Query를 where절에 사용
select
    emp_id,
    emp_name,
    job_code,
    salary
from employee
    where salary >= (select avg(salary) from employee);
    --단일행 단일열 서브쿼리

Sub-Query란

쿼리 내에서 쿼리를 쓴다

그 쿼리가 무엇을 반환하냐에 따라 문법적 차이가 조금 난다.

다중행 다중열 Sub-Query

단일행 서브쿼리

-- 1. 직원 중에서 윤은해 직원과 급여가 같은 사원들의
--  사원번호 사원이름 급여를 출력하세요
select salary from employee where emp_name='윤은해';

select
    emp_id,
    emp_name,
    salary
from employee
    where salary=(select salary from employee where emp_name='윤은해');
-- 2. 급여가 제일 많은 사원과 적은 사원의 이름과 급여 출력
select emp_name, salary 
from employee 
    where salary =(select max(salary) from employee) or
          salary =(select min(salary) from employee);
-- 3. D1, D2 부서에 근무하는 사원들 중에서
-- 기본급여가 D5 부서 직원들의 '평균월급' 보다 많은 사람들만
-- 부서번호, 사원번호, 사원명, 월급을 나타내세요.

select
    dept_code,
    job_code,
    emp_name,
    salary
from employee
    where dept_code in ('D1','D2') and
    salary > (select avg(salary) from employee where dept_code = 'D5');

다중행 서브쿼리

 

--다중행 서브쿼리

select * 
from employee 
where dept_code in (select dept_code from employee where emp_name in ('송종기','박나라'));
-- 다중행 비교는 (=)이 아니라 in
-- 1. 차태연 / 전지연 직원의 급여등급 (sal_level) 같은
-- 사원의 이름과 직급명을 출력하세요.

select
    e.emp_name,
    j.job_name
from employee e, job j
    where e.job_code=j.job_code and
    e.sal_level in 
    (select sal_level from employee where emp_name in ('차태연','전지연')
    );
-- 2. 직급이 대표, 부사장이 아닌 모든 사원의
--  이름, 부서명, 직급코드를 출력하고 부서별로 출력.

select emp_name, dept_title, job_code
from employee, department
where employee.dept_code = department.dept_id
and job_code not in (select job_code from job where job_name in ('대표','부대표'));

IN / ANY / ALL

 

-- ANY
select emp_name, salary from employee
    where salary > any (2000000,5000000);
    
select emp_name, salary from employee
    where salary > 2000000 or salary > 5000000;
-- ALL

select emp_name, salary from employee
    where salary > all (2000000,5000000);
    
select emp_name, salary from employee
    where salary > 2000000 and salary > 5000000;
select emp_name, salary from employee
    where salary > any (select salary from employee where job_code= 'J3')
-- 1. 'D1' 또는 'D5' 부서코드를 가지고 있는 사원들의 
--  급여중에서 가장 높은 급여 보다 작은 
--  모든 사원들의 이름, 급여, 부서코드를 출력하세요.

select
    emp_name,
    salary,
    dept_code
from employee
    where salary < any (select salary 
                        from employee 
                        where dept_code in ('D1','D5'));
-- 2. 부서별 평균 급여를 조사하였을때 가장 낮은 부서의 급여보다
--    높거나 같은 모든 사원들의 이름, 급여, 부서명을 출력하시오

--	ANSI 표준
select
    emp_name,
    salary,
    dept_title
from employee left join department on (dept_code =dept_id)
    where 
    salary >= any(select avg(salary) from employee group by dept_code);
    
--	오라클    
select
    emp_name,
    salary,
    dept_title
from employee, department
    where employee.dept_code=department.dept_id(+) and
    salary >= any(select avg(salary) from employee group by dept_code);

다중열 : 한줄만 여러컬럼

-- 다중행 다중열    
select dept_code, job_code from employee;

다중행 다중열

-- 다중열 
select dept_code, job_code from employee
    where emp_name='유하진';

다중열

-- 다중열 일때 비교하는 컬럼들을 괄호로 묶어서 (,) 로 구분해 넣기
-- dept_code,job_code둘다 비교

select emp_name, dept_code, job_code from employee
where (dept_code,job_code)=(select dept_code, job_code from employee
    where emp_name='유하진');
-- 다중행 다중열 비교시 (=) 대신 in 사용

select emp_name, dept_code, job_code from employee
where (dept_code,job_code) in (select dept_code, job_code from employee
    where emp_name in('유하진','박나라'));
-- 3. 각 부서별 가장 급여를 많이 받는 직원들의 목록

select
    dept_code,
    emp_name,
    salary
from employee
    where (salary,dept_code) in (select max(salary),dept_code
                     from employee
                     group by dept_code)
order by 1;
-- 4. 직급별 최소 급여를 받는 직원의
--   이름, 직급코드, 부서코드, 입사일, 연봉을 출력

select
    emp_name,
    job_code,
    dept_code,
    hire_date,
    salary*12
from employee
where (salary,dept_code) in (select min(salary),dept_code from employee group by dept_code)
order by 3;

-- 5. 기술지원부에 속한 사람들의 이름, 부서코드, 급여를 출력하시오

select
    emp_name,
    dept_code,
    salary
from employee join department on (dept_code=dept_id)
    where (dept_code) in (select dept_code from employee where dept_title = '기술지원부');
    
select
    emp_name,
    dept_code,
    salary
from employee 
    where (dept_code) in (select dept_id from department where dept_title = '기술지원부');
--JOIN 을 쓰지 않고 같이 출력
-- 상관 Sub-Query

select
    emp_name,
    (select dept_title from department d where d.dept_id = e.dept_code) "부서명"
from employee e;

--mainQuery의 컬럼을 subQuery가 쓸 수는 있지만
--subQuery의 컬럼을 mainQuery가 쓸 수는 없음
--Inline view
--from절에 subQuery를 사용

select
    emp_name,
    dept_code
from (select emp_code,dept_code,job_code,email from employee);