Notice
Recent Posts
Recent Comments
Link
나의 개발일지
[SQL] 데이터 조작어 (DML) 본문
데이터 조작어 (DML)
- 데이터베이스에 데이터를 추가, 갱신 또는 삭제할 때 실행하는 SQL 명령어
- 기본적인 DML 명령문의 종류
| 명령어 | 설명 |
| INSERT | 테이블에 새 행 추가 |
| UPDATE | 테이블의 기존 행 수정 |
| DELETE | 테이블에서 기존 행 삭제 |
| MERGE | 테이블에 INSERT와 UPDATE를 동시에 처리 |
트랜잭션
- 논리 작업 단위를 형성하는 DML 문의 모음
- 데이터베이스는 트랜잭션 단위로 데이터베이스에 변경 내용을 적용
- 트랜잭션에 포함된 명령문 중 하나라도 실행되지 못하면 나머지 명령문과 함께 모두 취소
INSERT 명령
- INSERT INTO ... VALUES 명령
- 한 번에 하나의 행을 테이블에 입력
- 서브쿼리 사용
- 한 번에 여러 행을 테이블에 입력
- 한 번에 여러 행을 여러 테이블에 입력하는 다중 테이블 INSERT
단일행 INSERT
- INSERT INTO ... VALUES ~ 명령문을 사용하면 한 번에 한 행씩 테이블에 추가
- INSERT INTO 절에는 대상 테이블의 이름 및 선택적으로 데이터를 입력할 열 이름을 지정
- VALUES 절에는 입력할 행의 값을 나열
- 구문
INSERT INTO table [(column, column...)]
VALUES (value [, value...]);
단일행 INSERT 문 작성 가이드
- 열 목록을 지정하는 경우
- VALUES 절에는 INSERT INTO 절에 지정한 열의 순서대로 데이터 값을 지정
- 명령문에 포함되지 않은 열에 대해서는 NULL 값이 적용
- INSERT INTO 절에서 열 목록을 생략하는 경우
- VALUES 절의 값을 테이블의 기본 열 순서에 따라 나열
- 건너뛰는 열 없이 모든 열에 대해 각각 값을 제공
- 문자 및 날짜 값의 경우
- 작은 따옴표로 처리해야 하며 대소문자가 구분되어 저장됨
- 특히 날짜데이터의 경우 형식을 맞추어 입력하거나 TO_DATE 함수 이용
- y_dept 테이블에 새 생을 추가하는 INSERT 명령
INSERT INTO y_dept(dept_id, dept_name, mgr_id, loc_id)
VALUES (600, '생산관리', 1038, 6);
-- 테이블의 열 순서와 동일한 순서로 값을 지정했으므로 열 목록 생략 가능
INSERT INTO y_dept
VALUES (600, '생산관리', 1038, 6);
INSERT 시 NULL 값이 생성되는 경우
- 암시적 방법
- INSERT INTO 절의 열 목록에서 해당 열을 생략하는 경우
- 명시적 방법
- VALUES 절에서 해당 열에 NULL을 명시적으로 지정하는 경우
- y_dept 테이블의 mgr_id와 loc_id 열에 NULL 값을 생성하고 확인
INSERT INTO y_dept(dept_id, dept_name)
VALUES(700, '회계부');
INSERT INTO y_dept
VALUES(800, '시스템관리', NULL, NULL);
SELECT * FROM y_dept;
INSERT 중 발생하는 일반적 오류
- NOT NULL이 정의된 열에 값을 누락한 경우
- 중복 값으로 고유성 제약 조건을 위반한 경우
- 외래 키 제약 조건에 위반한 경우
- CHECK 제약 조건에 위반한 경우
- 데이터 유형이 일치하지 않는 경우
- 값의 폭이 너무 넓어 열에 맞지 않는 경우
INSERT 시 날짜 데이터의 사용
- 두 자릿수의 연도 사용 시 RR과 YY형식
- YY/MM/DD 형식
- 세기 기본값으로 항상 현재 세기를 입력
- RR/MM/DD 형식
- 현재 세기가 0~49년인 경우 50~99년을 이전 세기로 처리
- 날짜에는 시간 정보가 포함
- 입력 시 시간을 생략하면 기본 시간인 자정(00:00:00)으로 입력
- YY/MM/DD 형식
- 날짜를 기본 형식이 아닌 다른 형식으로 입력하려면 TO_DATE 함수를 사용
- y_emp 테이블에 새 사원에 대한 정보를 입력, 이때 hiredate 열에 현재 날짜 및 시간을 제공하기 위해 SYSDATE 함수를 사용
INSERT INTO y_emp(emp_id, emp_name, hiredate, dept_id)
VALUES(2007, '박민영', SYSDATE, 700);
SELECT emp_id, emp_name, hiredate, dept_id
FROM y_emp
WHERE emp_id = 2007;
- 새 사원의 정보를 y_emp 테이블에 기록하면서 hiredate 열을 99년 2월 3일로 설정하는데 형식에 따라 hiredate의 연도는 1999 또는 2099로 저장될 수 있다.
INSERT INTO y_emp(emp_id, emp_name, hiredate)
VALUES (2008, '윤현민', TO_DATE('99/02/03', 'YY/MM/DD'));
INSERT INTO y_emp(emp_id, emp_name, hiredate)
VALUES (2009, 'Jason Lee', TO_DATE('99/02/03', 'RR/MM/DD'));
SELECT emp_id, emp_name, TO_CHAR(hiredate, 'yyyy/mm/dd')
FROM y_emp
WHERE emp_id IN (2008, 2009);
다중행의 INSERT
- INSERT 문에 VALUES 절 대신 서브 쿼리를 사용
- 기존 다른 테이블에서 선택한 데이터를 대상 테이블의 행으로 추가
- 다른 테이블로부터 선택한 여러 행을 대상 테이블로 한꺼번에 복사하여 입력
- INSERT 절의 열 목록에서 열의 수 및 데이터 유형은 서브 쿼리의 열의 수 및 데이터 유형과 일치해야 함
- 구문
INSERT INTO table [(column, column...)]
Subquery;
- y_emp 테이블에서 300번 부서 사원 정보를 선택하여 emp300 테이블에 입력하고 확인
INSERT INTO emp300
SELECT emp_id, emp_name, salary, dept_id
FROM y_emp
WHERE dept_id = 300;
SELECT * FROM emp300;
COMMIT;
다중 테이블 INSERT
- 다중 행 INSERT 구문(INSERT INTO ... SELECT 구문)의 확장 기능
- 서브 쿼리에서 반환한 행에 대해 계산을 수행한 다음 이 행들을 하나 이상의 테이블에 INSERT
- 여러 테이블에 INSERT 작업이 단일 DML로 처리
- INSERT INTO ... SELECT ~ 문을 변형하여 INTO 절이 여러 번 반복할 수 있는 구조
- INSERT INTO ... SELECT를 여러 번 수행하거나 IF ... THEN 구문을 사용하여 처리하는 작업 대신 사용
- 대용량 데이터 처리에 있어서 많은 성능상의 이점 제공
- 유형
- 무조건 INSERT
- 조건 ALL INSERT
- 조건 FIRST INSERT
- 피벗 INSERT
- 구문
INSERT [ALL] | [ALL | FIRST]
[WHEN ... THEN ...[ELSE...]]
INTO table
[WHEN ... THEN ...[ELSE...]]
INTO table
무조건 INSERT
- ALL 뒤에 여러 개의 INTO 절을 지정하여 다중 테이블 INSERT를 수행
- 오라클 서버는 서브 쿼리에서 반환된 행들을 INTO 절에 지정된 여러 개의 테이블에 각각 입력
- SELECT 문에서 검색된 행이 아무런 제한 없이 INTO 절에 지정된 모든 테이블에 각각 입력
- sal_list 및 mgr_list 테이블에 동시에 INSERT 하는 무조건 ALL INSERT 명령
INSERT ALL
INTO sal_list VALUES(emp_id, salary, position)
INTO mgr_list VALUES(emp_id, mgr_id, hiredate)
SELECT emp_id, hiredate, salary, position , mgr_id
FROM y_emp
WHERE emp_id > 2000 ;
SELECT * FROM sal_list;
SELECT * FROM mgr_list;
ROLLBACK;
조건 INSERT 구문
- 조건절을 지정하여 조건 다중 테이블 INSERT를 수행
- 오라클 서버는 INTO 절의 실행 여부를 INTO절 앞의 WHEN 조건을 통해 평가
- 조건 INSERT 유형
- 조건 ALL
- 조건 FIRST
조건 INSERT 구문의 ELSE 절
- ELSE 절은 선택적으로 사용 가능
- ELSE 절을 지정한 경우
- 주어진 행에 대해 WHEN 절이 모두 거짓이면 오라클 서버는 ELSE 절에 지정한 INTO 절 목록을 실행
- ELSE 절을 지정하지 않은 경우
- WHEN 절이 모두 거짓이면 오라클 서버는 해당 행에 대해 아무 작업도 수행하지 않아 INSERT 대상 행에서 제외됨
- ELSE 절을 지정한 경우
조건 INSERT 유형
- 조건 ALL INSERT
- 서브 쿼리에서 반환된 행들을 WHEN 절의 조건을 만족하는 모든 테이블에 대해 INSERT
- 조건 FIRST INSERT
- 오라클 서버는 서브 쿼리 결과 행이 첫 번째 WHEN 절을 만족하는 경우에 해당 INTO 절을 실행
- 첫 번째 WHEN 절에 만족한 행에 대해 이후의 WHEN 절은 더 이상 평가하지 않음
- 첫 번째 WHEN 절을 만족하지 않는 행에 대해서는 조건 INSERT ALL과 동일하게 처리
- 조건 ALL INSERT 명령 예제
INSERT ALL
WHEN salary > 300 THEN
INTO sal_list VALUES(emp_id, salary, position)
WHEN position <> '대리' THEN
INTO mgr_list VALUES(emp_id, mgr_id, hiredate)
SELECT emp_id, hiredate, salary, position, mgr_id
FROM y_emp
WHERE emp_id > 1080;
SELECT * FROM sal_list;
SELECT * FROM mgr_list;
- 조건 FIRST INSERT 구문을 사용하여 여러 테이블에 행을 INSERT 한다. 또한 ELSE 절을 지정하여 WHEN 절을 모두 만족하지 않는 행을 처리한 후 결과를 확인
INSERT FIRST
WHEN SAL > 5000 THEN
INTO high_income VALUES (deptid, sal)
WHEN HIREDATE like ('17%') THEN
INTO hired_list17 VALUES(deptid,hiredate)
WHEN HIREDATE like ('18%') THEN
INTO hired_list18 VALUES(Deptid, Hiredate)
ELSE
INTO hired_list VALUES(deptid, hiredate)
SELECT dept_id DEPTID, SUM(salary) SAL, MAX(hiredate) HIREDATE
FROM y_emp
GROUP BY dept_id ;
SELECT * FROM high_income;
SELECT * FROM hired_list17;
SELECT * FROM hired_list18;
SELECT * FROM hired_list;
피벗(PIVOT) INSERT
- 비관계형 데이터베이스 테이블 또는 정규화가 잘못된 테이블에 필요한 작업
- 각 레코드를 관계형 데이터베이스 테이블 환경에 적합한 여러 레코드로 변환
- 동일한 테이블에 대해 INTO 절은 반복되면서 VALUES 절의 열 이름만 다르게 사용하는 방식
- 정규화가 잘못된 SALES_DATA 테이블의 구조와 데이터를 확인
DESC sales_data;
SELECT * FROM sales_data;
- 이 문제를 해결하기 위해 SALES_DATA 테이블은 다음과 같이 SALES_REPORT 테이블로 변형
DESC sales_report;
SELECT * FROM sales_report;
- 피벗 INSERT 문을 사용하여 SALES_DATA 테이블의 각 레코드를 SALES_REPORT 테이블로 변환하여 INSERT 한 후 확인한다.
INSERT ALL
INTO sales_report VALUES (emp_id, week_id, mon_sales)
INTO sales_report VALUES (emp_id, week_id, tue_sales)
INTO sales_report VALUES (emp_id, week_id, wed_sales)
INTO sales_report VALUES (emp_id, week_id, thur_sales)
INTO sales_report VALUES (emp_id, week_id, fri_sales)
SELECT emp_id, week_id, mon_sales, tue_sales, wed_sales, thur_sales, fri_sales
FROM sales_data;
SELECT * FROM sales_report;
UPDATE 문
- 기존행을 수정
- WHERE 절을 지정하여 UPDATE 문이 특정 행을 수정
- 필요한 경우 한 번에 여러 행 갱신
- SET 절에 COLUMN = VALUE를 여러 번 지정하여 한꺼번에 여러 열을 갱신
- 구문
UPDATE table
SET column = value [, column = value, ...]
[WHERE 조건];
- emp300 테이블의 1037번 사원의 근무 부서 번호를 400번으로 변경하는 UPDATE 작업
UPDATE emp300
SET dept_id = 400
WHERE emp_id = 1037;
SELECT * FROM emp300;
UPDATE 문의 WHERE 절
- WHERE 절을 지정하여 한 번에 여러 행 변경
- WHERE 절을 생략하면 테이블의 모든 행 갱신
- WHERE 절의 표현식이 잘못되어 조건에 일치하는 행이 없으면 오류가 나지 않고 "0행이 갱신되었습니다"라는 메시지 출력
- WHERE 절을 생략하여 테이블의 모든 행이 수정된 경우
UPDATE emp300
SET dept_id = 80;
SELECT * FROM emp300;
- WHERE 절의 조건이 잘못되어 수정된 행이 없는 경우
UPDATE emp300
SET salary = 700
WHERE emp_id = 1056;
UPDATE 절에 서브쿼리 사용
- SET 절에서 연산자의 오른쪽에 변경할 값 대신 값을 반환할 서브 쿼리를 사용
- UPDATE 대상 테이블과 서브 쿼리에서 사용하는 테이블이 서로 다른 경우도 허용
- 서브 쿼리를 사용하여 UPDATE를 실행
- emp_id가 1087인 행의 salary는 emp_id가 1038인 사원의 salary로 설정 (570)
- dept_id도 emp_id가 1038인 사원의 dept_id로 설정 (400)
UPDATE emp300
SET salary = (SELECT salary FROM y_emp
WHERE emp_id = 1038),
dept_id = (SELECT dept_id FROM y_emp
WHERE emp_id = 1038)
WHERE emp_id= 1087;
SELECT * FROM emp300;
COMMIT;
DELETE 문
- 테이블에서 기존 행을 삭제
- WHERE 절을 추가
- 특정 행을 삭제하거나 한꺼번에 여러 행 삭제
- WHERE 절을 생략하면 모든 행 삭제
- WHERE 절에 해당하는 행이 없으면 삭제되는 행이 없음
- 구문
DELETE [FROM] table
[WHERE 조건];
- emp300 테이블에서 1019번 사원을 삭제
DELETE FROM emp300
WHERE emp_id = 1019;
SELECT * FROM emp300;
- WHERE 절을 생략한 DELETE 명령문을 수행한 후의 출력 메시지를 확인하고 커밋
- 테이블의 정의는 남아있고 데이터는 한 행도 남아있지 않은 상태가 된다.
DELETE FROM emp300;
SELECT * FROM emp300;
COMMIT;
- DELETE 문에 서브 쿼리를 사용하면 다른 테이블의 값을 기반으로 행을 삭제할 수 있다.
- y_emp 테이블에서 부서 이름에 문자열 '회계'가 포함된 부서(700번)에 속하는 사원들을 삭제
DELETE FROM y_emp
WHERE dept_id = (SELECT dept_id
FROM y_dept
WHERE dept_name LIKE '%회계%');
SELECT dept_id
FROM y_emp
WHERE dept_id = 700;
MERGE 명령
- INSERT 및 UPDATE 명령의 결합
- DML을 여러 번 하지 않고 행을 조건에 따라 UPDATE하거나 INSERT, DELETE
- 데이터 웨어하우징 응용 프로그램에서 유용하게 사용
- 여러 원본의 데이터를 사용하여 작업해야 할 경우 중복 데이터로 인한 오류가 빈번히 발생하는 문제 해결
- PL / SQL 루프문과 함께 여러 DML 문을 사용하는 방법 대체
- 사용하기도 쉽고 하나의 SQL 문장으로 처리하기 때문에 훨씬 간단하므로 더 권장
- 구문
- ON절의 조건에 의해 INSERT 또는 UPDATE 결정
MERGE INTO table_name table_별칭
USING (table|view|sub_query) 별칭
ON (join_조건)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
[DELETE WHERE (조건)]
WHEN NOT MATCHED THEN
INSERT (column_name, ...)
VALUES (column_value, ...);
- old_emp 테이블의 구조와 데이터를 먼저 확인
DESC old_emp;
SELECT * FROM old_emp;
- old_emp 테이블에 y_emp 테이블을 MERGE 한다. 동일한 사원 번호의 사원이 old_emp에 존재하면 y_emp 테이블의 정보로 UPDATE하고 존재하지 않으면 y_emp 테이블의 정보를 이용해 INSERT 한다. 그리고 부서 정보가 NULL인 사원은 MERGE과정에서 삭제를 한다.
MERGE INTO old_emp o
USING y_emp e
ON (o.emp_id = e.emp_id)
WHEN MATCHED THEN
UPDATE SET
o.emp_name = e.emp_name,
o.position = e.position,
o.salary = e.salary,
o.dept_id = e.dept_id
DELETE WHERE (e.position IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES(e.emp_id, e.emp_name,e.position, e.salary, e.dept_id);
SELECT * FROM old_emp;
'Oracle SQL' 카테고리의 다른 글
| [SQL] 테이블의 생성과 관리 (0) | 2023.05.31 |
|---|---|
| [SQL] 데이터베이스 트랜잭션 (0) | 2023.05.30 |
| [SQL] 서브 쿼리 (0) | 2023.04.24 |
| [SQL] JOIN (0) | 2023.04.18 |
| [SQL] GROUP BY 절의 추가 기능 (ROLLUP, CUBE, GROUPING SETS) (0) | 2023.04.16 |
'Oracle SQL' Related Articles
more
Comments