SQL transaction, view 요약

< SQL 트랜젝션에 관해 >

-- 단위별 처리를 의미함. 한 사용자에 의해 수행되는 한 개 이상의 SQL 구문을 포함하는 가장 작은 논리적인 작업이다.

-- 데이터의 일관성을 보장함

-- A(Atomicity) C(Consistency) I(isolation) D(durablility) 를 만족해야 한다.

-- Transaction 발생 : insert,update,delete...

-- Transaction 종료 : commit, rollback...

create table sa6 as select * from sawon;

select * from sa6;

delete from sa6 where sawon_no>= 10;

rollback; -- 윗줄 sawon_no >=10 삭제한 것을 원복함. // 단 커밋하기 전까지

--

delete from sa6 where sawon_no = 10;

commit; -- 완전히 폭파됨

select * from sa6;

-- save point 사용하여 transaction 처리

select * from sa6;

update sa6 set sawon_pay=777 where sawon_no=11;

savepoint a; -- 중간 저장 지점

update sa6 set sawon_pay=888 where sawon_no=12;

rollback to savepoint a;

select * from sa6;

commit;

select * from sa6;

-- deadlocks : 2개의 트랜젝션이 서로의 진행을 막고 충돌하는 상황

-- 이것은 자원과 시간을 낭비하므로 피해야 됨

-- 트랜젝션을 완료해준다. 일관성 있는 작업이 필요

update sa6 set sawon_name='tom' where sawon_no=11;

rollback;

< View 파일 : 물리적인 테이블을 근거로 논리적인 가상 테이블을 만들어 사용 >

-- select 문의 조건을 파일로 만들어 테이블처럼 사용한다.

-- 복잡하고 긴 select 문을 단순화

-- 보안강화 // 실제 데이터가 아니기 때문에

-- 자료의 독립성 확보

-- 형식 : create[or replace] view 뷰파일명 as select 문

create table jikwon as select * from sawon; -- sawon 테이블을 jikwon 테이블을 새로 생성해서 복사한 것

select * from jikwon;

-- system 계정에서 scott 계정에 대한 view 생성 권한 부여 작업 필요

-- SQL> grant create view to scott; // SQLPlus에서 실행

-- 권한이 부여되었습니다.

create or replace view v_a as

select sawon_no, sawon_name, sawon_pay from jikwon

where sawon_ibsail < '2015-12-31';

select * from v_a;

select sawon_no, sawon_name from v_a;

select sawon_no, sawon_name from v_a where sawon_no <=5;

select count(*) from v_a;

create view v_b as select * from jikwon where sawon_name like '김%' or sawon_name like '박%';

rename jikwon to nice;

select * from v_a; -- 오류 // jikwon 테이블에서 자료를 가져오는데 jikwon 테이블 이름이 변경되면서 데이터를 가져오지 못하는 것

rename nice to jikwon; -- 다시 nice를 직원으로 원복

create view v_c as select * from jikwon order by sawon_pay desc; -- order by : 정렬

select * from v_c;

create view v_d as select sawon_no as bun, sawon_name as irum, sawon_pay * 10000 as ypay

from jikwon where sawon_jik is not null and sawon_pay is not null;

create view v_e as select bun, irum, ypay from v_d where ypay >= 50000000; -- view 파일로 view 파일을 만들 수 있음

select * from v_e; -- v_d 파일에 종속됨

update v_e set irum='tom' where bun=1;

select * from v_e;

select * from v_d;

select * from jikwon; -- view에서 update, delete 하면 원본 테이블도 바뀌게 됨.

update v_e set ypay='100' where bun=1; -- view에선 연산에 의한 값은 사용 불가능

delete from v_e where bun = 1;

delete from v_e where ypay = 55000000;

select * from v_e;

drop view v_e; -- 최종 삭제

create view v_f as select sawon_no, sawon_name, buser_num, sawon_pay from jikwon where sawon_pay > 2000;

select * from v_f;

insert into v_f values(100,'황사', 20, 4550);

insert into v_f values(110,'황진이', 20, 1550); -- 들어는 갔으나 where sawon_pay > 2000 조건에 걸려서 출력되지 않음

create view v_g as

select sawon_jik, sum(sawon_pay) as hap from jikwon group by sawon_jik;

create view v_h as select sawon_name, buser_name, gogek_name from jikwon

inner join buser on buser_num=buser_no

inner join gogek on sawon_no=gogek_damsano;

select * from v_h;

< 문1) 사번  이름    부서  직급  근무년수  고객확보 >

--      1   홍길동  영업부 사원     6        O   or  X

--조건 : 직급이 없으면 임시직, 전산부 자료는 제외

--위의 결과를 위한 뷰파일 v_exam1을 작성

create or replace view v_exam1 as

select distinct sawon_no as 사번,sawon_name 직원명,buser_name 부서,

nvl(sawon_jik,'임시직') 직급,

to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY') as 근무년수,

case nvl(gogek_name,'a')

when 'a' then 'X' else 'O' end 고객확보

from sawon

left outer join buser on buser_num=buser_no

left outer join gogek on sawon_no=gogek_damsano

where buser_name <> '전산부';

select * from v_exam1;

< 문2) 부서명   인원수 >

--     영업부     7

--조건 : 직원수가 가장 많은 부서 출력

--위의 결과를 위한 뷰파일 v_exam2을 작성

create or replace view v_exam2 as

select buser_name 부서명,count(*) 인원수

from buser

inner join sawon on buser_num=buser_no

group by buser_name having count(*)=(select

max(count(*)) from sawon group by buser_num);

select * from v_exam2;

< 문3) 가장 많은 직원이 입사한 요일에 입사한 직원 출력 >

--    직원명   요일     부서명   부서전화

--    한국인  수요일    전산부   222-2222

--위의 결과를 위한 뷰파일 v_exam3을 작성  

--select to_char(sawon_ibsail,'DAY') from sawon;

create or replace view v_exam3 as

select sawon_name 직원명, to_char(sawon_ibsail,'DAY') 요일,

buser_name 부서명, buser_tel 부서전화

from sawon

left outer join buser on buser_num=buser_no

where to_char(sawon_ibsail, 'DAY') in(

select to_char(sawon_ibsail, 'DAY') from sawon

group by to_char(sawon_ibsail, 'DAY')

having count(*) = (select max(count(*)) from sawon

group by to_char(sawon_ibsail, 'DAY')));

select * from v_exam3;