달에 앉아있는 서비

Oracle (10) - WITH 절 사용방법

SQL을 작성하다 보면 복잡한 서브쿼리를 여러 번 사용해야 하는 경우가 있습니다. 이 경우 가독성이 떨어지고, 쿼리에 불필요한 연산이 많아져 쿼리 성능도 저하될 수 있습니다. 이를 개선할 수 있는 하나가 WITH 절입니다.오라클에서는 9i 버전부터 지원하며 12c 버전부터는 프로시저에서도 사용할 수 있게 되었습니다. WITH 사용법해당 예제는 학생들의 점수를 합산하여 조회하는 예시입니다.WITH SCORE_ALL_TB AS( SELECT STUDENT_NO AS STUDENT_NO , SUM(SCORE) AS SCORE_ALL FROM STUDENT_SCORE GROUP BY STUDENT_NO)SELECT A.STUDENT_NO , B.NAME , A.SCORE_AL..

회사 내 업무를 줄이기 위해 자동화 작업을 다수 진행하고 있습니다. 이 중에서 동기화 및 일간 백업 등 스케줄러가 필요한 작업이 있습니다. 예를 들어 매일 반복되는 백업, 로그 정리, 동기화 작업등 이런 작업을 수동으로 한다면 많은 시간이 소요됩니다. 과거에는 가로등을 일일이 켜야 했지만, 전기 시스템의 발전으로 자동화되었습니다. 이를 DB 작업도 오라클 스케줄러를 통해 자동화할 수 있습니다. 스케줄러(scheluler)는 무엇인가?스케줄러(Scheduler)는 사전적인 의미로 일정 관리 및 작업을 계획하는데 조정되고 사용되는 시스템이나 도구를 뜻합니다. 스케줄러는 주로 특정 시간이나 조건에 따라 작업을 자동으로 실행하도록 설정할 수 있으며, 이러한 작업 관리의 자동화를 통하여 작업의 효율성을 향상합니..

오라클을 사용하다 보면 여러 행의 데이터 처리해야 하는 경우가 있습니다.  커서를 활용하면 효율적으로 데이터를 다룰 수 있습니다. 커서는 데이터를 반환하는 것뿐만이 아니라 데이터를 한 행씩 읽으면서 특정 로직을 적용하거나 조건에 맞게 가공할 수 있습니다. 프로시저나 트랜잭션 작업에서 데이터를 유연하게 처리할 수 있습니다. 커서(Cursor)커서는 SQL의 데이터 셋을 순차적으로 접근할 수 있는 메모리 영역입니다. 이를 이용하여 쿼리 결과를 한 행씩 처리할 수 있도록 합니다. 쉽게 말하면 C에서 포인터와 같은 기능을 합니다. 이를 활용하면 다음과 같은 이점을 얻을 수 있습니다.다중 행 처리: 여러 행의 데이터를 한 번에 여러 행을 반환하거나 각 행을 개별적으로 처리할 수 있습니다.복잡한 로직 구현: 프로시..

SQL을 작성하여 어떤 데이터를 만들기 위해 많은 JOIN 및 서브쿼리를 하게 되는 경우가 있습니다. 해당 과정을 반복하다 보면 쿼리 실행시 성능 문제가 발생할 수 있습니다. 특히 인덱스를 타지 못하고 FULL SCAN 하는 경우 성능이 크게 떨어집니다. 이러한 문제를 해결하기 위해 EXPLAIN PLAN을 이용하여 쿼리의 실행 계획을 확인하고, 인덱스를 잘 활용하도록 최적화하는 방법을 알아보겠습니다. EXPLAIN PLAN-- 실행 계획을 생성EXPLAIN PLAN FORSELECT * FROM TEST WHERE MAJOR = '국문학과';-- 생성된 실행 계획 확인SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);우선 EXPLAIN PLAN은 오라클에서 제공하는 기능으로, ..

회사에서 업무를 하던 중 쿼리를 만들다 다소 신기한 방식으로 해결하였습니다.insert에서 update로 변경하는 것만으로 에러가 해결되어 공유하려고 합니다. 한줄요약 : INSERT SELECT 구문에서 실행이 안 되는 경우 태그를 insert에서 update로 바꾸어보자 ORA-00933ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다해당 오류는 SQL 구문 작성 오류 시 문법 오류로 인한 것입니다. 오라클에서 필요한 명령어 쉼표, AND 등 명령어가 빠져 있거나 오타가 난 경우 발생할 수 있습니다. Mybatis에서 발견한 오류IDE에서 쿼리로 작성 할 때 실행시 문제가 없었으나 Mybatis에서 쿼리를 동일한 쿼리를 실행할 때  ORA-00933에러가 계속 발생하였습니다. 아래와 같..

