컴알못의 SQL 공부 (내장함수, 프로시저, 트리거, 사용자정의함수)

내장 함수

개념

다양한 사용자의 질의 요청을 처리하기 위해 MySQL이 SQL문 안에 제공하는 함수.

SQL 함수는 DBMS가 제공하는 내장 함수와 사용자가 직접 정의하는 사용자 정의 함수로 나뉘어집니다.

 

종류

숫자함수	ABS(A)	A의 절대값
	CEIL(A)	A의 반올림
	FLOOR(A)	A의 반내림
	ROUND(A, B)	A를 소수점 B자리값까지 반올림
	FORMAT(A, B)	A를 B형식으로 변환

문자함수	LENGTH(A)	A 문자열의 길이
	CONCAT(A)	A 문자열 그룹들을 결합
	LEFT/RIGHT(A, B)	A문자열을 왼/오른쪽 B 길이만큼 자름
	LTRIM/RTRIM(A)	왼,오른쪽 공백을 제거
	SUBSTRING(A, B, C)	A의 B번째부터 C길이만큼 자름
	REPLACE(A, B, C)	A의 B를 C로 치환
	REPEAT(A, B)	A를 B만큼 반복

날짜시간함수	SYSDATE(), NOW()	현재 날짜와 시간
	CURRENT_DATE()	현재 날짜
	CURRENT_TIME()	현재 시간
	YEAR(A), MONTH(A), DAY(A)	A날짜의 연, 월, 일
	HOUR(A), MINUTE(A), SECOND(A)	A시간의 시, 분, 초
	LAST_DAY(A)	A의 해당월의 마지막 날짜
	DATE_ADD(A, INTERVAL B DAY/MONTH/YEAR)	A날짜에 B만큼 더함
	DATE_SUB(A, INTERVAL B DAY/MONTH/YEAR)	A날짜에 B만큼 뺌
	DATE_FORMAT(A, B)	A날짜를 B형식으로 변환

위는 MySql이 제공하는 내장함수입니다.

 

저장 프로시저

개요

미리 작성해서 데이터베이스 안에 미리 저장한 SQL 문장들의 집합.

DBMS가 데이터베이스 관리를 위해 사용하는 기능들도 저장 프로시저로 제공되는 것.

 

장점

복잡한 SQL 작업을 함수처럼 인자만 전달하여 재사용이 가능.

여러 응용프로그램들이 저장프로시저를 공유 가능.

복잡한 SQL문을 전달할 필요가 없어 네트워크 부하가 줄어듬.

 

생성

CREATE PROCEDURE 프로시저이름 (매개변수)
BEGIN
	(SQL문)
END

주의

DELIMITER //
CREATE PROCEDURE 프로시저이름 (
    IN 매개변수이름 도메인,
    .
    .
    .
)
BEGIN
    DECLARE 내부지역변수 도메인;
    SELECT A INTO 내부지역변수 FROM 테이블 WHERE 검색조건문
    IF 조건식 THEN ... ELSE ... END IF
END
//
DELIMITER ;

• 프로시저 내부의 SQL문에 포함된 ; 때문에 프로시저 정의가 중간에 끝날 위협이 있으니 DELIMITER로 ; 의 역할을 //로 바꾼 뒤에 프로시저가 종료되면 다시 ; 로 되돌립니다.
• 프로시저가 실행될 때 입력한 값이 매개변수로 전달되게 하는게 IN의 역할이고 반대로 프로시저가 밖으로 매개변수를 내보내는 역할을 하는게 OUT의 역할입니다.
• DECLARE를 이용하여 SQL문의 내부지역변수를 정의할 수 있습니다.
• INTO를 활용하여 검색 결과값을 변수 안에 할당할 수 있습니다.
• IF ~ THEN ~ ELSE ~ END IF가 프로시저 안의 조건문 문법입니다.

호출

CALL 프로시저이름 (매개변수값들);

 

 

삭제

DROP PROCEDURE 프로시저이름;

 

트리거

개요

약속된 이벤트가 발생할 때 자동실행되는 프로시저.

즉, 이벤트를 탐지하여 SQL문을 자동으로 실행시킵니다.

트리거가 정의된 테이블 또는 뷰에 INSERT, UPDATE, DELETE와 같은 이벤트가 발생하면 방아쇠를 당기듯 자동 실행됩니다.

 

목적

데이터 변경시 부수적으로 필요한 작업들을 자동처리하기 위해 사용.

무결성을 유지하거나 업무 규칙을 적용하기 위해 사용합니다.

 

생성

CREATE TRIGGER 트리거명
(BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON 테이블 FOR EACH ROW
BEGIN
	(SQL문)
END

형식은 위와 같습니다.

• BEFORE|AFTER 옵션은 사전 트리거인가 사후 트리거인가를 정의합니다.

     - 사전 트리거 : 특정 명령문 실행 전에 처리

     - 사후 트리거 : 특정 명령문 실행 후에 처리

 

예제

DELIMITER //
CREATE TRIGGER 트리거이름
AFTER INSERT ON 테이블 FOR EACH ROW
BEGIN
   IF (NEW.속성 = A) THEN
   SQL문
   ELSEIF (NEW.속성 = B) THEN
   SQL문
   END IF;
END;
//
DELIMITER ;

트리거는 이벤트 발생 후 변경 후의 속성값에는 NEW.속성으로, 변경 전의 속성 값에는 OLD.속성으로 접근할 수 있습니다.

FOR EACH ROW는 해당테이블에 INSERT문을 통해 각 행이 입력될 때마다 입력된 직후 자동으로 실행된다는 의미입니다.

 

실행

설정한 테이블에 설정한 이벤트를 실행하면 자동으로 실행됩니다.

 

삭제

DROP TRIGGER 트리거이름;

 

사용자 정의 함수

개요

DBMS가 기본으로 제공하는 내장함수 이외에 사용자가 직접 정의한 함수.

검색문이나 프로시저 안에 호출하여 사용가능합니다.

함수이기 때문에 특정값을 받고 특정값을 반환합니다.

 

반환되는 결과값에 의해 스칼라 함수 또는 테이블 함수로 분류.

스칼라 함수 : 하나의 값 또는 NULL값을 반환

테이블 함수 : 테이블을 반환.

 

목적

제한된 SQL 명령문의 기능을 확장시키고 명령문 작성의 편의성을 향상.

 

생성

CREATE FUNCTION 함수명 (매개변수명 도메인)
RETURNS 반환값도메인
BEGIN
   (SQL문);
   RETURN 반환값;
END

 

 

활용

내장함수와 사용법은 같습니다.

 

삭제

DROP FUNCTION 함수명;

 

 

프로시저, 트리거, 사용자 정의 함수의 비교

공통점

• 데이터베이스의 객체로 저장
• DBMS 내부에 저장
• SQL문으로 작성

차이점

• 프로시저는 CALL문으로 직접 호출하여 실행
• 트리거는 이벤트 실행시 자동 호출 실행
• 사용자 정의 함수는 SQL문 안에 포함되어 실행
• 프로시저는 반환값 제공이 선택 옵션
• 트리거는 반환값이 없음
• 사용자 정의 함수는 반환값이 필수

 

 

목차