SQL - 프로그래밍

SQL

SQL - 프로그래밍

hyeon1016 2024. 11. 8. 15:56

스토어드 프로시저

MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 객체로 SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에서 만들어져야 한다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
    SQL 코딩
END $$
DELIMITER ;

CALL 스토어드_프로시저_이름();

변수 선언

DECLARE 변수명 데이터타입;

SELECT ~INTO 를 사용해 변수에 값을 넣을 수 있다.

SELECT 컬럼명 INTO 변수명 FROM 테이블명 WHERE 조건식;

변수 초기화

SET 변수명 = 값;

IF문

조건이 참일 때 특정 SQL 문장을 실행하는 구문이며 JAVA와 동일하다.

IF <조건식> THEN
    SQL문장
END IF;

THEN : 조건식이 참일 때 실행할 SQL문장을 정의하는 부분으로 JAVA의 중괄호 역할을 한다.

END IF : IF의 끝을 뜻하며 조건문이 끝나는 곳에 작성해야한다.

IF ~ELSE 문

조건이 참이 아닐 때 실행할 SQL 문장을 추가하는 구문이다.

IF <조건식> THEN
    SQL문장
ELSE
    SQL문장
END IF;

IF문의 활용

DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE;
    DECLARE curDate DATE;
    DECLARE days INT;

    SELECT debut_date INTO debutDate
        FROM market_db.member
        WHERE mem_id = 'APN';

    SET curDate = CURRENT_DATE();
    SET days = DATEDIFF(curDate, debutDate);


    IF(days/365) >=5 THEN
    	SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!.') AS '메시지';

    IF(days/365) >=5 THEN
    	SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 핑순이들 축하합니다!.') AS 'ㅇㅁㄴㅇ';
    ELSE 
    	SELECT CONCAT('데뷔한지', days, '일 밖에 안되었네요. 핑순이들 화이팅!') AS '메시지';
    END IF;
END $$
DELIMITER ;

- 변수 3개를 선언한다.

- debutDate 변수에 mem_id가 APN인 행의 debut_date 컬럼의 값을 저장한다.

- curDate 변수에는 현재 날짜 정보를, days 변수에는 데뷔 일자 부터 현재 날짜 까지의 일수를 저장한다.

- IF문으로 데뷔 날짜에 따른 SQL을 실행한다.

CASE문

JAVA의 SWITCH문과 동일한 역할을 한다.

CASE 
    WHEN 값1 THEN 결과1
    WHEN 값2 THEN 결과2
    ...
    ELSE 기본결과
END

SELECT M.mem_id, M.mem_name, SUM(price*amount) AS '총 구매액',
    CASE
        WHEN (SUM(price*amount) >= 1500) THEN '최우수 고객'
        WHEN (SUM(price*amount) >= 1000) THEN '우수 고객'
        WHEN (SUM(price*amount) >= 1) THEN '일반 고객'
        ELSE '유령 고객'
    END AS '회원 등급'
    FROM buy B
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

- SUM()함수로 price와amount 컬럼의 데이터를 곱한 값을 선택한다. / 별칭 '총 구매액'

- CASE 문으로 SUM() 값에 따라 등급을 분류한다.

WHILE문

필요한 만큼 계속 반복하는 구문이다.

WHILE <조건식> DO
	SQL문장
END WHILE;

DELIMITER $$
CREATE PROCEDURE SUM_WHILE()
BEGIN
    DECLARE sum INT DEFAULT 0;  -- 합을 저장할 변수
    DECLARE i INT DEFAULT 1;     -- 반복 카운터 변수

    WHILE i <= 10 DO
    	SET sum = sum + i;       -- 현재 값 i를 합에 추가
    	SET i = i + 1;           -- 카운터 증가
    END WHILE;

    SELECT sum AS total_sum;     -- 최종 합 출력
END $$
DELIMITER ;

ITERATE / LEAVE

ITERATE는 JAVA의 continue와 기능이 동일하고, LEAVE는 JAVA의 break와 기능이 동일하다.

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;

    myWhile:
    WHILE(i<=100)DO 
    	IF(i%4=0) THEN
            SET i = i + 1;
            ITERATE myWhile;
    	END IF;
    	SET hap = hap + 1;
    	IF(hap > 1000) THEN
            LEAVE myWhile;
    	END IF;
    	SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>' AS '합계', hap;
END $$
DELIMITER ;

- WHILE문을 myWhile이라는 레이블로 지정

- i를 4로 나눈 나머지가 0이면 i의 값을 1증가 시키고 다음 반복을 실행한다.

- hap 변수의 값을 1 증가시킨다.

- hap의 값이 1000 초과라면 WHILE문을 빠져나간다.

동적 SQL

SQL문은 내용이 고정되어 있는 경우가 많은 데 PREPARE / EXECUTE를 사용하면 SQL을 동적으로 사용할 수 있다.

PREPARE / EXECUTE

JAVA의 PreparedStatement와 똑같은 기능을 하며 사용 방법도 비슷하다.

SET @id='ABC';
PREPARE mySQL FROM 'SELECT mem_name, height FROM member WHERE mem_id=?';
EXECUTE mySQL USING @id;

- SET @id = 'ABC';로 mem_id에 사용할 변수를 설정한다.
- PREPARE 명령으로 SQL 쿼리를 미리 준비한다.. 이때, WHERE 절에서 사용할 자리 표시자?를 설정한다.
- EXECUTE 명령으로 준비한 쿼리를 실행한다. USING @id를 통해 @id 변수의 값을 쿼리에 전달한다.

- DEALLOCATE PREPARE mySQL 로 문장을 종료한다. JAVA의 close()