chapter01. 데이터베이스
DB = DBMS : 데이터베이스 관리 시스템(DBMS)
DBMS 종류들
Oracle, MS SQL, SyBase, MySQL, PostgresSQL 등
=================================================================
chapter02. 관계형 데이터베이스
테이블=릴레이션 : 순서쌍 : {<홍길동, 서울>,<김광식, 대전>,<박철수, 대구>,<최용만, 부산>}
테이블이름:주소록
필드:속성,열(column)
레코드:튜플,행(Row)
속성(Attribute) – 필드, 컬럼
릴레이션을 구성하는 각 열(Column)의 이름
ex) 주소록 릴레이션을 구성하는 속성
이름, 전화번호, 주소, 생일
튜플(Tuple) – 레코드, 행
릴레이션의 각 행
ex) 주소록 릴레이션의 한 튜플
<홍길동, 010-1234-1234, 서울, 80/03/15>
기본값은 null :0이나 공백 문자와는 다르다./특정 필드에 대한 값을 알지 못하거나 아직 정해지지 않아 입력하지 못한 경우의 필드 값
도메인 - 각 필드에 입력 가능한 값들의 범위, 즉 각 필드가 가질 수 있는 모든 값들의 집합
테이블 스키마:테이블구조(정의에 따라 만들어진 데이터 구조
테이블 특징 : 1.중복된 레코드가 존재하지 않는다.
2.순서는 의미가 없다.(행이 바뀌어도 상관없다)
3.열의 순서도 의미가없다
4.원자 값을 갖는다
------------------------------------------------------------------------
키(key)
기본키(Primary key) :1. 온전히 나만 가질수있는 정보(주민등록번호)
2. 테이블에 기본키는 하나만 만들 수 있다
3. 널 값을 가질 수 없다
외래키(foreign key) : 1.R1의 기본기를 R2에 참조하는것(R1 : 참조하는 테이블 , R2 : 참조하는 테이블)
2.관리하기 편하게 보기위해 쪼개는 것
3.기본키 안에있는 데이터만 사용가능하다
후보키 :기본키가 될수있는 후보(대체)키
관계형 데이터베이스 관리시스템 -RDBMS 1.가장 일반적인 DBMS
2.작성과 이용이 비교적 쉽고 확장이 용이하다.
SQL : 사용자와 관계형 데이터베이스를 연결시켜주는 표준검색언어(사전을의미)
==================================================================================
chapter03.SQL의 기본
1.데이터 딕셔너리(Data Dictionary: DD)
예시) SELECT * FROM TAB;
1.관계형 데이터베이스에서 객체를 정의하게 되면 그 객체가 가진 메타 데이터(metadata)의 정보가 저장되는 곳.
2.사용자에 의해서 추가, 삭제, 수정되지 못하며 오로지 오라클 시스템에 의해서만 가능.
-----------------------------------------------------------------------------------
오라클
SELECT * FROM TAB;
TAB은 TABLE의 약자로서 해당 사용자가 소유하고 있는 테이블의 정보를 알려주는 데이터 딕셔너리이다.
2. DESC(1)(2)(3)(4)
DESC "테이블명"
DESC : 테이블 구조를 확인하는 명령어 (not null)
EX) DESC DEPT; / DESC EMP;
---------------------------------------------------------------------------------
3. 오라클 데이터 형(1)
형식 : NUMBER(precision,scale) - 숫자 데이터 저장precision(전체자리)scale(소수점)
ex) no number(10)-가로안에 정수값 저장 , 소수점 이하는 반올림되어 정수 값만 저장된다
3. 오라클 데이터 형(2)
DATE(날짜)
"YY/MM/DD"형식(년/월/일만출력)2019년 12월 14일은 “19/12/14”로 출력.
3. 오라클 데이터 형(3)
CHAR(정적문자)
고정 길이 문자 데이터를 저장하기 위한 자료형
주소를 생성하고,CHAR(20) : 20개의 공간이 생긴다,최소 크기는 1이다
3. 오라클 데이터 형(4)
VARCHAR2
(데이터공간에 맞춰서 저장된다 "20자리까지 21이상 불가능")
숫자NUMBER / 날짜DATE / 문자VARCHAR2 (3가지 외우세요)
---------------------------------------------------------------------------------
C 삽입(회원가입)
R 조회(로그인) SELECT [ ]{*, [ ]}FROM테이블명;
U 수정(개인정보 변경)
D 삭제(탈퇴)
SELECT * FROM dept; (여기서 *은 전부를 다 보겠다라는 의미)
Dept no,
Dname ( ,라는 의미는 여기서 아래줄까지 두줄만 보겠다라는 의미)
------------------------------------------------------------------------------
SELECT empno,ename FROM EMP; EMP데이터 안에 있는 empno,ename 을 출력
SELECT * FROM EMP; EMP 전체출력
SELECT * FROM DEPT; DEPT 전체출력
SELECT JOB,SAL FROM EMP; EMP안에 JOB출력
SESECT ename, sal sla*12 FROM emp; 이름안에 연산식 출력
------------------------------------------------------------------------------
7. NULL(1)
NULL : 0도 아니고 빈 공간도 아니다
7. NULL(2)
예시) SELECT ename, sal, job, comm, sal*12, sal*12+comm FROM emp;
커미션(comm) 컬럼에 NULL값이 저장되어 있어 연산식의 결과도 NULL값으로 나오는것을 확인.
즉 NULL은 일반적인 연산이 되지 않는다.
컬럼에 NULL값이 저장되는것도 허용되지만 안되는경우도있다(제약조건)
----------------------------------------------------------------------------------
7. NULL(3)
예시) SELECT ENAME,SAL ,JOB,nvl(COMM,0),SAL*12,SAL*12+nvl(COMM,0) FROM EMP;
오라클에서는 NULL을 0또는 다른 값으로 변환하기 위해 nvl함수를 제공한다
함수 nvl은 null을 치환한다(각 구문에만 치환된다, 다른구분은 다시 해줘야한다)
-------------------------------------------------------------------------------
8. 별칭
예시) SELECT ename as “이름”, sal as “월급” FROM emp;
원래 컬럼명을 대신 원하는 컬럼명을 별칭으로 출력하고자 하는 구문
AS라는 키워드를 붙이면 컬럼명을 변경할수있다
문자 한글 상관없다,전부 대문자나 한글로출력된다
SELECT ENAME "NAME" "이름",SAL AS "월급" FROM EMP;
하지만 ""를써야 원하는 걸로 나올수있다
-----------------------------------------------------------------------------------
9. Concatenation 연산자
예시) SELECT ename || ‘ is a ‘ || job FROM emp;
출력 데이터들을 연결해주는 연산자로 '||'구문을 사용하여 해당 컬럼등을
연결하여 데이터를 출력할수있다
-------------------------------------------------------------------------------
10. DISTINCT 키워드
예시1) SELECT deptno FROM emp;
예시2) SELECT DISTINCT deptno FROM emp;
중복을 제거하는 키워드,가져오는 쿼리에서 중복 제거하여 가져올수있다
----------------------------------------------------------------------------------
chapter04.데이터 추출하기
1.WHERE
원하는 행(ROW)만 얻으려면 다음과 같이 조건을SELECT문에 WHERE절을 추가형 제시한다
형식 : SELECT * [COLUMN1,COLUMN2 ..] FROM 테이블명 WHERE 조건절;
구성 : WHERE SAL >= 300; ①컬럼 ②연산자 ③비교대상값
급여가 300이하인 사원의 사원번호,사원이름,급여를 출력하는 SQL문을 작성해보자.
SELECT empno,ename,sal FROM WHERE sal<=300 ;
2.비교 연산자
<> , != , ^= 다르다 를 의미
문자열이나 날짜는 단일 따옴표( ' )안에 표시해야 한다.
SELECT empno,ename,sal FROM EMP WHERE ENAME = '김사랑';
출력한 값에 대소문자 구분
2003년 9 월 1일 이후에 입사한 사원 출력하는 예제.
SELECT * FROM EMP WHERE HIREDATE >= '03/09/01';
------------------------------------------------------------------------
AND 두가지 조건을 모두 만족해야만 검색이 가능하다
OR 두가지 조건 중에서 한 가지만 만족하더라도 검색할 수 있다
NOT 조건에 만족하지 못하는 것만 검색한다
----------------------------------------------------------------------
300에서 500사이의 급여를 받는 사원을 조회해보자.
SELECT * FROM EMP WHERE SAL>=300 AND SAL<=500;
------------------------------------------------------------------------
BETWEEN AND연산자
SELECT * FROM EMP WHERE SAL>=200 AND SAL<=300;
SELECT * FROM EMP WHERE SAL BETWEEN 200 AND 300;
월급이 200미만 300이상 데이터 출력.
SELECT * FROM EMP WHERE SAL<200 OR SAL>300;
-----------------------------------------------------------------------
IN연산자
급여가 300,500,100이 모두 아닌 데이터 출력.
SELECT * FROM EMP WHERE SAL=300 OR SAL=500 OR SAL=1000
컬럼명 IN(A,B,C)
SELECT * FROM EMP WHERE SAL IN(300,500,1000);
---------------------------------------------------------------------------------
LIKE 연산자(중요함)★★★
1.검색하고자 하는 값을 정확히 알지 못하는 경우에도 검색이 가능하도록 하는 연산자이다
2.와일드 카드와 함께 사용한다
형식 : 컬럼명 LIKE PATTERN(와일드카드)
와일드카드 의미 1. % 문자가 없거나, 하나 이상의 문자가 어떤 값이 와도 상관없다.
이름을 김밖에 모른다면 ='%김%'
SELECT * FROM EMP WHERE ENAME LIKE '김%';
2. _ 하나의 문자가 어떤 값이 와도 상관없다
외자이거나 한글자만 가져올때 ='_동_'
SELECT * FROM EMP WHERE ENAME LIKE '_동_'';
--------------------------------------------------------------------------------
IS NULL관련 연산자
형식 : 컬럼명 IS NULL
SELECT * FROM EMP WHERE COMM IS NULL;
커미션이 NULL인 사원들의 데이터를 출력
IS NOT NULL
형식 : 컬럼명 IS NOT NULL
SELECT * FROM EMP WHERE COMM IS NOT NULL;
------------------------------------------------------------------------------
ORDER BY ★★ 코드 제일 마지막에 붙는다
순서에 맞게 정렬하는 조건을 말한다 조건은 내가결정한다
오름차순(Ascending) ASC
작은값이 위에 출력되고 아래로 갈수록 큰값이 출력
내림차순(Descending) DESC
큰 값이 위에 출력되고 아래로 갈수록 작은 값이 출력
정렬을 하기위서 SELECT문에 ORDER BY 절을 추가하고 어떤 컬럼을 기준으로 정렬할 것이지 결정해야한다
ASC(오름차순)
숫자-작은값부터 정렬(1~9 , 'A'~'Z')
문자-사전 순서로 정렬
날짜 - 빠른 날짜 순서로 정렬
NULL-가장 마지막에 나온다
형식 : ORDER BY 컬럼명 ASC;
급여를 적게 받는 사원순으로 출력한 예제이다.
SELECT * FROM EMP ORDER BY SAL ASC;
DESC(내림차순)
숫자-큰값부터 정렬(9~1 , 'Z' ~'A')
문자-사전 반대 순서로 정렬
날짜 - 늦은 날짜 순서로 정렬
NULL-가장 먼저 나온다
형식 : ORDER BY 컬럼명 DESC;
급여를 많이 받는 사원순으로 출력한 예제이다.
SELECT * FROM EMP ORDER BY SAL DESC;
문자 순으로 출력
형식 : SELECT * FROM emp ORDER BY ename ASC;
날짜 순으로 출력
최근에 입사한 사람부터 출력하는 예제.
SELECT * FROM EMP ORDER BY HIREDATE DESC;
급여를 많이 받는 사람을 기준으로 정렬한뒤
SELECT * FROM EMP ORDER BY SAL DESC;
혹시 급여가 같을 경우 이름의 철자가 빠른사람으로 출력
SELECT * FROM EMP ORDER BY SAL DESC,ename ASC;
1.급여가 300이하인 사원의 사원번호,사원명,급여를 출력하시오.
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL<=300;
2.이름이 "오지호"인 사원의 사원번호,사원명,급여를 출력하시오.
SELECT empno,ename,sal FROM EMP WHERE ENAME = '오지호';
3.급여가 250이거나, 300이거나, 500인 사원들의 사원번호,사원명,급여를 출력하시오.
(비교 연산자 or 논리 연산자를 작성해보고, in연산자로 한번 더 작성하시오.)
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL= 250 OR SAL=300 OR SAL=500;
4.급여가 250도 , 300도, 500도 아닌 사원들을 검색하시오.
(비교 연산자와 and논리 연산자를 사용하여 작성해보고,in연산자로 한 번 더 작성하시오.)
SELECT * FROM EMP WHERE SAL NOT IN(250,300,500);
SELECT* FROM EMP WHERE SAL!=250 OR SAL!=300 OR SAL!=500;
5.사원들 중에서 이름이 "김"으로 시작하는 사람과 이름 중에 "기"를 호함하는 사원의 사원번호와 사원명을 출력하시오.
(LIKE 연산자와 와일드카드를 이용)
SELECT * FROM EMP WHERE ENAME LIKE '김%'OR ENAME LIKE '%기%';
6.상급자가 없는 사원(사장)을 검색하기 위한 SQL문을 작성하시오.
(NILL 이용)
SELECT * FROM EMP WHERE MGR IS NULL;
7.사원 테이블의 자료를 입사일을 기준으로 정렬하여 최근에 입사한 직원을 가장 먼저 출력하는 쿼리문을 작성하시오.
(사원번호,사원명,직급,입사일만 출력)
SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP ORDER BY HIREDATE DESC;
SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP;
================================================================
chapter05. SQL주요함수
1.DUAL테이블
DUMMY라는 단 하나의 컬럼으로 되어있다.
selsct * from dual;(출력값 1X로 나온다)
DESC DUAL;
2.숫자함수
ABS - 절대값을 구한다(거리,나이차)
-10에 대한 절대값을 구하는 예제. SELECT -10,ABS(-10)FROM DUAL;
FLOOR - 소수점 아래를 잘라낸다(버림)
SELECT 34.5678, FLOOR(34.5678) FROM DUAL;
ROUND - 특정 자릿수에서 반올림한다
34.5678에 대한 ROUND함수르 적용한 예제
SELECT 34.5678, ROUND(34.5678) FROM DUAL;
특정 자릿수에서 반올림 할수있도록 자릿수를 지정할수있다
SELECT 34.5678, ROUND(34.5678 , 2) FROM DUAL;
소수점이 아니라 일단위,십단위,백단위 순으로 거슬러 올라가며 반올림이 이루어진다
SELECT 34.5678, ROUND(34.5678, -1) FROM DUAL;
TRUNC - 특정 자릿수에서 잘라낸다(버림)
SELECT TRUNC(34.5678,2) , TRUNC(34.5678 -1), TRUNC(34.5678)FROM DUAL;
지정한 자리 수 이하를 버린결과를 구해주는 함수
함수의 두번째 인자 값이 2이면 소수점 이하 세번째 자리에서 버림 연산을 하여 소수점
MOD - 입력받은 수를 나는 나머지값을 반환한다(%나머지값이랑똑같다)
SELECT MOD(27,2),MOD(27,5),MOD(27,7) FROM DUAL;
3.문자 처리 함수
SUBSTR - 문자를 잘라 추출한다.(한글 1byte)
LENGTH - 문자의 길이를 반환한다.(한글 1byte)
데이터의 길이를 알려주는 함수
SELECT LENGTH('오라클') , LENGTH('Oracle') FROM DUAL;
SUBSTR- 대상 문자열이나 컬럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출한다
형식 SUBSTR(대상,시작위치)
SELECT SUBSTR('WELCOME TO Oracle',4,3) FROM DUAL;
시작위치가 뒤쪽에서 4번째 추출글자수 3개
SELECT SUBSTR('WELCOME TO Oracle'-,4,3) FROM DUAL;
SUBSTR함수를 이용해서 입사일을 저장하고 있는 hiredate 컬럼에서 첫 글자부터 2개를 추출하면
SELECT SUBSTR(hiredate, 1 , 2)as "년도" , SUBSTR(hiredate, 4 , 2)as "달" FROM EMP;
INSTR- 대상 문자열이나 컬럼에서 특정 문자가 나타나는 위치를 알려준다
TRIM 칼럼이나 대상 문자열에서 특정 문자가 첫번째 글자이거나 마지막 글자이면
잘라낸다
4.날짜함수
SYSDATE -시스템에 저장된 현재 날짜를 반환한다(현재 시스템 날짜)
SELECT SYSDATE FROM DUAL;
날짜 연산
SELECT SYSDATE-1어제 ,
5.형 변환 함수(문자가 중간에 껴있다)
TO_CHAR(DATE, 'FORMAT')(날짜->문자)
DATE형태의 데이터를 지정한 양식에 의해 VARCHAR2형의 문자로 변형된다
현재 날짜와 시간을 출력하는예제.(MI는 분을 표현 , SS는 초를 표현)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
숫자 형태인 20040108을 TO_DATE 함수를 사용해서 날짜형으로 변환하는 예제.
SELECT emame, hiredate FROM emp WHERE hiredate = TO_DATE('20040108',YYYYMMDD');
올해 며칠이 지났는지 현재 날짜에서 2020/01/01을 뺀 결과를 출력하는 예제.
SELECT TRUNC(sysdate - TO_DATE('2020/01/01', 'YYYY/MM/DD')) FROM dual;
TO_NUMBER(문자->숫자) 특정 데이터를 숫자형으로 변환해 주는 함수
'20,000'을 '10,000의 차이를 알아보기 위해서 빼기를 하는 예제.
SELECT TO_NUMBER('20,000' , '99,999') - TO_NUMBER('10,000' , '99,999')FROM DUAL;
DECODE
프로그램 언어에서 가장 많이 사용되는 SWITCH CASE 문과 같은 기능 을 갖는다
여러 가지 경우에 대해서 선택할 수 있다
DECODE(표현식 , 조건1 ,결과1,
조건2 , 결과2,
조건3, 결과3,
기본결과n
)
부서 번호에 해당되는 부서명을 구하는 예제(DECODE 함수 사용)
SELECT ename , deptno , DECODE(deptno, 10 , 'ACCOUNTING' , 20, 'RESEATCH', 30, 'SALES', 40, 'OPERATIONS') FROM EMP;
as dname-생략가능
CASE
프로그램 언어의 if else if else 와 유사한 구조를 갖는다
CASE표현식 when조건1 then 결과 1
when조건1 then 결과 1
when조건1 then 결과 1
ELSE 결과n
END
부서 번호에 해당되는 부서명을 구하는 예제(CASE 함수 사용)
SELECT ename, deptno , CASE
WHEN deptno=10 THEN 'ACCOUNTING'
WHEN deptno=10 THEN 'RESEATCH'
WHEN deptno=10 THEN ' SALES'
WHEN deptno=10 THEN ' OPERATIONS'
END
AS DNAME-생략가능
FROM EMP
NVL함수
NUL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수.
SELECT ename,sal, comm, sal*12+comm, NVL(comm,0),sal*12+NVL(comm,0)FROM EMP
1.10월에 입사한 사월을 출력해 보시오(SUBSTR 함수를 사용한다)
SELECT SUBSTR(hiredate, 4 , 2) as "월" FROM EMP where SUBSTR(hiredate, 4 , 2)='10';
2.2003년도에 입사한 사원을 알아내기 위한 쿼리문을 작성하시오(SUBSTR 함수를 사용한다)
SELECT SUBSTR(hiredate, 1,2) "년도" FROM EMP where SUBSTR(hiredate, 1,2)='03';
3.이름이 "기"로 끝나는 사원을 검색하시오(LIKE연산자를 사용하지말고SUBSTR 함수를 사용하시오)
SELECT * FROM EMP WHERE SUBSTR(ENAME, -1)='기';
4.직급에 따라 급여를 인상하도록 한다
직급이 '부장'인 사원을 5%, '과장'인 사원은 10%,'대리'인 사원은 15%, '사원'인 사원은 20%,
이외의 직급은 급여 그대로 출력한다(DECODE 함수를 사용하시오.)사원번호,이름,직급,급여,인상급여(UPSAL)출력
SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB,
'부장', SAL*1.05,
'과장', SAL*1.1,
'대리', SAL*1.15,
'사원', SAL*1.2,
SAL) AS "UPSAL"
FROM EMP;
5.입사일을 연도는 4자리(YYYY), 월은 숫자(MON)로 표시하시오.
(입사일, 변경 입사일CDATE출력
SELECT HIREDATE,TO_CHAR(HIREDATE, 'YYYY/MON/DD')AS "CDATE"FROM EMP;
====================================================================
chapter06.그룹함수★★★
하나 이상의 행을 그룹으로 묶어 연산하여 종합, 평균 등 하나의 결과로 나타낸다
그룹함수의종류
SUM-해당 컬럼 값들에 대한 총합을 구하는 함수 (NILL인 것을 제외하고 계산한다)
SELECT SUM(sal)FROM emp; (급여의 총액을 구하는 예제)
SELECT SUM(comm)FROM emp; (커미션 총액을 구하는 예제)
AVG-그룹의 평균을 반환합니다
SELECT SUM(AVG)FROM emp; (급여 평균을 구하는 예제)
COUNT-그룹의 총 개수를 반환합니다★★★
테이블에서 조건을 만족하는 행의 개수를 반환하는 함수
특정 컬럼을 기술하면 해당 컬럼 값을 갖고 있는 로우의 개수를 계산하여 출력한다
null값에 대해서는 개수를 세지 않는다
COUNT함수에 COUNT(*)처럼 *를 적용하면 테이블의 전체 행의 수를 구한다
SELECT COUNT(*),COUNT(comm) FROM emp; (전체 사원의 수와 커미션을 받는 사원의 수를 구하는 예제)
SELECT COUNT(job) FROM EMP;(사원들의 직급 개수 출력 예제)
SELECT COUNT(DISTINCT job) FROM EMP; (직업의 종류가 몇개인지 즉, 중복되지 않은 직업의 개수를 출력할 때에는 중복 행 제거 키워드DISTINCT를 이용하여 질의한다)
MAX-그룹의 최대값을 반환합니다
MIN-그룹의 최소값을 반환합니다
SELECT MAX(SAL),MIN(SAL) FROM EMP; (가장 높은 급여와 가장 낮은 급여를 출력하는예제)
그룹 함수와 단순 컬럼
★그룹함수와 일반 컬럼은 같이 사용할 수 없다
컬럼 값들 중에서 최대 값을 구하는함수
SELECT ename, MAX(sal) FROM emp; (각 사원들의 최대 급여를 구하는 예제)
GROUP BY
그룹 함수를 쓰되 어떤 컬럼 값을 기준으로 그룹 함수를 적용할 경우 사용.
형식 SELECT 컬럼명,그룹함수
FROM 테이블명
WHERE 조건(연산자)
GROUP BY 컬럼명;
사원 테이블을 부서번호로 그룹 짓는 보는 예제
SELECT deptno FROM emp GROUP BY deptno;
소속 부서별 평균 급여 구하는 예제
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
소속 부서별 최대 급여와 최소 급여를 구하는 예제
SELECT deptno, MAX(sal), MIN(sal) FROM emp GROUP BY deptno;
부서별로 사원 수와 커미션을 받는 사원들의 수를 계산하는예제
SELECT deptno, COUNT(*), COUNT(comm) FROM emp GROUP BY deptno;
직급별 급여합계출력
SELECT sum(sal) FROM EMP;
급여를 가장 많이 받는 직급 순으로 출력
SELECT JOB FROM EMP ORDER BY SAL DESC;
HAVING
SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE절을 사용하지만 그룹의 결과를 제한할 때는HAVING절을사용
부서별로 그룹을지은 후(GROUP BY),그룹 지어진 부서별 평균 급여가 500이상인(HAVING) 부서 번호와 부서별 평균 급여를 출력하는예제
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>= 500;
부서별 급여의 최대값과 최소값을 구하되 최대 급여가 500이하인 부서만 출력하는 예제
SELECT deptno, max(sal),min(sal) from emp GROUP BY deptno HAVING max(sal)<= 500;
1. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오.
(평균에 대해서는 정수로 반올림하시오.)
SELECT MAX(SAL),MIN(SAL),SUM(sal),AVG(sal),ROUND( AVG(sal),0)FROM EMP ORDER BY SAL;
2. 각 직급별로 급여 최고액, 최저액, 총액 및 평균액을 출력하시오.
(평균에 대해서는 정수로 반올림하시오.)
SELECT MAX(SAL),MIN(SAL),SUM(sal),AVG(sal),ROUND( AVG(sal),0) FROM EMP GROUP BY job;
3. 그룹 함수를 이용하여, 직급별 사원수를 출력하시오.
SELECT COUNT(*) FROM emp GROUP BY job;
4. 그룹 함수를 이용하여 상급자를 가지고있는 직원의 수를 출력하시오.
SELECT COUNT(*) FROM EMP WHERE MGR IS NULL;
5. 급여 최고액, 급여 최저액의 차액을 출력하시오.
SELECT MAX(sal)- MIN(sal) FROM emp;
6. 직급별 사원의 최저 급여를 출력하시오.
최저 급여가 500 미만인 그룹은 제외시키고,
결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.
SELECT MIN(SAL) FROM EMP GROUP BY JOB HAVING AVG(sal)>= 500 ORDER BY MIN(SAL) DESC;
7. 각 부서에 대해 부서번호, 사원수, 부서 내의
모든 사원의 평균 급여를 출력하시오.
(평균 급여는 소수점 둘째 자리로 반올림하고, 부서번호별 오름차순으로 정렬하시오.)
SELECT ROUND (AVG(sal), 2), DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO ASC;
8. 각 부서에 대해 부서이름, 지역명, 사원수,
부서 내의 모든 사원의 평균 급여를 출력하시오.
(DEPT 테이블을 참고하여 부서이름, 지역명을
DECODE함수를 통해 출력하시오. 평균 급여는 정수로 소수점 둘째 자리로 반올림하시오)
SELECT DECODE(DEPTNO, 10,'경리부',20,'인사부',30,'영업부',40,'전산부'),
DECODE(DEPTNO, 10,'서울',20,'인천',30,'용인',40,'수원'),
ROUND (AVG(sal), 2),
COUNT(*)
FROM EMP GROUP BY DEPTNO;
==========================================================================
chapter7 join★★★
COUNT(*)전체행의개수를 한번에 볼수있다
EQUI JOIN = INNER JOIN
형식 : SELECT * FROM tabel1 inner join table2 ON table1.column1 = table2.column2;
예제. ON 절에 조인 조건을 명시하고 , 다른 조건은 where절에 지정한다.
SELECT ename, dname
FROM emp inner join dept
ON emp.deptno=dept.deptno
WHERE ename='김사랑';
같은데이터 같은정보만출력한다
별칭을 넣을때 띄어쓰기 넣어준다
OUTER JOIN
OUTER JOIN은 어느 한쪽 테이블에는 해당하는 데이터가 존재 하는데 다른 쪽 테이블에는
데이터가 존재하지 않을경우 그 데이터가 출력되지 않는 문제점을 해결하기 위해 사용하는 조인기법이다
형식 : SELECT * FROM table1 [Left | Right | Full] Outer Join table2
ANSI JOIN
형식 : T1 조인종류 T2
SELECT * FROM EMP CROSS JOIN DEPT;
1. 경리부에서 근무하는 사원의 이름과 입사일을 출력하시오
SELECT E.name, D.name , E.HIREDATE
FROM emp E inner join dept D
ON E.deptno=D.deptno
WHERE dept.DNAME = '경리부';
2. 인천에서 근무하는 사원의 이름과 급여를 출력하시오.
SELECT ename , SAL
FROM emp E inner join dept
ON E.deptno=dept.deptno
WHERE dept.LOC = '인천';
=========================================================================
서브쿼리★
하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호로 감싸야한다
메인 쿼리가 실행되기 전에 한번만 실행된다
메인쿼리
SELECT DNAME
FROM DEPT 서브쿼리
WHERE DEPTNO = (SELECT DEPTNO 메인쿼리안에 괄호 서브쿼리가 들어 가 있다
FROM EMP
WHERE ENAME='김사랑');
단일행 서브쿼리
단일 행 (Single Row)서브 쿼리는 수행 결과가 오직 하니의 로우(행,Row)만을 반환하는 서브 쿼리를 말한다
예제 '김사랑'과 같은 부서에서 근무하는 사원의 이름과 부서번호를 출력하시오.
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME='김사랑');
1.'김사랑'과 동일한 직급을 가진 사원을 출력하시오
SELECT *
FROM EMP
WHERE JOB = (SELECT JOB
FROM EMP
WHERE ENAME='김사랑');
2.'김사랑'의 급여와 동일하거나 더 많이 받는 사원명과 급여를 출력하시오.
SELECT E.NAME, E.SAL
FROM EMP
WHERE SAL >= (SELECT SAL
FROM EMP
WHERE ENAME='김사랑');
3.'용인'에서 근무하는 사원의 이름,급여를 출력하시오.
SELECT ename , SAL
FROM emp E inner join dept
ON E.deptno=dept.deptno
WHERE dept.LOC = '용인';
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC='용인');
4.직속 상관이 '장동건'인 직원의 이름과 급여를 출력하시오.
SELECT ENAME, SAL,MGR
FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME='장동건');
평균 급여를 구하는 쿼리문을 서브쿼리로사용하여 평균 급여보다 더 많이
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP);
다중 행 서브 쿼리
결과의 행이 여러개일때
IN 메인쿼리 비교조건
ANY,SOME 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참
ALL 메인쿼리의 비교조건이 서브쿼리의 검색결과와 모든 값이 일치하면 참
IN 연산자문제
급여를 400이살 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보를 출력하는 예제.
SELECT ENAME, SAL,DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DISTINCT DEPTNO
FROM EMP
WHERE SAL>=400);
ALL 연산자문제(최대값보다 더 크거나 많으면 참)
30번 소속 사원들 중에서 급여를 가장 많이받는 사원보다 더 많은 급여를 받는 사람의 정보를 출력하는 예제
SELECT ENAME, SAL
FROM EMP
WHERE SAL> ALL (SELECT SAL
FROM EMP
WHERE DEPTNO=30);
ANY 연산자
SELECT ENAME, SAL
FROM EMP
WHERE SAL> ANY (SELECT SAL
FROM EMP
WHERE DEPTNO=30);
5.부서별로 가장 급여를 많이 받는 사원의 정보를 출력하시오.(IN 연산자 사용)
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY deptno );
6.직급이 '과장'인 사람이 속한 부서의 부서 번호와 부서명,지역을 출력하시오.(IN 연산자 사용)
SELECT *
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='과장') ORDER BY deptno;
7.가장 많은 급여를 받는 '과장'보다 더 많은 급여를 받는 직원들의 이름,급여,직급을 출력하시오(ALL 연산자 사용)
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > ALL ( SELECT MAX(SAL) FROM EMP WHERE JOB='과장');
8.가장 적은 급여를 받는 '과장'보다 더 많은 급여를 받는 직원들의 이름,급여 직급을 출력하시오.(ANY 연산자 사용)
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > ANY ( SELECT MIN(SAL) FROM EMP WHERE JOB='과장');
===============================================================================
chapter09. DDL 구조를 만드는 관리하는 언어
DDL
테이블과 같은 데이터 구조를 정의하는데 사용되는 언어로,생성,변경,삭제,이름 변경등 데이터 구조와 관련된 명령어들을 말한다
SCHEMA, DOMAIN, TABLE,VIEW,INDEX를 정의하거나 변경 또는 삭제할 때 사용.
CREATE - 데이터 베이스,케이블들을 생성하는 역할
ALTER - 테이블을 수정하는 역할
DROP - 데이터 베이스 , 테이블을 삭제하는 역할
TRUNCATE - 테이블을 초기화 시키는 역할(테이블의 초기내용이 나온다)
데이터 형
VARCHAR2(size) :가변 길이 문자 데이터. 실제 입력된 문자열의 길이만큼 저장 영역을 차지 최대 크기 2000byte
숫자형 데이터형 NUMBER :최대 22Byte
날짜 데이터형 DATE : BC 4712년 1월 1일~AD 4712년 12월 31일까지의 날짜
문자 데이터 형 1)VARCHAR2 2)CHAR 3)LONG
숫자 데이터 형 1)NUMBER
날짜 데이터 형 1)DATE 2)TIMESTAMP
CLOB - e-BOOK과 같은 대용량의 텍스트 데이터를 저장하기 위해서 사용
ROWID
테이블에서 행의 위치를 지정하는 논리적인 주소값
테이블의 특정 레코드를 랜덤하게 접근하기 위해서 주로 사용된다
형식 : SELECT rowid ,empno, ename FROM emp;
1.CREATE TABLE 명령어로 테이블을 생성하시오(길이 및 데이터 형태 자유)
테이블 명 : EMP01
컬럼 : EMPNO – 사원번호
ENAME – 사원명
HEIGHT – 키
ADDRESS – 주소
PHONE – 핸드폰번호
CREATE TABLE EMP01
(
EMPNO NUMBER(38,0)
ENAME VARCHAR2(20),
HEIGHT NUMBER(5,2)
ADDRESS VARCHAR2(200),
PHONE NUMBER(20)
);
서브 쿼리를 사용하여 이미 존재하는 테이블과 동일한 구조와 내용을 갖는 새로운 테이블을 생성할 수 있다
CREATE TABLE EMP02 AS SELECT * FROM EMP;
기존 테이블에서 원하는 컬럼만 선택적으로 복사해서 생성 할 수도있다
CREATE TABLE EMP03 AS SELECT EMPNO,ENEME FROM EMP;
기존 테이블에서 원하는 행만 선택적으로 복사해서 생성할 수도 있다
CREATE TABLE EMP04 AS SELECT * FROM EMP WHERE DEPTNO=10;
서브쿼리로 테이블 복사하기
서브 쿼리를 사용하여 이미 존재하는 테이블과 같은구조의 테이블을 복사하되 데이터는 복사하지 않을 수 있다
형식 : CREATE TABLE EMP05 AS SELECT * FROM EMP WHERE 1=0;
WHERE 1=0은 항상 거짓이기 때문에 이를 이용하여 테이블을 생성시 데이터를 제외한 테이블 구조만 복사할 수 있다
2.EMP 테이블을 복사하되 사원번호, 사원명, 급여컬럼으로 구성된 테이블을 생성하시오.(생성 테이블의 이름은 EMP06)
CREATE TABLE EMP06 AS SELECT ENAME,ESAL FROM EMP;
ALTER TABLE
기존 테이블의 구조를 변경하기 위한 DDL 명령문의 하나로, 테이블에대한 구조 변경은 컬럼추가,삭제,
컬럼의 타임이나 길이가 변경할때 사용한다
ADD COLUMN 절을 사용하여 새로운 컬럼을 추가
MODIFY COLUMN 절을 사용하여 기존 컬럼을 수정
DROP COLUMN 절을 사용하여 기존 컬럼을 삭제
컬럼추가
ALTER TABLE ADD 문은 기존 테이블에 새로운 컬럼을 추가한다.
새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에 만들어 넣을 수 없다.
이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이 추가되지만, 컬럼 값은 NULL 값으로 입력됩니다
형식 : ALTER TABLE 테이블 명
ADD (컬럼 명 데이터 타입,....);
3.EMP01 테이블에 문자타입의 직급(JOB) 컬럼을 추가하시오(크기9)
ALTER TABLE EMP01 ADD (JOB VARCHAR2(9));
ALTER TABLE EMP01 ADD JOB VARCHAR2(9);
컬럼수정
ALTER TABLE MODIFY 다음과 같이 사용하면 테이블에 이미 존재하는 컬럼을 변경할 수 있게 된다.
컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나 크기, 기본 값들을 변경한다는 의미.
형식 : ALTER TABLE 테이블명
MODIFY(컬럼 명 데이터 타입,...);
4.EMP01테이블의 직급(JOB)컬럼을 최대 30글자까지 저장할 수 있게 (수정)변경 하시오.
ALTER TABLE EMP01 MOEIFY (JOB VARCHAR2(30));
ALTER TABLE EMP01 MODIFY JOB VARCHAR2(30);
컬럼 삭제
ALTER TABLE DROP COLUMN 명령어로 컬럼을 삭제할 수 있다.
형식 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
5.EMP01 테이블의 직급(JOB)컬럼을 삭제하시오.
ALTER TABLE EMP01 DROP COLUMN JOB;
SET UNUSED
컬럼을 미사용 상태로 표시
형식 : ALTER TABLE 테이블명
SET UNUSED(컬럼명);
미사용으로 표시된 컬럼을 드롭
형식 : ALTER TABLE 테이블명
DROP UNUSED COLUMNS;
* EMP02 = EMP COPY TABLE
ALTER TABLE EMP02 SET UNUNUSED(JOB);
DROP TABLE문은 기존 테이블명을 삭제한다
형식 : DROP TABLE 테이블 명;
6.EMP01 테이블을 삭제하시오.
DROP TABLE EMP01;
TRUNCATE
기존에 사용하던 테이블의 모든 로우(데이터, 레코드)를 제거하기 위한 명령어
테이블을 최초 생성된 초기상태로 만든다.
용량이 줄어들고, 인덱스 등도 모두 삭제된다.
Rollback이 불가능하다.(복구 불가)
무조건 전체 삭제만 가능하다.
삭제 행 수를 반환하지 않는다.
형식 : TRUNCATE TABLE 테이블 명;
7.TRUNCATE를 이용하여 EMP06테이블의 모든 데이터를 삭제하시오.
TRUNCATE TABLE EMP06;
RENAME
기존에 사용하던 테이블의 이름을 변경하기 위한 명령어
RENAME old_name TO new_name;
8. EMP06 테이블의 이름을 TEST 란 이름으로 변경하시오.
RENAME EMP06 TO TEST;
데이터 딕셔너리
데이터 베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 '시스템테이블'
데이터 딕셔너리 뷰
접두어 의미
DBA_XXXX - 데이터 베이스 관리자만 접근 가능한 객체 등의 정보 조회
ALL_XXXX - 자신 계정 소유 또는 권한을 부여 받는 객체 등의 관한 정보 조회
USER_XXXX - 자신의 계정이 소유한 객체 등에 관한 정보 조회(자신이 생성한 테이블이나 인덱스나 뷰)
1.DESC 명령어로 데이터 딕셔너리 뷰 USER_TABLES의 구조 확인
DESC USER_TABLES;
테이블의 이름을 알려주는 TABLE_NAME 컬럼의 내용을 확인한다
SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME DESC;
=======================================================================
chapter 10.DML★★★★★
DML
테이블에 데이터 검색,삽입,수정,삭제하는 데 사용한다
Rollback이 가능하다
SELECT - 데이터를 검색하는 역할(R)
INSERT - 데이터를 추가하는 역할(C)
UPDATE - 데이터를 수정하는 역할(U)
DELETE - 데이터를 삭제하는 역할(D)
INSERT
테이블에 새로운 데이터를 입력하기 위해 사용하는 데이터 조작어
형식 INSERT INTO 테이블명 (컬럼이름,...) VALUES (컬럼 데이터,....)
DEPT테이블의 구조만 복사한 DEPT01테이블 준비
CREATE TABLE DEP01 AS SELECT * FROM DEPT WHERE 1=0;
INSERT
데이터 추가
INSERT INTO DEPT01
(DEPTNO , DNAME , LOC)
VALUES(10, '영업부','부산'); DEPTNO-10 , DNAME-'영업부' , LOC-'부산'
컬럼명이3개면 값도 무조건3개 아니면 오류남
문자열 숫자열 설정한 값 제대로 넣어주기
컬럼명을 생략한 INSERT 구문
테이블의 기본 컬럼 순서대로 입력된다
INSERT INTO DEPT01
VALUES(10,'영업부','부산')
1. 다음과 같은 구조로 SAM01 테이블을 생성한 후, 데이터를 추가하시오.
CREATE TABLE SAM01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7,2));
INSERT INTO SAM01 VALUES(1000, '사과', '경찰', 1000);
INSERT INTO SAM01 VALUES(1010, '바나나', '간호사', 1500);
INSERT INTO SAM01 VALUES(1020, '오렌지', '의사', 2000);
NULL 값 삽입하는 방법
암시적 방법
컬럼명 리스트에 컬럼을 생략하는 방법.
다른 컬럼은 값을 입력한다 이렇게 생략한 컬럼에는 암시적으로 NULL 값이 할당된다.
명시적 방법
VALUSE 리스트에 명시적으로 NULL을 입력한다.
암시적 NULL 값 삽입
INSERT INTO DEPT01 (DEPTNO , DNAME) VALUES(30,'영업부'); 3개의 컬럼이있을때 명시를안해주면 NULL로 나온다
명시적 NULL 값 삽입
INSERT INTO DEPT01 VALUES(40, '전산부' , NULL); NULL대신 "을 사용할 수 있다
NULL 값 삽입하는 방법
NOT NULL에는 NULL값을 넣을 수 없다(제약조건)
서브 쿼리로 데이터 삽입하기
INSERT INTO 다음에 VALUSE 절을 사용하는 대신에 서브 쿼리를 사용할 수 있다
빈테이블 생성
CREATE TABLE DEPT02 AS SELECT * FROM DEPT WHERE 1=0;
테이블 구조만을 복사해서 내용을 갖지 않는 테이블에 서브 쿼리 로우를 입력해본다
INSERT INTO DEPT02 SELECT * FROM DEPT;
UPDATE (WHERE이 없으면 전부 수정된다 위험하다)
테이블에 저장된 데이터를 수정하기 위해서 사용한다
UPDATE 테이블명
SET 컬럼1=값1 , 컬럼2=값2,...
WHERE 조건;
1.모든 사원의 부서번호를 30으로 수정한다
UPDATE EMP01 SET DEPTNO=30;
2.모든 사원의 급여를 10% 인상시킨다
UPDATE EMP01
SET SAL=SAL*1.1;
3. 모든 사원의 입사일을 오늘로 수정한다.
UPDATE EMP01
SET HIREDATE=SYSDATE;
4.EMP01 테이블의 데이터 중 부서번호가 10번인 사원의 부서번호를 30으로 수정하시오
UPDATE EMP01
SET DEPTNO=30
WHERE DEPTNO=10;
5.EMP01 테이블의 직원들의 급여 중 400이상인 직원만 급여를 10% 인상하시오
UPDATE EMP01
SET SAL=SAL*1.1
WHERE SAL>=400;
6.SAM01 테이블에 저장된 직원 중 급여가 10000이상인 직원들의 급여를 5000씩 삭감하시오
UPDATE SAM01
SET SAL=SAL-5000
WHERE SAL>=10000;
다중 컬럼 값 변경
ex) 한예슬 직원의 부서번호는 10으로, 직급은 ‘과장’으로 한꺼번에 수정하도록 한다.
UPDATE EMP01 SET DEPTNO=10 , JOB='과장' WHERE ENAME='한예슬';
ex) 오지호 직원의 입사일자는 오늘로, 급여를 600으로, 커미션을 200으로 수정하도록 한다.
UPDATE EMP01 SET HIREDATE=SYSDATE, SAL=600,COMM=200 WHERE ENAME='오지호';
ex) 20번 부서의 지역명을 40번 부서의 지역명으로 변경하기 위해서 서브 쿼리문을 사용한다.
UPDATE DEPT01
SET LOC=(SELECT LOC FROM DEPT01
WHERE DEPT=40)
WHERE DEPTNO=20;
7. EMP 테이블의 특정 컬럼과 데이터로 구성된 SAM02 테이블을 생성하시오.
CREATE TABLE SAM02
AS
SELECT ENAME, SAL, HIREDATE, DEPTNO
FROM EMP;
8. SAM02 테이블 데이터 중 ‘인천’에 위치한 부서 소속 직원들의 급여를 100 인상하시오.
UPDATE SAM02
SET SAL=SAL+100
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC='인천');
DELETE
테이블에 저장된 데이터를 삭제하기 위해서 사용한다
DELETE문에 WHERE절을 사용하지 않을 경우 테이블에 있는 모든 행이 삭제되므로 매우 신중하게 명령문을 사용해야 한다
형식 : DELETE FROM 테이블명 WHERE 조건;
DELETE 문으로 DEP01테이블의 모든 행을 삭제한다
형식 : DELETE FROM DEPT01;
9. SAM01 테이블에서 직급이 정해지지 않은 직원을 삭제하시오.
DELETE FROM SAM01
WHERE JOB IS NULL;
10. SAM02 테이블에서 ‘영업부’ 부서 소속 직원들만 삭제하시오.
DELETE FROM SAM02
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME=‘영업부’);
merge 두 테이블을 합친다
====================================================================
chapter11 트랜잭션
트랜잭션
데이터 처리의 한 단위.
오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 과정.
하나의 트랜잭션은 All-or-Nothing방식으로 처리.
데이터 베이스에서 작업의 단위로 트랜잭션이란 개념을 도입한 이유는 데이터의 일관성을
유지하면서 안정적인 데이터를 복구시키기 위해서이다.
트랜잭션 제어를 위한 명령어(Transaction Control Language)에는 다음과 같은 것들이 있다.
COMMIT
SAVEPOINT
ROLLBACK
COMMIT과 ROLLBACK의 장점
데이터 무결성이 보장된다.
영구적인 변경 전에 데이터의 변경 사항을 확인할 수 있다.
논리적으로 연관된 작업을 그룹화 할 수 있다.
COMMIT
Transaction(INSERT, UPDATE, DELETE) 작업 내용을 실제 DB에 저장한다.
이전 데이터가 완전히 UPDATE 된다.
모든 사용자가 변경된 데이터의 결과를 볼 수 있다.
ROLLBACK
Transaction(INSERT, UPDATE, DELETE) 작업 내용을 취소한다.
이전 COMMIT한 곳/이전 트랜잭션이 종료된 지점 까지만 복구된다.
AUTO COMMIT(1)
데이터베이스 사용자가 COMMIT이나 ROLLBACK 명령어를 명시적으로 수행시키지 않더라도 다음과 같은 경우에 자동 커밋 혹은 자동 롤백이 발생한다.
자동 커밋과 자동 롤백 명령이 되는 경우
SQL*PLUS가 정상 종료되었다면 자동으로 COMMIT 되지만, 비정상 종료되었다면 자동으로 ROLLBACK 된다.
DDL과 DCL 명령문이 수행된 경우 자동으로 COMMIT 된다.
정전이 발생했거나 컴퓨터 Down시(전원 끊김) 자동으로 ROLLBACK된다.
AUTO COMMIT(2)
ex) CREATE문에 의한 자동 커밋에 의해서 이전에 수행했던 DML 명령어가 자동으로 커밋됨을 확인한다.
1. 부서 번호가 40번인 부서를 삭제한다.
DELETE * FROM DEPT02
WHERE DEPTNO=40;
2. 삭제 후 부서 테이블(DEPT)과 동일한 내용을 갖는 새로운 테이블(DEPT03)을 생성한다.
CREATE TABLE DEPT03
AS
SELECT * FROM DEPT;
3. DEPT02 테이블의 부서번호가 40번인 부서를 다시 되살리기 위해서 ROLLBACK 명령문을 수행하여도 이미 수행한 CREATE문 때문에 자동으로 커밋이 발생하였으므로 되살릴 수 없다.
SAVEPOINT
SAVEPOINT 명령을 써서 현재의 트랜잭션을 작게 분할할 수 있다.
저장된 SAVEPOINT는 ROLLBACK TO SAVEPOINT 문을 사용하여 표시한 곳까지 ROLLBACK할 수 있다.
여러 개의 SQL문의 실행을 수반하는 트랜잭션의 경우, 사용자가 트랜잭션 중간 단계에서 SAVEPOINT를 지정할 수 있다.
이 SAVEPOINT는 차후 ROLLBACK과 함께 사용해서 현재 트랜잭션 내의 특정 SAVEPOINT까지 ROLLBACK 할 수 있게 된다.
뒤의 예제에서 ROLLBACK TO C1하면 SAVEPOINT도 지워지고 C2라는 SAVEPOINT도 지워진다.
SAVEPOINT
아래 그림을 보면 COMMIT 명령이 내려진 후 다음 COMMIT 명령이 나타날 때까지가 하나의 트랜잭션으로 구성되므로 ②번에서 ④번까지가 하나의 트랜잭션이 된다.
이렇게 트랜잭션을 구성할 때 중간 중간 SAVEPOINT 명령으로 위치를 지정해 놓으면(예를 들어 C) 하나의 트랜잭션 내에서도 ROLLBACK TO C(SAVEPOINT 문을 사용하여 표시한 곳)까지 ROLLBACK할 수 있다.
ex) SAVEPOINT로 특정 위치를 지정하기 위한 사용 형식이다.
SAVEPOINT LABEL_NAME;
ex) SAVEPOINT로 지정해 놓은 특정 위치로 되돌아가기 위한 사용형식이다.
ROLLBACK TO LABEL_NAME;
1. 부서 번호가 40번인 부서를 삭제한 후에 커밋을 수행하여 새롭게 트랜잭션을 시작한다.
DELETE FROM DEPT01 WHERE DEPTNO=40;
COMMIT;
2. 부서 번호가 30번인 부서를 삭제한다.
DELETE FROM DEPT01 WHERE DEPTNO=30;
3. 세이브포인트 C1을 설정한 후, 부서 번호가 20번인 사원을 삭제한다.
SAVEPOINT C1;
DELETE FROM DEPT01 WHERE DEPTNO=20;
4. 세이브포인트 C2를 설정한 후, 부서 번호가 10번인 사원을 삭제한다.
SAVEPOINT C2;
DELETE FROM DEPT01 WHERE DEPTNO=10;
ex) 부서번호가 10번인 사원을 삭제하기 바로 전으로 되돌리기 위해 세이브 포인트를 이용해서 트랜잭션 중간 단계로 돌려본다.
* ROLLBACK 명령을 내리게 된다면 이전 COMMIT 지점으로 되돌아가므로 10, 20, 30번 부서의 삭제가 모두 취소된다. 따라서 원했던 10번 부서의 삭제 이전까지만 되돌리려면 다시 30, 20번의 부서를 삭제해 주서야 한다.
ROLLBACK TO C2;
위 결과 화면을 보면 세이브포인트 C2 지점으로 이동되어 10번 부서의 삭제 이전으로 되돌려진 것을 확인할 수 있다.
ROLLBACK TO C1;
마지막으로 이전 트랜잭션까지 롤백한 후의 결과를 본다.
ROLLBACK;
=======================================================================
chapter 12 제약조건 ★★★
무결성 제약조건
데이터 무결성 제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서
테이블을 생성할때 각 컬럼에 대해서 정의하는 여러가지 규칙을 말한다
NOT NULL - NULL 을 허용하지 않는다
UNIQUE - 중복된 값을 허용하지 않는다 항상 유일한 값을 갖도록 한다
PRIMARY KEY - NULL을 허용하지 않고 중복된 값을 허용하지 않는다
FOREIGN KEY -참조되는 테이블의 컬럼의 값이 존재하면 허용한다
CHECK - 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.
제약 조건 확인
USER_CONSTRAINTS 데이터 딕셔너리는 제약 조건의 정보를 위해서 많은 컬럼으로 구성되어 있지만, 중요한 컬럼 몇 개만 살펴보도록 한다.
OWNER는 제약 조건을 소유한 사용자명을 저장하는 컬럼이다.
CONSTRAINT_NAME은 제약 조건 명을
CONSTRAINT_TYPE은 제약 조건 유형을 저장하는 컬럼이다.
CONSTRAINTS_TYPE P,R,U,C 4가지 값 중에 하나를 갖는다
P - PRIMARY KEY(기본키)
R- FOREIGN KEY(외래키)
U- UNIQUE(중복제거)
C- CHECK , NOT NULL
제약 조건이 5개인데 제약 조건 유형이 4가지인 이유는 NOT NULL과 CHECK 제약 조건을 모두 C로 표현하기 때문이다.
NOT NULL 제약 조건은 컬럼에 NOT NULL 조건을 체크할지 말지를 결정하기 때문에 CHECK를 나타내는 C로 표현한 것으로 이해하면 된다.
제약 조건 유형은 제약 조건의 이니셜로 표현되지만 FOREIGN KEY만은 R로 표현하는 것을 볼 수 있다.
이는 PRIMARY KEY를 참조(REFERENCE) 무결성의 이니셜인 R을 FOREIGN KEY의 제약 조건 유형으로 지정한 것으로 이해하면 된다.
ex) USER_CONSTRAINTS 테이블의 내용을 살펴보자.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;
--NOT NULL
CREATE TABLE EMP04(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
새로운 사원이 입사하여 사원의 정보를 입력하는데 사원 번호와 사원 명이 불분명하여 데이터가 저장되지 않았다면 누구의 직급인지, 누구의 부서번호인지를 모르게 되므로 자료로서의 의미를 갖기 어렵다.
* EMPNO, ENAME, JOB, DEPTNO의 컬럼 구조만을 갖는 EMP03 테이블 생성 후 진행
INSERT INTO EMP04 VALUES(NULL,NULL, '영업부',30);
ex) EMP03 테이블에 사원 번호와 사원명에 데이터를 저장하지 않더라도 해당 로우가 테이블에 추가된다.
테이블을 생성하면서 아무런 제약 조건도 주지 않았기 때문에 DESC 명령어로도 NOT NULL 제약조건이 설정되지 않음을 확인할 수 있다.
DESC EMP03;
DESC 명령어로 NOT NULL 제약 조건이 설정되어 있음을 확인할 수 있다.
DESC EMP04;
--UNIQUE
기존과 동일한 구조를 갖는 EMP05테이블을 만들되 사원 번호를 유일키로 지정한다.
CREATE TABLE EMP05(
EMPNO NUMBER(4) UNIQUE,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
DESC EMP05;
위에서 생성한 EMP05 테이블에 데이터를 추가한다.
INSERT INTO EMP05 VALUES(7499,'김씨','영업부',30);
앞에서 사원번호를 7499번의 자료를 입력하였는데 다시 동일한 사원번호를 입력하면 어떻게 되는지 확인한다.
INSERT INTO EMP05 VALUES(7499,'박씨','인사부',40);
--PRIMARY KEY
UNIQUE 제약 조건과 NOT NULL 제약 조건을 모두 갖고 있는 제약 조건이다.
기존과 동일한 구조를 갖는 EMP06테이블을 만들되 사원 번호를 기본키로 지정한다.
CREATE TABLE EMP06(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
--위에 생성한 EMP06 테이블에 테이터를 추가한다
INSERT INTO EMP06
VALUES(7499, ‘김씨’, ‘영업부’, 30);
--다음은 기본키로 지정된 사원 번호에 동일한 값을 저장해본다
INSERT INTO EMP06
VALUES(7499, ‘박씨’, ‘인사부’, 40);
이번에는 기본키로 지정된 사원번호에 NULL값을 입력해본다.
INSERT INTO EMP06
VALUES(NULL, ‘이씨’, ‘영업부’, 20);
--FOREIGN KEY
조인이나 서브 쿼리를 학습할 때 살펴보았듯이 사원 테이블에 없는 상세 정보는 부서 테이블에서 찾아오는데, 사원 테이블에 저장된 부서 번호가 테이블에 없다면 참조할 때 무결해야 한다는 조건(참조 무결성)에 위배되는 것이 된다.
그러므로 사원 테이블에 부서 번호를 입력할 때 부서 테이블에 존재하는 부서 번호만 입력하도록 하면 참조 무결성이 지켜진다.
이를 위해서 사원 테이블의 부서 번호 컬럼에 외래키 제약 조건을 명시해야 한다.
외래키 제약 조건은 사원 테이블의 부서 번호는 반드시 부서 테이블에 존재하는 부서 번호만 입력하도록 함으로서 사원 테이블이 부서 테이블을 부서 번호로 참조 가능하도록 하는 것을 의미한다.
참조의 무결성은 두 테이블 사이(사원 테이블, 부서 테이블)의 주종 관계에 의해서 결정되는데 주체가 되는 테이블은 부모 테이블이 되고 종속이 되는 테이블은 자식 테이블이 된다.
'사원은 회사 내에 존재하는 부서에 소속되어 있어야 한다.'
사원과 부서의 소속 관계가 위와 같이 표현된다면 부서가 주체(부모 테이블)이고 사원이 종속(자식 테이블)이 된다.
주체 관계가 애매모호한 경우에는 어느 테이블의 데이터가 먼저 정의되어야 하는가를 기준으로 부모 테이블과 자식 테이블을 구분할 수 있다.
먼저 정의되어야 하는 테이블이 부모 테이블이고 나중에 정의되어야 하는 테이블이 자식 테이블이 된다.
회사를 설립하고 어떤 부서를 구성하여 운영할지 정한 후에, 그 부서에서 일할 사원을 뽑아야 소속이란 관계가 성립되므로 부서가 부모 테이블이 되고 사원이 자식 테이블이 된다.
외래 키(FOREIGN KEY) 제약 조건은 자식 테이블인 사원 테이블(EMP)의 부서 번호(DEPTNO) 컬럼에 부모 테이블인 부서 테이블(DEPT)의 부서 번호(DEPTNO)를 부모 키로 설정하는 것이다.
이때 주의할 점은 부모 키가 되기 위한 컬럼은 반드시 부모 테이블의 기본 키(PRIMARY KEY)나 유일키(UNIQUE)로 설정되어 있어야 한다는 점이다.
외래 키 제약 조건이 지정된 사원 테이블에 부서 테이블에 존재하지 않은 50번 부서 번호를 저장해 보도록 한다.
SELECT * FROM DEPT;
다음은 오라클에서 제공해주는 EMP 테이블과 DEPT 테이블의 제약 조건을 살펴보도록 한다.
SELECT TABLE_NAME, CONSTRAINT_TYPE,
CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('DEPT', 'EMP');
R_CONSTRAINT_NAME 컬럼은 FOREIGN KEY인 경우 어떤 PRIMARY KEY를 참조했는지에 대한 정보를 갖는다.
EMP 테이블의 제약조건 FK_DEPTNO의 R_CONSTRAINT_NAME 컬럼 값이 PK_DEPT으로 설정되어 있다.
이는 EMP 테이블의 FK_DEPTNO는 외래 키 제약 조건으로 PK_DEPT 제약조건을 참조하고 있다는 내용이다. PK_DEPT 제약조건은 DEPT 테이블의 기본 키 제약 조건이므로 EMP 테이블은 DEPT 테이블을 참조하고 있는 셈이 된다.
ex) 지금까지 실습에 사용했던 사원 테이블과 유사한 구조의 사원 번호, 사원명, 직급, 부서 번호 4개의 컬럼으로 구성된 테이블을 생성하되 기본 키 제약 조건은 물론 외래키 제약 조건도 설정해본다.
CREATE TABLE EMP07(
EMPNO NUMBER(4) PRIMARY KEY ,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO)
);
현재 EMP07 테이블에 부서 테이블에 존재하지 않는 부서 번호를 갖는 사원 정보를 추가해본다.
INSERT INTO EMP07
VALUES(1200, ‘김씨’, ‘사원’, 50);
--CHECK
CHECK 제약 조건은 입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면 오류 메시지와 함께 명령이 수행되지 못하게 하는 것이다.
조건으로 데이터의 값의 범위나 특정 패턴의 숫자나 문자 값을 설정할 수 있다.
예를 들어 사원 테이블에 급여 컬럼을 생성하되 급여 컬럼 값은 200에서 1000사이의 값만 저장할 수 있도록 하거나 성별을 저장하는 컬럼으로 GENDER 를 정의하고, 이 컬럼에는 남자는 M, 여자는 F 둘 중의 하나만 저장할 수 있도록 제약을 주려면 CHECK 제약조건을 지정해야 한다.
ex) 사원번호, 사원명, 급여, 성별, 부서 번호 등 5개의 컬럼으로 구성된 테이블을 생성하되 기본 키 제약 조건, 외래키 제약 조건은 물로 CHECK 제약 조건도 설정해보자.
CREATE TABLE EMP08(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10) NOT NULL,
SAL NUMBER(7,2),CHECK(SAL BETWEEN 200 AND 1000),
GENDER VARCHAR2(1) CHECK (GENDER IN('M','F')),
DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO)
);
--DEFAULT
디폴트는 아무런 값을 입력하지 않았을 때 디폴트 제약의 갑이 입력된다.
ex) 만약 지역명(LOC)라는 컬럼에 아무런 값도 입력 안했을 때 디폴트의 값인 ‘서울'이 들어가도록 하고 싶을 경우 디폴트 제약 조건을 지정한다.
CREATE TABLE DEPT02(
DEPTNO NUMBER(2)PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) DEFAULT'서울'
);
만약 지역명(LOC)라는 컬럼에 아무런 값도 입력하지 않았을 때 디폴트의 값인 ' 서울'이 들어감을 확인할 수 있다.
INSERT INTO DEPT02(DEPTNO,DNAME) VALUES(10,'영업부');
컬럼 레벨 제약 조건
CREATE TABLE로 테이블을 생성하면서 컬럼을 정의하게 되는데 하나의 컬럼 정의가 다 마무리되기 전에 컬럼 명 다음에 타입을 지정하고 그 뒤에 연이어서 제약 조건을 지정하는 방식이다.
테이블 레벨 제약 조건
컬럼을 모두 정의하고 나서 테이블 정의를 마무리 짓기 전에 따로 생성된 컬럼들에 대한 제약 조건을 한꺼번에 지정하는 것입니다.
--컬럼 레벨 제약 조건
CREATE TABLE EMP10(
EMPNO NUMBER(4)PRIMARY KEY,
ENAME VARCHAR2(10)NOT NULL,
JOB VARCHAR2(9) UNIQUE,
DEPTNO NUMBER(4) REFERENCES DEPT(DEPTNO)
);
--테이블 레벨 제약 조건
다음은 테이블 레벨 정의 방식의 기본 형식이다.
CREATE TABLE table_name
(column_name1 datatype1,
column_name2 datatype2,
. . .
[CONSTRAINT constraint_name] constraint_type (column_name)
)
2. 테이블 레벨 제약 조건
CREATE TABLE EMP11(
EMPNO NUMBER(4),
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(4),
PRIMARY KEY(EMPNO),
UNIQUE(JOB),
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
);
--명시적으로 제약 조건 명을 지정하여 테이블 레벨 방식 제약 조건 지정
CREATE TABLE EMP12(
EMPNO NUMBER(4) CONSTRAINT EMP12_ENAME_NN NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(4),
CONSTRAINT EMP12_EMPNO_PK PRIMARY KEY(EMPNO),
CONSTRAINT EMP12_JOB_UK UNIQUE(JOB),
CONSTRAINT EMP12_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO)
);
ALTER로 제약 조건 추가하기(1)
테이블 구조를 결정하는 DDL을 학습하면서 테이블이 이미 생성된 이후에 테이블의 구조를 변경하기 위한 명령어로 ALTER TABLE을 사용한다는 것을 이미 학습하였다.
제약조건 역시 이미 테이블을 생성하면서 지정해주는 것이었기에 테이블 생성이 끝난 후에 제약 조건을 추가하기 위해서는 ALTER TABLE로 추가해 주어야 한다.
다음은 제약 조건을 추가하기 위한 형식이다.
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
constraint_type (column_name);
ALTER로 제약 조건 추가하기(2)
ex) 지금까지 실습에 사용했던 사원 테이블과 유사한 구조의 사원 번호, 사원명, 직급, 부서 번호 4개의 컬럼으로 구성된 EMP13 테이블을 제약조건을 하나도 설정하지 않은 채 생성해보자.
CREATE TABLE EMP13(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(4)
);
ALTER로 제약 조건 추가하기(3)
이제 이미 생성이 완료된 EMP13 테이블에 2가지 제약조건을 설정해 보도록한다. 첫 번째는 EMPNO 컬럼에 기본키를 설정하고 두 번째에는 DEPTNO 컬럼에 외래키를 설정한다.
ALTER TABLE EMP13
ADD CONSTRAINT EMP13_EMPNO_PK PRIMARY KEY(EMPNO);
ALTER TABLE EMP13
ADD CONSTRAINT EMP13_DEPTNO_FK
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
MODIFY로 제약 조건 추가하기(1)
NOT NULL 제약 조건을 이미 존재하는 테이블에 추가해 보도록한다.
이미 존재하는 테이블에 무결성 제약 조건을 추가로 생성하기 위해서 ALTER TABLE . . . ADD . . . 명령문을 사용하였다.
하지만 NOT NULL 제약 조건은 ADD 대신 MODIFY 명령문을 사용하므로 사용에 주의해야한다.
이는 'NULL을 허용하는 상태'에서 'NULL을 허용하지 않는 상태'로 변경하겠다는 의미로 이해하면 된다.
MODIFY로 제약 조건 추가하기(2)
ex) 이미 존재하는 테이블에 NOT NULL 제약 조건을 추가한다.
ALTER TABLE EMP10
MODIFY ENAME CONSTRAINT EMP01_ENAME_NN NOT NULL;
제약 조건 제거하기(1)
제약 조건을 제거하기 위해서 DROP CONSTRAINT 다음에 제거하고자 하는 제약 조건 명을 명시해야한다.
ALTER TABLE table_name
DROP [CONSTRAINT constraint_name];
제약 조건 제거하기(2)
ex) 사원 테이블에 지정한 제약 조건들을 제거해 보도록 합시다.
1. 기본 키 제약 조건을 제거한다.
ALTER TABLE EMP12
DROP CONSTRAINT EMP12_EMPNO_PK;
2. 사원명에 NULL이 저장될 수 있도록 NOT NULL 제약 조건을 제거해 보도록 한다.
ALTER TABLE EMP12
DROP CONSTRAINT EMP12_ENAME_NN;
제약 조건 비활성화(1)
제약 조건이 설정되면 항상 그 규칙에 따라 데이터 무결성이 보장된다.
특별한 업무를 수행하는 과정에서 이러한 제약 조건 때문에 작업이 진행되지 못하는 경우가 생긴다.
그렇다고 제약 조건을 삭제해 버리면 데이터 무결성을 보장받지 못하게 된다.
그렇기 때문에 오라클에서는 제약 조건을 비활성화 시킴으로서 제약 조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법을 제공해준다.
이렇게 비활성화 된 제약 조건은 원하는 작업을 한 후에는 다시 활성화 상태로 만들어 주어야 한다.
제약 조건을 비활성화, 활성화하는 방법을 살펴보도록 하자.
제약 조건 비활성화(2)
ex) 실습을 위해서 부서 테이블을 만든다. 그런 후에 부서 테이블을 부모 테이블로 하는 사원 테이블을 작성한다. 그러기 위해서는 부서 테이블의 부서 번호가 기본 키로 설정되어 있고, 사원 테이블의 부서 번호가 부서 테이블의 부서 번호를 참조할 수 있도록 외래 키를 설정해야 한다.
1. 부서 테이블 생성 후 DEPT 테이블의 내용을 복사 해온다.
CREATE TABLE DEPT01(
DEPTNO NUMBER(2) CONSTRAINT DEPT01_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
); 두가지 방법
INSERT INTO DEPT01(DEPTNO, DNAME, LOC)
SELECT DEPTNO, DNAME, LOC FROM DEPT;
제약 조건 비활성화(3)
2. 부서 테이블을 만들었으므로 이제 부서 테이블을 부모 테이블로 하는 사원 테이블을 작성하기 위해서 사원 테이블의 부서 번호가 부서 테이블의 부서 번호를 참조할 수 있도록 외래 키를 설정한다.
CREATE TABLE EMP01(
EMPNO NUMBER(4)
CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY ,
ENAME VARCHAR2(10)
CONSTRAINT EMP01_ENAME_NN NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(4)
CONSTRAINT EMP01_DEPTNO_FK REFERENCES DEPT01(DEPTNO)
);
제약 조건 비활성화(4)
3. 사원 테이블로서 사원의 정보를 추가할 때 부서 테이블을 참조하므로 부서 테이블에 존재하는 부서 번호를 입력한다.
INSERT INTO EMP01 VALUES(7499, '김씨', '영업부', 10);
INSERT INTO EMP01 VALUES(7369, '박씨', '인사부', 20);
4. DEPT01 테이블에서 10번 부서를 ‘김씨’란 사람이 참조하고 있는 상태에서 삭제해 봅시다.
DELETE FROM DEPT01 WHERE DEPTNO=10;
제약 조건 비활성화(5)
자식 테이블인 사원 테이블(EMP01)은 부모 테이블인 부서 테이블(DEPT01)에 기본 키인 부서 번호를 참조하고 있을 때.
부서 테이블의 10번 부서는 사원 테이블에 근무하는 10번 사원이 존재하기 때문에 삭제할 수 없다.
부모 테이블(DEPT01)의 부서 번호 10번이 삭제되면 자식 테이블(EMP01)에서 자신이 참조하는 부모를 잃어버리게 되므로 삭제할 수 없는 것이다.
부서 번호가 10인 자료가 삭제되도록 하기 위해서는 아래 방법이 있다.
1)사원 테이블(EMP01)의 10번 부서에서 근무하는 사원을 삭제한 후 부서 테이블(DEPT01)에서 10번 부서를 삭제한다.
2) 참조 무결성 때문에 삭제가 불가능하므로 EMP01 테이블의 외래키
제약 조건을 제거한 후에 10번 부서를 삭제한다.
제약 조건 비활성화(6)
테이블에서 제약 조건을 삭제하지 않고 일시적으로 적용시키지 않도록 하는 방법으로 제약 조건을 비활성화하는 방법이 있다. 제약조건을 비활성화하는 방법을 살펴보도록 하자.
DISABLE CONSTRAINT : 제약 조건의 일시 비활성화
ENABLE CONSTRAINT : 비활성화된 제약 조건을 다시 활성화
비활성화는 DISABLE 예약어를 사용하여 다음과 같이 지정한다.
ALTER TABLE table_name
DISABLE [CONSTRAINT constraint_name];
제약 조건 비활성화(8)
ex) EMP01 테이블에 지정한 외래키 제약 조건을 비활성화한 후에 DEPT01 테이블에서 10번 부서를 삭제해 본다.
1. EMP01 테이블에 지정한 외래키 제약 조건을 비활성화 시킨다.
ALTER TABLE EMP01
DISABLE CONSTRAINT EMP01_DEPTNO_FK;
제약 조건의 상태를 확인하기 위해서 USER_CONSTRAINTS 데이터 딕셔너리의 STATUS 컬럼값을 살펴보면 EMP01_DEPTNO_FK 제약 조건에 대해서 STATUS 컬럼값이 DISABLED로 지정되어 있음을 확인할 수 있다.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,
TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP01';
제약 조건 비활성화(9)
2. 이제 EMP01 테이블에 지정한 외래키 제약 조건을 비활성화하였기 때문에 DEPT01 테이블에서 10번 부서를 삭제할 수 있게 되었다.
DELETE FROM DEPT01
WHERE DEPTNO=10;
제약 조건 활성화(1)
제약 조건을 비활성화 해 보았으므로 이번에는 제약 조건을 활성화 해보도록 하자.
활성화는 ENABLE 예약어를 사용하여 다음과 같이 지정한다.
ALTER TABLE table_name
ENABLE [CONSTRAINT constraint_name];
제약 조건 활성화(2)
ex) EMP01 테이블에 지정한 제약 조건 중에서 외래키 제약 조건을 비활성화 했습니다. 비활성화된 제약 조건은 다시 활성화해야 한다.
1. DISABLE CONSTRAINT 문에 의해 비활성화된 제약 조건을 되살리려면 다음과 같이 ENABLE 을 사용해야 한다
ALTER TABLE EMP01
ENABLE CONSTRAINT EMP01_DEPTNO_FK;
제약 조건 활성화(3)
2. 그러므로 외래키 제약 조건을 활성화시키기 전에 먼저 삭제된 부서 테이블의 10번 부서를 새로 입력해 놓아야 한다.
INSERT INTO DEPT01 VALUES(10, '경리부', '서울');
3. 10번 부서를 새로 입력해 놓았으므로 이제 외래키 제약 조건을 활성화한다.
ALTER TABLE EMP01
ENABLE CONSTRAINT EMP01_DEPTNO_FK;
CASECADE
CASECADE 옵션은 부모 테이블과 자식 테이블 간의 참조 설정이 되어있을때
부모테이블의 제약 조건을 비활성화하면 이를 참조하고 있는 자식 테이블의
제약 조건까지 같이 비활성화시켜 주는 옵션이다
또한 제약 조건의 비활성화뿐만 아니라 제약 조건의 삭제에도 활용되며
역시 같은 이치로 부모 테이블의 제약 조건을 삭제하면 이를 참조하고 있는
자식테이블의 제약조건도 같이 삭제된다
CASCADE(2)
ex) 부서 테이블(DEPT01)의 기본 키 제약 조건을 비활성화해 보도록 하자.
1. 부서 테이블(DEPT01)의 기본 키 제약 조건을 "DISABLE PRIMARY KEY"로 비 활성화하려고 시도한다.
ALTER TABLE DEPT01
DISABLE PRIMARY KEY;
CASCADE(3)
부모 테이블(부서)의 기본 키에 대한 제약조건을 비활성화하고자 하는 것인데 자식 테이블(사원)에서 이를 외래 키 제약조건으로 지정한 컬럼이라면 절대 비활성화할 수 없다.
만일 비활성화될 수 있다고 가정하면 기본 키가 더 이상 아닌 상태로 일반 컬럼을 자식 테이블이 외래 키 제약조건으로 지정하고 있는 아이러니 한 상태가 되기 때문이다.
그렇기 때문에 부모 테이블(부서)의 기본 키에 대한 제약조건을 비활성화하려면 자식 테이블(사원)의 외래 키에 대한 제약조건을 비활성화하는 작업이 선행되어야 한다.
두 테이블 사이에 아무런 관련이 없어야 만 즉, 부서 테이블이 더 이상 부모 테이블로서의 역할을 하지 않고 있어야만 기본 키 제약 조건을 비활성화 시킬 수 있다.
CASCADE(4)
부모 테이블(부서)의 기본 키에 대한 제약조건을 비활성화하기 위한 작업을 순서대로 정리해보자.
1) 부모 테이블의 기본 키를 참조하는 자식 테이블의 외래 키에 대한
제약 조건을 비활성화해야 한다.
2) 부모 테이블의 기본 키에 대한 제약 조건을 비활성화해야 한다.
위 순서대로 제약조건을 여러 번에 걸쳐 비활성화 시키기는 번거로움을 없애주는 것이 CASCADE 옵션이다.
CASCADE 옵션을 지정하여 기본 키 제약 조건을 비활성화하면 이를 참조하는 외래 키 제약 조건도 연속적으로 비활성화되기 때문에 한 번만 비활성화 해 주면 된다.
CASCADE(5)
ex) CASCADE 옵션을 지정하여 기본 키 제약 조건을 비활성화하면 이를 참조하는 외래 키 제약 조건도 연속적으로 비활성화된다.
1. 부서 테이블(DEPT01)의 기본 키 제약 조건을 CASCADE 옵션을 지정하여 비활성화한다.
ALTER TABLE DEPT01
DISABLE PRIMARY KEY CASCADE;
2. 데이터 딕셔너리 USER_CONSTRAINTS를 살펴본다.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,
TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT01', 'EMP01');
CASCADE(7)
ex) CASCADE 옵션을 지정하여 기본 키 제약 조건을 제거하면 이를 참조하는 외래 키 제약 조건도 연속적으로 제거된다.
1. 이번에는 부서 테이블(DEPT01)의 기본키 제약 조건을 삭제해보도록 한다.
ALTER TABLE DEPT01
DROP PRIMARY KEY;
CASCADE(8)
2. CASCADE 옵션을 지정하여 기본 키 제약 조건을 삭제하게 되면 이를 참조하는 외래 키 제약 조건도 연속적으로 삭제된다.
ALTER TABLE DEPT01
DROP PRIMARY KEY CASCADE;
데이터 딕셔너리 USER_CONSTRAINTS를 살펴본다.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,
TABLE_NAME, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('DEPT01', 'EMP01');
끝
문제2.
--1. 회원 시퀀스를 생성하시오. [ user_seq ]
CREATE SEQUENCE USER_SEQ START WITH 1 INCREMENT BY 1;
--증가하는값.nextval
SELECT USER_SEQ.nextval from dual;
--2. 회원 테이블을 생성하시오. [ userTable ]
--* 필드
--1. 회원번호(숫자) userNo PK 시퀀스
--2. 회원ID(문자-20) userId 필수 중복 안 됨
--3. 회원비밀번호(문자-20) userPw 필수
--4. 회원연락처(문자-20) userTel 중복 안 됨
--5. 회원멤버쉽(문자-10) userMembership gold / silver / bronze 중 하나
--6. 회원이메일(문자-20) userEmail 중복 안 됨
--7. 회원포인트(숫자) userPoint 기본 1000점
--8. 회원가입일(날짜) joinDate 기본 현재 날짜
CREATE TABLE userTable(
userNo NUMBER PRIMARY KEY,
userId VARCHAR2(20) NOT NULL UNIQUE,
userPw VARCHAR2(20) NOT NULL,
userTel VARCHAR2(20) UNIQUE,
userMembership VARCHAR2(10) CHECK(userMembership IN ('gold', 'silver', 'bronze')),
userEmail VARCHAR2(20) UNIQUE,
userPoint NUMBER DEFAULT 1000,
joinDate DATE DEFAULT SYSDATE
);
--3. 적절한 데이터 5개 이상 삽입하시오.
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id01', '1111', '010-1111-1111', 'gold', 'id01@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id02', '1111', '010-1111-2222', 'silver', 'id02@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id03', '1111', '010-1111-3333', 'bronze', 'id03@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id04', '1111', '010-1111-4444', 'gold', 'id04@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id05', '1111', '010-1111-5555', 'silver', 'id05@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id06', '1111', '010-1111-6666', 'bronze', 'id06@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id07', '1111', '010-1111-7777', 'gold', 'id07@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id08', '1111', '010-1111-8888', 'silver', 'id08@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id09', '1111', '010-1111-9999', 'bronze', 'id09@aa.com');
insert into userTable (userNo, userId, userPw, userTel, userMembership, userEmail) values (user_seq.nextval, 'id10', '1111', '010-1111-0000', 'gold', 'id10@aa.com');
commit;
SELECT * FROM USERTABLE;
--4. 다음 질의문을 처리하시오.
--1) userNo 이 5 이상인 회원을 검색하시오.
SELECT * FROM userTable WHERE USERNO>=5;
--2) userNo 이 3 ~ 7 사이인 회원을 검색하시오.
SELECT * FROM userTable WHERE USERNO>=3 AND USERNO<=7;
SELECT * FROM userTable WHERE USERNO BETWEEN 3 AND 7;
--3) userTel 마지막 4자리가 '5555' 인 회원을 검색하시오.
SELECT * FROM userTable WHERE USERTEL LIKE '%5555';
--4) userMembership 이 'gold' 인 회원의 userPoint 평균을 출력하시오.
SELECT AVG(userPoint) FROM userTable WHERE userMembershiP='gold';
--5) userId 가 'id03' 인 회원의 joinDate 를 '19/10/28/'로 수정하시오.
UPDATE userTable
SET joinDate= '19/10/28'
WHERE userId= 'id03';
--6) userMembership 이 'gold' 인 회원들의 userPoint 를 모두 3배 증가시키시오.
UPDATE userTable SET userPoint= userPoint*3 WHERE userMembership='gold';
--7) userId 가 'id08' 인 회원의 userPw 를 '2222'로 수정하시오.
UPDATE userTable SET userPw='2222' where userId='id08';
--8) userMembership 이 'bronze' 인 회원들 중에서 userPoint 가 1000 이상인 회원들의 userMembership 을 'silver'로 수정하시오.
UPDATE userTable SET userMembership='silver'WHERE userMembership='bronze'AND userPoint>=1000;
--9) userNo 가 5 인 회원을 삭제하시오.
DELETE FROM userTable WHERE userNo=5;
---------------------------------------------------------------------------------------------------------------------------------------
문제3.
--1. 책 테이블을 생성하시오. [ book ]
--* 필드
--1. 책번호(숫자) bookid PK
--2. 책이름(문자-50) bookname
--3. 출판사(문자-50) publisher
--4. 가격(숫자) price
CREATE TABLE book(
bookid NUMBER PRIMARY KEY,
bookname VARCHAR2(50),
publisher VARCHAR2(50),
price NUMBER
);
SELECT * FROM book;
--2. 고객 테이블을 생성하시오 [ customer ]
--* 필드
--1. 고객번호(숫자) customerid PK
--2. 이름(문자-20) name
--3. 주소(문자-50) address
--4. 전화번호(문자-20) phone
CREATE TABLE customer (
customerid NUMBER PRIMARY KEY,
name VARCHAR2(20),
address VARCHAR2(50),
phone VARCHAR2(20)
);
SELECT * FROM customer;
--3. 주문 테이블을 생성하시오 [ orders ]
--* 필드
--1. 주문번호(숫자) orderid PK
--2. 고객번호(숫자) customerid FK
--3. 책번호(숫자) bookid FK
--4. 판매가격(숫자) saleprice
--5. 주문일자(날짜) orderdate
CREATE TABLE orders (
orderid NUMBER PRIMARY KEY,
customerid NUMBER REFERENCES customer(customerid),
bookid NUMBER REFERENCES book,
saleprice NUMBER NOT NULL,
orderdate DATE
);
SELECT * FROM orders;
insert into book values (1, '축구의역사', '굿스포츠', 7000);
insert into book values (2, '축구아는여자', '나무수', 13000);
insert into book values (3, '축구의이해', '대한미디어', 22000);
insert into book values (4, '골프바이블', '대한미디어', 35000);
insert into book values (5, '피겨교본', '굿스포츠', 6000);
insert into book values (6, '역도단계별기술', '굿스포츠', 6000);
insert into book values (7, '야구의추억', '이상미디어', 20000);
insert into book values (8, '야구를부탁해', '이상미디어', 13000);
insert into book values (9, '올림픽이야기', '삼성당', 7500);
insert into book values (10, '올림픽챔피언', '피어슨', 13000);
insert into customer values (1, '박지성', '영국 맨체스터', '010-0000-0000');
insert into customer values (2, '김연아', '대한민국 서울', '010-1111-1111');
insert into customer values (3, '장미란', '대한민국 강원도', '010-2222-2222');
insert into customer values (4, '추신수', '미국 텍사스', '010-4444-4444');
insert into customer values (5, '박세리', '대한민국 대전', '010-5555-5555');
insert into orders values (1, 1, 1, 6000, '2014-07-01');
insert into orders values (2, 1, 3, 21000, '2014-07-03');
insert into orders values (3, 2, 5, 8000, '2014-07-03');
insert into orders values (4, 3, 6, 6000, '2014-07-04');
insert into orders values (5, 4, 7, 20000, '2014-07-07');
insert into orders values (6, 1, 2, 12000, '2014-07-07');
insert into orders values (7, 4, 8, 13000, '2014-07-07');
insert into orders values (8, 3, 10, 12000, '2014-07-08');
insert into orders values (9, 2, 10, 7000, '2014-07-09');
insert into orders values (10, 3, 8, 13000, '2014-07-10');
commit;
-----------------------------------------------------------------
--4. bookid가 1인 책의 이름을 출력하시오.
SELECT * FROM book WHERE bookid=1;
--5. 가격이 20000원 이상인 책의 이름을 모두 출력하시오.
SELECT * FROM book WHERE price>=20000;
--6. 출판사 중복을 제거하여 출력하시오.
SELECT DISTINCT PUBLISHER FROM book;
--7. 총 판매된 책의 가격을 구하고, 컬럼명을 '총판매액'으로 출력하시오.
select sum(saleprice) as "총판매액" from orders;
--8. 박지성의 총 구매액을 구하시오.
select sum(orders.saleprice)
from customer LEFT join orders
ON customer.customerid = orders.customerid
where customer.name = '박지성';
SELECT sum(saleprice)
from orders
where customerid = (SELECT customerid
from customer
where name='박지성');
--9. 박지성의 구매한 도서 수를 구하시오.
SELECT COUNT(saleprice)
from orders
where customerid = (SELECT customerid
from customer
where name='박지성');
--10. 2014년 7월 4일부터 7월 7일 사이에 주문 받은 주문 정보를 출력하시오. (+@ 기간 제외 정보 출력)
SELECT * FROM orders where orderdate>='2014-07-04' and orderdate<='2014-07-07';
--11. 주문한 적이 없는 고객의 이름을 출력하시오.
select c.name from customer c LEFT JOIN orders o on c.customerid=o.customerid
where o.orderid is null;
--12. 박지성이 구매한 도서의 출판사 수(중복없이)를 출력하시오.
select count(distinct publisher)
from book
where bookid in (select bookid
from orders
where CUSTOMERID = (select CUSTOMERID
from customer
where name = '박지성'));
SELECT count(DISTINCT b.publisher)
from customer c inner join orders o
on c.customerid=o.customerid inner join book b
on o.bookid=b.bookid
where c.name='박지성';
--13. 고객의 모든 이름과 고객별 총 구매액을 함께 출력하시오.
select c.name, sum(o.saleprice) from orders o right outer join customer c on o.customerid = c.customerid group by c.name;
--13-1. 구매 내역이 있는 고객의 이름과 고객별 총 구매액을 함께 출력하시오.
select c.name, NVL(sum(o.saleprice), 0) from orders o right outer join customer c on o.customerid = c.customerid group by c.name having NVL(sum(o.saleprice), 0) > 0;
--13-2. 고객의 모든 이름과 고객별 총 구매액을 함께 출력하되 구매 내역이 없는 고객의 구매 금액은 0으로 출력하시오.
select c.name, NVL(sum(o.saleprice), 0) from orders o right outer join customer c on o.customerid = c.customerid group by c.name;
==========================================================================================
chapter13 뷰
뷰(View)
물리적인 테이블을 근거한 논리적인 가상 테이블
가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고, 테이블이란 단어는 실질적으로 데이터를 저장하고 있지 않더라도 사용자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 가상 테이블이라 불린다.
기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문이다.
사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 된다.
뷰의 기본 테이블
뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.
뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데 이 테이블을 기본 테이블이라고 한다.
우선 시스템에서 제공하는 DEPT 테이블과 EMP 테이블의 내용이 변경되는 것을 막기 위해서 테이블의 내용을 복사한 새로운 테이블을 생성한 후에 이를 기본 테이블로 사용하자.
테이블의 내용을 복사할 때 제약조건은 복사되지 않는다.
ex) 뷰의 기본 테이블을 생성한다.
1. DEPT_COPY를 DEPT 테이블의 복사본으로 생성한다.
CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPT;
2. EMP 테이블의 복사본으로 EMP_COPY를 생성한다.
CREATE TABLE EMP_COPY
AS
SELECT * FROM EMP;
뷰 정의하기
뷰를 생성하여 자주 사용되는 SELECT 문을 간단하게 접근하는 방법을 학습해보자. 다음은 뷰를 생성하기 위한 기본 형식이다.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
[(alias, alias, alias, ...)]
AS subquery (서브쿼리)
[WITH CHECK OPTION]
[WITH READ ONLY];
테이블을 생성하기 위해서 CREATE TABLE 로 시작하지만, 뷰를 생성하기 위해서는 CREATE VIEW로 시작합니다. AS 다음은 마치 서브 쿼리문과 유사하다.
subquery에는 우리가 지금까지 사용하였던 SELECT 문을 기술하면 된다.
CREATE OR REPLACE VIEW
뷰를 만들 때 CREATE OR REPLACE VIEW 대신 그냥 CREATE VIEW만 사용해도 된다.
그러나 그냥 CREATE VIEW를 통해 만들어진 뷰의 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 되는 반면, CREATE OR REPLACE VIEW는 새로운 뷰를 만들 수 있을 뿐만 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있다.
그래서 대부분 뷰를 만들 때는 CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용하는 편이다.
FORCE
FORCE를 사용하면 기본 테이블의 존재 여부에 상관없이 뷰를 생성한다.
WITH CHECK OPTION
WITH CHECK OPTION을 사용하면, 해당 뷰를 통해서 볼 수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능하다.
WITH READ ONLY
WITH READ ONLY를 사용하면 해당 뷰를 통해서는 SELECT만 가능하며 INSERT/UPDATE/DELETE를 할 수 없게 된다.
만약 이것을 생략한다면, 뷰를 사용하여 추가, 수정, 삭제 (INSERT/UPDATE/DELETE)가 모두 가능하다.
뷰에서 사용가능하다
뷰를 만들기 전에 어떤 경우에 뷰를 사용하게 되는지 다음 예를 통해서 뷰가 필요한 이유를 설명해 보도록 하겠다.
만일 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 자주 검색
한다고 한다면 다음과 같은 SELECT문을 여러 번 입력해야한다
SELECT EMPNO, ENAME, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;
자주 사용되는 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를
출력하기 위한 SELECT문을 하나의 뷰로 정의해보자
CREATE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;
뷰는 테이블에 접근(SELECT)한 것과 동일한 방법으로 결과를 얻을 수 있다.
SELECT * FROM EMP_VIEW30;
1. 기본 테이블은 EMP_COPY로 하여 20번 부서에 소속된 사원들의 사번과 이름과 부서번호와 상관의 사번을 출력하기 위한 SELECT문을 EMP_VIEW20 이란 이름의 뷰로 정의하시오.
CREATE VIEW EMP_VIEW20
AS
SELECT EMPNO, ENAME, DEPTNO, MGR
FROM EMP_COPY
WHERE DEPTNO=20;
ex) USER_VIEWS에서 테이블 이름과 텍스트만 출력해보자
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;
뷰의 구조
1. 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회한다.
2 기본 테이블에 대한 뷰의 접근 권한을 살핀다.
3. 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환한다.
4. 기본 테이블에 대한 질의를 통해 데이터를 검색한다.
5. 검색된 결과를 출력한다.
ex) 기본 테이블을 가져다가 쿼리문을 수행한다는 것을 증명하기 위해서 간단한 예를 살펴보자.
1. EMP_VIEW30 뷰에 행을 하나 추가하는 문장이다.
INSERT INTO EMP_VIEW30
VALUES(1111, ‘AAAA’, 30);
3. 뷰 뿐만 아니라 기본 테이블의 내용을 출력해 보면 INSERT 문에 의해서 뷰에 추가한 행이 테이블에도 존재함을 확인할 수 있다.
SELECT * FROM EMP_COPY;
뷰의 구조
INSERT 문에 뷰(EMP_VIEW30)를 사용하였지만, 뷰는 쿼리문에 대한 이름일 뿐이기 때문에 새로운 행은 기본 테이블(EMP_COPY)에 실질적으로 추가되는 것임을 알 수 있다. 뷰(EMP_VIEW30)의 내용을 확인하기 위해 SELECT문을 수행하면 변경된 기본 테이블(EMP_COPY)의 내용에서 일부를 서브 쿼리한 결과를 보여준다.
뷰는 실질적인 데이터를 저장한 기본 테이블을 볼 수 있도록 한 투명한 창이다, 기본 테이블의 모양이 바뀐 것이고 그 바뀐 내용을 뷰라는 창을 통해서 볼 뿐이다. 뷰에 INSERT 뿐만 아니라 UPDATE, DELETE 모두 사용할 수 있는데, UPDATE, DELETE 쿼리문 역시 뷰의 텍스트에 저장되어 있는 기본 테이블이 변경하는 것이다.
그렇기에 뷰가 물리적인 테이블을 근거로 한 논리적인 가상 테이블이란 뜻이다.
뷰를 사용하는 이유
예를 들어 사원 테이블에 개인 적인 정보인 급여와 커미션은 부서에 따라 접근을 제한해야 한다. 급여나 커미션 모두에 대해서 인사과에서는 조회할 수 없도록 하고 경리과에서는 이 모두가 조회될 수 있도록 하지만 영업부서에서는 경쟁심을 유발하기 위해서 다른 사원의 커미션을 조회할 수 있도록 해야 한다.
뷰의종류
뷰는 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰(Simple View)와 복합 뷰(Complex View)로 나뉜다.
단순뷰 복합 뷰
하나의 테이블로생성 여러개의 테이블로 생성
그룹 함수의 사용이 불가능 그룹 함수의 사용이 가능
DISTINCT 사용이 불가능 DISTINCT 사용이 가능
DML 사용 가능 DML 사용 불가능
단순 뷰에 대한 데이터 조작
ex) 단순 뷰에 대해서 DML 즉,INSERT/UPDATE/DELETE 문을 사용할 수 있음을 확인한다.
1. EMP_VIEW30 뷰에 데이터를 추가해본다.
INSERT INTO EMP_VIEW30
VALUES(8000, ‘김천사’, 30);
SELECT * FROM EMP_VIEW30;
2. 단순 뷰를 대상으로 실행한 DML 명령문의 치리 결과는 뷰를 정의할 때 사용한 기본 테이블에 적용된다.
SELECT * FROM EMP_COPY;
단순 뷰의 컬럼에 별칭 부여하기
ex) 기본 테이블(EMP_COPY)의 컬럼 명을 상속받지 않고 한글화 하여 컬럼 명이 사원번호, 사원명, 급여, 부서번호로 구성되도록 한다.
1. EMP_VIEW 뷰를 생성한다.
CREATE OR REPLACE
VIEW EMP_VIEW(사원번호, 사원명, 급여, 부서번호)
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP_COPY;
2. EMP_VIEW 는 전체 사원에 대해서 특정 컬럼만 보여주도록 작성하였다. 다음과 같이 EMP_VIEW 를 SELECT 하면서 WHERE 절을 추가하여 30번 부서 소속 사원들의 정보만 볼 수 있다.
SELECT * FROM EMP_VIEW WHERE 부서번호=30;
그룹 함수를 사용한 단순 뷰
1. 부서별 급여 총액과 평균을 구하기 위한 뷰를 생성해보자.
CREATE VIEW VIEW_SAL
AS
SELECT DEPTNO, SUM(SAL) AS "SalSum", AVG(SAL) AS "SalAvg"
FROM EMP_COPY
GROUP BY DEPTNO;
단순 뷰
단순 뷰에 대해서 DML 명령어를 사용하여 조작이 가능하다고 하였다.
하지만, 다음과 같은 몇 가지의 경우에는 조작이 불가능하다.
복합 뷰
뷰를 사용하는 이유 중의 하나가 복잡하고 자주 사용하는 질의를 보다 쉽고 간단하게 사용하기 위해서라고 했다. 이를 살펴보기 위해서 사원 테이블과 부서 테이블을 자주 조인한다고 하자.
사원 테이블과 부서 테이블을 조인하기 위해서는 다음과 같이 복잡한 SELECT 문을 매번 작성해야 한다.
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
ORDER BY EMPNO DESC;
복합 뷰 만들기
ex) 사원 테이블과 부서 테이블을 조인하기 위해서 복합 뷰를 생성해보자.
1. 다음은 사번, 이름, 급여, 부서번호, 부서명, 지역명을 출력하기 위한 복합 뷰이다.
CREATE VIEW EMP_VIEW_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO DESC;
2. 뷰를 생성한 후, 이를 활용하면 복잡한 질의를 쉽게 처리할 수 있다.
SELECT * FROM EMP_VIEW_DEPT;
2. 각 부서별 최대 급여와 최소 급여를 출력하는 뷰를 SAL_VIEW 란 이름으로 작성하시오.
CREATE VIEW SAL_VIEW
AS
SELECT D.DNAME, MAX(E.SAL) "MAX_SAL", MIN(E.SAL) "MIN_SAL"
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY D.DNAME;
뷰 삭제와 옵션
뷰 정의하는 방법을 살펴보면서 뷰를 생성하기 위한 사용되는 옵션에 대해서 대략적으로 설명을 했다.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
[(alias, alias, alias, ...)]
AS subquery (서브쿼리)
[WITH CHECK OPTION]
[WITH READ ONLY];
OR REPLACE
CREATE OR REPLACE VIEW 를 사용하면 존재하지 않은 뷰이면 새로운 뷰를 생성하고 기존에 존재하는 뷰이면 그 내용을 변경한다.
이전에 작성한 EMP_VIEW30 뷰는 “EMPNO, ENAME, DEPTNO” 3 개의 컬럼을 출력하는 형태였는데 급여와 커미션 컬럼을 추가로 출력할 수 있도록 하기 위해서 뷰의 구조를 변경한다.
CREATE OR REPLACE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;
FORCE
뷰를 생성하는 경우에 일반적으로 기본 테이블이 존재한다는 가정 하에서 쿼리문을 작성한다.
극히 드물기는 하지만, 기본 테이블이 존재하지 않는 경우에도 뷰를 생성해야 할 경우가 있다. 이럴 경우에 사용하는 것이 FORCE 옵션이다.
FORCE 옵션과 반대로 동작하는 것으로서 NOFORCE 옵션이 있다.
NOFORCE 옵션은 반드시 기본 테이블이 존재해야 할 경우에만 뷰가 생성된다.
지금까지 뷰를 생성하면서 FORCE/NOFORCE 옵션을 지정하지 않았다. 이렇게 특별한 설정이 없으면 디폴트로 NOFORCE 옵션이 지정된 것이므로 간주한다.
ex) FORCE/NOFORCE 옵션이 어떤 역할을 하는지 살펴보기 위해서 존재하지 않는 테이블인 EMPLOYEES를 사용하여 뷰를 생성하도록 한다.
1. 존재하지 않는 EMPLOYEES를 기본 테이블로 하여 뷰를 생성하게 되면 오류가 발생한다.
CREATE OR REPLACE VIEW EMPLOYEES_VIEW
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMPLOYEES
WHERE DEPTNO=30;
2. 기본 테이블이 존재하기 않는 경우에도 뷰를 생성하기 위해서 FORCE 옵션이 적용한다.
CREATE OR REPLACE FORCE VIEW NOTABLE_VIEW
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMPLOYEES
WHERE DEPTNO=30;
WITH CHECK OPTION
뷰를 정의하는 서브 쿼리문에 WHERE 절을 추가하여 기본 테이블 중 특정 조건에 만족하는 로우(행)만으로 구성된 뷰를 생성할 수 있다.
WITH CHECK 옵션은 특정 조건의 컬럼값을 변경 못하게 하는 옵션이다.
다음은 30번 부서 소속 사원들의 정보만으로 구성된 뷰이다.
CREATE OR REPLACE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;
SELECT * FROM EMP_VIEW30;
WITH CHECK OPTION
뷰를 마치 테이블처럼 SELECT문으로 조회할 수 있음은 물론이고 DML 문으로 내용을 조작할 수 있음을 이미 학습했으므로 UPDATE 문으로 30번 부서에 소속된 사원 중에 급여가 400 이상인 사원은 20번 부서로 이동시켜 보자
UPDATE EMP_VIEW30 SET DEPT=20
WHERE SAL>=400;
이러한 결과는 뷰를 공유해서 사용할 경우 혼돈을 초래할 수 있으므로 미연에 방지해야 한다. 다행히 오라클에서는 WITH CHECK OPTION 으로 이러한 혼돈을 막을 수 있도록 한다.
CREATE OR REPLACE VIEW VIEW_CHK30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH CHECK OPTION;
ex) 위에서 만든 VIEW_CHK30 뷰를 이용하여 급여가 400이상인 사원은 20번 부서로 이동시켜본다.
UPDATE VIEW_CHK30 SET DEPTNO=20
WHERE SAL>=400;
READ ONLY
WITH READ ONLY 옵션은 뷰를 통해서는 기본 테이블의 어떤 컬럼에 대해서도 내용을 절대 변경할 수 없도록 하는 것이다.
ex) WITH READ ONLY 옵션을 지정한 뷰를 정의한다.
CREATE OR REPLACE VIEW VIEW_READ30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH READ ONLY;
1. WITH READ ONLY 옵션을 기술한 VIEW_READ30 뷰의 커미션을 모두 2000으로 변경해보도록 한다.
UPDATE VIEW_READ30 SET COMM=2000;
인라인 뷰
ROWNUM컬럼은 데이터가 입력된 순서(몇번째 입력된 데이터인지)를 알려주는 ‘가상컬럼’이다.
1. 다음은 ROWNUM 컬럼 값을 출력하기 위한 쿼리문이다.
SELECT ROWID, ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP;
2. 입사일이 빠른 사람 5명만(TOP-N)을 얻어오기 위해서는 일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그 중 일부의 데이터만 출력할 수 있도록 해야 하므로 ORDER BY 절을 사용하여 입사일을 기준으로 오름차순 정렬해보자.
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;
3. 이번에는 입사일을 기준으로 오름차순 정렬을 하는 쿼리문에 ROWNUM 컬럼을 출력해보자.
SELECT ROWNUM, EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;
인라인 뷰
위 결과를 보면 입사일을 기준으로 오름차순 정렬을 하였기에 출력되는 행의 순서는 바뀌더라도 해당 행의 ROWNUM 컬럼 값은 바뀌지 않는다는 것을 알 수 있다.
ROWNUM 컬럼은 오라클의 내부적으로 부여되는데 INSERT 문을 이용하여 입력하면 입력한 순서에 따라 1씩 증가되면서 값이 지정되어 바뀌지 않는다.
정렬된 순서대로 ROWNUM 칼럼 값이 매겨지도록 하려면 새로운 테이블이나 뷰로 새롭게 데이터를 저장해야만 한다.
인라인 뷰를 사용하여 급여를 많이 받는 순서대로 3명만 출력하시오.
SELECT ROWNUM "RANKING", EMPNO, ENAME, SAL
FROM ( SELECT EMPNO, ENAME, SAL
FROM EMP_COPY
WHERE SAL IS NOT NULL
ORDER BY SAL DESC)
WHERE ROWNUM<=3;
========================================================================
CHAPTER14시퀀스
시퀀스 개념
시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로 시퀀스를 기본 키로
사용하게 되면 사용자의 부담을 줄일 수 있다(INSRET문에쓰인다 ,기본키에 쓰인다)
--다음은 시퀀스 생성을 위한 기본 형식이다.
CREATE SEQUENCE sequence_name
[START WITH n] ①
[INCREMENT BY n] ②
[{MAXVALUE n | NOMAXVALUE}] ③
[{MINVALUE n | NOMINVALUE}] ④
[{CYCLE | NOCYCLE}] ⑤
[{CACHE n | NOCACHE}] ⑥
1.START WITH
시퀀스 번호의 시작값을 지정할때 사용된다. 만일1부터 시작되는 시퀀스를
생성하려면 START WITH 1이라고 기술하면 된다
2.INCREMENT BY
연속적인 시퀀스 번호의 증가치를 지정할 때 사용된다. 만일 1씩 증가하는 시퀀스를
생성하려면 INCREMENT BY 1 이라고 기술하면 된다
시퀀스 형식
다음은 부서 번호를 자동으로 부여해주는 시퀀스 객체를 생성하는 문장이다.
CREATE SEQUENCE DEPT_DEPTNO_SEQ
INCREMENT BY 10
START WITH 10;
시퀀스 데이터 딕셔너리
사용자가 작성한 객체들의 정보를 저장하고 있는 데이터 딕셔너리의 이름을 보면 다음과 같은 규칙성이 있다.
테이블(TABLE) 객체에 대한 정보를 저장하는 데이터 딕셔너리는 USER_TABLES이고, 뷰(VIEW)객체에 대한 정보를 저장하는 데이터 딕셔너리는 USER_VIEWS이므로 이번 장에서 새로 배운 시퀀스(SEQUENCE)에 대한 자료사전의 이름은 USER_SEQUENCES 이라고 생각할 수 있다.
생성된 시퀀스 객체에 대한 정보를 저장하는 데이터 딕셔너리로는 USER_SEQUENCES가 있다.
ex) SEQUENCE_NAME은 시퀀스 객체의 이름을 저장하고 MIN_VALUE는 최소값, MAX_VALUE는 최대값, INCREMENT_BY는 증가치에 대한 정보를 가지고 있으며, CYCLE_FLAG는 CYCLE옵션을 사용하는지, 하지 않는지에 대한 정보를 가지며 LAST_NUMBER는 마지막 숫자 값을 가지고 있다.
1. 데이터 딕셔너리 USER_SEQUENCES로 현재 사용 중인 시퀀스 객체의 정보를 살펴보자.
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,
INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES;
시퀀스의 현재 값을 알아내기 위해서 CURRVAL를 사용하고, 다음 값을 알아내기 위해서는 NEXTVAL를 사용한다.
CURRVAL : 현재 값을 반환한다
NEXTVAL : 현재 시퀀스값의 다음 값을 반환한다
CURRVAL에 새로운 값이 할당되기 위해서는 NEXTVAL로 새로운 값을 생성해야 한다.
즉, NEXTVAL로 새로운 값을 생성한 다음에 이 값을 CURRVAL에 대체하게 됩니다.
CURRVAL, NEXTVAL
NEXTVAL, CURRVAL을 사용할 수 있는 경우와 사용할 수 없는 경우를 살펴보자.
NEXTVAL, CURRVAL을 사용할 수 있는 경우
서브 쿼리가 아닌 SELECT 문
INSERT 문의 SELECT 문
INSERT 문의 VALUES 절
UPDATE 문의 SET 절
NEXTVAL, CURRVAL을 사용할 수 없는 경우
VIEW의 SELECT 절
DISTINCT 키워드가 있는 SELECT 문
GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문
SELECT, DELETE, UPDATE의 서브 쿼리
CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값
CURRVAL, NEXTVAL 사용하기
ex) CURRVAL, NEXTVAL의 실제 사용 예를 살펴보자.
1. NEXTVAL로 새로운 값을 생성한다.
SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;
2. 시퀀스의 현재 값을 알아내기 위해서 CURRVAL를 사용한다.
SELECT DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;
시퀀스 적용하기
시퀀스는 99.9%가 INSERT 연산과 같이 사용되어 컬럼 값을 자동으로 발생시키는 용도로 사용된다.
기본키에 시퀀스 적용
사원 번호를 생성하는 시퀀스 객체를 생성하여 이를 기본 키인 사원 번호에 사용하여 사용자가 새로운 사원을 추가할 때마다 유일한 사원번호를 INSERT 해야 하는 번거로움을 줄여보자.
1. 시작 값이 1이고 1씩 증가하고, 최댓값이 100000이 되는 시퀀스 EMP_SEQ 생성한다.
CREATE SEQUENCE EMP_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100000;
2. 이번에는 생성된 시퀀스를 사용하기 위해서 사원 번호를 기본 키로 설정하여 EMP01란 이름으로 새롭게 생성한다.
DROP TABLE EMP01;
CREATE TABLE EMP01(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
HIREDATE DATE
);
3. 사원 번호를 저장하는 EMPNO 컬럼은 기본 키로 설정하였으므로 중복된 값을 가질 수 없다. 다음은 생성한 EMP_SEQ 시퀀스로부터 사원번호를 자동으로 할당 받아 데이터를 추가하는 문장이다.
INSERT INTO EMP01
VALUES(EMP_SEQ.NEXTVAL, ‘김씨’, SYSDATE);
4. 데이터를 추가하면서 EMP_SEQ 시퀀스로부터 사원번호를 자동으로 할당받았는지 EMP01 테이블의 내용을 확인한다.
SELECT * FROM EMP01;
ex) 부서 테이블을 생성한다.
CREATE TABLE DEPT_EXAMPLE(
DEPTNO NUMBER(4) PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);
1. 위에서 만든 부서 테이블(DEPT_EXAMPLE)의 DEPTNO 컬럼에 유일한 값을 가질 수 있도록 시퀀스 객체를 생성(DEPT_EXAMPLE_SEQ)하고 데이터를 입력하시오.
시퀀스 생성
CREATE SEQUENCE DEPT_EXAMPLE_SEQ
START WITH 10
INCREMENT BY 10;
데이터 입력
INSERT INTO DEPT_EXAMPLE VALUES(DEPT_EXAMPLE_SEQ.NEXTVAL, '경리부', '서울');
INSERT INTO DEPT_EXAMPLE VALUES(DEPT_EXAMPLE_SEQ.NEXTVAL, '인사부', '인천');
INSERT INTO DEPT_EXAMPLE VALUES(DEPT_EXAMPLE_SEQ.NEXTVAL, '영업부', '용인');
INSERT INTO DEPT_EXAMPLE VALUES(DEPT_EXAMPLE_SEQ.NEXTVAL, '전산부', '수원');
DROP SEQUENCE문으로 시퀀스를 제거해보자.
DROP SEQUENCE DEPT_DEPTNO_SEQ;
============================================================
chapter15 인덱스
업다운게임 개념느낌 1~100까지안에 숫자를 맞춘다면 50부터 짜르면서 효율적으로 처리하는 개념
인덱스(INDEX)
SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체다.
속도개선 특정컬럼에 부여
장점
검색 속도가 빨라진다
시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다
단점
인덱스를 위한 추가적인 공간이 필요하다
인덱스를 생성하는데 시간이 걸린다
데이터의 변경작업(INSERT UPDATE DELETE)이 자주 일어날 경우에는 오히려 성능이 저하된다
인덱스 사용 경우 판단하기
인덱스가 검색을 위한 처리 속도만 향상시킨다고 했다
하지만 무조건 인덱스를 사용한다고 검색 속도가 향상되는 것은 아니다
계획성 없이 너무 많은 인덱스를 지정하면 오히려 성능을 저하시킬 수도 있다
인덱스 생성
제약 조건에 의해 자동으로 생성되는 인덱스 외에 CREATE INDEX 명령어로 직접 인덱스를 생성할 수도 있다.
CREATE INDEX index_name
ON table_name (column_name);
인덱스가 지정하지 않은 컬럼인 ENAME 으로 조회하여 어느 정도의 시간은 소요됨을 확인하였으므로 이번에는 ENAME 컬럼으로 인덱스를 지정하여 조회 시간이 단축됨을 확인해보자.
1. 이번에는 테이블 EMP01의 컬럼 중에서 이름(ENAME)에 대해서 인덱스를 생성해보자.
CREATE INDEX IDX_EMP01_ENAME
ON EMP01(ENAME);
1. EMP01 테이블의 직급 컬럼을 인덱스로 설정하되 인덱스 이름을 IDX_EMP01_JOB로 주고 데이터 딕셔너리를 이용하여 설정된 화면을 출력하시오.
인덱스 생성
CREATE INDEX IDX_EMP01_JOB
ON EMP01(JOB);
인덱스 출력
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP', 'EMP01');
인덱스 제거
DROP INDEX index_name;
================================================================
CHAPTER16 사용자권한
권한의 역할과 종류(1)
권한은 사용자가 특정 테이블을 접근할 수 있도록 하거나 해당 테이블에 SQL(SELECT/INSERT/UPDATE/DELETE) 문을 사용할 수 있도록 제한을 두는 것을 말한다.
데이터베이스 보안을 위한 권한은 시스템 권한(System Privileges)과 객체 권한(Object Privileges)으로 나뉜다.
시스템 권한은 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한 등 주로 DBA에 의해 부여되며 그 권한의 수가 80 가지가 넘기에 대표적인 시스템 권한만 정리하고 넘어간다.
권한의 역할과 종류(2)
테이터베이스를 관리하는 권한으로 다음과 같은 것이 있다 이러한 권한은
시스템 관리자가 사용자에게 부여하는 권한이다
시스템 권한 기능
CREATE SESSION 데이터베이스에 접속할 수 있는 권한
형식 : CREATE USER USER_NAME
IDENTIFIED BY PASSWORD;
권한부여
사용자에게 시스템 권한 부여하기 위해서는 GRANT명령어를 사용한다
형식 : GRANT PRIVILEGE_NAME,...
TO USER_NAME;
REVOKE
권한줬다가 뺏기
사용자에게 부여한 객체 권한을 데이터베이스 관리자나 객체 소유자로부터 철회(권한을 취소)하기 위해서는 REVOKE 명령어를 사용한다.
다음은 REVOKE 명령어의 형식
형식 : REVOKE {privilege_name | all} ON object_name FROM {user_name | role_name | public};
===========================================================
CHAPTER17. 롤
롤이란
롤은 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것이라고 생각하면 된다.
============================================================
CHAPTER18. PL/SQL
PL/SQL 구조
PL/SQL은 PASCAL과 유사한 구조로서 DECLARE~BEGIN~EXCEPTION~END 순서를 갖는다.
==============================================================
CHAPTER19 프로시저
프로시저(함수와같은개념)
지금까지 실습한 예제는 한번 실행하면 결과값을 돌려주고 끝나는 예제였다.
경우에 따라서는 우리가 만든 PL/SQL을 저장해 놓고 필요한 경우 호출하여 사용할 수 있었으면 할 때가 있다. 오라클은 사용자가 만든 PL/SQL 문을 데이터베이스에 저장 할 수 있도록 저장 프로시저라는 것을 제공한다.
이렇게 저장 프로시저를 사용하면 복잡한 DML 문들을 필요할 때마다 다시 입력할 필요 없이 간단하게 호출만 해서 복잡한 DML 문의 실행 결과를 얻을 수 있다.
저장 프로시저를 사용하면 성능도 향상되고, 호환성 문제도 해결된다.
프로시저 생성하기
ex) 사원 테이블에 저장된 모든 사원을 삭제하는 프로시저를 작성해보도록 하겠다.
1. 모든 사원을 삭제하는 프로시저를 실행시키기 위해서 미리 사원 테이블을 복사해서 새로운 사원 테이블(EMP01)을 만들어 놓는다.
2. 다음과 같이 입력하시오.
CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
DELETE FROM EMP01;
END;
/
3. 생성된 저장 프로시저는 EXECUTE 명령어로 실행시킨다.
예
EXECUTE DEL_ALL
============================================================
CHAPTER20 트리거
트리거
다음은 트리거(TRIGGER)의 사전적인 의미이다
1.(총의)방아쇠 = HAIR TRIGGER
2.제동기 ,제륜장치
3.연쇄반응.생리현상.일련의 사건 등을 유발하는)계기,유인,자극
오라클에서의 트리거 역시 해당 단어의 의미처럼 어떤 이벤트가 발생하면 자동적으로 방아쇠가 당겨져 총알이 발사되듯이 특정 테이블이 변경되면 이를 이벤트로 다른 테이블이 자동으로(연쇄적으로) 변경되도록 하기 위해서 사용한다.
끝
