개발자연습생

PL / SQL 이란 (Oracle's) Procedural Language extension to SQL 의 약자이다.

즉, 오라클에 내장되어 있는 프로그래밍 언어를 뜻한다.

쿼리문은 DB 를 사용하는 일종의 명령어이지만 PL / SQL 은 변수를 선언하는 등 좀 더 언어에 가까운 모습이다.

PL / SQL은 절차지향적 언어의 성격을 띄며 조건문(IF), 반복문(LOOP) 등을 처리할 수 있다.

이번 포스팅에서는 PL / SQL 에 대해 많은 내용을 다루진 못하며 어떤 개념인가에 대해 정리하고자 한다.

하지만 다음 포스팅 주제인 프로시저, 함수, 트리거와 관련하여 계속 사용되므로 꼭 읽어보도록 하자.

1. PL / SQL 의 예약어

PL / SQL을 사용하기 위해서는 다음과 같이 핵심적인 예약어들이 필요하다.

• 셀 병합
• 행 분할
• 열 분할
• 너비 맞춤
• 삭제

2. PL / SQL 사용하기

아래는 PL / SQL 을 사용한 코드이다.

DECLARE 아래를 보면 V_NUM 이라는 NUMBER형의 변수를 선언하였다.

BEGIN 에서는 변수에 값을 대입한 것을 확인할 수 있는데 이 때 사용한 연산자( := ) 는 대입 연산자라는 점을 알아 두자.

밑에서 DBMS_OUTPUT.PU_LINE( ) 은 출력하기 위해 사용하는 함수로 자바의 println() 과 동일한 기능을 한다.

끝으로 end 를 통해 PL / SQL 이 끝남을 알린다. 또한 마지막에 / 를 사용하여 PL / SQL 의 한 블록이 끝났음을 뜻한다.

실행 결과

2. PL / SQL 과 SELECT 를 함께 사용하기.

위의 예제는 PL / SQL 을 사용하는 방법이였다. 이번에는 조금 응용하여 SELECT 문과 함께 사용하는 방법을 보자.

SELECT 문을 함께 사용하면 테이블에서 값을 가져와 변수에 대입하는 작업이 가능하다.

아래의 코드는 지난 예제로 사용했던 사람, 자동차 테이블을 사용하였다.

BEGIN 을 보면 SELECT 구문이 들어간 것을 볼 수 있는데, 풀어서 써 보면 다음과 같다.

"HUMAN 테이블에서 H_NAME 이 '고길동' 인 사람의 H_AGE의 값을 V_H_AGE 에 넣고 출력해 주세요."

정도로 얘기할 수 있다. 따라서 실행 결과는 고길동의 나이가 출력될 것이다.

실행 결과

※ V_H_AGE HUMAN.H_AGE % TYPE; 구문의 뜻은

"V_H_AGE 라는 변수의 자료형은 HUMAN 테이블의 H_AGE의 자료형과 같습니다." 이다.

인덱스란 테이블에서 데이터 검색을 빠르게 할 수 있도록 만들어 주는 구조체를 의미한다.

1. 인덱스의 장 / 단점

인덱스의 장단점을 간단하게 정리하면 다음과 같다.

장점 : 검색의 속도가 빨라진다.

단점 : 인덱스를 생성하는데 시간이 소요된다.

이렇게 장단점이 있으므로 사용하는 시점에 따라 약이 될 수도, 독이 될 수도 있다.

인덱스를 사용하면 좋은 경우는 다음과 같다.

1). WHERE 절이나 JOIN 등에서 조건으로 자주 사용되는 컬럼

2). NULL 값이 많이 포함되는 컬럼

반대로 안좋은 경우는 다음과 같다.

1). 테이블의 크기가 작은 경우 (데이터가 적게 들어가는 경우)

=> 크기가 작다면 인덱스를 안써도 충분히 검색이 빠르다.

2). 자주 갱신되는 테이블

=> 갱신이 자주될 수록 인덱스 또한 자주 변경되게 된다.

2. 인덱스의 생성 / 삭제

지난 포스팅에서 만든 사람, 자동차 테이블 사용해 본다.

인덱스의 생성은 다음과 같다.

위의 코드는 사람 테이블에서 사람의 이름을 인덱스로 작성한 부분이다.

생성은 테이블 생성과 동일하게 CREATE 문을 사용하였으며 아랫줄에서는 ON 을 통해 어떤 컬럼을 인덱스로

만들 것인지 설정한다. 위의 코드에서는 HUMAN 테이블의 H_NAME 컬럼을 인덱스로 설정한다는 의미이다.

삭제는 테이블 생성과 동일하게 DROP 구문을 사용한다.

※ 검색 속도의 차이를 보기 위해 사람의 데이터를 2만개 넣어봤지만 속도는 0.001 초 차이였다.

하지만 규모가 큰 테이블의 경우 10만개 이상의 데이터가 들어가는 테이블도 많을 것이다.

아마도 속도를 높이기 위해서는 10만개 정도의 규모가 되어야 하지 않을까 싶다.

뷰란 두 개의 테이블에서 일부 컬럼을 조합해 하나의 가상 테이블을 만드는 기능이다.

