SQL - DAY 12

BUMSOO·2024년 6월 25일
sql오라클 DBA

SAVEPOINT

DML작업시에 ROLLBACK 을 도와주는 표시자
ROLLBACK TO 표시자;

  • 표시자 밑에 있는 transaction을 취소한다.
  • 표시자 이름은 중복되면 안된다.
  • 오라클 전용 표시자
INSERT INTO hr.test(id) VALUES (1); --transaction 시작

SAVEPOINT a;

INSERT INTO hr.test(id) VALUES (2);

SAVEPOINT b; -- SAVEPOINT 명칭이 중복되면 이전 포인트는 삭제된다.

INSERT INTO hr.test(id) VALUES (3);

SELECT * FROM hr.test;

ROLLBACK TO b; -- b표시자 전까지 transaction만 취소, 위에 transaction은 살아있다.

COMMIT; -- 1, 2 데이터가 영구히 저장

다중테이블 INSERT(9i)

  • source 테이블에서 데이터를 추출해서 여러개의 target 테이블에 데이터를 로드(insert)하는 SQL문
  • ETL(Extraction(추출,select), Transformation(변형), Loading(적재,insert))
  • 동일한 source 테이블을 중복해서 ACCESS 하는것을 방지하기 위해 만들어짐

무조건 INSERT ALL

INSERT ALL
INTO hr.sal_history(employee_id, hire_date, salary) VALUES(id, day, sal)
INTO hr.mgr_history(employee_id,manager_id, salary) VALUES (id, mgr, sal)
SELECT employee_id AS id,hire_date AS day, manager_id AS mgr, salary AS sal
FROM hr.employees;

VALUES 값이 무조건 들어가야한다

조건 INSERT ALL

insert all
when deptno=10 then 
    into tbl_emp10 values(empno, ename, job, mgr, hiredate, sal, comm,deptno)
when  job='CLERK' then 
    into tbl_emp20 values(empno, ename, job, mgr, hiredate, sal, comm,deptno) 
    --만일 deptno=10이고 job='CLERK'인 사람은 first쓰면 뒤까지 안보니까
    --tbl_emp20에 추가 안됨 (all은 됨)
select * from emp;

조건 INSERT FIRST

insert first--first를 주면 첫번째 조건을 만족하면 뒤에꺼는 안물어보고 건너뛴다
when deptno=10 then 
    into tbl_emp10 values(empno, ename, job, mgr, hiredate, sal, comm,deptno)
when  job='CLERK' then 
    into tbl_emp20 values(empno, ename, job, mgr, hiredate, sal, comm,deptno) 
select * from emp;

MERGE

INSERT, UPDATE, DELETE 문을 한꺼번에 수행하는 SQL문

MERGE INTO hr.dw_emp d -- target table(insert,update,delete)
USING hr.oltp_emp o -- source table(데이터 추출,select)
ON(d.employee_id = o.employee_id) -- 조인조건
WHEN MATCHED THEN -- 키 값이 일치가 되면
    UPDATE SET -- delete 후 update
        d.salary = o.salary*1.1
    DELETE WHERE -- delete 먼저 수행
        o.flag ='d'
WHEN NOT MATCHED THEN -- 키 값이 일치 되지 않으면
    INSERT(d.employee_id, d.last_name, d.salary, d.department_id)
    VALUES(o.employee_id, o.last_name, O.salary, o.department_id);

컬럼추가

ALTER TABLE 소유자.테이블 ADD 컬럼명 컬럼타입(사이즈);

테이블의 통계정보

SELECT * FROM user_tables WHERE table_name = 'EMP'; -- 자신의 테이블
SELECT * FROM dba_tables WHERE table_name = 'EMP' AND owner = 'HR'; --전체 테이블
SELECT * FROM all_tables WHERE table_name = 'EMP' AND owner = 'HR'; -- 다른 유저의 테이블

컬럼의 통계정보

SELECT * FROM user_tab_columns WHERE table_name = 'EMP';

컬럼타입, 크기를 수정

