[ 데이터 조작어(DML) ]
1. 데이터 갱신
1) INSERT
(3) subquery를 이용한 insert
→ value 자리에 query 문으로 값을 가져옴
▷ 예제
-- ex1) 부서아이디가 80번인 사원의 정보를 emp_blank 테이블에 입력
insert into emp_blank
select *
from employees
where department_id = 80;
-- ex2) 100번 사원의 사번, 이름, 직책, 부서번호를 emp_blank 테이블에 입력
insert into emp_blank (employee_id, first_name, last_name, email, hire_date, job_id, department_id)
select employee_id, first_name, last_name, email, hire_date, job_id, department_id
from employees
where employee_id = 100;
2) 데이터 갱신 : UPDATE
UPDATE table_name
SET col_name = expression [, ...]
[WHERE predicate]
- expression | where 절에 subquery 사용 가능함
- 변경하려는 데이터가 primary key라도 변경 가능. (단, update로 변경하는 것은 권장되지 않음)
but, 다른 테이블에서 해당 key를 참조하고 있는 경우에는 변경하면 참조 무결성 위배되므로 변경 불가!
- insert all 같은 update 조작어 없음. 속도를 위해서는 batch (메모리에 쌓아서 db연결 한 번안에 처리하는 방식)이용함.
▷ 예제
-- ex1) 'java2'의 비번을 9876으로 변경
update member
set pass = 9876
where id = 'java2';
-- ex2) 'java2'의 비번을 1234로, 나이를 25로 변경
update member
set pass = 1234, age = 25
where id = 'java2';
-- ex3) 'java2'의 비번을 5678, 나이를 아이디가 oracle인 사람과 같게 변경
update member
set pass = 5678, age = (select age from member where id = 'oracle') -- update의 subquery
where id = 'java2';
-- ex4) 주소에 '구로'가 들어가는 회원들의 비밀번호를 87654321 로 변경
update member
set pass = 87654321
where id in (select id from member_detail where address = '%구로%'); -- update의 where절의 subquery
3) 데이터 삭제 : DELETE
DELETE [FROM] table_name
[WHERE predicate]
- where 절 입력하지 않으면 테이블 내의 데이터 전부가 삭제됨
- 참조하는 테이블 먼저 지우고, 참조 테이블 지워야 함
- insert all 같은 삭제 조작어 없기에, update 와 마찬가지로 batch 사용
▷ 예제
delete member_detail
where id = 'java2';
delete member
where id = 'java2';
4) MERGE
MERGE INTO table_name
USING table|view|subquery -- 하나의 테이블만 이용한다면 dual
ON (join condition) -- where 절에 조건 쓰듯이
WHEN MATCHED THEN -- on 절의 조건에 해당하는 데이터가 있는 경우
UPDATE SET col_name = val [, ...] -- update 실행
WHEN NOT MATCHED THEN -- on 절의 조건에 해당하는 데이터가 없는 경우
INSERT (column_lists) VALUES (values) ; -- insert 실행
▷ 예제
--ex1) 상품코드가 400인 자갈치(1200원) 150개 입고 >> 기존에 없는 상품코드이므로 insert
merge into product
using dual
on (pid = 400)
when matched then
update set cnt = cnt + 150
when not matched then
insert (pid, pname, cnt, price)
values (400, '자갈치', 150, 1200);
--ex2) 상품코드가 100인 새우깡(1500원) 50개 입고 >> 기존에 있는 상품코드이므로 update
merge into product
using dual
on (pid = 100)
when matched then
update set cnt = cnt + 50
when not matched then
insert (pid, pname, cnt, price)
values (100, '새우깡', 50, 1500);
5) TRANSACTION : 데이터 처리의 한 단위 (작업단위)
COMMIT; | ROLLBACK; | SAVEPOINT;
- 오라클에서 발생하는 여러개의 SQL 명령문들을 하나의 논리적인 작업단위로 묶은 것
- 트랜잭션은 All-OR-Noting 방식으로 처리되는데 하나의 트랜잭션 안의 여러개의 명령어가 모두 정상적으로 처리 되었다면 정상적으로 종료하지만, 그중 하나라도 명령어가 잘못 입력되면 모두 다 취소하게 됨
- COMMIT을 한번 적용하고 난뒤에는 그 이전으로 다시 되돌릴 수 없음
- CREATE, ALTER, DROP, RENAME, TRUNCATE등의 DDL문을 사용할때에는 자동으로 COMMIT이 적용 됨
→ DDL문은 ROLLBACK 불가 (DDL은 트랜젝션 처리가 안됨)
- insert / update / delete 작업 수행시 commit / rollback 둘 중 하나를 하지 않으면, rock 잡힐 수 있음.
예를 들어 어떤 게임한판을 하나의 트랜잭션이라고 생각해보자.
새로운 게임을 시작하는겠다고 하는것이 COMMIT이다.
그리고 중간 중간 게임을 저장(SAVE)를 하는것이 SAVEPOINT이며
게임을 다시 시작하거나 저장한 부분을 로드하는것이 ROLLBACK이 되는것이다.
출처: https://mirwebma.tistory.com/30 [Run and Fly]
6) SEQUENCE : 유일값을 생성해주는 객체
CREATE SEQUENCE sequence_name
[ START WITH n ] -- 시작 값
[ INCREMENT BY n ] -- 증가 값
[ MAXVALUE n | NOMAXVALUE ] -- 시퀀스 최대값
[ MINVALUE n | NOMINVALUE ] -- 시퀀스 최소값
[ CYCLE | NOCYCLE ] -- 최대값 도달시 순환 여부
[ CACHE | NOCACHE ] -- CACHE 여부
- 테이블과는 독립적으로 저장되고 생성됨
- 일련번호, 게시판 번호 등을 만들 때 주로 사용하며, insert 구문에서 사용함
- 트랜잭션 처리 불가 (ROLLBACK 불가)
- sequence는 증가는 되지만 감소는 안됨
- 하나의 쿼리 안에서 sequence의 증가는 한번만 일어남
▷ 예제
create sequence product_pid_seq
start with 1 increment by 1; -- 시퀀스 생성
select product_pid_seq.nextval from dual; -- 실행하는 순간 다음 번호가 매겨짐
select product_pid_seq.currval from dual; -- 현재 시퀀스 번호 조회
insert into product (pid, pname, cnt, price)
values (product_pid_seq.nextval, '이름', 10, 1000); -- 생성한 시퀀스를 이용해 데이터 등록
- end.
