GitHub - skier-song9/DevStudy: my workspace and study notes
아래 내용은 모두 myNotes/SQL_OracleKosmo.hwp에 있음
<aside> ⚙ [ 자주 발생하는 오라클 오류 ]
</aside>
INDEX
[노트 정리 규칙]
[ 용어 정리 ]
[ 정규 표현식 ]
+) Number Format 문자열
+) Date Format 문자열
[ ORACLE의 자료형 ]
[ SQL ]
[DML]
SELECT, INSERT, UPDATE, DELETE
[DDL]
CREATE, ALTER, DROP
[DCL]
[TCL]
[ PL/SQL ]
ORACLE의 주요 Built-In FUNCTION
+) 백업과 복원
+) 기타 유용 코드
+) 데이터 사전.
*** ORA-12505 오류 발생 시 해결방법 ***
원인 : xe SID를 찾지 못해서 오라클에 접속하지 못하는 오류.
해결 : 내PC 우클릭 >> 관리 >> 컴퓨터 관리 - 서비스 및 응용 프로그램 - 서비스 >> OracleServiceXe 찾아서 우클릭 >> 이미 실행 중이어도 다시 시작 누르기.
**[노트 정리 규칙]**
[] : 언어구분 = DDL/DML/DCL/TCL
- 구문에서의 [] 대괄호는 생략 가능한 것들.
※ : ~문/절
- 문법) : 문법/사용구문에 대한 내용
ex) : 예시 설명
∞ :
☆ :
★ :
○ : 종류별 정리
● : ○ 의 하위 종류
( => ) :
- :
**[ 용어 정리 ]**
# NULL = NULL은 이용할 수없고 할당되지 않고 알려져 잇지 않고 적용 불가능한 값을 의미한다. NULL이란 0이나 공백(SPACE)와 다르다. NULL값을 포함한 산술 표현식 결과는 NULL이다. 컬럼에 데이터 값이 없으면 그 값 자체가 NULL이다.
# DUAL = 오라클에서 제공하는 임시 테이블. 오라클은 DATA를 조회하려면 무조건 TABLE/VIEW를 통해서 조회해야 하기 때문에 어떤 결과를 출력(조회)하기 위해 아무것도 없는 빈 테이블인 DUAL를 빌린다. 연습장/메모장 같은 개념의 TABLE이다.
# CASCADE = (영어:종속) (SQL: DATA가 서로 연결(종속)된다)
DROP TABLE 테이블명 CASCADE CONSTRAINT에서는 흘려보내다(=끊다)의 의미를 가진다.
# DOMAIN = DATA의 범위/범주
# Privileges = 권한은 어떤 행동을 할 수 있는 자격을 의미한다. 즉, 어떤 계정이 실행할 수 있는 일/작업(CREATE/READ/UPDATE/DELETE) 등을 의미한다.
# Role = 역할은 사용자, 계정과 같은 의미이다. 역할은 어떤 권한들을 가진다.
# SESSION = ORACLE의 SESSION은 SID(Session ID)와 시리얼번호(Serial#)로 구성되고, 데이터베이스와 접속이 이루어지면서 SESSION이 생성된다. (잘 모르겠음.)
- 사용자가 DB에서 작업을 하기 위해선 SESSION을 생성? SESSION에 연결?
- 일정 시간동안 같은 사용자(정확하게 브라우저를 말한다)로부터 들어오는
일련의 요구를 하나의 상태로 보고 그 상태를 일정하게 유지시키는 기술???
- 아! USER는 SESSION을 통해서 DB에 접속한다! 그리고 DB에는 여러 SESSION이 접속할 수 있는데, 이때 한 SESSION이 DB의 DATA를 조작하고 있다면 그 DATA가 확정(COMMIT/ROLLBACK)되기 전까지 다른 SESSION의 접속을 LOCK하여 DB의 안정성을 유지한다. SESSION은 마치 JAVA의 쓰레드와 같다???
# TRANSACTION = ‘일련의 작업’을 처리하는 단위를 하나의 트랜젝션이라 한다.
- 하나의 업무는 다수의 작업(DML작업)으로 구성되고, ‘하나의 업무 = 하나의 트랜젝션’이다.
# RESULT SET (결과집합, 결과셋) = SELECT문을 실행하여 그 결과로 메모리에 만들어진 TABLE이다.
# INDEX = SQL은 INDEX가 1부터 시작한다.
# 파라미터 = IN , OUT , IN OUT 파라미터가 있다.
- IN 파라미터 : PL/SQL SUBPROGRAM의 FUNCTION은 IN 파라미터만 가질 수 있다. 일반적으로 알고 있는 파라미터의 개념과 같다. 매개변수를 외부로부터 받아 매개변수의 값을 가지고 명령문들을 실행할 때, IN 파라미터를 사용한다.
- OUT 파라미터 : OUT 매개변수란 PROCEDURE 실행 시점에 OUT 매개변수를 함수의 외부로 변수 형태로 전달(프로시져 호출 시 해당 OUT 파라미터의 인자로 받은 변수/바인드변수)하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당한다. 그리고 나서 실행이 끝나면 전달한 변수를 참조해(OUT파라미터 선언할 때의 변수명 그대로 참조) 값을 가져올 수 있는 것이다.
즉, 함수에서 반환값을 RETURN하는 것과 같이, PROCEDURE에서는 OUT 매개변수를 반환하여 같은 효과를 내는 것이다. PROCEDURE 실행부에서 OUT 매개변수에 특정 값을 할당한 후, DBMS_OUTPUT.PUT_LINE(OUT매개변수);해보면 해당 할당된 값이 출력된다.
OUT 매개변수는 해당 프로시저가 성공적으로 실행을 완료할 때까지 값이 할당되지 않는다.
- IN OUT 파라미터 : PROCEDURE에서 사용. 입력과 동시에 출력용으로 사용할 수 있다. OUT 매개변수는 해당 프로시저가 성공적으로 실행을 완료할 때까지 값이 할당되지 않기 때문에 매개변수에 값을 전달해서 사용한 다음 다시 이 매개변수에 값을 받아와 참조하고 싶다면 IN OUT 매개변수를 사용해야 한다.
∴ IN 매개변수는 참조만 가능하며 값을 할당할 수 없다.
∴ 활용) OUT 매개변수로 전달된 값을 판단해 PROCEDURE 작업의 성공/실패 여부를 확인할 수 있다.
∴ OUT, IN OUT 매개변수에는 디폴트 값을 설정할 수 없다.
( IN 매개변수는 가능 >> 매개변수명 IN 자료형 DEFAULT 기본값 )
∴ IN 매개변수에는 변수나 상수, 각 데이터 유형에 따른 값을 전달할 수 있지만, OUT, IN OUT 매개변수에 전달할 때는 반드시 변수 형태로 값을 넘겨줘야 한다.
# TARGET TABLE = 타겟테이블은 트리거를 적용시킬 테이블, 즉 타겟테이블에서 INSERT/ UPDATE/DELETE가 발생하면 트리거가 작동.
**[ 정규 표현식 ]**
. : 임의의 한글자를 의미
예) a.b(acb,afb........)
* : * 바로 앞의 문자가 없거나 한 개 이상이 있을 경우
예) a*b (b,ab,aab,aaab....)
+ : +바로 앞의 문자가 최소 한개 이상일때
예) a+b (ab,aab,aaab...)
? : ?바로 앞의 문자가 없거나 한 개 존재하는 경우
예) a?b (b,ab,)
^ : ^ 뒤에 문자열과 같은 문자열로 시작하는 경우
즉 문자열의 시작을 의미
[ ]안에서 ^ 는 [ ]안의 문자를 제외한 문자를 의미
예) ^ab(ab,abc,abdr...)
$ : $앞의 문자열과 같은 문자열로 끝나는 경우 즉 문자열의 끝을 의미
예) ab$ (avab,aab,abab...)
[] : []안에 문자열중에 하나만의 문자만을 의미
예) [a-z](a부터 z까지중 한 문자)
[0-9](0부터 9까지 숫자중 한 문자)
[abc](a혹은 b혹은 c)
{} :{}앞의 문자열의 개수를 의미 문자{최소개수,최대개수}
최소개수는 반드시 있어야하고 최대개수가 없는경우는 1개또는 1개이상을 의미
하고 숫자 하나만을 적어 주엇을때는 그 숫자만큼의 개수를 의미한다.
예) abc{1,2} (abc,abcc)
a{3} (aaa)
a{1,} (a,aa,aaa....)
| : or 연산자
[^ ] : []안의 문자는 사용 못한다는 의미
예) [^abc] (a나 b나 혹은 c를 포함하지 않은 한 문자)
[^0-9] (0에서 9까지의 숫자를 포함하지 않은 한 문자)
**[ Oracle 자료형 ]**
- DataBase에서는 table(relation)의 열을 column(attribute), 행을 raw(record,tuple)이라 한다.
- 각 column은 자료형(domain)을 가진다.
※ 숫자형
- 숫자는 보통 binary_integer형보단 number형을 사용한다.
- number는 메모리를 과도하게 차지하게 때문에 보통 자릿수를 지정하여 사용한다.
○ BINARY_INTEGER:-2147483647에서 2147483647 사이의 정수(4Byte:-2^31-1 ~ 2^31-1)
○ NUMBER:숫자 데이터/최대 38자리의 정수,실수
○ NUMBER(n):숫자 데이타/최대 n자리까지의 정수
○ NUMBER(p,s):숫자 데이타/부동 소수점 p는 소수점을 제외한 전체 자리수 s는 소수점이하의 자리수
-중요) 정수자리는 p-s 자리수 이하여야됨.
-결론) 정수자리수는 무조건! (p-s)자리수 맞춰야 하고, 소수점자리수는 넘어가면 반올림됨.
ex) salary number(5,2) +>전체 자리수는 5 소수점 이하는 2자리
123.56(O) ,1234.34(X),
123.3456789(O) ->소수점 2자리 이하는 반올림 처리됨(123.35)
123(o)
1234(x)
123.1(o)
12(o)
※ 문자형
- 문자는 무조건 single quotation으로 감싼다.
- N이 없으면 byte단위, N이 있으면 문자단위로 data를 저장한다.
- varchar계열은 메모리 낭비는 없지만, data의 자리수를 계산하여 저장하는 parsing과정을 거치기 때문에 속도 측면에서 char계열보다 느리다.
- char계열은 가변길이 data를 저장할 땐 메모리 낭비가 있지만, 데이터를 parsing하지 않고 바로 저장하기 때문에 속도 측면에서 유리하다.
- 한글 = 3byte (바뀜!!!)
○ CHAR(size): 주어진 size만큼 "고정 길이" 문자저장 필드 생성시에는 size 최대값이 2000임.
- 단위는 byte
- 영문 및 숫자는 최대 2000자까지
한글(유니코드)은 최대 1000자까지 저장
○ NCHAR(size):주어진 size만큼 "고정 길이" 문자저장 필드 생성시에는 size 최대값이 1000임. 한글/특수문자/영어 외 문자 등 저장할때 사용
- 단위는 문자
- 영문 및 숫자는 최대 1000자까지
한글(유니코드)은 최대 1000자까지 저장
○ VARCHAR2(size):"가변 길이" 문자 데이터 필드 생성시에는 size 최대값이 4000임.
- 단위는 byte
- 영문 및 숫자는 최대 4000자까지
한글(유니코드)은 최대 2000자까지 저장
○ NVARCHAR2(size):"가변 길이" 문자 데이터 필드 생성시에는 size 최대값이 2000임.
- 단위는 문자
- 영문 및 숫자는 최대 2000자까지
한글(유니코드)은 최대 2000자까지 저장
↓↓↓ 아래는 잘 사용하지 않는 문자열 자료형
○ LONG:"가변길이" 문자열(최대 4GB)
○ LONG RAW:"가변길이"의 바이너리 데이타(2GB=이미지,음악,영상...)이다. PL/SQL에 의해 해석되지 않는 자료형이다.
+) 실제로는 DB에 직접 미디어 데이터(바이너리 데이터)를 저장하진 않고, 미디어 서버를 따로 만들어서 저장. DB에는 그 미디어 서버의 데이터의 주소를 저장해서 끌어다 쓴다.
+) 자바와 마찬가지로, 미디어 데이터는 바이너리 형태로 저장된다.
○ CLOB:대용량의 텍스트 데이타를 저장(최대4GB)
+)가끔 사용.
○ BLOB:대용량의 바이너리 데이터 저장(최대 4GB).이미지 저장시 사용
※ 날짜/시간
○ DATE : 날짜와 시간에 대한 기본형.
**[ SQL ]**
- SQL(Structred Query Language)-구조화된 질의어 : DBMS(Oracle, MySQL, MS-SQL, DB2, INFORMIX등)의 데이타를 제어하기위한 표준 질의어
**[ DML ]**
- Data Manipulation Language
- table에 저장된 data를 SELECT (조회), INSERT (삽입), UPDATE (수정), DELETE (삭제)하는 질의어. (CRUD = Create Read Update Delete)
※ SELECT문
- 데이터를 조회하고자 할 때 사용하는 DML문이다.
- 문법)
⑤ SELECT 컬럼이름,컬럼이름,.....
① FROM 테이블 이름
② [WHERE 찾는 조건]
③ [GROUP BY 결과 데이터 그룹회]
④ [HAVING 그룹에서 찾는 조건]
⑥ [ORDER BY 데이터 정렬 표현]
- 쿼리 실행 순서 !!!
FROM절 >> WHERE절 >> GROUP BY절 >> HAVING절 >> SELECT문 >> ORDER BY절
FROM절 > 해당 TABLE의 모든 데이터를 불러옴
WHERE절 > WHERE절 조건을 만족하는 RECORD를 선별
GROUP BY절 > 그룹핑
HAVING절 > GROUP에서 HAVING절 조건을 만족하는 RECORD를 선별
SELECT문 > SELECT절에 작성한 컬럼대로 조회
ORDER BY절 > ORDER BY에 의해 정렬
○ SELECT * : 해당 테이블의 모든 컬럼 조회
○ SELECT 컬럼명 : 특정 컬럼 조회
○ 산술 표현식 : + , - , * , /
- %(나머지)연산자는 따로 없고, 오라클 명령어를 이용해야 한다.
- 데이터가 출력되는 방식을 수정하거나 계산을 수행하고자 할 때 산술 표현식을 사용한다.
- 산술 표현식은 열 이름,숫자상수,문자 상수, 산술 연산자를 포함 할 수 있다.
○ 별칭
- 질의의 결과를 출력할 때 혹은 테이블명이나 컬럼명이 길 때, 사용자가 이해하기 쉽게 이름을 부여 할 수 있다.
- 문법) 컬럼명/테이블명 [AS] “별칭”
- AS는 생략가능
- 별칭에 공백, single quotation이 들어가면 double quotation으로 감싸야 한다.
○ DISTINCT 키워드
- 문법) SELECT DISTINCT 컬럼명1[,컬럼명2,...]
- DISTINCT키워드는 항상 SELECT문의 바로 뒤에만 올 수 있다.
- DISTINCT뒤에 여러 개의 컬럼을 기술하엿을 때 나타나는 행은 컬럼의 조합(AND연산)들이 중복되지 않게 나타난다.
ex) SELECT DISTINCT job FROM emp;
>> JOB column의 중복된 record가 제거하여 조회한다.
SELECT DISTINCT job,ename
FROM emp;
>> (job & ename) 조합으로 유일한 행만 조회한다.
○ WHERE절
- 특정 행의 검색, 일반적인 경우 테이블에 있는 모든 자료를 조회할 필요 없이 사용자가 원하는 자료를 조회하는 경우 WHERE절을 사용하여 조회한다.
- WHERE절은 FROM절 다음에 기술한다.
- 조건은 COLUMN명,표현식,상수,문자, 비교연산자,SQL연산자,논리 연산자 등으로 구성된다.
○ 비교연산자 : = , > , < , >= , <= , !=(<>)
- 같다,초과,미만,이상,이하,다르다
○ 논리연산자 : AND , OR , NOT
- 자바와 같다.
○ SQL연산자
● BETWEEN a AND b
- a이상 AND b이하와 같은 의미이다.
- 두 값의 범위에 해당하는 행을 출력하기 위해 사용한다.
- 문법) 컬럼명 BETWEEN 값1 AND 값2
‘컬럼명 >= 값1 AND 컬럼명 <= 값2’ 와 같다.
● IN
- 목록에 있는 값에 대해서 출력하기 위해 IN연산자를 사용한다.
- IN 연산자는 OR로 연결된 논리연산자의 결합과 같다.
- 문법) 컬럼명 IN (값1,값2,...)
‘컬럼명=값1 OR 컬럼명=값2 OR ...’와 같다.
● LIKE연산자
- 검색 값에 대한 와일드 카드(%,_,[]) 검색을 위해서 LIKE연산자를 사용한다.
- % : 임의의 0개 이상의 문자열
- _ : 임의의 한 글자
- [] : 특정 범위를 만족하는 문자 한 개
- 문법) 컬럼명 LIKE ‘와일드카드 사용’
ex) LIKE 'A%':컬럼이 'A'로 시작하는 데이터들만 검색됩니다.
>> ABC,A123,A
LIKE '%A':컬럼이 'A'로 끝나는 테이터들만 검색됩니다.
>> BCA,123A,A
LIKE '%KIM%':컬럼에 'KIM' 문자가 있는 데이터 들만 검색됩니다.
>> ABCKIM ,ABCKIMDEF,KIMABC ,KIM
LIKE '_A%':컬럼에 'A'문자가 두 번째 위치한 데이터 들만 검색됩니다
>> DA(O),DDDA(X),A(X)
LIKE '[A-C]AN': A/B/C중 하나로 시작해야 한다.
>> AAN(O),AN(X),BAN(O),CAN(O),ABAN(X)
● IS NULL / IS NOT NULL 연산자
- NULL에 대한 설명은 용어정리 참고
- NULL인지 아닌지 판단하는 데 사용하는 연산자이다. NULL에는 다른 연산자를 사용하면 연산 결과가 NULL이기 때문에 ‘IS’로만 판단할 수 있다.
○ ORDER BY절
- ORDER BY절은 행을 정렬하는 데 사용한다.
- 문법) "SELECT문의 맨 뒤에 기술" 되어야 한다.
Order BY 컬럼 내지 표현식 [ASC|DESC] - ASC가 default
ex) ORDER BY 컬럼1 , 컬럼2 DESC, ... ;
- 디폴트 정렬은 오름차순이다.
- 내림 차순으로 정렬하려면 ORDER BY절에서 열 이름 뒤에 DESC 키워드를 명시해야한다.
- 하나 이상의 열로 질의 결과를 정렬할 수 있다.
ex) ORDER BY 컬럼1 , 컬럼2 DESC, ... ;
- 주어진 테이블에 있는 컬럼 개수까지만 가능하다. 실행 순서가 SELECT문 다음이기 때문에 SELECT문에 있는 컬럼만을 가지고 정렬해야 한다.
- ORDER BY절에서 열을 명시하고, 열 이름은 콤마(,)로 구분한다,
○ GROUP FUNCTION 그룹함수 (=집합함수)
● COUNT(*): 테이블의 전체 레코드 개수를 가져온다
● COUNT(컬럼명): 해당 컬럼에서 NULL이 아닌 행의 개수를 가져온다.
● AVG(컬럼명): 해당 컬럼의 전체 평균값을 구한다.
● MAX(컬럼명): 해당 컬럼의 가장 큰 값을 구한다.
● MIN(컬럼명): 해당 컬럼의 가장 작은 값을 구한다
● SELECT SUM(컬럼명) : 합계 구하기
- 그룹함수는 SELECT절에서 단독으로 쓸 수 있으나 다른 칼럼정보를 함께 출력할 수 없다.
- 그룹함수가 아닌 칼럼을 SELECT절에 나열할 경우, 그 모든 컬럼들을 GROUPING해줘야 한다.
>> 왜? 조회결과수가 안 맞아서 (그림설명)
○ GROUP BY절
- "~~별로 ~~를 조회하라"에서 "~~별"에 해당하는 부분이다.
- GROUP BY는 ORDER BY전에 기술해야 한다.
- 그룹에 대한 조건은 HAVING절에서 기술해야 한다. '그룹'과 관련된 컬럼 또는 그룹함수에 조건을 걸어야지 그외의 컬럼에 조건을 걸면 에러 발생.
- WHERE조건과 함께 쓰면 "WHERE조건절에서 먼저 필터링" 한후 그룹화 한다.
- 문법) GROUP BY 컬럼명,컬럼명,...
○ HAVING절
- GROUP BY의 조건을 기술하는 절이다. WHERE절이 FROM을 제한하는 것이라면 HAVING은 GROUP을 제한한다.
(그림설명_그룹함수)
○ JOIN문
- 테이블 간 조인을 할 경우, 조인조건에서 사용하는 컬럼명은 테이블명까지 명시해야 한다. >> 테이블명.컬럼명
- 두 개 이상의 테이블로부터 자료를 검색하기 위해서 join을 사용한다.
- 일반적으로 primary key와 foreign key을 사용하여 join 하는 경우가 대부분
● INNER JOIN
- 문법)SELECT e.deptno,ename,dname
FROM emp e JOIN dept d ON e.deptno=d.deptno;
- INNER 키워드는 생략 가능
- ON절 = 조인조건 = 부모테이블의 PK와 자식테이블의 FK를 조인조건에서 사용한다.
- 가장 많이 사용되는 조인문으로 테이블 간에 연결 조건을 모두 만족하는 행을 검색하는데 사용한다.
- 검색시 검색되는 컬럼이 조인하는 테이블 모두에 존재한다면 반드시 컬럼명에 테이블 이름을 "테이블명.컬럼명" 의 형태로 기술해야 한다.
-INNER JOIN은 자식테이블(FK가 있는 테이블)을 "기준"으로 모든 데이터를 가져온다. 예를 들면) 자식테이블에 전체 레코드가 10개이면 INNER JOIN한 결과의 레코드 수는 10개이다.
- 세 개 이상의 테이블끼리 조인할 경우 문법)
SELECT 컬럼명, ...
FROM 테이블1 JOIN 테이블2 ON 조인조건1
JOIN 테이블3 ON 조인조건2 ~~
JOIN ~~~ ;
- TIP) 마지막 JOIN절 위의 FROM절을 “하나의 테이블”로 보면 이해하기 쉽다.
+) ORACLE에서의 조인 방법 : 조인할 테이블명은 FROM절에 다 나열하고, 표준SQL식 조인에서 ON절의 조건을 WHERE절로 다 내려서 AND로 연결.
ex) FROM 테이블1,테이블2,...
WHERE 조인조건1 AND 조인조건2 AND ... ;
● OUTER JOIN
- OUTER JOIN은 INNER JOIN과는 달리 두 테이블에 "조인 조건이 정확히 일치 하지 않더라도"어느 한쪽 테이블에서 결과값을 모두 가져오는 JOIN문이다."
- OUTER JOIN문을 사용할때는 "반드시" OUTER 전에 데이터를 어느 쪽 테이블에서 가져올지 즉 왼쪽인지(LEFT) 오른쪽인지(RIGHT) 아니면 양쪽 테이블(FULL)인지 "반드시" 기술해야 한다. = 즉, ‘기준’을 정해줘야 한다.
- 규칙)자식을 기준으로 아웃터 조인하면 INNER조인과 같다.
보통 부모를 기준으로 OUTER JOIN한다.
보통 공통 컬럼도 부모의 컬럼을 SELECT한다.
관계형 DB에서 FULL OUTER JOIN은 의미가 없다.
- 문법) INNER JOIN과 같은데, OUTER와 기준방향을 명시한다.
- 구체적 사용법은 그림설명 참고
(그림설명_OUTERJOIN)
● SELF JOIN
- 자기 자신의 COLUMN을 JOIN하는 경우.
- 부모를 기준으로 하는 OUTER JOIN으로 SELF JOIN을 구현한다.
- 문법) FROM 테이블(부모역할) LEFT OUTER JOIN 테이블(자식역할) ON 부.PK=자.FK;
ex) FROM EMP p RIGHT OUTER JOIN EMP c ON p.empno = c.mgr;
매니저 컬럼 = 자식, EMPNO컬럼 = 부모.
p.empno = c.mgr 매니저컬럼은 EMPNO컬럼을 참조한다.
○ 서브쿼리 SubQUERY
- 조건)
1) 서브쿼리는 다른 하나의 SQL문장 안에 기술된 SELECT문이다.
2) 서브쿼리는 괄호()로 묶어야 한다.
3) 서브쿼리 단독으로 실행했을 때도 문제가 없이 잘 작동해야 한다.
4) 단일행 연산자, 복수행 연산자를 함께 사용한다.
● 단일행 연산자 : 연산결과로 나오는 RECORD가 1개인 연산자.
- = , > , < , >= , <= , != 비교연산자의 우항에는 1개의 값만 있어야 한다. X=1,2 이렇게 사용하지 않음.
ex)
SELECT *
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
● 복수행 연산자 : 연산결과로 나오는 RECORD가 1개 이상인 연산자.
- IN , NOT IN 논리연산자를 사용.
ex)
SELECT *
FROM emp
WHERE (job,sal) IN (SELECT job,MAX(sal) FROM emp GROUP BY job);
-- 이 때, (job,sal)의 순서도 중요하다.
5) 서브쿼리는 연산자의 오른쪽에 기술되어야 한다.
6) 단일행 서브 쿼리에는 단일행 연산자를, 다중행 서브쿼리에는 복수행 연산자를 사용한다.
7) 서브쿼리는 SELECT절, FROM절, WHERE절 등에 위치할 수 있다.
○ 탑쿼리 TOP QUERY
- MS-SQL에서는 top 키워드로, MySQL에서는 limit 키워드로 TOP쿼리를 구현한다. 오라클은 키워드가 따로 없고, ROWNUM을 이용해 구현한다.
- 위 또는 아래에서부터 N개의 RECORD를 조회하고 싶을 때, 얻어진 질의 결과에서 위에서부터 순서대로 몇 개만 가져오는 경우에 사용한다.
- 오라클은 주로 FROM절에서 서브쿼리를 이용해 TOP쿼리를 구현한다.
- top쿼리는 반드시! 정렬한 후에 사용한다!!!
- 구간쿼리로도 TOP쿼리를 구현할 수 있기 때문에 보통 그냥 구간쿼리를 사용함.
● ROWNUM : ORACLE에서 자동으로 생성하는 내부컬럼으로, 데이타가 입력된 순서대로 혹은 서브퀴리에 의해 생성된 테이블에 레코드가 생성된 "순서대로 내부적으로 부여된 번호를 값으로 가지는" 컬럼이다.
- 문법)
SELECT t.*,rownum
FROM (서브쿼리) t
WHERE rownum 비교연산(>/</<=/>=) 숫자N ;
>> 서브쿼리의 테이블에서부터 N번째 이상/이하/초과/미만의 행을 조회한다.
○ 구간쿼리
- 특정 ROWNUM 구간에 있는 레코드를 조회한다. (BETWEEN a AND b를 이용한다.)
- 문법)
SELECT 조회할 컬럼
FROM (SELECT t.컬럼명,ROWNUM rn FROM
(SELECT * FROM 테이블명 ORDER BY 컬럼명) t)
WHERE rn BETWEEN 특정ROWNUM AND 특정ROWNUM ;
- 구간쿼리 구현하는 방법)
1) FROM절의 서브쿼리안의 서브쿼리는 특정 컬럼(보통 PK컬럼)으로 반드시!!! ORDER BY (정
렬)해야 하고 생성된 테이블 별칭을 부여
2) 서브쿼리안의 SELECT절에서 STEP1의 별칭.*,ROUNUM 컬럼별칭을 기술한다
3) 밖의 WHERE절에서 ROWNUM을 별칭한이름으로 between a and b한다.
-- 보통은 제일 내부의 서브쿼리에서는 모든 컬럼(*)을 조회한다.
-구간쿼리/top쿼리에서 join을 사용할 때는 웬만하면 서브쿼리 내에서 JOIN하기보단
FROM절 아래에서 JOIN하는 것이 더 쉽다.
ex1) 서브쿼리 안에서 JOIN ==> 이 경우에 ambiguous identifier 오류가 안 나도록 주의해야 한다!!!
SELECT ename 이름,sal 연봉,deptno 부서코드,dname 부서명
FROM (SELECT t.*,ROWNUM rn FROM (SELECT e.*,dname FROM emp e JOIN dept d ON e.deptno=d.deptno ORDER BY sal DESC) t) t1
WHERE rn BETWEEN 6 AND 10;
ex2) FROM절 아래에서 JOIN
SELECT ename 이름,sal 연봉,d.deptno 부서코드,dname 부서명
FROM (SELECT t.*,ROWNUM rn FROM (SELECT * FROM emp ORDER BY sal DESC) t) t1
JOIN dept d ON t1.deptno=d.deptno
WHERE rn BETWEEN 6 AND 10;
>> t1을 ‘하나의’ 테이블로 보고 조인하면 이해하기 쉽다.
○ INSERT문 - 데이터 입력
- 데이터 입력을 위한 SQL구문 작성시 문자형(CHAR혹은 VARCHAR2등)은 반드시 '(작은따옴표)로 값을 감싸야 한다. 숫자형은 작은따옴표가 필요없다.
- 문법)
1) 전체 컬럼에 값을 INSERT할 때
INSERT INTO 테이블명 VALUES(값1,값2,...);
2) 특정 컬럼에만 값을 INSERT할 때
INSERT INTO 테이블명(컬럼1,컬럼3,...) VALUES(값1,값3,...);
- DATA로 NULL을 삽입하고 싶을 때 : VALUES에 '' 혹은 NULL 입력. 보통 NULL을 쓰는 게 좋다.
- DEFAULT값으로 삽입하고 싶을 때 : VALUES에 DEFAULT 입력.
ex) INSERT INTO 테이블명 VALUES(값1,값2, NULL, DEFAULT);
○ UPDATE문 - 데이터 수정
- SQL이다.
- 문법)
UPDATE 기존테이블명
SET 컬럼명 = 수정할값
[WHERE 업데이트 조건]
- WHERE절을 쓰지 않으면 "모든" DATA가 수정됨. = TABLE 전체 수정.
- 주로 PK컬럼을 이용하여 수정.(왜? PK를 써야 RECORD가 1개 나오니까.)
○ DELETE문 – 데이터 삭제
- SQL구문이다.
- 문법)
DELETE [FROM] 기존테이블명
[WHERE 삭제 조건]
- WHERE절을 쓰지 않으면 "모든" DATA가 삭제됨. = TABLE 스키마만 남긴다.
- 주로 PK컬럼을 이용하여 수정.(왜? PK를 써야 RECORD가 1개 나오니까.)
● TRUNCATE TABLE : 기능은 DELETE FROM 테이블명과 같다, 즉 테이블안에 잇는 모든 데이타를 삭제한다. 기능을 수행하는 방식이 다르다.
- TRUNCATE TABLE은 DROP TABLE + CREATE TABLE 로 작업을 수행한다. 즉, DROP해서 데이터를 모두 지운 다음, 해당 TABLE의 스키마대로 다시 CREATE한 것.
- DELETE VS TRUNCATE 비교
구분
DELETE
TRUNCATE
대용량 데이터
속도 느림
속도 빠름
ROLLBACK 가능여부
ROLLBACK 로깅정보를 남김
>> ROLLBACK 가능
ROLLBACK 로깅정보를 남기지 않음
>> ROLLBACK 불가능=복원 불가
실행 과정
하나씩 DELETE
DROP TABLE -> CREATE TABLE
**[ DDL ]**
- Data Definition Language
- Entity(개체. table, view, StoredProcedure ...)를 정의하는 질의어
- Entity를 CREATE (생성), ALTER (수정), DROP (삭제)한다.
○ CREATE문 – 테이블 생성
- 문법)
CREATE TABLE 테이블명(
컬럼명1 자료형1 [CONSTRAINT 제약조건명] [제약조건1, 제약조건2, ...]
, 컬럼명N 자료형N [] []
, [CONSTRAINT 제약조건명] [제약조건(칼럼명),제약조건(칼럼명),...]
);
-- 컬럼을 정의하고 바로 뒤에 제약조건을 써도 되고, CREATE문의 마지막 절에 제약조건을 몰아서 써도 되는데 이때는 제약조건(컬럼명)으로 어느 컬럼에 대한 제약조건인지 명시해야 한다.
- 테이블 및 컬럼 이름 명명규칙)
1) 문자로 시작한다.
2) 30자 이내여야 한다.
3) 동일한 이름(DUPLICATED), 예약어는 사용 불가
4) 대소문자 구분하지 않는다. (SQL은 DATA만 대소문자 구분)
● 4가지 제약조건
- 하나의 데이터베이스는 4가지 제약조건을 모두 만족해야 한다.
1. Domain CONSTRAINT 도메인 제약
- column(attribute)의 값(data)이 지정한 범위(domain) 내에 있어야 한다.
- Multivalued Attribute는 허용되지 않는다. ex) 취미column에 ‘축구,농구,배구’ 3개의 데이터를 INSERT할 수 없다.
2. Key CONSTRAINT 키 제약
- table은 키key를 가져야 한다.
- TABLE(RELATION)의 모든 RECORD(TUPLE)는 서로 식별 가능해야 한다.
3. Entity Integrity CONSTRAINT 개체무결성 제약
- 개체의 기본키(Primary Key)는 UNIQUE & NOT NULL 이어야 한다.
4. Referential Integrity CONSTRAINT 참조무결성 제약
- 어떤 릴레이션R1의 어떤 속성A가 다른 릴레이션R2의 속성A을 참조한다면 그 속성은 Foreign Key가 되어야 한다.
- FK는 NULL 이거나
NULL이 아닌 경우, R2.A에 실제로 존재하는 값이어야 한다.
- 제약조건 CONSTRAINT 설정
● 기본 키(PRIMARY KEY)
- 참조무결성,개체무결성을 유지하기 위한 제약조건이다.
- 하나의 테이블에는 하나의 PK만 존재한다. PK를 구성하는 컬럼이 다수일 수는 있다(=COMPOSITE KEY).
- PK는 NOT NULL 그리고 UNIQUE 이다.
- 문법)
: 컬럼명 자료형 [CONSTRAINT 제약조건명] PRIMARY KEY
: 컬럼명 자료형,
[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명)
: 컬럼명1 자료형1, 컬럼명2 자료형2, 컬럼명3 자료형3, ... ,
[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명1,컬럼명2) (COMPOSITE KEY)
● NOT NULL
- NULL값을 허용하지 않는다.
- 문법)
: 컬럼명 자료형 [CONSTRAINT 제약조건명] NOT NULL
: 컬럼명 자료형,
[CONSTRAINT 제약조건명] NOT NULL(컬럼명)
● UNIQUE
- 값의 중복을 허용하지 않는다. "NULL은 허용"한다
- ORACLE은 NULL을 여러 번 사용 가능.
- MS-SQL은 NULL도 중복을 허용하지 않는다.(딱 1개의 NULL만 허용)
- 문법)
: 컬럼명 자료형 [CONSTRAINT 제약조건명] UNIQUE
: 컬럼명 자료형,
[CONSTRAINT 제약조건명] UNIQUE(컬럼명)
● DEFAULT
- DEFAULT는 데이터 입력시 해당 컬럼에 아무값도 입력하지 않았을 때 자동적으로 입력해주는 기본값이다. (제약조건명을 지정할 수없다, 마지막줄에서 제약조건 지정 불가)
- 주로 'SELECT SYSDATE FROM DUAL;', 오늘 날짜를 DEFAULT로 많이 사용.
- 문법) 컬럼명 자료형 DEFAULT 기본값
● FOREIGN KEY
- '외래키'로 '참조무결성'을 유지하기 위한 제약조건이다.
-문법1)
CREATE TABLE 테이블명(
컬럼명1 자료형1 PRIMARY KEY ,
컬럼명2 자료형2 [CONSTRAINT 제약조건명] REFERENCES 부모테이블(컬럼명) ,
...);
-문법2.마지막줄에 명시)
CREATE TABLE 테이블명(
컬럼명1 자료형1 PRIMARY KEY
, 컬럼명2 자료형2
, ------------------
컬럼명N 자료형N ,
[CONSTRAINT 제약조건명] FOREIGN KEY(컬러명2) REFERENCES 부모테이블(부모테이블의 PK컬럼) );
-FOREIGN 생성시 부모의 컬럼과 자료형이 일치해야 한다.
-FOREIGN KEY 생성시 추가할 수 있는 "옵션"
ON DELETE CASCADE 혹은 ON DELETE SET NULL조건을 추가할수 있다.
단, UPDATE CASCADE옵션은 오라클에는 없음
-문법)
REFERENCES ~ ON DELETE/UPDATE CASCADE : 부모의 PK레코드(값)가 삭제/수정 되면 자식의 FK값도 삭제/수정.
REFERENCES ~ ON DELETE/UPDATE SET NULL : 부모의 PK레코드가 삭제/수정 되면 자식의 FK값을 NULL로 변경.
# CASCADE = (영어:종속) (SQL: DATA가 서로 연결(종속)된다)
+) 계정에 존재하는 제약조건들을 보고 싶을 때. 데이터 사전으로 볼 수 있다.
SELECT * FROM user_cons_columns
(사용자가 생성한 제약조건들을 ORACLE이 해당 TABLE에 모아둔다.)
● CHECK
- "DOMAIN무결성"을 유지하기 위한 제약으로 해당 컬럼에 잘못된 데이터가 입력 되지 않도록 유지하기 위한 제약조건이다.
# DOMAIN = DATA의 범위/범주
- WHERE절에서 쿼리 쓰듯이 CHECK(내부)의 내부를 작성하면 된다.
- CHECK의 조건으로 regexp_like()함수를 사용할 수 있다.
- 문법)
: CHECK( SQL구문 )
: CHECK( regexp_like(컬럼명, ‘ 정규표현식 ’) )
(regexp_like함수는 DUAL 테이블에서는 실행이 안 된다. 당연, 비교할 컬럼이 없으니.)
● 기존 테이블의 구조와 내용(DATA)을 그대로 복사해서 테이블 생성하기
- 문법)
: CREATE TABLE 새테이블명
AS
SELECT * FROM 기존테이블명;
- 단, 제약조건은 복사가 안 된다.
- 테이블 DATA 백업용으로 사용하는 것이 좋다.
○ ALTER문 – 테이블 수정
● 기존테이블에 새로운 컬럼 및 제약조건 추가
- 문법)
: ALTER TABLE 테이블명 ADD 컬럼명 자료형 제약조건;
: ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명);
- 한 번에 여러 제약조건을 추가할 수 없다.
ALTER TABLE 테이블 ADD 제약조건1(COL),제약조건2(COL) 불가..
NOT NULL 제약조건을 추가하기 위해선 해당 테이블이 ‘비어 있는 상태’여야 한다.(그림설명)
● 기존테이블의 기존 컬럼 및 제약조건 삭제
- 컬럼 삭제)
- 문법) ALTER TABLE 테이블명 DROP COLUMN 컬럼명
- 제약조건 삭제)
- 문법) ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
(그림설명_NOT NULL컬럼 추가 시 주의점)
● 기존테이블의 기존 컬럼 수정
- 데이터 타입(자료형)과 자료형의 크기(size) 변경)
- 문법) ALTER TABLE 테이블명 MODIFY 컬럼명 새로운자료형(size);
- 마찬가지로 자료형을 수정할 때는 기존 테이블에 데이터가 없어야 한다.
- 자료형의 size를 변경할 때는 기존보다 큰 size(기존의 DATA를 담을 수 있는 size)로 수정해야 한다. 기존의 data를 담을 수 없으면 ERROR 발생.
- 컬럼명 변경)
- 문법) ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 새컬럼명;
● 기존테이블의 기존 제약조건은 수정이 불가능하므로, 삭제 후 새로 추가하는 것밖에 방법이 없다.
● 테이블명 수정
- 문법) RENAME 기존테이블명 TO 새테이블명;
○ DROP문 – 테이블 삭제
● 기존 테이블 삭제
- 문법) DROP TABLE 테이블명;
- 해당 테이블이 다른 테이블의 부모테이블이라면 CASCADE CONSTRAINT해야 한다.
- 오라클 11g버전 이전에는 DROP TALBE과 DROP TABLE ~ PURGE를 구분했다. PURGE는 완전삭제를 의미. 이전에는 DROP TABLE한 테이블이 휴지통으로 들어가서 SHOW RECYCLEBIN 명령어를 실행하면, 휴지통에 해당 테이블이 보였는데, 11g버전 이후부터는 DROP TABLE이 DROP TABLE ~ PURGE까지 한다.
+) 의미없어진 구문들
DROP TABLE 테이블명 PURGE; : 테이블 완전삭제
FLASHBACK TABLE 테이블명 TO BEFORE DROP; : 휴지통의 테이블 복원
SHOW RECYCLEBIN : 휴지통 보기
PURGE RECYCLEBIN; : 휴지통 비우기
PURGE TABLE 테이블명; : 휴지통에 있는 테이블 완전삭제
● 기존 테이블의 제약조건까지 삭제
- 삭제할 테이블이 다른 테이블과 관계를 맺고 있다면 해당 CONSTRAINT를 끊고 테이블을 DROP한다.
- 문법) DROP TABLE 테이블명 CASCADE CONSTRAINT;
- 참조관계(FK제약조건)을 끊고 해당 테이블을 DROP한다. >> 자식테이블의 DATA들은 그대로 남지만, 자식 테이블의 해당 컬럼은 FK제약조건이 DROP된 상태가 된다.
**여러 개체들 ENTITY ]**
※ SEQUENCE 시퀀스
- 시퀀스 개체는 ‘일련번호’를 부여할 때 사용한다.
- MS-SQL, MySQL은 시퀀스객체를 사용하지 않고 따로 키워드를 사용하여 일련번호를 부여한다.
- 시퀀스를 모른다고 가정할 때 일련번호 특성의 데이터를 입력하는 방법)
: INSERT INTO 테이블명(일련번호컬럼) VALUES((SELECT MAX(일련번호컬럼)+1 FROM 테이블명));
(단, 일련번호컬럼에 1개 이상의 DATA가 있어야 한다.)
○ 시퀀스 생성)
: CREATE SEQUENCE 시퀀스명
[INCREMENT BY 증가값]
[START WITH 시드값]
[MAXVALUE m | MINVALUE n]
[CYCLE | NOCYCLE]
[CACHE/NOCACHE]
● INCREMENT BY : 일련번호를 몇씩 증가할 것인지.
- DEFAULT = 1
● SEED : 일련번호 처음 시작 값.
- DEFAULT = MINVALUE 또는 MINVALUE미지정 시 1
● MAXVALUE : 일련번호 최대값.
- DEFAULT = 1.0000E+28
● MINVALUE : 일련번호 최소값, CYCLE 시 SEED값.
- DEFAULT = 1
● CYCLE : 일련번호의 MAXVALUE에 도달하면 다음 일련번호부터는 MINVALUE부터 다시 시작한다.
● NOCYCLE : 일련번호의 MAXVALUE에 도달하면 "시퀀스명.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다." 에러를 발생시킨다.
- DEFAULT로 설정.
● CACHE : CACHE메모리에 다음 일련번호 미리 생성해둔다.
- DEFAULT로 설정되었으며, DEFAULT값은 20이다.
- CYCLE인 경우, CACHE 값 < MAXVALUE여야 한다.
ex) CREATE SEQUENCE 시퀀스
MAXVALUE 10 CYCLE; 인 경우,
시퀀스 실행 시 CACHE에는 1~20의 일련번호를 미리 생성해둠. 그런데 MAXVALUE 10에 도달 후 2번째 CYCLE을 시작할 때, 1을 가져와야 하는데 CACHE에 의하면 다음 일련번호는 11이 되어야기 하기 때문에 충돌이 발생. >> "CACHE 에는 1 사이클보다 작은 값을 지정해야 합니다" 에러발생
>> NOCYCLE로 지정하거나, CACHE 값 < MAXVALUE을 지켜야 한다.
- 시퀀스 개체의 데이터 사전)
: SELECT * FROM USER_SEQUENCES;
○ 시퀀스 수정
: ALTER SEQUENCE 시퀀스명 (변경할 속성) 변경할 값 ;
○ 시퀀스 삭제
: DROP SEQUENCE 시퀀스명 ;
※ VIEW 뷰
- VIEW는 하나 또는 그 이상의 테이블로부터 생성된 가상의 테이블(메모리에 저장된 테이블)이다.
- 물리적으로 존재하지 않는 테이블 / 테이블=물리적인 테이블= HDD에 저장된 테이블
- 복잡한 질의어를 통해 얻을 수 있는 결과를 간단할 질의어를 써 구할 수 있다.(자바의 메소드처럼 복잡한 질의어를 뷰로 생성하여 해당 뷰를 메소드처럼 사용.)
- 하나의 테이블로 만든 VIEW에서는 DML(INSERT,DELETE,UPDATE)문장을 수행 할 수 있지만(원본TABLE도 변형됨)
여러 테이블로 만든 VIEW(PK와 FK로 조인한)에서는 DML문(INSERT)을 수행 할 수 없다,(단,UPDATE와 DELETE는 가능-BUT 사용 안 하는 걸 추천.)
- VIEW생성시 ORDER BY를 포함 할 수 있다.(ORACLE 8i이전버전까지는 불가능. 9i이후부터 ORDER BY 가능)
- 장점)
1)보안성 - 중요한 컬럼을 숨길 수 있어 보안을 유지 할 수 있다.
2)편의성 - 여러 개의 테이블 사용을 위해 복잡한 조인이 사용되었다 하더라도 사용자는 이를
몰라도 된다.
3)간결성 - 뷰를 사용하면 APPLICATION을 개발할 때 복잡한 쿼리문을 숨길 수 있어 소스가
간결해 진다.
- 문법)
○ VIEW 생성
CREATE [OR REPLACE] VIEW 뷰명 [(“별칭1”,“별칭2”,...)]
AS
SELECT문 ~
[WITH READ ONLY] ;
- SELECT문의 조회결과로 나온 TABLE이 VIEW가 된다.
● OR REPLACE : 해당 뷰명을 가진 VIEW가 존재한다면 새로 대체한다는 의미. 쓰는 것이 좋다. VIEW 수정과 동일.
● 별칭 : VIEW컬럼의 별칭은 반드시 “”로 감싸야 한다.
● WITH READ ONLY : 뷰를 읽기전용으로 만든다. INSERT/DELETE/UPDATE불가.
○ VIEW 수정
REPLACE VIEW ~ 이하 동일
○ VIEW 삭제
DROP VIEW 뷰명
- VIEW개체의 데이터 사전)
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
※ INDEX 인덱스
- 행의 검색 속도를 향상 시킬 수 있는 '개체'
- 인덱스를 명시적(CREATE INDEX) 또는 자동적으로 제약조건으로(PRIMARY KEY,UNIQUE KEY)로 생성 할 수 있다.(PK제약조건, UNIQUE제약조건을 설정하면 인덱스가 자동으로 생성됨.)
- 장점) 쿼리의 조회성능을 향상시켜준다.
- 주의점) DML(INSERT/UPDATE/DELETE)이 많은 컬럼에 INDEX를 부여하면 MANIPULATION이 일어날 때마다 RECORD에 재인덱싱을 하기 때문에 데이터 조작 시 성능이 저하된다.
● Clustered INDEX
● NonClustered INDEX
구분
Clustered INDEX
NonClustered INDEX
생성 방법
PK/UNIQUE 제약조건 설정 시 자동으로 INDEX 생성
CREATE INDEX 구문으로 생성
테이블 당 INDEX
테이블 당 1개 (‘리프페이지=데이터페이지’이기 때문.)
테이블 당 240개
인덱스페이지
용량
작다.(‘리프페이지=데이터페이지’이기 때문.)
크다. 데이터와 별도로 인덱스페이지를 따로 만들기 때문.
검색(조회) 성능
↑
↓
데이터 조작 성능
↓
↑
정렬
물리적으로 행을 정렬하여 재배열
실제 DATA를 변형하지 않음.
인덱스 페이지만 정렬
권장 사용 비율
30% 이내
3% 이내
+) 비유 : 쉽게 책에 비유하자면 클러스터 인덱스는 페이지를 알기 때문에 바로 그 페이지를 펴는 것이고, 넌 클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것과 같습니다. 테이블 스캔은 처음부터 한 장씩 넘기면서 내용을 찾는 것과 같다.
- INDEX를 설정하면 좋은 컬럼)
1. WHERE조건이나 조인 조건에서 자주 사용되는 컬럼.(주로 PK컬럼)
2. 광범위한 값을 포함하는(광범위한 DOMAIN을 가진) 컬럼
3. 많은 NULL값을 포함하는 컬럼.
○ INDEX 생성
- 문법) CREATE [CLUSTERED] INDEX 인덱스명 ON 테이블명(컬럼1 [컬럼2,...]);
-- 해당 테이블의 컬럼1[,컬럼2,..]에 대한 넌클러스터 인덱스를 생성한다. 그러면, 컬럼1[,컬럼2,...]을 정렬하여 인덱스 페이지를 구성한다.
○ INDEX 수정은 불가능하다.
○ INDEX 삭제
- 문법) DROP INDEX 인덱스명;
- INDEX 데이터 사전 보기)
SELECT * FROM user_ind_columns;
+) 참고 (<https://mozi.tistory.com/m/320>)
+) 인덱스 검색 과정
(그림설명_인덱스검색과정)
**[ DCL ]**
- Data Control Language
- 사용자에게 데이터를 제어할 권한을 GRANT(부여)하거나 REVOKE(취소)하는 질의어
- GRANT / REVOKE 하기 위해서는 DBA역할이 있는 최고 관리자(SYSTEM/SYS)계정으로 접속하여야 한다.
- 권한(Privileges)과 역할(Role)은 다르다.
# Privileges = 권한은 어떤 행동을 할 수 있는 자격을 의미한다. 즉, 어떤 계정이 실행할 수 있는 일/작업(CREATE/READ/UPDATE/DELETE) 등을 의미한다.
# Role = 역할은 권한을 묶는 기능을 한다. 역할=권한의 집합.
- 권한은 항상 ‘최소 권한’만 부여하는 것이 원칙이다.
○ 사용자 생성 및 암호 설정
- 문법) CREATE USER 아이디 IDENTIFIED [BY 암호 | EXTERNALLY];
[[DEFAULT | TEMPORARY]TABLESPACE 테이블스페이스명]
[QUOTA [UNLIMITED] ON 테이블스페이스명]
[QUOTA UNLIMITED ON 테이블스페이스명]
[ ... ]
- 사용자는 생성된 후 어떤 권한도 가지지 못한다.
○ 사용자에 권한 혹은 역할 부여
- 문법) GRANT ~권한[,권한2,권한3,...]또는 ~역할[,역할1,...]
TO 사용자1[,사용자2,..]
[WITH ADMIN OPTION] ;
- WITH ADMIN OPTION : 받은 권한/역할을 다른 사용자에게 부여할 수 있는 권한.
- A,B,C권한을 가진 역할을 사용자1이 부여받으면 사용자1도 A,B,C권한을 가진다.
● SYSTEM PRIVILEGE
- 사용자가 DB에 ACCESS하기 위한 시스템 권한.
- DDL(CREATE/ALTER/DROP)이 대표적인 시스템 권한.
- 시스템 권한은 주로 DBA역할의 사용자(SYSTEM/SYS)가 부여한다.
- - DBA역할 : 상급의 시스템권한들을 가진 역할이다.
- 시스템 권한 데이터 사전)
SELECT * FROM SYSTEM_PRIVILEGE_MAP;
+) ANY 계열(CREATE ANY/ALTER ANY)의 권한은 자기 계정의 개체뿐만 아니라 다른 계정의 개체도 변경할 수 있기 때문에 잘 사용하지 않는다.
ex)
CREATE SESSION : 접속 가능(conn 아이디/암호)
CREATE TABLE:테이블 생성 권한
CREATE VIEW :뷰 생성 권한
CRETAE USER:사용자 생성 권한등
● OBJECT PRIVILEGE
- 사용자가 DB 개체의 내용(DATA)을 조작하기 위한 권한.
- DML(SELECT/INSERT/DELETE/UPDATE)이 대표적인 객체 권한.
- 시스템 권한(CREATE TABLE)을 부여하면 자동으로 객체 권한(INSERT/SELECT/DELETE/UPDATE)이 부여 된다.
- 객체 권한 데이터 사전)
SELECT * FROM TABLE_PRIVILEGE_MAP;
● 외에도 200개 이상의 시스템 권한과 28개 이상의 개체권한이 존재.
● Predefined Role
- 오라클에서 제공하는 ROLE
- DBA역할 : 최고 권한을 가지는 역할
- CONNECT역할 : DB에 액세스 할 수 있는 권한을 가지는 역할
+) CONNECT role이 가진 privileges
-- CREATE SEESION
--
- RESOURCE역할 : 개체를 생성할 수 있는 권한을 가지는 역할
-- RESOUCRE role을 GRANT하면 해당 사용자에게 unlimited tablespace(system tablespace)를 제공하기 때문에, 보안/관리 상 문제가 될 소지가 있다.
>> DEFAULT TABLESPACE가 SYSTEM일 경우, RESOURCE role을 부여하면 system tablespace를 사용하게 되고, 여기에서 개체를 생성/조작한다.
>>
+) RESOURCE role이 가진 privileges
-- CREATE TRIGGER
-- CREATE SEQUENCE
-- CREATE TYPE
-- CREATE PROCEDURE
-- CREATE CLUSTER
-- CREATE OPERATOR
-- CREATE INDEXTYPE
-- CREATE TABLE
*보통 최소한의 권한으로 CONNECT와 RESOURCE역할을 부여한다. (CREATE VIEW 권한은 없음)
● User Role
- 사용자가 정의한 ROLE
○ 암호 변경
- 문법) ALTER USER 사용자아이디 IDENTIFIED BY 새 암호;
○ 할당량 부여 ???????????????????
- CREATE TABLE권한을 GRANT 한 후에는 할당량을 부여 해야 테이블을 생성할 수 있다
-문법) ALTER USER 사용자아이디 QUOTA 할당량 On Users;
예]ALTER USER Choi QUOTA 5M On Users;
-
○ 권한이 없는 다른 사용자에게 특정 테이블 조회 권한 부여하기
- 문법) GRANT SELECT ON 다른 사용자아이디.테이블 TO 사용자 아이디;
- GRANT SELECT ON만 되고, INSERT/DELETE/UPDATE는 불가능하다.
○ 역할을 통해 권한 동시에 주기(사용자 역할 정의하기)
1)ROLE 생성: CREATE ROLE STAFF;
2) ROLE에 권한 부여 : GRANT CREATE TABLE, CREATE VIEW TO STAFF;
3)사용자에게 User ROLE 부여 : GRANT STAFF TO 사용자1,사용자2;
○ 권한제거
- 문법) REVOKE 권한 및 역할 FROM 사용자;
- 단, WITH ADMIN OPTION으로 부여된 권한은 취소 되지 않는다.
○ 사용자 삭제
- 문법) Drop USER 사용자 CASCADE;
- 사용자와 관련된 모든 개체,권한 등을 같이 DROP한다.
+) 사용자 관련 데이터 사전))
+) 사용자 목록 보기
DESC DBA_USERS
SELECT USERNAME FROM DBA_USERS
+) 사용자가 가진 시스템권란 보기
DESC dba_sys_privs
select privilege from dba_sys_privs where grantee='사용자명'
+) 사용자 계정 잠금 풀면서 동시에 암호변경
alter user 사용자계정 account unlock identified by 암호
alter user 사용자계정 account unlock;계정푼거
alter user 사용자계정 identified by 암호;암호부여
○ GRANT EXECUTE ON 사용자계정1.함수 TO 사용자계정2 ;
- 계정2에게 사용자계정1의 함수에 접근할 수 있는 권한 부여.
**[ TCL ]**
- Transaction Control Language
# TRANSACTION 이란?
- '일련의 작업'을 '처리하는 단위'를 하나의 트랜잭션이라 한다.
ex) 은행 송금 업무가 대표적인 예시.
'국민은행'을 사용하는 '가'가 '우리은행'을 사용하는 '나'에게 10억을 송금하려 한다. 송금이라는 업무는 "가의 계좌에서 10억을 인출(UPDATE)+나의 계좌에 10억을 입금(INSERT)"하는 2개의 작업으로 이루어져 있다. 둘 중 하나라도 실패한다면 해당 업무는 취소되고 원래의 상태로 다 되돌아가야 한다.
이렇듯 하나의 업무에서 각각의 작업을 수행하는 도중에 방해가 이루어지면 안 되는 경우, 업무의 작업들을 '하나의 TRANSACTION'으로 묶어 관리한다.
- 일련의 작업에서 하나의 작업이라도 실패한다면 모든 작업을 취소시킨다(= ROLLBACK 한다)
- 일련의 작업이 정상적으로 끝나면 COMMIT한다(실제 DATA를 변형시킨다.일련의 작업 결과를 저장한다.)
○ 오라클의 트랜잭션 제어 명령어)
● COMMIT; :모든 미결정 데이타(TEMPORARY TABLEPLACE의 데이터)를 영구적으로 변경함으로써(PERMANENT TABLEPLACE에 반영) 현재 TRANSATION을 종료
● ROLLBACK [TO SAVEPOINT 세이브포인트명]; :모든 미결정 데이터 변경을 원래대로 돌림으로써 현재의 TRANSATION종료
● SAVEPOINT 세이브포인트명; :TRANSATION내의 SAVEPOINT표시/ANSI 표준 SQL이 아님(ORACLE만 있는 기능.)
- SAVEPOINT 전까지의 작업은 COMMIT되고, SAVEPOINT~ROLLBACK 사이의 작업은 ROLLBACK된다.
ex)
SQL>UPDATE emp SET deptno=30 WHERE empno=7369; --DML문1
SQL>SAVEPOINT point1
SQL>UPDATE emp SET job='MANAGER'; --DML문2
SQL>UPDATE emp SET SAL=500; --DML문3
SQL> ROLLBACK TO SAVEPOINT point1; -- >> point1지점부터 COMMIT 사이에 수행한 작업들(DML1,DML2)이 취소된다.
SQL> EXIT --COMMIT
-자동COMMIT/자동ROLLBACK
-- 자동으로 COMMIT이 일어나는 경우.
1. DDL(CREATE/ALTER/DROP), DCL(GRANT,REVOKE)문장 실행 시.
2. SQL*PLUS를 정상적으로 종료했을 시 = EXIT 실행 시.
∴ 주로 DML(특히 INSERT,UPDATE,DELETE) 문장들로 하나의 TRANSACTION을 구성한다.
-- 자동으로 ROLLBACK이 일어나는 경우.
1. SQL*PLUS를 비정상적으로 종료했을 시 = 창닫기 혹은 에러로 인한 실행 실패.
**[ PL/SQL ]**
+) ORACLE의 주요 Built-In FUNCTION
+) 기타 유용 코드
- PROCEDURAL LANGUAGE/SQL : 절차적 언어, 프로그래밍 언어의 특성을 수용한 SQL의 확장.
- SQL의 데이타 조작(DML)과 질의문을 블락 구조에 절차적 단위(IF,FOR,WHILE,LOOP등)로 된 커맨드를 포함 할 수 있으며 프로그래밍을 가능하게 한 언어이다.
- SELECT문의 조회결과는 단일행(단일RECORD)이어야 한다. SELECT문은 INTO와 같이 사용해야 한다. (아래서 자세히...)
- DDL문은 사용할 수 없다 !!!
- MS-SQL에서는 TL/SQL(TRANSACTUAL LAGUAGE/SQL)이라 한다.
-문법) PL/SQL 기본구조]
[DECLARE] -- 선언부
변수 및 상수, CURSOR,EXCEPTION선언 등
BEGIN — 실행부
SQL,PL/SQL문 둘 다 사용 가능
[EXCEPTION] - 예외처리부
END;
/
-- 명령문의 끝에는 세미콜론(;)을 붙여야 한다.
-- BEGIN 실행부를 종료하려면 ENG;하고 꼭 엔터를 친 후 /슬래쉬를 해야 한다.
-- 주석 : --한줄 주석, /* 여러줄 주석*/
○ 변수
● 변수선언
- DECLARE부에서 선언해야 한다.
- 문법) 변수명 [CONSTANT] 자료형 [NOT NULL] [:= 초기값 | DEFAULT 초기값 ];
- CONSTANT 붙이면 '상수'.
- 대입(할당)연산자가 “ := ” 이다.
- 한 라인에 하나의 식별자(변수)만 선언 가능 (ex) v_num,v_num2 NUMBER; [X]
- 상수선언에서 CONSTANT는 자료형보다 먼저 기술
- 변수명은 대소문자를 구분하지 않는다.
● 변수에 값 할당
- 지정 연산자(:=)를 이용해서 값 할당.
- 문법) 변수명 := 값;
○ 바인드(호스트) 변수
- 호스트 환경(CMD창=PROMPT환경)에서 선언된 변수
- PL/SQL 프로그램의 내부나 외부에서 변수를 생성하여 전달하기 위해서 사용
- PL/SQL문이 프로시저나 함수 안에 있지 않다면 호스트에서 선언된 변수(바인드 변수)를 PL/SQL문장에서 참조 할 수 있다.
- 호스트 변수와 PL/SQL내에서의 변수를 구분하기 위해 호스트 변수앞에 콜론(:)을 붙인다.
- 선언만 할 수 있고 호스트 변수는 선언시에 값을 할 당 못한다.
- 문법) VAR[VARIABLE] 변수명 자료형;
ex1)
VARIABLE return_var NUMBER;
BEGIN
:return_var:=300;
END;
/
SQL>PRINT return_var 또는 SELECT :RETURN_VAR FROM DUAL; 실행 시 >> 300 출력됨.
ex2)
DECLARE
v_name VARCHAR2(10);
BEGIN
v_name:='Hello World';
END;
/
SQL>PRINT v_name >> Bind variable "V_NAME" not declared. 바인드변수가 아닌 것은 PROMPT에서 변수로 판단하지 않는다.
○ PL/SQL에서의 치환 변수 & (연산자)
- 단일 앰퍼센트(&) 및 이중 앰퍼센트(&&) 치환을 사용하여 값을 임시로 저장.
- & 단일 앰퍼센트 : 호출될 때마다 매번 대화상자를 통해 값을 입력받는다.
- && 이중 앰퍼센트 : 최초에만 입력받고, 이후에는 최초의 값을 계속 사용한다.
- 문법) &변수 / &&변수
- 매번 수행할때마다 사용자가 값을 입력(지정)하는 변수.
- &&변수를 한 번 값을 지정하면 그 뒤로 계속 사용하던데 UNDEFINE으로도 풀리지 않는다. 어떻게 풀까?
ex)
만약, ACCEPT 변수X PROMPT로 변수X를 입력받았다면)
PL/SQL문에서의 &X는 ACCEPT로 입력받은 값을 저장한 변수X를 참조하고 있음을 의미한다.
따라서 &&X는 &X의 값을 그대로 사용하고, &X=ACCEPT의 X이기 때문에 새롭게 호출되어도
값을 새로 입력받지 않는다.
만약, ACCEPT로 입력받은 값이 없다면)
PL/SQL문에서 &Y는 호출될 때마다 새로운 값을 입력받고, &&Y는 최초의 &Y값을 그대로 사
용하고, &&S는 최초로 호출될 때는 값을 입력받고 이후에는 최초의 값을 그대로 사용한다.
(일반 SQL문에서의 치환변수)
- &&변수명의 고정된 치환변수값 초기화 방법)
>> UNDEFINE 변수명;
- SELECT문, WHERE절, ORDER BY절, COLUMN표현식, TABLE명 등에서 사용할 수 있다.
○ 연산자
- 산술 연산자 : +,-,/,*
- 비교 연산자: =,!=,<,>,<=,>=,IS NULL,BETWEEN,IN
- 논리 연산자:AND ,OR, NOT등
○ PL/SQL문에서 SELECT문장 사용시 주의 사항
- 문법1) - CURSOR 미사용 >> 단일행 조회
SELECT 컬럼명1[,컬럼명2,...] INTO 변수1[,변수2,...] FROM 테이블 WHERE ~ ;
- 문법2) - CURSOR 사용 >> 다중행 조회
- SELECT 문은 하나의 행만 RETURN해야 한다. 하나이상 이거나 없을시 PL/SQL은 TOO_MANY_ROWS와 NO_DATA_FOUND 예외를 발생 시킨다. 여러행을 리턴시에는 CURSOR를 사용해야 한다.
- SELECT문장을 PL/SQL문에서 사용시에는 INTO절을 ‘반드시’ 사용해야 한다.
- INTO절에서는 테이블의 열과 INTO절에서의 출력 변수의 수가 1대1 대응해야 한다.
○ 제어문
● IF문
- 문법)
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE statements;]
END IF;
ex)
SET SERVEROUT ON
DECLARE
v_a NUMBER;
BEGIN
v_a:=10;
IF MOD(v_a,2) =0 THEN
DBMS_OUTPUT.PUT_LINE('짝수');
--DBMS_OUTPUT.PUT_LINE = 자바의 SYSOUT과 같은 함수.
ELSE
DBMS_OUTPUT.PUT_LINE('홀수');
END IF;
END;
/
○ 반복문
- PL/SQL에서는 무한루프를 만들 때 반복조건에 무조건 TRUE인 산수식을 쓴다.
ex) 1=1, 0=0 1+1=2
● LOOP문
- 자바의 DO~WHILE문과 유사. 명령문을 최초의 1번은 무조건 실행한다.
- 문법)
LOOP
statement1;
statement2; ...
EXIT [WHEN condition];
END LOOP;
● FOR문
-FOR문은 "1씩밖에" 증가 못한다.
- 문법)
FOR 인덱스 카운터 IN [REVERSE] 초기값 .. 종료값 LOOP
statement1;
statement2;
END LOOP;
- 인덱스 카운터는 자동으로 선언된 변수, DECLARE에서 선언하지 않고 FOR문에서'만' 사용할 수 있는 변수다.
- 항상 초기값이 종료값보다 작아야한다.초기값>종료값이면 애초에 FOR문의 내부로 진입하지 않는다.
- REVERSE를 쓰면 종료값 >> 초기값으로 거꾸로 ‘-1’씩 감소한다. 이때도, 초기값<종료값이어야 한다.
ex)
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('I의 값은 ' || I);
END LOOP;
END;
/
>> I의 값은 1 ... I의 값은 10 까지 출력됨.
● WHILE문
- 문법)
WHILE 반복조건 LOOP
statement1;
statement2;
END LOOP;
- FOR문을 WHILE문으로 변형시키는 방법은 JAVA와 동일.
1) 초기식을 WHILE문 밖에 작성
2) WHILE문의 마지막 statement로 증감식 작성.
>> i := 1; --초기식
WHILE 반복조건 LOOP
statement;
i := i + 1; --증감식
END LOOP;
● EXIT문
- 반복문의 statement에 사용하면 된다.
- 문법) EXIT [WHEN condition];
- condition이 TRUE일 때, 반복문을 탈출한다.
○ 예외처리부 EXCEPTION
- EXCEPTION: PL/SQL에서 발생하는 ERROR
- ORACLE Server 에러가 발생하면 이와 관련된 EXCEPTION이 자동 발생
- OTHERS = JAVA의 Exception클래스,Throwable인터페이스와 같이 최상위 계층의 예외.
- 선언부(DECLARE)에서 발생한 EXCEPTION은 잡을 수 없다!!!
ex) SQLFOUND.sql 참고
ACCEPT id PROMPT 'Input username?'
ACCEPT title PROMPT 'Input title?'
DECLARE
--선언부 에러는 CATCH가 안된다
--방법1 : 선언부에서 초기화시. 즉 테이블의 자료형 크기보다 크게 선언
id VARCHAR2(1000):= '&id';
title NVARCHAR2(1000) := '&title';--자료형의 길이보다 많은 데이타로 설정
/*
--방법2:초기화를 실행부에서
id bbs.id%TYPE;
title bbs.title%TYPE;
*/
BEGIN
--방법1
IF LENGTH(title) > 10 OR LENGTH(id) > 10 THEN
DBMS_OUTPUT.PUT_LINE('TITLE OR ID IS MORE THAN 10');
ELSE
INSERT INTO bbs VALUES(SEQ_BBS.NEXTVAL,title,id,SYSDATE);
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'ROW AFFECTED');
COMMIT;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('PK IS DUPLICATE');
--방법2:초기화를 실행부에서 한다
/*
id := '&id';
title := '&title';
INSERT INTO bbs VALUES(SEQ_BBS.NEXTVAL,title,id,SYSDATE);
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'ROW AFFECTED');
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('TITLE OR ID IS MORE THAN 10');
*/
END;
/
- 문법)
DECLARE
-- 선언부에서 발생한 ERROR는 CATCH 못 함.
BEGIN -- 실행부 자체가 JAVA로 치면 try절
. . .
EXCEPTION -- 예외처리부가 JAVA로 치면 CATCH절.
WHEN exception명1 THEN -- CATCH ( exception명 ) { } 과 동일.
statement1
WHEN exception명2 THEN -- CATCH ( exception명 ) { }
. statement2
WHEN OTHERS THEN -- CATCH (THROWABLE e) { }, OTHERS = 모든 예외 다 잡는다.
. statement3
END;
/
● 에러코드/메세지 함수
- 일반적인 Error 처리를 위하여 Oracle에서 제공하는 함수
- SQLCODE : ORACLE에서 지정된 Error Code를 숫자로 Return (0은 success를 의미)
- SQLERRM : ORACLE에서 지정된 Error Code에 해당하는 Error Message를 Return (JAVA의 getMessage()메소드와 같은 역할)
● 예외 종류
- PREDEFINED / NON-PREDEFINED / USER DEFINE 가 있다.
1. PREDEFINED EXCEPTION
- ORACLE Server 에러 중 자주 발생되는 20가지 에러에 대해 미리 정의되어 있는 EXCEPTION
EXCEPTION 명
ERRORCODE
설 명
NO_DATA_FOUND
ORA-01403
레코드(데이터)를 반환하지 않은 SELECT문
TOO_MANY_ROWS
ORA-01422
(PL/SQL에서는 단일행을 반환해야 하는데)두 개 이상을 반환한 SELECT문
INVALID_CURSOR
ORA-01001
잘못된 CURSOR 연산 발생
ZERO_DIVIDE
ORA-01476
0으로 나누기(0으로 나눌 수 없다. JAVA의 ARITHMETHICS EXCEPTION)
DUP_VAL_ON_INDEX
ORA-00001
UNIQUE COLUMN에 중복된 값을 입력할 때
CURSOR_ALREADY_OPEN
ORA-06511
이미 열러 있는 커서를 여는 경우
INVALID_NUMBER
ORA-01722
문자열을 숫자로 전환하지 못한 경우
PROGRAM_ERROR
ORA-06501
PL/SQL 내부의 오류
STORAGE_ERROR
ORA-06500
PL/SQL에 메모리 부족
VALUE_ERROR
ORA-06502
산술, 절단 등에서 크기가 다른 오류 발생
...
...
...
2. NON-PREDEFINED EXCEPTION
- EXCEPTION명은 사용자가 지정하지만, 실제 ERROR는 PREDEFINED EXCEPTION을 빌려서 사용한다.
- 사용자가 '선언부'에 EXCEPTION명을 정의하고 ORACLE Server에서 제공하는 error번호를 사용하여 Error와 연결한 후 예외처리부에서 Error처리
- 문법)
DECLARE
exception명 EXCEPTION;
PRAGMA EXCEPTION_INIT(exception명, 에러번호); --예외를 PREDEFINED EXCEPTION
을 사용하여 초기화 하는 것.
BEGIN
. . .
EXCEPTION
WHEN exception명 THEN
~~~~~;
. . .
END;
/
3. USER DEFINE EXCEPTION
- JAVA의 사용자 정의 EXCEPTION과 같음. 99% 사용자 정의.
- 문법)
DECLARE
exception명 EXCEPTION;
BEGIN
[주로 조건절과 함께 사용]
RAISE exception명 ; -- RAISE = JAVA로 치면 THROW와 같음.
. . .
EXCEPTION
WHEN exception명 THEN
. . .
END;
/
※ Oracle 주요 Built-In FUNCTION
- 오라클 함수의 특징 : '무조건' 반환값이 있어야 한다.
☆ 문자열 관련 함수
- JAVA로 치면 String클래스의 주요 메소드
○ NVL(컬럼명,NULL인 경우 대체할 값)
- 해당 컬럼의 DATA가 NULL이면 대체값으로 대체한다.
ex) SELECT ename,sal,NVL(comm,-1) FROM emp; >> comm컬럼이 NULL인 RECORD는 comm이 -1로 대체되고, NULL이 아닌 RECORD는 그대로 조회된다.
○ NVL2(컬럼명,대체값1,NULL인 경우 대체값2)
- 컬럼이 NOT NULL이면 대체값1로, NULL이면 대체값2로 DATA를 변경한다.
ex) SELECT ename,sal,NVL2(mgr,'직원','대표') mgr FROM emp;
>> mgr이 NULL이 아니면 ‘직원’, NULL이면 ‘대표’로 대체되어 조회한다.
○ LOWER('문자열')
- 영문자를 소문자로 변환
ex) SELECT LOWER('ORACLE') FROM DUAL >> oracle
○ UPPER('문자열')
- 영문자를 대문자로 변환
ex) SELECT UPPER('hello') FROM DUAL; >> 'HELLO'
○ INITCAP('문자열')
- 첫 영문자만 대문자로 변환, 나머지는 소문자로 변환.
ex1) SELECT job,INITCAP(job) FROM EMP; >> MANAGER => Manager
ex2) SELECT UPPER('123hello') FROM DUAL; >> '123hello' 문자열의 시작이 영문자가 아니면 의미 없음.
○ CONCAT('문자열','문자열')
- 문자열 연결 또는 ||
- CONCAT의 인자는 2개여야 한다. >> 3개의 문자열 연결 시 CONCAT(CONCAT('1','2'),'3')
ex) SELECT CONCAT(123,456) FROM DUAL; >> '123456'
○ LENGTH('문자열')
- 문자열의 '문자'단위 길이
ex) SELECT LENGTH('문자열') FROM DUAL; >> 3
○ LENGTHB('문자열')
- 문자열의 바이트단위 길이(이제 한글은 3바이트)
ex) SELECT LENGTHB('문자열 계산') FROM DUAL; >> 16
○ LPAD('문자열',전체 자리수,'채울 문자열'):좌측을 지정한 값으로 채운다
- '전체 자리수' 중에서, '문자열'을 먼저 채우고 '좌측에 남은 공백'만큼 '채울 문자열'로 채운다.
ex1) SELECT LPAD('HELLO',10,'X') FROM DUAL; >> _ _ _ _ _HELLO (먼저 문자열이 채워짐) >> XXXXXHELLO (남은 공백에 'X'를 채움)
ex2) SELECT LPAD('HELLO',10,'XYZ') FROM DUAL; >> _ _ _ _ _HELLO (먼저 문자열이 채워짐) >> XYZXYHELLO (남은 공백에 'XYZ'를 순서대로 채우다가 공백이 끝나면 나머지는 잘림.)
○ RPAD('문자열',전체 자리수,'채울 문자열'):우측을 지정한 값으로 채운다
- '전체 자리수' 중에서, '문자열'을 채우고 '우측에 남은 공백'을 '채울 문자열'로 채운다.
ex) SELECT RPAD('HELLO',10,'XYZ') FROM DUAL; >> HELLO_ _ _ _ _ (먼저 문자열이 채워짐) >> HELLOXYZXY (남은 공백에 'XYZ'를 채움)
+) LPAD, RPAD 활용 : 민감한 정보(주민번호,전화번호...) 가리기.
공식) L/RPAD(SUBSTR('문자열',index,count),LENGTH('문자열'),'기호');
ㆍindex = 문자열에서 드러내고 싶은 문자의 시작 index
ㆍcount = 드러낼 문자의 개수.
ex) emp테이블에서 ename을 조회할 때, 첫 문자만 드러내고 나머지는 *로 가려라.
SELECT RPAD(SUBSTR(ename,1,1),LENGTH(ename),'*') FROM emp;
○ INSTR('문자열','찾을 문자열')
- 찾은 문자열의 인덱스 반환.인덱스는 1부터 시작!!!
- JAVA의 indexOf()와 유사.
- 찾을 문자열이 없으면 0 반환.
- like 연산자와 결과가 같으나 쿼리속도면에서 INSTR함수가 훨씬 성능이 우수하다.
+) INSTR 활용 : LIKE연산자와 유사하게 사용할 수 있다. 단, 주의해야 한다.
공식) SELECT ~ WHERE INSTR(컬럼명,'특정 문자열')!=0;
ㆍ컬럼명 LIKE '%특정문자열%' 과 같은 기능을 한다.
ㆍ단, 자바의 lastIndexOf()처럼 뒤에서부터 찾는 기능은 없기 때문에 이로 인한
문제는 해결할 수 없다. ex) 특정 문자로 끝나는 문자열 찾기, 파일의 확장자찾기
>> SUBSTR()을 활용해야 한다.
ex1) SELECT INSTR('ABCDEFG','DE') FROM DUAL; >> 4
- 그러나! lastIndexOf()메소드처럼 뒤에서부터 인덱스를 반환하는 기능은 없기 때문에, 특정 문자로 끝나는 경우를 INSTR()함수로 찾으면 오류가 발생하기 쉽다.
ex2) 이름이 't'로 끝나는 사람을 SELECT할 때, WHERE절에 LOWER(INSTR(last_name,'t'))=LENGTH(last_name)을 쓰면 Everett처럼 t가 이름에 2번 이상 쓰이는 사람은 이름이 t로 끝남에도 불구하고 검색되지 않는다... => SUBSTR()함수로 찾아야 한다.
○ SUBSTR('문자열',시작인덱스,개수)
- 문자열에서 시작인덱스부터 개수만큼 추출한다. 인덱스는 1부터 시작
ex) SELECT SUBSTR('123456789',3,3) FROM DUAL; >> 345
+) SUBSTR 활용 : 특정 문자로 끝나는 문자열을 찾을 때 활용한다.
공식) SELECT ~ FROM ~
WHERE SUBSTR(문자열,LENGTH(문자열)-LENGTH(특정문자)+1,LENGTH(특정문자))='특정문자';
ㆍ문자열의 뒤에서 LENGTH(특정문자)만큼의 문자열을 추출하여 비교.
ex) '.txt'로 끝나는 파일을 찾아라 >> SELECT ~~ WHERE SUBSTR(파일이름,LENGTH(파일이름)-LENGTH('.txt')+1,LENGTH('.txt')='.txt');
○ REPLACE('문자열','바꿀 문자열','대체할 문자열')
- 특정 문자열을 다른 문자열로 대체
ex) SELECT REPLACE('HELLO WORLD','HELLO','JAVA') FROM DUAL >> JAVA WORLD
○ TO_CHAR()
- JAVA의 String.toString()과 유사. 문자열로 변환하는 함수.
● TO_CHAR(숫자 혹은 날짜)
● TO_CHAR(숫자 혹은 날짜,숫자형식 포맷 문자열 혹은 날짜형식 포맷 문자열)
- 숫자포맷문자열
ㆍ9는 값이 있으면 표시 ,없으면 공백으로 표시.
ex1) TO_CHAR(12,'9999') >> '_ _ 12'
ex2) TO_CHAR(12,'$9999') >> '_ _ $12' (공백이 제일 앞으로 이동함.)
ㆍ0는 값이 있으면 표시, 없으면 0으로 표시
ex) TO_CHAR(12,'0000') >> '0012'
ㆍ소수점은 9든 0이든 값이 없으면 모두 0으로 표시됨
ex1) TO_CHAR(12,'0000.99') >> '0012.00'
ex2) TO_CHAR(12,'0000.00') >> '0012.00'
또한 소수점은 실제값의 자리수가 많으면 반올림한다.
ex1) TO_CHAR(12.12345,'00.00') >> '0012.12'
ex2) TO_CHAR(12.12645,'00.00') >> '0012.13'
ㆍ단, 정수인 경우는 실제값의 자리수가 많으면 값이 #으로 표시됨
ex) TO_CHAR(123456,'999') >> '######'
ㆍ콤마(,)는 그대로 출력.
ex1) TO_CHAR(123456,'0,000,999') >> '0,123,456'
ex2) TO_CHAR(123456,'0000,999') >> '0123,456'
ㆍ숫자포맷문자열에 일반문자는 ($/L/l)만 포함될 수 있고, 통화단위를 의미한다.
( $는 포맷문자열 어디에 위치하더라도, 반환 문자열에서 제일 앞에 위치한다.
L(l) 시스템 지역 통화단위는 포맷문자열 맨 앞에 위치해야 한다. )
ex1) TO_CHAR(123456,'0,00$0,999') >> '$0,123,456'
ex2) TO_CHAR(123456,'0000,999L') >> [X]
ex3) TO_CHAR(123456,'L0,000,999') >> '￦0,123,456'
- 날짜포맷문자열
ㆍyy:년도 2자리만
ㆍyyy:년도 3자리만 ( 2022 >> 022 )
ㆍyyyy:년도 4자리
ㆍmm:01~12형태의 월 (1~12형태는 없음.)
ㆍd: 요일을 숫자로 반환(일요일은 1,월요일 2, ... 목 5, 금 6,토 7)
ㆍday: 요일 전체를 반환 (월요일,화요일,...)
ㆍdy: 요일을 줄여서 반환 (월,화,수,목,...)
ㆍdd:1~31형태의 일 표시
ㆍddd:해당 년도의 1월 1일부터 현재까지의 일수 3자리로 반환 (04/03 >> '093')
ㆍHH:01~12시 형태로 표시
ㆍHH12:01~12시 형태로 표시
ㆍHH24:00~23시
ㆍMI:00~59분
ㆍSS:00~59초
ㆍAM/PM : 오전 또는 오후 표시. 아무거나 쓰면 알아서 처리해줌.
○ TO_DATE(날짜포맷문자열)
- 문자형을 날짜형으로 변환
- 한글은 인식하지 못한다. 대부분의 특수기호는 인식한다.
ex1) SELECT TO_DATE('2022-10-20') + 100 FROM DUAL; >> 23/01/28
ex2) SELECT TO_DATE('2022@10@20') + 100 FROM DUAL; >> 23/01/28 (@,#,/,-,...)등 기호는 거의 다 됨.
ex3) SELECT TO_DATE('2022년10월20일') + 100 FROM DUAL; >> [X] 한글 들어가면 무조건 인식 못 함.
ex4) SELECT TO_DATE('2022"년"10"월"20"일"') + 100 FROM DUAL; >> [X] 한글 들어가면 무조건 인식 못 함.
+) TO_CHAR()와 TO_DATE() 활용 : 날짜의 특정부분을 문자열로 반환
공식) TO_CHAR(TO_DATE(날짜형식문자열),'날짜포멧문자열')
ex) TO_CHAR(TO_DATE('2012/10/1'),'YYYY') >> '2012'
○ TO_NUMBER():문자형을 숫자형으로 변환
- 숫자형식의 문자열은 산술연산자를 만나면 자동으로 숫자형으로 변환된다.
ex1) SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL >> 579
ex2) SELECT '123' + '456' FROM DUAL >> 579
+) PL/SQL문의 SELECT ~ INTO ~ 에서 TO_NUMBER()를 사용하면 '10A'처럼 숫자로 시작하고 문자로 끝나는 경우에는 알아서 숫자만 추출해준다. 'A10','1A0'처럼 처음이나 중간에 문자가 있으면 INVALID_NUMBER 에러 발생.
○ TRIM(): 양쪽 공백 제거
ex) TRIM(TO_CHAR(12,'99,999')) >> '12'
☆ 날짜 관련 함수
- JAVA로 치면 Date클래스의 주요 메소드
○ SYSDATE
- 현재 날짜 가져오기
- SYSDATE함수는 괄호를 붙이지 않음.
ex) SELECT SYSDATE FROM DUAL;
○ + / - 연산자
- 날짜 더하기 빼기 가능하다.
ex) SELECT SYSDATE + 1 FROM DUAL >> 1일 더한 날 출력
○ MONTHS_BETWEEN(DATE1,DATE2)
- (인자1 - 인자2)의 개월 수 차이 반환.
- 정수값이 개월 수 차이. 소수값은 중요치 않음...
ex) SELECT MONTHS_BETWEEN(TO_DATE('2023-02-23'),SYSDATE) FROM DUAL; >> 4.07943884 4개월차이.
○ ADD_MONTHS(DATE타입,숫자):개월 수 더하기
ex) SELECT ADD_MONTHS(TO_DATE('22/10/20'),2) FROM DUAL >> 22/12/20
☆ 수학 관련 함수
- JAVA로 치면 Math클래스의 주요 메소드
○ ROUND(n)
- 소수점 첫째자리에서 반올림
● ROUND(n,m)
- 숫자 n을 소수점 m+1째자리에서 반올림
ex1) SELECT ROUND(3.145) FROM DUAL; >> 3
ex2) SELECT ROUND(3.145,2) FROM DUAL; >> 3.15
○ FLOOR(n)
- 소수자리 버림
ex1) SELECT FLOOR(3.145,2) FROM DUAL; >> [X]
ex2) SELECT FLOOR(3.145) FROM DUAL; >> 3
○ CEIL(n)
- 올림
ex1) SELECT CEIL(3.145,2) FROM DUAL; >> [X]
ex2) SELECT CEIL(3.145) FROM DUAL; >> 4
○ MOD(X,Y)
- X % Y = MOD(X,Y)
ex) SELECT MOD(10,3) FROM DUAL; >> 1
○ POWER(X,Y)
- JAVA의 MATH.POW() 메소드.
- X^Y = POWER(X,Y)
ex)
○ SQRT(X)
- 제곱근. 제곱해서 X가 나오는 수.
- Log2^X = SQRT(X)
ex) SELECT SQRT(4) FROM DUAL; >> 2
○ DECODE()
- 이산적인 값에 따라 결과를 표시하는 함수
- JAVA의 SWITCH문과 유사.
- DECODE()는 '값1 = 결과값1', 즉 '=' 연산만 가능(이산적인 값만 DECODE가능.)
- 문법) DECODE(표현식,값1,결과값1,값2,결과값2,..값N,결과값N,기본값)
- 표현식이 값1일때 결과값1이 반환됨, 값2일때는 결과값2,표현식이 아무값도 해당되지 않을때 기본값 반환
ex1) SELECT DECODE( 10/2 ,1,'값이1',2,'값이2',3,'값이3','그외값') FROM DUAL; >> '그외값' 반환
ex2) SELECT DECODE(job,'MANAGER','관리자','SALESMAN','판매자','기타') FROM DUAL;
○ CASE WHEN 절 :
- 문법1) 다양한 조건을 줄때 = IF~ELSIF~ELSE문처럼 쓸 때.
CASE WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
..................
WHEN 조건식N THEN 결과값N
ELSE 그외값
END [컬럼별칭]
-- 조건식은 논리식이어야 한다.
- 문법2) DECODE()함수(SWITCH문)처럼 쓰려면
CASE 표현식 WHEN 값1 THEN 결과값1
WHEN 값2 THEN 결과값2
..................
WHEN 값N THEN 결과값N
ELSE 그외값
END [컬럼별칭]
-- 표현식은 이산적인 값을 가져야 한다.
ex1) CASE WHEN을 IF문처럼 쓰는 경우
SELECT ename,
CASE WHEN sal>=10000 THEN '고액'
WHEN sal>=5000 THEN '중상위'
ELSE '평범'
END "별칭은 연봉등급"
FROM emp;
ex2) CASE WHEN을 DECODE()함수처럼 쓰는 경우
SELECT ename,
CASE job WHEN 'MANAGER' THEN '관리자'
WHEN 'SALESMAN' THEN '판매자'
ELSE '기타'
END "별칭은 직무"
FROM emp;
※ CURSOR 객체
# 결과집합(결과셋, RESULT SET) = SELECT문을 실행했을 때, 메모리에 만들어지는 조회 결과 TABLE.
- CURSOR = 결과집합에서의 어떤 RECORD를 가리키는 POINTER.
- CURSOR객체가 OPEN되면 기본적으로 결과셋의 첫 번째 RECORD의 바로 위를 POINT하고 있다.
# FETCH = 커서를 하나씩 내려가며(RECORD를 하나씩 옮겨가며) 각 컬럼의 값(DATA)을 가져오는(인출하는) 행위.
- 꺼내올 RECORD가 없는데 FETCH를 하면 에러 발생 >> 항상 반복문을 함께 사용해서 반복조건으로 POINT할 RECORD가 있는지 판단한다.
- PL/SQL문에서 다중행 결과가 나오는 SELECT문을 쓸 수 있게 해준다.(SEELCT 문장에 의해 여러행이 RETURN되는 경우 각 행에 접근하기 위한 것)
(그림설명)
(그림설명_CURSOR개체)
○ CURSOR 선언
- DECLARE부에서 선언한다
- 문법)
CURSOR 커서명 IS SELECT문 ; --(여기서의 SELECT문은 INTO절이 없는 SELECT문)
○ OPEN CURSOR
- 문법) OPEN 커서명;
- 실행부에서 커서를 실행하기 위한 문장.(질의(SELECT문)를 수행하라는 의미)
- 커서를 OPEN하는 과정)
OPEN 커서명 >> 해당 커서의 SELECT문 실행 >> 결과셋 반환 >> CURSOR를 그 결과셋의 첫번째 RECORD위로 POINT.
○ FETCH ~ INTO ~
- 문법) FETCH 커서명 INTO varaiable1[,variable2,....];
- CURSOR의 POINT를 다음 RECORD로 이동시키고 해당 행을 읽어 들이는 작업
- FETCH는 인출할 행이 없어도 에러를 발생시키지 않는다. 그냥 끝난다. >> 그래서 커서명%FOUND를 반복조건으로 쓸 수 있음.
○ CURSOR 닫기
- 문법) CLOSE 커서명;
- 결과셋의 자원을 반납.SELECT 문장이 다 처리된후 CURSOR를 CLOSE
○ CURSOR의 속성
● 커서명%FOUND
- 가장 최근의 인출(FETCH)이 행을 RETURN하면 TRUE아니면 FALSE;
- 커서명%FOUND가 TRUE일 동안, FETCH하면 된다.
● 커서명%ROWCOUNT
- 지금까지 RETURN된 행의 수 (= 결과셋의 RECORD 수)를 알 수 있다.
ex1) 커서 형식. LOOP문
- LOOP문 전에 FETCH 가능!!!
- EXIT WHEN 커서명%FOUND로 FETCH끝나면 반복문 종료.
DECLARE
CURSOR my_cursor IS
SELECT job FROM emp;
var_job emp.job%TYPE;
BEGIN
OPEN job_cursor;
[LOOP 전에도 FETCH 가능]
LOOP
FETCH job_cursor INTO var_job;
IF SUBSTR(var_job,LENGTH(var_job)-3+1,3)='MAN' THEN
DBMS_OUTPUT.PUT_LINE(REPLACE(var_job,'MAN','WOMAN'));
ELSE
DBMS_OUTPUT.PUT_LINE(var_job);
END IF;
EXIT WHEN NOT job_cursor%FOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('RECORD COUNT OF FETCHED : '||job_cursor%ROWCOUNT);
CLOSE job_cursor;
END;
/
ex2) 커서 형식. WHILE문
- WHILE문 전에 1번 무조건 FETCH 해줘야 함!!!
- WHILE문 마지막에 FETCH 해야 함!!!
- 반복조건으로 커서명%FOUND로 FETCH끝나면 반복문 종료.
DECLARE
CURSOR my_cursor IS
SELECT job FROM emp;
var_job emp.job%TYPE;
BEGIN
OPEN job_cursor;
FETCH job_cursor INTO var_job;
WHILE job_cursor%FOUND LOOP
IF SUBSTR(var_job,LENGTH(var_job)-3+1,3)='MAN' THEN
DBMS_OUTPUT.PUT_LINE(REPLACE(var_job,'MAN','WOMAN'));
ELSE
DBMS_OUTPUT.PUT_LINE(var_job);
END IF;
FETCH job_cursor INTO var_job;
END LOOP;
DBMS_OUTPUT.PUT_LINE('RECORD COUNT OF FETCHED : '||job_cursor%ROWCOUNT);
CLOSE job_cursor;
END;
/
※ SUBPROGRAM
- PL/SQL에는 '(STORED)프로시저' 와 '함수'라는 두가지 유형의 SUBPROGRAM이 있다
- SELECT구문 뿐만 아니라 기타 DML문등을 이용하거나 혹은 프로그래밍적인 요소등을 사용하여 처리하기 복잡한 여러가지 데이터베이스 작업등을 처리 할 수 있도록 만들어진 데이터베이스 객체이다.
- 프로시저의 장점 : 보안이 우수하다. 외부로 노출이 안 된다.
☆ Function
- 사용자가 PL/SQL구문을 사용하여 직접 오라클 에서 제공하는 내장함수와 같은, '기능을 정의 한 것'
- 함수는 'IN 파라미터만 사용'할 수 있으며, 반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 하며 단일값만 반환 된다.
- 문법)
CREATE [OR REPLACE] FUNCTION 함수명 --함수를 수정하려면 OR REPLACE까지 쓰면 된다.
[(
매개변수1 [IN] 자료형, --IN 생략 가능
매개변수2 [IN] 자료형 ---자료형 정의시 자리수 지정 안함 !!!
)]
RETURN 자료형 -- 함수의 반환 타입. 마찬가지로 자리수 지정 안 함. 세미콜론 쓰면 안 됨.
IS
[변수 선언] -- 함수의 선언부=함수의 실행부에서 사용할 변수를 선언. 해당 변수는 함수의
'지역 변수'이다. 이때는 자료형의 자리수(size) 지정 가능.
BEGIN -- 함수의 실행부. 실행할 명령문들을 작성.
함수 내용
RETURN 반환값; -- 반환값 반환하기.
END;
/
- 호출) 함수 호출 전에 한 번 실행해서 Function created.됐는지 확인.(한 번 실행해서 컴파일해야 함.)
--함수 호출 방법1)
SQL>SELECT GETSUM(1,100) FROM DUAL
--함수 호출 방법2) EXECUTE 명령어, 바인드변수 사용.
SQL>VAR HAP CHAR(2); --바인드변수
SQL>EXECUTE :HAP := GETSUM(1,100); --SQL명령어 EXECUTE사용
SQL>PRINT HAP; --ORACLE명령어
○ 다른 계정에 함수 실행권한 주기
- 문법) GRANT EXECUTE ON 소유계정.함수명 TO 부여받는계정;
- 다른 계정의 함수를 호출할 때는 '테이블명.함수명()'으로 접근.
예]grant execute on scott.asterisk to hr;
SELECT scott.asterist(email) FROM employees;
○ FUNCTION의 예외처리
- 문법) 함수 실행부의 마지막에 예외처리부 추가.
BEGIN
statement1;
RETURN 반환값1;
EXCEPTION
WHEN ~ THEN
statement2;
RETURN 반환값2;
END;
/
- PL/SQL FUNCTION에서 예최처리 시, 예외처리부는 RETURN문 이후에 기술해야 한다. 또한 "
예외처리부에도 RETURN값이 존재해야 한다." statement1에서 예외가 발생하여 EXCEPTION
으로 들어오면 반환값1은 RETURN되지 않기 때문에 예외처리부에서도 반환을 해줘야 한다.
☆ STORED PROCEDURE
- 프로시저는 RETURN문이 없다 'OUT 매개변수'로 값을 RETURN할 수 있다.
-- OUT 파라미터를 선언해도 프로시저 호출 시 인자를 전달해야 한다!!! 이 때, 인자는 OUT 파라미터 값을 전달받을 'ASSIGNMENT TARGET'이 되어야 한다. 즉, 변수 혹은 바인드변수여야 한다. 프로시저 내에서 OUT 매개변수에 값이 할당되면, 그 값은 OUT매개변수를 통해서 최종적으로 ASSIGNMENT TARTGET(프로시져 호출할 때 OUT매개변수로 전달한 인자)에 전달된다.
- 파라미터의 종류를 반드시 명시해야 한다.
- 저장 프로시져(STORED PROCEDURE)의 장점)
1) 성능이 좋다. PARSING으로 인한 속도 저하가 적다.
(설명) ↓↓↓
기본적으로 USER가 DB에 데이터를 요청할 때, SQL문을 사용한다.
ex. SELECT ename,job,sal FROM emp WHERE ename='SMITH';
USER가 쿼리를 요청(SQL문 전달) >> DB가 쿼리를 PARSING(쿼리가 문법적으로 맞는지 분석) >> DB가 USER에게 응답(성공+쿼리에 대한 결과/실패+에러)
만약 USER 1,2,3이 같은 쿼리를 DB에 요청한다면 DB는 같은 내용의 쿼리여도 각각 PARSING한다. 그러나 해당 쿼리의 내용을 DB의 STORED PROCEDURE로 만들어두면, STORED PROCEDURE가 컴파일될 때 1번만 PARSING이 이루어지고, 그 후에는 PARSING을 하지 않는다. USER는 쿼리를 작성하지 않아도, PROCEDURE를 '호출'하기만 하면 쿼리에 대한 결과를 더 빠른 속도로 응답받을 수 있다.
2) 보안성을 높일 수 있음.
ex1. SELECT ename,job,sal FROM emp WHERE ename='SMITH';
ex2. SELECT my_procedure('SMITH' ,OUT_VAR);
1번처럼 쿼리를 작성하면, 해당 테이블명/컬럼명 등이 노출된다. 그러나 2번처럼 PROCEDURE
를 사용하면, 테이블명/컬럼명 등 매개변수의 인자를 제외하곤 노출되는 부분이 줄어든다.
3) 다양한 처리(복잡한 업무처리)가 가능
- PROCEDURE 내부에서 다양한 작업(DML문, 내장함수,...)을 수행할 수 있다. 여러 쿼리를 하나의 PROCEDURE로 묶어 효율적으로 처리 가능.
4) 네트웍의 부하를 줄일 수 있음. (장점1과 같은 맥락)
- 문법)
- RETURN 문이 없다.
CREATE [OR REPLACE] PROCEDURE 프로시저명
[(
매개변수 IN 자료형, //크기 지정 안함
매개변수 OUT 자료형,//출력용, 변수/바인드변수 전달
매개변수 IN OUT 자료형//입 출력용 EXEC시 "숫자 전달 불가능" ,역시 변수 전달
)]
IS
[변수선언]
BEGIN
........
END;
/
- PROCEDURE 내에서 다른 PROCEDURE를 사용할 때) EXECUTE 미사용
BEGIN
프로시저명(~,~,~...);
END;
/
- 실행)
EXEC[UTE] 프로시저명[(인수1,인수2,..)]
ex) VAR rt VARCHAR2(20) --OUT파라미터에 전달할 바인드 변수 선언
EXEC SP_INS_PROC('SONG',1234,'SKH',:rt);
--PROCEDURE내부에서 연산된 결과가 OUT매개변수를 통해 :rt에 저장됨(값 할당).
PRINT rt
- 활용) 프로시저의 OUT파라미터로 전달된 값을 가지고 경우의 수를 판단.
ex) OUT 파라미터값이 1인 경우 ‘회원’,0이면 아이디 불일치, -1이면 비번 불일치 판단.
VAR rt_num NUMBER(10)
EXECUTE SP_ISMEMBER('ID','PW',:rt_num);
DBMS_OUTPUT.PUT_LINE(DECODE(:rt_num,1,'회원',0,'~','~'));
+) FUNCTION vs PROCEDURE
FUNCTION
STORED PROCEDURE
식의 일부로서 사용
PL/SQL문으로서 실행
RETURN문이 필수
RETURN문이 없음
RETURN 값이 필수
OUT 파라미터로 값을 REURN할 수 있음
※ TRIGGER 개체
ex) 상품, 입고, 재고 테이블.
A상품을 입고해서 입고 테이블에 입고리스트를 추가(INSERT)하면 입고테이블에 있는 A상품의 수량만큼 재고 테이블에도 A상품의 수량이 존재해야 한다. 이 때, 입고 테이블에 A상품이 INSERT되면 재고 테이블의 A상품 RECORD가 자동으로 수정(UPDATE 재고테이블 SET A상품수량=A상품수량+:NEW) WHERE 상품명=A상품)되게 만들면 입고 시 자동으로 재고 테이블도 수정된다. 이렇게 어떤 테이블에 INSERT/UPDATE/DELETE가 발생했을 때, 다른 테이블도 같이 바꾸려면 트리거를 이용해야 한다.
- 자동으로 실행되는 프로시저의 한 종류. 직접(exec) 실행불가
- 하나의 테이블에 "최대 3개"까지 트리거 적용가능
단, 트리거 많을 수록 성능저하 초래 가능성 있다.
- 트리거 몸체(PL/SQL블락)안에는 COMMIT;ROLLBACK불가
트리거는 실행 시 자동 COMMIT이 기본 설정.(설정 가능. 나중에 배움)
# TARGET TABLE = 타겟테이블은 트리거를 적용시킬 테이블, 즉 타겟테이블에서 INSERT/ UPDATE/DELETE가 발생하면 트리거가 작동.
○ 트리거의 종류
● 트리거몸체 (DELARE ~ BEGIN END; 부분)의 '실행 횟수'에 따른 분류
- 문장 단위 TRIGGER : 타겟테이블에 I/U/D이벤트가 발생하여 영향을 받은 행의 수와 관계없이 트리거는 1번만 실행된다.
- 행 단위 TRIGGER : 타겟테이블에 I/U/D이벤트가 발생하여 영향을 받은 행의 수만큼 트리거가 실행된다. (대부분 행 단위 트리거) (FOR EACH ROW)
● 트리거 실행 '타이밍'에 따른 분류
- BEFORE TRIGGER : 타겟테이블에서 (I/U/D)이벤트가 발생하기 전에 트리거가 실행된다.
ex) 판매테이블에 판매기록을 INSERT하기 전에는 재고테이블의 수량을 먼저 확인해서 판매할 수량이 남아있는지 판단해야 한다. >> 판매테이블에 BEFORE 트리거를 걸어서 재고보다 많은 수량을 판매하지 못하게 막아야 한다.
- AFTER TRIGGER : 타겟테이블에서 (I/U/D)이벤트가 발생한 후에 트리거가 실행된다.
ex) 입고테이블에 상품 입고기록이 INSERT되면 그 후에 재고테이블의 상품 수량을 UPDATE해야 한다.
○ 임시테이블 : DML문은 실행되면 TEMPORARY TABLESPACE에 결과셋이 생기고, COMMIT될 경우 해당 임시테이블이 실제 PERMANENT TABLESPACE에 반영된다.
- 임시테이블은 행단위 트리거에서만 사용가능
● :OLD
- TARGET TABLE을 I/U/D하기 전의 임시테이블
● :NEW
- TARGET TABLE을 I/U/D한 후의 임시테이블
(타겟 테이블에
INSERT 실행시 = :OLD는 NULL, :NEW는 새로운 입력값
UPDATE 실행시 = :OLD는 업데이트 전의 값, :NEW는 새로운 변경 값
DELETE 실행시 = :OLD는 삭제 전, :NEW는 NULL값이다. )
○ 상수 키워드와 함수
● INSERTING - TARGET TABLE에서 해당 이벤트 발생 시 TRUE 반환.
● UPDATING - TARGET TABLE에서 해당 이벤트 발생 시 TRUE 반환.
● DELETING - TARGET TABLE에서 해당 이벤트 발생 시 TRUE 반환.
● RAISE_APPLICATION_ERROR(에러번호,'에러메세지');
- BEFOR TRIGGER의 몸체에 주로 사용. RAISE함수가 실행되면 EXCEPTION을 발생시킨다.
- 에러번호는 -20000~-20999 사이의 임의의 숫자 아무거나 사용.
- 에러메세지는 직접 입력 또는 SQLERRM 사용.
○ 문법) TRIGGER 생성
CREATE TRIGGER 트리거명
BEFORE|AFTER [INSERT [OR] UPDATE [OR] DELETE]
ON 트리거를 걸 테이블명(타겟테이블)
[FOR EACH ROW] --생략시 문장단위 트리거, 작성 시 행단위 트리거
[WHEN 트리거 조건]
DECALRE
변수 선언
BEGIN
트리거 실행 시 수행할 문장;
END;
/
○ 문법) TRIGGER 삭제
DROP TRIGGER 트리거명;
○ 문법) TRIGGER 수정
ALTER TRIGGER 트리거명 [ENABLE/DISABLE] ;
- 해당 트리거 활성화/비활성화
ALTER TABLE 테이블명 ENABLE/DISABLE ALL TRIGGERS ;
- 해당 테이블에 건 모든 트리거 활성화 비활성화
+) 백업과 복원
- 오라클 실행 파일을 이용하여 백업과 복원을 한다.
- 백업 및 복원 실행 파일 위치 : [오라클설치된 디렉토리]\\product\\11.2.0\\SERVER\\BIN
● 백업:EXP.EXE (오라클의 파일을 외부로 내보냄. EXPORT)
- 백업 = 데이타와 구조를 바이너리 파일로 저장
- dos>exp userid=아이디/비밀번호@전역데이터베이스명 file=저장경로
(전역데이터베이스명 = SID = xe)
ex1) scott계정의 모든 데이터 백업
dos>exp userid=scott/scott@xe file=d:\\skh\\scott.dmp
>> d:\\skh\\scott.dmp경로로 scott계정의 모든 데이터가 백업됨.
ex2) scott계정의 emp테이블만 백업
dos>exp userid=scott/scott@xe file=d:\\skh\\scott.dmp tables=emp
>> d:\\skh\\scott.dmp경로로 scott계정의 emp테이블만 백업됨.(여러 테이블 백업 시, 괄호로 묶고 콤마로 연결)
ex3) system 계정으로 scott 계정에 있는 DB백업
dos>exp userid=system/비밀번호@전역데이타베이스명 owner=scott file=c:\\dump.dmp
● 복원:IMP.EXE (외부의 파일을 오라클의 내부로 들여옴. IMPORT)
- dos>imp 아이디/비밀번호@전역데이타베이스명 file=백업경로
ex1) scott계정의 모든 데이터 복원
dos>imp scott/비밀번호@전역데이타베이스명 file=c:\\dump.dmp
ex2) system 계정으로 scott 계정에 있는 DB복원
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=scott file=c:\\dump.dmp
ex3) system 계정으로 scott 계정에서 export해서 hr계정에 import
dos>imp system/비밀번호@전역데이타베이스명 fromuser=scott touser=hr file=c:\\dump.dmp
ex4) 복원하고자하는 DB에 같은 이름의 Object가 있을때,오류를 무시하고 건너 띄고 싶을때 ignore 옵션사용
dos>imp 아이디/비밀번호@전역데이타베이스명 file=c:\\dump.dmp ignore=y
+) 기타 유용 코드
>>PROMPT 명령어
- VAR[IABLE] 변수명 자료형 : 바인드 변수 선언
- ED 파일명 : 해당 .sql파일을 EDIT(편집)한다. 해당 파일이 없으면 새로 만든다.
- @파일명 : 해당 .sql파일을 실행한다.
- PRINT 바인드변수 : 해당 바인드변수를 출력한다.
- SET SERVEROUT ON; : 출력 기능을 사용할 수 있게 해준다. ON해줘야 출력이 보인다.SESSION을 처음 실행할 때만 해주면 된다.
- PROMPT ‘ ’ : 입력받는다. 입력받은 걸 저장하진 않는다.
- ACCEPT 변수명 PROMPT ‘ ’ : ‘ ’를 출력하고, 값을 입력받아 변수에 저장한다.
-- &연산자를 사용하여 &변수명하면 입력받은 값을 참조하여 해당 값을 사용할 수 있다.
>>PL/SQL 코드
- DBMS_OUTPUT.PUT_LINE() : 출력 기능, 자바의 SYSTEM.OUT.println()과 동일.
- DBMS_OUTPUT.PUT() : 자바의 write()와 유사. 출력스트림을 flush해줘야 출력됨.
-- flush는 줄바꿈으로 한다.
방법1>> DBMS_OUTPUT.PUT_LINE(‘’); (‘’쓰지 않으면 ERROR 발생)
방법2>> DBMS_OUTPUT.NEW_LINE;
- &변수
- 테이블명.컬럼명%TYPE : 해당 테이블의 해당 컬럼의 ‘자료형’과 같은 자료형/자료크기를 사용한다.
- SQL%FOUND : 가장 최근의 SQL문이 하나 또는 그 이상의 행(RECORD)에 영향이 미첬다면(DML문이 정상적으로 실행됬다면) TRUE
ex) INSERT INTO 테이블 VALUES(~~~);
IF SQL%FOUND ~~~;
>> INSERT SQL문이 제대로 실행되어 RECORD에 영향을 미쳤다면(RECORD를 INSERT했다면) TRUE 반환.
- SQL%NOTFOUND : 어떤 행에 영향을 미치지 않았다면 TRUE반환
--위의 2개(FOUND/NOTFOUND)는 출력문에 사용불가 >> 조건문에서 주로 사용
- SQL%ROWCOUNT : 가장 최근의 SQL문장(DML문)에 의해 영향을 받은 행(RECORD)의 수
+) 데이터 사전
○ 사용자 계정 목록 보기
SQL> DESC DBA_USERS;
SQL> SELECT * FROM DBA_USERS
SQL> SELECT USERNAME FROM DBA_USERS
○ 사용자가 가진 시스템권란 보기
SQL> DESC dba_sys_privs
SQL> select privilege from dba_sys_privs where grantee='사용자명'
○ 제약조건 목록 보기
SQL> DESC USER_CONSTRAINTS
SQL> SELECT * FROM USER_CONSTRAINTS
○ 사용자가 만든 시퀀스 개체 목록
SQL> SELECT * FROM USER_SEQUENCES;
○ VIEW개체
SQL> SELECT VIEW_NAME,TEXT FROM USER_VIEWS;
○ INDEX개체
SQL> SELECT * FROM user_ind_columns;
○ 시스템 권한
SQL> SELECT * FROM SYSTEM_PRIVILEGE_MAP;
○ 객체 권한
SQL> SELECT * FROM TABLE_PRIVILEGE_MAP;