정보처리기사 - SQL 응용 #126~127

126. 사용자 정의 함수

126.1 사용자 정의 함수

프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하지만, 종료 시 처리 결과로 단일값만을 반환하는 절차형 SQL

• 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행
• 예약어 RETURN을 통해 단일값만을 반환하며, 출력 파라미터가 없음

126.2 사용자 정의 함수의 구성도

• DECLARE : 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 사용자 정의 함수의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : SELECT문이 삽입되어 데이터 조회 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

126.3 사용자 정의 함수 생성

사용자 정의 함수를 생성하기 위해서는 CREATE FUNCTION 명령어를 사용

• 표기 형식

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
    사용자 정의 함수 BODY;
    RETURN 반환값;
END;

• OR REPLACE : 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있음
• 파라미터 : 사용자 정의 함수의 파라미터로는 다음과 같은 것들이 올 수 있음
  • IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
  • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
  • 자료형 : 변수의 자료형을 지정
• 사용자 정의 함수 BODY
  • 사용자 정의 함수의 코드를 기록하는 부분
  • BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함
• RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

126.3.1 예제

• 'i_성별코드'를 입력받아 1이면 '남자'를, 2면 '여자'를 반환하는 사용자 정의 함수를 'Get_S_성별'이라는 이름으로 정의하라

1  CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
2  RETURN VARCHAR2
3  IS
   BEGIN
4      IF i_성별코드=1 THEN
           RETURN 남자;
5      ELSE
           RETURN 여자;
6      END IF;
   END;

1. 호출 프로그램이 전달한 값을 'i_성별코드'에 저장한 후 사용하는 사용자 정의 함수 'Get_S_성별'을 생성
2. 블록에서 리턴할 데이터의 자료형을 정의
   • 자료형의 크기는 입력하지 않음
   • 형식 : RETURN [자료형]
3. 변수 선언을 위해 사용하는 예약어로, 변수를 사용하지 않으므로 예약어만 입력
4. 'i_성별코드'가 1이면 '남자'를 반환하고,
5. 'i_성별코드'가 1이 아니면 '여자'를 반환
6. IF문의 끝

126.4 사용자 정의 함수 실행

사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용됨

• 표기 형식

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명=사용자 정의 함수명;
UPDATE 테이블명 SET 속성명=사용자 정의 함수명;

126.4.1 예제

• 다음의 <사원> 테이블을 출력하되, '성별코드'는 앞에서 사용자 정의 함수 'Get_S_성별'에 값을 전달하여 반환받은 값으로 대체하여 출력하라
• <사원> 테이블

이름	성별코드
김대진	1
이고을	2
최승규	1
송하나	2

SELECT 이름, Get_S_성별(성별코드) FROM 사원;

• 결과

이름	Get_S_성별(성별코드)
김대진	남자
이고을	여자
최승규	남자
송하나	여자

126.5 사용자 정의 함수 제거

사용자 정의 함수를 제거하기 위해서는 DROP FUNCTION 명령어를 사용

• 표기 방식

DROP FUNCTION 사용자 정의 함수명;

126.5.1 예제

• 앞에서 생성된 사용자 정의 함수 'Get_S_성별'을 제거하라

DROP FUNCTION Get_S_성별;

---

127. 제어문

127.1 제어문

위에서 아래로 차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용되는 명령문

• 제어문에는 IF, LOOP 등이 존재

127.2 IF문

조건에 따라 실행할 문장을 달리하는 제어문

127.2.1 형식 1 : 조건이 참일 때만 실행

1  IF 조건 THEN
2      실행할 문장1;
       실행할 문장2;
            :
3  END IF;

1. IF와 THEN은 예약어로 그대로 적음
   • 조건에는 참 또는 거짓이 결과로 나올 수 있는 수식을 적음
2. 조건이 참일 경우 실행할 문장을 적음
3. IF문이 종료되었음을 알리는 예약어로 그대로 적음

127.2.1.1 예제 1

• x가 10보다 크면 화면에 'true'를 출력하라

1  DECLARE
2      x INT:=20;
3  BEGIN
4      IF x>10 THEN
5          DBMS_OUTPUT.PUT_LINE('true');
6      END IF;
7  END;

1. 프로시저, 트리거, 사용자 정의 함수를 사용하지 않고 절차형 SQL을 실행하고 싶을 때 사용하는 예약어
   • 첫 문단에 그대로 적음
2. 정수형 변수 x를 선언하고 20으로 초기화
3. 절차형 SQL의 시작
4. x가 10보다 크면 5번 문장을 실행하고, 아니면 6번으로 이동하여 IF문 종료
   • x가 10보다 크므로 5번으로 이동
5. 4번의 조건식이 참일 경우 실행할 문장
   • 화면에 true가 출력

true

6. IF문의 끝
7. 절차형 SQL의 끝

127.2.2 형식 2 : 조건이 참일 때와 거짓일 때 실행 문장이 다름

   IF 조건 THEN
1      실행할 문장1;
   ELSE
2      실행할 문장2;
   END IF;

1. 조건이 참일 경우 실행할 문장을 적음
2. 조건이 거짓일 경우 실행할 문장을 적음

127.2.2.1 예제 2

   DECLARE
1      x INT:=10;
   BEGIN
2      IF x>10 THEN
3          DBMS_OUTPUT.PUT_LINE('true');
4      ELSE
5          DBMS_OUTPUT.PUT_LINE('false');
6      END IF;
   END;

1. 정수형 변수 x를 선언하고 10으로 초기화
2. x가 10보다 크면 3번 문장을 실행하고, 아니면 4번의 다음 문장인 5번 문장을 실행
   • x가 10보다 크지 않으므로 5번으로 이동
3. 2번 조건식이 참일 경우 실행할 문장
   • 화면에 true를 출력하고 6번으로 이동하여 IF문을 종료
4. 2번의 조건식이 거짓일 경우 실행할 문장의 시작점
5. 2번의 조건식이 거짓일 경우 실행할 문장
   • 화면에 false를 출력하고 6번으로 이동하여 IF문을 종료
6. IF문의 끝

127.3 LOOP문

조건에 따라 실행할 문장을 반복 수행하는 제어문

• 형식

1  LOOP
2      실행할 문장;
3      EXIT WHEN 조건;
4  END LOOP;

1. LOOP문의 시작을 알리는 예약어로 그대로 적음
2. 반복 수행할 문장을 적음
3. EXIT, WHEN은 예약어로 그대로 적음
   • 조건에는 참 또는 거짓이 결과로 나올 수 있는 수식을 적음
   • 조건이 참일 경우 LOOP문을 빠져나감
4. LOOP문이 종료되었음을 알리는 예약어로 그대로 적음

127.3.1 예제

• 다음은 1부터 10까지의 합을 구하는 절차형 SQL을 PL/SQL로 구현한 것임

   DECLARE
1      i INT:=0;
2      i_sum INT:=0;
   BEGIN
3      LOOP
4          i:=i+1;
5          i_sum:=i_sum+i;
6          EXIT WHEN i>=10;
7      END LOOP;
   END;

1. 정수형 변수 i를 선언하고 0으로 초기화
2. 정수형 변수 i_sum을 선언하고 0으로 초기화
3. LOOP의 반복문의 시작점
   • 4~6번 문장을 반복하여 수행
4. i의 값을 1씩 증가시킴
5. i의 값을 i_sum에 누적시킴
6. i가 10보다 크거나 같으면 LOOP문을 빠져나감
7. LOOP 반복문의 끝

• MOD(A, B) : A를 B로 나눈 나머지를 반환하는 내장 함수