0624-log

せっかくなので、そのままコピペ

use test;
drop procedure param_proc_one if exists param_proc_one;
create procedure param_proc_one(in param1 int, out param2 int, inout param3 int)
select param1, param2, param3;
set @value1=10, @value2=200, @value3=300;
call param_proc_one(@value1, @value2, @value3);

drop procedure param_proc_two if exists param_proc_two;
create procedure param_proc_two(in param1 int, out param2 int, inout param3 int)
set param1=1, param2=2, param3=3;

call param_proc_two(@value1, @value2, @value3);

select @value1, @value2, @value3;

drop function hello1 if exists hello1;
create function hello1(input char(20)) returns char(50) return concat('Hello, ', input, '!');

select hello1('tttttttttttt');

-----

use world;
delimiter //

create procedure country_info(in c_code char(3))
begin
select * from city where countrycode=c_code;
select * from country where code=c_code;
select * from countrylanguage where countrycode=c_code;
end//

delimiter ;

call country_info('NLD');

-----

delimiter //
create function pop_percentage(c_code char(3))
returns decimal (4,2)
begin
select sum(population) from country into @WorldPop;
select population from country where code=c_code into @CountryPop;
return 100*@CountryPop/@WorldPop;
End//
delimiter ;

select pop_percentage('JPN');

select @CountryPop, @WorldPop;

-----

delimiter //
create function docase(1st double(10,2), 2nd double(10,2), formula char(15)) returns char(30)
begin
case formula

when 'M' then
return concat(1st, 2nd);

when 'D' then
return concat(1st, 2nd);

when 'A' then
return concat(1st, 2nd);

when 'S' then
return concat(1st, 2nd);

else
return 'Invalid,,,';

end case;

end//

delimiter ;

select docase(10,5,'A');
-----

delimiter //
create procedure dowhile(p1 int)
begin
declare var_x int;
set var_x=0;
while var_x < p1 do

set var_x=var_x+1;
select concat('x is', var_x) as while_counter;
end while;
select concat('final is', var_x) as while_results;
end //
delimiter ;

call dowhile(10);

-----
delimiter //
create procedure doloopif(p1 int)
begin
declare var_x int;
set var_x=0;
loop_test: loop

if var_x set var_x=var_x+1;
else
leave loop_test;
end if;

end loop loop_test;

select concat('final is', var_x);

end//
delimiter ;

call doloopif(100);

-----
delimiter //
create procedure dorepeat(p1 int)
begin
declare var_x int;
set var_x=0;
repeat set var_x=var_x+1;
select concat('x is', var_x) as repeat_counter;
until var_x > p1
end repeat;
select concat('final is', var_x) as repeat_results;
end//
delimiter ;

call dorepeat(10);

-----

create table d_table(s1 int, primary key(s1));

delimiter //
create procedure dohandler()

begin
declare dup_keys condition
for sqlstate '23000';
declare continue handler for
dup_keys set @garbage=1;
set @x=1;
insert into world.d_table values(1);
set @x=2;
insert into world.d_table values(1);
set @x=3;
end//
delimiter ;

call dohandler();

select @x;

select @garbage;
-----

delimiter //
create function g_table(tbl_name char(30))
returns int
begin
declare good_table int default 0;
begin
declare show_table char(30);
declare done1 int default 0;
declare cur1 cursor for show tables;
declare continue handler for sqlstate '02000' set done1=1;
open cur1;
gt_loop:repeat
fetch cur1 into show_table;
if not done1 then
if show_table=tbl_name
then
set good_table=1;
leave gt_loop;
end if;
end if;
until done1 end repeat;
return good_table;
close cur1;
end;
end//
delimiter ;

select g_table('howdy');

select g_table('country');

-----
use world;

show tables;

create table dotriggers(column1 int);

create table audit_triggers(old_column int, new_column int, date_completed datetime);

create trigger dotriggers_ai after insert on dotriggers for each row
insert into audit_triggers(new_column, date_completed) values(new.column1, now());

insert into dotriggers values(1), (2), (3), (4), (5), (6);

select * from dotriggers;

select * from audit_triggers;

---

Trigger: TRNファイルとTRGファイルが作成される

Function/Procedure: mysql.procに作成される

　mysqldump --routinesでダンプできる