create table aa(
    id int auto_increment primary key,
    sname varchar(32) unique
);
insert into aa values(5,'abc');

create procedure 过程名(参数...)
begin
    SQL语句...
end

call 过程名(实参)

delimiter &&
create procedure p1()
begin
    insert into stud values('P100','小李',43);
    select * from stud;
end&&
delimiter ;
call p1;/*调用p1()中的SQL语句,如果没有call之前，p1()中的SQL语句是没有被执行的*/

delimiter &&
create procedure p2(in id varchar(32),in sname varchar(32),in age int)
begin
    insert into stud values(id,sname,age);
    select * from stud;
end &&
delimiter ;
call p2('P1007','小白',25);
call p2('P1008','小丽',28);

delimiter &&
create procedure p3(in id varchar(32) ,in sname varchar(32),in age int ,out num int)
begin
    insert into stud values(id,sname,age);
    select * from stud;
    select count(*) into num from stud;
end&&
delimiter ;
call p3('P012','小小五',27, @num);
/*调用且用num接收结果*/
select @num; /*显示用户变量num*/

select * from stud where sname like 'J%';

select * from stud where binary sname like 'J%';