[배운내용정리] 1222 자바 국비교육 _ Oracle

2020년 12월 22일 9시 ~ 15시 30분 zoom으로 수업 진행

 

수업 내용을 그대로 정리해서 올리고 있었는데 너무 학원 수업 내용을 공개해버린 것 같아 걱정이 되어, 한 동안 글을 올리지 못했다. 배운 내용 정리 글을 비공개로 돌리는 것도 생각해봐야겠다.

 

---

INSERT ALL

 서브쿼리를 활용하여 INSERT를 수행할 때 서브쿼리를 사용하는 테이블이 같다면

 두 개 이상의 INSERT를 묶어서 한번에 처리 가능하다.

 

UPDATE

 해당 테이블의 데이터를 수정하는 명령어

 사용형식

 UPDATE 테이블명 SET 컬럼명 = 바꿀값 [WHERE 조건]

 

UPDATE + SUBQUERY

 여러 행을 변경하거나, 여러 컬럼의 값을 변경하고자 할 때

 서브쿼리를 사용하여 UPDATE 작성가능

 UPDATE 테이블명 SET 컬럼명 = (서브쿼리);

 

DCL안에 COMMIT, ROLLBAK, GRANT, LEVOKE가 있는데 그 중에 COMMIT과 ROLLBACK을 TCL 이라고 한다.

 

TCL (TRANSACTION CONTROL LANGUAGE)

 트랜잭션을 제어하는 언어

 COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO

 

트랜잭션

 데이터를 처리하는 작업의 최소 단위

 하나의 트랜잭션으로 이루어진 작업은 반드시 작업 내용이 한꺼번에 저장되거나 원상복구되어야 한다.

 

COMMIT

 트랜잭션이 종료될 때 정상적으로 종료되었다면, 변경한 사항을 영구히 DB에 저장(반영)

 

ROLLBACK

 트랜잭션 작업 중 오류가 발생할 경우 , 잘못 작업한 내역을 취소(복구)

 

SAVEPOINT 임시저장소명

 트랜잭션 수행 중 특정 구역을 나누어 현재까지 진행된 부분만 별도로 저장

 

ROLLBACK TO 임시저장소명

 트랜잭션 수행 중 임시 저장된 SAVEPOINT를 찾아 해당 부분까지 원상복구 시킨다.

 

DDL

 CREATE : 객체(테이블) 생성

 ALTER  : 생성한 객체(테이블) 수정

 DROP   : 객체(테이블) 삭제

 

 

DROP

 객체 자체를 제거하거나 객체의 특정 요소를 제거할 때 사용하는 명령어

사용형식

 1. DROP 객체 객체명

 2. ALTER 객체 객체명 DROP 지우고자하는요소

 

---

CREATE TABLE EMP_DEPT_D1 
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
    FROM EMPLOYEE
    WHERE 1=2;
    
SELECT * FROM EMP_DEPT_D1;

CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID
    FROM EMPLOYEE
    WHERE 1=2;

SELECT * FROM EMP_MANAGER;

--실습1
--EMPLOYEE 테이블에서 D1 부서에 근무하는 직원들의 
--사번, 이름, 부서코드 ,입사일을 조회하여
--EMP_DEPT_D1 테이블에 추가하시오
INSERT INTO EMP_DEPT_D1 (SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
                            FROM EMPLOYEE
                            WHERE DEPT_CODE = 'D1');

SELECT * FROM EMP_DEPT_D1;

--AND

--EMPLOYEE 테이블에서 D1 부서에 근무하는 직원들의 
--사번, 이름, 부서코드, 관리자 사번을 조회하여
--EMP_MANAGER 테이블에 추가하시오.
INSERT INTO EMP_MANAGER (SELECT EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID
                            FROM EMPLOYEE
                            WHERE DEPT_CODE = 'D1');

SELECT * FROM EMP_MANAGER;


--각 테이블의 데이터 전체 삭제하기
DELETE FROM EMP_DEPT_D1;
DELETE FROM EMP_MANAGER;


