5장. PL/SQL 기초

<숫자함수>

ABS(n) : 절대값

ACOS(n) : 아크코사인 값

CEIL(n) : 크거나 같은 가장 작은 정수 반환(올림)

FLOOR(n) : 버림

ROUND(n) : 반올림

EXP(n) : E의 n제곱을 반환

MOD(n1, n2) : n1을 n2로 나눈 나머지(floor기반)

remainder – round 기반 나머지

NANVL(n1, n2) : n1이 ‘NaN’이 아니면 n1을, ‘NaN’이면 n2를 반환

POWER(n1, n2) : n1의 n2제곱값

SQRT(n) : 제곱근

TRUNC(n1, n2) : 숫자 n1을 소수점 자리 파라미터 n2이하를 절삭

WIDTH_BUCKET : 동일한 넓이를 갖는 히스토그램 생성

 

<변환함수>

CAST : 다양한 형식으로 변환

 

# 수식을 수치로 변환

SELECT AVG(수량) FROM 주문;

SELECT CAST(AVG(수량) AS NUMBER(3)) FROM 주문;

SELECT CAST(AVG(수량) AS NUMBER(4,1)) FROM 주문;

 

# 수식을 문자열로 변환

SELECT 제품명,

CAST(재고량 AS CHAR(5)) || 'X' || CAST(단가 AS CHAR(5)) AS "재고량 x 단가",

재고량 * 단가 AS 재고금액

FROM 제품;

 

# 다양한 구분자를 갖는 날짜데이터를 정형적인 날짜로 변환

SELECT sysdate from dual;

SELECT CAST(‘2020/08/01’ as DATE) FROM dual;

SELECT CAST(‘2020-08-01’ as DATE) FROM dual;

SELECT CAST(’20/08/01’ as DATE) FROM dual;

SELECT CAST(‘2020@08@01’ as DATE) FROM dual;

 

TO_CHAR : 숫자를 문자 형식으로 변환

datetime (아래의 예제 참고)

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MM:SS') FROM DUAL;

 

TO_NUMBER : 문자를 숫자 형식으로 변환

SELECT TO_NUMBER('0123'), TO_NUMBER('1234.456') FROM DUAL;

<문자함수>

CONCAT(s1, s2) : 두 문자를 연결

TRIM(…) : 양쪽의 공백 제거 또는 선택적으로 특정 한문자를 제거

ASCII(s) : 문자의 10진 아스키코드값을 반환

LENGTH(s) : 문자의 길이를 반환. LENGTHB는 바이트단위

SUBSTR(…) : 부분 문자열을 반환

===> DB에선 Index가 0이 아닌 1부터 시작

 

<날짜 및 시간 함수>

SELECT sysdate FROM dual; ==> 로컬 데이터베이스의 OS의 일자와 시간을 반환.

SELECT ADD_MONTHS('2020-01-01', 5), ADD_MONTHS(SYSDATE, -5) FROM DUAL;

==> Date에 특정 개월수 integer를 더한 DATE를 반환

SELECT extract(year from date '1998-03-07'), extract(month from sysdate) from dual;

==> 특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환

LAST_DAY(date) : 해당 날짜가 속한 달의 마지막 날짜를 반환

NEXT_DAY(…) : 해당일을 기준으로 명시된 요일의 다음 날짜를 변환

MONTHS_BETWEEN(d1,d2) : 날짜와 날짜 사이의 개월 수를 출력

 

<순위함수>

[순위함수의 사용 형식]

순위함수이름( ) OVER(

[PARTITION BY <partition list>]

ORDER BY <order list>

)

 

ROW_NUMBER( ) : 분할별로 정렬된 결과에 대한 순위 부여

RANK( ) : 값의 그룹에서 값의 순위를 계산

DENSE_RANK( ) : 순위를 부여. 동순위 다음 순위는 동순위 수량에 관계없이 1증가된 값

NTILE( ) : 순서화된 데이터를 지정된 bucket의 수로 분할하여 순위 부여

 

<분석함수>

CUME_DIST( ) : 값의 그룹에 있는 값의 누적 분포치를 계산

STDDEV( ) : Number의 조합인 expr의 표본표준편차를 반환.

VARIANCE( ) : expr의 분산을 반환

 

피벗

행을 열로 변경 : 이전의 DECODE, CASE WHEN 대체

SELECT * FROM 구매이력

