[LG U+ 유레카 4기] WEEK 07 - 데이터베이스 (6)

저장 프로그램, 계정/권한 관리

---

1. 학습 흐름

• 데이터 모델링 실습 (스포츠점)
• Stored Procedure (저장 프로시저)
• 사용자 정의 함수
• 커서 (Cursor)
• 계정 생성/삭제
• 권한 부여/취소 (GRANT / REVOKE)
• Role

---

2. Stored Procedure (저장 프로시저)

프로그램 로직을 DBMS에 저장해두고 호출해서 쓰는 객체다. MySQL에 저장되므로 저장 프로그램이라고 한다.

프로시저 안에서 ;이 문장의 끝으로 인식되므로, 프로시저 전체의 끝을 구분하기 위해 DELIMITER를 바꿔야 한다.

-- 기본 구조
DELIMITER //
CREATE PROCEDURE 프로시저이름([IN|OUT|INOUT] 매개변수 타입)
BEGIN
    -- SQL 로직
END;//
DELIMITER ;

-- 호출 (SELECT로 조회 불가, CALL만 가능)
CALL 프로시저이름(인자);

-- 삭제
DROP PROCEDURE IF EXISTS 프로시저이름;

매개변수 종류

• IN: 입력 (기본값, 생략 시 IN)
• OUT: 출력. 프로시저 외부에서 세션 변수(@변수)로 확인
• INOUT: 입력과 출력 모두

기본 프로시저

-- 매개변수 없는 프로시저
DELIMITER //
CREATE PROCEDURE emplist()
BEGIN
    SELECT * FROM emp;
END;//
DELIMITER ;

CALL emplist;

IN 매개변수 - INSERT

DELIMITER //
CREATE PROCEDURE insertbook(
    IN pbookid    INTEGER,
    IN pbookname  VARCHAR(40),
    IN ppublisher VARCHAR(40),
    IN pprice     INTEGER
)
BEGIN
    INSERT INTO book(bookid, bookname, publisher, price)
    VALUES (pbookid, pbookname, ppublisher, pprice);
END;//
DELIMITER ;

CALL insertbook(13, '스포츠과학', '마당과학서적', 25000);

OUT 매개변수 - 결과 반환

DELIMITER //
CREATE PROCEDURE averageprice(OUT avgVal INTEGER)
BEGIN
    SELECT AVG(price) INTO avgVal FROM book;
END;//
DELIMITER ;

-- 세션 변수로 결과 확인
CALL averageprice(@myValue);
SELECT @myValue;

지역 변수 vs 세션 변수

지역 변수: DECLARE 변수명 타입 [DEFAULT 값];   → 프로시저 내부에서만 사용
세션 변수: @변수명                               → 접속 세션 내 어디서나 사용 가능

값 대입:
  SET 변수명 = 값;
  SELECT ... INTO 변수명;   (SELECT 결과를 변수에 대입)

IF 조건문 - INSERT or UPDATE

DELIMITER //
CREATE PROCEDURE bookinsertorupdate(
    IN pbookid INTEGER, IN pbookname VARCHAR(40),
    IN ppublisher VARCHAR(40), IN pprice INTEGER
)
BEGIN
    DECLARE mycount INTEGER DEFAULT 0;
    SELECT COUNT(*) INTO mycount FROM book WHERE bookname = pbookname;
    IF mycount = 0 THEN
        INSERT INTO book(bookid, bookname, publisher, price)
        VALUES (pbookid, pbookname, ppublisher, pprice);
    ELSE
        SET sql_safe_updates = 0;
        UPDATE book SET price = pprice WHERE bookname = pbookname;
    END IF;
END;//
DELIMITER ;

반복문 (REPEAT)

DELIMITER //
CREATE PROCEDURE dorepeat(IN p1 INTEGER, OUT x INTEGER)
BEGIN
    SET x = 0;
    REPEAT
        SET x = x + 1;
    UNTIL x > p1
    END REPEAT;
END;//
DELIMITER ;

CALL dorepeat(1000, @x);
SELECT @x;  -- 1001

---

3. 커서 (Cursor)

SELECT 결과를 한 행씩 순차적으로 처리하는 기능이다. JDBC의 ResultSet이 내부적으로 커서로 구현되어 있다. 한 줄씩 처리하므로 성능이 느린 편이다.

DELIMITER //
CREATE PROCEDURE Interest()
BEGIN
    DECLARE myInterest INTEGER DEFAULT 0;
    DECLARE price INTEGER;
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;

    -- 1. CURSOR 선언
    DECLARE InterestCursor CURSOR FOR
        SELECT saleprice FROM Orders;

    -- NOT FOUND 시 종료 변수 설정
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE;

    -- 2. OPEN
    OPEN InterestCursor;

    cursor_loop: LOOP
        -- 3. FETCH (한 행 읽기)
        FETCH InterestCursor INTO price;
        IF endOfRow THEN LEAVE cursor_loop; END IF;

        -- 4. 처리 로직
        IF price >= 30000 THEN
            SET myInterest = myInterest + price * 0.1;
        ELSE
            SET myInterest = myInterest + price * 0.05;
        END IF;
    END LOOP cursor_loop;

    -- 5. CLOSE
    CLOSE InterestCursor;
    SELECT CONCAT('전체 이익 금액 = ', myInterest);
END;//
DELIMITER ;

CALL Interest();

커서 동작 흐름: 선언 → OPEN → FETCH(반복) → CLOSE

---

4. 사용자 정의 함수

개발자가 직접 만들어서 SQL 내부에서 사용할 수 있는 함수다. 단일 값을 반환한다.

-- 형식
CREATE FUNCTION 함수명(매개변수 타입)
RETURNS 반환타입
[옵션]
BEGIN
    RETURN 값;
