오라클 데이터 조작어 DML

10. 데이터 조작어

테이블에 새로운데이터를 입력하거나 기존 데이터를 수정 또는 삭제하기 위한 데이터 조작어(DML)

데이터 입력(INSERT)

사용법

INSERT INTO table [ (column [, column…])]

VALUES (value [, value…]);

NULL의 묵시적 입력

부서 테이블의 부서번호와 부서 이름을 입력하고 나머지 칼럼은 NULL입력

INSERT INTO department (deptno, dname) VALUES (300,'생명공학부');

입력데이터 확인

SELECT * FROM department WHERE deptno=300;

NULL의 명시적 입력

INSERT INTO department VALUES (301, '환경보건학과',NULL,NULL);

또는

INSERT INTO department VALUES (301, '환경보건학과','','');

날짜 데이터 입력 방법

교수 테이블에서 입사일을 2005년 1월 1일로 입력해라

퍼스널 오라클 기본 날짜형식(YY/MM/DD)형식으로 입력

INSERT INTO professor(profno, name, position, hiredate, deptno) VALUES (9920,'최윤식','조교수','05/01/01',102);

YYYY/MM/DD형식 사용

INSERT INTO professor(profno, name, position, hiredate, deptno) VALUES (9920,'최윤식','조교수',TO_DATE(‘2005/01/01’,’YYYY/MM/DD’),102);

SYSDATE 함수를 이용한 현재 날짜 입력

:SYSDATE함수는 시스템에 저장된 현재 날짜 데이터를 반환

교수 테이블에서 새로운 행을 입력할 때 입사일을 현재 날짜로 입력하여라

INSERT INTO professor VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 101);

다중행 입력

INSERT 명령문에서 서브쿼리 절을 이용하여 자신이나 다른 테이블의 데이터를 복사하여 여러 행을 동시에 입력 할 수 있는 방법을 제공.

단일 테이블에 다중행 입력

사용법 : INSERT INTO table [ (column1, column2, ….)] subquery;

INSERT INTO professor SELECT * FROM professor WHERE profno >9905;

다중행 테이블에 다중행 입력방법1

INSERT ALL 명령문은 서브쿼리의 결과 집합을 조건없이 여러 테이블에 동시에 입력하기 위한 명령문이다. 이때 서브쿼리의 칼럼이름과 데이터가 입력되는 테이블의 칼럼 이름은 동일해야 한다.

사용법:

INSERT ALL|FIRST

INTO [table1] values [ (column1, column2,...)]

INTO [table2] values [ (column1, column2,...)]

INTO [table3] values [ (column1, column2,...)]

subquery;

ALL:서브쿼리의 결과 집합을 해당하는 INSERT절에 모두 입력

FIRST : 서브쿼리의 결과 집합을 해당하는 첫 번째 INSERT절에 입력

subquery : 입력 데이터 집합을 정의하기 위한 서브쿼리, 서브쿼리는 한번에 하나의 행을 반환하여 각 INSERT절 수행

INSERT ALL 실습테이블 생성.

CREATE TABLE height_info(

studno NUMBER(5),

name VARCHAR2(10),

height NUMBER(5,2));

CREATE TABLE weight_info(

studno NUMBER(5),

name VARCHAR2(10),

weight NUMBER(5,2));

학생 테이블에서 2학년 이상의 학생을 검색하여 height_info테이블에는 학번, 이름, 키,

weight_info 테이블에는 학번,이름,몸무게를 가각 입력하여라

INSERT ALL

INTO height_info VALUES(studno, name, height)

INTO weight_info VALUES(studno, name, weight)

SELECT studno, name, height, weight

FROM student

WHERE grade >= ‘2’;

(서브쿼리에서 2학년 이상 학생을 검색하여 키정보는 height_info테이블에, 몸무게 정보는 weight_info 테이블에 각가 입력)

다중행 테이블에 다중행 입력방법2

Conditional INSERT ALL 명령문은 서브쿼리의 결과 집합에 대해 WHEN 조건 절에서 지정한 조건을 만족하는 행을 해당되는 테이블에 각각 입력하기 위한 명령문이다.

사용법

INSERT ALL

