MySQL 5.5 创建存储步骤和函数
发布时间：2022-04-07 19:29:12 所属栏目：MySql教程 来源：互联网
导读：执行CREATE PROCEDURE和CREATE FUNCTION语句需要CREATE ROUTINE权限。 授权 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) 注意：在命令行缩进时，
|
执行CREATE PROCEDURE和CREATE FUNCTION语句需要CREATE ROUTINE权限。 授权 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$$ （编辑：济南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读