오라클 잡과 스케줄러 데이터베이스를 운영하다보면 하루에 한번씩 운영서버의 데이터를 개발서버에 데이터를 넣어주거나 하는 주기적인 작업을 처리할 때가 있습니다. 이렇게 일정한 주기의 작업을 잡(Job)이라고 합니다. 이러한 주기적인 잡을 수행하는 방법은 스케줄러를 활용하는 방법이 있습니다. 오라클 스케줄러 (DBMS_SCHEDULER) 기존에는 주기적인 작업을 잡 패키지(DBMS_JOB)라는 것을 활용하여 작업을 하였지만 다양한 문제점으로 인해 오라클 10g부터 새로운 스케줄러 패키지인 오라클 스케줄러 (DBMS_SCHEDULER)라는 패키지가 추가되었습니다. 최근에는 이 오라클 스케줄러라는 패키지를 통해서 주기적인 잡(Job)을 처리합니다. 오라클 스케줄링 사용법 잡 객체를 이용한 스케줄링 BEGIN DB..
계층형 쿼리란? 계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태를 말합니다. 예를 들자면 특정회사의 부서, 특정학교의 학과등이 있습니다. 계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말합니다. 오라클에서의 계층형 쿼리는 START WITH ... CONNECT BY 절로 생성할 수 있으며 계층형 정보를 표현하기 위한 목적으로 오라클 8부터 지원되었습니다. 수행순서 1. START WITH 절에 시작 조건을 찾습니다. 2. CONNECT BY 절에 연결조건을 찾습니다. 샘플데이터 생성 위와같은 구조를 이루도록 부서 테이블을 생성하도록 하겠습니다. CREATE TABLE DEP ( DEP_CD NUMBER NOT NULL, -- 부서코드 PARENT_CD NUMBER,..
아주 가끔 SELECT문의 행을 열로 변환해야 할 경우가 있습니다. 과거에는 행을 열로 바꿀때 CASE WHEN이나 DECODE, WITH절을 통해서 컬럼을 로우로 변환하였었는데 오라클 11g에서 새로나온 피벗테이블을 활용하면 과거에 사용하던 방법보다 훨씬 간단한 문장으로 행과 열을 전환 할 수 있습니다. 이번 포스팅에서는 PIVOT절을 활용하여 행을 열로 바꾸는 방법에 대해 알아보도록 하겠습니다. [Oracle] 오라클 열을 행으로 변환하기 (UNPIVOT) 샘플 데이터 SELECT * FROM KOR_LOAN_STATUS 위의 데이터는 대출년도, 지역, 대출종류, 대출금을 나타낸 데이터입니다. PIVOT의 로우와 컬럼을 바꿔주는 기능을 활용하여 위 데이터의 행과 열을 바꿔보도록 하겠습니다. PIVOT..
ROULLUP과 CUBE을 사용하면 그룹별 소계 및 합계를 출력할 수 있습니다. 그룹별 소계 및 합계를 구하려면 우선 그룹핑된 데이터가 필요합니다. 샘플 데이터 SELECT PERIOD, GUBUN, SUM(LOAN_JAN_AMT) FROM KOR_LOAN_STATUS GROUP BY PERIOD, GUBUN ORDER BY PERIOD 위의 데이터는 대출년도, 대출명으로 대출금액의 합계값을 나타낸 데이터입니다. 이 데이터를 바탕으로 소계와 합계를 구해보도록 하겠습니다. ROLLUP ROLLUP은 추가적인 집계 정보를 보여주는 기법입니다. ROLLUP절에는 그룹핑 된 컬럼을 나열 할 수 있으며 명시된 나열한 컬럼의수에 따라 레벨 별로 집계한 결과가 반환됩니다. 나열한 컬럼의 수가 N개이면 N+1레벨까지,..
동적쿼리란? 동적쿼리란 실행시에 쿼리문장이 만들어져 실행되는 쿼리문을 말합니다. 쿼리문이 변하냐 변하지 않느냐에따라 변하지 않으면 정적쿼리 변한다면 동적쿼리로 생각하시면 됩니다. 대부분 동적쿼리를 사용할때에는 텍스트문장으로 쿼리문을 가지고 있다가 실행할때마다 텍스트 쿼리문장을 바꿔서 실행하는 방식을 사용합니다. 동적쿼리 사용법 BEGIN EXECUTE IMMEDIATE '쿼리문 (선언한 변수활용)' END; 동적쿼리를 사용하는 방법은 DECLARE상에서 동적으로 받을 변수를 먼저 생성한 후 생성한 변수를 활용해서 쿼리문을 작성한 후 EXECUTE IMMEDIATE로 실행시켜주시면 됩니다. 동적쿼리 예제 CREATE OR REPLACE FUNCTION FUNC1( W_PRODUCT_ID IN NUMBER ..
패키지란? 패키지란 변수, 상수, 서브프로그램등의 항목을 묶어놓은 객체입니다. 여러가지 항목들을 모아 하나의 이름으로 묶어놓은 객체라고 생각하시면 됩니다. 패키지는 컴파일 과정을 거쳐 데이터베이스에 저장되며 다른 프로그램에서 패키지의 항목을 참조하고 실행할 수도 있습니다. 패키지를 사용하는 이유 프로그래밍을 하다보면 프로시저같은 서브 프로그램의 수가 기하급수적으로 많아지게 됩니다. 이렇게 항목들이 많아지게되면 관리하기가 힘들어지게되고 자신이 필요한 항목을 찾는것마저 어려워지는 상황이 옵니다. 이 상황을 방지하기 위해 여러가지 항목들을 좀 더 손쉽게 관리하자라는 요지에서 나온 것이 바로 패키지라는 개념입니다. 패키지 사용법 패키지는 선언부, 본문, 실행부로 이루어집니다. 선언부 CREATE OR REPLA..
커서란? 커서란 특정 SQL 문장을 처리한 결과를 담고있는 메모리 영역을 가리키는 일종의 포인터입니다. 대부분의 SQL문 결과 ROW는 여러개인데 커서를 사용하면 이 ROW에 순차적으로 접근이 가능합니다. 커서의 종류에는 묵시적 커서와 명시적 커서가 있습니다. 묵시적 커서는 오라클 내부에서 자동으로 생성되어 SQL문장이 실행될때마다 자동으로 만들어져 실행되는 커서이고, 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말합니다. 이번 포스팅에서는 사용자가 직접 만드는 명시적 커서를 만들어보고 사용하는 방법에대해 알아보도록 하겠습니다. 커서를 만드는 단계 커서를 만드는 단계는 크게 커서 선언 -> 커서 열기 -> 패치 단계에서 커서 사용 -> 커서 닫기 크게 이렇게 4가지로 분류할 수 있습니다. 커서 ..
프로그램을 개발할때면 다양한 경우의 수를 예상 해 예외처리를 해야합니다. 이번 포스팅에서는 오라클 PL/SQL 예외처리 방법에 대해 알아보도록 하겠습니다. 예외처리 종류 오라클 예외처리는 크게 시스템 예외와 사용자 정의 예외로 나뉩니다. 시스템 예외는 오라클에서 발생시키는 에러로 나누기 연산 시 제수를 0으로 나누면 에러가 발생하는 에러 같은 것처럼 오라클 내부에 미리 정의된 에러를 의미합니다. 사용자 정의 예외는 사용자가 직접 예외를 정의해서 사용하는것을 말하는데 저는 실제로 실무에서 써본적은 없습니다. 예외처리 방법 EXCEPTION WHEN 예외명 THEN 예외처리 --예외명을 알 경우 WHEN OTHERS THEN 예외처리 --예외명을 모를 경우 오라클에서 예외를 처리하는 문법은 위와같습니다. 첫..
프로시저란? 오라클에서의 프로시저는 PL/SQL을 통해 만들어집니다. 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있습니다. 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램입니다. 프로시저 생성 CREATE OR REPLACE PROCEDURE EX_PROC ( P_DEPARTMENT IN VARCHAR2, P_STUDENT_CNT IN NUMBER ) IS P_UNIVERSITY VARCHAR2(100) := '서울대학교'; BEGIN INSERT INTO UNIVERSITY1 (UNIVERSITY, DEPARTMENT, STUDENT_CNT) VALUES (P_UNIVE..
다른언어와 마찬가지로 PL/SQL에서도 반복문을 지원합니다. PL/SQL에서의 반복문 종류는 LOOP문, WHILE문, FOR문이 있습니다. LOOP문 --사용법 LOOP 처리문; EXIT[조건]; END LOOP; --예제 DECLARE NUM1 NUMBER :=1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(NUM1); --출력 NUM1 := NUM1+1; --NUM = NUM +1 EXIT WHEN NUM1 >10; --NUM1이 10보다 크면 LOOP종료 END LOOP; END; LOOP문 사용법은 위와같습니다. 반복문은 특정조건까지만 반복하며 로직을 수행하는데 LOOP의 반복문은 EXIT[WHEN 조건]; 이라는 구절에서 실행됩니다. 따라서 EXIT를 누락하면 해당 LOOP는 무..
프로그램을 짜다보면 특정 조건에 맞을때 처리하는 조건문이 굉장히 많이 사용됩니다. 오라클에서 지원하는 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문과의 공통..