loop, while, cursor

<loop>
[begin_label:] LOOP
  statement_list
end loop[end_label]

[label] : LOOP
...
--terminate the loop
if condition then
  leave [label]; - exit
  iterate [label]; -반복
end if
...
end loop;
- leave label : 지정된 label의 반복문을 벗어나게 한다
- iterate label : 지정된 label를 반복하게 한다

ex)1~10까지 출력
create procedure loop_test1()
begin
  declare a int default 0;

start_a : loop
  set a =a+1;
  if a>10 then
    leave start_a;
  end if;
    select a;
  end loop;
end;


<while>
begin_label : while 조건 do
...
end while end_label;


ex)
create procedure while_test1()
begin
  declare cnt int default 0;
  declare str varchar(100) default ' ';
  begin_label : while cnt<10 do
    set cnt = cnt+1;
    set str = concat(str, ' ', cnt);
  end while;
  select str;
end;




<cursor>
- 선택된 데이터를 가지고 있다

<cursor 특징>
- Read-only
- Non-scrollable
- Asensitive(강압적이지 않다->커서에 의해 선택된 상태값이
변경 될 경우 인식하지 못한다)


<커서 선언하기>
declare cursor_name cursor for select_statement;
declare continue handler for not fount set finished=1; ->커서에 데이터가 없는 경우
open cursor_name;
fetch cursor_name into variables list;
...
close cursor_name;
- finished : 일반변수
- fetch : 순환
- fetch cursor_name into variables list;
=> cursor_name의 값을 variables list라는 곳에 순환시켜라
=> for(x of obj) | for(x in obj) 과 비슷

ex)사원 번호와 이름 출력하기
create procedure cursor_test1()

begin
  declare m_no varchar(30);
  declare m_nmae varchar(30);
  declare finished int default 0;
  declare cur cursor for select employeeNumber, lastName from employees;
  declare continue handler for not found set finished=1;

  open cur;
  my_job:loop
    fetch cur into m_no, m_name;
    if finished=1 then
      leave my_job;
      end if;
      select m_no, m_name;
    end loop;
    close cur;
  end;
- "employeeNumber, lastName"에서 fetch cur into "m_no, m_name"의
   갯수와 순서가 반드시 일치해야한다
---------------------------------------------
<loop, while>

#loop문을 사용하여 1~10합계를 구하여 출력
#function
use lecture;
set global log_bin_trust_function_creators=1;

drop function func_loop;
create function func_loop()
returns int
begin
declare hap int default 0;
    declare x int default 0;
    my_loop:loop
    set x = x+1;
    set hap = hap + x;
    if x>=10 then
        leave my_loop;
       end if;
      end loop;
  return hap;
end;

select func_loop();

#procedure
drop procedure pro_loop;
create procedure pro_loop()
begin 
declare hap int default 0;
    declare x int default 0;
   
   my_loop:loop 
     set x = x + 1;
     set hap = hap + x;
    if x=10 then 
      leave my_loop;
    end if;
   end loop;
  
select hap;
end;

call pro_loop();

#while
drop procedure while_test1()
create procedure while_test1()
begin 
declare cnt int default 0;
    declare str varchar(100) default '';
    my_loop:while cnt<=10 do
       set cnt=cnt+1;
       set str=concat(str,'', cnt);
      end while 
     select cnt, str;
end;

call while_test1();

#매개변수로 전달된 정수를 사용하여 정수에 해당하는 구구단을 출력하시오(loop, while)

#function+loop 
drop function func_loop_gugu;
create function func_loop_gugu(dan int)
returns varchar(300)
begin 
declare i int default 0;
    declare str varchar(300) default '';
   here:loop 
     set i = i+1;
     set str = concat(str, dan, '*', i, '=', (dan*i), '');
  if i=9 then 
     leave here;
    end if;
   end loop;
  return str;
 end;

select func_loop_gugu(5);

#function+while 
drop function func_while_gugu;
create function func_while_gugu(dan int)
returns varchar(300)
begin 
declare i int default 0;
    declare str varchar(300) default '';
    here : while i<9 do
      set i = i+1;
      set str = concat(str, dan, '*', i, '=', (dan*i), '');
     end while 
     return str;
end;

select func_while_gugu(5);

#procedure+loop 
drop procedure pro_loop_gugu;
create procedure pro_loop_gugu(dan int, out str varchar(100))
begin 
declare i int default 0;
    set str = '';
    here:loop 
     set str = concat(str, dan, '*', i, '=', (dan*i), '');
     if i=9 then
      leave here;
     end if;
   end loop;
  end;
 
call pro_loop_gugu(5, @str);
select @str;

#procedure+while
drop procedure pro_while_gugu;
create procedure pro_while_gugu(dan int, out str varchar(100))
begin 
declare i int default 0;
    set str = '';
    here:while i<9 do 
     set i=i+1;
     set str = concat(str, dan, '*', i, '=', (dan*i),'');
    end while;
   end;

call pro_while_gugu(5,@str);
select @str;
---------------------------------------------

<cursor>

[cursor 미션] function and procedure
1. 매개변수로 사무실코드(officecode)를 입력받아 해당 사무실 직원의
사번, 이름, 이메일 주소를 조회

2. 고객번호(CustomerNumber)를 입력받아 해당 고객의 주문번호, 수량, 
단가, 금액(수량*단가)를 조회(테이블 : orders, orderdetails)

3. 두개의 날짜를 입력받아 두 날짜 사이에 주문된 주문번호, 주문일,
고객번호를 조회(테이블 : orders)

4. 고객별 금액 합계 조회(테이블:payments)

5. 년도를 입력받아 년도에 해당하는 금액총액과 평균금액을 조회
(테이블:payments)



