스토어드 프로시저(Procedure)를 이용한 프로그래밍.MySQL

 

 

스토어드 프로시저를 이용한 프로그래밍

 

---

IF...ELSE

• 조건에 따라 분기
  • 참 / 거짓 두가지만 있기에 2중분기
• 한 문장 이상 처리되어야 할때 begin...end로 묶어주기
• 형식

• 부울 표현식 부분이 참이면 SQL문장들1 수행/ 거짓이면 SQL 문장들2 수행

---

CASE

• 조건에 따라 분기 합니다.
  • 다중 분기
  • 조건에 맞는 WHEN이 여러 개더라도 먼저 조건이 만족하는 WHEN이 처리됩니다.
  • SELECT문에서 많이 사용됩니다.
  • 점수로 성적을 판단하는 경우처럼 여러 단계로 분기 될 때 사용합니다.

 

 

---

-- case 문을 활용한 sql 프로그래밍
-- sqldb의 구매 테이블(buytbl)에 구매액(price * amount)이 1500원 이상인 고객은 '최우수 고객', 1000원 이상이면 '우수 고객',
-- 1원 이상이면 '일반 고객', 구매실적 없으면 '유령 고객' 이 출력, 고급 등급 표시
-- userid, name, 총구매액, 고객등급

use sqldb;

select * from usertbl;
select * from buytbl;

select u.userid, u.name, b.price * b.amount as '총 구매액'
	from usertbl u
		inner join buytbl b
			on u.userid = b.userid
	group by u.userid
order by u.userid;

 

---

-- 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 '총 구매액'
	from buytbl b
		right outer join usertbl u
			on u.userid = b.userid
	group by u.userid
order by u.userid;

---

 

 

 

 

-- 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) >= 100) then '우수고객'
		when (sum(b.price * b.amount) >= 1) then '일반고객'
        else '유령고객'
	end as '고객등급'
	from buytbl b
		right outer join usertbl u
			on u.userid = b.userid
	group by u.userid
order by sum(b.price * b.amount) desc, name;

---

WHILE 과 ITERATE/LEAVE

WHILE문

• 다른 프로그래밍 언어의 WHILE 과 동일한 개념
• 해당 부울식이 참인 동안에 계속 반복되는 반복문

• ITERATE문을 만나면 WHILE문으로 이동해서 비교를 다시합니다
  • 다른 프로그래밍 언어의 Continue와 동일한 개념
• LEAVE문을 만나면 WHILE 문을 빠져나옵니다.
  • 다른 프로그래밍 언어의 BREAK와 동일한 개

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();

---

-- 3과 5의 배수가 빠지고, 1~100까지의 합계 구하기

delimiter $$
create procedure whileproc2()
begin
	declare i int;
    declare sum int;
    
    set i = 1;
    set sum = 0;
    
    while1 : while(i<=100) do
		if(i%3 = 0 or i%5 = 0) then
			set i = i + 1;
            iterate while1;
		end if;
        
			set sum = sum + i;
            set i = i + 1;
		end while;
	
    select i, sum;
end $$
delimiter ;
		
call whileproc2();

 

---

오류 처리

형식

• 액션
  • 오류 발생시 행동 정의
  • CONTIUNUE 와 EXIT 둘 중 하나를 사용, CONTINUE가 나오면 제일 뒤의 '처리할_문장' 부분이 처리
• 오류조건 : 어떤 오류를 처리 할것인지를 지정
  
  • MySQL의 오류코드 숫자가 오거나 SQLSTATE'상태코드', SQLEXCEPTION, SQLWARNING, NOT FOUND등이 올수 있음
• 처리할_문장
  • 처리할 문장이 여러 개일 경우에는 BEGIN...END 로 묶어줍니다.

-- -----------------------------------------------
-- 이미 기입된 튜플에 대한 값 입력시 에러 출력에 대한 메시지 출력
use sqldb;

select * from usertbl;



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();

 

 

---

동적 SQL

PREPARE문

• SQL문을 실행하지는 않고 미리 준비만 해놓음

EXECUTE문

• 준비한 쿼리문 실행
• 실행 후에는 DEALLOCATE PREFARE로 문장을 해제 해줍니다.

use sqldb;

prepare myQuery from 'select * from usertbl where userid = "EJW"';  -- 메모리에 실행할 SQL 문 저장
execute myQuery;													-- 메모리에 저장한 SQL 문 실행

deallocate prepare myQuery;											-- 메모리에서 해제

---

use sqldb;

prepare myQuery from 'select * from usertbl where userid = "EJW"';  -- 메모리에 실행할 SQL 문 저장
execute myQuery;													-- 메모리에 저장한 SQL 문 실행

deallocate prepare myQuery;											-- 메모리에서 해제

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);

select * from mytable;

 

---