END

-- 옵션 종류
DETERMINISTIC     -- 같은 입력 = 같은 결과 (캐싱 가능)
NOT DETERMINISTIC -- 결과가 바뀔 수 있음 (NOW() 등)
READS SQL DATA    -- SELECT만 함
MODIFIES SQL DATA -- INSERT/UPDATE 가능

-- 가격에 따른 인상 요율 계산 함수
DELIMITER //
CREATE FUNCTION fnc_Interest(price INTEGER)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE result INTEGER;
    IF price >= 30000 THEN SET result = price * 1.1;
    ELSE SET result = price * 1.05;
    END IF;
    RETURN result;
END;//
DELIMITER ;

SELECT custid, saleprice, fnc_Interest(saleprice) AS interest FROM Orders;

-- NULL 처리 함수 (Oracle의 NVL 구현)
DELIMITER //
CREATE FUNCTION nvl(v1 VARCHAR(200), v2 VARCHAR(200))
RETURNS VARCHAR(200)
DETERMINISTIC
BEGIN
    IF v1 IS NULL THEN RETURN v2;
    ELSE RETURN v1;
    END IF;
END;//
DELIMITER ;

SELECT empno, nvl(comm, 0) AS comm FROM emp;
SELECT empno, nvl(job, '대기') AS job FROM emp;

MySQL 타입 처리가 유연해서 숫자나 날짜를 문자열로 처리해도 오류가 나지 않는다.

---

5. 계정 관리

-- 계정 생성
CREATE USER 계정명@접속위치 IDENTIFIED BY '비밀번호';

-- 접속 위치
-- localhost  : 내부 접속만 허용
-- %          : 외부 접속 허용
-- %.domain   : 특정 도메인만 허용

CREATE USER mdguest@'localhost' IDENTIFIED BY 'mdguest';
CREATE USER uplus@'%' IDENTIFIED BY 'uplus';

-- 계정 목록 확인
SELECT * FROM mysql.User;

-- 계정 삭제
DROP USER uplus@'%';

MySQL vs Oracle 차이:

• MySQL: 계정 생성 후 접속은 됨. 권한이 없어서 아무것도 못할 뿐.
• Oracle: 계정 생성만으로는 접속도 안 됨. GRANT CREATE SESSION이 필요.

---

6. 권한 관리 (GRANT / REVOKE)

-- 권한 부여
GRANT 권한 ON 객체 TO 사용자;

-- 특정 DB의 모든 테이블에 모든 권한 부여
GRANT ALL PRIVILEGES ON madangdb.* TO 'mdguest'@'localhost';

-- 모든 DB에 모든 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'mdguest'@'localhost';

-- 특정 권한만 부여
GRANT SELECT, INSERT, UPDATE ON madangdb.* TO 'testrole';

-- 다른 계정에게도 권한을 줄 수 있는 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'mdguest'@'localhost' WITH GRANT OPTION;

-- 권한 취소
REVOKE ALL PRIVILEGES ON *.* FROM 'mdguest'@'localhost';
REVOKE GRANT OPTION ON *.* FROM 'mdguest'@'localhost';

-- 현재 사용자 권한 확인
SHOW GRANTS FOR CURRENT_USER();
SELECT CURRENT_USER();

WITH GRANT OPTION: 받은 권한을 다른 계정에게도 줄 수 있게 허용한다. REVOKE로 이 옵션만 따로 취소할 수 있다.

---

7. Role

여러 권한을 묶어서 한 번에 부여하기 위한 기능이다. 사용자가 많을 때 권한 관리를 단순화한다.

-- Role 생성
CREATE ROLE testrole;

-- Role에 권한 부여
GRANT SELECT, INSERT, UPDATE ON madangdb.* TO 'testrole';

-- 계정 생성 후 Role 부여
CREATE USER 'madang2'@'localhost' IDENTIFIED BY 'madang2';
GRANT 'testrole' TO 'madang2'@'localhost';

-- Role 활성화 (로그인 후 SET ROLE 필요)
SET ROLE 'testrole';

-- 로그인 시 자동으로 Role 활성화 설정
SET DEFAULT ROLE testrole TO 'madang2'@'localhost';

Role을 부여받아도 SET ROLE로 활성화하지 않으면 아무 권한이 없다. SET DEFAULT ROLE을 설정하면 로그인할 때 자동으로 활성화된다.

---

8. 프로시저 vs 함수 비교

	프로시저	함수
호출 방법	CALL 프로시저명()	SELECT 문 내부에서 직접
반환 값	OUT 매개변수 또는 없음	단일 값 RETURN
사용 위치	단독 호출	SQL 쿼리 내 표현식
트랜잭션	가능	제한적

---

9. 리뷰

면접을 다녀오면서 이번 수업을 못 들었는데, 파일을 보고 복습하면서 몇 가지가 눈에 들어왔다.

프로시저에서 DELIMITER를 바꿔야 하는 이유가 처음엔 번거롭게 느껴졌다. 그런데 생각해보면 ;이 SQL의 기본 구분자인데 프로시저 내부에도 ;이 있으니 충돌이 생길 수밖에 없다. 구분자를 바꾸는 게 가장 단순한 해결책이다.

커서가 JDBC의 ResultSet과 같은 개념이라는 설명이 인상 깊었다. 배운 것들이 실제 DB 내부 구조와 연결되는 지점이다.

Role이 실무에서 왜 중요한지도 이해됐다. 직원이 수십 명인 조직에서 한 명씩 권한을 주고받으면 관리가 불가능하다. 직군별 Role을 만들어두면 계정에 Role만 부여하면 끝이다.

---

10. 키워드 정리

Stored Procedure Role