3일차에는 사용자 함수 정의 + 시퀀스 학습 인덱스 설정에 대해 학습했습니다.
사용자 함수 정의
사용장의 함수 정의 사용법 예시는 아래와 같습니다.
CREATE OR REPLACE FUNCTION FUN_GET_SYSDATE -- 사용자 정의 함수 생성
RETURN VARCHAR2 -- 반드시 한 개의 리턴 값을 갖는다.
IS
v_SYSDATE VARCHAR2(20);
BEGIN
SELECT to_char(SYSDATE, 'yyyymmddhh24miss')
INTO v_SYSDATE
FROM DUAL
;
RETURN(v_SYSDATE);
EXCEPTION
WHEN OTHERS THEN
RETURN(NULL);
END;
/ -- / <= Run의 의미 위 처럼 함수의 예시를 만들어 준다음 아래와 같이 작성하여 사용할 수 있습니다.
SELECT 365 * 24 * 60 * 60 Year_To_Sec
, Sysdate AS 현재날자
, Fun_Get_Sysdate AS 현재날자_2 -- 사용자 함수를 테스트
--, ack_num_seq.nextval AS 순번_테스트
FROM Dual ;
위 코드를 살펴보면 DUAL이라고 있습니다. DUAL은 더미 컬럼 하나만 가진 테이블을 의미하며 SYS 사용자 안에 있습니다.
SYNONYM은 동의어(약속된 말)라고 해석되며 PUBLIIC SYNONYM은 공용 동의어로 모든 사용자가 사용할 수 있게 됩니다. DUAL 같은 경우에도 PUBLIC SYNONYM으로 만들기 때문에 모든 사용자가 그냥 DUAL만 작성해도 사용이 가능합니다. 하지만 권한을 부여해주어 SYS로 들어오는 것을 차단합니다.
CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;라고 작성하면 공용 동의어로 생성(SYS.DUAL을 DUAL로만 사용가능하게 해줌)하는 방법입니다.
GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;위 처럼 작성하면 권한을 부여하여 SYS로 들어오는 것을 차단합니다.
시퀀스 생성하는 방법
시퀀스(Sequence)를 생성하는 예시는 아래와 같습니다.
CREATE SEQUENCE HR.StudSeq -- Sequence명 : Stud_Seq : 역할은 학번을 자동으로 부여합니다.
START WITH 20001 -- 시작값은 20001, 종료값 : 99999
MAXVALUE 99999 -- 증가분 : 1
MINVALUE 20001
CYCLE -- 값의 범위는 : 20001~99999 사이의 값을 취합니다.
CACHE 20 -- 램에 20개의 번호를 저장할 수 있는 공간을 예약해둡니다.
ORDER -- 1/1000,000초 간격으로 번호 채번이 가능, 번호를 내어줄 때 순차적으로 내어줘라
NOKEEP -- 한번 내준 번호를 기억하지말고 최신번호로 기억해라
NOSCALE
GLOBAL;
seq명.nextval은 다음 번호를 부여하고 currval은 cpu에서 부여받은 번호를 주기 때문에 한 번호로 고정이 되어있습니다.
오라클에서는 INTO는 변수값으로 대체하는 것입니다.
C:\COSMOS\Oracle\admin\oraclejava\pfile
오라클 램 전용 영역의 램을 확인할 수 있습니다. 폴더 경로는 오라클이 설치된 경로를 따라가면 됩니다.
파라미터
Toad 사용시 오라클DB의 파라미터를 확인하는 방법은 아래와 같습니다.
Toad : Database Menu -> Admin -> Oracle Parameters 에서 확인
-
P Parameter 확인
오라클 처음 기동시 P Parameter을 읽어서 환경을 설정합니다.
파라미터가 바뀌면 반드시 서비스를 Restart 해야 적용이 됩니다. -
P Parameter를 조회하는 SQL문
Select *
From v$parameter -- init.ora에 있는 P Parameter setting사항을 조회
Where Name Like 'db%'
- SP Parameter을 조회하는 SQL문
서비스 운영중에 수정하여 적용할 수 있는 parameter을 SP Parameter라고 합니다.
DBA가 SYS사용자에게 명령을 사용하여 수정합니다.
Select *
From v$spparameter -- SP Parameter setting사항을 조회
Where Name Like 'db%' 테이블의 통계값을 모으는 방법
- Table Anayze : 자료사전에 필요한 테이블의 기본 통계값을 모으는 작업
- 테이블이 생성되고 나면 오라클의 자료사전에 있는 정보도 갱신시켜주어야 하는데 이 부분이 갱신이 되어있지가 않습니다.
- 오라클에서 사용하기 좋은 상태가 되게 하려면 오라클의 기본 통계값을 갱신시켜 줌으로써 속도도 빠르게 하고 최적의 성능을 낼 수 있도록 조정해야합니다.
- 툴마다 서로 다르고 작업권한도 DBA가 가지고 있기 때문에 기본적으로 DBA에 의뢰하여 수행하는 작업
BEGIN -- Table Analyze : 특정 테이블의 기본 통계값을 모으는 명령
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'HR',
tabname => 'cal_co_mast',
-- partname => '',
block_sample => FALSE,
granularity => 'ALL',
cascade => DBMS_STATS.AUTO_CASCADE,
estimate_percent => 20,
degree => 4 );
END;
BEGIN -- HR 사용자에 들어있는 모든 테이블의 통계값을 모으는 명령
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'HR',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
block_sample => FALSE,
granularity => 'ALL',
cascade => DBMS_STATS.AUTO_CASCADE,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => 4 );
END;
BEGIN -- SYS 사용자에 들어있는 모든 테이블의 통계값을 모으는 명령
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'SYS',
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
block_sample => FALSE,
granularity => 'ALL',
cascade => DBMS_STATS.AUTO_CASCADE,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => 4 );
END; 묵시적 데이터 타입 변환
-
데이터 타입의 변환
숫자나 날짜 타입을 문자와 함께 결합하거나 보고서 양식에 맞추기 위해 주로 사용됩니다. -
묵시적인 데이터 타입 변환
묵시적인 데이터 타입 변환은 정확한 연산을 위하여 오라클에서 데이터 타입을 내부적으로 자동 변환하는 경우를 말합니다.
| A의 데이터 타입 | B의 데이터 타입 | 변환결과 |
|---|---|---|
| NUMBER | VARCHAR2 | B가 NUMBER 타입으로 변환 |
| VARCHAR2 | NUMBER | A가 NUMBER 타입으로 변환 |
문자 타입의 숫자타입으로 변환은 문자열이 숫자로 구성되어 있을 경우만 가능합니다.
인덱스
View Point는 DB를 관찰했을 때 얻게 되는 모양을 View Point라고 합니다.
LOGGING은 주요 변동사항을 기록하는 설정으로 임시테이블을 생성할 때는 NO LOGGING을 옵션으로 주면 됩니다.
인덱스를 잡는 코드는 아래 예시와 같이 작성할 수 있습니다.
CREATE INDEX STUDENTT_INX07 ON STUDENT
(AUDIT_DATE)
LOGGING
TABLESPACE DW_TSI
하지만 FULL 스캔 방법이지만 더 적은 COST비용으로 쿼리문을 짜는 방법은 아래와 같습니다.
SELECT *
FROM STUDENT
WHERE GRADE IN ('1','2','3','4')
AND height BETWEEN 150 AND 170;튜닝
Oracle DB 에서 SQL문을 만들면서 빠르게 실행하기 위해서 우리는 튜닝(Tuning)을 합니다.
SQL튜닝을 하다하다 마지막으로 시도하는 것이 "병렬처리(Parallel Processing)" 입니다.
- HASH
Single Job MultiBlock Access <효율이 좋음> - Parallel
Multi Job Single Block Access <효율이 적음>
Multi Job Multi Block Access <매우 효율이 좋음>
Divide And Conquer(분할 정보의 원리) 항상 생각해야합니다.
함수
- 함수나 프로시져는 오라클에게 작업을 시키는 일괄작업(batch Job)의 일종
- 함수 : 반드시 한 개의 결과값을 가져온다. (결과를 중시 , Return A Value)
변수형에 따른 제공함수(시스템기본제공함수),
그룹계산을 위한함수(집계함수) 에는 Count(*) : 건수 , SUM : 합계 , AVG : 평균, MAX , MIN, STDDEV : 표준편차 , VARIANCE : 분산값 등 있다.
사용자정의함수 등이 있다.
함수는 SQL문장에서 직접 불러서 사용한다.
SELECT Fun_Get_Sysdate -- 함수는 이렇게 문장상에서 호출하여 사용
From Dual ;- 프로시져
결과보다는 과정을 중시한다. (Do Action )
따라서 결과값이 없을수도 한개가 있을 수도 또는 여러개가 있을 수가 있다.
Declare ~ Begin ~ End 사이에서 호출하여 사용한다.
Declare
Begin
P_Get_Sysdate();
End; - 업무의 특성에 따라 동일한 것을 함수 또는 프로시져로 선택하여 작성할 수가 있다.
함수의 개념
- SQL 함수 사용 경우
- 컬럼의 값이나 데이터 타입을 변경하는 경우
- 숫자 또는 날짜 데이터의 출력 형식을 변경하는 경우
- 하나 이상의 행에 대한 집계를 하는 경우
- SQL 함수의 유형
- 단일 행 함수 : 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수
- 복수 행 함수 : 조건에 따라 여러 행을 그룹화하여 그룹벼로 결과를 하나씩 반환하는 함수
단일 행 함수에는 형변환 함수가 있습니다.(TO_CHAR, TO_NUMER, LOWER, UPPER등등)
복수 행 함수에는 그룹 함수(SUM, MIN, MAX)가 있습니다.
묵시적 형변환은 오라클 10g 부터 자동으로 적용해줍니다.
문자 함수
- 문자 함수
- 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수를 의미
- 문자 함수의 종류
- 대소문자 변환 함수
- 문자조작 함수
- 문자열 길이 반환 함수
기본적으로 오라클은 테이블에 데이터를 저장할 때는 대문자로 저장을 합니다. 따라서 조회할 때는 LOWER나 UPPER을 사용하여 조회합니다.
임시테이블 생성
임시테이블을 생성하는 코드는 아래와 같습니다.
CREATE TABLE STUDENT_TNP AS
SELECT *
FROM STUDENT
;CTAS(CREATE TABLE AS SELECT) 명령을 사용하여 기존 데이터를 임시 테이블로 백업을 합니다. CTAS 구조로 임시 테이블을 만들면 테이블의 구조 및 데이터를 백업하게 됩니다.
임시 테이블이 바로 만들어지는 장점이 있지만 단점으로는 파티션 테이블을 GLOBAL TABLE 즉, 통 테이블로 생성하기 때문에 테이블의 물리적인 구조가 변경될 수 있다라는 단점이 있습니다. 또한, 관련 인덱스 및 속성을 제거하고 생성을 합니다.
테이블의 구조만 복제하는 예제 코드입니다.
--테이블의 구조만 복제
CREATE Table Cal_Mast_TMP2 AS
SELECT *
FROM Cal_mast
WHERE 0 = 1 --절대 거짓인 조건을 강제적으로 삽입하여 결과값이 생성되지 않도록 조치
; 해당 sql문을 실행하면 테이블의 데이터는 제외하고 구조만 복제되는 것입니다.
※공부하고 있어 다소 틀린점이 있을 수 있습니다. 언제든지 말해주시면 수정하도록 하겠습니다.
※용어에 대해 조금 공부 더 해서 수정하겠습니다.
