[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급5 (SQL 프로그래밍, if else, case, while)
bobo122023. 2. 8. 19:11
728x90
1. SQL 프로그래밍
◎ 스토어드 프로시저를 이용한 프로그래밍
▷ 10장에서 심화 학습 예정
▷ 형식
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저이름()
BEGIN
이 부분에 SQL 프로그래밍 코딩..
END $$
DELIMITER ;
CALL 스토어드 프로시저이름();
◎ IF...ELSE
▷ 조건에 따라 분기
▶ 참 / 거짓 두가지만 있기에 2중 분기
▷ 한 문장 이상 처리되어야 할 때 BEGIN.. END로 묶어주기
▷ 형식
IF <부울 표현식> THEN
SQL문장들1..
ELSE
SQL문장들2..
END IF;
부울 표현식 부분이 참이면 SQL문장들1 수행 / 거짓이면 SQL문장들2 수행
◎ IF 예제 1
drop procedure if exists ifProc;
use sqldb;
delimiter $$
create procedure ifProc()
begin
-- sql 프로그래밍
declare var1 int; -- 변수 선언
set var1 = 100; -- 변수에 값을 대입
if var1 = 100 then
select '100입니다.';
else
select '100이 아닙니다.';
end if;
end $$
delimiter ;
call ifProc();
-- 100입니다. 반환
delimiter 줄에는 주석이 있으면 create 자체가 실행되지 않습니다.
◎ IF 예제 2
-- employees DB의 employees 테이블을 사용
-- 열에는 입사일(hire_date)열이 있는데, 직원 번호 10001번에 해당하는 직원의 입사일이 5년이 넘었는지를 확인
drop procedure if exists ifProc2;
use employees;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE hireDATE DATE; -- 입사일
DECLARE curDATE DATE; -- 오늘
DECLARE days INT; -- 입사일
SELECT hire_date INTO hireDATE
-- hire_date into hireDATE : hire_date의 결과 값을 변수 hireDATE에 바로 넣는다.
FROM employees.employees
WHERE emp_no = 10001;
SET curDATE = CURRENT_DATE(); -- 현재 날짜를 대입
SET days = DATEDIFF(curDATE, hireDATE);
IF (days/365) >= 5 THEN
SELECT CONCAT('입사한지 ', days, '일이나 지났습니다. 축하합니다.');
ELSE
SELECT CONCAT('입사한지 '+ days+ '일밖에 안되었네요. 열심히 일하세요.');
END IF;
END $$
DELIMITER ;
call ifProc2();
-- 입사한지 13376일이나 지났습니다. 축하합니다. 반환
◎ IF 예제 3(성적처리)
-- IF ELSE 문을 이용한 성적처리
use sqldb;
drop procedure if exists ifProc3;
delimiter $$
create procedure ifProc3()
begin
declare point int;
declare credit char(1);
set point = 77;
if point >= 90 then
set credit = 'A';
elseif point >= 80 then
set credit = 'B';
elseif point >= 70 then
set credit = 'C';
elseif point >= 60 then
set credit = 'D';
else
set credit = 'F';
end if;
select concat('취득점수==>',point), concat('학점==>', credit);
end $$
delimiter ;
call ifProc3();
-- 취득점수==>77 학점==>C 반환
◎ CASE
▷ 조건에 따라 분기
▶ 다중분기
▶ 조건에 맞는 WHEN이 여러개더라도 조건을 먼저 만족하는 WHEN 처리됨
▶ SELECT문에서 많이 사용됨
▶ 점수로 성적을 판단하는 경우처럼 여러 단계로 분기될 때 사용
◎ CASE 예제 1 (성적처리, IF 예제 3과 결과 값 동일)
-- CASE 문을 이용한 성적처리
use sqldb;
drop procedure if exists caseProc;
delimiter $$
create procedure caseProc()
begin
declare point int;
declare credit char(1);
set point = 77;
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();
-- 취득점수==>77 학점==>C 반환
◎ CASE 예제 2 (고객등급 구분)
-- CASE 문을 활용한 SQL 프로그래밍
-- sqldb의 구매 테이블(buytbl)에 구매액(price*amount)이 1500원 이상인 고객은 '최우수 고객', 1000원 이상 '우수 고객',
-- 1원 이상이면 '일반 고객', 구매실적이 없으면 '유령 고객'이 출력 고급 등급 표시
-- userID, name, 총구매액, 고객등급
use sqldb;
select * from usertbl;
select * from buytbl;
select U.userid, U.name, sum(B.price * B.amount) as '총 구매액',
case
when (sum(B.price * B.amount) >= 1500) then '최우수고객'
when (sum(B.price * B.amount) >= 1000) then '우수고객'
when (sum(B.price * B.amount) >= 1) then '일반고객'
else '유령고객'
end as '고객등급'
from usertbl U
left join buytbl B
on U.userID = B.userID
group by U.userID
order by sum(B.price * B.amount) desc, name;
◎ WHILE과 ITERATE/LEAVE
◎ WHILE문
▷ 다른 프로그래밍 언어의 WHILE과 동일한 개념
▷ 해당 부울식이 참인 동안에 계속 반복되는 반복문
-- while 문
drop procedure if exists whileProc;
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 hap;
end $$
delimiter ;
call whileProc();
-- 5050 반환
▷ ITERATE문을 만나면 WHILE문으로 이동해서 비교를 다시함
▶다른 프로그래밍 언어의 Continue와 동일한 개념
▷ LEAVE문을 만나면 WHILE문을 빠져 나옴
▶ 다른 프로그래밍 언어의 Break와 동일한 개념
◎ 7의 배수를 제외하고 1~100까지의 합을 구하는데 합계가 1000이 넘으면 값을 출력하는 예제
-- 1~100까지의 합을 구하는 중간에 7의 배수를 제외하고 합계가 1000이 넘으면 값을 출력하고 프로그램 종료
drop procedure if exists whileProc2;
delimiter $$
create procedure whileProc2()
begin
declare i int;
declare hap int;
set i = 1;
set hap = 0;
mywhile : while(i <= 100) do
if(i % 7 != 0) then
set hap = hap + i;
if(hap > 1000) then -- hap이 1000을 넘기면 반복문을 벗어남
leave mywhile;
end if;
end if;
set i = i + 1;
iterate mywhile; -- mywhile 구문으로 되돌아가기
end while;
select hap;
end $$
delimiter ;
call whileProc2();
-- 1029 반환
-- 1~100까지의 합을 구하는 중간에 3, 5의 배수를 제외하고 합계 출력하고 프로그램 종료
drop procedure if exists whileProc3;
delimiter $$
create procedure whileProc3()
begin
declare i int;
declare hap int;
set i = 1;
set hap = 0;
mywhile : while(i <= 100) do
if(i % 3 != 0 and i % 5 != 0) then
set hap = hap + i;
end if;
set i = i + 1;
iterate mywhile; -- mywhile 구문으로 되돌아가기
end while;
select hap;
end $$
delimiter ;
call whileProc3();
-- 2632 반환
아래의 코드를 사용해도 동일한 결과값이 출력됩니다.
mywhile : while(i <= 100) do
if(i % 3 = 0 or i % 5 = 0) then
set i = i + 1;
iterate mywhile; -- mywhile 구문으로 되돌아가기
end if;
set hap = hap + i;
set i = i + 1;
end while;
◎ 오류 처리
▷ 형식
declare 액션 handler for 오류조건 처리할_문장;
1. 액션 ▷ 오류 발생 시에 행동 정의 ▷ CONTINUE와 EXIT 둘 중 하나 사용, CONTINUE가 나오면 제일 뒤의 ‘처리할_문장’ 부분이 처리
2. 오류 조건 : 어떤 오류를 처리할 것인지를 지정 ▷ MySQL의 오류 코드 숫자가 오거나 SQLSTATE‘상태코드’, SQLEXCEPTION, SQLWARNING, NOT FOUND등이 올 수 있음
3. 처리할_문장 ▷ 처리할 문장이 여러 개일 경우에는 BEGIN…END로 묶어줌
4. 오류 처리 예제 1
-- 오류 처리 : DECLARE 액션 HANDLER FOR 오류조건 처리할 문장;
drop procedure if exists errorProc;
delimiter $$
create procedure errorProc()
begin
declare continue handler for 1146 select '테이블이 없습니다.. ㅠㅠ' as '메시지';
select * from noTable; -- 1146 오류 코드 발생
end $$
delimiter ;
call errorProc();
-- 테이블이 없습니다.. ㅠㅠ 반환
◎ 오류 처리 예제 2
use sqldb;
select * from usertbl;
insert into usertbl values('LSG', '이상구', 1988, '서울', NULL, NULL, 170, current_date());
drop procedure if exists errorProc2;
delimiter $$
create procedure errorProc2()
begin
declare continue handler for sqlexception
begin
show errors;
select '오류가 발생했습니다. 작업을 취소 시켰습니다.' as '메시지'; -- 작업 취소 메시지 출력
rollback;
end;
insert into usertbl values('LSG', '이상구', 1988, '서울', NULL, NULL, 170, CURRENT_DATE());
end $$
delimiter ;
call errorProc2();
-- 오류가 발생했습니다. 작업을 취소 시켰습니다. 반환
LSG가 기본키이므로 에러가 발생합니다. 다른 이름으로 바꾸면 에러가 발생하지 않고 값을 입력할 수 있습니다.
2. 동적 SQL
◎ PREPARE문
▷ SQL문을 실행하지는 않고 미리 준비만 해놓음
◎ EXECUTE문
▷ 준비한 쿼리문 실행
▷ 실행 후에는 DEALLOCATE PREPARE로 문장 해제
-- 동적 SQL
use sqldb;
-- 메모리에 실행할 SQL문을 저장
prepare myQuery from 'select * from usertbl where userID = "ejw"';
-- 메모리에 저장한 SQL문을 실행
execute myQuery;
-- EJW, 은지원, 1972, 경북, 011, 8888888, 174, 2014-03-03 반환
-- 메모리에서 해제
deallocate prepare myQuery;
◎ using을 사용해 값을 전달하는 예제
drop table if exists myTable;
create table myTable (id INT auto_increment primary key, mDate datetime);
set @curDATE = current_timestamp(); -- 현재 날짜와 시간
prepare myQuery from 'insert into myTable values(null, ?)';
execute myQuery using @curDATE;
deallocate prepare myQuery;
insert into myTable values(null, @curDate);
SQL 프로그래밍, if else, case, while 등을 통해서 데이터를 연결하고 필요한 값들만 출력해보았습니다.
함수처럼 만드는 형식도 있고 바로 출력할 수 있는 case도 있었습니다. 구문들은 조건문들과 비슷하네요!!
