Mysql存储过程实例

千万别忘了
delimiter // 
啊！
否则要出大事！！
## **********first test,procedure**********
#<1>
use testprocedure;
delimiter //
create procedure simpleproce1 (out par1 int)
begin
 select count(*) into par1 from proce;
end
//
delimiter ;
call simpleproce1(@a);
select @a;
#<2>,每次只有单一的行可以被取回select id,name into par1,par2 from proce LIMIT 1;中的LIMIT 1;
use testprocedure;
delimiter //
DROP procedure IF EXISTS simpleproce2
create procedure simpleproce2 (out par1 int,out par2 char(30))
begin
 select id,name into par1,par2 from proce LIMIT 1;
end
//
delimiter ;
call simpleproce2(@a,@b);
select @a,@b;
 
## *********second test,function************
#<3>
delimiter //
DROP FUNCTION IF EXISTS hello
//
create function hello(s char(20)) returns char(50)
  return concat('Hello, ',s,'!');
//
delimiter ;
select hello('world');
show create function testprocedure.hello\G
  #它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期
show function status like 'hello'\G
#<4>
#注意name不能和字段名相同
delimiter //
DROP procedure IF EXISTS test //
CREATE PROCEDURE test ()
  BEGIN
    DECLARE name VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
   
    SELECT name,id INTO newname,xid
      from proce WHERE name = name;
    SELECT newname;
  END;
//
call test1() //
#***
delimiter //
DROP procedure IF EXISTS test2 //
CREATE PROCEDURE test2 ()
  BEGIN
   
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
   
    SELECT name,id INTO newname,xid
      from proce limit 1;
    SELECT newname,xid;
  END;
//
call test2() //
#<5>
use testprocedure;
CREATE PROCEDURE