📌 SQL 활용 - 사용자 정의 함수/DBMS 접속 기술

자격증/정보처리기사 📌 SQL 활용 - 사용자 정의 함수/DBMS 접속 기술

Dexter_- 2024. 2. 22. 16:56

728x90

데이터베이스 구축 - SQL 활용

사용자 정의 함수의 개요

📌 사용자 정의 함수는 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL이다.

사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행된다.
사용자 정의 함수는 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없다.
사용자 정의 함수는 INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있다.
사용자 정의 함수는 프로시저를 호출하여 사용할 수 없다.
사용자 정의 함수는 SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도로 사용된다.

💡 내장 함수 : DBMS에 기본적으로 포함되어 있는 함수들을 의미하여, 합계를 구하는 SUM(), 평균을 구하는 AVG() 같은 그룹 함수가 여기에 속한다.

프로시저 vs 사용자 정의 함수

구분	프로시저	사용자 정의 함수
변환값	없거나 1개 이상 가능	1개
파라미터	입/출력 가능	입력만 가능
사용 가능 명령문	DML, DCL	SELECT
호출	프로시저, 사용자 정의 함수	사용자 정의 함수
사용 방법	실행문	DML에 포함

사용자 정의 함수의 구성

📌 사용자 정의 함수의 구성은 프로시저와 유사하다. 프로시저의 구성에서 RETURN만 추가하면 된다.

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

사용자 정의 함수 생성

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

표기 형식

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)

[지역변수 선언]

BEGIN

사용자 정의 함수 BODY;

RETURN 반환값

END;

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

🔔 예제) '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;

💡 해설 :

파라미터로 'i_성별코드'를 전달 받는 사용자 정의 함수 'Get_S_성별'을 생성한다.
블록에서 리턴 할 데이터의 자료형을 정의한다. 자료형의 크기는 입력할 필요 없다.
- 형식 : RETURN [자료형]
변수 선언을 위해 사용하는 예약어로 변수를 사용하지 않으므로 예약어만 입력한다.
'i_성별코드'가 1이면 남자를 반환하고,
'i_성별코드'가 1이 아니면 여자를 반환한다.
IF문의 끝

사용자 정의 함수 실행

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

표기 형식

SELECT 사용자 정의 함수명 FROM 테이블명;

INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);

DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;

UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

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

<사원>

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

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

<결과>

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

💡 해설: <사원> 테이블에서 '이름' 속성과 앞에서 정의한 사용자 정의 함수 'Get_S_성별'에 '성별코드' 속성을 인수로 전달하고 반환 받은 값을 결과로 출력한다. '성별코드'의 값이 1이면 남자가 출력되고, 1이 아니면 여자가 출력 된다.

사용자 정의 함수 제거

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

표기 형식

DROP FUNCTION 사용자 정의 함수명;

🔔 예제) 앞에서 생성된 사용자 정의 함수 'Get_S_성별'을 제거하시오.

DROP FUNCTION Get_S_성별;

DBMS 접속의 개요

웹 응용시스템의 구조

사용자는 웹 서버에 접속하여 데이터를 주고 받는다.
웹 서버는 많은 수의 서비스 요청을 처리하기 때문에 사용자가 대용량의 데이터를 요청하면 직접 처리하지 않고 WAS에게 해당 요청을 전달한다.
WAS는 수신한 요청을 트랜잭션 언어로 변환한 후 DBMS에 전달하여 데이터를 바든다. 이렇게 받은 데이터는 처음 요청한 웹 서버로 다시 전달되어 사용자에게까지 도달하게 된다.

웹 응용 시스템 이메일 사이트와 비교하면 이해가 쉽다. 예를 들어, 이메일을 확인하기 위해 사이트에 접속한다고 가정할 때, 사이트에 접속하기 위해 사용하는 웹 브라우저는 '웹 응용 프로그램'이고, 접속한 사이트에서 보여주는 웹 페이지의 내용은 '웹 서버'에서 송출되는 것이다. 로그인 후 이메일을 확인하기 위해 받은 편지함을 클릭하면 서버는 받은 편지 목록에 대한 요청을 'WAS'에게 보내고, 'WAS'는 DBMS로 부터 데이터를 가져와 '웹 서버'에 전달함으로써 사용자는 받은 편지 목록을 확인할 수 있다.

웹 서버와 WAS의 차이점
- 웹 서버(Web Server)
  - 웹 서버란 HTTP 프로토콜을 기반으로 클라이언트가 웹 브라우저에서 어떠한 요청을 하면 그 요청을 받아 정적 컨텐츠를 제공하는 서버이다. 정적 컨텐츠란 단순 HTML 문서, CSS, 이미지, 파일 등 즉시 응답 가능한 컨텐츠이다. 이 때 웹 서버가 정적 컨턴츠가 아닌 동적 컨텐츠를 요청 받으면 WAS에게 해당 요청을 넘겨주고, WAS에서 처리한 결과를 클라이언트에게 전달하는 역할도 해준다. 이러한 웹 서버에는 Apache, NginX(엔지넥스) 등이 있다.