#cursor
#cursor를 사용하여 사번, 사원명을 출력하는 procedure를 작성
drop procedure cursor_test1;
create procedure cursor_test1()
begin
declare eno int;
    declare ename varchar(100);
    declare stop int default 0;
   
   
    declare cur cursor for
      select employeeNumber, lastNmae
      from classicmodels.employees e ;
     declare continue handler for not found set stop=1;
    
   open cur;
   here:loop
   fetch cur into eno, ename;
   if(stop=1) then
    leave here;
   end if;
   select eno, ename;
  end loop;
 
   close cur;
end;
call cursor_test1();

desc classicmodels.employees ;

/*매개변수로 사무실코드(officecode)를 입력받아 해당 사무실 직원의
사번, 이름, 이메일 주소를 조회*/
#function
drop function cursor_f1;
create function cursor_f1(off_code int)
begin 
declare eName varchar(50) default '';
    declare eNumber int default 0;
    declare eMail varchar(50) default '';
   
    declare cur cursor for 
    select e.employeeNumber, e.email, e.lastName 
    from classicmodels.employees e ;
    where e.officeCode = off_code;
   
    declare continue handler for not found set stop=1;
   
    open cur;
    here:loop 
    fetch cur into eName, eNumber, eMail;
    if(stop=1) then  
     leave here;
    end if 
     select eName, eNumber, eMail;
    end loop;
   
   close cur;
  end;
 call cursor_f1();


#procedure
drop procedure proc_cur_mis1;
create procedure proc_cur_mis1(code varchar(10))
begin 
declare eno int;
    declare ename varchar(50);
    declare em varchar(100);
    declare stop int default 0;
   
declare cur cursor for 
select employeeNumber, lastName, email 
from classicmodels.employees e
where officeCode = 2;
    declare continue handler for not found set stop=1;
   
    open cur;
    here:loop 
    fetch cur into eno, ename, em;
    if(stop=1) then 
      leave here;
    end if;
      select eno, ename, em;
     end loop;
    close cur;
   end;
call proc_cur_mis1(2);

/*고객번호(CustomerNumber)를 입력받아 해당 고객의 주문번호, 수량, 
단가, 금액(수량*단가)를 조회(테이블 : orders, orderdetails)*/
drop procedure if exists proc_cur_mis2;
create procedure proc_cur_mis2(cno int)
begin 
declare ono int;
    declare ea int;
    declare price decimal(10,2);
    declare stop int default 0;
   
    declare cur cursor for
      select o.orderNumber, o2.quantityOrdered, o2.priceEach 
      from classicmodels.orders o join classicmodels.orderdetails o2 
      on o.orderNumber = o2.orderNumber 
      where o.customerNumber = cno;
     declare continue handler for not found set stop=1;
    
     open cur;
       here:while stop=0 do
         fetch cur into ono, ea, price;
         select ono, ea, price, (ea*price);
       end while;
      close cur;
end;
call proc_cur_mis2(181);


drop function cursor_f2;
create function cursor_f2(cus_num int)
begin 
declare eNumber int default 0;
    declare eproduct int default 0;
    declare eprice int default 0;
    declare etot int default 0;
    
    declare cur cursor for  
      select o2.orderNumber, o2.priceEach 
      from classicmodels.orderdetails o2 
      select o.orderNumber
      from classicmodels.orders o 
end;

/*두개의 날짜를 입력받아 두 날짜 사이에 주문된 주문번호, 주문일,
고객번호를 조회(테이블 : orders)*/
drop procedure proc_cur_mis3;
create procedure proc_cur_mis3(snal date, enal date)
begin 
declare ono int;
    declare onal date;
    declare cno int;
    declare stop int default 0;
    declare cur cursor for 
      select o.orderNumber, o.orderDate, o.customerNumber 
      from classicmodels.orders o 
      /*where o.orderDate >= snal and o.orderDate <= enal;*/
      where o.orderDate between snal and enal;
      declare continue handler for not found set stop=1;
     
      open cur;
      here:while stop=0 do 
        fetch cur into ono, onal, cno;
        select ono, onal, cno;
      end while;
      close cur;
end;
call proc_cur_mis3('2003-01-01', '2003-01-31');

/*고객별 금액을 합계 조회(테이블:payments)*/
drop procedure proc_cur_mis4;
create procedure proc_cur_mis4(cn int)
begin 
declare cno int;
declare tot decimal(10,2);
    declare stop int default 0;
   
    declare cur cursor for 
      select customerNumber, sum(amount)
      from classicmodels.payments
      where customerNumber = cn
      group by customerNumber;
    declare continue handler for not found set stop=1;
   
   open cur;
   here:while stop=0 do 
     fetch cur into cno, tot;
     select cno, tot;
   end while;
   close cur;
end;
call proc_cur_mis4(103);

/*년도를 입력받아 년도에 해당하는 금액총액과 평균금액을 조회
(테이블:payments)
*/
drop procedure proc_cur_mis5;
create procedure proc_cur_mis5(y int)
begin 
declare totAmt decimal(10,2) default 0;
    declare avgAmt decimal(10,2) default 0;
    declare stop int default 0;
   
   declare cur cursor for 
     select sum(amount), avg(amount)
     from classicmodels.payments p 
     where year(paymentDate) = y #year를 쓰면 반환값이 '숫자'이다
     group by year(paymentDate);
    declare continue handler for not found set stop=1;
   
   open cur;
   here:while stop=0 do 
     fetch cur into totAmt, avgAmt;
    select totAmt "총액", avgAmt "평균";
    end while;
   close cur;
end;
call proc_cur_mis5(2004);