[ WHEN 조건절1 THEN INTO [table1] VALUES [ (column1, column2, ….)]

[ WHEN 조건절2 THEN INTO [table2] VALUES [ (column1, column2, ….)]

[ ELSE INTO [table3] VALUES [ (column1, column2, ….)]

subquery;

ALL:WHEN~THEN~ELSE 조건을 만족하는 서브쿼리의 모든 검색 결과를 입력하기 위한 옵션

WHEN 조건절 THEN : 서브쿼리의 결과 집합에 대한 비교 조건 정의

서브쿼리의 결과 집합중에서 조건절1을 만족하는 결과 행은 table1에 입력, 2면 table2 만족하지 않는 행은 table3에 입력

Conditional INSERT ALL 실습 테이블 생성

DELETE FROM height_info;

DELETE FROM weight_info;

학생 테이블에서 2학년 이상의 학생을 검색하여 height_info테이블에는 키가 170보다 큰 학생의 학번, 이름 ,키를 입력

weight_info 테이블에는 몸무게가 70보다ㅣ 큰 학생의 학번, 이름,몸무게를 가각 입력하여라

INSERT ALL

WHEN height > 170 THEN INTO height_info VALUES (studno,name,height)

WHEN weight > 70 THEN INTO weight_info VALUES(studno, name, weight)

SELECT studno, name, height, weight

FROM student

WHERE grade >= ‘2’;

다중행 테이블에 다중행 입력방법3

Conditional-First INSERT 명령문은 서브쿼리의 결과 집합에 대해 WHEN 조건절에서 지정한 조건을 만족하는 첫번째 테이블에 우선적으로 입력하기 위한 명령문이다. 즉 Conditional-First INSERT 명령문은 서브쿼리의 결과가 여러개의 WHEN절을 만족하더라도 조건을 만족하는 첫 번째 WHEN 절에서 지정한 테이블에만 입력되고 다음 WHEN절은 실행되지 않는다.

사용법

INSERT FIRST

[ WHEN 조건절1 THEN INTO [table1] VALUES [ (column1, column2, ….)]

[ WHEN 조건절2 THEN INTO [table2] VALUES [ (column1, column2, ….)]

[ ELSE INTO [table3] VALUES [ (column1, column2, ….)]

subquery;

Conditional-First INSERT 실습 테이블 생성

DELETE FROM height_info;

DELETE FROM weight_info;

학생 테이블에서 2학년 이상의 학생을 검색하여 height_info테이블에는 키가 170보다 큰 학생의 학번, 이름 ,키를 입력

weight_info 테이블에는 몸무게가 70보다 큰 학생의 학번, 이름,몸무게를 가각 입력하여라

단 키가 170보다 작고 몸무게가 70보다 큰 학생은 weight_info 테이블에만 입력한다

INSERT FIRST

WHEN height > 170 THEN INTO height_info VALUES (studno,name,height)

WHEN weight > 70 THEN INTO weight_info VALUES(studno, name, weight)

SELECT studno, name, height, weight

FROM student

WHERE grade >= ‘2’;

다중행 테이블에 다중행 입력방법4

PIVOTING INSERT 명령문

하나의 행을 여러개의 행으로 나누어서 입력하는 기능 제공

데이터 수정

UPDATE 명령문은 테이블에 저장된 데이터를 수정하기 위한 데이터 조작어이다.

사용법

UPDATE table

SET column=value [, column=value, ….]

[ WHERE condition ];

교수번호가 9903인 교수의 현재 직급을 부교수로 수정

UPDATE professor SET position = '부교수' WHERE profno =9903;

서브쿼리를 이용한 데이터 수정

UPDATE명령문의 SET절에서 서브쿼리를 이용하여 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러 칼럼의 내용을 수정할 수 있다. 이때, SET절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 상관 없다. 하지만 데이터 타입과 칼럼 수는 반드시 일치해야 한다.

서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를 10103학번의 학생의 학년과 학과 번호와 동일하게 수정하여라

UPDATE student

SET (grade, deptno) =(SELECT grade, deptno FROM student WHERE studno=10103)

WHERE studno=10201;

데이터 삭제

DELETE 명령문은 테이블에 저장된 데이터를 삭제하기 위한 데이터 조작어이다.

사용법

DELETE [FROM] table [WHERE condition];

WHERE절을 생략하면 테이블 모든행이 삭제됨.

학생테이블에서 학번이 20103인 학생의 데이터를 삭제

DELETE

FROM studentWHERE studno=20103;

서브쿼리를 이용한 데이터 삭제

DELETE 명령문의 WHERE절에서 서브쿼리를 이용하여 다른 테이블에 저장된 데이터를 검색하여 한꺼번에 여러 행의 내용을 삭제할 수 있다. 이때 WHERE절의 칼럼 이름은 서브쿼리의 칼럼 이름과 달라도 상관없다. 하지만 데이터 타입과 칼럼수는 일치해야 한다.

학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라.

DELETE FROM student WHERE deptno=(SELECT deptno FROM department WHERE dname='컴퓨터공학과');

MERGE

: 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어이다.

WHEN절의 조건절에서 결과 테이블에 해당 행이 이미 존재하면 UPDATE명령문에 의해 새로운 값으로 수정하고 존재하지 않으면 INSERT명령문에 의해 새로운 행을 입력한다.

대량의 데이터 분석을 위한 데이터 웨어하우스 업무를 위해 유용하게 사용할 수 있다(ex-평소 판매데이터를 월단위로 분리하여 별도의 테이블에서 관리하다가 연말에 판매실적 분석을 위해 하나의 테이블로 합칠때 MERGE 명령문을 사용하면 편리하다)

사용법

MERGE INTO [table] [alias]

USING [ table | view | subquery] alias

ON [join condition]

WHEN MATCHED THEN

UPDATE SET ….

WHEN NOT MATCHED THEN

INSERT INTO …

VALUES …;

MERGE INTO : 하나의 테이블로 합치기 위한 결과 테이블

USING : 테이블, 뷰, 서브쿼리에 대한 별명 지정

ON : 조인 조건 지정

WHEN MATCHED THEN : ON 절의 조인 조건을 만족하는 행이 존재하면 지정된 값으로 행을 UPDATE

WHEN NOT MATCHED THEN : ON 절의 조인 조건을 만족하지 않을 경우 새로운 행으로 INSERT

WHEN MATCHED THEN절과 WHEN NOT MATCHED THEN 절에서는 테이블이나 뷰 이름 대신에 USING절에서 지정한 별명 사용

professor 테이블과 professor_temp테이블을 비교하여 professor 테이블에 있는 기존 데이터는 professor_temp 테이블의 데이터에 의해 수정하고, professor 테이블에 없는 데이터는 신규로 입력하여라.

1. professor 테이블에서 직급이 교수인 데이터를 검색하여 professor _temp에 저장

CREATE TABLE professor_temp AS SELECT * FROM professor WHERE position='교수';

2. professor_temp 테이블의 ‘교수’ 직급을 명예교수로 수정

UPDATE professor_temp

SET position = '명예교수'

WHERE position = '교수';

2개행 갱신됨

3. professor_temp 테이블에 교수번호가 9999인 데이터를 입력

INSERT INTO professor_temp VALUES(9999, '김도경', 'arom21', '전임강사', 200, SYSDATE, 10, 101);

1개행 만들어짐

4. professor테이블과 professor_temp 테이블을 병합한다

> MERGE INTO professor p

USING professor_temp f

ON (p.profno = f.profno)

WHEN MATCHED THEN

UPDATE SET p.position = f.position

WHEN NOT MATCHED THEN

INSERT VALUES (f.profno, f.name, f.userid, f.position, f.sal, f.hiredate, f.comm, f.deptno);

3행이 병합되었습니다(professor테이블에 2건의 수정고 1건의 신규데이터가 입력되었다)

트랜잭션 관리

트랜잭션 : 관계형 DB에서 실행되는 여러개의 SQL 명령문을 하나의 논리적인 작업단위로 처리하는 개념을 말한다.(All - or - Nothing 방식으로 처리 : 하나의 트랜잭션이 정상종료(commit)되거나 하나라도 잘못처리되면 트랜잭션 전체가 취소(rollback)됨)

시퀀스

시퀀스 값은 오라클 내부 루틴에 의해 자동으로 번호가 생성된다.

사용법

CREATE SEQUENCE sequence

[INCREMENT BY n]

[ START WITH n]

[ MAXVALUE n | NOMAXVALUE]

[ MINVALUE n | NOMINVALUE]

[ CYCLE | NOCYCLE]

[ CACHE n | NOCACHE];

INCREMENT BY n : 시퀀스 번호의 증가치로 기본값은 1, 일반적으로 1과 -1 사용

START WITH n : 시퀀스 시작번호,기본값은 1

MAXVALUE n : 생성 가능한 시퀀스의 최대값

NOMAXVALUE : 오름차순일경우 최대값은 10의 27승, 내림차순일경우 최대값은 -1

MINVALUE n : 시퀀스 번호를 순환적으로 사용하는cycle로 지정된 경우 MINVALUE 에 도달한 후 새로 시작하는 시퀀스 값

NOMINVALUE: 오름차순일 경우 최소값은 1, 내림차순일경우 최소값은 -10의 26승

CYCLE | NOCYCLE: MAXVALUE 또는 MINVALUE 에 도달한 후 순환적인 시퀀스 번호의 생성 여부 지정, 기본 키로 사용할 경우 유일성을 보장을 위해 NOCYCLE로 지정

CACHE n | NOCACHE : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스 개수 기본값은 20

시작번호는 1 증가치는1 최대값은 2인 s_seq 시퀀스를 생성

CREATE SEQUENCE s_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 2;

user_sequences 데이터 딕셔너리에서 시퀀스 정보를 확인

SELECT min_value, max_value, increment_by, last_number FROM user_sequences WHERE sequence_name = 'S_SEQ';

CURRVAL과 NEXTVAL함수

CURRVAL과 NEXTVAL함수는 시퀀스에서 생성된 현재 번호를 확인하거나 다음 번호를 생성하는 함수이다.

CURRVAL을 통해 현재 번호를 조회하려면 NEXTVAL을 사용하여 새로운 값을 생성한 이후에만 가능하다.

(CURRVAL과 NEXTVAL함수는 INSERT문과 UPDATE문에서 사용할 수 있다)

s_seq의 현재 값과 다음 값을 확인

SELECT s_seq.CURRVAL FROM DUAL;

> NEXTVAL값을 생성하지 않고 CURRVAL값을 조회하면 오류가 발생한다.

SELECT s_seq.NEXTVAL FROM DUAL;

SELECT s_seq.CURRVAL FROM DUAL;

>NEXTVAL값을 한번 생성하면 CURRVAL값은 계속 조회할 수 있다

SELECT s_seq.NEXTVAL FROM DUAL;

SELECT s_seq.NEXTVAL FROM DUAL;

….. 100을넘어가면) MAX VALUE값을 초과하면 오류가 발생한다.

시퀀스를 이용한 기본 키 생성

테이블에서 기본키로 사용할 수 있는 적절한 칼럼이 없거나 다수의 칼럼을 결합해야 식별이 가능한 경우에는 시퀀스를 이용하여 기본 키를 생성하면 편리하다.

시퀀스를 이용하여 기본 키를 생성하느 방법은 NEXTVAL함수를 이용하여 시퀀스 값을 증가 또는 감소 시켜 일련번호를 생성한다.

시퀀스를 이용하여 웹 게시판의 기본 키를 생성하는 경우에는 시퀀스 값이 연속으로 생성되지 않고 건너띄는 경우가 있다, 그 이유는 시퀀스 값은 INSERT 명령문이 실행되면서 NEXTVAL에 의해 먼저 생성되므로 사용자가 INSERT명령문을 ROLLBACK하면 해당 시퀀스 값이 저장되지 않기 떄문이다. 또한 하나의 시퀀스를 여러 테이블에서 공유하는 경우에도 이런 현상이 발생 할 수 있다. 하지만 시퀀스에 의해 생성되는 기본 키는 행을 유일하게 구별하기 위한 식별자의 의미로 사용되므로 이런 현상은 무시해도 무방하다.

학생테이블에서 시퀀스 값을 이용하여 기본 키 값을 생성하여 입력하여라.

ALTER SEQUENCE s_seq MAXVALUE 100;

s_seq시퀀스의 최대값을 100으로 변경

INSERT INTO student(studno, name, deptno) VALUES(s_seq.NEXTVAL, ‘홍길동’, 101);

SELECT s_seq.currval FROM dual;

학생 테이블에서 시퀀스로 생성된기본키 값을 확인

SELECT studno, name, deptno FROM student WHERE studno=3;

시퀀스 정의 변경

시퀀스 생성 후에도 증가치, 최소값, 최대값 등과 같은 시퀀스의 정의를 수정할 수 있다. 변경된 시퀀스 정의는 새로 생성되는 시퀀스 값부터적용된다.

ALTER SEQUENCE 명령문을 사용하여 시퀀스 정의를 변경할 수 있다. 하지만 START WITH절은 시퀀스가 생성된 직후의 시작 값을 의미하므로 변경할 수 없다.

사용법

ALTER SEQUENCE sequence

[INCREMENT BY n]

[ MAXVALUE n | NOMAXVALUE]

[ MINVALUE n | NOMINVALUE]

[ CYCLE | NOCYCLE]

[ CACHE n | NOCACHE];

s_seq 시퀀스의 최대값을 200으로 변경하여라

ALTER SEQUENCE s_seq MAXVALUE 200;

시퀀스 삭제

: DROP SEQUENCE 명령문을 사용

s_seq시퀀스를 삭제하여라

DROP SEQUENCE s_seq;