상세 컨텐츠

본문 제목

SQL transaction, view 요약

Machine Learning

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

본문

< 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 캠퍼스의 지원을 받은 리포트입니다.
혹시 잘못되거나 문제 소지시 댓글 남겨주시면 조치하겠습니다.





'Machine Learning' 카테고리의 다른 글

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

태그

관련글 더보기

댓글 영역