멋쟁이 사자처럼 24회 ( 04 / 02 )

🦁멋쟁이 사자처럼 15기/4월달 수업 내용 정리

멋쟁이 사자처럼 24회 ( 04 / 02 )

코딩하는 하마 2025. 4. 2. 16:48

[ 학습 목표 ]

1. 프로시저(STORED PROCEDURE) 의 구조와 사용법을 이해하고, 입력 (IN)출력 (OUT) 파라미터를 통해 반복적 작업을 자동화할 수 있다.

2. 사용자 정의함수(UDF:USER DEFINED FUNCTION)를 생성하고 호출하는 방법을 익혀, 쿼리 내에서 재사용 가능한 로직을 구현할 수 있다.

3. 인덱스(INDEX)의 개념과 종류(PRIMARY, UNIQUE , COMPOSITE 등)를 이해하고, 조회 성능 향상을 위해 적절히 설계할 수 있다.

4. EXPLAIN 명령어를 활용하여 인덱스가 적용되는 쿼리 실행 계획을 분석하고, 쿼리 최적화를 위한 인덱스 활용 방법을 설명할 수 있다.

프로시저

1) 매개 변수를 받는 프로시저

ex)

-- `PRO02_DELETE`(IN E_NO INT) 프로시저를 작성하여, 사원 번호를 입력받아 해당 사원을 삭제해보자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO01_INSERT`()
BEGIN
	INSERT INTO EMP_TEST(EMPNO, ENAME , DEPTNO) VALUES(1,'111',10), (2,'222',20) , (3,'333',30);
    COMMIT;
END

-- 프로시저 호출하기
CALL PRO01_INSERT();

ex)

-- `PRO03_UPDATE`(IN E_NO INT, IN E_NAME VARCHAR(50)) 프로시저를 작성하여, 사번에 해당하는 사원의 이름을 수정하자.
-- 사번에 해당하는 사원의 이름을 수정하자. 
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO03_UPDATE`(IN E_NO INT , IN E_NAME VARCHAR(50))
BEGIN
	UPDATE EMP_TEST 
    SET ENAME = E_NAME 
    WHERE E_NO;
    COMMIT;
END

-- 호출하기
CALL PRO03_UPDATE(2,'홍길동');

ex)

-- `PRO04_SELECT`(IN EMP_NAME VARCHAR(50)) 프로시저를 작성하여, 사원 이름으로 해당 사원의 정보를 조회하자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO04_SELECT`(IN EMP_NAME VARCHAR(50))
BEGIN
	SELECT * 
    FROM EMP_TEST
    WHERE ENAME = EMP_NAME;
    COMMIT;
END

-- 호출하기
SET @NAME = 'FORD';
CALL PRO04_SELECT(@NAME);

ex)

-- `PRO05_COUNT`(OUT CNT INT) 프로시저를 작성하여, EMP_TEST 테이블의 총 사원 수를 반환하자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO05_COUNT`(OUT CNT INT)
BEGIN
	SELECT COUNT(*) INTO CNT
    FROM EMP_TEST;
    COMMIT;
END

-- 호출하기
CALL PRO05_COUNT(@RES); -- 프로시저 호출 시 OUT으로 리턴되는 값을 받을 변수를 선언 
SELECT @RES; -- 개수 출력

2) if 프로시저 작성하기

ex)

-- `PRO07_IF`(IN ENO INT) 프로시저를 작성하여,
--      사원이 존재하면 해당 사원의 월급을 2배로 인상하고, 
--      존재하지 않으면 메시지를 출력하자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO07_IF`(IN ENO INT)
BEGIN
	-- 조건 TRUE FALSE 판정할 수 있는 변수 
    DECLARE FW INT; 
    
    -- 사원의 존재 유무 확인 
    SELECT COUNT(*) INTO FW
    FROM EMP_TEST
    WHERE EMPNO = ENO;
    
    -- 사원이 존재할 경우 봉급 2배 증가
    IF FW = 1 THEN 
    UPDATE EMP_TEST 
    SET SAL = SAL*2
    WHERE EMPNO = ENO;
    
    ELSE 
    -- 사원이 존재 하지 않을 경우 메시지 출력
    SELECT "사원이 존재 하지 않음" AS MESSAGE;
    
    END IF;
    
END

-- 프로시저 호출하기
SET @ENO = 7963;
CALL PRO0_IF(@ENO);

ex)

-- `PRO08_IF`(IN IN_SAL DECIMAL) 프로시저를 작성하여,
--      입력받은 월급보다 많이 받는 사원의 이름과 급여를 출력하되, 
--      존재하지 않으면 메시지를 출력하자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO08_IF`(IN IN_SAL DECIMAL(10,2))
BEGIN
	-- 조건 TRUE , FALSE 판정 변수
     DECLARE CNT INT;
     
     -- 입력 받은 월급보다 많이 받는 사원 수의 계산
     SELECT COUNT(*) INTO CNT
     FROM EMP_TEST
     WHERE SAL >= IN_SAL;
     
     IF CNT >=0 THEN 
     SELECT ENAME , SAL 
     FROM EMP_TEST
     WHERE SAL >= IN_SAL;
     
     ELSE 
     SELECT "입력한 월급보다 더 높은 사원이 존재 하지 않음" AS MESSAGE ;
     END IF;
        
