获取第一条记录
- select * from ad_plan limit 0,1
去重sql：
delete from change_of_rate where (reference = 'GDAX' or reference ='BSTP' or reference='KRAK')
and id not in (select a.id from ( select min(id) as id FROM change_of_rate GROUP BY reference,create_time
HAVING count(*) > 1) a)
存储过程
- clearing_proc
CREATE DEFINER=`root`@`%` PROCEDURE `clearing_proc`(IN makerId VARCHAR(255))
BEGIN
DECLARE v_IDFrom VARCHAR(255);
DECLARE v_cashFlow DOUBLE;
DECLARE v_cashFlowCur VARCHAR(255);
DECLARE v_dealID VARCHAR(255);
DECLARE v_clearingID INT;
DECLARE cusmargin_done INT DEFAULT FALSE;
DECLARE cur_cusmargin CURSOR FOR
select C.id_from ,C.cash_flow AS CcashFlow,C.cash_flow_currency,C.deal_id,C.clearing_id
from clearing_cashflow as C where book_from="Cash_cusmargin" AND
C.clearing_id not in (SELECT clearing_id FROM cash_cusmargin);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cusmargin_done = TRUE;
SET @curTime = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
OPEN cur_cusmargin;
read_loop1: LOOP
FETCH cur_cusmargin INTO v_IDFrom, v_cashFlow, v_cashFlowCur, v_dealID, v_clearingID;
IF cusmargin_done THEN
LEAVE read_loop1;
END IF;
insert into cash_cusmargin(cus_id,log_date_time
,cash_flow,cash_flow_currency,status,deal_id,source,clearing_id)
values(v_IDFrom,@curTime,-1 * v_cashFlow,v_cashFlowCur
,"batchClearingToCash",v_dealID,"batchClearingToCash",v_clearingID);
BEGIN
DECLARE makercash_done INT DEFAULT FALSE;
DECLARE cur_makercash CURSOR FOR
select C.cash_flow,C.cash_flow_currency,C.deal_id,C.clearing_id
from clearing_cashflow as C where book_to="cash_makercash" AND
C.clearing_id not in (SELECT clearing_id FROM cash_makercash);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET makercash_done = TRUE;
OPEN cur_makercash;
read_loop2: LOOP
FETCH cur_makercash INTO v_cashFlow, v_cashFlowCur, v_dealID,v_clearingID;
IF makercash_done THEN
LEAVE read_loop2;
END IF;
insert into cash_makercash(maker_potfolio_id
,log_date_time,cash_flow,cash_flow_currency,status,deal_id
,source,clearing_id)
values(makerId,@curTime,v_cashFlow,v_cashFlowCur
,"batchClearingToCash",v_dealID,"batchClearingToCash",v_clearingID);
END LOOP read_loop2;
CLOSE cur_makercash;
END;
END LOOP read_loop1;
CLOSE cur_cusmargin;
END;
- decision_range_proc
CREATE DEFINER=`root`@`%` PROCEDURE `decision_range_proc`(in cutoffDateTime dateTime,in spotRef double(32, 8))
BEGIN
DECLARE v_id VARCHAR(255);
DECLARE v_dealID VARCHAR(255);
DECLARE v_DdealID VARCHAR(255);
DECLARE v_cusID VARCHAR(255);
DECLARE v_notional VARCHAR(255);
DECLARE v_notionalCur VARCHAR(255);
DECLARE d_cashFlow DOUBLE(32, 8) DEFAULT 0;
DECLARE i_profit INT DEFAULT 0;
DECLARE d_balance DOUBLE(32, 8) DEFAULT 0;
DECLARE i_type INT DEFAULT 0;
DECLARE i_total_cash_flow_type INT DEFAULT 0;
DECLARE i_cash_flow_type INT DEFAULT 0;
DECLARE d_total_balance DOUBLE(32, 8) DEFAULT 0;
BEGIN
update deal_cusspreadcupondeal set status="exe", judge_price=spotRef where expiry_cutoff_date_time=cutoffDateTime and strike_from<=spotRef and strike_to>spotRef and status="live";
update deal_cusspreadcupondeal set status="unexe", judge_price=spotRef where expiry_cutoff_date_time=cutoffDateTime and status="live";
update position_option_maker set status="exe" where expiry=cutoffDateTime and status="live"
and(( CALL_PUT =1 and Strike<=spotRef ) OR ( CALL_PUT =-1 and Strike>=spotRef));
update position_option_maker set status="unexe" where expiry=cutoffDateTime and status="live";
END;
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE deal_cusspreadcupondeal CURSOR FOR
select D.id,D.deal_id,CONCAT(D.deal_id,"-",D.ID) as DdealID,D.cus_id,D.notional,D.notional_cur,type,TRUNCATE(D.notional*100/spotRef, 4) as DcashFlow, 100 as profit
from deal_cusspreadcupondeal as D where expiry_cutoff_date_time=cutoffDateTime and status='exe' AND deal_date < '2018-11-02 18:00:00'
UNION
select D.id,D.deal_id,CONCAT(D.deal_id,"-",D.ID) as DdealID,D.cus_id,D.notional,D.notional_cur,type,TRUNCATE(D.notional*10/spotRef, 4) as DcashFlow, 10 as profit
from deal_cusspreadcupondeal as D where expiry_cutoff_date_time=cutoffDateTime and status='exe' AND deal_date > '2018-11-02 18:00:00';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN deal_cusspreadcupondeal;
-- 遍历
read_loop: LOOP
-- 取值 取多个字段
FETCH deal_cusspreadcupondeal INTO v_id, v_dealID, v_DdealID, v_cusID, v_notional, v_notionalCur, i_type, d_cashFlow, i_profit;
IF done THEN
LEAVE read_loop;
END IF;
insert into clearing_cashflow(cash_flow,cash_flow_currency,book_from,id_from,book_to,id_to,deal_id,log_date_time,source)
values(-1 * d_cashFlow,v_notionalCur,"Cash_cusMargin",v_cusID,"Cash_makercash","makerCS",v_dealID,
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'),"batchJudgment");
IF i_type = 0 THEN
set i_cash_flow_type = 6;
ELSEIF i_type = 1 THEN
set i_cash_flow_type = 7;
ELSE
set i_cash_flow_type = 8;
END IF;
UPDATE deal_cusspreadcupondeal SET profit = i_profit WHERE deal_id = v_dealID;
UPDATE customer SET balance = balance + d_cashFlow WHERE cus_id = v_cusID;
SELECT @d_balance:=balance FROM customer WHERE cus_id = v_cusID;
-- SELECT @d_balance:=balance FROM cash_flow ORDER BY created DESC LIMIT 1;
INSERT INTO cash_flow(cus_id, number, type, asset, amount, balance, created, status)
VALUES(v_cusID, v_dealID, i_cash_flow_type, "BTC", d_cashFlow, @d_balance, DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'), 0);
IF i_type = 0 THEN
set i_total_cash_flow_type = 11;
ELSEIF i_type = 1 THEN
set i_total_cash_flow_type = 12;
ELSE
set i_total_cash_flow_type = 13;
END IF;
SELECT @d_total_balance:=balance FROM total_cash_flow ORDER BY id DESC LIMIT 1;
INSERT INTO total_cash_flow(cus_id, type, number, relevance, pay_type, asset, amount, balance, remark, channel, created)
VALUES("平台", i_total_cash_flow_type, v_dealID, v_dealID, 0, "BTC", (-1)*d_cashFlow, @d_total_balance + (-1)*d_cashFlow, "发放奖励", "余额", DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'));
INSERT INTO total_cash_flow(cus_id, type, number, relevance, pay_type, asset, amount, balance, remark, channel, created)
VALUES(v_cusID, i_total_cash_flow_type, v_dealID, v_dealID, 1, "BTC", d_cashFlow, @d_total_balance, "发放奖励", "余额", DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'));
END LOOP read_loop;
CLOSE deal_cusspreadcupondeal;
END;
END;
网友评论