SQL 문장을 작성할 때는 항상 NULL을 염두에 두어야 함을 꼭 기억하자.
SELECT 문 : 데이터 조회
구문형식
SELECT 컬럼1, 컬럼2, ...
FROM 테이블1, 테이블2, ...
WHERE 조건들;
ex) SELECT last_name, first_name
FROM employees
WHERE hire_date >= '2006-01-01';
INSERT 문 : 테이블에 새로운 데이터를 넣을 때
구문형식
INSERT INTO 테이블명1 ( 컬럼1_1, 컬럼1_2, ...)
VALUES (컬럼1_1 값, 컬럼1_2 값, ...);
INSERT INTO 테이블명1
VALUES (컬럼1_1 값, 컬럼1_2 값, ...);
주의 사항 : 컬럼 리스트와 그 컬럼에 들어갈 값의 순서는 정확히 맞아야 한다. 두 번째 유형처럼 컬럼 리스트 생략이 가능한데, 이런 경우에는 테이블에 있는 모든 컬럼에 데이터를 넣는다는 의미이다.
UPDATE 문 : 기존 테이블에 저장되어 있는 데이터를 새로운 값으로 변경할 때
구문형식
UPDATE 테이블명
SET 변경컬럼1 = 변경하고자 하는 값,
변경컬럼2 = 변경하고자 하는 값,
...
WHERE 조건절 ..;
DELETE 문 : 데이터를 삭제할 때
구문형식
DELETE [FROM] 테이블
WHERE 조건...;
실제 데이터의 변경은 데이터 파일에 변경사항이 반영될 때 발생하게 된다. 그 전에는 변경된 데이터들은 오직 오라클 메모리 상에만 존재하게 된다.
COMMIT 문 : 데이터 변경 작업(INSERT, UPDATE, DELETE)을 한 뒤, COMMIT을 실행하면 변경된 데이터들이 데이터 파일에 반영된다.
구문형식
COMMIT [WORK] [TO SAVEPOINT savepoint_name];
'COMMIT;' 이나 'COMMIT WORK;'는 동일한 효과를 가진다. SAVEPOINT는 특정 시점까지 작업한 내용을 반영할 경우 사용한다.
ROLLBACK 문 : COMMIT과는 반대의 개념으로, 변경된 데이터들을 변경 전 상태로 되돌리는 역할
이전 ROLLBACK이나 COMMIT이 실행된 시점 이후에 변경된 사항은 모두 원상태로 되돌아간다.
구문형식
ROLLBACK [WORK] [TO SAVEPOINT savepoint_name];
CREATE 문 : 데이터베이스 객체들을 생성할 때 사용
기본 구조 - 'CREATE 객체종류 객체명 ...'
ex) CREATE TABLE 테이블명 ...
DROP 문 : 이미 생성된 객체들을 삭제할 때 사용
DELETE는 테이블 상의 데이터를 삭제할 때 사용하는 것이고, DROP은 객체 자체를 데이터베이스에서 영구히 없앨 때 사용하는 문장이다.
기본 구조 - 'DROP 객체종류 객체명 ...'
ALTER 문 : 기존에 생성된 객체를 변경할 때 사용
TRUNCATE 문 : 테이블에 있는 데이터를 삭제할 때 사용
TRUNCATE 문을 사용하여 데이터를 삭제했을 경우에는 ROLLBACK을 사용하여 데이터를 복구할 수 없다. TRUNCATE가 실행되면서 자동 COMMIT이 되기 때문이다.
TRUNCATE 문은 테이블과 클러스터에만 적용이 가능하다.
GRANT 문 : 접근제어나 어떤 작업을 허용하는 권한을 주는 역할
REVOKE 문 : 허용된 권한을 없애는 역할
테이블에 있는 일반적인 컬럼처럼 행동하기는 하지만 실제로 테이블에 저장되어 있지 않은 컬럼
ROWNUM
쿼리의 결과로 나오게 되는 각각의 로우들에 대한 순서값을 가리키는 의사컬럼이다.
ROWID
테이블에 저장된 각각의 로우들이 저장된 주소값을 가진 의사컬럼이다. 따라서 모든 테이블의 모든 로우들은 오직 자신만의 유일한 ROWID 값을 갖고 있다고 할 수 있다.
기본적인 테이블 생성 패턴은 CREATE TABLE이며 그 내용은 다음과 같다.
CREATE TABLE 테이블명
( 컬럼1 컬럼1타입,
...
);
ex) CREATE TABLE chr_exam1 (
names1 CHAR (3 BYTE),
names2 VARCHAR2 (3 BYTE) );
CHAR : 고정된 길이의 데이터 타입, 데이터를 넣을때 빈칸을 포함시켜서 고정길이로 채운다. 데이터 검색시에도 빈칸 포함해서 고정길이로 채운다.
VARCHAR2 : 가변 길이의 타입.
데이터의 크기를 정의할 때 그 크기유형(BYTE 혹은 CHAR)을 지정하지 않으면 디폴트값으로 BYTE가 적용된다.
NULL
데이터베이스에서는 NULL은 컬럼의 속성 중 하나로 해당 컬럼이 NULL 값을 허용하는지 허용하지 않는지를 정해줄 수 있으며, 테이블 생성 스크립트에서 데이터 타입 다음에 명시해 준다. 명시하지 않을 경우에는 디폴트값으로 NULL이 적용된다.
null_col1 VARCHAR2(20) NOT NULL,
null_col2 VARCHAR2(20) NULL,
null_col3 VARCHAR2(20) );
한 가지 주의할 점은 NULL은 값이 없는 것으로 공백(' ')과는 다르다는 점을 기억해 두자. 공백은 공백(SPACE)이라는 값이 있다.
UNIQUE 키
테이블에 있는 데이터를 유일하게 식별하기 위한 무결성 제약조건 중 하나. 한 개 이상의 컬럼도 UNIQUE 키로 만들 수 있다. 이런 경우를 복합 UNIQUE 키라고 한다.
uni_col1 VARCHAR2(10) UNIQUE NOT NULL,
uni_col2 VARCHAR2(10) UNIQUE, // NULL 을 허용하는 UNIQUE 키
uni_col3 VARCHAR2(10) NOT NULL,
uni_col4 VARCHAR2(10) NOT NULL,
CONSTRAINTS uni_tmp_uk UNIQUE ( uni_col3, uni_col4 ) );
기본 키
기본 키(Primary Key)는 UNIQUE와 동일하게 한 테이블에 있는 데이터들을 유이랗게 식별하기 위한 무결성 제약조건이다. 하지만 다른 점이 있다면 기본 키가 걸린 컬럼들은 반드시 NOT NULL 속성을 가진다는 점이다. 컬럼 하나 혹은 여러 개의 컬럼을 하나의 기본 키로 만들 수 있다.
employee_id NUMBER(6,0),
CONSTRAINT "employee_pk" PRIMARY KEY(employee_id) - 아웃라인 표기방식
외래 키
관계형 데이터베이스에서 관계(relationship)라는 용어는 바로 테이블 간의 관계를 의미한다. 그리고 이러한 관계는 테이블 간에 공통값을 가진 컬럼을 통해서 이루어진다.
DEPARTMENT_ID 컬럼은 DEPARTMENTS 테이블에서는 기본 키가 되지만 EMPLOYEES 테이블에서는 유일한 값이 아니며(중복값이 존재), 단지 참조정보(부서정보)를 갖고 있을 뿐이다. 바로 이러한 경우에 EMPLOYEES 테이블의 DEPARTMENT_ID를 외래 키(Foreign key)로 정의할 수 있다.
구문형식
컬럼명 CONSTRAINTS "외래 키 이름"
REFERENCES 참조테이블명 ( 참조 컬럼 );
ex) department_id CONSTRAINT fk_deptno
REFERENCES departments (department_id);
제약 사항
외래 키를 만들기 전에 반드시 부모 테이블이 먼저 생성되어 있어야 하며, 참조하는 부모 테이블의 컬럼은 반드시 기본 키 혹은 UNIQUE 키이어야 한다.
자식 테이블에 존재하는 값을 부모 테이블에서 삭제할 수 없다.
CHECK
제약조건은 입력되는 값을 체크하여 일정한 조건에 해당되는 값만 입력될 수 있게 하는 제약조건이다.
CONSTRAINT check_gender CHECK ( gender IN ('남성', '여성') )
만약 '남성'이나 '여성' 이외의 값을 입력할 경우 오류가 발생
디폴트
컬럼에 특정 값을 디폴트값으로 설정하면 테이블에 데이터를 입력할 때 해당 컬럼에 값을 입력하지 않을 경우 디폴트로 설정한 값이 자동으로 입력된다.
CONSTRAINT check_gender CHECK ( gender IN ('남성', '여성') ),
insert_date DATE DEFAULT SYSDATE,
...
주의할 점은 반드시 데이터 타입 다음에, NULL이나 NOT NULL 앞에 위치시켜야 한다.
테이블에 저장되어 있는 데이터를 좀 더 효율적이고 빠르게 찾기 위해 사용된다. 책 뒤편에 찾아보기 목록이 따로 있듯이 데이터베이스에서도 테이블에 있는 인덱스 컬럼의 데이터에 대한 인덱스 정보가 별도로 저장된다. 오라클에서는 자동으로 인덱스를 검색하여 데이터를 조회한다.
인덱스의 생성
인덱스는 테이블 생성과 동시에 인덱스를 생성할 수는 없다.
구문형식
인덱스가 생성되면 오라클은 인덱스 정보를 별도로 저장하는데 저장되는 인덱스 정보는 인덱스 컬럼값과 ROWID 정보를 담고 있다. 새로운 로우가 삽입되거나 업데이트, 삭제에 의해 데이터 변경이 일어나면 오라클은 자동으로 저장된 인덱스 정보를 갱신한다. UNIQUE 키나 기본 키를 생성하면 UNIQUE 인덱스가 자동으로 생성된다. 복합 인덱스에서는 컬럼의 순서도 중요한데 일반적으로 SELECT 문의 WHERE 절에서 사용되는 컬럼 순으로 인덱스를 구성해야 한다.
테이블과 매우 흡사한 오브젝트이다. 뷰와 테이블의 차이점은 뷰는 실제로 데이터를 저장하고 있지 않다는 점이다. 실제로 테이블에 저장된 데이터를 뷰를 통해서 보는 것이다.
구문형식
뷰는 테이블처럼 사용하면서도 정의된 쿼리의 결과만 볼 수 있으므로 다른 사용자들에게 데이터의 일부만 공개할 수 있는 것이다.
스키마 오브젝트들의 별칭을 말한다. 실제로는 데이터를 저장하고 있지 않으며 시노님의 정의만 데이터 딕셔너리에 저장되어 있다.
구문형식
PUBLIC 시노님을 생성하려면 PUBLIC을 추가해야 하고, 이를 생략하면 디폴트로 PRIVATE 시노님이 생성된다. 그런데 PUBLIC구문을 추가했을 경우에는 스키마명은 생략해야 한다. 왜냐하면 PUBLIC 시노님은 공개용으로 모든 사용자가 사용할 수 있으므로 특정한 스키마에 종속되지 않기 때문이다. 시노님을 제대로 사용하기 위해서는 시노님 생성 권한과 시노님의 대상 객체에 대한 권한이 있어야 한다. PUBLIC 시노님의 대표적인 것이 바로 DUAL이다.
연속적인 숫자를 생성해내는 객체를 말한다.
MINVALUE 1 // 시퀀스가 시작되는 최초의 숫자
MAXVAULE 99999 // 시퀀스가 끝나는 최대 숫자
INCREMENT BY 1 // 시퀀스가 증가되는 단위. 0은 올 수 없다.
START WITH 207 // 시퀀스 생성이 시작되는 값.
NOCACHE // CACHE 를 사용하면 시퀀스를 생성하기 위해 미리 값을 할당해 놓기 때문에 좀 더 빠른 접근이 가능.
NOORDER // ORDER 를 사용하면 요청되는 순서대로 값을 생성.
NOCYCLE; // 최대치(증가) 혹은 최소치(감소)에 다다랐을 때에 초기값부터 다시 시작할지 여부.
시퀀스의 삭제
구문형식
사용자들에게 의미 있는 데이터를 제공하기 위해서는 각각의 테이블에 분리되어 있는 연관성 있는 데이터들을 연결하거나 조합해야 하는데 바로 이러한 일련의 작업들을 조인이라고 한다.
WHERE 절에서 명시할 수 있는 조건 : 테이블에 있는 데이터들을 걸러내는 필터역할을 하는 일반조건
조인 시에 테이블들을 연결하는 조인조건
조건절을 구성하는 항목들 : 연산자 ( +, -, *, /, =, >, < 등 )
컬럼, 숫자나 문자 상수
LIKE, IN, BETWEEN, EXISTS, NOT
IS NULL, IS NOT NULL,
함수
AND, OR, NOT
ANY, SOME, ALL
UNKNOWN 타입 : NULL 이 포함된 조건연산을 수행할 경우 발생하는 타입. 즉 NULL 이 포함된 연산에서 조건이 맞지 않는 경우 UNKNOWN 타입을 리턴.
범위 조건
조건절에 명시된 컬럼값이 어느 범위에 걸쳐 있는지를 검사하는 경우 BETWEEN ... AND 구문을 사용할 수 있다.
first_name || ' ' || last_name 성명,
FROM employees
WHERE employee_id BETWEEN 110 AND 120; // 사원번호가 110번에서 120번까지의 사원명단 구해라!
IN
특정 컬럼값이나 변수 값이 여러 개의 값에 포함되는지 여부를 비교하는 조건
first_name || ' ' || last_name 성명,
FROM employees
WHERE department_id IN ( 30, 60, 90 );
EXISTS
특정 컬럼값이 존재하는 여부를 체크한다. 괄호안에 오직 서브 쿼리만 올 수 있다.
emp.department_id 부서코드
FROM employees emp
WHERE EXISTS
( SELECT 1
FROM departments dep
WHERE dep.department_id IN ( 30, 60, 90 )
AND emp.department_id = dep.department_id );
EXISTS는 존재하느냐 존재하지 않느냐의 여부만 체크하기 때문에 서브쿼리의 SELECT 리스트에는 등장하는 값과는 상관없이 서브쿼리의 결과로 반환되는 로우가 있느냐 없느냐만 중요한 것이다.
LIKE
'~와 같은'이라는 뜻.
LIKE에서 사용되는 %는 모든 문자를 대체하는 효과를 지닌다.
NULL 처리
조회조건에서 NULL만을 검색하기 위한 구문을 별도로 가지고 있다.
IS NULL
FROM locations
WHERE state_province IS NULL;
NULL 이 아닌 데이터를 조회할 때는 NOT을 추가해서 IS NOT NULL 조건을 준다.
연관관계가 성립되는 데이터들을 SQL 문장에서 연결하여 조회하는 것을 바로 조인(Join)이라 한다.
구문형식
SELECT 컬럼 리스트
FROM 조인대상 테이블
WHERE 조인조건;
조인은 두 개 이상의 테이블들이 대상이 되므로 FROM 절에 연결할 테이블들을 기술해 주는데 각 테이블의 구분은 콤마(,)를 사용한다.
WHERE 절에서는 조인되는 테이블들 간에 사용되는 공통 컬럼이 어떤 것이며, 어떤 연관관계를 맺고 있는지를 기술한다. WHERE 절에 기술하는 이 부분을 바로 조인조건이라고 한다.
FROM employees, departments
WHERE employees.department_id = departments.department_id;
단일 테이블에 있는 데이터를 조회할 경우에는 상관없지만 조인 시에는 조회 대상 테이블이 두 개 이상이므로 컬럼을 명시할 때 항상 '테이블명.컬럼명' 형식을 따라야 한다.
외부조인
조인에 참여하는 테이블 중 어느 한 테이블에서만 조회조건을 만족한다면 다른 한 테이블의 값이 없더라도 데이터를 조회할 수 있다.
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id (+)
ORDER BY emp.department_id;
외부조인은 조회조건에서 (+) 기호를 사용한 조인을 말하는 것이다. 테이블에 해당 데이터가 존재하지 않더라도 이를 무시하고 조인에 참여하라는 역할을 하는 것이 바로 (+)이다. 데이터가 존재하지 않는(조건에 맞는 로우가 없는) 테이블의 조인조건에 (+)를 붙여야 한다.
어떤 연산이나 작업을 수행하고 난 뒤 그 결과를 반환한다. 오라클에서 사용되는 함수는 작성자에 따라 내장함수와 사용자 정의함수로 구분할 수 있다. 내장함수란 오라클에서 자체 제공되는 함수로써 정식 명칭은 SQL 함수라고 부르며, 사용자 정의함수는 개발자나 DBA등 오라클 사용자들이 필요에 따라 직접 작성한 함수를 말한다.
일반적인 함수의 구조
SQL 문장에서 함수가 사용될 수 있는 곳은 다음과 같다.
SELECT 리스트 , WHERE 절, START WITH 절, HAVING 절, INSERT 문의 INTO 절, UPDATE 문의 SET 절
숫자형 함수
숫자형 데이터를 가지고 연산을 수행하여 그 결과(결과 역시 숫자형)를 반환하는 기능을 수행하는 함수들
ABS(n) : 숫자의 절대값을 반환
SIGN(n) : n이 양수인지 음수인지의 여부를 반환
ROUND(n, i) : n을 소수점 이하 i+1 번째 자리에서 반올림한 결과를 반환
TRUNC(n1, n2) : 숫자를 잘라내는 기능. 즉 n1 값을 n2 번째 자리에서 잘라낸다.
CEIL(n) : n 보다 큰 수 중에서 가장 작은 정수.
1
FLOOR(n) : n보다 작은 수 중에서 가장 큰 정수
0
문자형 함수
CONCAT(char1, char2) : 파라미터로 들어오는 두 문자열을 연결하여 그 결과를 반환. ( '||' 연산자도 같은 기능 )
INITCAP(char) : 파라미터인 char의 첫 문자를 대문자로 바꾸는 함수로써 첫 문자만을 대문자로 변환하며 나머지 문자들을 모두 소문자로 변환.
LPAD(expr1, n ,[expr2]) : expr1을 n자리만큼 문자열 길이를 늘려서 반환하는 함수. expr1 자릿수가 n보다 작을 경우에는 n-expr1의 길이만큼 expr2로 들어오는 문자열로 왼쪽을 채워 반환한다.
####abc
SUBSTR(char, position, length) : 문자열의 일부분을 떼어내는 기능을 하는 함수. char 문자열에서 position으로 지정된 위치로부터 length개의 문자를 떼어내어 그 결과를 반환하게 된다.
not
REPLACE(char, search_string, replace_string) : 문자열 중 일부를 다른 문자열로 변경하여 그 결과를 반환하는 함수. char 문자열에서 search_string 값으로 들어온 문자를 replace_string 값으로 대체하여 이 결과를 반환.
We are not alone
날짜형 함수
SYSDATE : 시스템의 현재 날짜를 반환하는 함수. 파라미터가 없다.
CURRENT_DATE : 현재 세션의 시간대를 기준으로 한 현재 날짜를 반환.
NULL 관련 함수
NVL(expr1, expr2) : 첫 번째 파라미터인 expr1 값이 NULL 이면 expr2를 반환하고, NULL 이 아니면 expr1 값을 반환하는 함수.
NVL2( expr1, expr2, expr3) : expr1이 NULL이면 expr3를, NULL이 아니면 expr2를 반환.
변환함수
TO_CHAR(datetime) : 날짜형 데이터를 VARCHAR2 타입으로 변환. 특정한 포맷으로 변환할 수 있다.
TO_CHAR(number) : 숫자형 데이터를 VARCHAR2 타입으로 변환
TO_NUMBER(expr, fmt) : 파라미터로 들어온 expr을 NUMBER 타입으로 변환. fmt로 지정된 포맷으로 데이터 반환 가능.
TO_DATE(char, fmt) : 파라미터로 들어온 char을 DATE 타입으로 변환한 결과를 반환. fmt는 날짜 포맷.
DECODE
구문형식
첫 번째 파라미터로 들어오는 expr 표현식을 검사하여 이 값이 search1에 해당할 경우에는 result1을, search2에 해당할 경우에는 result2를 반환하게 되며 명시된 모든 search값에 해당하지 않을 경우 default_result 값을 반환한다. 만약 default_result 값이 생략되어 있고 모든 search 값을 만족하지 않을 경우에는 NULL을 반환한다.
DECODE의 경우에는 오직 expr과 search 값이 같은 것만 비교된다. 즉 동등 연산자(equal)만 사용되는 셈이라고 볼 수 있다.
CASE
조건을 비교하고 그 결과를 반환하는 측면에서 DECODE와 유사하나 DECODE보다 더 다양한 조건을 줄 수 있다.
검색형 구문
WHEN 비교조건2 THEN 처리2
...
ELSE 디폴트 처리
정보별로 데이터를 묶은 다음 이들 각각을 대상으로 집계성 정보를 추출해야 할 경우 첫째로 어떤 단위로 묶을 것인가를 결정하고 둘째로는 묶은 정보를 바탕으로 어떤 정보를 추출할 것인지 결정해야 한다. 바로 첫 번째 작업에 해당하는 것이 GROUP BY 절이며, 두 번째에 해당하는 것이 그룹 함수의 사용이다.
DISTINCT : 해당 컬럼중 중복을 제거한 결과를 보여준다.
COUNT ( [DISTINCT , ALL] expr ) : 테이블에 있는 데이터(로우)가 몇 건이 존재하는지 반환하는 함수
SUM ( [DISTINCT, ALL], expr ) : expr의 전체 합계를 계산하여 그 결과를 반환하는 함수. 파라미터로 반드시 숫자형 데이터 타입의 값이나 컬럼이 와야 한다.
MAX, MIN, AVG, STDDEV, VARIANCE ..
WHERE 조건 절에서는 집계 함수를 사용할 수 없다.
특정 범위별 집계 데이터를 얻기 위해서는 바로 '특정 범위'를 지정해 주어야 한다. 이를 "그룹을 짓는다"라는 의미로 그룹핑이라고도 한다. 이러한 특정 범위는 바로 GROUP BY 절을 사용해서 지정할 수 있다. GROUP BY 절은 집계 함수와 짝을 이뤄야 좀 더 의미있는 데이터를 추출할 수 있다.
GROUP BY department_id; DEPARTMENT_ID SUM ( SALARY )
100 51600
30 24900
7000
...
위의 결과를 보면 부서별 월급 총계를 볼 수 있다.
SELECT 문장에서 GROUP BY 절을 사용할 경우에는 SELECT 리스트에 있는 항목 중 집계 함수를 제외하고는 모든 항목이 GROUP BY 절에 명시되어야 한다.
GROUP BY 절과 같이 사용되어 SELECT 문장에서 집계 함수를 사용한 조건을 명시하는 절이다.
WHERE department_id IS NOT NULL DEPARTMENT_ID COUNT ( * )
GROUP BY department_id 10 1
HAVING COUNT ( * ) <= 5 20 2
ORDER BY department_id; 40 1
...
SET 연산자는 여러 개의 SELECT 문장을 하나의 문장으로 취급한다는 점에서는 서브쿼리와 비슷하지만 SELECT 문장들의 관계가 종속적이라기 보다는 동등한 관계라는 점에서는 차이가 있다.
A와 B란 데이터 셋이 있을 경우, A U B 로 표현되는 집합을 합집합이라 한다. 합집합은 A와 B에 포함된 모든 요소(데이터)들을 포함한 집합을 말한다.
SELECT 1, 3, 4, 5, 7, 8 FROM dual 1 3 4 5 7 8
UNION 1 3 4 5 7 8
SELECT 2, 4, 5, 8, 9, NULL FROM dual; 2 4 5 8 9
SET 연산자를 사용할 경우에는 SET 연산자로 묶이는 SELECT 문장의 열의 개수가 반드시 같아야 한다. 또한 두 SELECT 문장에서 반환되는 컬럼의 순서에 따른 데이터 타입도 동일한 유형이어야 한다.
UNION의 경우에는 공통값을 가진 로우를 한 번만 추출하기는 하는데, 이 공통값이 어느 한 데이터 셋에 포함되어 있을 경우에만 한 번만 추출해 낸다. 만약 공통값이 두 테이블에 모두 있을 경우에는 합집합과는 달리 두 번 추출하게 된다.
집합론에서 차집합과 같다. 차집합의 경우에는 어느 집합이 먼저 오는지에 따라 그 결과에 차이가 있다.
'SQL을 확장한 순차적 처리 언어'. 한 마디로 말해 PL/SQL은 데이터 베이스 질의어인 SQL과 일반 프로그래밍 언어의 특성을 결합한 언어이다. 즉 PL/SQL을 사용하면 조건문이나 반복문, 변수나 상수를 선언해서 사용할 수 있을 뿐만 아니라 SQL도 사용할 수 있다.
PL/SQL의 기본 단위는 블록이다. 하나의 블록은 다시 다음과 같이 세 부분으로 구분된다.
선언부 : 실행부에서 사용할 변수나 상수를 선언하는 부분. 'DECLARE' 를 사용해서 나타낸다.
실행부 : 실제 처리할 로직을 담당하는 부분. 'BEGIN' 으로 시작되어 'END' 로 끝나게 된다.
예외처리부 : 각종 오류들에 대해 처리하는 부분. 'EXCEPTION' 키워드를 사용한다.
counter INTEGER; // INTEGER형 변수 counter 선언
BEGIN
counter := counter + 1; // 값을 할당할 때 '='가 아닌 ':='를 사용함
IF counter IS NULL THEN
dbms_output.put_line( 'Result : COUNTER IS NULL' ); // SET SERVEROUTPUT ON 설정해야 출력보기 가능
END IF;
END; // EXCEPTION 처리는 생략 가능
함수나 프로시저를 묶어 놓은 것을 오라클에서 패키지라고 부른다. 좀 더 자세히 설명하면, 패키지란 처리하는 작업의 성격이 비슷한 함수나 프로시저를 하나로 묶어 놓은 오라클 객체를 말한다.
패키지란 오라클 데이터베이스에 저장된 프로시저, 함수 뿐만 아니라 변수, 상수, 커서, exception 들을 하나로 묶은 캡슐화된 객체를 말한다.
사용자들이 입력하는 데이터가 아닌 오라클 DBMS를 운영하는데 필요한 정보들을 관리하는데 사용되는 객체들이다. 이러한 시스템 객체들을 오라클에서는 데이터 딕셔너리 혹은 데이터 사전이라고 부른다. 보통 데이터 딕셔너리는 오라클에 대한 시스템 정보를 갖고 있기 때문에 개발자들을 포함해 오라클 사용자들은 이들을 참조 즉 읽기만 할 수 있다.
뷰를 통해 일반 사용자들에게도 필요할만한 정보나 데이터들을 제공하고 있다. 이들을 가리켜 시스템 뷰라고 한다.
ALL 뷰
시스템 뷰들 중에 그 이름이 'ALL_'로 시작되는 뷰들은 오라클 상에 있는 대부분의 모든 객체들에 대한 정보를 갖고 있다. 이 뷰들은 사용자가 소유한 오브젝트들과 사용자들의 접근이 가능한 오브젝트들에 대한 정보를 제공한다.
출력결과
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
SYS DUAL TABLE 258
PUBLIC DUAL SYNONYM 259
...
DBA 뷰
데이터베이스 관리자를 위한 정보를 제공하는 뷰들이다. 일반 사용자들은 DBA로 시작되는 뷰들을 조회할 수 없다.
USER 뷰
USER로 시작되는 뷰의 경우 로그인한 사용자가 소유자가 되는 스키마 정보들을 갖고 있다.
예를 들어, HR 사용자로 로그인한 경우 자신이 만든 테이블들만 조회하고자 한다면 USER_TABLES, 자신이 만든 객체에 대한 정보만 조회하고자 할 경우에는 USER_OBJECTS를 참조하면 된다.
SQL 문장을 처리하기 위한 목적을 가진 일종의 최적화 도구.
실행계획이란 작성된 SQL 문장을 분석 및 처리하기 위한 일련의 절차를 명시한 계획이라고 할 수 있다.
옵티마이저가 실행계획 만드는 순서
1. 주어진 SQL 문장에 대해 가능한 모든 실행계획을 세운다.
2. SQL 문장을 처리하기 위해 각종 통계정보(데이터 딕셔너리에 해당 정보가 있다), 예를 들면 테이블에 저장된 데이터들의 저장정보, 데이터 분산도, 인덱스 정보 등을 기초로 해서 이전 단계에서 만들어냈던 각각의 실행계획에 대한 비용(cost)를 산출해 낸다.
3. 각각의 계산된 비용을 비교해서 옵티마이저는 가장 적은 비용이 예상되는 실행계획을 선택한다.
옵티마이저를 위해 우리가 해야 할 일
1. 통계 정보의 주기적 갱신
옵티마이저가 제대로 된 실행계획을 만들어 내기 위해서는 통계 정보가 최신 상태로 유지되어야 한다.
ANALYZE TABLE 명령어
2. 비용 모델의 선택
OPTIMIZER_MODE 란 파라미터 값을 설정함으로써 옵티마이저 동작방식을 결정할 수 있다.
전체적인 처리 결과를 우선시 할 경우에는 ALL_ROWS 값을, 전체적인 처리는 좀 늦더라도 첫 번째 행을 반환하는 시간이 빠르게 하는 방식으로 실행계획을 작성하고자 한다면 FIRST_ROWS 값으로 설정한다.
어떤 경우에는 무조건 오라클에 맡기는 것보다는 사용자가 직접 관여하는 경우가 쿼리를 더 효율적으로 처리할 수도 있다. 왜냐하면 오라클 엔진은 범용적인 엔진이기 때문에 모든 시스템에 대해 항상 효율적인 처리를 한다고 보장할 수 없기 때문이다.
오라클에서는 옵티마이저의 동작에 어느 정도 관여할 필요성이 있을 때 힌트 절을 사용하여 사용자가 명시한 대로 옵티마이저의 동작을 조정할 수 있다.
힌트 절의 종류
힌트 절은 쿼리의 일부에 포함되며, 해당 문장에 대해서만 적용이 된다. 보통 힌트 절은 다음과 같이 세 가지 형태로 사용된다.
/*+ hint */
/*+ hint( argument ) */
/*+ hint( argument-1 argument-2 ) */
간단히 정리하면 /*+ 와 */ 사이에 사용할 힌트 종류를 명시하며, 종류에 따라 파라미터가 한 개 혹은 두 개까지 올 수 있다.
일반적인 SQL 튜닝 기법
개발자라면 옵티마이저가 실행계획을 생성할 때, 최대한도로 성능을 발휘할 수 있도록 불필요한 로직을 타지 않게 쿼리를 작성해야 한다.
일반적인 가이드 라인
바인드 변수를 사용한다.
가급적 WHERE 조건에서는 인덱스 컬럼을 모두 사용한다.
인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자(=)를 사용하라.
인덱스 컬럼은 변형하여 사용하지 않도록 한다.
OR 보다는 AND를 사용하라.
그룹핑 쿼리를 사용할 경우 가급적 HAVING 보다는 WHERE 절에서 데이터를 필터링하라.
DISTINCT는 가급적 사용하지 않는다.
IN, NOT IN 대신 EXISTS와 NOT EXISTS를 사용하라.
SET 연산자 사용시 UNION 대신 UNION ALL을 사용하라.
SET AUTOT TRACE : SQL 문장 처리 비용을 보여준다.
취소는 SET AUTOT OFF
PURGE RECYCLEBIN : drop table 명령으로 휴지통에 남은 테이블 완전히 없애기.
FLASHBACK TABLE [테이블명] TO BEFORE DROP : 휴지통에 남아있는 테이블 복구.
오라클 SQL*Plus 로그인 환경 설정하기
login.sql 파일을 생성해서
을 입력하고
C:\oracle\product\10.2.0\db_1\BIN 폴더에 login.sql을 복사해 넣어준다. 그리고 로그인하면 파일내용이 적용된 환경이 된다.
SHOW ERRORS : 최근 오류 살펴보기
SELECT * FROM USER_ERRORS