고정 헤더 영역
상세 컨텐츠
본문
ANSI/ISO SQL: 1999 Standard Support In Oracle10g
ANSI/ISO SQL 1999년의 표준에 부합하는 기능을 Oracle10g에서 제공하며, 가장 대표적인 것으로는 죠인, CASE 표현식 및 문장, 스칼라 서브쿼리와 명시적 DEFAULTS에 대한 지원을 위주로 설명한다.
SQL을 사용하는 응용프로그램
Oracle SQL은 ANSI/ISO SQL: 1999 표준을 지원하며, 기존의 코드를 수정하지 않고 응용 프로그램을 이전할 수 있다. 또한 데이터베이스 내에 ANSI/ISO 표준 기능들을 제공하며, 다른 데이터베이스 제품으로부터 사용자를 유도하는데 용이하다.
SQL : 1999 죠인
1999 죠인 문법은 다음과 같은 두가지 이유로 Oracle에서 지원하는 죠인과 다르다.
1. 1999 문법에서는 FROM 절에 죠인의 유형을 명시적으로 표기한다.
2. 죠인 조건은 WHERE 절에서의 검색 조건과 구분되며, ON 절을 이용해서 표기한다.
추가된 죠인 유형은 다음과 같이 5가지로 나뉜다.
1. CROSS 죠인
2. NATURAL 죠인
3. USING 절을 사용한 죠인
4. 전체 또는 양측 OUTER 죠인
5. OUTER 죠인에 대한 임의 죠인 조건
1. CROSS 죠인
CROSS 죠인은 두 개 테이블 사이의 카텐시안 프로덕트(Cartensian Product)를 생성한다.
문장에 대한 문법은 다음과 같다.
SELECT <column list> FROM <table > CROSS JOIN <table>;
실제 EMP 테이블과 DEPT 테이블에서 CROSS 죠인을 수행하는 SQL문장은 다음과 같다.
SQL> SELECT e.ename, e.deptno, d.deptno, d.dname FROM emp e CROSS JOIN dept d;
2. Natural Join
1. NATURAL 죠인은 같은 이름을 갖는 두개의 테이블의 모든 컬럼에 기반한다.
2. 모든 일치하는 컬럼에서 값이 동일한 두개의 테이블의 레코드를 선택한다.
3. 만약 같은 이름을 갖는 두개의 테이블이 다른 데이터타입을 가지고 있다면 에러가 발생한다.
4. 만약 ‘SELECT * …’ 문장이 사용된다면 질의 결과에서 중복된 컬럼은 한번만 나타난다.
5. 테이블 이름 또는 별명(Aliase)은 NATURAL 죠인에서 죠인 컬럼으로 사용될 수 없다.
NATURAL 죠인의 문법은 다음과 같다.
SELECT <column_list> FROM <table> NATURAL JOIN <table>;
실제 EMP 테이블과 DEPT 테이블에서 NATURAL 죠인을 하는 문장은 다음과 같다.
SQL> SELECT ename, deptno, deptno, dname FROM emp NATURAL JOIN dept;
이 NATURAL 죠인을 Oracle 9i이전에 지원하던 형태로 바꾸면 다음과 같다.
SQL> SELECT e.ename, e.deptno, d.deptno, d.dname FROM emp e, dept d
2 WHERE e.deptno = d.deptno;
만약에 테이블 명 또는 별명(Aliase)를 명시할 경우에는 다음과 같이 에러가 발생한다.
SQL> SELECT e.ename, e.deptno, d.deptno, d.dname FROM
emp e NATURAL JOIN dept d;
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
USING 절을 이용한 죠인문 생성하기
NATURAL 죠인의 경우에는 동일한 컬럼이름을 가지고 있는 컬럼이라도 데이터 타입이 다를 경우에는 에러를 발생한다. 따라서, 이경우에 이들 컬럼들에 대해서 이퀴-죠인(Equi-Join)을 사용하도록 USING 절에서 명시해주도록 SQL문을 변경할 수 있다.
단, USING 절에서 참조되는 컬럼의 경우에는 SQL문장에서 테이블 명 또는 별명과 같은
문법은 아래와 같다.
SELECT <column_list> FROM <table_name> JOIN <table_name> USING <column_name>;
EMP 테이블과 DEPT 테이블에서 USING 절을 이용한 SQL문은 다음과 같다.
SQL> SELECT ename, deptno, dname FROM emp JOIN dept USING(deptno);
On 절을 이용한 죠인 생성하기
NATURAL 죠인 조건의 경우 기본적으로 동일한 이름을 갖는 모든 컬럼의 이퀴죠인이지만, 임의의 죠건을 명시하거나 또는 죠인에 대한 컬럼을 명시하는데는 ON 절을 이용하게 된다. 따라서, 다른 필터 조건 예를 들면 WHERE절과 테이블의 죠인 조건을 분리하는 역할을 하게 된다. 또한, 이해하기가 위운 SQL문장을 작성할 수가 있게 된다.
문법은 USING 절을 이용하는 것과 유사하며, 다만 ON을 사용한다는 것이다.
SELECT <column_list> FROM <table_name1> JOIN <table_name2>
ON <join_condition> JOIN <table_name3> ON <join_condition> JOIN ….;
예를 들어, EMP란 테이블과 DEPT란 테이블의 죠인 컬럼이름이 각각 emp_deptno와 deptno와 같이 상이 한 경우에는 USING 절이나 NATURAL죠인을 이용할 수 없게 되므로, 이경우에 ON절을 이용해서 SQL문을 작성하는 예와 결과는 다음과 같다.
SQL> SELECT e.ename, e.emp_dept, d.deptno, d.dname FROM temp_emp e JOIN
2 temp_dept d ON (e.emp_deptno = d.deptno) ;
INNER 대 OUTER 죠인
NATURAL 죠인과 같은 INNER 죠인의 경우에는 매치되는 레코들만을 얻게 되지만, 매치되는 레코드 뿐 만 아니라, 매치되지 않는 레코드까지는 얻는 것이 OUTER 죠인이다. 또한 좌 OUTER 죠인과 우(RIGHT) OUTER 죠인을 모두 적용한 FULL OUTER 죠인을 지원한다.
먼저 좌(LEFT) OUTER 죠인의 문법은 다음과 같다.
SELECT <column_list> FROM <table_name1>
LFET OUTER JOIN <table_name2> ON <join_condition>;
예를 들어, EMP 테이블과 DEPTNO를 LEFT OUTER죠인을 하는 SQL문을 실행한 결과는 다음과 같다.
SQL> SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e LEFT OUTER JOIN dept d ON (e.emp_deptno = d.deptno);
위의 결과문을 Oracle 9i이전의 SQL문으로 작성한다면 아래와 같은 SQL문이 된다.
SQL> SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e , dept d
3 WHERE e.emp_deptno = d.deptno(+);
이번에 반대의 경우로 EMP와 DEPT 테이블에 대해서 우(RIGHT) OUTER죠인을 실행하는 SQL문의 예와 결과는 다음과 같다.
SQL> SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e RIGHT OUTER JOIN dept d
3 ON (e.emp_deptno = d.deptno);
좌 OUTER 죠인과 마찬가지로 Oracle 9i이전의 SQL문으로 작성한다면 다음과 같은 SQL문으로 작성할 수 있다.
SQL > SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e ,dept d
3 WHERE e.emp_deptno(+) = d.deptno;
완전(FULL) OUTER 죠인의 경우에는 두 개의 테이블에 대해서 매치되는 레코드 뿐만 아니라, 좌 OUTER 죠인과 우 OUTER 죠인의 결과를 모두 포함하게 된다.
아래의 SQL문은 EMP 테이블과 DEPT 테이블에 대해서 완전 OUTER 죠인을 이용하는 SQL문을 작성하는 예를 나타낸다.
SQL> SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e FULL OUTER JOIN dept d
3 ON (e.emp_deptno = d.deptno);
완전 OUTER 죠인 역시 좌/우 OUTER 죠인과 마친가지로 Oracle 9i이전의 SQL문으로 작성하게 되면 UNION을 이용해서 좌 OUTER 죠인과 우 OUTER 죠인을 합병해야 한다.
SQL> SELECT e.ename, e.emp_deptno, d.deptno, d.dname
2 FROM emp e , dept d
3 WHERE e.emp_deptno = d.deptno(+)
4
5 SELECT e.ename, e.emp_deptno, d.deptno, d.dname
6 FROM emp e , dept d
7 WHERE e.emp_deptno(+) = d.deptno;
복합 죠인 생성하기
ON 절에 서브 질의문, AND/OR, [NOT]EXISTS와 [NOT] IN을 이용하여 좀 더 복잡한 형태의 질의문을 생성할 수 있다.
현재 보너스를 지급하고 있는 사원에 대해서 사원이름과, 부서번호, 부서명을 출력하는 SQL문과 결과는 다음과 같다.
SQL> SELECT e.ename , e.deptno, d.deptno, d.dname
2 FROM emp e JOIN dept d
3 ON (e.deptno = d.deptno)
4 AND e.ename IN (SELECT ename FROM bonus);
SQL문에서 CASE 표현식 : 1999
SQL은 CASE 문장의 네 가지 유형을 지원한다.
1. 간단한 CASE 표현식
2. 검색 CASE 표현식
3. NULLIF
4. COALESCE
이중에서 간단한 CASE 표현식은 Oracle 8.1.7에서 제공되었으며, 나머지 기능들은 Oracle 9i에 포함된다. 또한 CASE 표현식은 PL/SQL에서도 지원되며, 표현식과 문장으로 분류된다.
간단한 CASE 표현식
이것은 Oracle 9i이전의 서버에서 제공하던 DECODE 문장과 유사하며 주어진 표현식에서 검색을 하거나 값을 치환하는데 사용될 수 있다. 또 각각의 검색 값에 대해서 반환되는 값을 명시할 수 있으나, 비교 연산자는 허용되지 않는다.
EMP 테이블에서 각 사원의
SQL> SELECT ename,
2 (CASE EXTRACT(YEAR FROM hiredate)
3 WHEN 1982 THEN '3 years service'
4 WHEN 1981 THEN '4 years service'
5 WHEN 1980 THEN '5 years service'
6 END) AS "Award for 2000"
7 FROM emp
8 WHERE EXTRACT(YEAR FROM hiredate) IN (1982,1981,1980)
9 ORDER BY hiredate;
검색 CASE 표현식
검색 CASE 표현식은 IF…THEN ELSE 구조와 유사하며, 표현식 내에서 조건에 따른 검색과 값들의 치환을 하는데 사용될 수 있다. 각 WHEN 조건은 논리적 연산자(AND,OR등)와 결합되어 사용될수 있으며, 조건 표현식에서 비교 연산자를 사용할 수 있다. 따라서, 간단한 CASE 표현식보다는 유연하다고 할 수 있다.
예를 들어 EMP 테이블에서 Sal 컬럼의 범위에 따른 영역을 표현하는 SQL문과 결과는 다음과 같다.
SQL> SELECT empno, ename,
2 (CASE
3 WHEN sal >= 5000 THEN 'High Sal'
4 WHEN sal >= 3000 AND sal < 5000 THEN 'Middle Sal'
5 WHEN sal >= 1000 AND sal < 3000 THEN 'Average Sal'
6 WHEN sal < 1000 THEN 'Low Sal'
7 END) AS Sal_Category
8 FROM emp;
NULLIF 함수
NULLIF 함수의 경우 첫 번째 입력인자와 두번째 입력인자가 같은 경우에는 NULL을 반환한고, 그렇지 않을 경우에는 첫 번째 입력인자의 값을 반환한다.
예를 들어, EMP 테이블의 사원중에 Sal 컬럼의 값이 5000인 사람에 대해 NULLIF함수를 적용하는 SQL문과 결과는 다음과 같다.
SQL> SELECT ename, sal
2 FROM emp
3 WHERE NULLIF(sal, 5000) IS NULL;
만약 NULLIF 함수를 CASE 표현식으로 나타낸다면 다음과 같다.
CASE
WHEN expression1 = expression2 THEN NULL
ELSE expression1
END CASE;
COALESCE 함수
COALESCE 함수는 Oracle의 NVL 함수를 일반화한 것이다. 즉, 첫번째 입력 인자가 널이 아닐 경우에는 첫번째 입력인자를 반환하고, 아닐 경우에는 두번째 표현식에 대해서 평가를 계속하게 된다.
예를 들어, EMP 테이블에서 COMM 컬럼의 값이 널인지 검사하는 SQL문은 다음과 같다.
SQL> SELECT ename, COALESCE(comm,’NULL’)
2 FROM emp;
NULLIF 함수와 유사하게 CASE 표현식으로 나타내면 다음과 같다.
CASE
WHEN expression1 IS NOT NULL THEN expression1
ELSE expression2
END CASE;
또한, NVL 함수에 비해 COALESCE 함수의 장점은 여러 대체 값을 취할 수 있다는 것이다. 예를 들어, 첫번째 입력 인자가 널일 경우에는 나머지 표현식에 대해서 COALESCE를 계속 적용하는 것이 가능하다.
스칼라 서브질의(Scalar Subqueries)
스칼라 서브질의는 질의 표현식으로부터 생성되는스칼라 값을 명시하는데 사용되며, 오라클 8.1에서 제한된 기능으로 제공되었다. 예를 들어, 삽입 문장에서 VALUES 리스트와 TYPE 생성자에 대한 입력인자가 대표적이다.
Oracle 9i에서의 스칼라 서브질의는 WHERE 절에서 사용될 수 있으며, 유효한 표현식이 사용될 수 있는 어떤 곳에서도 사용될 수가 있다.
스칼라 서브질의는 단지 하나의 값만을 반환하며, 반환되는 값의 데이터 타입은 서브 질의에서 선택될 값과 일치한다.
스칼라 서브 질의는 다음과 같은 곳에서 사용될 수 있다.
1. DECODE와 CASE 표현식과 조건에서 사용될 수 있다.
2. GROUP BY절을 제외한 SELECT 문장의 모든 절에서 사용될 수 있다.
3. INSERT 문장의 VALUES절에서 사용될 수 있다.
4. UPDATE 문장의 SET 절과 WHERE 절에서 사용될 수 있다.
하지만, 다음과 같은 곳에서는 지원되지 않는다.
1. 컬럼에 대한 기본 값(DEFAULT VALUE)로 사용될 수 없다.
2. 반환 절에서 사용될 수 없다.
3. 클러스터에 대한 해쉬 표현식에서 사용될 수 없다.
4. 함수 인덱스(Functional Index) 표현식에서 사용될 수 없다.
5. 컬럼의 체크 제약(Check Constraints)에서 사용될 수 없다.
6. 트리거의 조건식에서 사용될 수 없다.
7. GROUP BY절에서 사용될 수 없다.
CASE 표현식에서 스칼라 서브질의 사용하기
스칼라 서브 질의들은 DECODE와 CASE 표현식들에 대한 표현의 일부와 조건에 모두 사용될 수 있다.
예를들어, EMP 테이블의 사원중에 BONUS 테이블에 등록된 사원의 이름을 검색하는 SQL문은 다음과 같다.
SQL> SELECT ename, sal,
2 (CASE
3 WHEN ename IN (SELECT ename FROM bonus) THEN 'Paid'
4 END) AS paid_status
5 FROM emp;
함수에서 스칼라 서브질의 사용하기
스칼라 서브질의는 Oracle에서 제공하는 함수, 사용자 정의 함수와 타입 생성자에서 입력 인자로 사용될 수 있다.
SQL> SELECT empno,
2 SUBSTR((select b.ename from bonus b where e.ename = b.ename),1,3)
3 AS ename, sal
4 from emp;
SELECT 리스트에서 스칼라 서브 질의 사용하기
Oracle 9i에서부터 SELECT 절에 스칼라 서브질의를 지원한다.
예를 들어, EMP 테이블과 DEPT 테이블에 대해서 ename, empno, dname을 SELECT절에서 스칼라 서브질의를 사용하여 얻는 SQL문과 결과는 다음과 같다.
SQL> SELECT e.ename, e.empno,
2 (SELECT d.dname FROM dept d
3 deptno = e.deptno) AS dname
4 FROM emp e;
이것은 우리 일반적으로 작성하는 EMP 테이블과 DEPT 테이블의 죠인을 죠인하는 다음의 문장과 동일한 결과를 얻게 된다.
SQL> SELECT e.ename, e.empno, d.dname
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno;
WHERE 절에서 스칼라 서블 질의 사용하기
Oracle 9i의 SQL에서는 또한 WHERE 절에서 스칼라 서브 질의를 사용할 수 있다.
SQL> SELECT e.ename, e.empno, e.deptno
2 FROM emp e
3 WHERE (( SELECT b.ename FROM bonus b WHERE b.ename = e.ename)
3 IN (SELECT ename FROM emp));
ORDER BY절에서 스칼라 서브 질의 사용하기
Oracle 9i의 SQL에서는 정렬을 명시하는 ORDER BY절에서 스칼라 서브질의를 사용할 수 있다.
아래의 예는 EMP테이블에서 선택한 결과를 DEPT 테이블의 dname으로 정렬하는 예를 나타낸다.
SQL> SELECT e.ename, e.empno
2 FROM emp e
3 ORDER BY (SELECT d.dname FROM dept d WHERE e.deptno = d.deptno);
명시적 기본값(Explict Defaults)에 대한 개요
명시적 기본값을 사용하는 것은 컬럼 기본 값을 요구하는 곳에서 DEFAULT 를 사용할 수 있는 것으로 SQL 1999에 순응한다. DEFAULT가 사용되는 두 가지 유형은 다음과 같다.
1. 삽입문장(INSERT )
2. 갱신문장(UPDATE)
3. 바인드 변수
명시적 기본값을 사용하는 예는 아래와 같다.
1. INSERT INTO emp(empno, ename, deptno) VALUES (1000,’Mike’, DEFAULT);
2. UPDATE emp SET deptno = DEFAULT WHERE ename = ‘Mike’;
3. INSERT INTO emp(empno, ename, deptno) VALUES (1000,’Dura’,:deptno_var);
만약 테이블을 생성할 때 컬럼의 DEFAULT 값을 설정하지 않았다면 널(NULL)이 들어가게 된다.
명시적 기본 값의 장접은 좀 더 나은 데이터의 무결성을 제공하며, 응용프그램을 작성할 때 “hard coding”을 피할 수 있다. 또한 사용자에게 보다 친숙하고 유연한 인터페이스를 제공한다.
요약
Oracle 9i는 1999년에 발표된 SQL 표준 III에 순응하는 SQL을 지원함으로써 응용 프로그램을 작성하는데 있어서 데이터의 관리 측면과 개발 측면에서 유연성과 개발 속도를 향상시켜준다.
