求个简单的mysql 存储过程例子
需求:插入一条数据,惟一索引列存在,则进行select查询并返回主键ID.不存在则获取到主键ID.
最后输出主键ID.

[解决办法]
主键是整型自增:

SQL code

mysql> delimiter //mysql> drop procedure if exists insert_pro//mysql> create procedure insert_pro(uname varchar(100))    -> begin    -> declare idx int;    -> select id into idx from foo where memo = uname;    -> if ifnull(idx,0) = 0    -> then    -> #insert into foo(memo) values(uname);/*不存在插入*/    -> #select LAST_INSERT_ID() as not_find; /*插入后返回ID*/    -> select max(id)+1 as PK from foo; /*不存在返回主键*/    -> end if;    -> if idx >0    -> then    -> select * from foo where memo=uname;/*存在显示整行*/    -> end if;    -> end //mysql> delimiter ;mysql> select * from foo;id      memo1       f5       e9       a10      b11      cmysql> call insert_pro('QQ');PK12mysql> call insert_pro('f');id      memo1       f
[解决办法]
create procedure sp_test(a int)
begin
if exists (select 1 from tb where num=a)  
then 
  select id from tb where num=2;
else
  insert into tb(num) values(a);
  select last_insert_id();
end if;
end