프로그램을 짜다보면 특정 조건에 맞을때 처리하는 조건문이 굉장히 많이 사용됩니다. 오라클에서 지원하는 PL/SQL에도 이 조건문이 존재합니다. 조건문 IF문 --사용법 IF 조건 THEN 처리문 ELSIF 조건2 THEN 처리문 ...... ELSE 처리문 END IF; IF문은 조건값이 참이면 해당 조건의 처리문장이 실행됩니다. 사용법은 위와같으며 다른 언어와 비교하여 전체적인 구조는 다르지 않으나 3가지가 다릅니다. 조건 다음에 THEN을 붙인다는것이 다르고, 여러개의 조건을 붙일 경우 다른 언어에서는 ELSE IF라고 많이 하지만 PL/SQL에서는 ELSIF 라는 명령어를 사용합니다. 그리고 마지막에는 IF를 끝낸다는 END IF를 붙인다는 점이 다릅니다. DECLARE SCORE NUMBER :=..
PL / SQL이란? PL/SQL은 상용 관계형 데이터베이스 시스템인 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나입니다. 오라클에서 변수를 선언 후 사용하시려면 이 PL/SQL을 사용하시면 됩니다. 변수 및 상수 선언 --변수 선언 변수명 데이터타입 : = 초기값; EX_NUM NUMBER := 1; EX_STR VARCHAR2(10) := 'STR1'; --상수 선언 상수명 CONSTRANT 데이터타입 : = 상수값; EX_NUM CONSTANT NUMBER := 1; 변수 선언법은 위와같습니다. PL/SQL에서는 일반적인 프로그래밍 언어에서 사용하는 = 이 아니라 :=입니다. 특정 변수에 :=을 기준으로 오른쪽 값을 왼쪽에 할당한다는 뜻을 가지고 있습니다...
SELECT문을 실행하면 데이터가 조회됩니다. 만약 이렇게 조회한 여러개의 SELECT문을 가지고 데이터를 가공하고 싶다면 집합 연산자를 사용하시면 됩니다. 집합 연산자는 SELECT문 활용하여 조회한 데이터를 연산하는 연산자이며 우리가 어릴적 배웠던 그 집합과 동일합니다. 집합 연산자에는 UNION, UNION ALL, INTERSECT, MINUS가 있는데 이번 포스팅에서는 첫번째 SELECT문을 기준으로 두번째 SELECT문과의 공통된 데이터를 제거한 후 반환하는 MINUS에 대해 포스팅 해보도록 하겠습니다. 차집합 MINUS 첫번째 SELECT문에서 두번째 SELECT문을 빼고 싶다면 MINUS를 사용하면 됩니다. MINUS를 사용하면 첫번째 SELECT문을 기준으로 두번째 SELECT문과의 공통..
SELECT문을 실행하면 데이터가 조회됩니다. 만약 이렇게 조회한 여러개의 SELECT문을 가지고 데이터를 가공하고 싶다면 집합연산자를 사용하시면 됩니다. 집합연산자는 SELECT문 활용하여 조회한 데이터를 연산하는 연산자이며 우리가 어릴적 배웠던 그 집합과 동일합니다. 집합 연산자에는 UNION, UNION ALL, INTERSECT, MINUS가 있는데 이번 포스팅에서는 SELECT문들의 중복된 데이터만 추출해주는 INTERSECT에 대해 알아보도록 하겠습니다. 교집합 INTERSECT 두개의 SELECT문의 중복값을 출력하고 싶은 경우에는 INTERSECT를 사용하면 됩니다. INTERSECT를 사용하면 두개의 데이터 집합에서 공통된 항목만 추출해냅니다. 위의 그림과 같은 데이터를 가진 두개의 테이..
SELECT문을 실행하면 데이터가 조회됩니다. 만약 이렇게 조회한 여러개의 SELECT문을 가지고 데이터를 가공하고 싶다면 집합연산자를 사용하시면 됩니다. 집합연산자는 SELECT문 활용하여 조회한 데이터를 연산하는 연산자이며 우리가 어릴적 배웠던 그 집합과 동일합니다. 집합 연산자에는 UNION, UNION ALL, INTERSECT, MINUS가 있는데 이번 포스팅에서는 복수의SELECT문을 합쳐주는 UNION, UNION ALL에 대해 포스팅해보려 합니다. 합집합 UNION (중복제거) 두개의 SELECT문을 서로 합치고 싶은데 그 중에서도 두개의 조회문의 중복되는 데이터를 한번만 출력하고 싶다면 UNION을 사용하시면 됩니다. UNION은 합집합을 의미하며 사용하게되면 두개의 SELECT문을 서로..
WITH절이란 WITH절은 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리라고 생각하시면 됩니다. 임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷한데 차이점이 있다면 VIEW는 한번 만들어놓으면 DROP할때까지 없어지지 않지만 WITH절의 같은경우 한번 실행할 쿼리문내에 정의되어 있을경우, 그 쿼리문안에서만 실행된다는 차이점이 있습니다. WITH절을 사용하는 이유 WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립..
그룹별 순번매기기 ROW_NUMBER() 카테고리(CATEGORY_ID)별 저렴한 금액(STANDARD_COST)순으로 순번 매기기 SELECT A.CATEGORY_ID, B.CATEGORY_NAME, A.STANDARD_COST, ROW_NUMBER() OVER (PARTITION BY A.CATEGORY_ID ORDER BY A.CATEGORY_ID, A.STANDARD_COST) AS NUM --그룹별 순번 FROM PRODUCTS A INNER JOIN PRODUCT_CATEGORIES B ON A.CATEGORY_ID = B.CATEGORY_ID 카테고리별 가격의 순번을 매기는 쿼리입니다. CPU카테고리에서는 70번까지의 순번이 값이 작은 순에서 큰 순으로 순번이 매겨졌고 그 뒤로 두번째 카테고..
오라클 순번매기기 ROWNUM SELECT ROWNUM, A.PRODUCT_NAME, A.STANDARD_COST FROM PRODUCTS A ROWNUM이라는 시스템 함수를 사용하면 위와 같이 라인별 순번을 가져올 수 있습니다. 하지만 위와 같이 아무런 정렬이 되지 않은 데이터에 순번을 매긴다면 그 순번은 상당히 의미 없는 숫자가 되어 버릴 것입니다. 정렬된 데이터에 순번매기기 위의 예제와 같이 순번을 매기는 시스템 함수는 ROWNUM이고 정렬은 ORDER BY를 사용하시면 되는데 ROWNUM와 ORDER BY를 같이 사용할 경우 매겨놓은 순번이 섞여버리는 현상이 발생합니다. SELECT ROWNUM,--순번매기기 A.PRODUCT_NAME, A.STANDARD_COST FROM PRODUCTS A O..
COALESCE 함수는 인자값으로 들어오는 표현식에서 NULL이 아닌 첫번째 표현식을 리턴해주는 함수입니다. COALESCE 함수 사용법 SELECT COALESCE('A','B','C'), --결과값 : A COALESCE(NULL,'B','C'), --결과값 : B COALESCE(NULL,NULL,'C'), --결과값 : C COALESCE(NULL,NULL,NULL) --결과값 : NULL FROM DUAL COALESCE 작동원리는 NVL, NVL2함수와 비슷합니다. 인자값으로 들어온 값 중에서 NULL값이 아닌 첫번째 값을 반환합니다. 만약 NULL값만 들어왔다면 NULL값을 반환합니다. 파라미터의 타입은 일치하여야 합니다. SELECT COALESCE(NULL,'A',1) FROM DUAL ..
오라클에서 날짜를 비교할 때 주의사항이 있습니다. DATE형/ 문자형끼리 비교는 문제가 없지만 DATE형과 문자형의 비교일 경우 서로 데이터 타입을 맞춰줘야 합니다. 오라클에서 주로 날짜 포맷을 맞추는 TO_CHAR 함수의 리턴 값이 문자형이므로 이런 실수가 자주 나옵니다. 날짜 비교 WITH EXAMPLE AS( SELECT TO_CHAR(SYSDATE,'YY/MM/DD') AS DAY1, --19/12/01' SYSDATE AS DAY2 --19/12/01' FROM DUAL ) SELECT '두 날짜는 서로 같습니다.' FROM EXAMPLE WHERE DAY1 = DAY2 위 쿼리를 조회해보면 데이터가 조회되지 않습니다. 데이터가 조회되지 않은 이유는 바로 서로의 데이터 타입이 다르기 때문입니다...
날짜 계산 (연산자) SELECT SYSDATE + 100, --현재날짜 +100일 SYSDATE - 100, --현재날짜 -100일 SYSTIMESTAMP + 100, --현재날짜 +100일 SYSTIMESTAMP - 100 --현재날짜 -100일 FROM DUAL 위와같이 + , - 연산자를 활용하여 간단한 날짜 계산이 가능합니다. 날짜 계산 (함수) SELECT ADD_MONTHS(SYSDATE,6), --현재시간 + 6개월 뒤 LAST_DAY(SYSDATE), --해당월 마지막 일자 계산 NEXT_DAY(SYSDATE,'일요일'), --다음 주 일요일 계산 MONTHS_BETWEEN(SYSDATE, SYSDATE-100) --DATE1과 DATE2의 개월 수 반환 FROM DUAL ADD_MONT..
쿼리문을 짜다보면 원하는 날짜와 시간의 형태를 뽑아내고 싶을 경우가 많습니다. 오라클에서 날짜 포맷의 변환은 TO_CHAR함수를 활용하여 변경하실 수 있습니다. 다양한 날짜 / 시간 포맷 SELECT --날짜 포맷 TO_CHAR(SYSDATE,'YYYYMMDD'),--20191130 TO_CHAR(SYSDATE,'YYYY-MM-DD'),--2019-11-30 TO_CHAR(SYSDATE,'YYYY/MM/DD'),--2019/11/30 TO_CHAR(SYSDATE,'YYYY'),--2019 TO_CHAR(SYSDATE,'YY'),--19 TO_CHAR(SYSDATE,'MM'),--11 TO_CHAR(SYSDATE,'MON'),--11월 TO_CHAR(SYSDATE,'D'),--7 (주중의 일을 1~7로 표..
오라클에서 현재날짜 / 현재시간을 나타내주는 시스템함수는 SYSDATE, SYSTIMESTAMP함수입니다. 현재날짜 구하기 (SYSDATE, SYSTIMESTAMP) SELECT SYSDATE, SYSTIMESTAMP FROM DUAL 오라클에서 SYSDATE,와 SYSTIMESTAMP를 입력하기만 하면 위와같은 형태로 날짜가 출력됩니다. 위 데이터 포맷은 기본데이터 포맷으로 리턴한 값이므로 환경에따라 다를 수 있습니다. SYSDATE와 SYSTIMESTAMP함수는 리턴하는 타입이 서로 다릅니다. SYSDATE DATE형태로 리턴하고 SYSTIMESTAMP함수는 TIMESTAMP타입으로 리턴됩니다. 두 타입간의 차이점은 아래 표와 같습니다. 데이터타입 설명 DATE BC 4712년 1월 1일부터 9999..