오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원 오라클 OCP 오라클자격증 OCP자격증 데이터베이스 DB 데이터베이스관리자 DB관리자 오라클학원 OCP학원
* 데이터 검색
SELECT 칼럼명 , GROUP 함수
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼명
HAVING 조건식
ORDER BY 칼럼이나 표현식 ;
- DISTINCT : 중복행을 제거함.
항상 SELECT 바로 다음에서 기술
DISTINCT뒤에 나타나는 컬럼들은 모두 DISTINCT의 영향를 받음
기본적으로 오름차순 정렬됨
- ALIAS : 나타날때 컬럼에 대하여 다른 이름을 부여함
- GROUP BY : 전체 데이터를 소그룹으로 나눌 칼럼을 명시한다.
- HAVING : GROUP에 대한 조건을 기술한다.
- WHERE절에 사용되는 연산자의 3가지 부류
논리 연산자 - AND : 여러조건을 동시에 만족한다.
- OR : 여러조건들 중 어느 하나라도 만족한다.
- NOT : 조건에 대한 반대를 돌려준다.
논리 비교 연산자 - = : 같다.
- > : 보다 크다.
- >= : 보다 크거나 같다.
- < : 보다 작다.
- <= : 보다 작거나 같다.
SQL 비교 연산자 - BETWEEN a AND b : a와 b사이에 있다.(a,b값 포함)
- IN (list) : list의 값 중 어느 하나와 일치한다.
- IS NULL : NULL값을 가졌다.
LIKE '%문자_문자%‘ : % 는 0개서부터 여러개까지의 문자열
을 나타내는 와일드카드 (wildcard)이고
_ 는 단 하나의 문자를 나타내는 와일드카드입니다.
와일드 카드를 일반문자처럼 쓰고 싶은 경우에는 ESCAPE 옵션을 사용
WHERE name LIKE '%X_Y%' ESCAPE ''
SQL> SELECT name,salary*18 AS 연봉
2 FROM s_emp;
SQL> SELECT name||' '||title
2 FROM s_emp ;
SQL> SELECT name, salary*18 연봉 , title
2 FROM s_emp
3 ORDER BY salary*18 DESC ;
SQL> SELECT name , salary
2 FROM s_emp
3 WHERE salary BETWEEN 1000 AND 1500;
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id IN (110,113) ;
SQL> SELECT name, start_date , title
2 WHERE start_date BETWEEN '01-JAN-91'
3 AND '31-DEC-91' ;
SQL> SELECT dept_id , count(*) 인원수
2 FROM s_emp
3 WHERE dept_id = 110
4 GROUP BY dept_id ;
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id ;
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 WHERE title = '사원'
4 GROUP BY dept_id ;
SQL> SELECT dept_id , title , COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id , title ;
SQL> SELECT title , AVG(salary) , COUNT(*)
2 FROM s_emp
3 GROUP BY title
4 HAVING COUNT(*) > 2 ;
SQL> SELECT title , SUM(salary) 급여총합
2 FROM s_emp
3 WHERE title NOT LIKE ‘%부장’
5 HAVING SUM(salary) > 10000
6 ORDER BY SUM(salary) ;
* SQL*PLUS의 기능
- 버퍼에 있는 명령어 편집하기
A(PPEND) text : line의 끝에 text를 추가함
C(HANGE)/old/new : old를 new로 바꿈
DEL n : n line을 지움
I(NPUT) text : 다음 line에 text를 추가함
L(IST) : 전체 문장을 보여줌
n text : n line전체를 text로 바꿈
R(UN) : buffer에 있는 문장을 실행함(/ 와 같음)
EDIT : buffer에 있는 문장을 파일로 부름(afiedt.buf)
- 파일에 있는 명령어 편집하기
SAVE a : buffer에 있는 내용을 a.sql 파일로 저장
GET a : 파일 a에 있는 내용을 buffer로 부름
START a (=@a) : 파일 a를 실행함
! : UNIX Shell로 나들이
!vi a.sql : 파일 a.sql을 vi편집기로 부름
- 환경설정 : SET 환경변수 값
COLSEP (text) : 칼럼이 표시될때 칼럼간의 구별문자 기본값은 공백
FEEDBACK (off|on) : 선택된 행이 몇행인지를 표시함 기본값은 6행 이상인 경우에 on
HEADING (off|on) : 칼럼에 대한 HEADING를 표시함 기본 값은 on
LINESIZE (n) : 한 라인당 표시되는 문자의 수 기본값은 80
PAGES (n) : 한 페이지당 표시되는 라인수 기본값은 24
PAUSE (off|on|text) : 「ENTER」키를 누를 때마다 화면이 지나감 기본값은 off
TIMING (off|on) : SQL문장이 처리되는데 걸리는 시간을 표시 기본값은 off
SHOW : SET 환경이 어떻게 설정되어 있는지 보는 명령어
SHOW ALL : 전체가 어떻게 설정되어 있는지 보고 싶은 경우
자신이 쓰는 환경을 항상 맞추고 싶으면
login.sql 파일에 SET환경을 설정하면 됩니다.
- 표시형식
COL(UMN) 칼럼이나 ALIAS 옵션
- 옵션의 종류 - CLE(AR) : 칼럼에 지정된 형식을 지움
- FOR(MAT) : 형식 칼럼에 대한 표시 형식과 폭을 바꿈
- HEADING text : 칼럼에 대한 HEADING를 지정함
SQL> COL name HEADING ‘사원이름’ FORMAT A10
SQL> COL salary FORMAT $9,999,999
SQL> COL salary CLEAR
CLEAR COLUMNS : 지정된 형식을 다 지우고 싶은 경우
- 변수사용
&변수 사용 : 사용자가 원하는 데이터를 사용할 때마다 입력하고 싶은 경우에
치환변수를 사용. 사용시에는 &기호를 이용
&&기호를 사용하면 한번 입력받은 변수값을 다시 적용할 수 있음
&기호를 이용하여 조건, 칼럼명, 테이블명, 전체 SELECT구문도 받을 수 있음
SQL> SELECT id,name,dept_id
2 FROM s_emp
3 WHERE dept_id = &부서번호 ;
Enter value for 부서번호: 112
old 3: WHERE dept_id = &부서번호
new 3: WHERE dept_id = 112
ACCEPT를 사용한 변수
사용자가 SELECT구문을 사용하기 전에 미리 변수를 설정해 놓는 경우에
ACCEPT를 사용할 수 있습니다.
> ACCEPT 변수명 데이터타입 FORMAT PROMPT text HIDE
데이터타입 : NUMBER,CHAR,DATE 중에서 지정
FORMAT : A10, 9,999등의 표시 형식지정
PROMPT text : 사용자가 데이터를 입력할 때 보여주는 문장
HIDE : password등을 지정시 화면에 보이지 않도록 함
SQL> ACCEPT p_salary NUMBER PROMPT ‘급여 입력 :’
SQL> ACCEPT pswd CHAR PROMPT 'Password : ' HIDE
* 단일행 함수
- 문자형 함수
UPPER : 모든 문자를 대문자로 전환
LOWER : 모든 문자를 소문자로 전환
INITCAP : 문자를 단어별로 앞머리는 대문자 나머지는 소문자로 전환
CONCAT : 두 문자열을 합성. ||연산자와 같은 용도로 사용
SUBSTR : 특정문자열의 부분을 선택
LENGTH : 문자열의 길이를 구함
LPAD : 왼쪽 문자 자리를 채움
RPAD : 오른쪽 문자 자리를 채움
LTRIM : 왼쪽 문자를 지움
RTRIM : 오른쪽 문자를 지움
TRANSLATE : 특정 문자열을 대체
REPLACE : 특정 문자열을 대신
UPPER(문자값) : UPPER('Oracle Server')
→ ORACLE SERVER
LOWER(문자값) : LOWER('Oracle Server')
→ oracle server
INITCAP(문자값) : INITCAP('Oracle Server')
→ Oracle Server
CONCAT(문자값1, 문자값2) : CONCAT('Oracle',' Server')
→ Oracle Server
SUBSTR(문자값, a, b)
a 선택할 문자열의 시작위치.
음수면 끝에서부터 시작
b 선택할 문자열의 개수.
이 인자는 생략할 수 있으며,
생략할 경우 문자열의 끝까지 선택
: SUBSTR('강남구 역삼동‘,5,2)
→ 역삼
LENGTH(문자값1, a, 문자값2) : LENGTH(‘홍길동’)
→ 3
LPAD(문자값1, a, 문자값2)
RPAD(문자값1, a, 문자값2)
a : 전체 채울 자리수
문자값2 : 채울 문자
생략할 수 있으며, 생략되면
공백값임
: LPAD(‘홍길동’,10 ‘*’)
→ ****홍길동
LTRIM(문자값1, 문자값2)
RTRIM(문자값1, 문자값2)
문자값1에서 왼쪽(오른쪽)에서부터
더이상 문자값2를 만나지 않을 때까지 지움
: LTRIM('XXAXBA','X')
→ AXBA
TRANSLATE(문자값, a, b)
a 대체하고 싶은 문자(from)
b 대체할 결과의 문자(to)
: TRANSLATE('AABBA','B','C')
→ AACCA
REPLACE (문자값, a, b)
a 바꾸고 싶은 문자(from)
b 바꿀 결과의 문자(to)
: REPLACE ('JACK and JUE','J','BL')
→ BLACK and BLUE
- 숫자형 함수
ROUND : 숫자를 반올림
TRUNC : 숫자를 절사
MOD : 나누기 연산에서 나머지 구함
POWER : 거듭제곱
SQRT : 제곱근
SIGN : 양수인지 음수인지 0인지를 구벌함
CHR : ASCII 값에 해당하는 문자를 구함
ROUND(숫자값, a), TRUNC(숫자값, a)
a 숫자값을 반올림(버림)하여 a자리까지
돌려줌.a가 양수이면 소수이하자리를,
음수이면 정수 부분 자리임
생략할 수 있으며, 생략하면 0
: ROUND(35.735,2)→35.74
MOD(숫자값, a )
a 숫자값을 나누기 할 수 있음
: MOD(7,2)→1
POWER(숫자값1, 숫자값2) : POWER(3,2)→9
SQRT (숫자값) : SQRT(25)→5
SIGN(숫자값) : SIGN(-15)→-1
CHR(숫자값) : CHR(65)→A
- 날짜형 함수
SYSDATE : 현재 시스템의 날짜 및 시간을 구함
LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함
MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함
ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함
ROUND : 날짜에 대한 반올림
TRUNC : 날짜에 대한 버림
SYSDATE : SYSDATE → 10-MAY-99
LAST_DAY(날짜값) : LAST_DAY('17-FEB-98') → 28-FEB-98
MONTHS_BETWEEN(날짜값1, 날짜값2) : MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323
ADD_MONTHS(날짜값, 숫자값) : ADD_MONTHS('22-JUL-95',21) → 22-APR-97
ROUND(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.
ROUND(SYSDATE,'MONTH') → 01-MAY-99
TRUNC(날짜값, 자리수) : 현재 날짜가 1999년 5월 10일이라고 가정하자.
TRUNC(SYSDATE,'YEAR') → 01-JAN-99
- 날짜에 대한 산술연산
날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산
- 변환형 함수
TO_CHAR : 숫자나 날짜를 문자열로 변환
TO_NUMBER : 문자를 숫자로 변환
TO_DATE : 문자를 날짜로 변환
- TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소
9 : 일반적인 숫자를 나타냄
0 : 앞의 빈자리를 0으로 채움
$ : dollar를 표시함
L : 지역 통화 단위(ex )
. : 소숫점을 표시함
, : 천단위를 표시함
- TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소
SCC : 세기를 표시 S는 기원전(BC)
YEAR : 연도를 알파벳으로 spelling
YYYY : 4자리 연도로 표시
YY : 끝의 2자리 연도로 표시
MONTH : 월을 알파벳으로 spelling
MON : 월의 알파벳 약어
MM : 월을 2자리 숫자로 표시
DAY : 일에 해당하는 요일
DY : 일에 해당하는 요일의 약어
DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시
HH , HH24 : (1-12) , (0-23)중의 시간을 표시
MI : 분을 표시
SS : 초를 표시
AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시
TO_CHAR(문자값,‘형식’)
숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000
숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25
TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92
TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234
- DECODE 함수
DECODE 함수는 값을 비교하여 해당하는 값을 돌려주는 함수
> DECODE (형식,비교값1,결과치1,비교값2,결과치2,...기본치 )
형식 : 컬럼이나 값
비교값1 : 형식이 비교값1에 맞는지를 비교
결과값1 : 형식이 비교값1에 맞을 때 갖는 값
기본치 : 형식이 비교값1,2,...에 맞지 않을 때 가지는 값
생략될 수 있으며, 생략되면 NULL이다.
SQL> SELECT name,title,DECODE(SUBSTR(title,-2,2),
2 '부장',salary*1.1,
3 '과장',salary*1.07,
4 '사원',salary*1.05,
5 salary) 이번달급여
6 FROM s_emp
7 ORDER BY 3 DESC ;
* 다중행 함수
- 그룹함수
COUNT( a ) : a의 행의 개수를 구함
AVG( a ) : a의 평균을 구함
SUM( a ) : a의 합계를 구함
MIN( a ) : a의 최소값을 구함
MAX( a ) : a의 최대값을 구함
STDDEV( a ) : a의 표준 편차를 구함
VARIANCE( a ) : a의 분산을 구함
COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않습니다.
중복값을 제거하고 싶은 경우는 a의 앞에 DISTINCT를 기술합니다.
MAX , MIN , COUNT를 제외한 그룹함수는 숫자타입의 데이터에만 가능합니다
* JOIN
- EQUIJOIN
컬럼에 있는 값들이 정확하게 일치하는 경우에 =연산자를 사용하여 JOIN하는 방법을 말합니다.
WHERE 절에서 JOIN조건을 기술하도록 합니다.
SELECT 테이블명.컬럼명, 테이블명.컬럼명, ...
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼2 ;
SQL> SELECT s_emp.name, s_emp.dept_id , s_dept.name
2 FROM s_emp , s_dept
3 WHERE s_emp.dept_id = s_dept.id ;
SQL> SELECT e.name 사원명, e.dept_id , d.name 부서명
2 FROM s_emp e, s_dept d
3 WHERE e.dept_id = d.id ;
SQL> SELECT e.name 사원명, d.name 부서명
2 FROM s_emp e, s_dept d , s_region r
3 WHERE e.dept_id = d.id
4 AND d.region_id = r.id
5 AND r.name LIKE '%서울%‘ ;
- NON-EQUIJOIN
Non-Eqijoin이란 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에
=연산자외의 다른 연산자를 사용하여 JOIN하는 방법을 말합니다
SQL> SELECT e.name, e.salary , g.grade 급여등급
2 FROM s_emp e, salgrade g
3 WHERE e.salary BETWEEN g.losal AND g.hisal ;
- OUTER JOIN
Outer join이란 JOIN조건을 만족하지 않는 경우에도 모든 행들을 다 보고자하는 경우에 JOIN하는 방법
SQL> SELECT e.name 사원명 ,e.id,c.name 고객명
2 FROM s_emp e, s_customer c
3 WHERE e.id (+) = c.sales_rep_id
4 ORDER BY 2;
- SELF JOIN
Self join이란 한 테이블의 행을 같은 테이블에 있는 행과 연결하는 방법으로 같은
테이블을 마치 두개의 테이블인 것처럼 사용하여 JOIN하는 방법을
SQL> SELECT w.id 사번, w.name 사원명 ,
2 m.id 부서장사번, m.name 부서장명
3 FROM s_emp w, s_emp m
4 WHERE w.manager_id = m.id ;
- SET 연산자의 활용
UNION : 각 QUERY결과의 합집합
UNION ALL : 각 QUERY결과의 합집합에 공통부분을 더함
INTERSECT : 각 QUERY결과의 교집합
MINUS : 첫번째 QUERY결과와 두번째 QUERY결과의 차집합
SELECT 칼럼1, 칼럼2 . . .
FROM 테이블1 . . .
SET 연산자
SELECT 칼럼1`, 칼럼2` . . .
FROM 테이블2 . . .
ORDER BY ;
SQL> SELECT name , dept_id , title
2 FROM s_emp
3 WHERE dept_id = 110
4 UNION
5 SELECT name , dept_id , title
6 FROM s_emp
7 WHERE dept_id = 113
8 ORDER BY 1 ;
* SUBQUERY
- SUBQUERY의 문형
SELECT 검색할 컬럼들
FROM 테이블명
WHERE 형식 연산자 (SELECT 검색할 컬럼들
FROM 테이블명
. . . );
SUBQUERY는 괄호로 묶여 있어야 합니다.
SUBQUERY구문에서는 ORDER BY절을 포함할 수 없습니다.
SUBQUERY는 연산자의 오른쪽에 나타나야 합니다.
SUBQUERY에서 사용할 수 있는 연산자의 종류에는
- 단일행 연산자(=, >, >=, <, <=, <>)
- 복수행 연산자 (IN, NOT IN)가 있습니다.
SUBQUERY를 사용할 수 있는 절의 종류
WHERE 절
HAVING절
UPDATE절
INSERT구문의 INTO절
UPDATE구문의 SET절
SELECT나 DELETE의 FROM절
- SINGLE ROW SUBQUERY
SUBQUERY에서 Main Query로 전달되는 행이 단 하나인 경우
이런 경우는 단일 행 연산자를 사용합니다
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id = 김정미가 근무하는 부서;
SQL> SELECT dept_id
2 FROM s_emp
3 WHERE name = '김정미';
SQL> SELECT name, title , dept_id
2 FROM s_emp
3 WHERE dept_id = (SELECT dept_id
4 FROM s_emp
5 WHERE name = ‘김정미’) ;
SQL> SELECT name, salary ,title
2 FROM s_emp
3 WHERE title = (SELECT title
4 FROM s_emp
5 WHERE name = ‘최정선’)
6 ORDER BY salary ;
- MULTI ROW SUBQUERY
SUBQUERY에서 Main Query로 전달되는 행이 여러 개인 경우를 말합니다.
이런 경우는 다중 행 연산자를 사용합니다.
SUBQUERY에서 넘어오는 행이 여러 개이므로, 이때 사용되는 연산자는 IN임을 주의하세요.
SQL> SELECT name, dept_id
2 FROM s_emp
3 WHERE dept_id IN (SELECT id
4 FROM s_dept
5 WHERE region_id =3 ) ;
SQL> SELECT name , dept_id
2 FROM s_emp
3 WHERE dept_id IN
4 (SELECT id
5 FROM s_dept
6 WHERE region_id =
7 (SELECT id
8 FROM s_region
9 WHERE name = ‘서울특별시’) ) ;
- MULTI COLUMN SUBQUERY
SUBQUERY구문을 작성할 때 WHERE절에서 비교하는 컬럼이 하나가 아니라
여러개의 컬럼을 동시에 비교하는 경우를 말하며, 이런 경우를 Pair-wise되었다고 합니다.
Non-Pairwise SUBQUERY
SQL> SELECT name, dept_id, salary
2 FROM s_emp
3 WHERE salary IN (SELECT MIN(salary)
4 FROM s_emp
5 GROUP BY dept_id );
Pairwise SUBQUERY
SQL> SELECT name, dept_id, salary
2 FROM s_emp
3 WHERE (salary,dept_id) IN
4 (SELECT MIN(salary),dept_id
5 FROM s_emp
6 GROUP BY dept_id );
- FROM절에서의 SUBQUERY
한 테이블에 데이터 양이 많은 경우에는 FROM절에 테이블 전체를 기술하여
사용하면 효율이 떨어질 수 있으므로 이런 경우에는 필요한 행과 열만을
선택하여 FROM절에 SUBQUERY로 기술함으로써 효율적인 데이터 검색을 할 수 있습니다.
SQL> SELECT e.name , e.title , d.name
2 FROM (SELECT name ,title , dept_id
3 FROM s_emp
4 WHERE title = ‘사원’) e , s_dept d
5 WHERE e.dept_id = d.id ;
- HAVING절에서의 SUBQUERY
일반적인 조건은 WHERE절에서 기술하지만, GROUP에 대한 조건은 HAVING절에서 기술합니다.
이 때 HAVING의 조건에 기술할 값이 주어져 있지 않은 경우에 모르는 값에 대한 데이터를
검색하기 위하여 SUBQUERY를 사용할 수 있습니다
SQL> SELECT dept_id , AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING AVG(salary) > (SELECT AVG(salary)
5 FROM s_emp
6 WHERE dept_id = 113) ;
- CORRELATED SUBQUERY
Outer Query의 candidate row가 더 이상 남지 않을때까지 반복됩니다.
CORRELATED SUBQUERY인 경우는 Outer Query의 각 행에 대해서 Inner Query가 매번 수행됩니다.
SQL> SELECT name, salary ,dept_id
2 FROM s_emp outer
3 where salary < (SELECT AVG(salary)
4 FROM s_emp
5 WHERE dept_id = outer.dept_id) ;
바로 2번째 LINE의 OUTER라는 테이블 ALLAS를 5번 LINE에서 사용하게 되면
내부적으로 CORRELATE SUBQUERY의 진행방식을 따르게 됩니다.
* 테이블 생성
- ORACLE8 데이터 타입
VARCHAR2(size) : 최대 길이가 size인 가변길이 문자값으로 최소길이는 1,최대길이는 2000
CHAR(size) : 길이가 size인 고정길이 문자값으로 기본길이는 1, 최대길이는 255
NUMBER : 38자리까지 유효한 부동 소수점 숫자
NUMBER(p,s) : 38의 범위 중에서 p의 자릿수까지 유효한 숫자값으로 전체 자릿수 p, 소수점 자릿수 s
DATE : B.C.4712년 1월에서 A.D.4712년 12월 31일 사이의 일자와 시간
LONG : 2GB까지의 가변길이 문자값으로 테이블당 한 개의 LONG열만 허용
RAW와 LONG RAW : 각각 VARCHAR2, LONG과 같지만 이진 데이터를 저장하는데 사용
- 테이블 생성
CREATE TABLE [스키마]테이블명
(칼럼명1 DATATYPE [DEFAULT 형식] ,
칼럼명2 DATATYPE [DEFAULT 형식] ,
..... ) ;
스키마 : 객체의 집합으로서 테이블의 소유자를 의미
테이블명 : 새로 생성하고자 하는 테이블의 이름
칼럼명 : 테이블에 포함된 칼럼의 이름
DATATYPE : 칼럼이 가질 데이터의 타입과 크기
DEFAULT : 데이터 입력(INSERT)시에 기본 값을 명시
- Constraint
제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의
무결성을 유지하기 위한 가장 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 성질입니다.
테이블을 생성할 때 제약 조건을 반드시 줄 필요는 없습니다.
+ Constraint 성질
Constraint란 데이터 무결성 유지를 위하여 사용자가 지정할 수 있는 성질
모든 Constraint은 데이터 사전(Data Dictionary)에 저장됨
의미있는 이름을 부여했다면 Constraint을 참조하기 쉬움
Constraint의 이름은 표준 객체 명명법을 따르는게 좋음
Constraint의 이름은 한 사용자 내에서 중복될 수 없음
Constraint의 이름을 생략했다면 ORACLE은 SYS_Cn형식의 이름을 만듦
+ Constraint 정의 방법
+ 컬럼레벨 제약조건
각 컬럼별로 제약조건을 정의
제약조건의 어떤 유형도 정의 가능
문형
컬럼 [CONSTRAINT 제약조건 이름] 제한조건 유형
+ 테이블레벨 제약조건
컬럼에 대한 정의와는 별개로 정의
하나 이상의 컬럼(composite column)에 대하여 정의할때 사용
NOT NULL을 제외한 어떤 제약조건도 정의 가능
문형
컬럼 , ...
[CONSTRAINT 제약조건 이름] 제한조건 유형 (컬럼1,컬럼2..)
+ Constraint 종류
o NOT NULL 제약 조건
NOT NULL 제약 조건은 NULL값을 컬럼에 사용할 수 없게 합니다.
NOT NULL 제약 조건이 없는 컬럼은 NULL값을 가질 수 있습니다.
NOT NULL 제약 조건은 컬럼 레벨로만 정의할 수 있습니다.
... last_name VARCHAR2(25)
CONSTRAINT friend_last_name_nn NOT NULL , ...
o UNIQUE 제약 조건
UNIQUE 제약조건은 컬럼이나 컬럼의 조합을 고유 키로 지정합니다.
테이블에는 이 키에 대해 같은 값을 갖는 행이 하나 밖에 없습니다.
UNIQUE 키가 한 컬럼에 대한 것이라면 NULL 값을 쓸 수 있습니다.
컬럼 레벨이나 테이블 레벨에서 모두 정의할 수 있습니다.
UNIQUE 컬럼에 대해 UNIQUE 인덱스가 자동으로 생성됩니다.
(Column) ....phone VARCHAR2(10)
CONSTRAINT s_emp_phone_uk UNIQUE ,...
(Table) ....phone VARCHAR2(10),
CONSTRAINT s_emp_phone_uk UNIQUE(phone),...
o PRIMARY KEY 제약 조건
테이블에 단 하나의 Primary key만 허용합니다.
여러 컬럼일지라도 NULL값을 허용하지 않습니다.
테이블이나 컬럼 레벨에서 정의할 수 있습니다.
UNIQUE 인덱스를 자동으로 생성합니다.
(Column) .... id NUMBER(7)
CONSTRAINT s_emp_id_pk PRIMARY KEY....
(Table) .... id VARCHAR2(7),
CONSTRAINT s_emp_id_pk PRIMARY KEY(id),...
o FOREIGN KEY 제약 조건
foreign key는 자식(child) 테이블에 정의합니다.
부모(Parent) 테이블의 값과 일치하거나 NULL이어야 합니다.
테이블이나 컬럼 레벨에서 정의할 수 있습니다.
Foreign Key 제약조건을 줄 때 주의사항
1. 참조하고자 하는 테이블이 먼저 생성되어 있어야 합니다.
2. 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 있어야 합니다.
3. ·참조하고자 하는 컬럼의 데이터 타입이 일치해야 합니다.
ON DELETE CASCDE옵션
FOREIGN KEY제약조건을 줄 때 ON DELETE CASCDE옵션을 줄 수 있습니다.
참조하는(child) 테이블쪽에 이 옵션을 주면 참조되는(parent) 테이블의
행을 삭제 가능하게 할 뿐만 아니라 참조하는 행들도 자동으로 삭제해 주는 옵션입니다.
(Column) ....dept_id NUMBER(7)
CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id)....
(Table) ....dept_id NUMBER(7),
CONSTRAINT s_emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES s_dept(id)....
o CHECK 제약 조건
CHECK 제약 조건은 각 행이 만족해야 하는 조건을 정의합니다.
CHECK 제약 조건에서 사용할 수 없는 것은 다음과 같습니다.
CURRVAL, NEXTVAL, LEVEL, ROWNUM에 대한 참조
SYSDATE, UID, USER, USERENV 함수에 대한 호출
다른 행의 값을 참조
ORACLE7 Server의 예약어는 쓸 수 없다.
컬럼이나 테이블 레벨에서 모두 정의할 수 있다.
(Column) .... salary NUMBER(11,2)
CONSTRAINT s_emp_salary_ck CHECK (salary between 500 and 10000)..
(Table) ... salary NUMBER(11,2) ,
CONSTRAINT s_emp_salary_ck CHECK (salary between 500 and 10000)..
o SQL> CREATE TABLE c_emp
2 (id NUMBER(5)
3 CONSTRAINT c_emp_id_pk PRIMARY KEY ,
4 name VARCHAR2(25)
5 CONSTRAINT c_emp_name_nn NOT NULL,
6 salary NUMBER(7,2) ,
7 phone VARCHAR2(15)
8 CONSTRAINT c_emp_phone_ck CHECK
9 (phone like '3429-%') ,
10 dept_id NUMBER(7)
11 CONSTRAINT c_emp_dept_id REFERENCES S_DEPT(ID) ) ;
SQL> CREATE TABLE emp_113(사번, 이름,메일,입사일)
2 AS SELECT id, name, mailid, start_date
3 FROM s_emp
4 WHERE dept_id = 113 ;
* 데이터 조작어
- INSERT
SQL> INSERT INTO emp(id,name,salary,title,in_date,dept_name)
2 VALUES (100,'무궁화',950,'대리',TO_DATE('1998/05/01','YYYY/MM/DD'),'영업부');
SQL> INSERT INTO emp
2 VALUES (100,'무궁화',950,'대리',TO_DATE('1998/05/01', 'YYYY/MM/DD'),'영업부');
SQL> INSERT INTO emp
2 VALUES (110, USER,1000,'과장',SYSDATE,'관리부');
SQL> INSERT INTO emp
2 VALUES (120, '진달래',NULL,NULL,SYSDATE,'관리부');
SQL> INSERT INTO emp
2 VALUES (120, '진달래','','',SYSDATE,'관리부');
SQL> INSERT INTO emp(id, name, in_date, dept_name)
2 VALUES (130, '개나리',SYSDATE,'관리부');
- UPDATE
SQL> UPDATE emp
2 SET dept_name = '영업부', salary = 800
3 WHERE id = 130 ;
- DELETE
DELETE FROM 테이블명
[WHERE 조건식];
- SUBQUERY 이용
SQL> INSERT INTO emp_113
2 (id, name, mailid , start_date)
3 SELECT id, name, mailid , start_date
4 FROM s_emp
5 WHERE dept_id = 118 ;
SQL> UPDATE s_emp
2 SET dept_id= ( SELECT dept_id
3 FROM s_emp
4 WHERE title = '사장')
5 WHERE name = '안창환' ;
* TRANSACTION CONTROL 과 DICTIONARY
- 명시적인 (Explicit) 트랜잭션 제어 명령어
COMMIT : 아직 저장되지 않은 모든 데이터 변경 사항을 데이터베이스에 저장하고 현재의 트랜잭션을 종료
SAVEPOINT 이름 : 현재의 트랜잭션에 savepoint를 지정
ROLLBACK [TO SAVEPOINT 이름] :아직 저장되지 않은 모든 데이터 변경 사항을 취소하고
현재의 트랜잭션을 종료
- 암시적인 (Implicit) 트랜잭션 제어 명령어
자동 COMMIT : DDL 명령이나 DCL 명령 실행
COMMIT나 ROLLBACK을 명시적으로 실행하지 않고 SQL*Plus를 정상적으로 종료
ROLLBACK [TO SAVEPOINT 이름] : SQL*Plus의 비정상적 종료나 시스템 실패
- COMMIT나 ROLLBACK 이전 데이터 상태
단지 buffer에만 영향을 받았기 때문에 데이터의 이전 상태는 복구 가능
현재 사용자는 SELECT 문으로 DML의 결과를 확인 가능
다른 사용자는 현재 사용자가 수행한 DML문의 결과를 볼 수 없음
변경된 행은 Lock이 설정되어서 다른 USER가 변경할 수 없음
- SAVEPOINT를 이용한 ROLLBACK
SAVEPOINT명령어로 현재의 트랜잭션중에 위치를 지정할 수 있음
ROLLBACK TO SAVEPOINT문을 써서 특정 SAVEPOINT까지 ROLLBACK 할 수 있음
SQL>INSERT...
SQL> SAVEPOINT in_done;
Savepoint created.
SQL> UPDATE..
SQL> ROLLBACK TO in_done;
Rollback complete.
* DICTIONARY
- DICTIONARY의 개념
데이터베이스가 생성될 때 ORACLE8 SERVER에 의해서 자동으로 생성되며
그 내용도 자동으로 갱신, 유지보수됩니다.
DATA Dictionary는 주로 읽기 전용의 테이블들의 집합으로써 사용자가 직접 access하여
데이터를 조작할 수 없고 단지 사용자의 DDL에 연관한 작업이 자동으로 DATA Dictionary에 반영됩니다
ORACLE7 Server 사용자명, 사용자에게 허가된 권한, 데이터베이스 객체명(TABLE, SEQUENCE, VIEW, INDEX 등)
테이블 제약조건 감사(Auditing) 정보 등이 기록되어 있습니다.
- DICTIONARY의 종류
View의 종류
접두어(prefix) 설 명
USER_ 사용자가 소유한 객체에 관한 정보를 저장
ALL_ 사용자에게 액세스가 허용된 객체에 관한 정보를 저장
DBA_ DBA권한을 가진 사용자가 액세스 할 수 있는 정보를 저장
V$ 서버의 성능과 Locking에 관한 정보를 저장. 일반적으로 DBA에게만 허용됨.
기타 뷰
뷰 이름 설 명
DICTIONARY 모든 데이터 사전 테이블, 뷰, 동의어 저장
TABLE_PRIVILEGES 사용자가 권한을 부여했거나(GRANTOR) 부여받은(GRANTEE) 오브젝트에 대한 권한
IND USER_INDEXES 의 동의어(SYNONYMS)
- DICTIONARY 검색
SQL> DESC dictionary
SQL> SELECT *
2 FROM dictionary ; 사용자가 엑세스할 수 있는 모든 데이터 사전 뷰를 검색
SQL> SELECT *
2 FROM dictionary
3 WHERE table_name LIKE 'USER%';
SQL> SELECT table_name
2 FROM dictionary
3 WHERE table_name LIKE 'USER%'; 사용자가 소유한 모든 데이터 사전 뷰의 이름을 검색
SQL> SELECT object_name
2 FROM user_objects
3 WHERE object_type = 'TABLE'; 사용자가 소유한 모든 테이블을 조회
SQL> SELECT *
2 FROM dictionary
3 WHERE LOWER(comments) LIKE '%constraint%';
DICTIONARY 테이블의 COMMENTS 칼럼에서 제약조건과 관련된 주제에 대한
데이터 사전 테이블에 대해 조회하시오.
SQL> DESC user_constraints; USER_CONSTRAINTS 테이블의 구조를 확인하시오.
SQL> SELECT constraint_name, constraint_type,
2 search_condition, r_constraint_name
3 FROM user_constraints
4 WHERE table_name = 'S_EMP'; S_EMP 테이블에 대한 제한조건을 검색하시오.
SQL> SELECT constraint_name,column_name
2 FROM user_cons_columns
3 WHERE table_name = 'S_EMP'; S_EMP 테이블에 대한 칼럼 제한 조건을 검색하시오.
* 데이터 정의어
- 명령어의 종류
CREATE TABLE 새로운 테이블을 생성
DROP TABLE 기존 테이블의 구조 및 모든 행을 삭제
ALTER TABLE 기존 테이블을 변경
- 컬럼의 추가 , 수정
- 제약조건 추가,삭제,활성화,비활성화
TRUNCATE 기존 테이블의 구조는 남기고 모든 행을 삭제
RENAME Object의 이름을 바꿈
COMMENT 테이블이나 컬럼에 주석문 달기
이러한 명령어는 모두 데이터 정의 명령어(DDL)로서 실행시 자동 COMMIT되고
ROLLBACK될 수 없으므로 주의해서 실행해야 합니다.
- 테이블 삭제
DROP TABLE 테이블명 [CASCADE CONSTRAINTS ] ;
모든 데이터가 테이블에서 삭제됨
트랜잭션은 자동 COMMIT되므로 ROLLBACK할 수 없음.
해당테이블의 모든 인덱스가 삭제됨
CASCADE CONSTRAINTS옵션은 종속된 제약조건을 삭제함
- 테이블 구조 변경
o 칼럼의 변경
- 칼럼의 추가
ALTER TABLE 테이블명
ADD (컬럼 datatype [DEFAULT 형식][CONSTRAINT 정의][, 컬럼 datatype] ...) ;
SQL> ALTER TABLE c_emp
2 ADD (title VARCHAR2(25)
3 CONSTRAINT c_emp_title_ck CHECK
4 (title IN ('사장','부장','과장','대리','사원' ) ),
5 in_date date DEFAULT SYSDATE );
- 칼럼의 수정
ALTER TABLE 테이블명
MODIFY (컬럼 datatype [DEFAULT 형식][NOT NULL제약조건][, 컬럼 datatype] ...) ;
컬럼의 크기, 기본값 (DEFAULT), NOT NULL 제약조건을 변경가능
컬럼의 크기를 확장
컬럼이 NULL이거나 테이블에 데이터가 없을때 컬럼의 크기를 축소하거나
데이터 타입을 변경 가능
DEFAULT를 변경하면 다음 입력할 때부터 적용됨
컬럼에 NULL값이 없을 경우에만 NOT NULL 제약 조건을 추가 가능
SQL> ALTER TABLE c_emp
2 MODIFY (phone VARCHAR2(25));
SQL> ALTER TABLE c_emp
2 MODIFY (title VARCHAR2(10));
SQL> ALTER TABLE c_emp
2 MODIFY (phone VARCHAR2(15));
o CONSTRAINT의 변경
- 제약조건의 추가
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약 조건명] 제약조건유형 (칼럼명) ;
NOT NULL제약조건은 테이블 레벨 제약조건으로 기술할 수 없으므로,
칼럼의 MODIFY절에서 가능합니다
SQL> ALTER TABLE c_emp
2 ADD CONSTRAINT c_emp_phone_uk UNIQUE(phone) ;
- 제약조건의 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약 조건명 [CASCADE] ;
SQL> SELECT constraint_name,constraint_type , search_condition
2 FROM user_constraints
3 WHERE table_name = 'C_EMP' ;
SQL> ALTER TABLE c_emp
2 DROP CONSTRAINT c_emp_dept_id_nn ;
- 제약조건의 활성화/비활성화
> ALTER TABLE 테이블명
ENABLE CONSTRAINT 제약 조건명 ;
> ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약 조건명 [CASCADE] ; CASCADE는 참조 조건과 함께 수정
SQL> ALTER TABLE s_emp
2 DISABLE CONSTRAINT s_emp_id_pk CASCADE;
SQL> ALTER TABLE s_emp
2 ENABLE CONSTRAINT s_emp_id_pk ;
o 그 외의 DDL
- RENAME-OBJECT의 이름 변경
테이블, VIEW, SEQUENCE, SYNONYM의 이름 변경
객체의 소유자만이 바꿀 수 있음
> RENAME 이전이름 TO 새로운 이름 ;
SQL> RENAME salgrade TO salary_grade:
- TRUNACTE-테이블의 구조를 남기고 모든 데이터 삭제하기
테이블의 모든 행을 삭제
테이블이 사용한 저장 공간을 반환
TRUNCATE 명령이 행의 삭제를 Rollback할 수 없는데 비해,
DELETE 명령은 삭제된 행을 Rollback가능
> TRUNCATE TABLE 테이블명 ;
SQL> TRUNCATE TABLE salary_grade ;
- COMMENT -테이블이나 칼럼에 주석문 달기
입력된 주석은 다음의 dictionary뷰를 통해 볼 수 있습니다
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
> COMMENT ON TABLE 테이블명 IS '주석문장' ;
> COMMENT ON COLUMN 테이블명.칼럼명 IS '주석문장' ;
SQL> COMMENT ON TABLE s_emp IS '사원정보' ;
SQL> COMMENT ON COLUMN s_emp.name IS '사원명' ;
SQL> SELECT *
2 FROM user_tab_comments
3 WHERE table_name = 'S_EMP' ;
* 데이터 제어어
- DATABASE SECURITY
o Privileges
Privileges란 특정한 SQL문장을 수행하거나 특정 Object에 허가된 작업을 의미합니다.
Privileges에는 특정한 SQL 문장을 실행할 수 있는 권한인 시스템 권한(system privilege)과
특정한 오브젝트에 대한 접근을 제어하는 권한인 오브젝트 권한(object privilege)로 나눌 수 있습니다.
o Role
권한 부여와 접근제어 관리를 손쉽게 하기 위해 연관된 권한들의 집합을 묶어서
정의해 놓은 것을 역할(role)이라고 합니다.
o Schema
Schema란 Table, View, Sequence, Procedure, Function 등의 오브젝트들의 집합으로서
Schema는 데이터베이스 사용자에 의하여 소유되어 있고 사용자의 이름과 같은 이름을 가지고 있습니다.
자신의 스키마에 속한 오브젝트가 아닌 것을 액세스하려고 할 때는 그 오브젝트에 대한
액세스 권한을 부여받아야 합니다.
- PRIVILEGES
o SYSTEM PRIVILEGES
- DBA이 갖는 상위 레벨의 시스템 권한
새로운 사용자 생성(CREATE USER)
사용자 삭제(DROP USER)
테이블 삭제(DROP ANY TABLE)
테이블 백업(BACKUP ANY TABLE) 등
o 새로운 사용자 생성 및 삭제 문형
> CREATE USER 사용자명 IDENTIFIED BY 암호 :
사용자를 생성하면 DBA는 GRANT 명령을 실행하여 사용자에게 권한을
부여할 수 있으며 사용자는 다음과 같은 시스템 권한을 부여받을 수 있습니다.
CREATE SESSION : 데이터베이스에 접속가능한 권한
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE 등
> DROP USER 사용자명 [CASCADE] ;
CASCADE옵션은 사용자가 포함한 object까지 같이 drop하는 옵션입니다
SQL> CREATE USER march
2 IDENTIFIED BY spring ;
o 사용자 암호 변경 문형
> ALTER USER 사용자명 IDENTIFIED BY 암호 :
SQL> ALTER USER scott
2 IDENTIFIED BY lion ;
o 시스템 권한 부여 및 박탈
DBA는 특정 시스템 권한을 사용자에게 허가할 수 있습니다.
사용자가 권한을 받으면 즉시 그 권한을 쓸 수 있습니다.
> GRANT 권한 [, 권한...] TO 사용자 [, 사용자 ...] ;
> REVOKE 권한 [, 권한...] FROM 사용자 [, 사용자 ...]
SQL> GRANT create table, create view, create sequence
2 TO scott ;
SQL> REVOKE create sequence
2 FROM scott ;
- OBJECT PRIVILEGES
DBA는 특정한 오브젝트에 대해서 사용자에게 작업을 수행하도록 허가할 수
있습니다. 오브젝트 권한은 오브젝트 유형에 따라서 다양하고, 오브젝트의 소유자는
다른 사용자에게 특정 오브젝트 권한을 부여할 수 있습니다.
> GRANT 권한 [(컬럼명)] [ 권한 ...|ALL ]
ON Object명
TO 사용자 | ROLE | PUBLIC [, 사용자...」
[WITH GRANT OPTION ] ;
> REVOKE 권한 [(컬럼명)] [ 권한 ...|ALL ]
ON Object명
FROM 사용자 | ROLE | PUBLIC [, 사용자...」 ;
o 각 Object에 따른 PRIVILEGES 유형을 알아봅시다.
객체 권한 테이블 뷰 SEQUENCE PROCEDURE SNAPSHOT
ALTER V V
DELETE V V
EXECUTE V
INDEX V v
INSERT V V
REFERENCES V
SELECT V V V
UPDATE V V
WITH GRANT OPTION으로 부여된 권한을 회수하면 그 권한을 받은
다른 사용자에게서도 권한이 회수됩니다.
SQL> GRANT SELECT , INSERT (id,name)
2 ON s_emp
3 TO usera , userb ;
SQL> REVOKE INSERT
2 ON s_emp
3 FROM userb ;
usera.SQL> GRANT select, insert
2 ON s_dept
3 TO scott
4 WITH GRANT OPTION ;
userb.SQL> GRANT select
2 ON usera.s_dept
3 TO PUBLIC ;
usera.SQL> REVOKE select, insert
2 ON s_dept
3 FROM userb ;
- ROLE
ROLE이란 사용자에게 허가할 수 있는 관련된 PRIVILEGES의 그룹을 말합니다.
ROLE을 이용하면 권한부여와 회수를 쉽게 할 수 있음
한 사용자가 여러 ROLE을 access할 수 있고, 여러 사용자에게 같은 ROLE을 부여할 수 있음
사용자는 ROLE에 ROLE을 부여할 수도 있음
o ROLL의 생성
> CREATE ROLE role이름 ;
ROLE의 생성 및 부여는 다음과 같은 순서에 따라야 합니다.
① ROLE의 생성
② ROLE에 권한 부여
③ ROLE을 사용자 또는 ROLE에게 부여
SQL> CREATE ROLE create_obj ;
SQL> GRANT create table, create view TO create_obj ;
SQL> GRANT create_obj TO usera, userb ;
- PRIVILEGES와 ROLE에 관한 DICTIONARY 정보
PRIVILEGES와 ROLE에 관련된 정보를 가지고 있는 데이터 사전 뷰의 종류는 다음과 같습니다.
데이터 사전 뷰 설 명
ROLE_SYS_PRIVS Role에 부여된 시스템 권한
ROLE_TAB_PRIVS Role에 부여된 테이블 권한
USER_ROLE_PRIVS 현재 사용자가 액세스할 수 있는 Role
USER_TAB_PRIVS_MADE 현재 사용자의 객체에 부여한 객체권한
USER_TAB_PRIVS_RECD 현재 사용자의 객체에 부여된 객체권한
USER_COL_PRIVS_MADE 현재 사용자 객체의 특정 칼럼에 부여한 객체권한
USER_COL_PRIVS_RECD 현재 사용자 객체의 특정 칼럼에 부여된 객체권한
SQL> SELECT username, granted_role
2 FROM user_role_privs;
* OBJECT
- OBJECT의 종류
오브젝트 설 명
테이블(TABLE) 행과 열의 조합으로 구성된 데이터의 기본 저장단위
시퀀스(SEQUENCE) 자동으로 고유한 숫자값을 생성해주며 주로 기본 키값을 생성하기 위해 사용
인덱스(INDEX) 쿼리(query) 속도를 향상
뷰(VIEW) 하나 또는 그 이상의 테이블로부터 논리적으로 데이터를
추출한 부분집합으로 논리적이고 가상적인 테이블
시노님(SYNONYM) OBJECT에 대한 또다른 이름으로 ALIAS역할
프로그램 유닛(PROGRAM UNIT) SQL, PL/SQL문으로 작성한 Procedure, Function, Trigger , Package
- SEQUENCE
SEQUENCE란 고유한 숫자값을 생성해 주는 객체로서 SEQUENCE GENERATOR가 자동으로 그 값을 생성해 주며
주로 PK값 생성을 위해 사용됩니다.
SEQUENCE는 공유 가능한 객체로서 한 사용자가 생성하면 다른 사용자도 사용할 수 있을 뿐 아니라,
SEQUENCE는 테이블과 독립적이므로 여러 테이블에서 이 값을 참조하여 사용할 수 있습니다.
> CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ { MAXVALUE n | NOMAXVALUE } ]
[ { MINVALUE n | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE n | NOCACHE } ]
이 름 기 능
INCREMENT BY n SEQUENCE 번호간의 간격.기본값 1씩 증가.
START WITH n 생성되는 첫번째 SEQUENCE 번호.기본값 1부터 시작
MAXVALUE n 생성가능한 SEQUENE의 최대값.
NOMAXVALUE 기본옵션으로 최대값은 1027
MINVALUE n 생성가능한 SEQUENCE의 최소값.
NOMINVALUE 최소값을 1로 지정.
CYCLE | NOCYCLE 최대값이나 최소값까지 값이 생성된 경우에도 값을 생성시키는 것으로 기본은 NOCYCLE
CACHE n|NOCACHE 메모리에 유지할 값의 수로, 기본값은 20.
SQL> CREATE SEQUENCE c_emp_id
2 INCREMENT BY 1
3 START WITH 26
4 MAXVALUE 9999999
5 NOCACHE
6 NOCYCLE ;
SQL> INSERT INTO c_emp (id, name, salary)
2 VALUES (c_emp_id.NEXTVAL, '개나리', 1500 ) ;
SEQUENCE가 가지는 PSEUDO COLUMN은 NEXTVAL, CURRVAL 두 가지가 있습니다.
주로 데이터 입력시에 NEXTVAL을 이용해서 데이터를 입력하게 되면,
CURRVAL은 SEQUENCE의 현재 값으로 NEXTVAL가 할당되어야만 그 값이 CURRVAL로 참조 가능해집니다.
SEQUENCE 자체는 일련적으로 값을 생성하지만 SEQUENCE는 테이블에는 독립적인 Object이므로
같은 테이블에서 SEQUENCE가 사용되거나 ROLLBACK이나 SYSTEM CRASH 에 의해서 GAP이 발생할 수 있습니다.
따라서 SEQUENCE가 특정 테이블에 사용된 경우는 일련번호로 입력되는 것을 보장받기는 어려우며,
단지 UNIQUE NUMBER만 생성할 뿐입니다.
SQL> SELECT sequence_name, min_value,
2 max_value, increment_by, last_number
3 FROM user_sequences ; SEQUENCE 정보 확인
- SEQUENCE의 변경 및 삭제
o SEQUENCE의 변경
> ALTER SEQUENCE SEQUENCE이름
[ INCREMENT BY n ]
[ { MAXVALUE n | NOMAXVALUE } ]
[ { MINVALUE n | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE n | NOCACHE } ]
자신의 SEQUENCE이거나 그 SEQUENCE에 대한 ALTER 권한을 가져야만 SEQUENCE를 수정할 수 있습니다.
유효성 검사를 하게 됩니다.
예를 들어 새로운 MAXVALUE는 현재의 SEQUENCE 값보다 작지 않아야 합니다.
START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없습니다.
다른 번호에서 시작하려면 이전 SEQUENCE를 삭제하고 다시 생성해야 합니다.
o SEQUENCE의 삭제
> DROP SEQUENCE SEQUENCE이름 ;
SEQUENCE를 제거할 DROP ANY SEQUENCE권한을 가지고 있거나 그 SEQUENCE의 소유자이어야 합니다.
- INDEX
ORACLE에서 제공하는 INDEX는 Pointer를 이용하여 좀더 빠르게 데이터를 검색하도록 하는 Object입니다.
만약 특정 컬럼에 INDEX가 생성되어 있지 않으면 일반적으로 검색시에 Full Scan을 하게 됩니다.
o INDEX의 생성방법에 따른 종류
Unique INDEX NON-Unique INDEX
Primary key , Unique 제약조건에 사용자에 의해서 생성
의해 자동으로 생성
각 INDEX(Table)의 값이 유일함을 보장 각 INDEX(Table)의 값이 유일하지 않음
o INDEX의 생성
> CREATE INDEX 인덱스명 ON 테이블명 (칼럼 [, 칼럼] ... ) ;
- 인덱스를 만드는 때
조건절(WHERE)이나 조인(Join) 조건에서 칼럼을 자주 이용할 때
칼럼이 넓은 범위값을 가질 때
많은 NULL 값을 갖는 칼럼일 때
테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15%정도일 때
- 인덱스를 만들지 않아야 할 때
테이블이 작을 때
칼럼이 조회의 조건으로 사용되는 경우가 별로 없을 때
대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때
테이블이 자주 변경될 때
SQL> CREATE INDEX c_emp_name_idx
2 ON c_emp (name) ;
SQL> SELECT id , name , salary
2 FROM c_emp
3 WHERE name = '구원상' ;
o INDEX의 정보 확인
USER_INDEXES 는 인덱스의 이름과 고유성 정보를 갖고 있습니다.
USER_IND_COLUMNS 는 인덱스명, 테이블명, 칼럼명을 갖고 있습니다.
SQL> SELECT ic.index_name, ic.column_name,
2 ic.column_position, ix.uniqueness
3 FROM user_indexes ix, user_ind_columns ic
4 WHERE ic.index_name = ix.index_name
5 AND ic.table_name = 'C_EMP' ;
o INDEX의 삭제
> DROP INDEX 인덱스명 ;
인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무 영향이 없습니다.
SQL> DROP INDEX C_emp_name_idx ;
인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.
특정 칼럼에 생성된 인덱스를 다른 칼럼으로 변경하는 ALTER는 불가능하므로 인덱스된 칼럼을
변경하려면 삭제한 후 다시 만들어야 합니다.
- VIEW
View란 테이블이나 다른 뷰를 기초로 한 논리적이고 가상(Virtual)의 테이블입니다.
View는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같고,
View는 실제적으로는 질의문장을 가집니다.
o VIEW의 종류 및 특성
Simple View Complex View
테이블 수 ONE MANY
Function의 포함여부 NO YES
Grouping 여부 NO YES
View를 통한 DML여부 YES NO
o VIEW의 생성
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW 이름
[ (alias[, alias] ...)]
AS subquery
[ WITH CHECK OPTION [ CONSTRAINT 제약 조건 ] ]
[ WITH READ ONLY ] ;
종 류 역 할
FORCE 기본 테이블 유무에 관계없이 뷰를 만듦.
Alias Subquery로 선택된 값에 대한 칼럼명
WITH CHECK OPTION 뷰에 의해 액세스될 수 있는 행만이 입력되거나 변경될 수 있음
WITH READ ONLY 뷰에서 DML이 수행될 수 없음.
뷰를 정의하는 질의어는 조인, 그룹, Subquery를 포함하는
복잡한 SELECT문장으로 구성될 수 있음
뷰를 정의하는 질의어에는 ORDER BY 절을 쓸 수 없음
제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn형태의 이름을 지정함
뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있음
- 뷰에 대한 DML문 사용 규칙
간단한 뷰에서는 DML연산 수행가능.
뷰가 다음 사항을 포함하는 경우 행을 삭제할 수 없음
- 조인(Join) 조건
- 그룹 함수
- GROUP BY 절
- DISTINCT 명령
뷰가 다음 사항을 포함하는 경우 데이터를 수정할 수 없음
- 위의 조건
- 식으로 정의된 칼럼
ex) SARARY*12
뷰가 다음 사항을 포함하는 경우 데이터를 추가할 수 없음
- 위의 조건
- VIEW에 선택되지 않은 NOT NULL 칼럼
SQL> CREATE VIEW empvu113
2 AS SELECT id, name, title
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE or REPLACE VIEW empvu113
2 AS SELECT id, name NAME, salary MONTHLY_SALARY
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE or REPLACE VIEW empvu113(ID,NAME,MONTHLY_SALARY)
2 AS SELECT id, name , salary
3 FROM s_emp
4 WHERE dept_id = 113 ;
SQL> CREATE OR REPLACE VIEW dept_sum_vu
2 (name, minsal, maxsal, avgsal)
3 AS SELECT d.name, min(e.salary),
4 max(e.salary), avg(e.salary)
5 FROM s_emp e, s_dept d
6 WHERE e.dept_id = d.id
7 GROUP BY d.name;
SQL> CREATE OR REPLACE VIEW empv
2 AS SELECT id,name,dept_id
3 FROM c_emp
4 WHERE dept_id=113 ;
o VIEW 생성시 사용되는 OPTION
OPTION종류 기 능
With CHECK Option View의 연산이 CHECK 조건이 만족될때만 수행되도록 합니다.
With Read Only View 정의에 WITH READ ONLY 옵션을 추가하여 DML 연산이 발생하지
않도록 합니다. View의 행에 대한 DML을 수행하려 한다면
Oracle7 Server error인 ORA-01732가 발생합니다.
SQL> CREATE OR REPLACE VIEW empvu113
2 AS SELECT *
3 FROM s_emp
4 WHERE dept_id=113
5 WITH CHECK OPTION
SQL> CREATE OR REPLACE VIEW empvu113
2 AS SELECT *
3 FROM s_emp
4 WHERE dept_id=113
5 WITH READ ONLY ;
o VIEW의 정보 확인
SQL> DESC user_views
SQL> SELECT view_name , text
2 FROM user_views ;
o VIEW의 삭제
> DESC VIEW view이름 ;
SQL> DROP VIEW empv ;
View에 대한 ALTER 명령어는 제공되지 않습니다. 기존의 View에 대한 정보를
변경하고 싶은 경우에는 OR REPLACE OPTION을 써서 VIEW를 생성합니다.
- SYNONYM
Synonym이란 특정 Object에 부여하는 또다른 이름으로, 사용자의 편의나 참조를 빠르게 하기 위해서
사용합니다.
> CREATE [ PUBLIC ] SYNONYM Synonym명
FOR Object명 ;
PUBLIC은 모든 사용자가 접근가능한 동의어를 생성합니다.
PUBLIC 동의어의 생성 및 삭제는 DBA 만이 할 수 있습니다.
USER A가 가 USER B의 s_dept 테이블에 대한 동의어인 dept를 생성하시오.
usera.SQL> CREATE SYNONYM dept
2 FOR userb.s_dept ;
Posted by jobs at 16:56 | Comments (0)
ORACLE LOCK 문제를 일으키는 SQL 명령 찾기
* 다음 Query는 Lock과 관련된 transaction을 출력해준다.
column username format a10
column sid format 999
column lock_type format a15
column MODE_HELD format a11
column MODE_REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0);
( 위의 Query를 실행시키면 다음과 같은 내용이 출력된다.
SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--- --------------- ---------- ---------- -------- --------
5 Transaction Exclusive None 262172 90
6 Transaction None Exclusive 262172 90
9 Transaction None Exclusive 262172 90
SID 6과 9는 SID 5가 걸고 있는 Lock이 풀리기를 기다리고 있음을 알 수 있다.
* 다음 Query는 Lock과 관련된 테이블을 출력해 준다.
column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS';
/
( 위의 Query를 실행하면 다음과 같은 결과가 출력된다.
USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT
--------------- --- ------------- -------------- ----------
SQL
---------------------------------------------------------------
LTO2 6 LTO EMP C3D320F4
update lto.emp set empno =25 where empno=7788
LTO3 9 LTO EMP C3D320C8
delete from lto.emp where empno=7788
LTO 5 LTO DEPT
insert into lto.dept values (60,'PROGRAMMER','LOS ANGELOS')
여기서는 USERNAME에 나와있는 유저가 OBJECT에 나와있는 테이블을 수정하려고
함을 나타낸다. LT02,LT03는 LT0가 Commit,Rollback 하기를 기다리고 있음을 알
수 있다. 하지만 여기에는 가장 최근의 DML 명령 하나만 나와있기 때문에 여기
나온 명령이 반드시 Lock을 걸고 있는 명령이라고 단정지을 수는 없다.
관련된 프로세스
---------------
* 다음 Query를 실행해 보면 프로세스와 관련된 정보를 얻을 수 있다.
column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS';
/
* 위의 Query를 실행하면 다음과 같은 결과가 출력된다.
ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT
USER ID ID SPID SPID
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8
이 때는 다음과 같이 조치한다.
1. LTO에게 Commit,Rollback 할 것을 요구한다.
2. SQLDBA>ALTER SYSTEM KILL SESSION '5,31';
3. %kill -9 17309 (유닉스상의 Shadown Process)
stop/id= (PROC SPID=SESS SPID on vms running single task)
여기서 SYS 유저는 제외시켰는데 필요하다면 Query의 Where 조건에서
s.username != 'SYS' 부분을 삭제하면 된다.
CATBLOCK.SQL & UTLLOCKT.SQL
---------------------------
$ORACLE_HOME/rdbms/admin 디렉토리에 있는 스크립트 가운데 catblock.sql과
utlockt.sql을 사용하여서 Lock 상황을 쉽게 파악할 수 있다. 이들은 다음과
같이 실행한다.
%cd $ORACLE_HOME/rdbms/admin
%sqldba lmode=y (svrmgrl
SVRMGR>connect internal
SVRMGR>@catblock
결과는 다음 Query 문으로 알아 본다.
column waiting_session format a8
select lpad(' ',3*(level-1)) || waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
위의 Query에 의한 출력은 다음과 같다.
WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
5 None
6 Transaction Exclusive Exclusive 262172 90
9 Transaction Exclusive Exclusive 262172 90
여기서 Session 6, Session 9가 Session 5를 기다리고 있음을 알 수 있다.
Lock & Hanging 문제를 추정하는 방법
-----------------------------------
프로그램 상에서 어느 부분이 Lock, Hanging 문제를 일으키는지 알아내기가
여의치 않을때 다음과 같은 방법을 사용해 보기 바란다.
1. init.ora의 sql_trace=ture로 세팅하면 연관된 SQL 명령이 출력될
것이다.
2. OS 상에서도 Process 상태를 점검하여 본다.
3. OS 상의 Debugging 기능을 사용하거나 만약 가능하다면 oradbx를 사용한다.
Platform 에 따라서 없을 수도 있다.
4. 여러가지 Monitoring 방법과 Locking/Blocking 스크립트를 이용한다.
PROCESS STACKS 확인
-------------------
때로는 Hanging Process나 리소스를 점유하고 있는 Process의 Process Stack을
점검해 보는 것이 문제 해결에 도움이 되는 경우가 있다.
1. OS Debugging 프로그램을 이용하여 Hangup 되기 전의 마지막 Call을 확인
한다.
ex)%truss -p
2. oradbx(오라클 debugging 프로그램)은 Support,Development 시에만 사용
된다.
select substr(s.username,1,11) "ORACLE USER" ,
p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#,
osuser "OS USER", p.spid "PROC SPID"
from v$session s, v$access a, v$process p
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS';
/
위의 Query를 실행하면 다음과 같은 결과가 출력된다.
ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT
USER ID ID SPID SPID
------ ------- ------- ------- ------- ---- ---- ------
LTO 19 5 31 usupport 17312 17309
LTO2 25 6 43 usupport 17313 17310 C3D320F4
LTO3 26 9 1 usupport 17314 17311 C3D320D8
만약 oradbx가 없다면 다음과 같이 해서 만들어 준다.
%cd $ORACLE_HOME/rdbms/lib
%make -f oracle.mk oradbx
LTO Process가 무엇을 하고 있는지 알고 싶으면 Process Stack을 보면 알수
있다.
ps -ef | grep 17312
usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713(DESCRIPTION=(LOCAL=YE
type
debug 17312 (이 유저의 oracle shadow process)
dump stack
dump procstat
위에서 생성된 트레이스 화일(user_dump_dest 에 생성됨)을 이용하면 Hanging
문제를 해결하는데 큰 도움이 된다.
자주 발생하는 LOCK 문제들
-------------------------
1. Parent-Child 관계로 묶인 테이블에서 Child 테이블에 Index가 없는 상황
에서 Child 테이블을 수정하게 되면 Parent테이블에 TABLE LEVEL SHARE
LOCK이 걸리게 되어서 Parent 테이블에 대한 모든 Update가 금지된다.
2. 블럭의 PCTFREE가 매우 작다면 한 블럭에 여러개의 레코드가 들어 있기
때문에 한 블럭에 과도한 트랜잭션이 들어와서 블럭의 Transaction Layer가
Release 되기를 기다리게 되는 경우도 있다.
Ex)
create table test (a number) initrans 1 maxtrans 1;
SYSTEM: insert into test values (5); /* completed */
SCOTT: insert into SYSTEM.test values (10); /* Scott waits */
SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
---- ----- ------------- ----------- ---------- -------- --------
7 System Transaction Exclusive None 196639 54
10 Scott Transaction None Share 196639 54
＊ 교육 담당 & 관리자 : 김 윤 석
＊ 교육 문의 & 예약 접수 : 02) 6255-8046
＊ 홈페이지 http://www.itmoya.net/ocp/main_1.htm
- 오라클지정 교육원, 본원 시험실시, 40%바우쳐제공
