--定义一个新的命令结束符号,默认的是以;为结束标记--同样的可以通过delimiter ;再设置;为结束标记delimiter $$--删除函数rand_stringdrop function rand_string $$--创建函数rand_string(n):随机产生n个字符组成的字符串create function rand_string(n INT)returns varchar(255)begin declare chars_str varchar(100）default 'abcefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do --concat('a','b')：ab --substring(str,pos,len):得到字符串str从pos位置开始长度为len的字符串 --rand():得到一个[0,1]的随机小数 set return_str = concat(return_str,substring(chars_str,floor(1 + rand() * 52),1)); set i = i + 1; end while; return return_str;end $$delimiter ; --修改语句定界符为$$delimiter $$--自定义函数rand_num():随机生成一个整数create function rand_num()returns int(5)begin declare i int defaule 0; set i = floor(10 + rand() * 50); return i;end $$--修改语句定界符为;delimiter ;--修改语句定界符为$$delimiter $$--创建一个存储过程create procedure insert_emp(in start int(10), in max_num int(10))begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into emp values(start + i, rand_string(6), 'SALESMAN'.0001,curdate(), 2000); until i = max_num end repeat; commit;end $$--修改语句定界符为;delimiter ;--调用存储过程call inert_emp(100001, 4800000);
存储过程例子,写于2015-5-22 23:20,基于dzg项目
delimiter $$DROP PROCEDUREIF EXISTS update_purchase_pro_refundable$$CREATE PROCEDURE update_purchase_pro_refundable ( IN productId VARCHAR (50), IN supplierId VARCHAR (50), IN shopId VARCHAR(50), IN counts INT(10))BEGIN DECLARE purchaseProId VARCHAR(50); DECLARE error_status INT DEFAULT 0 ; DECLARE datas CURSOR FOR ( SELECT pp.id FROM purchase_pro pp LEFT JOIN purchase pur ON pp.purchase_id = pur.id WHERE pur.shop_id = shopId AND pp.product_id = productId AND pp.supplier_id = supplierId ORDER BY pur.time DESC LIMIT counts ); DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1; UPDATE purchase_pro pp LEFT JOIN purchase pur ON pp.purchase_id=pur.id SET refundable = '0' WHERE product_id = productId AND supplier_id = supplierId AND pur.shop_id=shopId; OPEN datas; FETCH datas INTO purchaseProId; REPEAT UPDATE purchase_pro SET refundable='1' WHERE id=purchaseProId; FETCH datas INTO purchaseProId; UNTIL error_status END REPEAT; CLOSE datas;END$$delimiter ;
查看所有function
show function status
调用函数
select 函数名称()
查看某个存储过程的定义
show create function 函数名称查看所有存储过程
show procedure status查看某个存储过程的定义
show create procedure 存储过程名称
调用存储过程
call 存储过程名称()
查看所有事件
show events
查看所有触发器
show triggers