말 그대로 가상이기 때문에 실제 테이블로 생성되진 않지만 DB가 종료되는 시점까진 유지된다.

본격적으로 뷰에 대한 내용을 작성하기 전 OR REPLACE 에 대해 잠시 집고 넘어간다.

1. OR REPLACE

OR REPLACE란 이전에 사용된 오브젝트가 있다면 덮어 씌운다는 의미이다.

주로 사용되는 구문은 뷰, 프로시저, 함수를 만들 때 주로 사용된다.

뷰를 생성시 OR REPLACE 가 붙는 다면 기존에 생선된 뷰에 다른 내용이 추가된다면 새로 수정한다는 의미이다.

2. 뷰 생성하기

뷰를 생성할 때는 보통 내부 조인과 함께 사용된다.

우선 오늘 사용할 예제의 테이블 다음과 같다.

사람 테이블과 자동차 테이블을 만들며 사람의 번호와 차 주인의 번호를 사용하였다.

이제 뷰를 생성하여 두 테이블에서 필요한 컬럼만 가져와 출력할 것이다. 이 때 사람의 이름과 차종을 출력한다.

위의 코드는 뷰를 생성한 구문이다.

뷰의 이름은 CAROWNNER 이며 표시할 데이터는 사람의 이름, 차의 이름 이다.

서로 다른 테이블을 합치기 위해 내부조인을 사용하였다.

위의 코드가 제대로 실행되었는지 확인하기 위해 SELECT 해보면 다음과 같다.

실행 결과

뷰는 가상이긴 하지만 어쨌든 테이블이므로 SELECT 에서 출력하는 방법은 테이블과 동일하다.

이번에는 위에서 언급한 OR REPLACE를 확인하기 위해 차의 번호까지 추가하여 뷰를 생성하였다.

코드를 보면 C_NO 가 추가되었으나 별도로 DROP하여 삭제하지 않아도 정상적으로 생성이 가능하다.

다시 SELECT 해보면 정상적으로 실행된 것을 확인할 수 있다.

실행 결과

3. 뷰 삭제하기

뷰는 가상의 테이블이므로 자동으로 소멸되는 시점이 존재한다. 하지만 수동적으로 삭제하고 싶다면 아래의

구문을 실행하면 된다.

실행 결과

시퀀스란 순차적으로 일을 처리한다는 의미를 가지고 있다. SQL 에서도 자동으로 순차적으로 일정한 크기로

증감하는 변수를 만들 수 있는데 이를 시퀀스라 한다.

순차적으로 값이 증감한다면 유일한(UNIQUE) 값을 데이터로 하는 컬럼을 만들 수 있을 것이다. 실제로 시퀀스는

기본키 드 중복되지 않는 값이 들어가는 컬럼에 사용할 수 있다.

1. 시퀀스 생성 / 삭제 방법

테이블과 비슷하게 CREATE 문으로 시작한다.

삭제도 마찬가지로 DROP 문으로 삭제한다.

2. 시퀀스 구문에 사용되는 예약어

어떤 컬럼의 값이 일정하게 증감하게 한다는 것은 다음과 같이 조건을 가질 수도 있다.

1) 시작할 크기

2) 증감할 크기

3) 범위

4) 반복 여부

5) 미리 데이터를 조건에 맞춰 생성함.

위의 조건들을 예약어로 작성하면 다음과 같다.

여기서 자주 쓰이는 것은 START WITH와 INCREMENT BY 부분이다.

만약 어떤 테이블 레코드가 100부터 시작하여 99, 98, 97, ..., 1 까지 라면 아래와 같이 작성하면 된다.

※ 시퀀스는 1부터 시작, 1씩 증가를 기본으로 하고 있다. 따라서 아래와 같이 사용하면 자동으로 1부터 1씩 증가

이다.

※ 마지막의 CACHE는 DB의 속도를 위해 임시 테이블에 미리 해당 값의 갯수만큼 값을 생성해두는 것이다.

만약 DB가 속도에 민감한 이슈를 가지고 있을 때 고려하면 된다. (보통은 잘 안씀.)

※ CYCLE을 사용하게 되면 당연히 유일한 값을 갖기 위한 시퀀스의 용도가 달라진다.

3. 시퀀스 사용하기

이제 시퀀스를 생성하고 조건을 달았다면 실제로 사용해보자.

사용은 INSERT 문과 함께 사용할 수 있다.

만약 [사람] 테이블에 [번호], [이름] 을 갖는다면

처럼 나올 것이다. 여기서 번호가 기본키라면 시퀀스를 사용하면 된다.

실행 결과

위의 코드에서 보면 INSERT 문에서 [HUMAN_SEQ.NEXTVAL] 으로 작성한 곳에 1과 2가 삽입되었다.

HUMAN_SEQ.NEXTVAL 는 현재 시퀀스의 다음 값을 의미한다.

만약 현재 시퀀스 값이 궁금하다면 다음과 같이 쿼리를 작성하면 된다.

SYNONYM(이하 시노님) 은 "동의어" 라는 뜻을 가진 영단어이다.

