Mysql Stored Procedure nested Use cursor sample code, mysql sample code

Mysql Stored Procedure nested Use cursor sample code, mysql sample code

BEGIN -- DECLARE the variable DECLARE v_addtime_begin varchar (13); DECLARE v_addtime_end varchar (13); DECLARE v_borrow_id int; DECLARE v_count int; DECLARE s1 int;/** DECLARE the cursor, AND save the query results to the CURSOR **/DECLARE c_borrow cursor for select id from rocky_borrow where borrowtype = 2 AND PUBLISH_TIME> = UNIX_TIMESTAMP ('2017-05-27 ') AND PUBLISH_TIME <= UNIX_TIMESTAMP ('1970-07-30 ') ORDER by id asc;/** get query quantity **/SELECT count (ID) INTO v_count from rocky_borrow where borrowtype = 2 AND PUBLISH_TIME> = UNIX_TIMESTAMP ('2017-05-27 ') AND PUBLISH_TIME <= UNIX_TIMESTAMP ('2017-07-30') ORDER by id asc; SET s1 = 1; -- start transaction; -- OPEN the cursor OPEN c_borrow; -- loop cursor WHILE s1 <v_count + 1 DO -- traverse the cursor FETCH c_borrow INTO v_borrow_id; SELECT t1.addtime INTO v_addtime_begin FROM (SELECT * FROM rocky_ B _tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID ASC) t1 GROUP BY t1.borrow _ id; SELECT t1.addtime INTO v_addtime_end FROM (SELECT * FROM rocky_ B _tenderrecord bt WHERE BORROW_ID = v_borrow_id AND tender_type = 1 ORDER BY ID DESC) t1 GROUP BY t1.borrow _ id; IF (v_addtime_begin IS NOT NULL) & (v_addtime_end is not null) THEN -- nested Use cursor begin declare v_id int; DECLARE v_user_id int; DECLARE v_type varchar (20); DECLARE v_total decimal (20, 8) DEFAULT 0; DECLARE v_money decimal (20,8) DEFAULT 0; DECLARE v_use_money decimal (20,8) DEFAULT 0; DECLARE v_no_use_money decimal (20,8) DEFAULT 0; DECLARE v_collection decimal (20,8) DEFAULT 0; DECLARE v_to_user int (11); DECLARE v_remark VARCHAR (1000); DECLARE v_addtime varchar (13); DECLARE v_addip varchar (64); DECLARE limit decimal (20, 8) DEFAULT 0; DECLARE done VARCHAR (45) DEFAULT ''; DECLARE t_error int DEFAULT 0; DECLARE c_accountlog cursor for select id, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY, NO_USE_MONEY, COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP, FIRST_BORROW_USE_MONEY FROM (select id, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY, NO_USE_MONEY, COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP, FIRST_BORROW_USE_MONEY FROM rocky_accountlog where addtime> = v_addtime_begin and addtime <= v_addtime_end AND (type = 'your _ cold' or type = 'repayment _ deduct ') t group by t. user_id HAVING count (t. user_id)> 1; declare continue handler for not found set done = NULL; OPEN c_accountlog; FETCH c_accountlog INTO v_id, v_user_id, v_type, v_total, v_money, v_use_money, cost, v_collection, v_to_user, v_remark, v_addtime, v_addip, cursor; WHILE (done is not null) do insert into rocky_accountlog_test2 (ACCOUNTLOG_ID, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY, NO_USE_MONEY, COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP, distance, BORROW_ID) VALUES (v_id, v_user_id, v_type, v_total, v_money, v_use_money, hour, v_collection, v_to_user, v_remark, v_addtime, v_addip, distance, v_borrow_id ); FETCH c_accountlog INTO v_id, v_user_id, v_type, v_total, v_money, v_use_money, hour, v_collection, v_to_user, v_remark, v_addtime, v_addip, else; end while; CLOSE c_accountlog; end if; SET s1 = s1 + 1; end while; CLOSE c_borrow; COMMIT; -- transaction COMMIT END

Mysql Stored Procedure cyclic cursor

Cursor closure Condition
 
Does the SQLServer2000 Stored Procedure allow cursor nesting? What can I do if I can? Thanks

Yes, but it is best to use a transaction. Once a statement execution error occurs, the resource will be released.
In fact, the cursor in the stored procedure is the same as the for loop in other programming languages.