오라클을 사용하다 보면 아래와 같은 오류를 종종 보게 됩니다. ORA-01427 : 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다 해당 오류는 주로 서브쿼리에서 1건을 초과한 레코드가 나올때 발생하는 에러입니다. 예시 데이터 STUDENT_NO (PK) NAME DEPT_NO 20240304 강감찬 100 20240305 이순신 100 20240306 이산 200 20240307 홍길동 300 문제가 있는 쿼리문을 만들어 보겠습니다. SELECT STUDENT_NO, DEPT_NO FROM EXAMPLE_TABLE T1 WHERE STUDENT_NO = ( SELECT STUDENT_NO FROM EXAMPLE_TABLE T2 WHERE DEPT_NO = 100 ) 해당 쿼리에는 문제가 있습니다...

오라클을 사용하 다보면 null에 따른 이벤트 처리를 많이 하게 됩니다. 저는 그동안 CASE 문을 이용하여 사용하였으나 간단하게 함수로 처리하는 방법이 있어 소개하려고 합니다. 직관적으로 알 수 있도록 CASE 문을 사용하는 경우 어떤 형식으로 작성할 수 있는지 추가로 작성하겠습니다. 예제 테이블 데이터 GRADE NAME PRICE SCORE 1 강감찬 3000 300 2 홍길동 5000 500 3 황진이 600 4 장영실 700 5 이순신 NVL SELECT NAME , CASE WHEN PRICE IS NULL THEN SCORE ELSE PRICE END AS NVL_EX FROM EX_TABLE / SELECT NAME, NVL(PRICE,SCORE) AS NVL_EX -- PRICE값이 있는..

쿼리를 작업할 때 성적 및 순위 처리할 일들이 있습니다. 해당 데이터를 RANK 함수를 이용하여 순위처리 하는 방법을 정리하려고 합니다. 예시 테이블 STNT_NO GRADE KOREAN MATH ENGLISH TOTAL 2017111 3 100 90 80 270 2018112 2 90 90 90 270 2019113 3 50 50 60 160 2020114 1 60 70 80 210 RANK, DENSE_RANK, ROW_NUMBER SELECT STNT_NO , TOTAL , RANK() OVER (ORDER BY TOTAL DESC) AS RANK , DENSE_RANK() OVER (ORDER BY TOTAL DESC) AS DENSE_RANK , ROW_NUMBER() OVER (ORDER BY..

Oracle을 다루다 보면 데이터를 잘못 DELETE나 UPDATE 하는 경우가 있습니다. 그런 경우 ROLLBACK을 하면 해결할 수 있습니다. 하지만 COMMIT을 하면 어떻게 될까요? 이미 데이터가 적용되어 일반적인 방법으로는 복구를  못합니다. 하지만 Oracle에는 COMMIT를 하였을 때 임시로 데이터가 저장하게 되어있고, TIMESTAMP를 이용하여 데이터를 복구할수 있습니다. TIMESTAMP구문은 아래와 같습니다.SELECT [COLUMN / *] -- 칼럼FROM [table] -- 대상 테이블AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '[NUMBER]' [SECOND/MINUTE/HOUR/DAY]);[WHERE]-- [NUMBER]은 되돌릴 기준 초, 분, 시..

우리가 A라는 데이터를 만들 때 데이터가 있고 없는 경우에 따라 나누는 경우가 있습니다. PL/SQL을 이용하여 프로시저를 만드는 방법도 있지만 MERGE INTO라는 구문을 이용하면 쉽게 만들 수 있습니다. 예제 테이블 CREATE TABLE T1 ( STU_NO VARCHAR2(10) PRIMARY KEY, --학번 STU_NAME VARCHAR2(10) NOT NULL, --이름 SCR NUMBER(5) --점수 ); CREATE TABLE T2 ( STU_NO VARCHAR2(10) PRIMARY KEY, --학번 STU_GRADE VARCHAR2(2) NOT NULL, --학년 STU_GEND VARCHAR2(2), --성별 SCR NUMBER(5) --점수 ); CREATE TABLE T3 (..