mysql触发器和存储过程_mysql的触发器和存储过程

触发器

create table ps_1(

1_id int(11),

1_url varchar(255),

1_url_crc int(11)

)

create table ps_2(

2_id int(11),

2_url varchar(255),

2_url_crc int(11)

)

create trigger ps_crc_ins before insert on ps_1 for each row

begin

INSERT INTO ps_2(2_id,2_url,2_url_crc) values(new.1_id,new.1_url,new.1_url_crc);

end;

测试：

INSERT into ps_1(1_id,1_url,1_url_crc) values(1,'http://baidu.com','44121')

当在ps_1中插入数据时，ps_2中也会插入数据

存储过程：

CREATE TABLE `users` (

`ID` int(10) NOT NULL AUTO_INCREMENT,

`USERNAME` varchar(10) NOT NULL,

`PASSWORD` varchar(32) NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

CREATE  PROCEDURE num_from_users (IN u_id INT, OUT count_num INT )

BEGIN

SELECT  COUNT(*)  INTO  count_num

FROM  USERS

WHERE  id=u_id ;

END

CALL num_from_users(1,@a);

select @a;

变量需要加@,而且存储过程只是给@a定义了一个值，要得到a值要通过select

函数function(函数没有输出值，只有输入值)：

CREATE TABLE `users` (

`ID` int(10) NOT NULL AUTO_INCREMENT,

`USERNAME` varchar(10) NOT NULL,

`PASSWORD` varchar(32) NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

create procedure proc_name (in parameter integer) begin if parameter=0 then select * from users order by id asc; else select * from users order by id desc; end if; end; call proc_name(1)