授權 mysql> grant create routine on fire.* to neo; Query OK, 0 rows affected (0.12 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
注意：在命令行縮進時，不要用tab，要使用空格，否則會報下面的錯 DATE INNER MULTILINESTRING SET UNICODE warnings DATEDIFF INNOBASE MULTILINESTRINGFROMTEXT SHA UNION DATETIME INNODB MULTILINESTRINGFROMWKB SHA1 UNIQUE DATE_ADD INOUT MULTIPOINT SHARE UNIQUE_USERS -> Info; -> Display all 903 possibilities? (y or n)
授權 mysql> grant execute on fire.* to neo; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
創建不含參數的存儲過程，和Oracle不同的是，存儲過程名字后面必須要有() mysql> delimiter $$ mysql> create procedure proc_Subscribers_update() -> begin -> DECLARE v_count INT; -> select ifnull(max(a),0) into v_count from t2; -> while v_count < 2 do -> select concat('the maximum value is ',v_count); -> set v_count = v_count+1; -> end while; -> end$$ Query OK, 0 rows affected (0.06 sec)
創建包含傳入參數的存儲過程 delimiter $$ create procedure proc_Subscribers_update(IN v_fetch_cnt INT, IN v_sleep_secs INT) begin DECLARE v_count INT; DECLARE v_times INT DEFAULT 1; DECLARE v_max_value INT; /*compute the times that the loop runs*/ select ceil(count(MSISDN))/v_fetch_cnt into v_count from tmp_Subscribers_01; /*compute the maximum rows that have been already updated*/ WHILE v_times < v_count DO select ifnull(max(id),0) into v_max_value from tmp_Subscribers_02; if v_max_value < v_fetch_cnt * v_count then SET v_times = 1 + floor(v_max_value/v_fetch_cnt); update Subscribers s,tmp_Subscribers_01 t set s.LastAccessTimeStamp=1420066800 where s.MSISDN=t.MSISDN and t.id > v_max_value and t.id <= v_fetch_cnt * v_times; /*record the processing rows*/ insert into tmp_Subscribers_02 select id, MSISDN, now() from tmp_Subscribers_01 where id = v_fetch_cnt * v_times; select concat('The job',' has already updated ', v_fetch_cnt * v_times, ' rows..') as Info; select sleep(v_sleep_secs); end if; commit; END WHILE; select concat('The job',' is ','finished!') as Info; commit; end$$
