9. 사용자 정의 함수
사용자 정의 함수
•
프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL
•
데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML 문의 호출에 의해 실행된다.
•
예약어 RETURN을 통해 단일값을 반환하며, 출력 파라미터가 없다.
사용자 정의 함수의 구성도
사용자 정의 함수 생성
•
사용자 정의 함수를 생성하기 위해 CREATE FUNCTION 명령어를 사용한다.
•
예제 : ‘i_성별코드’를 입력 받아 1이면 ‘남자’를 ,2면 ‘여자’를 반환하는 사용자 정의 함수를 ‘Get_S_성별’ 이라는 이름으로 정의하시오.
create function Get_S_성별(i_성별코드 in INT)
return varchar2
is
begin
if i_성별코드 = 1 then
return '남자';
else
return '여자';
end if;
end
SQL
복사
사용자 정의 함수 실행
•
사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.
•
예제 : 다음의 <사원> 테이블을 출력하되, ‘성별코드’는 앞에서 사용자 정의 함수 ‘Get_S_성별’에 값을 전달하여 반환받은 값으로 대체하여 출력하시오.
select 이름, Get_S_성별(성별코드) from 사원;
SQL
복사
사용자 정의 함수 제거
•
사용자 정의 함수를 제거하기 위해서 DROP FUNCTION 명령어를 사용한다.
•
예제 : 위에서 생성된 사용자 정의 함수 ‘Get_S_성별’을 제거하시오.
drop function Get_S_성별;
SQL
복사
10. 제어문
제어문
•
위에서 아래로 차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
•
제어문에는 IF, LOOP 등이 있다.
IF 문
•
예제 : x가 10보다 크면 화면에 ‘true’ 출력하기
declare
x INT := 20;
begin
if x > 10 then
DBMS_OUTPUT.PUT_LINE('true')
end if;
end;
SQL
복사
•
DBMS_OUTPUT.PUT_LINE(데이터)는 ‘데이터’에 넣은 변수나 값을 화면에 출력해주는 함수이다.
•
예제 : x가 10보다 크면 화면에 “true”를 출력하고, 아니면 “false”를 출력하기
declare
x INT := 10;
begin
if x > 10 then
DBMS_OUTPUT.PUT_LINE('true');
else
DBMS_OUTPUT.PUT_LINE('false');
end if;
end;
SQL
복사
LOOP 문
•
조건에 따라 실행할 문장을 반복 수행하는 제어문
•
예제 : 1부터 10까지의 합을 구하는 절차형 SQL을 PL/SQL로 구현하시오.
declare
i INT := 0;
i_sum INT := 0;
begin
loop
i := i + 1;
i_sum := i_sum + i;
exit when i >= 10;
end loop;
end;
SQL
복사
11. 커서 (Cursor)
커서 (Cursor)
•
쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)
•
커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행된다.
묵시적 커서 (Implicit Cursor)
•
DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
•
커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능하다.
•
수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용된다.
•
속성의 종류
명시적 커서 (Explicit Cursor)
•
사용자가 직접 정의해서 사용하는 커서
•
쿼리문의 결과를 저장하여 사용함으로써, 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지한다.
•
커서는 기본적으로 Open - Fetch - Close 순으로 이루어지며, 명시적 커서를 사용하기 위해서는 Open 단계 전에 Declare 해야 한다.
열기(Open) 형식
•
커서를 사용하기 전에 반드시 적는다.
OPEN 커서명(매개변수1, 매개변수2 ...);
SQL
복사
패치(Fetch) 형식
•
커서명과 커서에 저장된 튜플들의 각 속성과 같은 자료형을 가진 변수를 적고 데이터를 가져온다.
FETCH 커서명 INTO 변수1, 변수2 ...;
SQL
복사
닫기(Close) 형식
•
사용된 커서는 메모리 해제를 위해 반드시 닫아야 한다.
CLOSE 커서명;
SQL
복사
•
예제 : 다음은 <employee> 테이블로부터 id가 20보다 크거나 같은 튜플의 name을 출력하는 절차형 SQL을 PL/SQL로 구현한 것이다.
declare
p.name employee.name%TYPE;
cursor cur_name(ff INT)
is
select name from employee where id >= ff;
begin
Open cur_name(20);
loop
fetch cur_name into p_name;
exit when cur_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(p_name);
end loop;
close cur_name;
end;
SQL
복사
12. DBMS 접속 (Connection)
•
DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등
•
DBMS 접속 기술의 종류
동적 SQL (Dynamic SQL)
•
다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
•
사용자로부터 SQL 문의 일부 또는 전부를 입력받아 실행할 수 있다.
•
응용 프로그램 수행 시 SQL이 변형될 수 있으므로, 프리컴파일(Precompile) 할 때 구문 분석, 접근 권한 등을 할 수 없다.
◦
프리컴파일 (Precompile)
▪
고급 언어를 기계어로 번역하는 컴파일(Compile) 전에 수행하는 작업
▪
필요한 라이브러리를 불러오거나 코드에 삽입된 SQL문을 DB와 연결하는 작업을 수행함
•
정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.
13. SQL 테스트
SQL 테스트
•
SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정
•
단문 SQL은 SQL 코드를 직접 실행한 후, 결과를 확인하는 것으로, 간단히 테스트가 가능하다.
•
절차형 SQL은 테스트 전에 생성을 통해 구문 오류나 참조 오류의 존재 여부를 확인한다.
•
정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인한다.
단문 SQL 테스트
•
DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트 하는 것으로, 직접 실행하여 결과물을 확인한다.
•
DDL이 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션 등을 확인할 수 있다.
DESC [개체명];
SQL
복사
•
DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인할 수 있다.
•
DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 조회하여 확인할 수 있다.
•
예제 : MySQL에서 <학생> 테이블의 정보를 확인하는 SQL문을 작성하시오.
DESC 학생;
SQL
복사
절차형 SQL 테스트
•
프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행한다.
•
많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로, SHOW 를 통해 오류 내용을 확인하고 문제를 수정한다.
SHOW ERRORS;
SQL
복사
14. ORM (Object-Relational Mapping)
ORM (Object-Relational Mapping)
•
객체 지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational DataBase)의 데이터를 연결(Mapping)하는 기술
•
객체 지향 프로그래밍에서 사용할 수 있는 가상의 객체 지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결한다.
•
ORM으로 생성된 가상의 객체 지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이하다.
ORM 프레임워크
•
ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 소프트웨어
ORM의 한계
•
프레임워크가 자동으로 SQL을 작성하기 때문에, 의도대로 SQL이 작성되었는지 확인해야 한다.
•
객체 지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우, 프로젝트가 크고 복잡해질수로 ORM 기술을 적용하기 어렵다.
•
기존의 기업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에, ORM에 적합하게 변환하려면 많은 시간과 노력이 필요하다.
15. 쿼리 성능 최적화
쿼리 성능 최적화
•
데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
•
쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화할 쿼리를 선정해야 한다.
◦
APM (Application Performance Management/Monitoring)
▪
애플리케이션의 성능 관리를 위해 접속사, 자원 ㅎ녀황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구
•
최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성한다.
옵티마이저 (Optimizer)
•
작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
•
RBO (Rule Based Optimizer)과 CBO (Cost Based Optimizer) 두 종류가 있다.
◦
RBO : 데이터베이스 관리자(DBA)가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
◦
CBO : 입출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플의 개수 등을 종합하여 각 DBMS 마다 고유의 알고리즘에 따라 산출되는 ‘비용’으로 최적의 경로를 찾는 비용 기반 옵티마이저
실행 계획 (Execution Plan)
•
DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법
•
EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현된다.
•
실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀 있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있다.
쿼리 성능 최적화 방법
•
쿼리 성능 최적화는 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것을 의미한다.