END

-- 프로시저 호출하기
CALL PRO08_IF(1500);

3) 예외 프로시저

참고 자료 : https://dev.mysql.com/doc/refman/8.4/en/declare-condition.html

MySQL :: MySQL 8.4 Reference Manual :: 15.6.7.1 DECLARE ... CONDITION Statement

15.6.7.1 DECLARE ... CONDITION Statement DECLARE condition_name CONDITION FOR condition_value condition_value: { mysql_error_code | SQLSTATE [VALUE] sqlstate_value } The DECLARE ... CONDITION statement declares a named error condition, associating a name

dev.mysql.com

[예외 프로시저 형식]

DECLARE condition_name CONDITION FOR condition_value
condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

1) DECLARE ... HANDLER : CONTINUE, EXIT, UNDO 핸들러를 통해 오류 발생 후의 흐름 제어

-DECLARE CONTINUE HANDLER FOR condition action: 지정된 조건이 발생하면 현재 실행 중인 구문을 완료하고 다음 구문을 계속 실행

-DECLARE EXIT HANDLER FOR condition action: 지정된 조건이 발생하면 현재 블록(BEGIN ... END)을 즉시 종료

-DECLARE UNDO HANDLER FOR condition action: (트랜잭션 스토리지 엔진에서) 지정된 조건이 발생하면 현재 트랜잭션을 롤백

condition 부분 지정 옵션

= SQLSTATE value: 특정 SQLSTATE 값 (5자리 문자열, 예를 들어 '45000').

=SQLEXCEPTION: 모든 SQ LSTATE 값 중에서 일반적인 오류를 나타내는 클래스

=SQLWARNING: 모든 SQLSTATE 값 중에서 경고를 나타내는 클래스

= NOT FOUND: 커서 작업에서 더 이상 행이 없을 때 발생하는 조건 (SQLSTATE '02000').

=specific_error_code: 특정 MySQL 오류 코드 (정수).

2) SIGNAL SQLSTATE :SQL 예외 발생

3) GET DIAGNOSTICS:발생한 오류에 대한 자세한 정보(SQLSTATE, 오류 코드, 메시지 등)확인

ex)

