8. ORACLE :: DML구문(insert, upadate, delete) 정리

DML(Data Manipulation Language)구문

row(데이터)를 삽입, 수정, 삭제하는 명령어

1. INSERT

테이블에 새로운 행을 추가하여 행 개수를 증가시키는 구문

: INSERT INTO 테이블명 [(컬럼명, …)] VALUES(값, 값, 값….)

• VALUES 값은 테이블 컬럼수와 타입이 같아야한다. (다르면 에러 발생)
• 모든 컬럼에 값을 넣으려면 컬럼명을 생략해도 된다.
  • 단, 순서를 지켜서 값을 기입해주면 된다.
• 일부 컬럼을 선택하지 않고 값을 넣을 경우 해당 칼럼은 null로 채워진다.
  • not null 조건이 있다면 default 값으로 채워지며, not null인데도 default가 없다면 에러가 난다.
• VALUES 대신 서브쿼리를 이용할 수도 있다.

-- 컬럼 3개인 테이블 복사 생성
CREATE TABLE TEMP_DEPT
AS SELECT * FROM DEPARTMENT 
WHERE 1 = 0;
SELECT * FROM TEMP_DEPT;
-- 전체 칼럼에 값 대입하기
INSERT INTO TEMP_DEPT VALUES('D1', '자바 학부', 'L3');
INSERT INTO TEMP_DEPT VALUES('D1', '자바 학부'); -- 에러
-- 컬럼을 선택해서 값 대입하기
INSERT INTO TEMP_DEPT(DEPT_ID, LOCATION_ID) VALUES ('D2', 'L2');
SELECT * FROM TEMP_DEPT; -- 지정하지 않은 컬럼의 값은 null로 채워진다. (NOT NULL 조건 없을 경우만 가능)

-- 문자열 형식 컬럼 3개인 테이블에 값 대입을 시도할 경우
insert into temp_emp values('test', '101원', '1234'); --수치 부적합 오류
insert into temp_emp values('test', '101', '1234'); -- 리터럴과 형식문자열 불일치 오류
insert into temp_emp values('test', '101', TO_DATE('1231', 'MM/DD')); -- 현재 년도로 알아서 넣어줌(정상작동)

select문으로 insert하기

다른 테이블값을 복사해서 대입하기 -> 서브 쿼리문 이용( 컬럼 타입과 갯수에 맞는 result set 생성)

-- 부서 테이블에서 3개 컬럼의 형식만 저장
create table insert_sub
as select emp_id, emp_name, dept_title
from employee join department on dept_code = dept_id
where 1 = 2;

-- 월급 300만원 이상인 사원의 아이디, 이름, 부서명만 가져와서 저장하기
insert into insert_sub(
    select emp_id, emp_name, dept_title
    from employee join department on dept_id = dept_code
    where salary >= 3000000
);

insert all

서브 쿼리가 사용하는 테이블이 같은 경우 한개 select문에서 데이터를 다수 테이블로 저장하는 명령어

동일한 데이터를 여러 테이블에 insert

@작성법
INSERT ALL
	INTO 테이블명 VALUES(컬럼1) -- 진행순서 2 : 컬럼1 값만 저장한다
	INTO 테이블명 VALUES(컬럼1, 컬럼2) -- 진행3 : 컬럼 1, 컬럼2 값만 저장한다.
	INTO 테이블명 VALUES(컬럼1, 컬럼3) -- 진행 4 : 컬럼1, 컬럼3 값만 저장한다.
SELECT 컬럼1, 컬럼2, 컬럼3 -- 진행순서 1: from 테이블에서 3개 컬럼을 가져온다.
FROM 테이블명;

• 활용 예

--임의 테이블 2개 생성
CREATE TABLE EMP_HIRE_DATE
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE FROM EMPLOYEE WHERE 1=0;
CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE FROM EMPLOYEE WHERE 1=0;
SELECT * FROM EMP_HIRE_DATE;
SELECT * FROM EMP_MANAGER;
INSERT ALL
    INTO EMP_HIRE_DATE VALUES(EMP_ID, EMP_NAME, HIRE_DATE)
    INTO EMP_MANGER VLAUES(EMP_ID, EMP_NAME, MANAGER_ID)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, MANAGER_ID FROM EMPLOYEE;

 

조건에 따라 여러 테이블에 각기 다른 데이터를 insert 

@작성법
INSERT ALL
	WHEN 조건식 THEN INTO 테이블명 VALUES(컬럼1)
	WHEN 조건식 THEN INTO 테이블명 VALUES(컬럼1)
	WHEN 조건식 THEN INTO 테이블명 VALUES(컬럼1)
SELECT 컬럼1, 컬럼2, 컬럼3 -- 진행순서 1: from 테이블에서 3개 컬럼을 가져온다.
FROM 테이블명;

