SQL & Database Part 3

정창민·2022년 12월 1일

1. PROCEDURE

반복적으로 사용해야 하는 SQL 문장이 있을 때 사용하면 편리

DROP PROCEDURE IF EXISTS 데이터베이스명.get_all;

DELIMITER $$
$$
CREATE PROCEDURE 데이터베이스명.get_all()
BEGIN
  SELECT * FROM product where 가격 > 5000; // *
END 
$$
DELIMITER ;

CALL 데이터베이스명.get_all(); 사용 시
SELECT * FROM product where 가격 > 5000; 코드가 실행됨

변수 문법

// 1.
SET @price = 6000;
SELECT * FROM product WHERE 가격 = @price;


// 2.
SET @price = (SELECT COUNT(*) FROM product WHERE 가격 = 5000) 

// 3.
SET @age =  20;
SET @age = @age + 1;
SELECT @age;

SET 변수명 뒤에 오는 등호는 값을 '변수명'에 삽입하겠다는 뜻

procedure 안에서 변수만들어쓰기

delimiter $$
$$
CREATE PROCEDURE homework.var_test()
BEGIN 
	DECLARE 변수1 int DEFAULT 123;	
	DECLARE 변수2 varchar(100) DEFAULT '반갑습니다.';
	SELECT 변수1, 변수2;
END
$$
delimiter ;


CALL homework.var_test();

DECLARE 변수명 데이터타입;
변수를 생성하고 @는 안 적어도 됨
DEFAULT는 변수에 초기값을 넣어주겠다는 뜻
변수1에는 123, 변수2에는 '반갑습니다.'가 출력됨

구멍뚫기 문법 (PARAMETER)

// 1.
CREATE PROCEDURE mart.get_all(구멍 INT)
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍;
END


// 2.
CREATE PROCEDURE mart.get_all( 구멍1 INT, 구멍2 varchar(100) )
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍1 OR 상품명 = 구멍2;
END

CALL mart.get_all(5000); 을 출력하면,
5000 이상의 가격 컬럼이 전부 필터링 돼서 출력됨

2. 날짜 & 시간데이터

날짜 저장하려면

테이블 만들 때 DATETIME(6) 1~6 숫자를 넣으면 초단위 소수점 6자리까지 기록 가능
보통의 경우 DATETIME을 사용하는 경우가 일반적

DATETIME 컬럼 가져오기

사용 예시

SELECT * FROM blog WHERE 발행일 > '2022-03-10 08:24:25'
SELECT * FROM blog WHERE 발행일 = '2022-03-10 08:24:25'

원하는 날짜의 모든 행 가져오기

// 1.
SELECT * FROM blog 
WHERE 발행일 >= '2022-03-10 00:00:00' AND 발행일 < '2022-03-11 00:00:00' 


// 2.
SELECT * FROM blog 
WHERE 발행일 > '2022-03-10 00:00:00' AND 발행일 <= now()

2022-03-10의 모든 행을 불러온다.
2022-03-10 00:00:00부터 현재시간까지의 모든 행을 불러온다.

날짜 포맷 마음대로 바꾸기

SELECT date_format(now(), '%Y년 %d일이고 %m월인데요')

새로운 행에 날짜 insert 하려면

INSERT INTO 테이블명 VALUES('2030-01-01 12:00:00');

3. FUNCTION 문법

procedure문법과 비슷한 function문법

DBeaver에서 function 만드는 법

function 만들 때 필요한 문법