-- `PRO08_IF`(IN IN_SAL DECIMAL) 프로시저를 작성하여,
--      입력받은 월급보다 많이 받는 사원의 이름과 급여를 출력하되, 
--      존재하지 않으면 메시지를 출력하자.
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO09_IF`(IN RATE DECIMAL(5,2))
BEGIN
	-- 비율이 0보다 작으면 예외 발생 : SIGNAL _예외 발생 키워드 
    IF RATE <= 0 THEN 
		SIGNAL SQLSTATE '45000'  -- 일반적인 사용자 오류 코드
        SET MESSAGE_TEXT = '급여 인상 비율은 0보다 커야 합니다.';
	END IF;
    
    -- 급여 인상 처리 
    UPDATE EMP_TEST 
    SET SAL = SAL + (SAL * RATE);
    
    -- 변경 결과 확인 
    SELECT ENAME, SAL FROM EMP_TEST;
END


-- 프로시저 호출하기
CALL PRO08_IF(1500);

ex)

-- PRO10(1);실행했을 경우 트랜잭션 구현코드를 추가해서 결과를 출력해보자. 
CREATE DEFINER=`hippo`@`%` PROCEDURE `PRO10_DO_INSERT`(IN VAL INT)
BEGIN
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN 
    ROLLBACK;
    -- 사용자 예외 정의 발생
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '오류 발생: INSERT 실패로 롤백이다';
    END;
	    -- 트랜잭션 시작 
    START transaction;
    INSERT INTO T1 (INT_COL) VALUES(VAL);
    COMMIT;
    
    -- 성공 메세지 출력 
    SELECT CONCAT('성공 ',VAL ,' 추가했다.') AS MESSAGE;
    
END

-- 프로시저 호출하기
CALL PRO10_DO_INSERT(1);

FUNCTIONS

함수는 위 그림에 표시해둔 부분을 우클릭 하여 작성하면 된다. 그럼 예제를 살펴보겠다.

ex)

-- 사원의 이름을 출력하고 월급 연봉을 출력하는 함수를 만들자 
CREATE DEFINER=`hippo`@`%` FUNCTION `FUN02`(SAL DECIMAL(5,2)) RETURNS decimal(10,0)
    DETERMINISTIC
BEGIN

RETURN SAL * 12;
END

-- 호출하기
SELECT ENAME, SAL , FUN02(SAL) 
FROM EMP_TEST ;

ex)

-- 부서번호와 부서이름을 출력하자.
CREATE DEFINER=`hippo`@`%` FUNCTION `FUN04`(D_DEPTNO INT) RETURNS varchar(100) CHARSET utf8mb4
    READS SQL DATA
BEGIN
    DECLARE D_NAME VARCHAR(100);
    SELECT DNAME INTO D_NAME FROM  DEPT WHERE DEPTNO =D_DEPTNO;
    RETURN D_NAME;
END

-- 호출하기
SELECT  EMPNO , ENAME ,  FUN04(DEPTNO) 
FROM EMP_TEST;

인덱스

1) 인덱스란?

테이블 안의 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터베이스 객체이다.
SQL을 실행할 때, 디스크 접근 횟수를 줄여 검색 속도를 높이기 위해 인덱스를 사용한다.
B-트리를 이용하면 모든 데이터에 대한 일정 수준의 검색 시간을 보장하는 장점이 있다.
테이블 행이 입력되거나 수정될 때마다 << 인덱스가 재구성 >>되어 데이터를 검색할 때는 효율적이지만
데이터를 추가하고 수정할 때는 인덱스 관리 때문에 시간이 더 걸린다.

2) 인덱스 생성이 바람직한 경우

a. 기본키와 외래키의 경우, 인덱스 생성이 바람직하다. 대부분의 DBMS는 기본키에 대해서 자동으로 인덱스를 생성한다.
b. WHERE 절 조건식에 자주 사용되는 테이블 열의 경우, 인덱스 생성이 바람직하다.
c. 조인 조건식에 자주 사용되는 테이블 열도 인덱스 생성이 바람직하다.
d. 하나의 테이블에 3~5개 정도의 인덱스가 효과적이다.
e. 가변길이 문자형이나 실수형, 날짜형 열보다는 정수형, 고정길이 문자형 열에 인덱스를 생성하는 것이 바람직하다.
f. ORDER BY절이나 GROUP BY절에 자주 사용되는 열의 경우, 인덱스 생성을 고려할 수 있다.

3) 인덱스 생성이 바람직하지 않은 경우

a. 갱신이 빈번한 테이블 열의 경우, 인덱스가 바람직하지 않다.
b. 집계 함수, 내장 함수를 적용하여 열 값을 변형하는 경우, 인덱스가 효과적이지 않다.
c. 성별 같은 열처럼 도메인이 작아서 열의 선택도(selectivity)가 높을 경우, 인덱스가 바람직하지 않다.
d. 범위 검색을 하는 경우, 인덱스가 바람직하지 않다.
e. 테이블의 행 개수가 별로 없는 경우, 인덱스가 바람직하지 않다

'🦁멋쟁이 사자처럼 15기 > 4월달 수업 내용 정리' 카테고리의 다른 글

멋쟁이 사자처럼 27회 ( 04 / 08 ) (0)	2025.04.08
멋쟁이 사자처럼 26회 ( 04 / 07 ) (0)	2025.04.07
멋쟁이 사자처럼 25회 ( 04 / 04 ) (0)	2025.04.04
멋쟁이 사자처럼 24회 ( 04 / 03 ) (0)	2025.04.03
멋쟁이 사자처럼 23회 ( 04 / 01 ) (0)	2025.04.01

현재글멋쟁이 사자처럼 24회 ( 04 / 02 )

codinghippo702

MongoDB, 평균 (1546), 알파벳 개수 (10808), DB 연동, CallableStatement, 백준, URI, 구간합, Partition, 구간합 구하기, etc thread, 에디터 (1406), Aggregation, 배열과 리스트, threadgroup, Virtual Thread, 숫자의 합 (11720), nosql, 백준 11659, crud,

Today :
Yesterday :

codinghippo702