mysql存储过程基础实例

DROP TABLE IF EXISTS TEST_USER;
CREATE TABLE TEST_USER(
id int primary key not null,
name varchar(255),
age int,
sex varchar(2)
);
insert into  TEST_USER (id,name,age,sex) values(1,"测试名称1",8,"男");
insert into  TEST_USER (id,name,age,sex) values(2,"测试名称2",14,"男");
insert into  TEST_USER (id,name,age,sex) values(3,"测试名称3",13,"男");
insert into  TEST_USER (id,name,age,sex) values(4,"测试名称4",28,"男");
insert into  TEST_USER (id,name,age,sex) values(5,"测试名称5",13,"男");
insert into  TEST_USER (id,name,age,sex) values(6,"测试名称6",8,"男");
insert into  TEST_USER (id,name,age,sex) values(7,"测试名称1",28,"男");

CREATE PROCEDURE PBase()
BEGIN
SELECT * FROM TEST_USER;
END

call PBase();

DROP PROCEDURE IF EXISTS P1;
CREATE PROCEDURE P1(IN maxAge INT)
BEGIN
SELECT * FROM TEST_USER t WHERE t.age>maxAge;
END

call P1(10);

drop PROCEDURE if exists P2;
create PROCEDURE P2(out total int)
begin
select count(id) into total from test_user;
end;

call p2(@out);
select @out

DROP PROCEDURE IF EXISTS P3;
CREATE PROCEDURE P3(IN maxAge INT,OUT total INT)
BEGIN
SELECT count(id) into total FROM  TEST_USER t WHERE t.age>maxAge;
END

DROP PROCEDURE IF EXISTS P4;
CREATE PROCEDURE P4(INOUT param INT)
BEGIN
IF param = 10 THEN
SELECT count(id) into param FROM  TEST_USER t;
ELSE
SELECT count(id) into param FROM  TEST_USER t WHERE t.age<param;
END IF;
END

set @param=10;
call P4(@param);
select @param;

set @param=9;
call P4(@param);
select @param;

mysql 数据库 存储过程

http://blog.xqlee.com/article/77.html