--INSERT ALL 사용
INSERT ALL 
    INTO EMP_DEPT_D1 VALUES(EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE)
    INTO EMP_MANAGER VALUES(EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1';



--UPDATE : 해당 테이블의 데이터를 수정하는 명령어
--사용형식
--UPDATE 테이블명 SET 컬럼명 = 바꿀값 [WHERE 조건]

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

SELECT * FROM DEPT_COPY;

--D9 부서를 총무부 -> 전략기획부 로 변경
UPDATE DEPT_COPY 
SET DEPT_TITLE = '전략기획부'
WHERE DEPT_ID = 'D9';

COMMIT;

--
--실습2
--EMPLOYEE 테이블에서 
--이소근의 생년월일을 720813으로 변경해보자
--EMP_NO를 수정하는 업데이트문 작성
SELECT EMP_ID, EMP_NAME, EMP_NO, '720813'||SUBSTR(EMP_NO, 7, 14)
FROM EMPLOYEE
WHERE EMP_NAME='이소근';

UPDATE EMPLOYEE
SET EMP_NO = '720813'||SUBSTR(EMP_NO, 7)
WHERE EMP_NAME = '이소근';

COMMIT;


--

--UPDATE + SUBQUERY
--여러 행을 변경하거나, 여러 컬럼의 값을 변경하고자 할 때 
--서브쿼리를 사용하여 UPDATE 작성가능
--UPDATE 테이블명 SET 컬럼명 = (서브쿼리);

CREATE TABLE EMP_SALARY
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
    FROM EMPLOYEE;

SELECT * FROM EMP_SALARY
WHERE EMP_NAME IN('유재식', '방명수');

--방명수 사원의 급여와 보너스를 유재식 사원과 같은 값으로 변경
UPDATE EMP_SALARY 
SET SALARY=(SELECT SALARY FROM EMP_SALARY WHERE EMP_NAME = '유재식'),
    BONUS=(SELECT BONUS FROM EMP_SALARY WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';


--실습3
--노옹철, 전형돈, 정중하, 하동운 사원의 급여와 보너스를
--유재식 사원과 같은 급여, 보너스로 수정하는 UPDATE 구문을 작성하시오
--단, 다중 열 서브쿼리로 구현하여 작성
UPDATE EMP_SALARY 
SET (SALARY, BONUS)=(SELECT SALARY, BONUS FROM EMP_SALARY WHERE EMP_NAME = '유재식')
WHERE EMP_NAME IN ('노옹철', '전형돈', '정중하', '하동운');

COMMIT;
--UPDATE 시에 해당 컬럼의 제약조건에 위배되지 않게 작성해야 한다.,
UPDATE EMPLOYEE
SET DEPT_CODE='D0'
WHERE DEPT_CODE= 'D6';
--에러 integrity constraint (TEST.SYS_C007405) violated - parent key not found
--외래키 제약조건에 위배된다.,

UPDATE EMPLOYEE
SET EMP_ID=NULL
WHERE EMP_ID= 200;
--에러 ORA-01407: cannot update ("TEST"."EMPLOYEE"."EMP_ID") to NULL 
--NOT NULL 제약조건에 위배된다.

UPDATE EMPLOYEE
SET EMP_NO = '621230-1985634'
WHERE EMP_NAME = '노옹철';
--unique constraint (TEST.SYS_C007408) violated
--UNIQUE 제약조건에 위배된다.

SELECT * 
FROM EMPLOYEE
WHERE ENT_YN = 'Y';
--EMP_ID가 222인 사람 한 명 조회된다.

UPDATE EMPLOYEE
SET ENT_YN = DEFAULT
WHERE EMP_ID = 222;
--DEFAULT = 'N'
--다시 ENT_YN이 Y 인 사람 조회하면 아무 데이터도 안 뜬다.


--DELETE
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';
--에러ORA-02292: integrity constraint (TEST.SYS_C007405) violated - child record found
--D1값을 참조하는 테이블이 존재하기 때문에 

COMMIT;

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE';

--SYS_C007405
--해당 제약조건 비활성화
ALTER TABLE EMPLOYEE
DISABLE CONSTRAINT SYS_C007405;

SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'SYS_C007405';

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';
--제약조건 비활성화 시키고 삭제했더니 잘 삭제된다.

SELECT * FROM DEPARTMENT;

ROLLBACK;
--다시 되돌리자

--제약조건 다시 활성화하기
ALTER TABLE EMPLOYEE
ENABLE CONSTRAINT SYS_C007405;

SELECT * FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'SYS_C007405';

COMMIT;

--TRUNCATE : DELETE와 유사하게 테이블의 모든 정보를 삭제하는 명령어
--DELETE보다 빠르다, 하지만 삭제 후 ROLLBACK 불가능하다

SELECT * FROM EMP_SALARY;

--DELET로 데이터 전체 삭제
DELETE FROM EMP_SALARY;

ROLLBACK;


SELECT * FROM EMP_SALARY;

--TRUNCATE 사용
TRUNCATE TABLE EMP_SALARY;
--Table EMP_SALARY이(가) 잘렸습니다. 라고 뜬다 신기신기
SELECT * FROM EMP_SALARY;

ROLLBACK;
SELECT * FROM EMP_SALARY;
--롤백 후에도 아무 데이터가 남아있지 않는 모습


-----------------------------


COMMIT;

CREATE TABLE USER_TBL (
    NO NUMBER UNIQUE,
    ID VARCHAR2(20) NOT NULL UNIQUE,
    PWD VARCHAR2(30) NOT NULL
);

INSERT INTO USER_TBL VALUES(1, 'TEST1', 'PASS1');
INSERT INTO USER_TBL VALUES(2, 'TEST2', 'PASS2');

COMMIT; --현재까지 작업한 DML 내용을 DB에 반영

INSERT INTO USER_TBL VALUES(3, 'TEST3', 'PASS3');

SELECT * FROM USER_TBL;

ROLLBACK;

SELECT * FROM USER_TBL;
--COMMIT한 곳인 2번까지만 저장되어있음

INSERT INTO USER_TBL VALUES(3, 'TEST3', 'PASS3');

SAVEPOINT SP1; --SAVEPOINT 생성

INSERT INTO USER_TBL VALUES(4, 'TEST4', 'PASS4');
SELECT * FROM USER_TBL;

ROLLBACK TO SP1;

SELECT * FROM USER_TBL;
--SAVEPOINT SP1을 생성한 곳까지의 데이터가 조회되는 것을 확인 할 수 있다.


--ALTER
DROP TABLE DEPT_COPY;

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

SELECT * FROM DEPT_COPY;


--DEPT_COPY에 컬럼 추가하기
ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(20));

SELECT * FROM DEPT_COPY;


--DEPT_COPY에 컬럼 삭제하기
ALTER TABLE DEPT_COPY
DROP COLUMN LNAME;

SELECT * FROM DEPT_COPY;

--DEPT_COPY에 기본값을 적용하여 컬럼 추가
ALTER TABLE DEPT_COPY
ADD (LNAME VARCHAR2(20) DEFAULT '한국');

SELECT * FROM DEPT_COPY;


--컬럼에 제약조건 추가하기
DROP TABLE DEPT_COPY2;

CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPARTMENT;

ALTER TABLE DEPT_COPY2
ADD (LNAME VARCHAR2(20) DEFAULT '한국');

SELECT * FROM DEPT_COPY2;

--DEPT_COPY2에 
--기본키 제약조건(DEPT_ID)과 유니크 제약조건(DEPT_TITLE) 추가하기
--필수입력사랑 LNAME 을 설정하기

--1. 기본키
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT PK_DEPT_CP2 PRIMARY KEY(DEPT_ID);

--2.유니크
ALTER TABLE DEPT_COPY2
ADD CONSTRAINT UK_DEPT_TITLE UNIQUE(DEPT_TITLE);

--3. NOT NULL
ALTER TABLE DEPT_COPY2
MODIFY LNAME CONSTRAINT NN_DEPT_LNAME NOT NULL;

SELECT * FROM DEPT_COPY2;



INSERT INTO DEPT_COPY2
VALUES('D10', '샘플부서', 'L1', '한국');
--ORA-12899: value too large for column "TEST"."DEPT_COPY2"."DEPT_ID" (actual: 3, maximum: 2)
--DEPT_ID를 CHAR(2)로 선언해서 D10이 크기가 커서 안담아지는것
DESC DEPT_COPY2;

ALTER TABLE DEPT_COPY2
MODIFY DEPT_ID CHAR(3);


--컬럼의 자료형 수정
ALTER TABLE DEPT_COPY2
MODIFY DEPT_TITLE VARCHAR2(30)
MODIFY LOCATION_ID VARCHAR2(2)
MODIFY LNAME CHAR(20);

ALTER TABLE DEPT_COPY2
MODIFY LNAME VARCHAR2(20) DEFAULT '한국';

INSERT INTO DEPT_COPY2 VALUES('D10' ,'교육부', 'L1', DEFAULT);

SELECT * FROM DEPT_COPY2;

ALTER TABLE DEPT_COPY2
MODIFY LNAME VARCHAR2(20) DEFAULT '미국';

INSERT INTO DEPT_COPY2 VALUES('D12' ,'개발팀', 'L1', DEFAULT);

SELECT * FROM DEPT_COPY2;


--컬럼의 이름을 변경
ALTER TABLE DEPT_COPY2
RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

DESC DEPT_COPY2;


--테이블의 이름 변경
ALTER TABLE DEPT_COPY2
RENAME TO DEPT_COPY_SECOND;

DESC DEPT_COPY2;
--DEPT_COPY2 객체가 존재하지 않습니다. 에러남 왜?
--RENAME TO DEPT_COPY_SECOND;로 테이블 이름을 변경했기 때문에
DESC DEPT_COPY_SECOND;
--이걸로 조회해야함


--테이블 한정
RENAME DEPT_COPY_SECOND TO DEPT_COPY2;
--테이블 이름 변경된다


--컬럼 삭제
ALTER TABLE DEPT_COPY2
DROP COLUMN LNAME;

SELECT * FROM DEPT_COPY2;

--DDL명령어는 COMMIT / ROLLBACK 불가능
--이전에 DML을 사용하였다면 DDL 명령 전까지의 데이터 조작 내용이 자동으로 COMMIT


ALTER TABLE DEPT_COPY2
DROP COLUMN LOCATION_ID;

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_NAME;

ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID;
--ORA-12983: cannot drop all columns in a table
--테이블의 모든 컬럼을 지울수 없다


--여러 컬럼 삭제
ALTER TABLE DEPT_COPY
DROP (DEPT_TITLE, LOCATION_ID);

SELECT * FROM DEPT_COPY;



--제약조건 삭제
CREATE TABLE CONST_TAB(
    EID CHAR(2),
    ENAME VARCHAR2(15) NOT NULL,
    AGE NUMBER NOT NULL,
    DEPT CHAR(5),
    CONSTRAINT PK_TAB PRIMARY KEY(EID),
    CONSTRAINT UK_TAB UNIQUE(ENAME),
    CONSTRAINT CK_TAB CHECK(AGE > 0),
    CONSTRAINT FK_TAB FOREIGN KEY(DEPT)
    REFERENCES DEPARTMENT ON DELETE CASCADE
);

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='CONST_TAB';

--제약조건 삭제
ALTER TABLE CONST_TAB
DROP CONSTRAINT CK_TAB;

ALTER TABLE CONST_TAB
DROP CONSTRAINT UK_TAB
DROP CONSTRAINT FK_TAB
DROP CONSTRAINT PK_TAB;

--NOT NULL 삭제
ALTER TABLE CONST_TAB
MODIFY (ENAME NULL, AGE NULL);

DESC CONST_TAB;


--객체 삭제
DROP TABLE CONST_TAB;
DROP TABLE DEPT_COPY2;

--DROP USER KH;
--에러 insufficient privileges
--시스템 계정에서 실행해야한다.

------------------------------------------------------

--SYSTEM 계정
--계정 생성
CREATE USER KING IDENTIFIED BY KING;

--권한 부여
GRANT CONNECT, RESOURCE TO KING;
--접근, 자원을 다루는 권한 부여
--계정 삭제 권한이 있는것은 아님

--계정 삭제
DROP USER KING;

--계정 추가 삭제 권한 부여
--GRANT CONNECT, RESOURCE , DBA TO KING;
--이거 주면 , KING 계정에서도 계정 생성, 삭제가 가능하다.