Oracle 5일차 : 테이블 복사, 트랜젝션, 뷰, 시퀀스

테이블 복사

• 조건이 false면 구조만 복사, 조건이 true면 데이타까지 복사

--구조만 복사
CREATE TABLE mydept AS 
  SELECT * 
  FROM   dept 
  WHERE  1 = 2; 
	
--데이타도 복사
CREATE TABLE mydept2 AS 
  SELECT * 
  FROM   dept 
  WHERE  1 = 1; 
  
-- myscott 계정의 테이블을 angel 계정으로 복사하려면?
> COPY FROM myscott/a1234 CREATE dept USING SELECT * FROM dept; 		
	
-- myscott 계정의 dept 테이블을 현재 계정으로 복사
copy FROM myscott/a1234 CREATE dept USING SELECT * FROM dept; 
copy FROM myscott/a1234 CREATE emp2 USING SELECT * FROM emp; 
copy FROM myscott/a1234 CREATE salgrade USING SELECT * FROM salgrade; 
		
-- dept의 데이타가 mydept에 삽입됨(테이블간 데이타 복사)
INSERT INTO mydept 
SELECT deptno, 
       dname, 
       loc 
FROM   dept; 

🐣 실습하기

-- 구조만 복사하는 테이블 두개를 생성
CREATE TABLE myemp_hire AS 
  SELECT empno, 
         ename, 
         hiredate, 
         sal 
  FROM   emp 
  WHERE  1 = 2; 

CREATE TABLE myemp_mgr AS 
  SELECT empno, 
         ename, 
         mgr 
  FROM   emp 
  WHERE  1 = 2; 
	
-- emp테이블로부터 두개의 테이블에 insert
INSERT ALL 
INTO myemp_hire 
VALUES (empno, ename, hiredate, sal) 
INTO myemp_mgr 
VALUES (empno, ename, mgr) 
SELECT empno, 
       ename, 
       hiredate, 
       sal, 
       mgr 
FROM   emp; 
	
-- 서브쿼리를 이용한 삭제 : deptno가 20인 사람의 loc와 같은 사람의 데이타를 삭제하기
DELETE FROM mydept 
WHERE  loc = (SELECT loc 
              FROM   dept 
              WHERE  deptno = 20); 
	
-- 평균나이보다 나이가 많은 사람 삭제하기
DELETE FROM client 
WHERE  age > (SELECT Avg(age) 
              FROM   client); 
	
-- 나이가 평균나이보다 큰 사람들의 이름뒤에 '선배' 로 수정하시오
UPDATE student 
SET    name = name||' 선배' 
WHERE  age > (SELECT Avg(age) 
              FROM   student); 
	
-- 최고나이를 가진 사람의 이름을 이름(최고선배), 제일 나이 어린 사람의 이름을 이름(막내)로 수정
-- 단 사이즈가 모자라면 테이블 구조를 수정하시오
UPDATE student 
SET    name = name||'(최고선배)' 
WHERE  age = (SELECT Max(age) 
              FROM   student); 

UPDATE student 
SET    name = name||'(막내)' 
WHERE  age = (SELECT Min(age) 
              FROM   student); 

ALTER TABLE student 
  MODIFY name VARCHAR2(20); 

트랜잭션

용어	설명
Commit	DML 문에 의해실행됐으나 실제로 저장되지 않은모든 데이타를DB에 저장하고트랜잭션 종료(한번 커밋된건 롤백 불가)형식: commit;
Rollback	저장되지 않은(커밋되지 않은) 모든데이타의 변경사항을 취소하고 트랜잭션 종료단, DML명령어(insert, delete, update, merge)만취소 가능함테이블 생성 삭제뭐 이런거는 이걸로 취소 안됨형식 : rollback [to savepoint 이름];
Savepoint	진행중인 트랜잭션을 특정 이름으로 지정하는 명령어(책갈피)Dml 명령문1;Save point a;Dml 명령문2;Rollback to savepoint a;Dml명령문1 까지실행된 상태로돌아간다Commit, rollback 명령 이후 모든savepoint는 없어진다

테이블 복구

명령어	효과
Flashback table 테이블명 to before drop	삭제된 테이블 복구
Show recyclebin	Recyclebin(휴지통개념) 객체정보 조회
Purge recyclebin	휴지통비우기 ->복구불가
Drop table 테이블명purge	삭제시recyclebin에저장하지 않고 완전 삭제

