출처 : http://naver.kaist.ac.kr/oracle_sql/s11.htm
♣ 데이터 조작 명령어와 트랜잭션 제어 명령어
◈데이터 조작어(DML)는 SQL의 핵심이다.
◈데이터베이스에 데이터를 추가, 변경, 삭제를 하려할 때 DML문을 사용한다.
◈아직 commitehlwl 않은 DML문의 집합을 트랜잭션 또는 작업의 논리적 단위라 한다.
| 명 령 | 설 명 |
| INSERT | 테이블에 새로운 행 추가 |
| UPDATE | 테이블의 행 변경 |
| DELETE | 테이블의 행 삭제 |
| COMMIT | 저장되지 않은 모든 변경사항을 DB에 저장 |
| SAVEPOINT | Savepoint 설정 |
| ROLLBACK | 저장되지 않은 모든 변경사항을 취소 |
1. 테이블에 새로운 행 삽입(INSERT)
◈INSERT문을 써서 테이블에 새로운 행을 추가한다.
◈Syntax
INSERT INTO 테이블명 [(column[, column…])]
VALUES (value_data[,value_data…]);
◈한 번에 한 행만 삽입된다.
◈모든 column에 대해 값을 갖는 새로운 행을 삽입한다.
◈INSERT 절의 column은 선택적으로 기입할 수 있다.
◈문자와 날짜 값은 단일 따옴표('')를 둘러싼다.
♣ 모든 column에 대해 값을 갖는 새로운 행 삽입
◈행에 대해 모든 값을 갖는 새로운 행을 삽입할 경우 테이블명 뒤에 column들을 표시할 필요가 없다.
즉, Column list가 없는 경우 테이블에 정의된 순서에 따라 값을 나열해야 한다.
◈명확성을 위해 INSERT절에 아래와 같이 column list를 사용하는 것이 좋다.
♠실습 : 부서번호 11, 부서명은 Finance, 지역번호를 2로 하는 새로운 부서를 추가한다.
| SQL> INSERT INTO s_dept VALUES (11,'Finance', 2); |
| SQL> INSERT INTO s_dept(id,name,region_id) VALUES (11,'Finance', 2); |
♣ NULL 값을 갖는 새로운 행 삽입
| 방법 | 설 명 |
| Implicit | column list에서 column명 생략. |
| Explicit | NULL 키워드를 이용. |
◈column list에서 column명을 제외 한다. : REGION_ID
◈VALUES 목록에 공백표시('')나 NULL을 기입한다. NULL 또는 ''
즉, column이나 value_data가 정의 되어있지 않으면 NULL값이 INSERT 된다. 이때 NULL값이
허용되지 않는 column인 경우는 ERROR가 발생하며, column 구분은 콤머로 한다.
◈RDBMS는 새로운 ROW를 INSERT할때 모든 column의 내용을 전부 정의할 필요가 없고 필요한
column의 내용만 정의하면 된다. 정의가 않된 column은 NULL이 들어간다.
◈DATA가 INSERT될 경우 KEY순서대로 DISK에 저장되는 것이 아니라 들어가는 순서대로 저장된다.
♠실습 : 지역 번호를 생략하고 새로운 부서를 입력한다. 지역 번호는 INSERT 절에 나열하지 않았기
때문에 NULL값이 지역번호(Region_Id)에 자동적으로 입력된다.
| SQL> INSERT INTO s_dept VALUES(12,'MIS',NULL); 또는 SQL> SELECT * FROM s_dept WHERE id = 12; |
♣ SQL 함수를 이용하여 특정한 값 삽입
◈USER 함수는 현재 사용자의 이름을 Return한다.
◈SYSDATE 함수는 현재 날짜와 시간을 Return한다.
♠실습 : S_EMP테이블에 사원에 대한 정보를 입력한다. USERID Column에 현재 사용자의 이름을,
Start_date Column에는 현재의 날짜와 시간을 넣는다.
| SQL> INSERT INTO s_emp(id,first_name, last_name, userid, salary, start_date) |
♠실습 : 행이 테이블에 삽입되었는지 확인한다.
| SQL> SELECT id, last_name, first_name, userid, salary, start_date |
2. 테이블의 특정 행 변경(UPDATE)
◈UPDATE 명령어는 조건에 맞는 ROW를 전부 변경시킨다.
◈만일 조건문(WHERE절)이 없으면 모든 ROW를 변경시킨다.
◈WHRER 절을 생략하면 해당 column의 모든 data값이 변경된다.
♣ Syntax
UPDATE {table|view}
SET column=값[,column=값]
[WHERE 조건]
♠실습 : s_dept에서 부서id가 58인 부서의 부서명을 'EDPS'로 변경하시오.
| SQL> select * from s_dept WHERE id = 58; SQL> UPDATE s_dept SET name = ‘EDPS’ WHERE id = 58; SQL> select * from s_dept WHERE id = 58; |
♠실습 : s_emp 테이블에서 id가 100인 사원의 급여를 100000 더하고, 보너스율을 15로 변경하시오.
| SQL> select id, salary,commission_pct from s_emp where id = 100; SQL> UPDATE s_emp Set salary = salary + 100000, commission_pct = 15 WHERE id = 100; SQL> select id, salary,commission_pct from s_emp where id = 100 |
◈ NULL 값에 어떤수를 더하여도 값은 NULL이다. 즉, 다음과 같이 수정하여야 한다.
| SQL> UPDATE s_emp SQL> select id, salary,commission_pct |
3. 테이블의 행 삭제(DELETE)
◈DELETE 명령을 이용하여 행을 삭제할 수 있다.
◈WHERE 절이 생략되면 테이블의 모든 행이 삭제된다.
◈Relational한 관계(Primary KEY와 Foreign KEY관계)로 되어있는 TABLE인 경우는
먼저 Foreign KEY로 정의된 TABLE의 ROW를 삭제시킨후에 Primary KEY로 정의된
TABLE의 ROW를 삭제시켜야 한다.
◈Syntax
DELETE FROM 테이블 [WHERE 조건];
♠실습 : 부서명이 EDPS 인 부서를 삭제하시오.
| SQL> DELETE s_dept where name = ‘EDPS’; |
♣ 무결성 제약조건 ERROR
◈다른 테이블에서 참조되어지는 Primary Key를 포함하고 있는 행을 삭제하려고 하면 무결성 제약 조건
error가 발생한다.
◈Relational한 관계(Primary KEY와 Foreign KEY관계)로 되어있는 TABLE인 경우는
먼저 Foreign KEY로 정의된 TABLE의 ROW를 삭제시킨후에 Primary KEY로 정의된
TABLE의 ROW를 삭제시켜야 한다.
♠실습 : 지역 번호가 1인 모든 부서를 삭제해 보십시오
| SQL> DELETE s_dept WHERE region_id=1; |
4. 데이터베이스 트랜잭션
♣ 트랜잭션 처리
◈Oracle7 Server는 트랜잭션 기반의 데이터 일관성을 보장한다.
◈트랜잭션은 데이터 변경시 더 많은 유연성과 제어를 제공하고,
프로세서나 시스템의 실패에도 데이터의 일관성을 보장
◈트랜잭션은 일관성있는 데이터의 변경을 위한 DML 명령으로 구성되어 있다.
◈예를 들면, 두 계정간의 자금 전송은 한 계정에는 차변을, 다른 계정에는 같은 액수를 대변에
포함시켜야 한다.
◈두 작업은 모두 성공하거나 실패해야만 한다.
◈대변은 차변없이는 commit되지 않아야 한다.
♣ 트랜잭션 유형
| 유 형 | 설 명 |
| 데이터 조작(DML) | 여러 DML 문으로 구성 |
| 데이터 정의(DDL) | 하나의 DDL 문으로 구성 |
| 데이터 제어(DCL) | 하나의 DCL 문으로만 구성 |
♣ 트랜잭션은 언제 시작되고 언제 끝나는가?
최초의 실행가능한 SQL 명령을 만나면 트랜잭션이 시작되고, 아래의 경우중 하나를 만나면 끝난다.
◈COMMIT나 ROLLBACK 명령 실행
◈CREATE 같은 DDL 명령이나 DCL 명령 실행
◈Dead Lock 발생
◈사용자에 의한 SQL*Plus 종료
◈기계 장애나 system crash
◈한 트랜잭션이 끝난 후에 다음의 실행가능한 SQL 문은 다음 트랜잭션을 자동적으로 시작할 것이다.
◈DDL 명령이나 DCL 명령은 자동적으로 커밋되므로 트랜잭션은 Implicit하게 종료된다.
♣ Explicit 트랜잭션
| 명령 | 설명 |
| COMMIT | 아직 저장되지 않은 모든 데이터 변경 사항을 DB에 저장하고 현재의 트랜잭션을 종료 |
| SAVEPOINT 이름 | 현재의 트랜잭션에 savepoint를 표시 |
| ROLLBACK (TO SAVEPOINT 이름) | 아직 저장되지 않은 모든 데이터 변경 사항을 무시(취소)하고 현재의 트랜잭션을 종료 |
♣ Implicit 트랜잭션
| 상태 | 환경 |
| 자동 커밋 | DDL 명령이나 DCL 명령 실행. COMMIT나 ROLLBACK을 명시적으로 실행하지 않고 SQL*Plus를 정상적으로 종료 |
| 자동 롤백 | SQL*Plus의 비정상적 종료나 시스템 실패 |
♣ COMMIT이나 ROLLBACK 이전의 데이터 상태
◈데이터의 이전 상태는 복구될 수 있다.
◈현재 사용자는 SELECT 문을 써서 DML문의 결과를 확인할 수 있다.
◈다른 사용자는 현재 사용자가 수행한 DML문의 결과를 볼 수 없다.
◈변경된 내용은 Lock이 설정되어 있어 다른 USER가 변경할 수 없도록 한다.
♣ COMMIT후의 데이터 상태
◈데이터에 대한 변경사항은 데이터베이스에 반영된다.
◈이전 데이터는 영원히 잃어버리게 된다.
◈모든 사용자는 결과를 볼 수 있다.
◈관련된 행에 대한 Lock 풀리고 다른 사용자들이 행을 조작할 수 있게 된다.
♣ 변경된 사항의 저장
◈ 트랜잭션 동안 생긴 모든 데이터 변경은 커밋될 때까지는 임시적인 것이다.
♣ COMMIT 결과
◈ 데이터에 대한 변경사항은 데이터베이스에 반영된다.
◈ 데이터의 이전 상태는 영원히 잃어버리게 된다.
◈ 모든 사용자는 트랜잭션의 결과를 볼 수 있다.
◈ 관련된 행에 대한 Lock이 풀리고 다른 사용자들이 데이터 변경을 할 수 있다.
◈ 모든 SAVEPOINT가 지워진다.
♠실습 : 1 명의 사원이 있는 Educatio이라는 부서를 만들고 변경된 내용을 저장 하라.
| SQL> INSERT INTO s_dept (id, name, region_id) |
♣ SAVEPOINTK를 이용한 롤백
◈ SAVEPOINT명령을 써서 현재의 트랜잭션에 savepoint를 생성한다.
◈ ROLLBACK TO SAVEPOINT명령을 써서 특정 savepoint로 롤백
◈ SAVEPOINT명령을 써서 현재의 트랜잭션에 SAVEPOINT를 할수 있다.
따라서 트랜잭션을 작게 분할할 수 있다.
◈ ROLLBACK TO SAVEPOINT문을 써서 표시한 곳까지의 rollback할 수 있다.
♠실습 : 모든 stock clerk의 급여를 10%씩 인상하고 UPDATE_DONE이라는 savepoint를 생성하여라.
| SQL> UPDATE s_emp SET salary = salary *1.1 |
♣ 문장 단위의 롤백
◈문장의 실행 중 errer가 발생하면 해당 문장만 롤백된다.
◈이전 DML문장에 의해 변경사항은 롤백되지 않는다.
◈DDL명령을 실행하기 전에 Server는 Implicit savepoint를 만들어 현재의 트랜잭션을 커밋하려한다.
◈DDL 명령이 수행되면 Server는 커밋을 하기 때문에 이전 내용을 롤백할 수 없다.
◈DDL 명령이 실패하면 트랜잭션은 커밋되지 않는다.