데이터베이스 10주차

함수와 프로시저의 차이

	함수	프로시저
실행 장소	클라이언트	서버
속도	느림	빠름
리턴	단일 리턴	여러 개 리턴 가능

함수

함수 생성

DELIMITER $$ /* 구분 문자를 세미콜론(;)에서 $$로 바꾸기 */
CREATE FUNCTION testDate(date DATE) /* testDate라는 이름을 가진 함수 생성 */
RETURNS varchar(20) /* 함수는 문자열을 리턴 */
DETERMINISTIC /* 입력값이 동일하면 출력값이 항상 동일하다는 것을 인지 1번만 함수를 호출(함수 호출의 결과값이 캐싱됨) */
BEGIN /*함수 내용 시작*/
     DECLARE changeDate varchar(20); /* 함수 안에서 사용할 변수 선언, 뒤에 DEFAULT붙여서 초기화 가능 */
     SET changeDate = NULL; /* 변수 초기화(초기화 생략 시 NULL) */
     SET changeDate = DATE_FORMAT(date, '%Y%m&d%H%i%s'); /* 변수를 날짜 타입으로 변환 */
     RETURN changeDate; /* 변수 changeDate 반환 */
END $$ /*함수 내용 끝과 구분문자 $$*/

DELIMITER ; /*끝난 후 다시 구분문자를 세미콜론으로 바꿔줘야한다.*/

DATE_FORMAT(날짜, 형식): 날짜를 지정한 형식으로 출력
%Y	4자리 년도	%y	2자리 년도
%M	긴 월(영문)	%b	짧은 월(영문)
%m	2자리 월	%c	월(한자리는 한자리)
%d	2자리 일	%e	일(한자리는 한자리)
%W	긴 요일 이름(영문)	%a	짧은 요일 이름(영문)
%I	시간(12시간)	%H	시간(24시간)
%i	분	%S	초
%T	시간:분:초	%r	시간:분:초 AM/PM

/* CONCAT */
SET 변수 = CONCAT("문자열1", "문자열2");
또는
SELECT CONCAT("문자열1", "문자열2") INTO 변수;

/*IF문*/
IF(조건) THEN
	실행할 문장;
ELSEIF (조건) THEN
	실행할 문장;
ELSE
	실행할 문장;
END IF /*IF문 종료*/

함수 삭제

DROP function 함수명

함수 호출

SELECT 함수명(매개인자)

---

프로시저

프로시저 생성

DELIMITER $$
CREATE PROCEDURE TEST_PROC(IN PARAM_ID INT, IN PARAM_NAME VARCHAR(20))
BEGIN
    SELECT * FROM animals WHERE ID = PARAM_ID;
    SELECT * FROM pet WHERE name = PARAM_NAME;
END $$

DELIMITER ;

/* 반복문 */

/* While문 */
while문 이름:WHILE 조건 DO 
	실행할 문장;
    ITERATE while문 이름; /*== continue*/
    LEAVE while문 이름; /*반복문 탈출*/
END WHILE;

/* REPEAT문(do while느낌) */
리피트이름:REPEAT
	실행할 문장;
    LEAVE 리피트이름; /*반복문 탈출 */
	UNTIL 조건
END REPEAT;

/* LOOP문 */
루프이름:LOOP
	실행할 문장
    LEAVE 루프이름; /*반복문 탈출*/
END LOOP;

프로시저 삭제

DROP PROCEDURE 프로시저명;

프로시저 호출

CALL 프로시저명(매개인자);

---

프로시저 변수 전달 방식(스코프)

IN(기본) - 매개인자의 값이 프로시저로 전송되지만 반환은 안됨

OUT - 매개인자의 값이 프로시저로 전송되지 않지만 프로시저에서 수정된 값 반환

INOUT - 매개인자의 값이 프로시저로 전송된 후 반환

프로시저 보안 타입

DEFINER(기본) - 프로시저를 생성한 사용자의 권한(관리자 계정)

INVOKER - 실행하는 사용자의 권한 (보안이 강력)

---

함수 - 구구단

DELIMITER $$ #구분문자를 세미콜론(;)에서 $$로 수정
CREATE FUNCTION FUNC_GUGUDAN() #구구단 함수 생성
RETURNS varchar(1000) #리턴 값 설정
DETERMINISTIC #동일 값일 경우 함수를 한 번만 호출
BEGIN #함수 정의 시작
	DECLARE str varchar(1000) DEFAULT '';  #리턴 할 문자열 선언 후 초기화
	DECLARE num1 INTEGER DEFAULT 1; #구구단 첫 번째 선언 후숫자 1로 초기화
	DECLARE num2 INTEGER DEFAULT 1; #구구단 두 번째 선언 후 숫자 1로 초기화
    
	WHILE(num1 < 10) DO #num1이 10이 될 때 까지
		WHILE(num2 < 10) DO #num2가 10이 될 때 까지
        		#원래 있던 문자열에 num1 X num2 = num1*num2 추가로 저장
			SET str = CONCAT(str, num1, "X", num2, "=", num1 * num2, '\t');
			SET num2 = num2 + 1; #num2에 1씩 더해주기
		END WHILE; #내부 WHILE문 종료
		SET num2 = 1; #두 번째 숫자
		SET num1 = num1 + 1; #num1에 1씩 더해주기
		SET str = CONCAT(str, '\n'); #보기 좋도록 단마다 줄바꿈 해주기
	END WHILE; #외부 WHILE문 종료
	RETURN str; #str반환
END $$ #함수 정의 끝과 구분문자 $$

DELIMITER ; #터미널의 경우 다시 세미콜론으로 구분문자를 바꿔주기 위해 사용

프로시저 - 구구단

DELIMITER $$ #구분문자를 세미콜론(;)에서 $$로 수정
CREATE PROCEDURE PROC_GUGUDAN() #PROC_GUGUDAN() 프로시저 생성
BEGIN #프로시저 정의 시작
    DECLARE str varchar(1000); #str 변수 생성
    DECLARE num1 int; #num1 변수 생성
    DECLARE num2 int; #num2 변수 생성
    SET str = ''; #str변수 초기화
    SET num1 = 1; #num1 변수 1로 초기화
    
    WHILE(num1 < 10) DO #num1 이 9가 될 때 까지
        SET num2 = 1; #num2 변수 1로 초기화
        WHILE(num2 < 10) DO #num2가 9가 될 때 까지
            #원래 있던 문자열에 num1 X num2 = num1*num2 추가로 저장
    	    SET str = CONCAT(str, num1, 'X', num2, '=', num1*num2, '\t');
            SET num2 = num2 + 1; #num2 값 1증가
        END WHILE; #WHILE문 종료
        SET num1 = num1 + 1; #num1 값 1 증가
        SET str = CONCAT(str, '\n'); #단이 끝날 때 마다 줄바꿈
	END WHILE; #WHILE문 종료
    SELECT str; #str 출력
END $$ #프로시저 정의 끝과 구분문자$$

DELIMITER ; #터미널의 경우 다시 세미콜론으로 구분문자를 바꿔주기 위해 사용