오라클 교육 5일차

오라클 교육의 마지막인날로 PL/SQL을 비롯한 여러 오라클 이론을 학습했습니다.

그룹함수

그룹함수의 종류	의미
COUNT	행의 객수 출력
MAX	NULL을 제외한 모든 행에서 최대 값
MIN	NULL을 제외한 모든 행에서 최소 값
SUM	NULL을 제외한 모든 행의 합
AVG	NULL을 제외한 모든 행의 평균 값
STDDEV	NULL을 제외한 모든 행의 표준편차
VARIANCE	NULL을 제외한 모든 행의 분산 값
GROUPING	해당 컬럼이 그룹에 사용되는지 여부를 1 또는 0으로 변환
GROUPING SETS	한 번의 질의로 여러 개의 그룹화 가능

여기서 GROUPING에서 특이사항은 0이 반환되면 GROUP BY를 사용했다는 결과 값이고 반대로 1인 경우에는 사용하지 않았다는 결과 값을 조회하는 것입니다.

위에 의미를 보다 싶이 NULL 값에 대한 사전 처리가 되어 있지 않으면 원하는 값이 나오지 않을 수 있습니다.

집계함수

GROUP BY

• 특정 컬럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

• 규칙

  • 그룹핑 전에 WHERE 절을 사용하여 그룹 대상 집합을 먼저 선택합니다.
  • GROUP BY 절에는 반드시 컬럼 이름을 포함해야 하며 컬럼 별명은 사용할 수 없습니다.
  • 그룹별 출력 순서는 오름차순으로 정렬합니다.
  • SELECT 절에서 나열된 컬럼 이름이나 표현식은 GROUP BY 절에서 반드시 명시합니다.
  • GROUP BY 절에서 명시된 컬럼 이름은 SELECT 절에서 명시하지 않아도 됩니다.

ROLLUP 연산자

• GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자

CUBE 연산자

• ROLLUP에 의한 그룹 결과와 GROUP BY 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자

집계항목의 갯수에 대해서

• SELECT 부분의 집계 항목의 갯수와 GROUP BY의 항목의 갯수는 반드시 같아야 합니다.
• 위의 항목(SELECT 부분)이 3개가 나온다면 아래 항목(GROUP BY)에서도 반드시 3개가 나오라는 것은 아닙니다.
• 2번째와 같은 경우에 문법 오류는 아니지만 실제적으로 그렇게 사용하지 않습니다.

효율적으로 집계함수 사용하는 방법

• 집계 항목은 SELECT절과 GROUP BY절의 갯수를 서로 맞춰줍니다.(필수는 아님)
• 아래 GROUP BY에서 3개가 나왔다고 반드시 위의 조회 갯수가 3개는 아닙니다.

추가적으로 집계항목에 대해서

• ROLLUP은 합계가 하단에 출력이 되는데 GROUPPING을 사용하면 합계가 상단에 출력됩니다.

조인

카티션 곱

• 두 개 이상의 테이블에 대해 연결 가능한 모든 행들 결합
• WHERE 절에서 조인 조건절을 생략하거나 잘못 설정한 경우
• 대용량 테이블에서 발생할 경우 SQL명령문의 처리속도 저하
• 개발자가 시뮬레이션을 위한 대용향의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능
• 오라클 9i 이후 버전에서 FROM절에 CROSS JOIN 키워드 사용

카티션 곱은 다른 말로 클로스 조인이라고도 합니다. 카티션 곱은 OUT JOIN 했을 때 나오는 경우의 수를 의미합니다.

PL/SQL

• PL/SQL이란 SQL로 Programing이 가능하도록 한 언어입니다.
• 대화식 프로그램입니다.
• Stored Function : 결과를 중시하고 반드시 결과값은 한 개가 나옵니다.
• Stored Procdure : Batch Job을 수행하기 위한 용도로 사용됩니다.
• Package : 오라클 시스템에서 기본 제공하는 패키지입니다.
  사용자가 서로 연관된 프로시저나 함수들을 묶어서 생성시켜 놓은 것을 의미합니다.
• 같은 기능을 가진 프로시저나 함수를 만들어서 사용할 수가 있습니다.
• 결과 값을 보고자 할 경우에는 Function을 사용합니다.
• 결과 값과 상관없이 해당 과정에서 작업처리가 주목적인 경우는 프로시저로 작성합니다.

추가 사항

동적 바인딩을 통해 메모리 효율을 높입니다.

   TM_WORK_YY         CAL_BATCH_MAST.WORK_YY%TYPE;
   TM_WORK_MM         CAL_BATCH_MAST.WORK_MM%TYPE; 

이런식으로 %TYPE을 사용하여 변수타입을 선언합니다.

PL/SQL의 개념

Oracle에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장 언어입니다.
PL/SQL BLOCK 내에서 SQL의 DML문과 Query문, 절차형 언어 등을 사용하여 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션 언어입니다.

기타 이론

• 파티션 초기화

	alter table Cal_Dsum_Tran truncate partition (P_DT_20234Q); 

• DML 명령에 의한 데이터 삭제 : Data Clear & (Not) Memory Clear

		DELETE
          FROM cal_dsum_tran
         WHERE hpn_yy = '2023'
           AND hpn_mm IN ('04','05','06')
        ;


		COMMIT;

DML로 작업을 하게 되면 메모리 반환을 해주지 않으면 시간이 오래걸린다는 단점이 있으므로 쿼리문을 잘 짜는 방법은 DDL 명령어를 통해 만들어주면 됩니다.

※공부하고 있어 다소 틀린점이 있을 수 있습니다. 언제든지 말해주시면 수정하도록 하겠습니다.
※용어에 대해 조금 공부 더 해서 수정하겠습니다.