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 반환
◎ 3, 5의 배수를 제외하고 1~100까지의 합을 구하는데 합계가 1000이 넘으면 값을 출력하는 예제
-- 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도 있었습니다. 구문들은 조건문들과 비슷하네요!!
많은 분들의 피드백은 언제나 환영합니다! 많은 댓글 부탁드려요~~
'BackEnd > DB(SQL)' 카테고리의 다른 글
[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 1-1(테이블 제약 조건 및 수정 방법 실습) (0) | 2023.02.09 |
---|---|
[DB_MySQL] 이것이 MySQL이다 ch08 테이블과 뷰 1(테이블 생성, 기본키, 외부키) (0) | 2023.02.08 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 4(피벗, JSON 데이터, 조인) (0) | 2023.02.07 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 3(수학 함수, 날짜 및 시간 함수, 시스템 정보 함수) (0) | 2023.02.07 |
[DB_MySQL] 이것이 MySQL이다 ch07 SQL 고급 2(ifnull, nullif, case, 문자열 함수) (0) | 2023.02.07 |