뷰

• 뷰는 하나의 가상 테이블이다
• 뷰는 실제 데이타가 저장되는 것은 아니지만 뷰를 통해 데이타를 관리할 수 있다.
• 뷰는 복잡한 쿼리를 통해 얻을 수 있는 결과를 간단한 쿼리를 써서 구할 수 있게 해준다.
• 한개의 뷰로 여러 테이블에 대한 데이타를 검색할 수 있다.
• 특정 쿼리문을 미리 맥인 테이블이라고 보면 된다.
• 원본 데이터를 복사해오는건 아니고 주소값만 가져옴

Create [or replace] view view_name
As
Subquery

• Create view 는 무조건 새로생성 이므로 기존 이름이 있으면 오류 발생
• Create or replace는 없으면 새로 생성하고 있으면 교체됨
• 뷰는 테이블이랑 달라서 수정 기능은 없음. 그냥 replace로 덮어 씌우는 것임 (라고 배웠는데 update문 먹던데????) 이곳을 참고하자
• With read only를 덧붙이면 읽기 권한만 있는 뷰가 생성됨
• 특정 컬럼 뒤에 WITH CHECK OPTION을 넣으면 바꿀 수 없게됨

🐣 실습하기

-- 뷰 생성시 반드시 뷰 생성 권한을 주자
GRANT CREATE VIEW TO hr; 

-- 뷰 생성시 서브쿼리의 쿼리문에 함수 사용시 반드시 alias를 줘야한다
CREATE VIEW emp_job 
AS 
  SELECT job, 
         Count(*) cnt 
  FROM   emp 
  GROUP  BY job; 

-- 뷰 네임과 내용 조회
SELECT view_name, 
       text 
FROM   user_views 
	
-- 뷰 조회
SELECT * 
FROM   emp_job; 
	
-- Emp_job이라는 뷰 내용 교체(없으면 새로 생성되도록)
CREATE OR replace VIEW emp_job 
AS 
  SELECT e.name, 
         e.job, 
         d.dname, 
         d.loc 
  FROM   emp e, 
         dept d 
  WHERE  e.deptno = d.deptno; 
	
-- Angel 계정에서 join 쿼리를 구현하여 myshopping 이라는 뷰네임으로 생성하시오
-- 고객명, 고객나이, 상품명, 색상, 개수, 구입일 위의 컬럼들을 출력하시오
CREATE VIEW myshopping 
AS 
  SELECT name, 
         age, 
         sangpum, 
         color, 
         cnt, 
         guipday 
  FROM   shop s, 
         client c, 
         cart ct 
  WHERE  s.num = ct.num 
         AND c.user_id = ct.user_id; 
	
-- 원본 데이터 변경시 뷰 내용도 바뀌나? -> 바뀐다
UPDATE client 
SET    name = '호동씨' 
WHERE  name = '김호동'; 

SELECT * 
FROM   myshopping; 
	
-- 뷰에서 데이터 삭제하면 원본 데이터는? -> 삭제안됨
DELETE 
FROM   myshopping 
WHERE  sangpum='니트조끼';SELECT * 
FROM   myshopping;-> 여기에선 삭제됨 
SELECT * 
FROM   shop; -- 여기에선 삭제안됨
	
-- 뷰로 또다른 뷰가 가능한가? -> 가능하다
SELECT * 
FROM   myshopping 
WHERE  color = 'yellow'; 

시퀀스

• 자동 숫자 발생기
• 호출될 때마다 자동으로 유일한 숫자를 생성하는 오라클 객체
• 테이블의 특정 컬럼값을 넘버링하기위해 사용 된다
• 한번 지워지면 다신 그 숫자 못씀

-- 형식
CREATE SEQUENCE 시퀀스명 
START WITH 시작값 
INCREMENT BY 증가값(공차) 
[MAXVALUE 최댓값] 
[MINVALUE 최솟값] 
[CYCLE | NOCYCLE] 
[CACHE | NOCACHE] 
	
-- 전체 시퀀스 목록 조회
SELECT * 
FROM   seq; 
	
-- 현재값 확인
시퀀스명.currval;
	
-- 다음 시퀀스 발생
시퀀스명.nextval;
	
-- 시퀀스 삭제
DROP SEQUENCE 시퀀스명; 

🐣 실습하기

CREATE SEQUENCE seq_person 
  START WITH 1 
  INCREMENT BY 1 
  NOCACHE;