오라클에서 컬럼을 변경 할 경우 ALTER TABLE를 사용하며, 컬럼의 데이터 타입, 길이를 수정할 때는 MODIFY, 컬럼명을 수정할 때는 RENAME COLUMN을 사용하면 된다. 컬럼 데이터 타입 변경 --데이터 타입 변경 : NUMBER(4) -> VARCHAR2(4) ALTER TABLE emp MODIFY editid VARCHAR2(4); ALTER TABLE [테이블명] MODIFY [컬럼명] [새로운 데이터타입(길이)] 데이터 타입을 변경해야 하는 경우 해당 컬럼의 값을 모두 지워야 변경이 가능하다. 컬럼 길이(크기) 변경 --데이터 길이(크기) 변경 : VARCHAR2(4) -> VARCHAR2(5) ALTER TABLE emp MODIFY editid VARCHAR2(5); ALTE..
오라클 테이블에 컬럼을 추가하기 위해서는 ALTER TABLE을 사용한다. 컬럼을 추가하면 테이블 컬럼의 마지막 순서에 추가가 되며, 컬럼 순서의 중간에 컬럼을 추가할 수는 없다. 오라클 12c 부터는 조금 쉽게 컬럼의 순서를 변경할 수 있도록 기능이 추가 되었으며, 이전 버전에서 컬럼 순서를 변경하기 위해서는 테이블을 재생성 해야 한다. 컬럼 추가, 삭제 --컬럼 추가 1 ALTER TABLE emp ADD email VARCHAR(25); --컬럼 추가 2 (기본 값, NOT NULL 지정) ALTER TABLE emp ADD email VARCHAR(25) DEFAULT 'test@test.com' NOT NULL; --컬럼 삭제 ALTER TABLE emp DROP COLUMN email; 컬럼 ..
오라클에서 테이블을 만들기 위해서는 CREATE TABLE을 사용하여 컬럼명, 데이터 타입을 정의하여 테이블을 생성하면 된다. 아래는 EMP(사원) 테이블을 만드는 예제이다. ① 테이블 명, ② 컬럼 명 - 테이블, 컬럼 명의 길이는 30byte 문자 (Oracle 12c R2 부터는 128byte) - 문자(영문, 한글), 숫자, 특수문자(_, $, #)만 가능 - 반드시 문자로 시작 ③ 데이터 타입 - NUMBER(4) : 4자리의 가변 길이 정수 - NUMBER(7,2) : 7자리의 가변 길이 정수와 2자리의 가변길이 소수 - VARCHAR2(10) : 10byte의 가변 길이 문자 ④ 기본 값 - 테이블에 값이 입력(INSERT) 될 때 값이 없으면 기본으로 생성되는 값 - DEFAULT [값(문..
오라클에서 AVG 함수를 사용하면 평균을 쉽게 구할 수 있다. 그러나 NULL 값을 처리하지 않으면 평균 값에 오류가 발생할 수 있으니 주의해야 한다. SELECT AVG(sal) FROM emp 평균을 구할 컬럼의 값에 NULL 값이 존재한다면 꼭 NVL 함수를 사용하여 NULL 값을 치환해야 한다. NULL 값을 치환하지 않으면 총합계÷총건수와 다른 결과가 조회된다. SELECT AVG(NVL(comm, 0)) AS avg1 , SUM(comm) / COUNT(*) AS avg2 FROM emp 오라클의 집계 함수는 NULL 값을 집계에서 제외하는 특성이 있다. AVG 함수는 SUM(comm) / COUNT(*) 와 동일한 기능을 수행한다. 급여(sal) 컬럼은 NULL 값이 없기 때문에 정상적으로 ..
오라클 쿼리를 작성하다 보면 수치값에 소수점이 존재하는지 또는 정수는 버리고 소수점 이하의 값만 잘라서 사용해야 할 때가 있다. 위의 상황을 한 번에 해결할 수 있는 함수가 없기 때문에 직접 연산을 해야 한다. 소수점 체크 - "컬럼" TRUNC("컬럼") - INSTR("컬럼", ".") > 0 소수점 이하 값 - "컬럼" - TRUNC("컬럼") 소수점 체크 방법 TRUNC( ) 함수를 이용하는 방법 수치 값에서 TRUNC 함수를 사용하여 소수점 이하를 버리고 동일한 값이면 소수점이 없다 WITH temp AS ( SELECT 1.25 AS num1 , 1.0 AS num2 , 0.25 AS num3 , 1 AS num4 FROM dual ) SELECT num1 --1.25 , num2 --1.0 ..
오라클에서 if 문과 비슷한 기능을 하는 DECODE 함수가 있다. 그러나 DECODE 함수는 조건이 많아지면 가독성이 떨어지고 복잡해지며, 가장 큰 문제는 오라클 SQL에서만 사용할 수 있는 비표준 함수이다. 오라클에서 DECODE 함수를 대체할 수 있는 기능이 CASE 표현식이며 가독성이 좋고 더 많은 기능을 제공한다. 조건이 복잡한 경우 DECODE 함수 보다 CASE 표현식을 사용할 것을 권장한다. If 문 방식 SELECT ename , deptno , CASE WHEN deptno = '10' THEN 'New York' WHEN deptno = '20' THEN 'Dallas' ELSE 'Unknown' END AS loc_name FROM emp WHERE job = 'MANAGER' CA..
오라클 9i 버전부터 ANSI JOIN을 지원하기 시작했다. 그렇지만 아직까지 오라클 데이터베이스를 사용하는 많은 시스템에서 ANSI JOIN을 사용하지 않고 오라클 방식의 조인을 사용하여 쿼리문을 작성하고 있다. ANSI JOIN에 익숙하지 않으면 쿼리문을 작성할 때 헷갈리는 부분이 많이 발생한다. 2개의 테이블을 조인하는 예제는 많지만, 3개 이상의 테이블을 조인하는 예제가 많이 없어서 간단히 정리한 내용이다. SELECT a.empno , a.ename , a.deptno , b.dname , b.locno , c.lname FROM emp a INNER JOIN dept b ON a.deptno = b.deptno INNER JOIN loc c ON b.locno = c.locno WHERE a...
오라클 버전이 업그레이드 되면서 옵티마이저의 성능도 많이 향상 되어 쿼리를 실행하면 최적의 인덱스를 선택하여 쿼리를 실행한다. 그러나 옵티마이저가 엉뚱한 인덱스를 선택하여 쿼리가 느려지는 경우가 있다. 실무에서 쿼리문를 작성하다보면 "인덱스가 안탄다", "인덱스를 태워야한다" 와 같은 얘기를 많이 듣는다. 아래는 어떻게 인덱스를 태워야하는지 간단히 정리한 내용이다. SELECT절 첫 줄에 힌트 주석(/*+ */)을 작성하여 적절한 인덱스를 부여하면 된다. 주석에 꼭 "+"를 붙여야 힌트절이 실행되며 "+"가 없으면 일반 주석으로 간주하고 아무런 이벤트가 없다. SELECT /*+ INDEX(a EMP_IDX02) */ a.empno , a.ename , a.hiredate FROM emp a 인덱스 힌..
오라클 쿼리에서 숫자 값을 정렬(ORDER BY) 하였는데 뒤죽박죽 순서가 섞여서 조회되는 경우가 있다. 이런 경우는 대부분 컬럼 타입이 문자형(VARCHAR)인 경우가 많다. 숫자형 문자 컬럼인 경우 컬럼의 값을 TO_NUMBER 함수를 사용하여 숫자 타입으로 변경하거나, LPAD 함수를 사용하여 숫자 앞에 컬럼의 길이만큼 "0"을 붙여서 조회하면 된다. TO_NUMBER( ) 함수를 사용하는 방법 (컬럼의 값이 모두 숫자인 경우 사용) SELECT * FROM dept ORDER BY TO_NUMBER(dept_id) LPAD( ) 함수를 사용하는 방법 (컬럼의 값이 숫자 + 문자인 경우 사용) SELECT * FROM dept ORDER BY LPAD(dept_id, 3, '0') - 컬럼의 길이만..
DUAL 테이블 사용 방법 오라클에서 쿼리문을 작성시 테이블이 없이 데이터를 만들고 싶을때가 있다. MS-SQL이나 MySQL에서는 FROM절 없이 SELECT 절만 사용해서 쿼리를 실행할 수 있다. 오라클은 FROM절이 없을경우 쿼리문이 실행되지 않는다. 그래서 FROM절에 사용가능한 가상테이블 DUAL 이라는 DUMMY 테이블을 제공하고 있다. SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM dual 위의 쿼리를 실행하면 테이블이 없어도 현재의 날짜를 가져올 수 있다. SELECT 7788 empno , 'SCOTT' ename , 'ANALYST' job FROM dual 여러개의 컬럼을 나열하여 결과를 얻을 수 있다. 활용 예제 1. 시스템의 현재 날짜를 얻어온다 2...