[정처기 필기] 「3」 | SQL 활용 - (4.2) 사용자 정의 함수, DBMS 접속 기술

💠기타/자격증

[정처기 필기] 「3」 | SQL 활용 - (4.2) 사용자 정의 함수, DBMS 접속 기술

튤 2024. 2. 13. 19:23

728x90

[정처기 필기] 「3」 | SQL 활용 - (4.2) 사용자 정의 함수, DBMS 접속 기술

「1」 소프트웨어 설계

「2」 소프트웨어 개발

> 「3」 데이터베이스 구축

- 논리 데이터베이스 설계, 물리 데이터베이스 설계, SQL 응용, > SQL 활용, 데이터 전환

「4」 프로그래밍 언어 활용

「5」 정보시스템 구축 관리

1 프로시저

2 트리거

> 3 사용자 정의 함수

> 4 DBMS 접속 기술

3. 사용자 정의 함수

사용자 정의 함수의 개요

SQL을 사용하여 일련의 작업을 연속적으로 처리하고, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL

- 데이터베이스에 저장, 별도로 실행되지 않고 DML문에 포함되어 DML문의 호출에 의해 실행

- 예약어 RETURN을 통해 값을 반환, 출력 파라미터가 없음

- INSET, DELETE, UPDATE 통해 테이블 조작 불가, SELECT로 조회 가능

- 프로시저를 호출하여 사용할 수 없음

- SUM( ), AVG( ) 같은 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도

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

사용자 정의 함수의 구성

프로시저와 유사, REUTURN만 추가

구성도

	사용자 정의 함수 DECLARE(필수) BEGIN(필수) - CONTROL - SQL - EXCEPTION - RETURN(필수) END(필수)

데이터 →		→ 반환

- DECLARE : 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입 정의하는 선언부

- BEGIN / END : 사용자 정의 함수의 시작, 종료

- CONTROL : 조건문 또는 반복문 삽입되어 순차적으로 처리

- SQL : SELECT문이 삽입되어 데이터 조회 작업 수행

- EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외 발생하면 처리 방법 정의

- RETURN : 호출 프로그램에 반환할 값, 변수 정의

사용자 정의 함수 생성

CREATE FUNCTION 명령어를 사용하여 사용자 정의 함수 생성

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

- OR REPLACE : 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체

- 파라미터

: IN - 호출 프로그램이 사용자 정의 함수에게 값 전달

: 매개변수명 - 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름 지정

: 자료형 - 변수의 자료형 지정

- 사용자 정의 함수 BODY : 코드 입력, BEGIN에서 END로 끝나며, 하나 이상의 SQL문

- RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

ex)

CREATE FUCTION Get_S_성별(i_성별코드 IN INT)

RETURN VARCHAR2

BEGIN

IF i_성별코드 = 1 THEN

RETURN '남자';

ELSE

RETURN '여자';

END IF;

END;

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

사용자 정의 함수 실행

DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

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

ex)

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

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

사용자 정의 함수 제거

DROP FUNCTION 명령어를 사용하여 사용자 정의 함수를 제거

DROP FUNCTION 사용자 정의 함수명;

ex)

DROP FUNCTION Get_S_성별;

// 생성된 사용자 정의 함수 'Get_S_성별'을 제거

4. DBMS 접속 기술

DBMS 접속의 개요

사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것

- 응용 시스템은 사용자로부터 매개변수를 전달받아 SQL 실행, DBMS로부터 전달받은 결과를 사용자에게 전달, 매개체 역할

- 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 접근

- 웹 응용 시스템은 웹 서버(웹 페이지, 저용량 자료 등 처리 담당)와 웹 어플리케이션 서버(WAS, DBMS와 통신, 대용량 파일, 복잡한 로직 등 처리 담당)를 통합하여 하나의 서버로 운용

>웹 응용 시스템의 구조<

	웹 응용 시스템
사용자 ↔	↔ 웹 서버 ↔	↔ WAS ↔	↔ DBMS

- 사용자는 웹 서버에 접속하여 데이터 주고받음

- 웹 서버는 대용량의 데이터가 요청되면 WAS에게 해당 요청 전달

- WAS는 수신한 요청을 트랜잭션 언어로 변환 후 DBMS에 전달하여 데이터를 받음, 받은 데이터는 처음 요청한 웹 서버로 다시 전달되어 사용자에게 도달

DBMS 접속 기술

DBMS에 접근하기 위해 사용되는 API, API의 사용을 편리하게 도와주는 프레임워크 등

JDBC(Java DataBase Connectivity)

: Java 언어로 다양한 데이터베이스에 접속하고 SQL문 수행할 때 사용되는 표준 API

: 썬 마이크로시스템에서 출시

: Java SE(Standard Edition)에 포함, java, sql, javax.sql에 포함

: 접속하려는 DBMS에 대한 드라이버 필요

ODBC(Open DataBase Conectivity)

: 표준 개방형 API, 개발 언어에 관계없이 사용

: 마이크로소프트에서 출시

: MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근

: 접속하려는 DBMS에 대한 드라이버 필요하지만, 접속하려는 DBMS의 인터페이스를 몰라도 SQL 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결

MyBatis

: JDBC 코드를 단순화하여 사용, SQL Mapping 기반 오픈 소스 접속 프레임워크

: 다양한 메서드의 호출, 해제를 간소화, 접속 기능 강화

: SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행

: SQL을 거의 그대로 사용 가능, SQL 친화적인 국내 환경에 적합

동적 SQL(Dynamic SQL)

개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리, 조건에 따라 동적으로 변경하여 처리

- 사용자로부터 SQL 문을 입력받아 실행

- 값이 입력되지 않을 경우에 사용하는 NVL 함수를 사용할 필요 없음

- 응용 프로그램 실행 시 SQL이 변형될 수 있으므로, 프리컴파일할 때 구문 분석, 접근 권한 확인 불가

- 정적 SQL에 비해 속도 느리지만, 다양한 조건 첨가 가능, 유연한 개발

>정적 SQL vs 동적 SQL<

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

출처 | <시나공> 정보처리기사 필기 2024 기본서 (길벗알앤디)

728x90