상세 컨텐츠

본문 제목

SQL transaction, view 요약

DATA/SQL-Transform

by 에스프리터 2018. 1. 18. 13:12

본문

728x90
< 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;




본 포스팅은 IT 교육기관인 KIC 캠퍼스의 지원을 받은 리포트입니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.





'DATA > SQL-Transform' 카테고리의 다른 글

SQL 수업내용 요약  (0) 2018.01.22
PL/SQL 기초 연습  (0) 2018.01.22
SQL 계정(사용자) 생성 및 보안 요약  (0) 2018.01.19
SQL subquery 연습문제 풀이  (0) 2018.01.17
SQL union, merge, subquery 명령어 요약  (0) 2018.01.16
SQL join 명령어 요약  (0) 2018.01.16

태그

관련글 더보기

댓글 영역