Deadlocks, 데드락 해결방안, VIEW 테이블, 수정, 삭제, 생성, 연습문제

--Deadlocks : 하나의 클라이언트가 작업을 마무리 못하고 있으면, 그 순간 다른 클라이언트는 작업 할 수 없는 상태에 빠지게된다. 즉, 두 개의 transaction이 서로의 진행을 막고 충동하는 상황 =데드락!
-- 해결방안  : 클라이언트에서 rollback이나 commit으로 작업을 마무리 해주어야, 다른 클라이언트에서 작업을 진행 할 수있다.
select * from sa1;
update sa1 set sawon_name = '신선해' where sawon_no=7;

--View 파일: 물리적인 테이블을 근거로 논리적인 가상의 테이블을 만들어

                   사용
 : select문의 조건을 파일로 만들어 마치 실제 테이블린 것 처럼 사용.
              사용 이유 :
                - 복잡한 query를 view로 단순화 가능
                - 보안 강화 *중요*
                - 동일한 자료로 부터 다양한 결과를 얻을 수 있다.
                - 자료의 독립성 보장
              형식 : create[or replace] view 뷰파일명 as select문
                ~with read only(수정, 삭제, 추가 불가)
                view수정 : alter ~
                view삭제 : drop ~
      계정에 관련 : view를 만드려면 system계정으로 로그인해야만 한다. 아니면 시스템계정에 들어가서 해당계정에 권한을 줘야한다.
      시스템 계정 : system/oracle
      계정 권한 주기 : grant create view to scott;
     
               
select * from sawon;

create or replace view v_a as                --처음 만들때는 create가 실행되고 두번째 부터는 replace가 실행된다.
select sawon_no, sawon_name,sawon_pay,sawon_ibsail from sawon
where sawon_ibsail < '2000-1-1';

select * from v_a;
select sum(sawon_pay) from v_a;

select view_name, text from user_views;  --view파일명과 내용 확인(오라클 명령어이기때문에 몰라도된다.)

create view v_b as select * from sawon
where sawon_name like '김%' or sawon_name like '이%';
select * from v_b;
select sawon_name as 이름, sawon_pay as 연봉 from v_b;

rename sawon to spring;    --sawon table 이름 바꾸기.

select * from v_a; --sawon table을 위에서 이름을 바꿨기때문에, 그 값을 참조해오는 view table도 돌아가지 않는다.
select * from v_b; --즉, 원본테이블을 근거로 조건을 파일로 저장을 해두고, 테이블인척하고 쓰는 것이다.

rename spring to sawon;

create view v_c as select * from sawon order by sawon_pay desc;

select * from v_c;

create view v_d as select sawon_no, sawon_name, sawon_pay*10000 as sawon_pay from sawon;  --주의!! : 칼럼에서 계산을 넣으면 반드시 별명을 줘야한다.!!
select * from v_d;

create view v_e as select sawon_name, sawon_pay from v_d where sawon_pay>=30000000;
select * from v_e;  --view파일로 새로운 view를 만들 수 있다. 그러나 모든 view파일은 원본인 sawon테이블이 사라지는 순간 쓸모가 없어진다.

update sawon set sawon_name='김치국' where sawon_no=1; --원본 테이블의 데이터를 바꿔버리면 연결된 모든 view 파일들도 데이터가 같이 바뀐다. 즉, view들은 원본을 근거로 하고있기때문이다.

select * from sawon;
select * from v_e;
update v_e set sawon_name='공기밥' where sawon_name='김치국'; --*중요* view파일에서 데이터를 바꾸면, 원본도 당연히 같이 바뀐다. 서로 연동되어 있기때문에.
select * from v_e;
select * from sawon;
select distinct gogek_damsano from gogek order by gogek_damsano;
delete from v_e where sawon_name='김부만'; --담당 고객이 없는 직원 삭제
select * from v_e;
update v_e set sawon_pay=1234566 where sawon_name='공기밥';  --가상의 칼럼은 수정에서 제외된다. v_e를 만들때 sawon_pay(sawon_pay*10000) 이라는 가상의 칼럼을 만들 것이므로 이것은 수정이 안된다.
--뷰 파일로 삭제 불가 경우(아래보기)..
 1-계산에 의한 가상 칼럼
 2-join 조건에 의해서 만들어진 녀석(경우에따라서 삭제 불가)
 3-집합함수
 4-group by
 5-distinct에 의한 결과
 -- 위에 다섯가지는 주의 할 것.
 
 create view v_f as select sawon_no, sawon_name, sawon_pay from sawon
 where sawon_pay >= 5000;
 
 select * from v_f;
 insert into v_f values(20,'신기해',6500);
 insert into v_f values(21,'신선해',3500); --*중요*안들어간게아니고 원본인 sawon에는 들어갔는데, v_f에서는 조건에 의해서 걸려져서 안보이는 것이다.
  select * from v_f;   --안보인다. 조건에 의해서 안보이는 것이다.
 select * from sawon;  --보인다. 원본테이블인 sawon에는 들어가 있다.

