MySQLのProcedureを作成、データ挿入、使い方まとめ

2014年12月10日

1.テーブルの作成
–create table
create table Procedure_startnews245 (
Procedure_startnews245ID int(11) unsigned not null auto_increment,
ProcedureName varchar(50) not null,
AddTime datetime not null,
primary key(Procedure_startnews245ID)
) engine=InnoDB default charset=utf8;

2.データの挿入
–insert data
insert into Procedure_startnews245 values(null, 'steven’, '2014-11-28 09:16:17’);

3.データの確認
–view data
select * from Procedure_startnews245;
–set delimiter //
delimiter //

4.プロシージャを作成
–create procedure
create procedure steven(IN IN_Procedure_startnews245ID int , OUT OUT_ProcedureName varchar(50), INOUT INOUT_Result varchar(255))
begin
select IN_Procedure_startnews245ID;
select OUT_ProcedureName;
select INOUT_Result;
select ProcedureName into OUT_ProcedureName from Procedure_startnews245 where Procedure_startnews245ID = IN_Procedure_startnews245ID;
set IN_Procedure_startnews245ID = IN_Procedure_startnews245ID + 1;
set INOUT_Result = concat(INOUT_Result, OUT_ProcedureName);
end
//

–set data
set @Procedure_startnews245ID = 1//
set @procedureName = 'heno’//
set @result = 'ready’//

5.プロシージャを呼び出す
–call procedure
call steven(@Procedure_startnews245ID, @procedureName, @result)//
–view data
select @Procedure_startnews245ID//
select @procedureName//
select @result//
–set delimiter ;
delimiter ;
–show procedure detail
show create procedure steven;
–delete procedure
drop procedure steven;