lee 1341 0 2017-02-21 21:07:00

文章位置:左搜> 编程技术> 正文
1.选择一个数据库创建表TEST_USER,并插入一些测试数据
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,"男");
执行结果:
执行结果,模拟数据
2.创建一个最基础的存储过程
CREATE PROCEDURE PBase()
BEGIN
SELECT * FROM TEST_USER;
END
执行结果:
基础1
执行基础的存储过程:
call PBase();
2
3.创建一个带输入参数的存储过程
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);
执行结果
4.创建一个带有输出参数的存储过程
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
输出
5.创建一个带输入参数又带输出参数的存储过程
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
执行:
3
6.创建一个即是输入又是输出参数并且喊判断语句的存储过程
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

执行1:参数10
set @param=10;
call P4(@param);
select @param;

1111
执行:参数9
set @param=9;
call P4(@param);
select @param;
123

评论区域

暂无评论,快来抢首发吧!!!