단어의 뜻에 맞춰 SQL에서의 시노님은 테이블의 별칭을 만드는 용도로 사용된다.

C 언어에서 #define 을 생각해 보면 쉽게 이해될 것이다.

1. 시노님의 장점

1) 정식적인 테이블의 본 이름을 숨겨 보안을 강화한다.

2) 복잡한 테이블의 이름을 단순화 하여 사용할 수 있다.

3) 추후 테이블의 이름이 변경되어도 시노님을 변경하면 코드를 변경하지 않고 동일하게 사용할 수 있다.

즉, 별칭을 지어 줌으로 본래의 테이블을 감출 수 있으며 테이블의 이름이 매우 길면 짧은 별칭으로 시노님을 정해

일일이 타이핑하는 불편을 없앨 수 있고 테이블의 이름이 변경되어도 코드 전체를 변경할 필요가 없어진다.

2. 시노님 생성

시노님을 생성하는 방법은 다음과 같다.

테이블 생성과 유사하게 CREATE SYNONYM 예약어를 사용한다.

첫번째 [ ] 에는 새로 만들 별칭을 넣어준다.

두번째 [ ] 에는 별칭을 만들 테이블(본 테이블)의 이름을 넣어준다.

<예시>

여기서 M 은 새로 생성된 시노님 이며, MEMBER 는 본 테이블의 이름이다.

여기서 시노님의 접근 권한에 따라 PUBLIC 과 PRIVATE 두 가지의 접근 방법이 생긴다.

1) PUBLIC

: 해당 DB 에 접근 권한을 가진 누구나 생성이 가능하다.

: 가장 일반적으로 사용되므로 PUBLIC 은 생략이 가능하다.

2) PRIVATE

: 해당 DB 의 관리자만 접근이 가능하다.

3. 시노님 출력 / 삽입 / 삭제하기

시노님은 앞서 말했 듯 테이블의 별칭을 정해 준 것이다. 따라서 시노님을 통해서도 테이블 출력이 가능하다.

위의 쿼리에서 보듯 MEMBER 테이블 대신 M 을 사용한 것을 볼 수 있다.

데이터 삽입 또한 일반 테이블의 쿼리에 값을 입력하는 것과 동일한 것을 볼 수 있다.

아래는 시노님을 삭제하는 방법이다.

역시나 테이블 삭제와 비슷한 것을 볼 수 있다.

오늘 작성할 함수는 다음과 같다.

1) 절대값 구하기 함수

2) 올림, 내림, 반올림 함수

3) 나머지(MOD) 계산 함수

4) 대소문자 변환 함수

5) 출력 자리 수 지정 함수

본문을 작성하기에 앞서 사용할 테이블은 DUAL 테이블을 기본으로 사용하며 이전 포스팅에서 사용했던

EMP 테이블을 사용할 예정이다. (지난 포스팅 첨부파일)

※ DUAL : 가상의 빈 테이블. 테이블이 필요 없지만 문장을 완성하기 위해 사용

1. 수학 함수

1) 절대값 구하기 ABS( )

ABS ( 값 ) 의 형태로 사용한다.

실행 결과

2) 올림 CEIL( )

CEIL ( 값 ) 의 형태로 사용한다.

자연수는 올림이 진행되지 않고 오로지 소수점 자리만 올림이 가능하다.

실행 결과

3) 내림 FLOOR( )

FLOOR ( 값 ) 의 형태로 사용한다.

올림과 마찬가지로 소수점 까지만 내림을 진행한다.

실행 결과

4) 반올림 ROUND( )

반올림 함수는 올림, 내림과 달리 자릿수 지정이 가능하여 ROUND (n, m) [n = 값, m = 자릿수] 로 사용한다.

m 의 값이 0, 1, 2, ~ 라면 소수점 1번째 라는 의미이며 반대로 음수인 -1 은 자연수 첫째자리를 의미한다.

실행 결과

5) 나머지 계산 MOD ( )

두 수를 나누어 나머지를 계산하는 함수로 MOD( n, m ) 의 형태로 사용되며, n 을 m 으로 나눈 나머지를 뜻한다.

실행 결과

6) 소수점 출력 자리 지정 TRUNC( )

숫자를 출력할 자릿수를 지정한다. TRUNC( n, m ) 의 형태로 사용된다.

n은 값이며 m은 반올림과 비슷하게 양수, 음수를 통해 표시할 자리를 지정한다.

실행 결과

재밌는 점은 15를 자연수 첫째 자리까지만 표시하면 1이 될 줄 알았지만 실행결과 10으로 나오게 된다.

2. 문자 함수

문자 함수는 사용 방법이 간단하지만 외워두면 유용할 것 같아 별도로 정리한다.

문자 함수를 사용하기 위해 사용 테이블은 EMP 테이블을 사용한다.

1) 대문자 -> 소문자 변환 LOWER( )

LOWER( 값 ) 의 형태로 사용한다.

실행 결과

2) 소문자 -> 대문자 변환 UPPER( )

이번엔 반대로 변환해보자.

실행 결과

여기서 중요한 것은 문자를 변환하였어도 원본 데이터는 변경되지 않는다는 것이다.

실행 결과