mysql 存储过程 on_MySQL存储过程+自定义函数 简单示例

drop table if exists students;

CREATE TABLE `students` (

`stu_id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT '学号',

`stu_name` VARCHAR(50) NULL DEFAULT '' COMMENT '姓名',

`stu_sex` INT(11) NULL DEFAULT '0' COMMENT '性别',

`cla_id` BIGINT(20) NULL DEFAULT '0' COMMENT '班级ID',

`stu_phone` BIGINT(20) NULL DEFAULT '0' COMMENT '手机号',

PRIMARY KEY (`stu_id`)

)

COLLATE='latin1_swedish_ci'

ENGINE=InnoDB;

delimiter //

drop function if exists print //

create function print (str varchar(100)) returns varchar(100)

begin

declare x varchar(100) default '';

set x = str;

return x;

end

/

delimiter ;

select print('hhehehe');

delimiter //

drop function if exists rand_string //

create function rand_string(n int) returns varchar(100)

begin

declare low_str varchar(255) default 'abcdefghijklmnopqrstuvwxyz';

declare up_str varchar(255) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

declare i int default 0;

declare return_str varchar(255) default '';

while i < n do

if i = 0 then

set return_str = concat(return_str, substring(up_str, floor(1 + 26 * rand()), 1));

else

set return_str = concat(return_str, substring(low_str, floor(1 +26 * rand()), 1));

end if;

set i = i + 1;

end while;

return return_str;

end

/

delimiter ;

select rand_string(5);

delimiter //

drop function if exists rand_sex //

create function rand_sex() returns int

begin

declare sex int default 0;

set sex = round(rand());

return sex;

end

/

delimiter ;

select rand_sex();

delimiter //

drop procedure if exists stu_inserts;

create procedure stu_inserts(in n int)

begin

declare stu_id bigint default 10001;

declare stu_name varchar(50) default '';

declare stu_sex int default 1;

declare cla_id bigint default 1;

declare i int default 0;

while i < n do

set stu_name = rand_string(5);

set stu_sex = rand_sex();

if stu_id % 100 = 0 then

set cla_id = cla_id + 1;

end if;

insert into students(stu_id, stu_name, stu_sex, cla_id) values(stu_id, stu_name, stu_sex, cla_id);

set stu_id = stu_id + 1;

end while;

end

/

delimiter ;

call stu_inserts(2000);

set @ss = 'abcdefghijklmnopqrstuvwxyz';

select length(@ss);

select substring(@ss, 28, 1);