ALTER TABLE 소유자.테이블 MODIFY 컬럼명 컬럼타입(사이즈);

  • 기존 데이터가 저장되어있는 경우에는 타입을 수정시 오류발생할 수 있다.

컬럼 삭제

ALTER TABLE 소유자.테이블 DROP COLUMN 컬럼명;

제약조건

  • 테이블의 데이터에 대한 규칙을 만든다.
  • 데이터에 대한 품질을 향상시키기 위해서 만든다.
  • 제약조건은 수정할 수 없다. 삭제한 후 다시 새롭게 추가해야 한다.

PRIMARY KEY

  • 테이블의 대표키
  • unique(유일한값) 입력
  • null 값은 입력할 수 없다.
  • 테이블 당 하나만 생성
  • 자동으로 unique index(유일한 인덱스) 생성

제약조건 확인
SELECT * FROM user_constraints WHERE table_name = 'EMPLOYEES';

SELECT * FROM dba_constraints WHERE table_name = 'EMPLOYEES' AND owner = 'HR';

제약조건 컬럼 확인
SELECT * FROM user_cons_columns WHERE table_name = 'EMPLOYEES';

SELECT * FROM dba_cons_columns WHERE table_name = 'EMPLOYEES' AND owner = 'HR';

인덱스 정보 확인
SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';

SELECT * FROM dba_indexes WHERE table_name = 'EMPLOYEES' AND owner = 'HR';

인덱스 컬럼 정보 확인

SELECT * FROM user_ind_columns WHERE table_name = 'EMPLOYEES';

SELECT * FROM dba_ind_columns WHERE table_name = 'EMPLOYEES' AND index_owner = 'HR';

PRIMARY KEY 제약조건 위반

ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(id);

INSERT INTO hr.emp(id,name,day) VALUES(1,'scott',sysdate); -- 입력시 id컬럼의 중복 여부를 확인
INSERT INTO hr.emp(id,name,day) VALUES(1,'james',sysdate); -- PRIMARY KEY 제약조건 위반, 오류발생
INSERT INTO hr.emp(id,name,day) VALUES(NULL,'henry',sysdate); -- PRIMARY KEY 제약조건 위반, 오류발생

PRIMARY KEY 제약조건 추가

  • PRIMARY KEY 제약조건
    ALTER TABLE 소유자.테이블 ADD CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼명);

또는
ALTER TABLE 소유자.테이블 ADD PRIMARY KEY(id); -- 제약조건 이름을 생략하게 되면 오라클이 자동으로 SYS_C숫자 이름으로 제약조건이름을 생성

PRIMARY KEY 제약조건 삭제

ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건이름;

또는
ALTER TABLE hr.emp DROP PRIMARY KEY;
-- PRIMARI KEY 제약조건은 해당 방식으로도 삭제가능

FOREIGN KEY

  • 외래키, 참조무결성 제약조건
  • 동일한 테이블이나 다른 테이블의 PRIMARY KEY, UNIQUE KEY 제약조건을 참조한다.
  • 데이터 품질을 위해 사용
  • 중복값 허용, NULL값 허용
  • 종속되는 행을 삭제할 수 없다.
    • primary key를 참조하고 있는 foreign key에 걸린 데이터가 있을 경우 primary key에 해당하는 행을 삭제 할 수 없다.

ALTER TABLE 소유자.테이블 ADD CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼명) REFERENCES 소유자.참조테이블(참조 컬럼명);

참조 무결성 제약 조건을 위반한 INSERT 문구

ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id);

INSERT INTO hr.emp(id,name,day,dept_id) VALUES(1,'lucy',sysdate,20);
INSERT INTO hr.emp(id,name,day,dept_id) VALUES(2,'lucas',sysdate,30);
--무결성제약조건 위반 오류발생, primary key에 없는 키값이 입력되는걸 방지

참조 무결성 제약 조건을 위반한 DELETE 문구

DELETE FROM hr.dept WHERE dept_id = 20; 
--무결성 제약조건 위반 오류발생, 참조하고 있는 자식 데이터가 있기 때문에 삭제불어
DELETE FROM hr.dept WHERE dept_id = 10;
-- 삭제가능 , 참조하고 있는 자식 데이터가 없기 때문에