create view v_g as
select sawon_jik, sum(sawon_pay) as hap, avg(sawon_pay) as ave
from sawon group by sawon_jik;    -->group by에 의한 뷰
select * from v_g; --계산에 의한 값은 select만 가능하다
select * from sawon;
update sawon set sawon_pay=25000 where sawon_no=3;
update sawon set sawon_pay=4500 where sawon_no=3;

create or replace view v_join as select sawon_no, sawon_name, buser_name, sawon_jik from sawon   --join으로 만든 뷰.
inner join buser on buser_num = buser.buser_no
where buser_name in('영업부','전산부');

select * from v_join; --join에 의한 뷰 실행.
update v_join  set sawon_name='지구인' where sawon_name='한국남';  --success
update v_join  set sawon_name='동양인',buser_name'축구부' where sawon_name='지구인'; --error: 조인에 의해 작업할때는 한 테이블의 칼럼만 참여 시킬수있다. 두 테이블의 칼럼을 동시에 수정하려고 해서 에러가 떨어졌다.
select * from v_join;
delete from v_join where sawon_no=15; --하나씩 조건을 주는 것은 가능하다. 그러나 두개씩은 안된다.
select * from sawon;
rollback;

문1) 사번    직원명   부서    직급    구분    근무년수    고객확보
      1     홍길동   총무부   사원    경력      10          0
조건 : 직급이 없으면 임시직으로 표시
      구분란에는 근무년수 5년 이상 경력, 나머지는 일반으로 표시
      고객확보란에는 관리 고객 유무에 따라 O,X 표시
      총무부 자료는 제외한다.
      위의 결과가 출력될 수 있는 v_exam1을 작서하시오.
     

--내방법0000
create or replace view v_exam1 as
select distinct sawon_no 사번,sawon_name 직원명, buser_name, nvl(sawon_jik,'임시직') 직급,
case when to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY')>=5 then '경력' else '일반' end as 구분,
to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY') as 근무년수,
case when sawon_no in(select distinct gogek_damsano from gogek) then 'O' when sawon_no not in(select distinct gogek_damsano from gogek) then  'X' end 고객확보
from sawon
left outer join buser on sawon.buser_num= buser.buser_no
left outer join gogek on sawon.sawon_no = gogek.gogek_damsano
where buser_name<>'총무부'; -- <> 부정

--선생님 방법000 
  create or replace view v_exam1 as
select distinct sawon_no 사번,sawon_name 직원명, buser_name, nvl(sawon_jik,'임시직') 직급,
case when to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY')>=5 then '경력' else '일반' end as 구분,
to_char(sysdate, 'YYYY') - to_char(sawon_ibsail,'YYYY') as 근무년수,
case nvl(gogek_name, 'a') when 'a' then 'X' else 'O' end as 고객확보
from sawon
left outer join buser on sawon.buser_num= buser.buser_no
left outer join gogek on sawon.sawon_no = gogek.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 
(select buser_name, count(buser_num) as count from sawon
right outer join buser on sawon.buser_num= buser.buser_no group by  buser_name order by count(buser_num) desc)
 where rownum<=1;
 
--선생님 방법---
select buser_name as 부서명, count(*) as 인원수 from buser, sawon
where buser.buser_no = buser_num
group by buser_name
having count(*)=(select max(count(*)) from sawon group by buser_num);

 
select * from v_exam2;

문3) 가장 많은 직원이 입사한 요일에 입사한 직원 모두를 v_exam3으로 작성 후 출력하시오.
    직원명    요일   부서명   부서전화
    한국인     화    영업부   02-222-2222
    ...
    ... 쭈루룩 뿌려라.
참고 : 요일 출력 방법 = 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 sawon.buser_num= buser.buser_no
where to_char(sawon_ibsail,'DAY')=
(select yoil from
(select to_char(sawon_ibsail,'DAY') yoil, count(to_char(sawon_ibsail,'DAY')) count
from sawon group by to_char(sawon_ibsail,'DAY') order by count desc)
where rownum <= 1);

select * from v_exam3;