procedure/function/trigger

为什么80%的码农都做不了架构师？>>>   

--定义一个新的命令结束符号,默认的是以;为结束标记
--同样的可以通过delimiter ;再设置;为结束标记
delimiter $$
--删除函数rand_string
drop 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 PROCEDURE
IF 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

转载于:https://my.oschina.net/lhplj/blog/206037