MySQL 5.5 创建存储步骤和函数

2024-07-24 12:32:25

字体：大中小

来源：转载

供稿：网友

执行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$$

（编辑：武林网）

上一篇：Shell脚本中执行sql语句操作MySQL数据库的几个办法

下一篇：mysql5.6单实例安置