SQL 프로그래밍

Preface

이번 내용은 'SQL 고급' 장의 마지막에 있는 내용이지만, 별도의 내용이라고 생각되어 따로 업로드한다.

if문과 while문 등의 반복분, 제어문은 형식만 조금 다를 뿐 기본적인 내용은 파이썬과 거의 동일하다.

또, MySQL의 prepare, execute 문은 '?'를 이용하여 포매팅을 가능하게 해주는 것 같다.

내가 아직 익숙치 않아서 그런지 SQL은 일반 프로그래밍 언어들 보다 쓸데없이 복잡한 것 같다는 생각이 든다.

1. SQL 프로그래밍

- 스토어드 프로시저를 만들고 사용하는 방법

delimiter $$
create procedure 스토어드 프로시저 이름()
begin

	이 부분에 SQL 프로그래밍 코딩...
    
end $$
delimiter ;
call 스토어드 프로시저 이름() ;

1) delimiter $$ ~ end $$: 스토어드 프로시저의 코딩할 부분을 묶어줌

2) 마지막에 'delimiter ;'로 종료 문자를 세미콜론으로 다시 변경해야 함

- if ··· else: 조건에 따라 분기한다.

→ 한 문장이던 두 문장이던 begin, end로 묶어주는 것이 좋다.

drop procedure if exists ifProc; -- 기존에 만든 적이 있다면 삭제
delimiter $$
create procedure ifProc()
begin
	declare var1 int; /* var1 변수 선언: 스토어드 프로시저나 함수 안에서는 declare문으로 선언한 후 사용하며, 
					     @변수명이 아닌 그냥 변수명만 사용한다. */
    set var1 = 100; -- 변수에 값 대입
    
    if var1 = 100 then
		select '100입니다.';
	else
		select '100이 아닙니다.';
	end if;
end $$
delimiter ;
call ifProc();

- 직원 번호 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열의 결과를 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 '입사한지 ' + days + '일밖에 안되었네요. 열심히하세요.';
	end if;
end $$
delimiter ;
call ifProc2();

→ select 열 이름 into 변수이름 from 테이블 이름: 조회된 열의 결과 값을 변수에 대입

- 다중분기하는 if문 코드

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

- 동일 코드를 case문으로 작성한 코드

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

→ case문은 조건에 맞는 when이 여러개면 먼저 조건이 만족하는 when을 처리한 후 case를 종료한다.

- sqldb의 구매 테이블의 구매액에 따라 고객을 분류하는 코드

select U.userID as 'ID', U.username, sum(price * amount) as '총 구매액',
		case
			when (sum(price * amount) >= 1500) then '최우수 고객'
            when (sum(price * amount) >= 1000) then '우수 고객'
            when (sum(price * amount) >= 1) then '일반고객'
            else '유령고객'
		end as '고객등급'
	from buytbl B
		right outer join usertbl U
			on B.userID = U.userID
	group by U.userID, U.username
    order by sum(price * amount) desc;

- while문: 해당 조건이 참인 동안에는 계속 반복되는 반복문

-- 1부터 100까지의 값을 모두 더하는 코드
drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
begin
		declare i int;	-- 1에서 100까지 증가할 변수
        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();

-while문에서 사용되는 문법

1) iterate문: 다른 프로그래밍 언어의 continue와 비슷한 역할

2) leave문: 다른 프로그래밍 언어의 break와 비슷한 역할

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	-- while문에 label을 지정
	 if (i % 7 = 0) then
				set i = i + 1;
				iterate myWhile;	-- 지정한 while문으로 가서 계속 진행
	 end if;
    
		set hap = hap + i;
        if (hap > 1000) then
			leave myWhile;			-- 지정한 while문을 떠남
	 end if;
		set i = i + 1;
	end while;
    
    select hap;
end $$
delimiter ;
call whileProc2();

→ iterate문을 만나면 바로 while문으로 이동해서 비교를 다시 하고, leave문을 만나면 while문을 빠져나온다.

- 발생한 오류를 직접 처리하는 형식

declare 액션 handler for 오류조건 처리할문장;

1) 액션: 오류 발생 시에 행동을 정의

→ continue와 exit 둘 중 하나를 사용하는데, 전자 사용 시 제일 뒤의 '처리할 문장' 부분이 처리된다.

2) 오류조건: 어떤 오류를 처리할 것인지를 지정한다.

3) 처리할 문장: 처리할 문장이 나오며, 여러 개일 경우 begin, end,로 묶어준다.

- 오류의 종류

1) SQLSTATE(상태코드): 5자리 문자열로 되어 있으며, MySQL 사이트에서 확인할 수 있다.

2) SQLEXCEPTION: 대부분의 오류를 의미

3) SQLWARNING: 경고 메시지를 의미

4) NOT FOUND: 커서나 select···into에서 발생되는 오류를 의미

- 오류 처리 예시

drop procedure if exists errorProc;
delimiter $$
create procedure errorProc()
begin
	declare continue handler for 1146 select '테이블이 없어요' as '메시지';
    select * from noTable;	-- db에 존재하지 않는 테이블
end $$
delimiter ;
call errorProc();

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

1) show errors: 오류에 대한 코드와 메시지를 출력

→ show count(*) errors: 발생된 오류의 개수를 출력

→ show warning: 경고에 대한 코드와 메시지를 출력

2) rollback: 진행중인 작업 취소

3) commit: 작업을 완전히 확정

- 동적 SQL: 미리 쿼리문을 준비한 후, 나중에 실생하는 것

1) prepare과 execute문을 사용해서 작성할 수 있다.

2) execute문 실행 후에는 deallocate prepare로 문장을 해제해 주는 것이 좋다.

3) prepare문에서 ?으로 향후에 입력될 값을 비워놓고, execute에서 using을 이용해서 값을 전달해 사용할 수 있다.

use sqldb;
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;

select * from myTable;

728x90