PIVOT ( SUM(금액)

FOR 상품

IN ('가전','의류','식품') ) ;

<PL/SQL>

PL/SQL 프로그래밍을 위해 변수를 선언해야 함

- 세션이 종료될 때까지 변수는 유효함

 

[PL/SQL 변수의 사용 형식]

DECLARE

-- 변수이름 ‘AGE’, 자료형 3자리 정수

AGE NUMBER(3);

BEGIN

-- 변수에 직접 할당

AGE := 25;

-- SQL문에서 변수에 값 할당

Select 나이 INTO AGE FROM 고객

END;

 

SET SERVEROUTPUT ON; ==> 출력을 하게 해주는 문장

 

일반 변수

Ex) Age Number(3);

 

상수 : 초기화를 해야 함

Ex) Pi CONSTANT NUMBER(4,2) := 3.14;

 

V_고객튜플 고객%ROWTYPE;

V_고객ID 고객.고객아이디%TYPE := 'apple';

 

==> %TYPE : 테이블 컬럼의 데이터타입, %ROWTYPE : 튜플의 데이터 형식

 

Record : 구조체의 Tyepdef와 유사. 사용자가 복합 데이터 타입을 정의함

TYPE Student IS RECORD (

ID NUMBER(6),

NAME NCHAR(5),

ADDR NCHAR(30)

);

학생 Student;

 

Collection : Varray, NestedTable, AssociativeTable

VARRAY

TYPE Vat IS VARRAY(5) OF NUMBER(6);

Arr Vat;

 

NESTED TABLE

TYPE Ntt IS TABLE OF NUMBER(6);

Ntable Ntt;

ASSOCIATIVE ARRAY

TYPE Aat IS TABLE OF NUMBER(6) INDEX By NCHAR(5) ;

NT Aat;

 

<PL/SQL 프로그래밍 : IF>

DECLARE

score NUMBER(5) ; -- 변수 선언

BEGIN

score := 87; -- 변수에 값 대입

IF score >= 60 THEN -- 만약 var1이 100이라면

BEGIN

DBMS_OUTPUT.PUT_LINE('합격입니다');

END ; -- 한줄이라면 BEGIN … END 생략해도 되지만…

ELSE

DBMS_OUTPUT.PUT_LINE('불합격입니다.');

END IF;

END ;

 

DECLARE

점수 NUMBER(3) ;

등급 CHAR(1) ;

BEGIN

점수 := 95;

IF 점수 >= 90 THEN 등급 := 'A';

ELSIF 점수 >= 80 THEN 등급 := 'B';

ELSIF 점수 >= 70 THEN 등급 := 'C';

ELSIF 점수 >= 60 THEN 등급 := 'D';

ELSE 등급 := 'F';

END IF;

DBMS_OUTPUT.PUT_LINE(점수||'점이고, 학점은 '||등급);

END ;

 

<PL/SQL 프로그래밍 : CASE… WHEN>

DECLARE

점수 NUMBER(3) ;

등급 CHAR(1) ;

BEGIN

점수 := 95;

CASE

WHEN 점수 >= 90 THEN 등급 := 'A';

WHEN 점수 >= 80 THEN 등급 := 'B';

WHEN 점수 >= 70 THEN 등급 := 'C';

WHEN 점수 >= 60 THEN 등급 := 'D';

ELSE 등급 := 'F';

END CASE;

DBMS_OUTPUT.PUT_LINE(점수||'점이고, 학점은 '||등급);

END ;

 

SELECT 고객아이디, 고객이름, CASE

WHEN (적립금 >= 5000) THEN '단골고객'

WHEN (적립금 >= 500) THEN '정상고객'

ELSE '신입고객' -- 회원가입포인트 500

END AS "고객유형", 적립금

FROM 고객

ORDER BY 적립금 DESC NULLS LAST;

 

<PL/SQL 프로그래밍 : FOR, WHILE>

SET SERVEROUTPUT ON;

DECLARE

i NUMBER(2) ;

total NUMBER(3) ; -- sum은 예약어

BEGIN

total := 0;

FOR i IN 1..10

LOOP

total := total + i;

END LOOP;

DBMS_OUTPUT.PUT_LINE(total);

END ;

 

SET SERVEROUTPUT ON;

DECLARE

i NUMBER(2) ;

total NUMBER(3) ; -- sum은 예약어

BEGIN

i := 1;

total := 0;

WHILE i <= 10 LOOP

