mysql存储过程案例讲解

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))

BEGIN

INSERT INTO admin(admin.username,PASSWORD)

VALUES(username,loginpwd)

END $

CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))

BEGIN

SELECT b.name ,b.phone INTO NAME,phone

FROM beauty b

WHERE b.id = id

END $

CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)

BEGIN

SELECT DATEDIFF(birth1,birth2) INTO result

END $

CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))

BEGIN

SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate

END $

CALL test_pro4(NOW(),@str)

如 传入 ：小昭

返回： 小昭 AND 张无忌

DROP PROCEDURE test_pro5 $

CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))

BEGIN

SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str

FROM boys bo

RIGHT JOIN beauty b ON b.boyfriend_id = bo.id

WHERE b.name=beautyName

END $

CALL test_pro5('柳岩',@str)

DROP PROCEDURE test_pro6

CALL test_pro6(3,5)$

如果想带日志只有自己写了，可以使用row_count()返回改的的行数

例如

create procedure p_a1(in p_id int,in p_name varchar(20),out p_out varchar(200))

begin

declare p_cm int

set p_cm = 0

insert into a1(id,name) values(p_id,p_name)

select row_count() into p_cm

if (p_cm = 1)

then

set p_out = '成功'

else

set p_out = '不成功'

end if

end

mysql>call p_a1(5,'KK',@a2)//

Query OK, 1 row affected (0.01 sec)

mysql>select @a2//

+--------+

| @a2|

+--------+

| 成功 |

+--------+

1 row in set (0.01 sec)

以上来自手打，希望对你有帮助！呵呵～

---