--제약조건을 확인할 수 있는 명령
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='USER2';
[TCL]
- *트랜잭션 제어어
- SQL명령이 저장된 트렌젝션을 실제 데이터베이스에 적용하거나 적용하지 않고 제거하는 명령
- 쉽게말하면, 잘못된 명령이나 데이터가 테이블에 들어가거나 삭제되는것을 방지하기 위해 있는 것이 트랜잭션이다.
*트랜잭션 : 현재 세션에서 작성된 SQL명령을 처리하기 위한 작업단위 - SQL명령의 집합
*세션 : DBMS에 접속하여 SQL명령을 전달하여 실행할 수 있는 사용자의 작업환경(즉, 사용자마다 작업환경을 바꿀 수 있단 말임)
TCL의 종류 | ||
COMMIT | ROLLBACK | SAVEPOINT |
올바르게 반영된 데이터를 DB에 반영시키는것 | 트랜잭션 시작 이전의 상태로 되돌리는것 | 저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. |
- 트랜잭션은 현재 세션에서 SQL명령(DML, DDL, DCL)을 작성하여 전달할 경우 자동으로 생성
- 현재 세션에서 작성되어 전달된 SQL명령은 DBMS에 의해 실행되는 것이 아니라, 세션의 트랜젝션에 저장된다.
- 트랜잭션에 저장된 SQL명령을 DBMS에 전달하여 실행하는 방법 - SQL명령 전달 후, 트랜잭션 자동소멸된다
- 현재 세션이 정상적으로 종료된 경우, 트랜젝션의 SQL명령이 자동으로 DBMS에 전달되어 실행된다.
- DDL명령 또는 DCL명령은 트랜젝션에 저장되면, 자동으로 DBMS에 전달되어 실행된다.(DML만 계속 쌓인다)
- DML은 COMMIT을 이용하여 트랜젝션에 저장된 SQL명령을 DBMS에 전달하여 실행한다. (세션이 정상 종료된 경우와 DDL과 DCL AUTO COMMIT이다.)
- 트랜젝션을 제거하는 방법 : 트랜젝션에 저장된 SQL명령이 전달되지 않아 실행되지 않는다.
- 현재 세션이 비정상적으로 접속 종료된 경우 트랜젝션 자동제거
- ROLLBACK명령을 이용하여 트랜젝션 제거 -> SQL명령이 제거가 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SELECT * FROM DEPT; -- 얘는 트랜젝션이 저장되지 않음
--DBMS에 SELECCT명령은 바로 전달되어 검색결과를 제공받을 수 있다.
SELECT * FROM DEPT;
--DEPT 테이블에서 부서번호가 50인 부서정보를 삭제
DELETE FROM DEPT WHERE DEPTNO = 50; --아직 삭제된게 아님, SELECT명령의 검색결과를 제공받아 트랜젝션의 SQL명령을 실행하여 결과를 제공
--현재 세션에서만 삭제된 것처럼 검색되며, 실제 테이블에서는 삭제되지 않고 존재한다.
--DML명령은 DBMS에 전달되어 실행되지 않고 트랜젝션에 저장된다.
SELECT * FROM DEPT;
--트랜젝션을 제거 : 트랜젝션에 저장된 SQL명령 삭제
ROLLBACK;
SELECT * FROM DEPT; -- 다시 50번 부서 살아남
COMMIT; -- 트랜젝션에 저장된 삭제명령을 DBMS에 전달하여 사용 -> 트랜잭션 자동 소멸됨
SELECT * FROM DEPT; -- 진짜 테이블에도 50번부서가 삭제됨
|
cs |
- 데이터의 일관성을 제공하기 위해 트렌젝션을 이용
- 데이터 일관성 : 다중사용자에게 동일한 검색결과를 제공하는 기능
- 현재 세션에서 작업중인 결과값을 다른 사용자에게 제공되지 않도록 트렌젝션 이용
DELETE FROM BONUS WHERE ENAME = 'KIM';
SELECT * FROM BONUS;
--다른 세션에서는 BONUS테이블에서 사원이름이 KIM인 사원을 검색된다.
--현재 세션의 작업완료 전까지 다른 세션에게는 작업 전의 결과값을 제공한다.
- 현재 세션에서 COMMIT 명령을 실행하면 작업이 완료되어 다른 세션에도 적용된 결과값이 나온다.
[LOCK기능]
- 데이터 LOCK기능을 제공하기 위해 트렌젝션을 이용
- ==> DBMS는 다중사용자 환경으로 같은 테이블의 행을 세션마다 조작이 가능하다
- 현재 세션에서 작업중인 테이블의 행을 다른 세션에서 작업하지 못하도록 트렌젝션이 데이터 잠금기능을 제공한다.
- 다중사용자가 동일한 테이블의 행을 조작할 수도 있으므로 잠금기능을 제공한다.
- 따라서 SYCRONIZED기능을 제공하는 벡터를 쓰기보다는 ARRAYLIST를 많이 사용한다. (굳이 사용X)
SELECT * FROM BONUS;
UPDATE BONUS SET SAL = 2000 WHERE ENAME = 'ALLEN';
[SAVEPOINT]
- SAVE POINT : 트렌젝션에 라벨을 붙이는 명령
- ROLLBACK처리 : 잘못된 SQL명령에 대한 취소 작업
- 트렌젝션에 저장된 모든 SQL명령 제거 작업초기화
- 라벨을 이용한 롤백 처리
- ROLLBACK TO 라벨명
--BONUS 테이블에서 사원이름이 ALLEN인 사원정보 삭제 - 라벨 설정
DELETE FROM BONUS WHERE ENAME='ALLEN';
SAVEPOINT DEL_ALLEN;
SELECT * FROM BONUS;
--BONUS 테이블에서 사원이름이 MARTIN인 사원정보 삭제 - 라벨 설정
DELETE FROM BONUS WHERE ENAME='MARTIN';
SAVEPOINT DEL_MARTIN;
SELECT * FROM BONUS;
--BONUS 테이블에 저장된 모든 사원정보 삭제
DELETE FROM BONUS;
SELECT * FROM BONUS;
--라벨을 이용한 롤백 처리
--형식)ROLLBACK TO 라벨명;
ROLLBACK TO DEL_MARTIN;
SELECT * FROM BONUS;
--사원이름이 MARTIN인 사원정보 삭제 명령 취소
ROLLBACK TO DEL_ALLEN;
SELECT * FROM BONUS;
--사원이름이 ALLEN인 사원정보 삭제 명령 취소
ROLLBACK;
SELECT * FROM BONUS;
[DDL(DATA DEFINITION)]
- 데이터 정의어.
- 데이터베이스 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어, 사용자 등)를 관리하기 위한 명령
1. 테이블(TABLE) : 데이터베이스에서 데이터를 저장하기 위한 가장 기본적인 객체
- 테이블 생성 : 속성의 집합
- 형식
CREATE TABLE 테이블명(컬럼명 자료형 (크기)) [DEFAULT 기본값] (컬럼명[컬럼제약조건], 컬럼명 자료형(크기 DEFAULT값 컬럼제약조건 테이블 제약조건)
[식별자] 작성 규칙 : 테이블명, 컬럼명, 라벨명 등
1. 영문자로 시작되며 1~30 범위의 문자들로 작성
2. A~Z, 0~9, _, $, # 문자를 조합하여 작성 - 대소문자 미구분 (단, 단어와 단어는 _를 사용하여 구분)
3. 영문자와 다른 문자 사용 가능 - 비권장
4. 키워드를 식별자로 선언할 경우 에러발생
5. 키워드와 이미 사용중인 식별자를 사용할 경우 에러가 발생
[자료형] : 컬럼에 저장 가능한 값을 표현하기 위한 키워드
1. 숫자형 : NUMBER(전체자릿수, 소숫점자릿수)
2. 문자형 : CHAR(크기) - 크기 : 1-2000(BYTE) >> 고정형 길이
VARCHAR2(크기) - 크기 : 1- 4000 (BYTE) >> 가변형 길이
LONG - 크기를 따로 주지 않아도 됨. 최대 2GBYTE를 줄 수 있음 >> 가변형(테이블에서 하나에 컬럼에만 부여가능하며, 정렬이 불가능하다.)
CLOB - 최대 4GBYTE저장가능 >> 가변형(문자데이터) >> 텍스트 파일을 저장할때 주로 사용
BLOB - 최대 4GBYTE저장가능 >> 가변형(원시데이터) >> 일반파일(이진 파일 : 이미지, 동영상파일)을 저 장 할 때 주로 사용
3. 날짜형 : DATE - 날짜와 시간정보를 저장할 수 있음
TIMESTAMP - 초단위 시간(원하는 패턴으로 변경가능) -> SIMPLEDATE를 사용하면 됨
[딕셔너리] : 시스템 정보를 확인할 수 있는 가상의 테이블(뷰)
-- USER_DICTIONARY(관리자가 아닐 경우) , DBA_DICTIONARY(관리자용) , ALL_DICTIONARY
1) USER_DICTIONARY : 현재 접속 사용자의 객체정보를 제공하는 딕셔너리
2) 현재 접속 사용자의 테이블 정보를 확인
SELECT OBJECT_NAME FROM USER_OBJECT WHERE OBJECT_TYPE = 'TABLE';
*USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리
--딕셔너리(DICTIONARY) : 시스템 정보를 확인할 수 있는 가상의 테이블(뷰)
--USER_DICTIONARY(일반사용자),DBA_DICTIONARY(관리자),ALL_DICTIONARY(모든 사용자)
--USER_OBJECTS : 현재 접속 사용자의 객체 정보를 제공하는 딕셔너리
--현재 접속 사용자의 테이블 정보 확인
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE';
--USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리
SELECT TABLE_NAME FROM USER_TABLES;
--USER_TABLES 딕셔너리에 대한 동의어(SYNONYM)로 TABS 제공
SELECT TABLE_NAME FROM TABS;
SELECT * FROM TAB
--컬럼에 제약조건이 없는 경우 어떤한 값이든 저장가능
--데이터 무결성 : 테이블에 잘못된 행(값)이 저장되지 않도록 설정하기 위한 기능 - 제약조건
INSERT INTO SALESMAN VALUES(2000,'임꺽정','10/10/10');
- 컬럼에 제약조건이 없는 경우 어떤한 값이든 저장가능
- 데이터 무결성 : 테이블에 잘못된 행(값)이 저장되지 않도록 설정하기 위한 기능 - 제약조건
- 행삽입시 컬럼 생략가능 : 생략된 컬럼에는 컬럼 기본값 자동저장됨
- 컬럼기본값을 설정하지 않으면 자동으로 NULL을 컬럼기본값으로 설정한다.
CREATE TABLE SALESMAN(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE);
SELECT TABLE_NAME FROM USER_TABLES;
--테이블 목록 및 속성 확인
SELECT TABLE_NAME FROM TABS;
SELECT * FROM TAB;
--컬럼기본값은 선언시 DEFAULT키워드를 사용하여 설정한다.
CREATE TABLE MANAGER(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE DEFAULT SYSDATE, PAY NUMBER DEFAULT 1000);
--현재 만들어져 있는 테이블 목록 및 속성 확인
SELECT TABLE_NAME FROM TABS;
- USER_TAB COLUMS : 테이블의 컬럼정보를 제공하는 딕셔너리
- 테이블의 컬럼 기본값 확인
SELECT COLUMS_NAME, DATA_DEFAULT FROM USER_TAB_COLUMS WHERE TABLE_NAME = 'MANAGER';
--값을 넣지 않은 컬럼은 DEFAULT값으로 채워지게 된다.
INSERT INTO MANAGER(NO, NAME) VALUES(1000,'홍길동');
[제약조건] : 잘못된 값이 컬럼에 저장되는 것을 방지하기 위한 기능 : 데이터 무결성
1) 컬럼 수준 제약조건 : 속성 선언시 컬럼에 제약조건을 설정
2) 테이블 수준 제약조건 : 테이블 선언시 테이블의 특정 컬럼에 제약조건을 설정
[NOT NULL 제약조건] : 컬럼에 NULL저장을 허용하지 않는 제약조건
1) 컬럼 수준의 제약조건으로만 설정가능
2) NOT NULL설정되지 않은 컬럼에는 기본적으로 NULL저장 허용
3) NOT NULL제약조건이 설정된 컬럼에 NULL을 전달할 경우 에러발생
--이게 컬럼수준의 제약조건
CREATE TABLE DEPT2 (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(12) NOT NULL, LOC VARCHAR2(11) NOT NULL);
--DEPT1 테이블 생성 : 부서번호 (숫자형), 부서이름(문자형), 부서위치(문자형)
CREATE TABLE DEPT1 (DEPTNO NUMBER(2), DNAME VARCHAR2(12), LOC VARCHAR2(11));
DESC DEPT1;
--DEP1T 테이블에 행 삽입
INSERT INTO DEPT1 VALUES(10, NULL, NULL);
INSERT INTO DEPT1 (DEPTNO) VALUES(20);
SELECT * FROM DEPT1;
COMMIT;
--DEPT2 테이블 생성 : 부서번호 (숫자형), 부서이름(문자형), 부서위치(문자형)
CREATE TABLE DEPT2 (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(12) NOT NULL, LOC VARCHAR2(11) NOT NULL);
DESC DEPT2;
--DEPT2 테이블에 행 삽입
INSERT INTO DEPT2 VALUES(10, NULL, NULL); --NOT NULL제약조건때문에 이렇게 삽입 X
INSERT INTO DEPT2 (DEPTNO) VALUES(20); -- 얘도 마찬가지(생략된 컬럼의 기본값이 NULL인 경우 에러발생)
SELECT * FROM DEPT2;
COMMIT;
[USER_CONSTRAINTS] : 테이블의 칼럼에 설정된 제약조건을 제공하는 딕셔너리
--CONSTRAINTS_NAME : 제약조건명 - 제약조건명을 설정하지 않으면 SYS_XXXX형식으로 자동설정
--CONSTRAINTS_TYPE : 제약조건 타입 - C(CHECK), P(PRIMARY), U(UNIQUE), R(REFERRENCE)
--칼럼에 제약조건을 설정할 경우 제약조건명을 저장하는 것을 권장(명확하게 알기 위해)
--형식) 컬럼명 자료형(크기) CONSTRAINT 제약조건명 제약조건 - 컬럼수준의 제약조건
--DEPT3 테이블 생성 - 부서번호(숫자형-NOT NULL),부서이름(문자형-NOT NULL),부서위치(문자형-NOT NULL)
CREATE TABLE DEPT3(DEPTNO NUMBER(2) CONSTRAINT DEPT3_DEPTNO_NN NOT NULL
,DNAME VARCHAR2(12) CONSTRAINT DEPT3_DNAME_NN NOT NULL
,LOC VARCHAR2(11) CONSTRAINT DEPT3_LOC_NN NOT NULL);
[CHECK] : 저장조건을 제공하여 조건에 맞는 값만 저장되도록 설정하는 제약조건
1) 컬럼수준의 제약조건으로는 해당 컬럼에 대한 조건식만 설정이 가능하다.
--컬럼수준으로 CHECK 제약조건 설정
CREATE TABLE SAWON2(NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER CONSTRAINT SAWON2_PAY_CHECK CHECK(PAY >= 3000000));
--제약조건확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON2';
--CHECK조건을 만족하지 못하므로 INSERT되지 못한다.
INSERT INTO SAWON2 VALUES(2000,'임꺽정', 50000)
2) 테이블 수준의 제약조건으로 설정
--모든 컬럼에 대한 조건식이 설정이 가능하다
--모든 컬럼 뒤에 , 찍고 CONSTRAINT __제약조건명__CHECK(제약조건 AND 제약조건...) 해주면 된다.
--테이블수준의 제약조건으로 설정
CREATE TABLE SAWON3 (NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER, CONSTRAINT SAWON3_PAY_CHECK CHECK(NO >= 1000 AND PAY >= 3000000));
[UNIQUE] : 중복된 컬럼값 저장을 방지하기 위한 제약조건
1) 컬럼수준의 제약조건
--테이블 다수의 컬럼에 UNIQUE 제약조건 설정이 가능하며 NULL 저장을 허용한다.
CREATE TABLE USER2 (ID VARCHAR2(20),
NAME VARCHAR2(20) CONSTRAINT USER2_NAME_UK UNIQUE,
PHONE VARCHAR2(15) CONSTRAINT USER2_PHONE_UK UNIQUE);
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='USER2';
INSERT INTO USER2 VALUES('ABC', '홍길동', '010-1234-5678');
--NAME컬럼이 기존행의 컬럼값과 중복값을 전달할 경우, UNIQUE제약조건에 의해 에러가 발생한다.
INSERT INTO USER2 VALUES('ABC', '홍길동', '010-1234-5688');
2)테이블 수준의 제약조건
CREATE TABLE USER3 (ID VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(15) , CONSTRAINT USER3_PHONE_UK UNIQUE(PHONE));
3) 테이블 수준의 제약조건 - 제약조건을 두개의 컬럼을 동시에 설정
-- 하나만 중복되도 상관없다.
CREATE TABLE USER3 (ID VARCHAR2(20),
NAME VARCHAR2(20),
PHONE VARCHAR2(15) , CONSTRAINT USER3_PHONE_UK UNIQUE(NAME, PHONE));
--NAME컬럼과 PHONE컬럼에 동일한 값이 전달될 경우, 유니크 제약조건에 의해 에러가 발생
[정리]
- unique와 pk제약조건은 중요한 제약조건이다.
- fk제약조건은 조인할때 필요한 제약조건이니 아는것이 좋다.
'💻 수업정리 (2020) > 오라클' 카테고리의 다른 글
[6/25] 인라인뷰와 시퀀스, 권한 부여 (0) | 2020.06.25 |
---|---|
[6/19] 제약조건과 VIEW (0) | 2020.06.19 |
[6/17] 오라클 서브쿼리 (0) | 2020.06.17 |
[6/16] 분석함수 (0) | 2020.06.16 |
[6/15] 오라클 함수사용 (0) | 2020.06.15 |