use test;
truncate sequence;
set @setTime = (select subtime(now(),'00:02:00'));
insert into sequence (name,deposit_date,deposit_amount,interest_amount) values ('이소윤',@setTime,10000000,0);
drop procedure if exists pro;
delimiter //
create procedure pro()
begin
while (select id from sequence order by id desc limit 1) < 10 do
INSERT INTO sequence (name,deposit_date,deposit_amount,interest_amount)
select
(select name from sequence order by id desc limit 1),
date_add((select deposit_date from sequence order by id desc limit 1),INTERVAL 1 hour),
((select deposit_amount from sequence order by id desc limit 1) + (select interest_amount from sequence order by id desc limit 1)),
(select deposit_amount from sequence where id=1)* 0.04 /12;
end while;
end //
delimiter ;
call pro();
select * from sequence;
mysql구분 기호를 재정의하려면delimiter명령을 사용하십시오.//전체 정의를 단일 명령문으로 서버에 전달한 다음;프로시저를 호출하기 전에 로 복원할 수 있도록구분 기호가 변경됩니다 .이렇게 하면 프로시저 본문에 사용된 구분 기호가mysql자체;에서 해석되지 않고 서버로 전달될 수 있습니다."
INSERT INTO sequence (deposit_date,interest_amount) select date_add(now(),INTERVAL 1 MONTH),10000000* 0.04 /12;
숫자로 넣어서 하긴했는데 실제는 이렇게 하면 안될듯
재사용할 수 있게 변수지정해야되지 않을까
<4차시도>
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where id='1'),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
where id="1"
로 첫번째 행에 있는 값을 사용하게 했다. 금액은 괜찮은데
하지만 날짜는 이전행을 기준으로 한달씩 증가되어야한다.
<5차시도>
LAG함수쓰면 될거같은데 안되네....
아 된건가?
SELECT * FROM test.sequence; use test; insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000'); INSERT INTO sequence (deposit_date,interest_amount) select date_add(now(),INTERVAL 1 MONTH),10000000* 0.04 /12; select deposit_date, LAG(deposit_date) over(order by deposit_date) from sequence;
<6차시도>
흠...
select deposit_date from sequence order by id desc limit 1;
마지막행선택함.
이렇게 마지막 행의 날짜를 선택하고 거기에 한달씩 더해주면 되지 않을까?
select date_add(deposit_date,INTERVAL 1 MONTH) from sequence where deposit_date order by id desc limit 1;
오 됐다
한달 추가하겠다 마지막행으로 찾은 날짜에
ㅇㄴㄹㄴ
오됐다
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
<종합해보면>
SELECT * FROM test.sequence; use test; insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000');
INSERT INTO sequence (deposit_date,interest_amount) select date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),(select deposit_amount from sequence where id='1')* 0.04 /12;
<7차시도>
아 이름도 같이 들어가게끔
INSERT INTO sequence (name, deposit_date,interest_amount) select (select name from sequence order by id desc limit 1) ,date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH), (select deposit_amount from sequence where id='1')* 0.04 /12 ;
<결과>
use test;
insert into sequence (name,deposit_date,deposit_amount) values ('이소윤','20200509','10000000');
INSERT INTO sequence (name, deposit_date,interest_amount)
select (select name from sequence order by id desc limit 1) ,date_add((select deposit_date from sequence where deposit_date order by id desc limit 1),INTERVAL 1 MONTH),
(select deposit_amount from sequence where id='1')* 0.04 /12 ;
