5. 데이터베이스 SQL프로그래밍

스토어드 프로시저

mysql에서 프로그래밍 기능이 필요할 때 사용

DELIMITER $$

create peocedure 스토어드프로시저이름
begin
	SQL프로그래밍
    
END$$
DELEMITER;
CALL 스토어드프로시저;

delemiter$$

end$$

스토어드 프로시정 시작과 끝을 나타난다.

이는 ;를 엔드기호로 입력받지 않게하는 역할을 한다.

마지막에 delemiter을 다시 ;로 바꾼다

 

IF~ELSE문

drop procedure if exists ifProc(); --이미 ifProc()가 있으면 삭제
delemiter$$ -- 시작
create porcedure ifProc1() -- 스토어드프로시저 생성
begin
	if(select count(*) from market_de.member) >= 10 then --10이상이면
    	select '회원수는 10 이상입니다.' as '답변'; -- 답변
   	else
    	select '회원수는 10 미민입니다' ㅁㄴ '답변'; --답변
    end if; --if문 종료
end $$ -- 스토어드프로시저 종료
delimiter; --delimiter울 다시 ;로

call idProc1();  --사용

if ~ then

else

end 문법을 사용한다. 타 언어와 혼동 금지.

 

변수 선언

drop procedure if exists ifProc2;
delimiter$$
create porcedure ifProc2()
begin
	declare ddate date; -- 데뷔일
    declare curdate date; -- 오늘
    declare days int; --활동일수
    
   	select debut_date into ddate from market_db_member where mem_id='APN';
    set curdate = current_date();
    set days = datediff(durdate, ddate);
    if(days > 1000) then delect concat('데뷔한 지', days, '일이 지났습니다 respect!') as 기간;
    else select concat('데뷔한 지', days, '일 밖에 안되었네요!') as 기간;
    end if;
end $$
delimiter;

call ifProc2();

declare 변수명 타입; 변수를 선언한다.

set 변수명 = 함수; 변수명에 특정 값을 세팅함

current_date(); 오늘 날짜를 알려주는 함수

datediff(날짜1, 날짜2); 날짜2에서 날짜1까지 일수를 알려주는 함수

 

case 문

select m.mem_id 아이디, m.mem_name 이름,
        sum(price*amount) 총구매액,
        case
            when sum(price * amount) >= 1500 then '최우수 고객',
            when sum(price * amount) >= 1000 then '우수 고객',
            when sum(price * amount) >= 1 then '그린 고객',
        else'잠재고객'
      end 회원등급
from buy B
	right outer join member M
    on M.mem_id = B.mem_id
  group by m.mem_id
  order by 총구매랙 desc;

select문 사이에서 case문을 사용하는 방법

case

       when 조건 then '값 ' 으로 지정한다.

 

 

while문

drop procedure if exists whileProc;
delimiter$$
create procedure whileProc1()
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 whileProce();

반복문인 while이다.

do를 사용한다는 점을 주의 변수에 set을 넣는다는 점 기억.

select문은 컬럼 제목을 범위 hap로 한 하는 것이다.

 

iterate, leave의 사용

drop procedure id exists whileProc;
delimiter $$
create procedure whileProc()
begin
	declare i int;
    declare hap int; --변수 선언
    set i = 1;
    set hap = 0; --변수 합넣기
    
    mywhile while(i <= 100) do --while문에 mywhile라는 이름을 부여
    
    	if(i%4=0) then
        	set i = i+1; iterate myWhile; --iterate는 continue와 같은 역할
            								--4의 배수는 건너뜀
        end if;
        
        set hap = hap + i;
        
        if(hap < 1000) then leave muWhile; --leave는 break와 같은 역할
        endif;
        set i = i+1;
   end while;
   
   select'1부터 100까지의 합(4의배수 제외), 1000넘으면 종료-->' 조건, hap;
   end$$
  delimiter;
  call whileProc();

while문에도 별칭부여 가능

iterate는 continue역할

leave는 break같은 역할

 

동적SQL

prepare,execute

drop table if exists gatetable;
create table gatetable(id int auto_increment primarykey, entry_tim datetime);

set @curdate = current_timeset(); --임시변수 curdate 현재 날짜와 시간
prepare myQuery from 'inset into gatetale values(null, ?)';
	-- sql문을 미리 준비 ?에는 변수가 들어간다.
execute myQuery using @curdate;
	-- 예약 sql myQuery를 변수 curdate를 ?에 넣어서 사용한다
execute myQuery using @curdate;
deallocate prepare myQuery;
	--다 사용한 후 prepare해제

prepare SQL문장을 미리 준비

execute SQL명 using @변수; 로 준비된 SQL문을 사용한다

deallocate로 prepare를 해제한다.

시스템 변수에 상한이 생길 수 있기 때문이다.

 

시간함수

now()
	-- sql 시작시간 기준 측정
current_data()
	-- sql 시작시간 기준 측정
sysdate()
	-- 실제시간 기준 측정
currnet_timestamp()
	--

 

SQL 3항 연상자처럼 사용하기

selelct mem_name, addr,
if(addr in(서울, 경기),'수도권,'비수도권') as 구분
from member
order by 구분, addr;

서울, 경기에 있을 때는 수도권, 없을 떄는 비수도권을 사용한다.