[DB] SQL 응용 1 (내장 함수 | 저장 프로시저)

1. 내장함수
2. 저장 프로시저
3. 트리거
4. 사용자 정의 함수

• 트래젝션내장 함수의 종류와 사용 방법을 알아본다.
• 저장 프로시저의 개념과 활용 방법을 이해한다.

내장함수

SQL 내장 함수

• 내장함수와 사용자 정의 함수로 구분한다.
• 열 이름이나 상수 값을 입력받아 값 하나를 결과로 반환
• 기본 연산 함수, 시스템 정보 제공 함수, 데이터 가공 함수 등 지원
• SELECT WHERE UPDATE SET 절 등에서도 사용 가능

 

숫자 함수	함수	기능
	ABS	입력 값의 절대 값 반환
	CEIL	입력 값보다 큰 정수 중에서 가장 작은 수 반환
	FLOOR	입력 값보다 작은 정수 중에서 가장 큰 수 반환
	ROUND	입력 값을 소수점이하 자리수까지 반올림한 값 반환
	FORMAT	입력 값을 형식에 맞게 변환하여 반환

 

ex) 숫자 함수

문자 함수	함수	기능
	LENGHTH CHAR_LEGHTH	문자열의 길이(바이트수)를 반환  문자열의 길이(문자수)를 반환
	CONCAT	콤마로 구분된 문자열, 컬럼값들의 결합 반환
	LEFT/LIGHT	문자열의 왼쪽/오른쪽부터 길이만큼만 반환
	LTRIM/RTRIM	문자열의 왼쪽/오른쪽 공백을 제거하여 반환
	SUBSTIRING	문자열의 '위치'번째부터 '길이'개수만큼의 부분 문자열을 반환
	REPLACE	문자열의 일부를 치환하여 반환
	REPEAT	문자열을 반복 횟수만큼 반복하여 반환
	SYSDATE(),NOW()	현재 날짜와 시간을 반환
	CURRENT_DATE()	현재 날짜를 반환
	CURRENT_TIME	현재 시간을  반환
	YEAR MONTH DAY	입력 날짜의 연도/월/일 부분을 반환

문자함수

날짜-시간 함수	함수	기능
	HOUR MINUTE SECOND	입력시간의 시/분/초 부분을 반환
	LAST_DAY	입력 날짜의 해당 월의 마지막 날짜를 반환
	DATE_ADD	입력 날짜에서 중분 값 만큼  날/월/년을더한 날짜를 반환
	DATE_SUB	입력 날짜에서 감소 값 만큼  날/월/년을 뺀 날짜를 반환
	DATE_FORMAT	입력 날짜를 형식에 맞게 변환하여 반환 %Y : 4자리 연도 %y : 2자리 연도 %c : 월 (0~12) %m : 월(00~12) %M : 월(January~December) %b : 월(Jan~Dec) %d : 일(01~31) %e : 일 (0~31) %a :  주(Sun~Sat)

날짜/시간 함수

저장 프로시저

• 미리 작성해 데이터베이스 안에 저장한 SQL 문장들의 묶음
• 독립된 프로그램으로 DB안의 한 객체로 저장

장점 : 최적화된 SQL문을 미리 DB에 저장 가능, 복잡한 SQL문을 전달할 필요 X → 네트워크 부하 줄어듬, 여러 응용 프로그램과 공유 가능

 

CREATE PROCEDURE 문

CREATE PROCEDURE 프로시저 이름
BEGIN 
SQL 명령문;
END

예제 ) 과목 테이블에 데이터를 추가(입력 또는 수정)하는 저장 프로시저를 입력하시오.

DELIMITER //
CREATE PROCEDURE InsertOrUpdateCourse (
IN CourseNo VARCHAR(4),
IN CourseName VARCHAR(20),
IN CourseRoom char(3),
IN CourseDept VARCHAR(20),
IN CourseCredit INT)
BEGIN
DECLARE Count INT;
SELECT COUNT(*) INTO Count FROM 과목 WHERE 과목번호 = CourseNo ;

IF (Count = 0) THEN
insert into 과목(과목번호,이름, 강의실,개설학과, 시수)
values(CourseNo,CourseName,CourseRoom,CourseDept,CourseCredit);
else 
update 과목
set 이름 = CourseName, 강의실 = CourseRoom, 개설학과 = CourseDept, 시수 = CourseCredit WHERE 과목번호 = CourseNo;
end if;
end//
DELIMITER ;

저장 프로시저 호출
삽입 저장 프로시저의 호출	수정 저장 프로시저의 호출

검색 저장 프로시저의 생성 및 활용

예제) '수강' 테이블에서 중간 성적 혹은 기말 성적으로 특정 점수 이상을 받은 학생 수를 반환하는 'SelectAverageOfBestScore'프로시저를 작성하시오

프로시저 호출 및 삭제
검색 저장 프로시저의 호출	예제) 'SelectAverageOfBestScore' 저장 프로시저를 호출해 중간 혹은 기말 성적 중 95 점 이상인 학생수 검색
저장 프로시저의 삭제	DROP PROCEDURE 명령문의 형식