출처 : http://www.oracleclub.com/main.action (오라클 클럽)
VIEW 테이블
뷰란?
- - 뷰는 하나의 가상 테이블이라 생각 하면 된다.
- - 뷰는 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터를 관리 할수 있다.
- - 뷰는 복잡한 Query를 통해 얻을 수 있는 결과를 간단한 Query로 얻을 수 있게 한다.
- - 한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.
- - 특정 평가 기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 한다.
뷰의 제한 조건.
- - 테이블에 NOT NULL로 만든 컬럼들이 뷰에 다 포함이 되 있어야 한다.
- - ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터도 INSERT 할 수 없다
- - WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신 할 수 없다.
- - WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제,수정을 할 수 있다
뷰 문법
- - FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성
- - WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지정
- - WITH READ ONLY : SELECT만 가능한 VIEW 생성
- - 함수를 사용한 컬럼은 반드시 ALIAS를 지정해야 한다.
뷰 예제
-- 뷰 생성 SQL> CREATE OR REPLACE VIEW name_query AS SELECT a.ename, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno AND b.deptno = 20; -- 뷰를 이용한 조회 SQL> SELECT * FROM name_query; ENAME DNAME -------------------- ---------- SMITH RESEARCH JONES RESEARCH ...
WITH CHECK OPTION
뷰의 조건식을 만족하는 데이터만 INSERT 또는 UPDATE가 가능 하도록 하는 옵션 이다.
-- 부서 번호 10의 데이터를 조회 하는 뷰 SQL> CREATE OR REPLACE VIEW check_option AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION ; -- 부서 번호가 10인 사원만 INSERT, UPDATE할 수 있다. SQL> INSERT INTO check_option(empno, ename, deptno) VALUES (10005, 'jain', 30); INSERT INTO check_option(empno, ename, deptno) * 1행에 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
WITH READ ONLY
SELECT만 가능한 VIEW를 생성 한다
-- 아래 뷰는 읽기만 가능하다. SQL> CREATE OR REPLACE VIEW read_only AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH READ ONLY;
뷰의 정보 조회
USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있다.
SQL> SELECT view_name, text FROM USER_VIEWS;
SEQUENCE(시퀀스)시퀀스란?
시퀀스 문법
시퀀스 예제-- 시퀀스 생성 SQL> CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 MAXVALUE 100000 ; -- NEXTVAL을 사용하여 시퀀스 값을 자동으로 입력할 수 있다. SQL> INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate); -- NEXTVAL은 현재 시퀀스 값의 다음 값을 반환 한다. SQL> SELECT emp_seq.NEXTVAL FROM DUAL ; -- CURRVAL은 현재 시퀀스 값을 반환 한다. SQL> SELECT emp_seq.CURRVAL FROM DUAL ; 시퀀스의 수정 및 삭제START WITH는 수정 할 수 없다. START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE 문장과 같다.
시퀀스의 수정 및 삭제 예제-- 2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정 SQL> ALTER SEQUENCE emp_seq INCREMENT BY 2 CYCLE; -- 시퀀스 삭제 SQL>DROP SEQUENCE emp_seq;
인덱스(Index)인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다. - 자동 인덱스 : 프라이머리 키 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스. - 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스 인덱스를 생성하는 것이 좋은 컬럼
다음과 같은 경우에는 인덱스 생성이 불필요 하다.
오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다. B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다. 이 알고리즘 원리는 비트맵 인덱스비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 사용하는 것이 좋다.(ex 남,여의 값을 가지는 성별 컬럼) 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킨다. 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있다. SQL> CREATE BITMAP INDEX emp_deptno_indx ON emp(deptno); UNIQUE 인덱스UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다. 프라이머리키 와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다. SQL> CREATE UNIQUE INDEX emp_ename_indx ON emp(ename); NON-UNIQUE 인덱스NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다. SQL> CREATE INDEX dept_dname_indx ON dept(dname); 결합 인덱스복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개 이다 SQL> CREATE UNIQUE INDEX emp_empno_ename_indx ON emp(empno, ename); 인덱스의 삭제인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다. 인덱스를 삭제하려면 인덱스의 소유자 이거나 DROP ANY INDEX권한을 가지고 있어야 한다. 인덱스는 ALTER를 할 수 없다.
SQL> DROP INDEX emp_empno_ename_indx;
인덱스 데이터사전인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수 있다. SQL> SELECT index_name, index_type FROM USER_INDEXES WHERE table_name='EMP'; INDEX_NAME INDEX_TYPE ------------------- ----------- EMP_DEPTNO_INDX BITMAP EMP_PK_EMPNO NORMAL
데이터 사전(Data Dictionary)데이터 사전(Data Dictionary) 이란?데이터 사전(Data Dictionary)이란 읽기전용 테이블 및 뷰들의 집합으로 데이터베이스 전반에 대한 정보를 제공 한다. 데이터 사전에 저장되는 내용은 아래와 같다.
Oracle 사용자 SYS는 데이터 사전의 모든 기본 TABLE과 VIEW를 소유 한다. Oracle은 DDL 명령이 실행 될 때 마다 데이터 사전을 Access 한다. 모든 Oracle 사용자는 DB정보에 대한 읽기 전용 참조로 데이터 사전을 사용 할 수 있다. DB작업동안 Oracle은 데이터 사전을 읽어 개체의 존재여부와 사용자에게 적합한 Access 권한이 있는지를 확인 한다. 또한 Oracle은 데이터 사전을 계속 갱신하여 DATABASE 구조, 감사, 사용자권한, 데이터등의 변경 사항을 반영 한다. USER_XXXX 데이터 사전한 특정 사용자에게 종속되어 있고, 그 사용자가 조회 가능한 데이터 사전 뷰들로 ALL_XXXX 데이터 사전의 모든 정보의 부분 집합 이다. -- Scott 사용자의 TABLE 조회 결과 이다. SQL> SELECT table_name, tablespace_name FROM USER_TABLES; TABLE_NAME TABLESPACE_NAME ------------- ------------------ BONUS TOOLS DEPT TOOLS DUMMY TOOLS EMP TOOLS SALGRADE TOOLS ALL_XXXX 데이터 사전한 특정 사용자가 조회 가능한 모든 데이터사전을 의미 한다. 자신이 조회하려는 객체의 주인이 아니더라도 그 객체에 접근 할 수 있는 권한을 가지고 있다면 ALL_XXX 뷰를 통하여 조회가 가능 하다. -- 아래 SQL문장을 실행해 보자. SQL> SELECT table_name, tablespace_name FROM ALL_TABLES; DBA_XXXX 데이터 사전DBA권한을 가진 사용자 만이 조회할 수 있는 데이터 사전으로서 모든 오라클 데이터베이스 객체에 대한 정보를 볼 수 있다. SELECT ANY TABLE 권한이 있는 사용자 또한 질의가 가능 하다. 이러한 데이터 사전의 동의어는 생성되지 않으며,다른 사용자가 질의 하려면 앞에 SYS.이라는 접두어를 붙여야 한다. -- SYS계정으로 접속하여 아래 문장을 실행해 보자 SQL> SELECT OWNER, OBJECT_NAME FROM SYS.DBA_OBJECTS;
FROM절상의 서브쿼리(INLINE VIEW)INLINE VIEW란 FROM절 상에 오는 서브쿼리로 VIEW처럼 작용 한다. -- 급여가 부서번호 20의 평균 급여보다 크고, 사원을 관리하는 사원으로서 -- 부서번호 20에 속하지 않은 사원의 정보를 조회하는 SQL 문장이다. SQL> SELECT b.empno,b.ename,b.job,b.sal, b.deptno FROM (SELECT empno FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 20)) a, emp b WHERE a.empno = b.empno AND b.mgr is NOT NULL AND b.deptno != 20 EMPNO ENAME JOB SAL DEPTNO ---------- ---------- --------- ---------- ---------- 7698 BLAKE MANAGER 2850 30 7782 CLARK MANAGER 2450 10
다중 열(Multiple-Column) 서브쿼리다중 열 서브쿼리란 서브쿼리의 결과 값이 두 개 이상의 컬럼을 반환하는 서브쿼리 이다. Pairwise(쌍비교) Subquery서브쿼리가 한 번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 준다. SQL> SELECT empno, sal, deptno FROM emp WHERE (sal, deptno) IN ( SELECT sal, deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO ---------- ---------- ---------- 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30
Nonpairwise(비쌍비교) Subquery서브쿼리가 여러 조건별로 사용 되어서 결과 값을 주 쿼리로 넘겨 준다. SQL> SELECT empno, sal, deptno FROM emp WHERE sal IN ( SELECT sal FROM emp WHERE deptno = 30 AND comm is NOT NULL ) AND deptno IN ( SELECT deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO ---------- ---------- ---------- 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30 Null Values in a Subquery서브쿼리에서 NULL 값이 반환 되면 주 쿼리 에서는 어떠한 행도 반환되지 않는다.
다중 행(Multiple-Row) 서브쿼리- 하나 이상의 행을 반환하는 서브쿼리를 다중 행 서브쿼리라고 한다. - 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있다. IN 연산자의 사용 예제-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제 SQL> SELECT empno,ename,sal,deptno FROM emp WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno); EMPNO ENAME SAL DEPTNO ---------- -------- --------- --------- 7698 BLAKE 2850 30 7788 SCOTT 3000 20 7902 FORD 3000 20 7839 KING 5000 10
ANY 연산자의 사용 예제ANY 연산자는 서브쿼리의 결과값중 어느 하나의 값이라도 만족이 되면 결과값을 반환 한다. SQL> SELECT ename, sal FROM emp WHERE deptno != 20 AND sal > ANY (SELECT sal FROM emp WHERE job='SALESMAN'); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 ... ALL 연산자의 사용 예제ALL 연산자는 서브쿼리의 결과값 중 모든 결과 값이 만족 되야만 결과값을 반환 한다. SQL> SELECT ename, sal FROM emp WHERE deptno != 20 AND sal > ALL (SELECT sal FROM emp WHERE job='SALESMAN'); ENAME SAL ---------- ---------- BLAKE 2850 CLARK 2450 KING 5000 EXISTS 연산자의 사용 예제EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값 들만을 결과로 반환해 준다. 서브쿼리에서 적어도 한 개의 행을 반환하면 논리식은 참이고 그렇지 않으면 거짓 이다. -- 사원을 관리할 수 있는 사원의 정보 조회 예제 SQL> SELECT empno, ename, sal FROM emp e WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr) EMPNO ENAME SAL ---------- -------- -------- 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 ...
단일 행(Sing-Row) 서브쿼리- 오직 한개의 행(값)을 반환. - 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용. SQL> SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369); -- 위의 서브쿼리를 실행 시키면 아래의 그림처럼, -- empno가 7369인 job을 먼저 검색하고, job이 'CLERK'인 -- 사원의 이름과 직업을 반환 한다.
|
서브쿼리(Subquery)란?
서브쿼리란?
- 서브쿼리는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장 이다.
- SELECT, UPDATE, DELETE, INSERT와 같은 DML문과 CREATE TABLE 또는 VIEW에서 이용 될 수 있다.
- 알려지지 않은 조건에 근거한 값 들을 검색하는 SELECT 문장을 작성하는데 유용 하다.
가이드라인
- 서브쿼리는 괄호로 묶어야 한다.
- 단일 행 연산자(=, >, >=, <, <=, <>, !=)와 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)들이 서브쿼리에 사용 된다.
- 서브쿼리는 연산자의 오른쪽에 위치해야 한다.
서브쿼리의 유형
- 단일 행(Sing-Row) 서브쿼리 : SELECT 문장으로 부터 오직 하나의 행 만을 검색하는 질의이다.
- 다중 행(Multiple-Row) 서브쿼리 : SELECT문장으로부터 하나 이상의 행을 검색하는 질의이다.
- 다중 열(Multiple-Column) 서브쿼리 : SELECT문장으로부터 하나 이상의 컬럼을 검색하는 질의이다.
- FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용 한다.
- 상관관계 서브 쿼리 : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이다.
GROUP BY와 HAVING
GROUP BY
- 특정한 컬럼의 테이터 들을 다른 데이터들과 비교해 유일한 값에 따라 무리를 짓는다.
- GROUP BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눈다.
- 컬럼명을 GROUP 함수와 SELECT절에 사용하고자 하는 경우 GROUP BY뒤에 컬럼명을 추가 하면 된다.
-- 부서별로 그룹한 결과 값이며, 부서별로 사원수를 조회한다. SQL> SELECT b.deptno, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.deptno; DEPTNO COUNT(*) ----- ---------- 10 3 20 5 30 6 -- 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, -- 급여의 합을 조회하는 예제 SQL> SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균", ROUND(SUM(sal)) "급여합계" FROM emp GROUP BY deptno; DEPTNO COUNT(*) 급여평균 급여합계 --------- ---------- ---------- ---------- 10 3 2998 8995 20 5 2175 10875 30 6 1567 9400 -- 업무별로 그룹하여 업무, 인원수, 평균 급여액, -- 최고 급여액, 최저 급여액 및 합계를 조회하는 예제 SQL> SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액", MAX(sal) "최고급여액", MIN(sal) "최저급여액", SUM(sal) "급여합계" FROM emp GROUP BY job JOB 인원수 평균급여액 최고급여액 최저급여액 급여합계 --------- ------- ---------- ---------- --------- -------- ANALYST 2 3000 3000 3000 6000 CLERK 4 1037.5 1300 800 4150 MANAGER 3 2840 2975 2695 8520 PRESIDENT 1 5000 5000 5000 5000 SALESMAN 4 1400 1600 1250 5600
HAVING 절
WHERE절에 GROUP 함수를 사용할 수 없다.
HAVING절은 GROUP 함수를 가지고 조건비교를 할 때 사용 한다.
WHERE → GROUP BY → HAVING → ORDER BY순으로 쿼리문이 와야 한다.
-- 사원수가 5명이 넘는 부서의 부서명과 사원수 조회 SQL> SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY dname HAVING COUNT(a.empno) > 5 DNAME COUNT(A.EMPNO) -------------- -------------- RESEARCH 6 SALES 6 -- 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 조회 -- 단 판매원은 제외하고 월 급여 합계로 내림차순 정렬. SQL> SELECT job, SUM(sal) "급여합계" FROM emp WHERE job != 'SALES' -- 판매원은 제외 GROUP BY job -- 업무별로 Group By HAVING SUM(sal) > 5000 -- 전체 월급이 5000을 초과하는 ORDER BY SUM(sal) DESC; -- 월급여 합계로 내림차순 정렬 JOB 급여합계 --------- ---------- MANAGER 8520 ANALYST 6000 SALESMAN 5600
그룹 함수의 종류
그룹함수
그룹 함수란?
- 여러 행 또는 테이블 전체의 행에 대해 함수가 적용되어 하나의 결과값을 가져오는 함수를 말한다.
- GROUP BY절을 이용하여 그룹 당 하나의 결과가 주어지도록 그룹화 할 수 있다.
- HAVING절을 사용하여 그룹 함수를 가지고 조건비교를 할 수 있다.
- COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않는다.
- MIN, MAX 그룹함수는 모든 자료형에 대해서 사용 할 수 있다.
COUNT
COUNT 함수는 검색된 행의 수를 반환 한다.
-- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
SQL> SELECT COUNT(deptno) FROM dept;
COUNT(DEPTNO)
-------------
4
MAX
MAX 함수는 컬럼값 중에서 최대값을 반환 한다.
-- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
SQL> SELECT MAX(sal) salary FROM emp;
SALARY
-------
5000
MIN
MIN 함수는 컬럼값 중에서 최소값을 반환 한다.
-- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
SQL> SELECT MIN(sal) salary FROM emp;
SALARY
--------
800
AVG
AVG 함수는 평균 값을 반환 한다.
-- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
SQL> SELECT ROUND(AVG(sal),1) salary
FROM emp
WHERE deptno = 30;
SALARY
----------
1566.7
SUM
SUM 함수는 검색된 컬럼의 합을 반환 한다.
-- 부서번호 30의 사원 급여 합계를 조회.
SQL> SELECT SUM(sal) salary
FROM emp
WHERE deptno = 30;
SALARY
---------
9400
STDDEV
STDDEV 함수는 표준편차를 반환 한다.
-- 부서번호 30의 사원 급여 표준편차를 반환.
SQL> SELECT ROUND(STDDEV(sal),3) salary
FROM emp
WHERE deptno = 30;
SALARY
----------
668.331
변환 함수(Conversion Functions)
TO_CHAR
TO_CHAR 함수는 DATE형, NUMBER형을 VARCHAR2형으로 변환 한다.
-- 오늘이 6월 10일 이니깐, 6월이 출력된다. SQL> SELECT TO_CHAR(SYSDATE) CHARTEST FROM DUAL; CHARTEST -------------- JUNE -- 오늘 날짜가 문자형으로 출력된다. SQL> SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM DUAL; CHARTEST -------------- 00/06/10
TO_DATE
TO_DATE 함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환한다.
-- '2000/06/16' 문자열이 날짜형으로 변환 된다 SQL> SELECT TO_DATE('2000/06/16','RRRR/MM/DD') FROM DUAL; TO_DATE( ------------ 2000/06/16
TO_NUMBER
TO_NUMBER 함수는 CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환 한다.
-- '1210616' 문자열이 숫자형으로 변환 된다 SQL> SELECT TO_NUMBER('1210616') FROM DUAL; TO_NUMBER( ------------ 1210616
날짜 처리 함수(Date Functions)
LAST_DAY(d)
LAST_DAY 함수는 달의 마지막 날의 날짜를 반환 한다
-- 오늘이 6월 5일 이니깐, 6월달의 마지막 날 30일을 반환 한다. SQL> SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL; TODAY LASTDAY ------------ --------------- 05-JUN-2000 30-JUN-2000
ADD_MONTHS(a, b)
ADD_MONTHS 함수는 a의 날짜에 b의 달을 더한 값을 반환 한다.
-- 오늘이 6월5일 이니깐, 3개월이 더해진 9월 5일이 반환 된다. SQL> SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'RRRR/MM/DD' LASTDAY) "date" FROM DUAL; date ------------ 2000/09/05
MONTH_BETWEEN(a1, a2)
MONTH_BETWEEN은 a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 한다.
-- 달사이의 간격을 숫자형으로 반환 한다. SQL> SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05'), TO_DATE('2000/09/23')) "Date" FROM DUAL; Date ------------- -3.880635
ROUND(d[,F])
ROUND 함수는 F에 지정된 단위로 반올림 한다. F가 연도라면 연도 단위로 반올림 한다.
SQL> SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR') FROM DUAL; ROUND(TO_ ------------- 99-01-01 SQL> SELECT ROUND(TO_DATE('1998/04/11'), 'MONTH') FROM DUAL; ROUND(TO_ ------------- 98-04-01 SQL> SELECT ROUND(TO_DATE('1998/04/11'), 'DAY') FROM DUAL; ROUND(TO_ ------------- 98-04-11
날짜에 대한 산술 연산
| 연 산 | 결과치 | 사용목적 |
|---|---|---|
| 날짜 + 숫자 | 날짜 | 특정한 날로부터 며칠 후의 날짜 계산 |
| 날짜 - 숫자 | 날짜 | 특정한 날로부터 며칠 전의 날짜 계산 |
| 날짜 - 날짜 | 숫자 | 두 날짜 사이의 차이를 숫자로 계산 |
문자열 처리 함수(Character Functions)
CONCAT(char1, char2)
CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 한며, "||" 연산자와 같은 역할을 한다.
SQL> SELECT CONCAT('Oracle', 'Club') FROM DUAL; NAME ---------- OracleClub
INITCAP(char)
주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 준다.
SQL> SELECT INITCAP('kim jung sick') FROM DUAL; NAME ------------- Kim jung sick
LOWER(char), UPPER(char)
LOWER 함수는문자열을 소문자로, UPPER 함수는 문자열을 대문자로 변환 시켜 준다.
SQL> SELECT LOWER('KIM JUNG SICK') FROM DUAL; NAME ------------- kim jung sick SQL> SELECT UPPER('kim jung sick') FROM DUAL; NAME -------------- KIM JUNG SICK
LPAD(char1, n [,char2])
왼쪽에 문자열을 끼어 놓는 역할을 한다. n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환 한다.
SQL> SELECT LPAD('JUNG-SICK', 10, '*') FROM DUAL; NAME ------------ *JUNG-SICK
RPAD(char1, n [,char2])
LPAD와 반대로 오른쪽에 문자열을 끼어 놓는 역할을 한다.
SQL> SELECT RPAD('JUNG-SICK', 10, '*') FROM DUAL; NAME ------------ JUNG-SICK*
SUBSTR(char, m ,[n])
SUBSTR 함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 한다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환한다.
SQL> SELECT SUBSTR('JUNG-SICK', 3, 3) FROM DUAL; NAME ----------- NG- -- 뒤에서부터 자를 SQL> SELECT SUBSTR('JUNG-SICK', -3, 3) FROM DUAL; NAME ----------- ICK
LENGTH(char1)
문자열의 길이를 반환 한다.
SQL> SELECT LENGTH('JUNG-SICK') TEST FROM DUAL; TEST ---------- 9
REPLACE(char1, str1, str2)
REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes -------------- BLACK and BLUE SQL> SELECT REPLACE('JACK and JUE','JA','BL') "Changes" FROM DUAL Changes ------------ BLCK and JUE -- 대소문자를 구분한다는 것을 알수 있습니다. SQL> SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL Changes ------------ JACK and JUE
INSTR
문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다. 지정한 문자열이 발견되지 않으면 0이 반환 된다.
-- 지정한 문자 OK가 발견되지 않아서 0이 반환. SQL> SELECT INSTR('CORPORATE FLOOR','OK') "Instring" FROM DUAL; Instring ---------- 0 -- OR이 있는 위치 2를 반환. -- 왼쪽부터 비교를 한다는 것을 알 수 있다. SQL> SELECT INSTR('CORPORATE FLOOR','OR') "Instring" FROM DUAL; Instring ---------- 2 -- 왼쪽에서 3번째부터 시작을 해서 비교를 한다. -- 2번째 OR의 위치가 반환 됩니다. SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3) "Instring" FROM DUAL; Instring ---------- 5 -- 왼쪽에서 3번째부터 시작을 해서 비교를 하는데 -- OR이 두 번째 검색되는 지점의 위치를 반환 한다. SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14
TRIM
특정한 문자를 제거 한다.제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 된다.
-- 0을 제거 한다. SQL> SELECT TRIM(0 FROM 0009872348900) "TRIM Example" FROM DUAL; TRIM Example ------------ 98723489 -- 어떤 문자도 입력하지 않으면 기본적으로 공백이 제거 된다. SQL> SELECT NVL(TRIM (' '),'공백') "TRIM Example" FROM DUAL; TRIM Example ------------ 공백 -- LTRIM SQL> SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example" FROM DUAL; LTRIM example ------------ XxyLAST WORD -- RTRIM SQL> SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example" FROM DUAL; RTRIM examp ----------- BROWNINGyxX
숫자함수(Number Functions)
ABS(n)
ABS 함수는 절대값을 계산하는 함수이다.
SQL> SELECT ABS(-10) Absolute FROM DUAL; Absolute -------- 10
CEIL(n)
CEIL 함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수이다.
SQL> SELECT CEIL(10.1) test FROM DUAL; test ------- 11 SQL> SELECT CEIL(-10.1) test FROM DUAL ; test ------- -10
FLOOR(n)
FLOOR 함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수이다.
-- CEIL 함수와 비교해 보자. SQL> SELECT FLOOR(10.1) test FROM DUAL ; test ------- 10 SQL> SELECT FLOOR(-10.1) test FROM DUAL ; test ------- -11
MOD(m, n)
MOD 함수는 m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.
SQL> SELECT MOD(9, 4) test FROM DUAL ; test ------- 1
POWER(m, n)
POWER 함수는 m의 n승 값을 계산 한다.
SQL> SELECT POWER(4, 2) test FROM DUAL ; test ------- 16
ROUND(n, [m])
ROUND 함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿 수를 나타 낸다.
SQL> SELECT ROUND(192.123, 1) test FROM DUAL ; test ------- 192.1 SQL> SELECT ROUND(192.123, -1) test FROM DUAL ; test ------- 190
TRUNC(n, m)
TRUNC 함수는 n값을 m 소숫점 자리로 반내림한 값을 반환 한다.
-- ROUND 함수와 비교해 보자 SQL> SELECT TRUNC(7.5597, 2) test FROM DUAL ; test ------- 7.55 SQL> SELECT TRUNC(5254.26, -2) test FROM DUAL ; test ------- 5200
Commit과 Rollback 예제
COMMIT 과 ROLLBACK
이전의 커밋(COMMIT)이 일어난 뒤부터 다음의 커밋(COMMIT) 전까지의 작업이 하나의 트랜잭션 이며, 커밋과 롤백(ROLLBACK)은 이러한 트랜잭션 단위로 데이터 베이스에서 발생한 작업을 저장, 삭제하는 일이다.
- - 자동 COMMIT : DDL(Create, Alter, Drop), DCL(Grant, Revoke)
- - 자동 ROLLBACK : 비정상적인 종료, system failure
SQL> DELETE FROM emp WHERE empno = 7521 ; 한 개의 행이 삭제 되었습니다. SQL> COMMIT; 커밋이 완료 되었습니다 -- 한 개의 행을 삭제하고, COMMIT 문으로 데이터를 영구히 저장했다. -- 이 것은 하나의 트랜잭션이 여기서 종료되고, 새로운 트랜잭션이 발생하는 것을 말한다. SQL> SELECT empno FROM emp WHERE empno = 7521; 선택된 레코드가 없습니다. SQL> INSERT INTO emp(empno, ename, hiredate) VALUES (9000, 'test', sysdate ); 한 개의 행이 작성되었습니다. SQL> COMMIT; 커밋이 완료 되었습니다. SQL> DELETE FROM emp WHERE empno = 9000; 한 개의 행이 삭제 되었습니다. SQL> SELECT empno FROM emp WHERE empno = 9000 ; 선택된 레코드가 없습니다. -- 위의 예제 처럼 empno가 9000번인 데이터를 등록 한 후 COMMIT으로 데이터를 저장한 다음에 데이터를 다시 삭제한 후 SELECT를 하면 데이터가 검색되지 않는 것을 알 수 있다. -- 하지만 다른 유저에서는 커밋이나 롤백을 하기 전까지 이전에 등록한 empno가 9000번인 데이터를 조회하면 데이터가 검색 된다. -- 데이터베이스에서의 이런 기능을 읽기 일관성이라고 합니다. SQL> ROLLBACK ; 롤백이 완료되었습니다. -- 이전에 트랜잭션(커밋)이 발생하고나서 지금 발생한 ROLLBACK문 전까지의 작업의 취소를 말한다. -- 검색을 해보면 커밋이 완료된 시점의 레코드 하나가 검색 된다. SQL> SELECT empno FROM emp WHERE empno = 9000; EMPNO ---------- 9000 한 개의 행이 선택되었습니다.
SAVEPOINT 와 ROLLBACK TO
SAVEPOINT는사용자가 트랜잭션의 작업을 여러개의 세그먼트로 분할할 수 있도록 하는 특별한 작업이다.
SAVEPOINT는 부분적인 롤백을 가능하게 하기 위해 트랜잭션에 대한 중간점을 정의 한다.
SQL> INSERT INTO emp(empno, ename, hiredate) VALUES (10000, 'test2', sysdate ); 한 개의 행이 작성되었습니다. -- SAVEPOINT를 생성 한다. SQL> SAVEPOINT A; 저장점이 생성되었습니다. SQL> INSERT INTO emp(empno, ename, hiredate) VALUES (10001, 'test3', sysdate ); 한 개의 행이 작성되었습니다. SQL> INSERT INTO emp(empno, ename, hiredate) VALUES (10002, 'test4', sysdate ); 한 개의 행이 작성되었습니다. SQL> DELETE FROM emp WHERE empno IN (10000, 10001, 10002); 세 개의 행이 삭제 되었습니다. SQL> SELECT empno, ename FROM emp WHERE empno IN (10000, 10001, 10002); 선택된 행이 없습니다. -- SAVEPOINT까지만 롤백이 시행 된다. SQL> ROLLBACK TO A; 롤백이 완료되었습니다. SQL> SELECT empno, ename FROM emp WHERE empno IN (10000, 10001, 10002); 한 개의 행이 선택되었습니다. EMPNO ENAME --------- --------------- 10000 test2 -- SAVEPOINT까지만 롤백이 실행되었다. -- 그 결과 첫 번재 데이터는 그대로 남고, SAVEPOINT 후에 실행된 데이터 입력은 삭제되었다.
트랜잭션(commit과 rollback)
트랜잭션(TRANSACTION)
- 트랜잭션은 데이터 처리의 한 단위 이다.
- 오라클 서버에서 발생하는 SQL문들이 하나의 논리적인 작업 단위로써 성공하거나 실패하는 일련의 SQL문을 트랙잭션이라 보면 된다.
- 오라클 서버는 트랜잭션을 근거로 데이터의 일관성을 보증 한다.
- 트랜잭션은 데이터를 일관되게 변경하는 DML문장으로 구성된다 (COMMIT, ROLLBACK, SAVEPOINT)
TRANSACTION의 시작
- - 실행 가능한 SQL문장이 제일 처음 실행될 때
TRANSACTION의 종료
- - COMMIT이나 ROLLBACK
- - DDL이나 DCL문장의 실행(자동 COMMIT)
- - 기계 장애 또는 시스템 충돌(crash)
- - deadlock 발생
- - 사용자가 정상 종료
자동 COMMIT은 다음의 경우 발생 한다.
- - DDL,DCL문장이 완료 될 때
- - 명시적인 COMMIT이나 ROLLBACK없이 SQL*Plus를 정상 종료 했을 경우
자동 ROLLBACK은 다음의 경우 발생 한다.
- - SQL*Plus를 비정상 종료 했을 경우
- - 비정상적인 종료, system failure
COMMIT과 ROLLBACK
- - COMMIT : 변경사항 저장
- - ROLLBACK : 변경사항 취소
COMMIT과 ROLLBACK의 장점
- - 데이터의 일관성을 제공 한다.
- - 데이터를 영구적으로 변경하기 전에 데이터 변경을 확인하게 한다.
- - 관련된 작업을 논리적으로 그룹화 할 수 있다.
- - COMMIT, SAVEPOINT, ROLLBACK 문장으로 TRANSACTION의 논리를 제어할 수 있다.
COMMIT이나 ROLLBACK 이전의 데이터 상태
- - 데이터 이전의 상태로 북구가 가능 하다.
- - 현재 사용자는 SELECT문장으로 DML작업의 결과를 확인할 수 있다.
- - 다른 사용자는 SELECT문장으로 현재 사용자 사용한 DML문장의 결과를 확인할 수 없다.
- - 변경된 행은 LOCK이 설정 되어서 다른 사용자가 변경 할 수 없다.
COMMIT 이후의 데이터 상태
- - 데이터베이스에 데이터를 영구적으로 변경
- - 데이터의 이전 상태는 완전히 상실
- - 모든 사용자가 결과를 볼 수 있다.
- - 변경된 행의 LOCK이 해제되고 다른 사용자가 변경할 수 있다.
- - 모든 SAVEPOINT는 제거 된다.
CROSS JOIN, INNER JOIN, NATURAL JOIN, USING, ON
CROSS JOIN
Cartesian Product 값을 얻을때 사용 한다.
-- 아래 SQL문장을 실행해 보자 SQL> SELECT ename FROM emp CROSS JOIN dept
INNER JOIN
- - 일반 조인시 ,(콤마)를 생략하고 INNER JOIN을 추가하고, WHERE절 대신 ON절을 사용하면 된다.
- - INNER는 생략 가능 하다.
- - 아래 두 조인의 결과 값은 같다.
-- INNER JOIN을 사용한 문장 SQL> SELECT e.empno, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno; -- 일반적인 SQL 문장 SQL> SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno;
NATURAL JOIN
- - Equi Join과 동일 하다고 보면 된다.
- - 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 된다.
- - 동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 한다.
- - 동일한 컬럼이 두개 이상일 경우 JOIN~USING 문장으로 조인되는 컬럼을 제어 할 수 있다.
- - 아래 두 조인의 결과 값은 같다.
-- NATURAL JOIN을 사용한 SQL 문장. SQL> SELECT empno, ename, deptno FROM emp NATURAL JOIN dept -- 일반적인 SQL 문장 SQL> SELECT e.empno, e.ename, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno
JOIN ~ USING
- - NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되는데, USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있다.
- - USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 한다.
-- 일반적인 사용 방법 SQL> SELECT e.empno, e.ename, deptno FROM emp e JOIN dept d USING(deptno)
ON 구문
- - 조인 조건을 지정 할 수 있다.
- - 모든 논리 연산 및 서브쿼리를 지정할 수 있다.
-- 테스트를 위해 scott유저에서 아래 insert문장을 실행시킨다. SQL> INSERT INTO bonus(ename, job, sal) VALUES('SMITH','CLERK',500); -- ON절 사용 예제 (multi-table joins) SQL> SELECT e.empno, e.ename, e.sal FROM emp e JOIN dept d ON (e.deptno=d.deptno) JOIN bonus b ON (b.ename = e.ename) WHERE e.sal IS NOT NULL
Outer Join (LEFT, RIGHT, FULL OUTER JOIN)
Outer(외부) Join
- - Equi Join 문장들의 한 가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개 컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 반환하지 못하는 것이다.
- - 정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 Outer Join을 사용 한다. Outer Join 연산자는 "(+)" 이다.
- - 조인시킬 값이 없는 조인측에 "(+)"를 위치 시킨다.
- - Outer Join 연산자는 표현식의 한 편에만 올 수 있다.
Outer Join 예제
-- 예제1) 일반 조인의 경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno = b.deptno DEPTNO DEPTNO ------ ---------- 10 10 20 20 30 30 -- 예제2) Outer Join을 했을 경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno(+) = b.deptno DEPTNO DEPTNO ------- -------- 10 10 20 20 30 30 40 -- 다음의 쿼리를 한번 잘 살펴보기 바란다. SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno(+) = b.deptno AND a.ename LIKE '%'; DEPTNO DEPTNO ---------- ---------- 10 10 20 20 30 30 -- 쿼리 결과를 잘 보면 Outer Join이 되지 않은 것을 알 수 있다. -- 위 쿼리를 Outer Join 시키기 위해서는 아래와 같이 고쳐야 한다. SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno(+) = b.deptno AND a.ename(+) LIKE '%' DEPTNO DEPTNO ---------- ---------- 10 10 20 20 30 30 40 -- Outer Join 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 -- 똑같이 Outer Join 연산자인 (+)를 해주어야 한다.
표준 Outer Join
Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 한다.
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 할 수 있다.
LEFT OUTER JOIN
오른쪽 테이블(아래 예제에서 emp 테이블)에 조인시킬 컬럽의 값이 없는 경우 사용한다.
SQL> SELECT DISTINCT(e.deptno), d.deptno FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno;
RIGHT OUTER JOIN
왼쪽 테이블(아래 예제에서 emp 테이블)에 조인시킬 컬럽의 값이 없는 경우 사용한다.
SQL> SELECT DISTINCT(e.deptno), d.deptno FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
FULL OUTER JOIN
양쪽 테이블에 다 Outer Join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 한다.
SQL>SELECT DISTINCT(a.deptno), b.deptno FROM emp a FULL OUTER JOIN dept b ON a.deptno = b.deptno;
Equi Join, Non_Equi Join, Self Join
조인(Join)
조인(Join) 이란?
- - 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법 이다.
- - 보통 둘 이상의 행들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.
- - 그러므로 두 개의 테이블을 SELECT문장 안에서 조인 하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서
- 공유 되어야 한다.
조인 방법
- - Equi Join(동등 조인, 내부조인)
- - Non-equi Join
- - Outer Join
- - Self Join
Cartesian Product(카티션 곱)
- - 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환 되는 현상
- - Cartesian Product는 다음과 같은 경우에 발생 한다.
-
- * 조인 조건을 정의하지 않았을 경우
- * 조인 조건이 잘못된 경우
- * 첫 번째 테이블의 모든 행들이 두 번째 테이블의 모든 행과 조인이 되는 경우
- * 테이블의 개수가 N이라면 Cartesian Product를 피하기 위해서는 적어도 N-1개의 등가 조건을 SELECT 문안에
- 포함시켜서 다른 테이블 안에 있는 각 테이블의 컬럼이 적어도 한번은 참조되도록 해야 한다.
Equi Join
- - 조건절 Equality Condition(=)에 의하여 조인이 이루 진다.
- - Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.
-- WHERE 절에 조인 조건을 작성한다. SQL> SELECT e.ename, d.dname FROM emp e , dept d WHERE e.deptno = d.deptno;
Non-Equi Join
- - Non-equi Join은 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 조인조건은
- 동등( = )이외의 연산자를 갖는다.
- - BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN
-- Non-Equi Join 예제 SQL> SELECT e.ename,e.sal,s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
Self Join
- - Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다.
- - 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.
-- 사원의 매니저명을 조회하는 Self Join 예제 SQL> SELECT e.ename, a.ename "Manager" FROM emp e, emp a WHERE e.empno = a.mgr;
SELECT문 및 연산자
SELECT문은 데이터베이스로부터 저장되어 있는 데이터를 검색하는데 사용 한다.
SELECT 문법
- - DISTINCT : 중복되는 행을 제거하는 옵션.
- - * : 테이블의 모든 column을 출력.
- - alias : 해당 column에 대해서 다른 이름을 부여할 때 사용.
- - table_name : 질의 대상 테이블 명
- - WHERE : 조건을 만족하는 행들 만 검색
- - condition : column, 표현식, 상수 및 비교 연산자
- - ORDER BY : 질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)
SQL문의 작성 방법
- - SQL 문장은 대소문자를 구별하지 않는다.
- - SQL 문장은 한 줄 또는 여러 줄에 입력될 수 있다.
- - 일반적으로 키워드는 대문자로 입력한다. 다른 모든 단어, 즉 테이블 이름, 열 이름은 소문자로 입력한다.(권장)
- - 가장 최근의 명령어 한 개가 SQL buffer에 저장 된다.
- - SQL문 마지막 절의 끝에 ";"를 기술하여 명령의 끝을 표시 한다.
-- empno와 ename 은 각각 사번과 성명이라는 컬럼 별칭(alias)으로 만들어 출력 -- alias를 사용할 때 as라는 키워드를 사용해도 되고, 생략 할 수도 있다. SQL> SELECT empno 사번, ename 성명 FROM emp WHERE deptno = 10 사번 성명 ---------- --------------- 7782 CLARK 7839 KING 7934 MILLER
WHERE절에 사용될 수 있는 SELECT 연산자
| 연산자 | 설 명 |
|---|---|
| BETWEEN a AND b | a와 b사이의 데이터를 출력.(a, b값 포함) |
| IN (list) | list의 값 중 어느 하나와 일치하는 데이터를 출력 |
| LIKE | 문자 형태로 일치하는 데이터를 출력(%, _사용) |
| IS NULL | NULL값을 가진 데이터를 출력 합니다. |
| NOT BETWEEN a AND b | a와 b사이에 있지않은 데이터를 출력(a, b값 포함하지 않음) |
| NOT IN (list) | list의 값과 일치하지 않는 데이터를 출력 |
| NOT LIKE | 문자 형태와 일치하지 않는 데이터를 출력 |
| IS NOT NULL | NULL값을 갖지 않는 데이터를 출력 |
IN, NOT IN 연산자
IN 연산자
-- 사번이 7900, 7934번인 사원의 사번과 성명 출력 SQL> SELECT empno, ename FROM emp WHERE empno IN (7900, 7934) ; EMPNO ENAME --------- ------------- 7934 MILLER 7900 JAMES
NOT IN 연산자
-- 사번이 7900, 7934번이 아닌 사원의 사번과 성명 출력 SQL> SELECT empno, ename FROM emp WHERE empno NOT IN (7900, 7934); EMPNO ENAME -------- -------------- 7369 SMITH 7499 ALLEN 7698 BLAKE ....
BETWEEN 연산자
AND를 이용해 두 조건을 결합한 검색과 같은 결과값을 보여 준다.
-- 급여가 3000에서 5000사이인 사원만 조회 SQL> SELECT empno, ename FROM emp WHERE sal BETWEEN 3000 AND 5000; EMPNO ENAME ------- ------ 7788 SCOTT 7839 KING 7902 FORD
LIKE 연산자
- - 검색 STRING 값에 대한 와일드 카드 검색을 위해서 LIKE 연산자를 사용.
- - % : 여러 개의 문자열을 나타내는 와일드 카드
- - _ : 단 하나의 문자를 나타내는 와일드 카드
- - ESCAPE : 와일드 카드 문자를 일반문자 처럼 사용하고 싶은 경우에 사용.
- ex) WHERE name LIKE '%a\_y%' ESCAPE '\'
- - LIKE 연산자는 대소문자를 구분 한다.
- - UPPER() 함수를 이용해 대소문자 구분없이 출력 할 수 있다.(인덱스 성능문제 발생, 함수기반 인덱스 사용..)
| 구 분 | 설 명 |
|---|---|
| LIKE 'A%' | 'A'로 시작하는 데이터만 검색 |
| LIKE '%A' | 'A'로 끝나는 테이터들만 검색 |
| LIKE '%KIM%' | 'KIM' 문자가 있는 데이터 들만 검색 |
| LIKE '%K%I%' | 'K' 문자와 'I'문자가 있는 데이터 들만 검색 |
| LIKE '_A%' | 'A'문자가 두 번째 위치한 데이터 들만 검색 |
LIKE 연산자 예제
-- 'K' 문자가 들어있는 사원 정보 조회 -- UPPER() 함수는 k를 대문자로 변환하여 'K'로 인식 SQL> SELECT empno, ename FROM emp WHERE UPPER(ename) LIKE '%K%'; EMPNO ENAME ------- ----------- 7698 BLAKE 7782 CLARK 7839 KING -- '_'를 이용한 LIKE검색 SQL> SELECT empno, ename FROM emp WHERE UPPER(ename) LIKE '_I%' EMPNO ENAME ------- ---------- 7839 KING 7934 MILLER
ORDER BY
ORDER BY 절은 데이터의 정렬을 위해 사용합니다.(ASC[오름차순], DESC[내림차순])
-- 아래 두 개의 쿼리는 동일한 결과를 반환 한다. -- 이름을 ASC로 정렬 SQL> SELECT empno, ename ROM emp WHERE deptno = 30 ORDER BY ename ASC; -- 두 번째 컬럼을 디폴트(ASC) 순으로 정렬 SQL> SELECT empno, ename FROM emp WHERE deptno = 30 ORDER BY 2 EMPNO ENAME -------- --------- 7499 ALLEN 7698 BLAKE 7900 JAMES ...
SELECT문 및 연산자 관련 강좌 정보
- - 대용량 데이터베이스 솔루션 2 - 논리합(OR, IN) 연산자의 이해
- - 대용량 데이터베이스 솔루션 2 - IN을 활용한 액세스 효율화
- - 대용량 데이터베이스 솔루션 2 - 중복된 IN 조건의 활용
- - 대용량 데이터베이스 솔루션 2 - IN을 고려한 결합 인덱스의 선정
데이터의 삽입, 수정, 삭제
INSERT
INSERT명령어는 테이블 안에 데이터를 삽입하는 역할을 한다.
INSERT 문법
- - 실제 데이터는 VALUES 괄호()안에 입력하고 문자열은 단일 따옴표(' ')로 둘러싼다.
- - 각각의 테이터 구분은 ","로 한다.
- - 테이블 이름 옆에 ()생략시에는 자동으로 모든 컬럼을 VALUES()안에 입력 시킨다.
INSERT 예제
-- 모든 데이터를 입력할 경우 SQL> INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20); -- 원하는 데이터만 입력할 경우 SQL> INSERT INTO dept (deptno, dname) VALUES(10, 'ACCOUNTING' ); -- SELECT 문장을 이용한 INSERT SQL> INSERT INTO dept2 SELECT * FROM dept;
UPDATE
테이블 안의 데이터를 수정 한다.
-- 사원번호가 7902번인 사원의 부서 번호를 30으로 수정 SQL> UPDATE emp SET deptno = 30 WHERE empno = 7902; -- 부서번호 20의 사원들 급여가 10% 인상됨 SQL> UPDATE emp SET sal = sal * 1.1 WHERE deptno = 20; -- 모든 사원의 입사일을 오늘로 수정 SQL> UPDATE emp SET hiredate = SYSDATE
DELETE
사용하지 않는 데이터를 삭제 한다.
-- 사원번호가 7902번인 사원의 데이터를 삭제. SQL> DELETE FROM emp WHERE empno = 7902 ; -- 평균급여보다 적게 받는 사원 삭제 SQL> DELETE FROM emp WHERE sal < (SELECT AVG(sal) FROM emp); -- 모든 행이 삭제 SQL> DELETE FROM emp;
테이블의 관리
테이블 컬럼의 관리
테이블의 컬럼은 ADD, MODIFY, DROP연산자를 통해서 관리 할 수 있습니다.
ADD 연산자
테이블에 새로운 컬럼을 추가 할 때 사용 한다.
-- VARCHAR2의 데이터 형을 가지는 addr 컬럼을 emp 테이블에 추가 SQL> ALTER TABLE emp ADD (addr VARCHAR2(50));
MODIFY 연산자
테이블의 컬럼을 수정 하거나 NOT NULL컬럼으로 변경 할 수 있으며, 컬럼이 이미 테이터를 가지고 있을 경우 다른 데이터형으로 변경이 불가능 하다.
-- ename 컬럼을 VARCHAR2 50자리로 수정한 예제. SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50)); SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL);
DROP 연산자
테이블 컬럼을 삭제 하거나, 테이블의 제약 조건을 삭제 할 때 사용 한다.
-- 컬럼의 삭제 문법 SQL> ALTER TABLE table_name DROP COLUMN column_name -- 제약 조건의 삭제 예제 SQL> ALTER TABLE emp DROP PRIMARY KEY ; -- CASCADE 연산자와 함께 사용하면 외래키에 의해 참조되는 기본키도 삭제 할 수 있다. SQL> ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;
기존 테이블의 복사
- 기존 테이블을 부분, 또는 완전히 복사할 때에 서브쿼리를 가진 CREATE TABLE 명령어를 사용해서 쉽게 복사 할 수 있다.
- 하지만 제약 조건, 트리거, 그리고 테이블 권한은 새로운 테이블로 복사되지 않는다.
- 제약조건은 NOT NULL 제약 조건만 복사 된다.
-- 한번 실습해 보세요. SQL> CREATE TABLE emp2 AS SELECT * FROM emp;
테이블의 테이블스페이스 변경
Oracle 8i부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로 쉽게 테이블의 테이블스페이스를 변경 할 수 있다.
-- 한번 실습해 보세요. (test라는 테이블스페이스가 있어야 겠죠) SQL> ALTER TABLE emp MOVE TABLESPACE test;
테이블의 TRUNCATE
- - 테이블을 Truncate하면 테이블의 모든 행이 삭제되고 사용된 공간이 해제 된다.
- - TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않는다.
- - DELETE명령으로 데이터를 지우면 롤백명렁어로 복구 할 수 있지만, TRUNCATE로 데이터를 삭제하면 롤백을 할 수가 없다.
- - 행당 인덱스도 같이 잘려 나간다.
- - 외래키가 참조중인 테이블은 TRUNCATE할 수 없다.
- - TRUNCATE 명령을 사용하면 삭제 트리거가 실행되지 않는다.
테이블의 삭제 (DROP TABLE)
-- emp 테이블 삭제 SQL> DROP TABLE emp; -- CASCADE CONSTRAINT는 외래키에 의해 참조되는 기본키를 포함한 테이블일 경우 -- 기본키를 참조하던 외래 키 조건도 같이 삭제 한다. SQL> DROP TABLE emp CASCADE CONSTRAINT;
관련자료
오라클 데이터 타입
- 관계형 데이터베이스에서 제공하는 데이터 타입은 대개 CHAR, VARCAHR2, NUMBER, DATE, LONG, RAW, LONG RAW, ROWID 타입으로 분류한다.
- 원하는 정보를 가장 효율적으로 처리하기 위해서 적절한 데이터 타입의 선정이 필요 하다.
- 적절하지 못한 데이터 타입은의 선정은 수행속도를 나쁘게 하고 불필요한 자원을 낭비를 가져온다.
VARCHAR2 타입
- - 가변길이 문자형 데이터 타입
- - 최대 길이 : 2000 바이트(반드시 길이 지정)
- - 다른 타입에 비해 제한이 적다
- - 일부만 입력시 뒷부분은 NULL
- - 입력한 값의 뒷부분에 있는 BLANK도 같이 입력
- - 전혀 값을 주지 않으면 NULL 상태 입력
- - 지정된 길이보다 길면 입력시 에러 발생
- - 컬럼 길이의 편차가 심한 경우, NULL 로 입력되는 경우가 많은 경우 VARCHAR2 사용
NUMBER 타입
- - 숫자형 데이타 타입, 음수, ZERO, 양수 저장
- - 전체 자리수는 38자리를 넘을 수 없다
- - 소수점이 지정되지 않았을 때 소수점이 입력되거나, 지정된 소수점자리 이상 입력되면 반올림되어 저장
- - 지정한 정수 자리수 이상 입력시 에러 발생
- - 연산이 필요한 컬럼은 NUMBER타입으로 지정한다.
- - NUMBER(p,s) 로 지정시 p는 s의 자리수를 포함한 길이므로 감안하여 P의 자리수를 결정
- - NUMBER 타입은 항상 가변길이므로 충분하게 지정할 것
DATE 타입
- - 일자와 시간을 저장하는 날짜형 타입
- - 일자나 시간의 연산이 빈번한 경우 사용
- - 포함정보 : 세기, 년도, 월, 일, 시간, 분, 초
- - NLS_DATE_FORMAT을 이용하여 국가별 특수성을 해결
- - 특별히 시간을 지정하지 않으면 00:00:00로 입력 됨
- - 특별히 일자를 지정하지 않았다면 현재월의 1일로 지정 됨
- - SYSDATE는 현재일과 시간을 제공
LONG 타입
- - 2기가 바이트의 가변길이 문자 저장
- - VARCHAR2와 유사한 특징을 가진나 아래와 같은 제한사항이 있다.
-
- * 하나의 테이블에 하나의 LONG 타입만 사용 가능
- * (NOT) NULL을 제외한 다른 제약 조건은 지정할 수 없다
- * 인덱스를 만들 수 없다
- * PROCEDURE 나 Stored FUNCTION에서 LONG 타입의 변수를 받을 수 없다
- * Stored FUNCTION은 LONG 타입 출력불가
- * SELECT문 내에서 WHERE, GROUP BY, ORDER BY, CONNECT BY, DISTINCT불가
- * SQL Function(SUBSTR,REPLACE...) 사용 불가
- * CREATE TABLE .. AS SELECT.. 사용불가
RAW, LONG RAW, ROWID 타입
- - 그래픽 IMAGE 나 디지탈 SOUND를 저장
- - HEXA-DECIMAL 형태로 RETURN
- - RAW는 VARCHAR2와 유사
- - LONG RAW는 LONG과 유사하나 아래와 같은 제한사항이 있다.
-
- * 저장과 추출만 가능하고, DATA를 가공할 수 없다.
- * LONG RAW는 LONG과 같은 제한 사항을 같는다.
참고자료
-
- - 오라클클럽 위키 제3장 데이터 타입 및 길이의 결정
- - Oracle Database 9i - Data Type
- - Oracle Database 10g - Data Type
- - Oracle Database 11g - Data Type
테이블의 제약조건
제약조건 (Constraint) 이란?
제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 된다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 이다.
- - 제약조건은 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 이다.
- - 모든 제약조건은 데이터 사전(DICTIONARY)에 저장 된다.
- - 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있다.
- - 표준 객체 명명법을 따르는 것이 좋다.
- - 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를 통해서도 추가가 가능하다.
- - NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능하다.
NOT NULL 조건
컬럼을 필수 필드화 시킬 때 사용 한다.
-- NOT NULL제약조건을 설정하면 ename 컬럼에는 꼭 데이터를 입력해야 한다. -- emp_nn_ename을 제약조건 명으로 설정 하였다. SQL> CREATE TABLE emp( ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL ); -- 제약조건은 USER_CONSTRAINTS 뷰를 통해서 확인 할 수 있다. SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='EMP' ; CONSTRAINT_NAME ----------------------- emp_nn_ename
UNIQUE 조건
데이터의 유일성을 보장(중복되는 데이터가 존재할수 없다) 되고, 자동으로 인덱스가 생성 된다.
-- deptno 컬럼에 UNIQUE 제약조건 생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno); -- 제약 조건의 삭제 SQL> ALTER TABLE emp DROP CONSTRAINT emp_uk_deptno;
CHECK 조건
컬럼의 값을 어떤 특정 범위로 제한할 수 있다.
-- comm 컬럼에 1에서 100까지의 값만을 가질수 있는 체크조건 생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK (comm >= 1 AND comm <= 100); -- 제약 조건의 삭제 SQL> ALTER TABLE emp DROP CONSTRAINT emp_ck_comm; -- 10000,20000,30000,40000,50000의 값만을 가질수 있는 체크조건 생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK comm IN (10000,20000,30000,40000,50000);
DEFAULT(컬럼 기본값) 지정
데이터를 입력 하지 않아도 지정된 값이 기본으로 입력 된다.
-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다. SQL> CREATE TABLE emp( ... (컬럼생략) ..., hiredate DATE DEFAULT SYSDATE );
PRIMARY KEY 지정
- - 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.
- - 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.
- - UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.
- - INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)
-- PRIMARY KEY 생성 예제 SQL> CREATE TABLE emp( empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY );
FOREIGN KEY(외래 키)지정
- - 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.
- - 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.
- - 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.
- - ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.
-- emp 테이블의 deptno 컬럼이 dept 테이블에 deptno 컬럼을 참조하는 외래키 생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);
제약 조건의 확인
- - USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회.
- - USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건 조회.
SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME, DECODE(B.CONSTRAINT_TYPE, 'P','PRIMARY KEY', 'U','UNIQUE KEY', 'C','CHECK OR NOT NULL', 'R','FOREIGN KEY') CONSTRAINT_TYPE, A.CONSTRAINT_NAME FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B WHERE A.TABLE_NAME = UPPER('&table_name') AND A.TABLE_NAME = B.TABLE_NAME AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME ORDER BY 1; -- 테이블 명을 입력 하면 됩니다. table_name의 값을 입력하십시오: emp2 COLUMN_NAME CONSTRAINT_TYPE CONSTRAINT_NAME ------------------- ----------------- -------------- DEPTNO CHECK OR NOT NULL SYS_C001362 FOREIGN KEY EMP2_FK_DEPTNO EMPNO PRIMARY KEY EMP2_PK_EMPNO ENAME CHECK OR NOT NULL EMP2_NN_ENAME MGR UNIQUE KEY EMP2_UP_MGR
테이블의 생성
테이블은 실제로 데이터들이 저장되는 곳 이라고 생각하면 쉽게 이해 할 수 있으며, CREATE TABLE 명령어를 이용해서 테이블을 생성 할 수 있다.
테이블이란?
- - 테이블은 데이타베이스의 기본적인 데이타 저장 단위 이다.
- - 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 된다.
- - 관계형 데이타베이스가 아닌 예전의 데이타 베이스 용어에서는 파일과 테이블이, 필드와 컬럼이, 그리고 레코드와 행이 동일시 되었다.
- - 테이블은 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있다. 예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능하다.
- - 테이블은 두 엔티티간의 관계를 표현할 수 있다. 즉 테이블은 고용자와 그들의 작업 숙련도 혹은 제품과 주문과의 관계를 표현하는데 사용될 수 있다.
- - 테이블내에 있는 외래 키 (ForeIgn Key)는 두 엔티티 사이의 관계를 표현하는데 사용 된다.
- - 컬럼 : 테이블의 각 컬럼은 엔티티의 한 속성을 표현 한다
- - 행(ROW, 레코드) : 테이블의 데이타는 행에 저장 된다
- - 테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. (공백 사용 불가능)
- - 테이블의 컬럼 이름은 30자를 초과할수 없고, 예약어를 사용할수 없다. (버전없이 되면서 컬럼수 제한 변경 됨)
- - 오라클 테이블 한 계정안에서 테이블 이름은 다른 테이블 이름과 달리 유사해야 한다.
- - 한 테이블 안에서 컬럼이름은 같을 수 없으며 다른 테이블에서의 컬럼이름과는 같을수 있다.
테이블 생성 문법
- - schema : 테이블의 소유자
- - table_name : 테이블 이름
- - column : 컬럼의 이름
- - datatype : 컬럼의 데이터 유형
- - TABLESPACE : 테이블이 데이터를 저장 할 테이블스페이스
- - PCTFREE : 블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정 한다.
- - PCTUSED : 테이블 데이터가 저장될 블록의 행 데이터 부분의 크기를 퍼센트지로 지정 한다. PCTFREE에 의해 지정된 크기만큼 Block이 차면 PCTUSED 값보다 작아져야 새로운 행 삽입이 가능 하다.
- - INITRANS : 하나의 데이터 블록에 지정될 초기 트랜잭션의 값을 지정 한다.
- - MAXTRANS : 하나의 데이터 블록에 지정될 수 있는 트랜잭션 최대 수를 지정 한다.
- - STORAGE : 익스텐트 스토리지에 대한 값을 지정 한다.
- - LOGGING : 테이블에 대해 이후의 모든 작업이 리두 로그 파일 내에 기록 되도록 지정 한다
- - NOLOGGING : 리두 로그 파일에 테이블의 생성과 특정 유형의 데이터 로드를 기록하지 않도록 지정 한다.
- - PCTFREE, PCTUSED에 대한 자세한 강좌는 오라클 어드민 강좌의 6. Storage Structure => PCTFREE와 PCTUSED를 참고해 주세요
- 오라클 버전없이 되면서 PCTFREE, PCTUSED 설정 및 관리가 많이 변경 되었습니다.
테이블 생성 예제
테이블 생성시 주의사항- - 테이블 이름을 지정하고 각 컬럼들은 괄호 "()" 로 묶어 지정한다.
- - 컬럼뒤에 데이터 타입은 꼭 지정되어야 한다.
- - 각 컬럼들은 콤마","로 구분되고, 항상 끝은 세미콜론";" 으로 끝난다.
- - 한 테이블 안에서 컬럼이름은 같을수 없으며 다른 테이블에서의 컬럼이름과는 같을 수 있다.
-- emp2와 dept2테이블을 생성하는 예제이다. SQL> CREATE TABLE EMP2( EMPNO NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY, -- (컬럼) (데이터타입) (제약조건) ENAME VARCHAR2(20), JOB VARCHAR2(40), MGR NUMBER, HIREDATE DATE, SAL NUMBER, COMM NUMBER, DEPTNO NUMBER); 테이블이 생성되었습니다. SQL> CREATE TABLE DEPT2( DEPTNO NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY, DNAME VARCHAR2(40), LOC VARCHAR2(50)) ; 테이블이 생성되었습니다.
USER가 소유한 모든 테이블 조회
-- USER_TABLES 데이터사전을 조회 하면 유저가 소유한 테이블을 확인 할 수 있다. SQL>SELECT table_name FROM USER_TABLES; TABLE_NAME ------------ BONUS CRETABLE DEPT DUMMY EMP EMP2 SALGRADE
객체 권한(Object Privileges)
객체권한은 USER가 소유하고 있는 특정 객체를 다른 사용자들이 엑세스 하거나 조작 할 수 있게 하기 위해 생성 한다.
객체권한(Object Privileges) 이란?
- - 테이블이나 뷰, 시퀀스, 프로시저, 함수, 또는 패키지 중 지정된 한 객체에 특별한 작업을 수행 할 수 있게 한다.
- - 객체 소유자는 다른 사용자에게 특정 객체권한을 부여 할 수 있다.
- - PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 한다.
- - 기본적으로 소유한 객체에 대해서는 모든 권한이 자동적으로 획득된다.
- - WITH GRANT OPTION 옵션은 ROLE에 권한을 부여할 때는 사용할 수 없다.
객체에 따른 권한 목록 예
| 객체권한 | 테이블 | 뷰 | 시퀀스 | 프로시저 |
|---|---|---|---|---|
| ALTER | ○ | ○ | ||
| DELETE | ○ | ○ | ||
| EXECUTE | ○ | |||
| INDEX | ○ | |||
| INSERT | ○ | ○ | ||
| SELECT | ○ | ○ | ○ |
객체권한 부여 문법
위의 표에서 맨 왼쪽에 있는 ALTER, DELETE, EXECUTE.. 등등은 object_privilege란에 오면 되고, 맨 윗줄에 있는 테이블, 뷰, 시퀀스, 프로시저 등등은 ON 다음에 있는 object에 입력하면 된다.
- - object_privilege : 부여할 객체권한의 이름
- - object : 객체명
- - user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
- - PUBLIC : 객체권한, 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있다.
- - WITH GRANT OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게 된다.
객체권한 부여 예제
-- scott USER에게 emp테이블을 SELECT, INSERT할 수 있는 권한을 부여했다. -- scott USER도 다른 USER에게 그 권한을 부여 할 수 있다. SQL>GRANT SELECT, INSERT ON emp TO scott WITH GRANT OPTION; 권한이 부여되었습니다.
객체권한의 회수
- - 객체 권한의 철회는 그 권한을 부여한 부여자만이 수행할수 있다.
- - CASCADE CONSTRAINTS : 이 명령어의 사용으로 참조 객체 권한에서 사용 된 참조 무결성 제한을 같이 삭제 할 수 있다.
- - WITH GRANT OPTION으로 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을 부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회가 발생한다.
객체권한 회수 예제
-- scott USER에게 부여한 emp 테이블에 대한 SELECT, INSERT 권한 회수 예제
-- 만약 scott USER가 다른 사용자에게 SELECT, INSERT권한을 부여했으면
그 권한들도 같이 회수가 된다.
SQL>REVOKE SELECT, INSERT
ON emp
FROM scott;
권한이 회수되었습니다.
WITH GRANT OPTION을 사용하여 객체권한 회수
WITH GRANT OPTION을 사용하여 부여한 객체 권한을 취소하면 취소 작업이 연쇄적으로 수행 된다.
시나리오- 1. SCOTT가 STORM에게 WITH GRANT OPTION을 사용하여 emp 테이블의 SELECT 권한을 부여 한다.
- 2. STORM이 emp 테이블의 SELECT 권한을 TEST에게 부여 한다.
- 3. SCOTT가 STORM에게 부여한 emp 테이블의 SELECT 권한을 취소 한다.
- - SCOTT가 STORM에게 부여한 emp 테이블에 대한 SELECT 권한을 취소하면, TEST USER가 emp 테이블을 SELECT할 수 있는 권한도 자동으로 취소가 된다.
객체권한관련 데이터 사전
| 데이터 사전 | 설 명 |
|---|---|
| USER_TAB_PRIVS | 객체권한의 소유자, 객체권한 부여자, 객체권한 피부여자를 볼수 있음 |
| USER_TAB_PRIVS_MADE | 사용자가 부여한 모든 객체권한 |
| USER_TAB_PRIVS_RECD | 사용자가 부여받은 모든 객체권한 |
| USER_COL_PRIVS | 객체권한의 소유자, 객체권한 부여자, 객체권한 피부여자의 컬럼의 객체권한 |
| USER_COL_PRIVS_MADE | 사용자가 부여한 객체 컬럼에 대한 모든 객체권한 |
| USER_COL_PRIVS_RECD | 사용자가 부여받은 객체 컬럼에 대한 모든 객체권한 |
시스템 권한(System Privileges)
오라클에서 권한(Privilege)은 특정 타입의 SQL문을 실행하거나 데이터베이스나 객체에 접근할 수 있는 권리이다.
- - 시스템권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 한다
- - 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미 한다.
- - GRANT 명령은 사용자 또는 ROLE에 대해서 권한을 부여 할 수 있다.
- - REVOKE 명령은 권한을 회수 한다.
- - CREATE SESSION : 데이터 베이스를 연결할 수 있는 권한
- - CREATE ROLE : 오라클 데이터베이스 역할을 생성할 수 있는 권한
- - CREATE VIEW : 뷰의 생성 권한
- - ALTER USER : 생성한 사용자의 정의를 변경할 수 있는 권한
- - DROP USER : 생성한 사용자를 삭제시키는 권한
시스템권한 부여 문법
- - system_privilege : 부여할 시스템권한의 이름
- - role : 부여할 데이터베이스 역할의 이름
- - user, role : 부여할 사용자 이름과 다른 데이터 베이스 역할 이름
- - PUBLIC : 시스템권한, 또는 데이터베이스 역할을 모든 사용자에게 부여할 수 있다.
- - WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게된다.
시스템권한 부여 예제
-- SYS 권한으로 접속한다. SQL>CONN sys/manager AS SYSDBA -- scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고, -- scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있도록 권한 부여. SQL>GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION; 권한이 부여되었습니다.
시스템권한의 회수
시스템권한 회수 예제
-- scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수 한다. SQL>REVOKE CREATE USER, ALTER USER, DROP USER FROM scott; 권한이 회수되었습니다.
WITH ADMIN OPTION을 사용하여 시스템권한 취소
WITH ADMIN OPTION을 사용하여 시스템권한을 부여했어도 시스템권한을 취소 할 때는 연쇄적으로 취소 되지 않는다.
시나리오- 1. DBA가 STORM에게WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템권한을 부여 한다.
- 2. STORM이 테이블을 생성 한다.
- 3. STORM이 CREATE TABLE 시스템권한을 SCOTT에게 부여 한다.
- 4. SCOTT가 테이블을 생성 한다.
- 5. DBA가 STORM에게 부여한 CREATE TABLE 시스템권한을 취소 한다.
- - STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없다.
- - SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있다.
USER의 변경 및 삭제
USER 정보를 변경하기 위해서는 ALTER USER문을 사용한다.
ALTER USER문으로 변경 가능한 옵션
- - 비밀번호
- - 운영체제 인증
- - 디폴트 테이블 스페이스
- - 임시 테이블 스페이스
- - 테이블 스페이스 분배 할당
- - 프로파일 및 디폴트 역할
USER 수정 문법
USER 수정 예제
-- SYS 권한으로 접속한다. C:\> SQLPLUS /NOLOG SQL> CONN / AS SYSDBA -- scott USER의 비밀번호를 수정한다. SQL> ALTER USER scott IDENTIFIED BY lion; 사용자가 변경되었습니다. -- scott USER의 비밀번호가 변경된 것을 확인할 수 있다. SQL> CONN scott/lion 접속되었습니다. SQL> CONN / AS SYSDBA 접속되었습니다. -- scott USER의 비밀번호를 처음처럼 수정한다. SQL> ALTER USER scott IDENTIFIED BY tiger; 사용자가 변경되었습니다.
USER 삭제
CASCADE를 사용하게 되면 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터 삭제되며 모든 스키마 객체들 또한 물리적으로 삭제 된다.
USER 정보의 확인
-- 데이터베이스에 등록된 사용자를 조회하기 위해서는 DBA_USERS라는
데이터사전을 조회하면 된다.
-- SQL*Plus를 실행시켜 SYS계정으로 접속을 한다.
SQL> CONN / AS SYSDBA
SQL> SELECT username, default_tablespace, temporary_tablespace
FROM DBA_USERS;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLES
---------------- ------------------- ----------------
SYS SYSTEM TEMP
SYSTEM TOOLS TEMP
OUTLN SYSTEM SYSTEM
DBSNMP SYSTEM SYSTEM
ORDSYS SYSTEM SYSTEM
ORDPLUGINS SYSTEM SYSTEM
MDSYS SYSTEM SYSTEM
CTXSYS DRSYS DRSYS
SCOTT SYSTEM SYSTEM
TEST TEST SYSTEM
STORM STORM SYSTEM
KJS SYSTEM SYSTEM
위와 같이 유저와 테이블 스페이스에 대한 정보가 화면에 나온다.
USER의 생성
- - 새로운 USER를 생성하기 위해서는 CREATE USER문을 이용하면 된다.
- - USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 한다.
USER 생성 문법
- - user_name : USER 이름
- - BY password : USER가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 USER 로그인시 사용하는 비밀번호 이다.
- - EXTERNALLY : USER가 운영 체제에 의해서 인증되도록 지정한다.
- - DEFAULT TABLESPACE는 USER 스키마를 위한 기본 테이블스페이스를 지정 한다.
- - TEMPORARY TABLESPACE는 USER의 임시 테이블스페이스를 지정한다.
- - QUOTA절을 사용하여 USER가 사용할 테이블스페이스의 영역을 할당한다.
- - PASSWORD EXPIRE : USER가 SQL*PLUS를 사용하여 데이터베이스에 로그인할 때 암호를 재설정 하도록 한다.(USER가 데이터베이스에 의해 인증될 경우에만 적합한 옵션이다.)
- - ACCOUNT LOCK/UNLOCK : USER 계정을 명시적으로 잠그거나 풀 때 사용할 수 있다.(UNLOCK이 기본값이다.)
- - PROFILE : 자원 사용을 제어하고 USER에게 사용되는 암호 제어 처리 방식을 지정하는데 사용된다.
- 여기선 간단한 유저생성에 대해서만 알아보고 자세한 유저관리와 PROFILE 관리는 어드민에서 설명 하겠다.
※ 참고 1
- 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만, 시스템 테이블스페이스에 단편화가 발생할 수 있으므로 USER를 생성할때 임시테이블스페이스를 따로 지정해 주는 것이 좋다.
- 또한 DEFAULT TABLESPACE도 USER를 생성할때 지정해 주지 않으면 기본적으로 시스템 테이블스페이스가 지정이 된다. 하지만 USER를 생성할때 DEFAULT TABLESPACE를 지정을 해서 USER가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 해야 한다.
- 시스템 테이블스페이스는 본래의 목적(모든 데이터 사전 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거등을 저장)을 위해서만 사용되어져야 하지 일반USER의 데이터 저장용으로 사용 되어서는 안된다.
※ 참고 2, 테이블스페이스란?
- - 오라클 서버가 테이터를 저장하는 논리적인 구조이다.
- - 테이블스페이스는 하나 또는 여러개의 데이터파일로 구성되는 논리적인 데이터 저장 구조이다.
- 테이블스페이스에 대한 자세한 내용는 오라클 어드민의 테이블스페이스 강좌에서 학습하도록 하겠다.
USER 생성 예제
-- SQL PLUS를 실행시키고 SCOTT/TIGER로 접속을 한다. SQL>CREATE USER TEST IDENTIFIED BY TEST; 1행에 오류: ORA-01031: 권한이 불충분합니다 -- SCOTT USER는 사용자 생성 권한이 없어서 사용자를 생성할 수 없다. -- DBA Role이 있는 유저로 접속 -- sqlplus / as sysdba 로 접속하셔도 됩니다. SQL>CONN sys/manager AS SYSDBA -- USER를 다시 생성. SQL>CREATE USER TEST IDENTIFIED BY TEST; 사용자가 생성되었습니다.
SQL> CONN TEST/TEST ERROR: ORA-01045: 사용자 TEST는 CREATE SESSION 권한을 가지고있지 않음; -- 새로 생성한 TEST USER는 권한이 없어서 접근할 수가 없다. -- 모든 USER는 권한이 있고 권한에 해당하는 역할만 할 수 있다. -- TEST라는 USER를 사용하기 위해서도 권한을 부여해 주어야 한다. SQL> CONN sys/manager AS SYSDBA 연결되었습니다. SQL> GRANT connect, resource TO TEST ; 권한이 부여되었습니다. SQL> CONN TEST/TEST 연결되었습니다.
SQL의 종류
- 1. DDL (Data Definition
Language) : 데이터베이스
객체(테이블,뷰,인덱스..)의 구조를 정의 합니다.
SQL문 내 용
CREATE
데이터베이스 객체를 생성 합니다.
DROP
데이터베이스 객체를 삭제 합니다.
ALTER
기존에 존재하는 데이터베이스 객체를 다시 정의하는역할을 합니다.
- 2. DML (Data Manipulation
Language) : 데이터의
삽입,삭제,갱신등을 처리
SQL문 내 용
INSERT
데이터베이스 객체에 데이터를 입력 한다.
DELETE
데이터베이스 객체의 데이터를 삭제 한다.
UPDATE
데이터베이스 객체안의 데이터 수정 한다.
- 3. DCL (Data Control Language) : 데이터베이스 사용자의 권한을 제어
SQL문 내 용
GRANT
데이터베이스 객체에 권한을 부여 한다.
REVOKE
이미 부여된 데이터베이스 객체 권한을 취소한다.
'AREA 1 > DB' 카테고리의 다른 글
| DB 설계 개념 (0) | 2011.04.14 |
|---|---|
| Oracle 우편번호 테이블 추가 (0) | 2011.04.08 |
| Oracle scott 계정 활성화/생성 (0) | 2011.04.08 |
| [Linux] Oracle Database 10g Express Edition Installaton on Linux Fedora14 (0) | 2011.04.08 |
| [Windows] Oracle Database 10g Express Edtion Installation on Windows7 (0) | 2011.04.08 |