FOREIGN KEY 제약조건 삭제

ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건이름;

UNIQUE 제약조건

  • 유일한 값만 체크
  • NULL 허용
  • 자동으로 unique index 생성
ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name); 
-- unique 제약조건 설정

INSERT INTO hr.dept(dept_id, dept_name) VALUES(30,'sales');
-- unique 제약조건 위반 오류 발생

INSERT INTO hr.dept(dept_id, dept_name) VALUES(30,null);
-- unique 제약조건은 null를 허용한다.

UNIQUE 제약조건 삭제

1) ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건이름;

2)ALTER TABLE 소유자.테이블 DROP UNIQUE(컬럼명);

CHECK 제약조건

  • 조건에 값이 TRUE인 경우 INSERT, UPDATE 할수 있도록 만드는 제약조건
  • NULL 허용한다.
  • 중복되는 값 허용한다.
ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000);

INSERT INTO hr.emp(id,name,day,dept_id,sal) VALUES(2,'scott',sysdate,10,500);
-- check 제약조건에 위반 오류 발생

CHECK 제약조건 삭제

ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건 이름;

NOT NULL 제약조건

  • NULL 값을 허용할 수 없는 제약조건
  • NOT NULL 제약조건은 modify를 이용해서 추가 해야한다.

ALTER TABLE 소유자.테이블 MODIFY 컬럼이름 CONSTRAINT 제약조건이름 NOT NULL;

ALTER TABLE hr.emp MODIFY name CONSTRAINT emp_name_notnull NOT NULL;

INSERT INTO hr.emp(id,name,day,dept_id,sal) VALUES(4,NULL,sysdate,10,1000);
-- null값을 시도시 not null제약조건 오류 발생

NOT NULL 제약조건 삭제

1) ALTER TABLE 소유자.테이블 DROP CONSTRAINT 제약조건이름;
2) ALTER TABLE 소유자.테이블 MODIFY 컬럼 NULL;

[12일차 후기]

오늘은 교육받은 날중 마지막에 가장 당이 떨어졌다고 느낀 날이었다. 오전 수업 내용중 가장 나를 괴롭혔던 건 다중 테이블 INSERT에 관한 것이었는데, 처음 내용만 듣고는 INSERT ALL WHEN THEN절과 INSERT FIRST 절의 차이가 이해가 가지 않았다. 하지만 많은 구글링과 주변 사람들의 설명을 듣고나니 이제는 확실히 개념이 잡힌 것 같다. INSERT ALL WHEN THEN 절 같은 경우에는 여러 조건절이 있고 앞에 조건절을 충족해 tale1에 insert 한다고 해도 뒤에 조건절까지 봐서 만족한다면 table2에 insert하는 방식이다. 하지만 INSERT FIRST 같은 경우 앞에 조건식에 충족하면 table1에 insert 하고 뒤에 조건식은 이행하지 않는다. 이러한 차이점으로 INSERT ALL 같은 경우 조건절에 중복 충족되어 중복되는 데이터가 있을 수 있는 반면, INSERT FIRST는 중복 데이터가 없다. 그 후 오후의 주된 수업 내용은 제약조건에 관한 내용이었다. 제약조건 같은 경우 3번의 정보처리기사 준비를 하며 어느정도 확립된 개념을 가지고 있었기에 수업 내용을 이해하는데 어려움은 없었다. 하지만 이것 또한 각 제약조건 별로 디테일한 조건들이 달랐기 때문에 새롭게 암기를 해야 할 것 같다. 제약조건들중 가장 흥미로웠던 제약조건은 CHECK 제약조건 이었는데, 이 조건을 컬럼에 걸게 되면 해당 컬럼에는 내가 건 조건을 만족해야지만 데이터 값이 들어올 수 있었다. 이제 점점 SQL이 끝나가는데 강사님께서 자주 언급하시면 아키텍쳐를 빨리 배워보고 싶다.

profile
BUMSOO
이전 포스트

SQL - DAY 11

다음 포스트

SQL - DAY 13

0개의 댓글