[SQL]-SQLプログラミング

1.ストレージプロセス

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

2.IF文

IF <조건식> THEN
(코딩)
END IF;

DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	if 100 = 100 THEN
    	SELECT '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER ;
CALL ifProc1();

3.IF~ELSE文

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE myNum INT;
SET myNum = 200;
IF myNum = 100 THEN
SELECT '100입니다.';
ELSE
SELECT '100이 아닙니다.';
END IF;
END $$
DELIMITER ;
CALL ifProc2();

4.CASE文

CASE
WHEN 조건1 THEN
SQL
WHEN 조건2 THEN
SQL
WHEN 조건3 THEN
SQL
ELSE
SQL
END CASE;

DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
DECLARE point INT ;
DECLARE credit CHAR(1);
SET point = 88 ;

CASE
WHEN point >= 90 THEN
SET credit = 'A';
WHEN point >= 80 THEN
SET credit = 'B';
WHEN point >= 70 THEN
SET credit = 'C';
WHEN point >= 60 THEN
SET credit = 'D';
ELSE
SET credit = 'F';
END CASE;
SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

5.WHILE文

WHILE <조건식> DO
SQL
END WHILE;

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
DECLARE i INT;
DECLARE hap INT;
SET i = 1;
SET hap = 0;
WHILE(i <= 100) DO
SET hap = hap + i;
SET i = i + 1;
END WHILE;
SELECT '1부터 100까지의 합 ==>', hap;
END $$
DELIMITER ;
CALL whileProc();

6.動的SQL

PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXCUTE myQuery;
DEALLOCATE PREPARE myQuery;(해제)

7.動的SQLの利用

CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP();

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPATE myQuery;

SELECT * FROM gate_table;