04-3. SQL 프로그래밍
- 한마디로 코딩하는 것
- 일반 언어보다는 약하지만 일반적 코딩에 가깝게 SQL을 사용할 수 있는 것
👉🏻 스토어드 프로시저
- SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 한다
- 스토어드 프로시저 이름은 우리가 지정
- 'SQL 프로그래밍 코딩'하는 부분에 코드 작성
`DELIMITER $$`는 MySQL 쿼리를 작성할 때 사용되는 구분자(delimiter)를 변경하는 명령입니다. 이 명령은 주로 저장 프로시저(Stored Procedure), 트리거(Trigger), 이벤트(Event) 등과 같은 복잡한 쿼리 블록을 정의할 때 사용됩니다.
일반적으로, MySQL에서 기본 구문 구분자는 세미콜론(`;`)입니다. 하지만 저장 프로시저 등의 복잡한 블록을 정의할 때는 여러 개의 SQL 문이 하나의 블록으로 묶여 있기 때문에, 블록 전체가 완전히 정의되기 전에 세미콜론을 만나면 MySQL은 해당 지점을 끝으로 간주하고 오류를 발생시킬 수 있습니다.
`DELIMITER $$` 명령은 이러한 문제를 해결하기 위해 구문의 끝을 나타내는 구분자를 세미콜론(`;`)이 아닌 다른 문자로 변경합니다. `$$`는 종종 사용되는 다른 문자 중 하나입니다. 구문이 완전히 정의되고 나면 `DELIMITER ;` 명령을 사용하여 다시 세미콜론을 기본 구문 구분자로 변경합니다.
예를 들어, 저장 프로시저를 정의할 때:
DELIMITER $$
CREATE PROCEDURE sample_procedure()
BEGIN
-- 저장 프로시저 본문
END $$
DELIMITER ;
이렇게 하면 저장 프로시저의 정의가 세미콜론(`;`)이 아닌 `$$`로 끝나게 되어, MySQL이 블록의 끝을 올바르게 처리할 수 있습니다.
`DELIMITER` 명령을 사용하면 MySQL에게 새로운 구문 블록의 시작을 알리고 해당 블록이 끝날 때까지 사용될 구문 구분자를 변경합니다. 특히 저장 프로시저, 트리거, 이벤트와 같은 복잡한 SQL 블록을 정의할 때 유용합니다.
기본적으로 MySQL은 세미콜론(`;`)을 구문 구분자로 사용합니다. 그러나 저장 프로시저 등을 정의할 때는 여러 SQL 문이 하나의 블록으로 묶이기 때문에, 중간에 세미콜론을 만나면 블록이 끝났다고 인식할 수 있습니다. `DELIMITER`를 사용하면 세미콜론이 아닌 다른 구분자를 사용하여 블록을 정의할 수 있습니다.
따라서 `DELIMITER`를 사용하면 앞으로 나오는 SQL 문이 하나의 블록으로 간주되어, 블록이 완전하게 정의될 때까지 MySQL이 끝을 인식하지 않습니다. 정의가 끝나면 `DELIMITER`를 다시 기본 세미콜론으로 변경하여 일반적인 SQL 문을 작성할 수 있습니다.
👉🏻 IF 문
- 조건문
- 참일 경우 문장을 실행, 거짓일 경우 실행하지 않음
🧩 기본 문법
- 처리해야할 문장이 여러개라면 BEGIN ~ END로 묶어줘야 한다
(나중에 추가될 문장을 위해서 한 문장만 있더라도 쓰는 것을 권장)
🎮 100과 100이 같은가?
- 혹시 모르니까 기존에 만든 것이 있다면 삭제
🎮 100과 200이 같은가?
- 실행은 되지만 거짓이기 때문에 아무것도 실행 결과가 나오지 않는다
👉🏻 IF ~ ELSE 문
- 참일 때 실행할 것 따로 거짓일 때 실행할 것 따로 있는 경우
🎮 100과 200이 같은가 + 같지 않아도 문장 출력하기
✅ DECLARE
- 변수를 선언함
- 변수 선언 시 @@를 붙였던 것은 저장 프로시저가 아닌 밖에서 변수 선언할 때 사용했던 것
- 저장 프로시저 안에서 선언할 때는 @@를 붙이지 않고 DECLARE로 선언한다
MySQL에서 저장 프로시저(Stored Procedure)는 하나 이상의 SQL 문을 그룹화하여 이름을 지정하고 이를 호출할 수 있는 프로그램 유형입니다. 저장 프로시저는 데이터베이스 내에서 서버 측에서 실행되며, 주로 데이터베이스 관리 및 조작 작업을 수행하는 데 사용됩니다. 저장 프로시저를 사용하면 코드를 모듈화하고 유지보수 및 실행 계획의 최적화를 쉽게 할 수 있습니다.
이 때, 변수를 선언하는 방식에는 내부적으로 사용되는 `DECLARE` 구문이 있습니다. 변수를 선언할 때 외부에서 사용되는 변수와 저장 프로시저 내에서 사용되는 변수의 차이는 다음과 같습니다.
외부에서의 변수 선언:
- 외부에서는 `@@` 기호를 사용하여 변수를 참조합니다.
- 예를 들어, `@@global.variable_name`은 전역 변수를 나타내며, `@@session.variable_name`은 세션 변수를 나타냅니다.
- 외부에서는 주로 시스템 또는 세션의 설정 값을 확인하는 데 사용됩니다.
SELECT @@global.variable_name;
SELECT @@session.variable_name;
저장 프로시저 내에서의 변수 선언:
- 저장 프로시저 내에서 변수를 선언할 때는 `DECLARE` 키워드를 사용합니다.
- 저장 프로시저 내에서 선언된 변수는 저장 프로시저의 로컬 변수로 간주되며, 외부에서 직접 참조할 수 없습니다.
DELIMITER //
CREATE PROCEDURE sample_procedure()
BEGIN
DECLARE local_variable INT;
-- 저장 프로시저 내에서 local_variable 사용 가능
-- 외부에서는 직접 참조할 수 없음
END //
DELIMITER ;
요약하면, `@@`를 사용하는 것은 외부에서 전역 또는 세션 변수에 접근할 때이고, `DECLARE`를 사용하는 것은 저장 프로시저 내에서 로컬 변수를 선언할 때입니다. 저장 프로시저 내에서 선언된 변수는 외부에서 직접 접근할 수 없으며, 저장 프로시저의 로직 내에서만 사용됩니다.
- DECLARE를 통해 정수형 변수 선언
- SET을 통해 변수에 값을 넣어줌
🎮 APINK 데뷔 일수에 따른 문장 출력하기
- 만약 이전에 만든 것이 있다면 지우기
- 기본 문법으로 ifProc3 만들기
- DECLARE를 통해 변수 생성
- 데뷔 날짜, 오늘 날짜, 활동일수에 대하여
- 데뷔일과 오늘의 차이가 활동일수가 될 것
- mem_id가 APN인 데이터의 debut_date 가져오기
- 원래 SELECT만 하면 해당 SQL문을 통해 나온 결과를 출력해주는데
- INTO를 통해 출력이 아니라 해당 변수에 집어넣음
- CURRENT_DATE() 함수를 실행하면 오늘 날짜를 가져옴, 그것을 SET을 통해 curDATE에 집어 넣음
- DATEDIFF라는 제공되는 함수를 통해 두 날짜의 차이를 구한다
`DATEDIFF()` 함수는 MySQL에서 날짜 간의 차이를 계산하는 데 사용되는 함수입니다. 주로 두 날짜 사이의 일 수, 시간 수, 분 수, 초 수 등을 계산할 때 활용됩니다. 함수의 일반적인 구문은 다음과 같습니다:
DATEDIFF(expr1, expr2)
여기서 `expr1`과 `expr2`는 비교할 두 날짜나 날짜/시간 표현식입니다. `expr1`에서 `expr2`를 뺀 결과를 반환합니다.
이렇게 `DATEDIFF()` 함수를 사용하여 날짜 간의 차이를 계산하면, 데이터베이스에서 날짜 기반의 작업을 수행하는 데 도움이 됩니다.
- 일수를 알고 싶은 것이 아니라 연차를 열고 싶은 것이기 때문에 구해진 days를 365로 나눈 몫을 통해 연차를 구한다
- 5년이 넘었다면 축하를, 그렇지 않다면 응원을 하는 것
- 마무리 및 호출
👉🏻 CASE문
- IF문은 참과 거짓만 있지만
- CASE문은 여러 종류로 분기할 때 사용 (다중 분기)
- 일반 프로그래밍 언어의 switch ~ case문과 비슷한 기능
🧩 기본 문법
- WHEN은 여러개 나올 수 있다
- WHEN을 모두 거쳐도 해당되는 게 없을 때 ELSE
- ELSE는 하나만
🎮 학점 계산기
- 만약 이전에 존재한다면 지우기
- PROCEDURE 구문 시작
- 시험 점수 변수와 학점 변수를 정의
- 시험 점수 변수에는 88을 대입
- 90점대일 경우 A, 80점대일 경우 B ...
- 60 미만은 다 F
- SELECT 문을 통해 취득 점수와 학점을 보여줌
- 구문 끝
🧩 CASE문의 활용
- 총 구매액에 따라서 회원 등급 나누기
🎮 총 구매액 구하기(1)
- buy테이블에서 mem_id를 기준으로 (그룹별로)
🎮 총 구매액 구하기(2)
- 많이 구매한 순으로 보기위해 ORDER BY 사용
🎮 총 구매액 구하기(3) + 이름
- 그룹 이름은 구매 테이블이 아니라 회원 테이블에만 있으므로 조인이 필요
- 이렇게 하면 유령 고객 (한 번도 구매하지 않은 고객)이 나오지 않는다
🎮 유령 고객까지 보이게 하기
- RIGHT OUTTER JOIN을 통해 구매 테이블에 없어도(구매 내역이 없어도) 회원 테이블에 있다면 (회원으로 등록되어 있다면) 모두 나오게 하기
🎮 회원 등급 (최종)
- SELECT문 안에 CASE문 적어주기
- END뒤에 alias(별칭)
👉🏻 WHILE문
- 조건식이 참인 동안 SQL문들이 반복된다
🧩 기본 문법
🎮 1 ~ 100까지의 합계
- 이전에 만든 것이 있다면 지워주기
- 구문 시작
- 증가할 변수 i와 합을 넣을 변수 hap을 선언하고 초기화한다
- i가 100이하인 동안
- 이전 hap 값에 현재 i를 더한 값을 hap에 넣어준다
- i를 하나 증가시킨다
- 100이 넘어가면 while문이 끝난다
- SELECT문으로 출력하기
- 구문 끝
- 호출
🧩 WHILE문의 응용
✅ ITERATE
- 반복문을 올라간다
- cotinue 같은 것
`ITERATE`는 MySQL에서 반복문을 제어하기 위해 사용되는 키워드 중 하나입니다. 주로 `WHILE` 루프 내에서 사용되며, 특정 조건이 충족될 때 루프를 계속 실행하도록 하는데 활용됩니다.
간단한 예제를 통해 설명하겠습니다. 다음은 `WHILE` 루프와 `ITERATE`를 사용하여 1부터 5까지의 숫자를 출력하는 저장 프로시저의 예제입니다:
DELIMITER $$
CREATE PROCEDURE simple_loop()
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 5 DO
-- 반복문 내에서 작업 수행
SELECT counter;
SET counter = counter + 1;
ITERATE; -- 다음 반복으로 이동
END WHILE;
END $$
DELIMITER ;
이 저장 프로시저는 `counter`라는 변수를 사용하여 1부터 5까지의 숫자를 출력하는 루프를 생성합니다. `ITERATE`는 현재 반복을 중단하고 다음 반복으로 이동하게 합니다. 여기서는 `counter`가 5보다 작거나 같을 때까지 반복하며, 각 반복에서 `counter` 값을 출력하고 1씩 증가시킵니다.
주의할 점은 `ITERATE`를 사용하기 전에 반드시 어떤 식으로든 반복 조건이 변경되어야 합니다. 그렇지 않으면 무한 루프에 빠질 수 있습니다.
이렇게 `ITERATE`를 사용하면 루프를 중단하고 다음 반복으로 이동하게 되어, 더 복잡한 루프 로직을 구현할 때 활용할 수 있습니다.
✅ LEAVE
- 돌던 반복문을 빠져나간다
`LEAVE` 문은 MySQL에서 `WHILE` 루프나 `REPEAT` 루프 등에서 특정 조건이 충족되었을 때 루프를 빠져나가기 위해 사용되는 키워드입니다. `LEAVE`를 사용하면 루프를 명시적으로 종료할 수 있습니다.
간단한 예제를 통해 설명하겠습니다. 다음은 `WHILE` 루프와 `LEAVE`를 사용하여 1부터 5까지의 숫자를 출력하는 저장 프로시저의 예제입니다:
DELIMITER $$
CREATE PROCEDURE simple_loop()
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 5 DO
-- 반복문 내에서 작업 수행
SELECT counter;
SET counter = counter + 1;
IF counter > 3 THEN
LEAVE; -- counter가 3을 초과하면 루프를 빠져나감
END IF;
END WHILE;
END $$
DELIMITER ;
이 저장 프로시저는 `counter`라는 변수를 사용하여 1부터 5까지의 숫자를 출력하는 루프를 생성합니다. `LEAVE` 문은 `IF` 문을 사용하여 `counter` 값이 3을 초과하면 루프를 빠져나가도록 설정되어 있습니다. 따라서 1, 2, 3을 출력한 후, `counter`가 3을 초과하면 루프가 종료됩니다.
`LEAVE` 문을 사용하면 특정 조건이 충족되었을 때 루프를 빠져나가거나, 특정 상황에서 루프를 중단시킬 수 있습니다. 이는 루프를 유연하게 제어하는 데 도움이 됩니다.
✅ 레이블
- 반복문의 이름이라고 생각하면 됨
- ITERATE, LEAVE 뒤에 이름을 붙여 해당 반복문을 돌아가거나 나가겠다고 밝히는 것
- 내가 지정해주면 됨
MySQL에서 반복문의 라벨(label)은 반복문을 식별하는 데 사용되는 이름입니다. 이 라벨은 `BEGIN ... END` 블록의 이름을 지정하는데 사용됩니다. 주로 `LOOP`, `WHILE`, `REPEAT`와 같은 반복문과 함께 사용되며, 특히 중첩된 반복문에서 특정 반복문을 식별하는 데 유용합니다.
간단한 예제를 통해 설명하겠습니다. 다음은 반복문의 라벨을 사용한 저장 프로시저의 예제입니다:
DELIMITER $$
CREATE PROCEDURE nested_loops()
BEGIN
DECLARE outer_counter INT DEFAULT 1;
DECLARE inner_counter INT DEFAULT 1;
outer_loop: -- 반복문의 라벨
WHILE outer_counter <= 3 DO
inner_loop: -- 내부 반복문의 라벨
WHILE inner_counter <= 2 DO
-- 반복문 내에서 작업 수행
SELECT outer_counter, inner_counter;
SET inner_counter = inner_counter + 1;
END WHILE inner_loop;
SET outer_counter = outer_counter + 1;
SET inner_counter = 1;
END WHILE outer_loop;
END $$
DELIMITER ;
이 저장 프로시저에서 `outer_loop`와 `inner_loop`는 각각 `WHILE` 루프의 라벨로 사용되었습니다. 라벨을 사용하면 특정 반복문을 명시적으로 식별할 수 있으며, 이는 중첩된 반복문에서 특정 루프를 탈출하거나 진행하는 데 도움이 됩니다.
라벨은 선택적으로 사용할 수 있으며, 프로그램의 가독성을 높이고 코드를 이해하기 쉽게 만드는 데 도움을 줄 수 있습니다.
🎮 1~100까지 더하는데 그 중 4의 배수는 건너 뛰기,
합계가 1,000이 넘으면 그 순간의 총합을 출력하고 반복문 빠져 나가기
- 만약 이전에 만든 것이 있다면 지우고 시작
- 구문 시작
- 변수 선언 및 초기화
- WHILE문 안에 조건 넣기
- 만약 i를 4로 나눈 나머지가 0이라면 (4의 배수라면) 그냥 i만 증가시키고 반복문의 처음으로 돌아감
- IF문에 걸리지 않았다면(4의 배수가 아니라면) hap에 현재의 i를 더한다
- 만약 현재 i를 더한 결과인 hap이 1,000을 넘는다면 해당 반복문을 종료한다
- IF문에 걸리지 않았다면(1,000을 넘지 않았다면) i를 1 증가시키고 다시 반복문을 돈다
- SELECT문을 통해 출력하기
- 구문 종료 및 호출
👉🏻 동적 SQL
- SQL문을 고정시키지 않고 동적으로 만들겠다
🧩 PREPARE
- 쿼리문 미리 준비
PREPARE:
- PREPARE 문은 동적 SQL 문을 준비하는 데 사용됩니다. 동적 SQL은 실행 시간에 생성되는 SQL로, 고정된 쿼리가 아니라 변수나 조건에 따라 내용이 변하는 쿼리를 의미합니다.
- PREPARE 문은 쿼리를 준비하여 나중에 EXECUTE 문을 통해 실행할 수 있도록 해줍니다.
- 구문은 다음과 같이 생겼습니다:
PREPARE statement_name FROM 'sql_query';여기서 statement_name은 준비된 쿼리에 대한 이름이며, 'sql_query'는 준비할 동적 SQL 문입니다.
🧩 EXECUTE
- 실행
EXECUTE:
- EXECUTE 문은 PREPARE를 통해 준비한 동적 SQL 문을 실행하는 데 사용됩니다.
- EXECUTE 문을 통해 동적 SQL을 실행할 때에는 필요한 매개변수를 전달할 수 있습니다.
- 구문은 다음과 같이 생겼습니다:
EXECUTE statement_name [USING @variable [, ...]];여기서 statement_name은 PREPARE를 통해 준비한 동적 SQL의 이름이며, [USING @variable [, ...]]는 필요한 매개변수를 나타냅니다.
✅ 그냥 할 때와 차이점
- 무언가 섞어서 실시간 변형 가능, 필요에 따라 변형
- 그냥 SQL문을 쓰면 쿼리문이 고정돼있는 것
🧩 DEALLOCATE
- 해제
DEALLOCATE는 MySQL에서 PREPARE로 준비한 동적 SQL 문을 실행한 후에 사용된 자원을 해제하는 데에 사용되는 명령입니다. 동적 SQL을 사용할 때, PREPARE로 준비한 후에는 EXECUTE를 통해 실행하고, 마지막에 DEALLOCATE를 사용하여 자원을 명시적으로 해제해주어야 합니다.
구문은 다음과 같습니다:
DEALLOCATE PREPARE statement_name;여기서 statement_name은 PREPARE를 통해 준비한 동적 SQL 문의 이름입니다. DEALLOCATE를 호출하면 해당 이름으로 준비한 동적 SQL 문과 관련된 자원이 해제되며, 메모리 등의 자원이 반환됩니다.
🎮 출입문 출입 내역
- 태그하는 순간의 날짜가 INSERT되도록
- 이전에 만든 것이 있다면 지우기
- 출입 데이터를 넣을 테이블 만들기
- ID 컬럼과 출입 시간 컬럼이 있다
- 변수 선언과 동시에 초기화
✅ CURRENT_TIMESTAMP()
`CURRENT_TIMESTAMP()` 함수는 MySQL에서 현재 날짜와 시간 정보를 반환하는 함수입니다. 이 함수는 서버의 현재 날짜와 시간을 기준으로 합니다. 여러 다른 형태의 날짜 및 시간 함수 중 하나로, 주로 현재 시간을 쿼리하거나, 날짜 및 시간 값을 기준으로 작업을 수행할 때 사용됩니다.
간단한 사용 예제를 통해 설명하겠습니다:
SELECT CURRENT_TIMESTAMP();
이 쿼리는 현재 날짜와 시간을 반환합니다. 반환되는 값은 `YYYY-MM-DD HH:MI:SS` 형식으로 표시되며, 연도, 월, 일, 시간, 분, 초를 나타냅니다. 예를 들어, `2023-11-14 15:30:45`와 같이 표시될 수 있습니다.
이 함수는 서버의 현재 시간을 기반으로 하기 때문에, 서버의 시간대(Time Zone) 설정에 의해 영향을 받습니다. 서버의 시간대를 변경하면 `CURRENT_TIMESTAMP()`의 반환 값도 변경됩니다.
이 함수는 주로 특정 이벤트가 발생한 시간을 기록하거나, 특정 시간을 기준으로 데이터를 조회할 때 활용됩니다.
- ID는 AUTOINCREMENT를 통해 자동으로 입력될 것이기 때문에 NULL을 넣어주고
- ?를 통해 데이터가 들어왔을 때 저장할 수 있도록 SQL문을 준비한다
- 데이터가 들어오면 curDate변수에 저장된 것이 위에 준비된 SQL문의 ?부분에 들어간다
- 해제까지 시켜준다
- 테이블 출력해보기
- 실행 버튼을 누른 순간마다 데이터가 찍힌다
출처: https://youtu.be/IOCsreDYqFE?si=INJDRzTlPGPsDEvJ
'MySQL > 혼공SQL' 카테고리의 다른 글
| 13강. SQL 테이블 제약조건(기본키, 외래키, 고유키) (1) | 2023.11.24 |
|---|---|
| [SQL 기초 강의] 12강. GUI 환경에서 테이블 생성하기, SQL로 테이블 만들기 (1) | 2023.11.20 |
| [SQL 기초 강의] 10강. 두 테이블을 묶는 JOIN(INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN) (1) | 2023.11.13 |
| [SQL 기초 강의] 9강. MySQL의 데이터 형식(정수형, 문자형, 실수형, 날짜형)과 형 변환 (0) | 2023.11.11 |
| [SQL 기초 강의] 8강. 데이터 변경을 위한 SQL 문(INSERT, UPDATE, DELETE) (1) | 2023.11.11 |