total := total + i;

i := i + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(total);

END ;

 

 

 

 

 

<CONTINUE, EXIT, GOTO>

Continue : C의 Continue와 동일

Exit : loop 종료

GOTO

GOTO 라벨이름 : 무조건 이동하게 됨.

라벨 : << 라벨 이름 >>으로 지정

 

-- 1부터 1씩 증가하면서 합을 구해 10000을 초과하면 중지

-- 그 때 합의 값을 출력. 단, 3 또는 5의 배수일 때는 합하지 않음

DECLARE

i NUMBER(3) ;

total NUMBER(5) ;

BEGIN

i := 1;

total := 0;

WHILE i <= 500

LOOP

IF MOD(i, 3) = 0 OR MOD(i, 5) = 0 THEN

i := i + 1;

CONTINUE;

END IF;

total := total + i;

IF total > 10000 THEN

EXIT;

END IF;

i := i + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(total);

END ;

 

----// PL/SQL : GOTO

-- 위의 문제를 EXIT 대신 goto로 표현

DECLARE

i NUMBER(3) ;

total NUMBER(5) ;

BEGIN

i := 1;

total := 0;

WHILE i <= 500

LOOP

IF MOD(i, 3) = 0 OR MOD(i, 5) = 0 THEN

i := i + 1;

CONTINUE;

END IF;

total := total + i;

IF total > 10000 THEN

GOTO STOP_POINT;

END IF;

i := i + 1;

END LOOP;

<< STOP_POINT >>

DBMS_OUTPUT.PUT_LINE(total);

END ;

 

<SLEEP>

BEGIN

DBMS_LOCK.SLEEP(10); == > 이면 해당 문장은 10초간 정지

DBMS_OUTPUT.PUT_LINE('10초후 수행');

END ;

 

<예외 처리>

NO_DATA_FOUND : SELECT 문에서 검색 데이터가 없음 또는 파일 끝을 읽음

INVALID_NUMBER 문자열을 숫자로 변환시 오류

TOO_MANY_ROWS : SELECT INTO 문에서 2개 이상의 ROW를 반환

ZERO_DIVIDE : 0으로 나누기

 

[시스템 정의 예외처리 형식]

EXCEPTION

WHEN 예외 [, 예외.....] THEN

Statements;

WHEN 예외 [, 예외.....] THEN

Statements;

.....

 

DECLARE

고객명 varchar2(10);

BEGIN

SELECT 고객이름 INTO 고객명 FROM 고객

WHERE 고객이름 LIKE '김%'; -- 2명

DBMS_OUTPUT.PUT_LINE('검색결과 : ' || 고객명) ;

EXCEPTION

WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('없음') ;

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('너무 많음') ;

END;

 

 

 

 

<예외 처리 : Raise Exception> ==> 강제 예외 발생

QueryUpdate(삽입, 삭제, 갱신) Update된 속성이 하나도 없을 때 값이 True임

DECLARE

고객명 varchar2(10);

강제예외 EXCEPTION;

BEGIN

고객명 := '홍길동';

UPDATE 고객

SET 적립금 = 500

WHERE 고객이름=고객명;

IF SQL%NOTFOUND THEN ==> ‘홍길동’을 찾기 못했을 경우 True가 된다

RAISE 강제예외;

END IF;

EXCEPTION

WHEN 강제예외 THEN

DBMS_OUTPUT.PUT_LINE ('자료없음. 변경불가') ; ==> 강제예외 EXCEPTION을 정의한 것

END ;

 

 

DECLARE

고객명 varchar2(10);

BEGIN

고객명 := '홍길동';

UPDATE 고객 SET 적립금 = 500 WHERE 고객이름=고객명;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20001, '자료없음. 변경불가');

END IF;

END ;

 

<동적(Dynamic) SQL>

SQL Query문이 실시간에 구성됨

DECLARE

sql_string VARCHAR2(80); -- SQL 문장을 저장할 변수

v_나이 number(3); -- 반환될 나이를 저장할 변수

BEGIN

sql_string := 'SELECT 나이 FROM UNIV.학생 WHERE 이름 = ''박광수'' ' ;

-- SQL문장을 동적으로 구성. 상수' 박광수' 대신 사용자로 부터 입력받은 값을 대체할 수 있음

EXECUTE IMMEDIATE sql_string INTO v_나이;

DBMS_OUTPUT.PUT_LINE (v_나이) ;

END ;