• 활용 예

CREATE TABLE OLD_EMP
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE FROM EMPLOYEE WHERE 1=9;
CREATE TABLE NEW_EMP
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE FROM EMPLOYEE WHERE 2=9;

INSERT ALL
    WHEN HIRE_DATE < '00/01/01' THEN INTO OLD_EMP VALUES(EMP_ID, EMP_NAME, HIRE_DATE)
    WHEN HIRE_DATE >= '00/01/01' THEN INTO NEW_EMP VALUES(EMP_ID, EMP_NAME, HIRE_DATE)
SELECT * FROM EMPLOYEE;

SELECT * FROM OLD_EMP;
SELECT * FROM NEW_EMP;

 

반응형

2. UPDATE

테이블 특정 row 컬럼값을 수정하는 명령어

: UPDATE 테이블명 SET 컬럼명 = 값[, 컬럼명 = 값…..] [where 특정 row 지정하는 구문]

• 연산식 사용 가능하다
• WHERE를 써주지 않으면 전체 테이블이 바뀔 수 있으므로 주의

@ 1개 데이터 수정
--전형돈의 급여를 300만원으로 수정하기(where를 꼭 써줘야한다)
UPDATE EMP_SALARY SET SALARY = 3000000 WHERE EMP_NAME = '전형돈';

@ 다수 데이터 수정 : , 로 추가 가능, 함수 사용가능
UPDATE EMP_SALARY SET SALARY = 200, BONUS = 0.5 WHERE EMP_NAME = '전형돈';
UPDATE EMP_SALARY SET DEPT_CODE = 'D' || SUBSTR(EMP_ID, 3, 1)

• 이전 데이터로 값 수정하기

-- 전체 사원의 급여를 100원씩 증가
UPDATE EMP_SALARY SET SALARY = SALARY + 100;

UPDATE + SELECT문

-- 방명수의 급여와 보너스를 심봉선과 동일하게 수정하기
UPDATE EMP_SALARY SET SALARY = (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME = '심봉선'),
                BONUS   = (SELECT BONUS FROM EMPLOYEE WHERE EMP_NAME = '심봉선')
WHERE EMP_NAME = '방명수';
-- 같은 결과 다른 방법
UPDATE EMP_SALARY 
SET(SALARY, BONUS) = (SELECT SALARY, BONUS FROM EMPLOYEE WHERE EMP_NAME = '심봉선') -- 순서 맞춰 써주기
WHERE EMP_NAME = '방명수'

MERGE :: 동일 구조 테이블 합치기

구조가 같은 두개의 테이블을 하나로 합치는 기능

두 테이블에서 지정하는 조건 값이 존재하면 update되고 없으면 insert함

@ 작성방법
MERGE INTO 기준테이블 USING 기준테이블에 합칠 다른 테이블 ON 동등비교_조건(pk값)
WHEN MATCHED[혹은 NOT MATCHED] THEN 실행할_DML_구문(update, insert); -- 여러개 가능

 

3. DELETE

특정 row를 삭제하는 명령어

: DELETE FROM 테이블명 [WHERE 특정 row 지정하는 구문]

DELETE FROM EMP_M1; -- where 조건문이 없으면 전체줄이 삭제됨
DELETE FROM EMP_M1 WHERE JOB_CODE = 'J4';
-- 스크립트 :  6개 행 이(가) 삭제되었습니다.

두 개 테이블을 동시에 처리하려면?

(1) ON DELETE SET NULL

참조값이 삭제되면 데이터가 null로 변경되도록 설정

단, 컬럼에 not null 제약 있으면 불가능

참조값이 사라져도 활용가치가 남아있는 테이블

예) 탈퇴 회원의 구매내역(상품 등)

alter table fk_board_comment
add constraint board_no number references **fk_board(board_no) on delete set null,**

(2) ON DELETE CASCADE

참조값이 삭제되면 같이 삭제되도록하는 조건 → 테이블 생성시 적어두면 된다.

참조값이 사라지면 활용가치가 없어지는 테이블의 경우 사용

예) 퇴자사의 가족정보 테이블, 삭제된 게시글의 댓글 테이블

@ 선언은 접근제한자처럼 컬럼 행에 넣어주면 된다.
create table fk_board_comment(
    comment_no number primary key,
    comment_content varchar2(1000) not null,
    comment_writer varchar2(20) references uq_member(member_id),
--    board_no number references fk_board(board_no) on delete set null, 혹은
    board_no number references **fk_board(board_no) on delete cascade,**
    comment_date date
);

 

4. TRUNCATE

전체 row를 삭제할 때 사용함

단, rollback이 안되니 주의 (완료되었다고 떠도 데이터는 돌아오지 않음)

TRUNCATE TABLE EMP_M1;
-- 스크립트 : Table EMP_M1이(가) 잘렸습니다.