daily pastebin goal
19%
SHARE
TWEET

Untitled

a guest Feb 22nd, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DELIMITER //
  2.  
  3. DROP PROCEDURE IF EXISTS apply_credit//
  4.  
  5. CREATE PROCEDURE apply_credit()
  6. BEGIN
  7.  
  8.   DECLARE done tinyint DEFAULT 0;
  9.   DECLARE _pk_id INT;
  10.   DECLARE _eid, _source VARCHAR(255);
  11.   DECLARE _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count DECIMAL;
  12.  
  13.   DECLARE commission CURSOR FOR
  14.     SELECT pk_id, eid, source, lh_revenue, acc_revenue, project_carrier_expense, carrier_lh, carrier_acc, gross_margin, fsc_revenue, revenue, load_count FROM ct_sales_commission;
  15.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  16.  
  17.   DELETE FROM debug;
  18.  
  19.   OPEN commission;
  20.  
  21.   REPEAT
  22.     FETCH commission INTO
  23.       _pk_id, _eid, _source, _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count;
  24.     INSERT INTO debug VALUES(concat('row ', _pk_id));
  25.  
  26.   UNTIL done = 1 END REPEAT;
  27.  
  28.   CLOSE commission;
  29.  
  30. END//
  31.  
  32. DELIMITER ;
  33. CALL apply_credit();
  34. SELECT * FROM debug;
  35.    
  36. SET done = 0;
  37. OPEN commission;
  38. FETCH ...
  39. commission_loop:WHILE NOT done DO
  40.    INSERT INTO ...
  41.    FETCH ...
  42. END WHILE commission_loop;
  43. CLOSE commission;
  44.    
  45. SET done = 0;
  46. OPEN commission;
  47. commission_loop:WHILE NOT done DO
  48.    FETCH ...
  49.    IF done THEN
  50.       LEAVE commission_loop;
  51.    END IF;  
  52.    INSERT INTO ...
  53. END WHILE commission_loop;
  54. CLOSE commission;
  55.    
  56. INSERT INTO debug
  57. SELECT concat('row ', _pk_id)
  58. FROM ct_sales_commission;
  59.    
  60. INSERT DELAYED INTO ... VALUES(....)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top