CREATE FUNCTION DB이름.함수이름(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 100;
END

소괄호 안에 parameter 삽입 가능
returns 뒤에 datatype 꼭 기입
deterministic도 꼭 넣어줘야함

그럼 procedure 쓰지 왜 function 만드냐

긴 쿼리문을 자주 재사용하고 싶을 때는 procedure
계산기능을 만들었는데 그걸 자주 재사용하고 싶을 때는 function
function은 자주쓰는 계산기능을 쉽게 재사용하고 싶을 때

부가세 계산 자주하고 싶은데

// 1.
CREATE FUNCTION mart.vat(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 구멍 * 0.1;
END 


// 2. 사용 예시
DROP FUNCTION IF EXISTS mart.vat(구멍 INT) 
delimiter $$

CREATE FUNCTION mart.vat(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 구멍 * 0.1;
END 
$$
delimiter ;

SELECT mart.vat(10000);

// 3.
DROP FUNCTION IF EXISTS 함수명;
DELIMITER $$

CREATE FUNCTION 함수명() 
RETURNS INT
BEGIN 
  어쩌구
  RETURN 저쩌구;
END 
$$
DELIMITER ;

함수 이름 적을 때 DB이름.함수명() 적는게 안전

procedure vs function 비교 정리

둘 다 SQL 코드를 저장해두고 재사용하는 용도
procedure는 CALL 문법으로 소환해야함, function은 CALL 문법 필요없음
procedure는 RETURN (그니까 OUT 파라미터) 없어도 됨,
function은 무조건 RETURN 있어야 함
procedure는 쿼리문 중간에 갑자기 사용불가능, function은 자유롭게 거의 아무데서나 사용가능

그래서 그냥 SQL 쿼리문 재사용 용이면 procedure, 계산기 만들고 싶으면 function

DETERMINISTIC 왜 넣음?

MySQL은 이상한 보안장치가 있는데

함수를 만들어 사용할 때 함수가

구멍에 뭘 넣어도 항상 같은 값을 RETURN 하면 DETERMINISTIC
SQL 문법을 사용안하면 NO SQL 표기해야하고
안에 SELECT를 사용하면 READS SQL DATA
안에 INSERT DELETE를 사용하면 MODIFIES SQL DATA

를 표기해둬야 함

일반적으론 DETERMINISTIC 넣음

4. procedure, function 안에서 쓸 수 있는 IF

조건에 따라 SELECT같은 쿼리문은 값을 출력하지 못할 수 있음

조건에 따라 다른 코드를 실행하고 싶으면 IF THEN ELSE

// 1.
IF 조건식1 THEN 
  조건식1이 참이면 실행할 쿼리문;
ELSEIF 조건식2 THEN 
  조건식2가 참이면 실행할 쿼리문;
ELSE 
  그게 아니면 실행할 쿼리문;
END IF;


// 2.
IF 2 = 1 THEN 
  SELECT '첫째가 맞음';
ELSEIF 1 = 1 THEN
  SELECT '둘째가 맞음';
ELSE 
  SELECT '틀림';
END IF;


// 3.
IF 1 > 0 THEN 
  SELECT '맞음';
ELSE 
  SELECT '틀림';
END IF;

조건에 따라서 다른 결과를 뱉는 함수만들기

예시

DROP FUNCTION IF EXISTS mart.age_check;
DELIMITER $$

CREATE FUNCTION mart.age_check(구멍 INT)
RETURNS INT
DETERMINISTIC
BEGIN 

    RETURN ??;

END $$
DELIMITER ;

SELECT mart.age_check(20);

procedure

DROP PROCEDURE IF EXISTS mart.test;
delimiter $$

CREATE PROCEDURE mart.test()
BEGIN
	
	IF (SELECT SUM(사용금액) FROM mart.card) > 5000000 THEN
		SELECT '잘 했어요';
	ELSE
		SELECT '분발하세요';
	END IF ;
	
END 
$$
delimiter ;

CALL mart.test();

function

DROP FUNCTION IF EXISTS mart.함수;
delimiter $$

CREATE FUNCTION mart.함수(구멍 int)
RETURNS varchar(100)
DETERMINISTIC
BEGIN
	
	IF 구멍 >= 20 THEN
		RETURN '성인입니다';
	ELSE
		RETURN '미성년자입니다';
	END IF ;
	
END 
$$
delimiter ;

SELECT mart.함수(30);