- WAS(Web Application Server)
  - WAS란 DB 조회 혹은 다양한 로직 처리를 요구하는 동적 컨텐츠를 제공하기 위해 만들어진 Application 서버이다. HTTP 프로토콜을 기반으로 사용자 컴퓨터나 장치에 애플리케이션을 수행해주는 미들웨어로서, 주로 데이터베이스 서버와 같이 수행된다. WAS는 JSP, Servlet 구동환경을 제공해주기 때문에 서블릿 컨테이너 혹은 웹 컨테이너로 불린다. 이러한 WAS는 웹 서버의 기능들을 구조적으로 분리하여 처리하고자 하는 목적으로 제시되었다.
    분산 트랜젝션, 보안, 쓰레드 처리 등의 기능을 처리하는 분산 환경에서 사용된다. WAS는 프로그램 실행 환경과 DB 접속 기능을 제공하고, 여러 개의 트랜잭션을 관리 가능하다. 또한 비즈니스 로직을 수행할 수 있다. 이러한 WAS에는 Tomcat, JBoss, WebSphere 등이 있다.

💡 정리하면 웹 서버는 웹 페이지, 저용량 자료 등의 처리를 담당하고 WAS는 DBMS와 통신하며 대용량 파일이나 복잡한 로직 등의 처리를 담당한다.

DBMS 접속 기술

📌 DBMS 접속 기술은 DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등을 의미한다.

JDBC(Java DataBase Connectivity)
- JDBC는 Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API이다.
  - 1997년 2월 썬 마이크로 시스템에서 출시했다.
  - JDBC는 Java SE(Standard Edition)에 포함되어 있으며, JDBC 클래스는 java.sql, javax.sql에 포함 되어 있다.
  - 접속하려는 DBMS에 대한 드라이버가 필요하다.

💡 API(Application Programming Interface) : API는 응용 프로그램 개발 시 운영체제나 DBMS 등을 이용할 수 있도록 규칙 등에 대해 정의해 놓은 인터페이스를 의미한다.
💡 프레임워크(Framework) : 프레임워크는 본래 '뼈대', '골조'를 의미하는 용어로, 소프트웨어에서는 특정 기능을 수행 하기 위해 필요한 클래스나 인터페이스 등을 모아둔 집합체를 가리킨다.
💡 Java SE : Java 표준안으로서, Java의 문법과 기능들을 정의하는 명세서이다. 개발 도구인 JDK(Java Development Kit)에 포함되어 사용되며, JDBC의 기능들을 정의하는 클래스 파일들을 포함하고 있다.
💡 드라이버 : 다른 장치나 시스템을 제어하는데 사용되는 프로그램을 의미한다.

ODBC(Open Database Connectivity)
- ODBC는 데이터베이스에 접근하기 위한 표준 개방형 API로 개발 언어에 관계없이 사용할 수 있다.
  - 1992년 9월 마이크로소프트에서 출시했다.
  - 프로그램 내 ODBC 문장을 사용하여 MS-Access, DBase, DB2, Excel 등 다양한 데이터베이스에 접근할 수 있다.
  - ODBC도 접속하려는 DBMS에 맞는 드라이버가 필요하지만, 접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용하여 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해 주므로 DBMS의 종류를 몰라도 된다.
MyBatis
- MyBatis는 JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크이다.
  - JDBC로 데이터베이스에 접속하려면 다양한 메소드를 호출하고 해제해야 하는데, MyBatis는 이를 간소화 했고 접속 기능을 더욱 강화하였다.
  - MyBatis는 SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행한다.
  - MyBatis는 SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 많이 사용된다.

SQL Mapping : SQL로 호출되는 테이블이나 열 데이터를 개발하려는 언어의 객체에 맞도록 변환하여 연결하는 것을 의미한다.

동적 SQL(Dynamic SQL)

📌 동적 SQL은 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것으로, 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있다.

동적 SQL은 사용자로부터 SQL문의 일부 또는 전부를 입력 받아 실행할 수 있다.
동적 SQL은 값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없다.
동적 SQL은 응용 프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없다.
동적 SQL은 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.

💡 동적 SQL은 쉽게 말해 사용자가 응용 프로그램을 실행 시킨 후 입력란에 SQL을 직접 입력하여 결과를 확인할 수 있는 것을 말한다.
💡 NVL 함수 : NVL(A, B) 형태의 함수로, A가 NULL인 경우 B를 반환하고 아니면 A를 반환한다. 동적 SQL에서는 원하는 조건에 따라 자유롭게 SQL 문을 바꿀 수 있으므로 NVL함수 없이 SQL문을 구성하는 것이 가능하다.
💡 프리컴파일(Precompile) : 프리컴파일은 고급언어를 기계어로 번역하는 컴파일(Compile) 전에 수행하는 작업으로, 필요한 라이브러리를 불러오거나 코드에 삽입된 SQL문을 DB와 연결하는 작업을 수행하는 것을 의미한다.

정적 SQL VS 동적 SQL
- 정적 SQL은 SQL 코드를 변수에 담지 않고 코드 사이에 직접 기술한 SQL문으로 동적 SQL과의 차이점은 다음과 같다.

	정적 SQL(Static SQL)	동적 SQL(Dynamic SQL)
SQL 구성	커서(Cursor)를 통한 정적 처리	문장열 변수에 담아 동적 처리
개발 패턴	커서의 범위 안에서 반복문을 활용하여 SQL 작성	NVL 함수 없이 로직을 통해 SQL 작성
실행 속도	빠름	느림
사전 검사	가능	불가능

💡 커서(Cursor) : SQL문의 실행 결과로 반환된 복수 개의 튜플들에 접근할 수 있도록 해주는 기능을 의미한다.