MySQLストレージ・プロシージャとストレージ関数

1.ストアド・プロシージャとストアド関数
1.1.ストアド・プロシージャと関数の概要
ストレージ・プロシージャと関数は、事前にコンパイルされ、データベースに格納されたSQL文の集合であり、ストレージ・プロシージャと関数を呼び出すことで、アプリケーション開発者の多くの作業を簡素化し、データベースとアプリケーション・サーバ間のデータの転送を低減し、データの処理効率を向上させることができます.
ストアド・プロシージャと関数の違いは、関数には戻り値が必要であり、ストアド・プロシージャにはありません.
関数:戻り値のあるプロシージャです.
プロシージャ:戻り値のない関数です.
1.2.ストアド・プロシージャの作成
構文:

create procedure performance_name ([proc_parameter[....]])
begin
-- SQL  
end;

例:

delimiter $
create procedure pro_test1()
begin
    select 'Hello MySQL';
end$
delimiter ;

注意:delimiter、このフィールドはSQL文の区切り記号を宣言するために使用され、MySQL解釈器に、このコマンドが終了したかどうか、mysqlがデフォルトで実行できるかどうか、delimiterはセミコロンであることを伝えます.コマンドラインクライアントで、セミコロンで終了するコマンドラインがある場合は、車に戻るとmysqlがコマンドを実行します.
1.3.ストアド・プロシージャの呼び出し

call procedure_name(); # procedure_name        

1.4.ストアド・プロシージャの表示

--   db_name           
select name from mysql.proc where db = 'db_name';
--   ：select name from mysql.proc where db = 'mysql_senior';

--              
show procedure status;

--            
show create procedure mysql_senior.pro_test1;

1.5.ストアド・プロシージャの削除
構文:

drop procedure [if exists] sp_name;

例:

drop procedure pro_test1;

1.6.ストアドプロシージャ構文
ストレージ・プロシージャはプログラミング可能であり、変数、式、制御構造を使用して複雑な機能を達成できることを意味します.
1.6.1.変数＃ヘンスウ＃

DECLARE:

DECLAREでは、BEGIN…ENDブロックでのみ機能するローカル変数を定義できます.
構文:

DECLARE var_name[,...] type [DEFAULT value]

例:

delimiter $
create procedure pro_test2()
begin
    declare num int default 5;
    select num+10;
end $
delimiter ;

SET

直接付与はSETを使用し、定数または付与式を付与できます.
構文:

set var_name = expr [, var_name = expr] ...

例:

delimiter $
create procedure pro_test3()
begin
 declare NAME varchar(20);
 set NAME = 'MYSQL';
 select NAME;
end $
delimiter ;

select...into方式で付与操作も可能

delimiter $
create procedure pro_test4()
begin
    declare countnum int;
    select count(*) into countnum from city;
    select countnum;
end $

1.6.2.if条件判断
構文

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
end if;

実例:

  ：         ，              
-- 180    ------------>     
-- 170-180  ------------>     
-- 170     ------------>     

delimiter $
create procedure pro_test5()
begin
    declare height int default 175;
    declare message varchar(20);
    if height >= 180 then
        set message = '    ';
    elseif height >= 170 and height < 180 then
        set message = '    ';
    elseif height < 170 then
        set message = '    ';
    end if;
    select message;
end$
delimiter ;

1.6.3.伝達パラメータ
構文

create procedure procedure_name([in|out|inout]         )
...

/*
in    ：         ，           ，  。
out   ：       ，             
inout ：         ，         
*/

IN-入力
実例:定義身長変数に基づいて、現在の身長の属するスタイルタイプを判定する

delimiter $
create procedure pro_test6(in height int)
begin
    declare message varchar(20);
    if height >= 180 then
        set message = '    ';
    elseif height >= 170 and height < 180 then
        set message = '    ';
    elseif height < 170 then
        set message = '    ';
    end if;
    select concat('  :',height,'        :',message);
end $
delimiter ;

call pro_test6(175);

OUT-出力
実例:入力された身長変数に基づいて、現在の身長の属するスタイルタイプを取得します.

delimiter $
create procedure pro_test7(in height int,out message varchar(20))
begin
    if height >= 180 then
        set message = '    ';
    elseif height >= 170 and height < 180 then
        set message = '    ';
    elseif height < 170 then
        set  message = '    ';
    end if;
end $
delimiter ;

call pro_test7(175,@message);
select @message;

注意:
@messageという変数は変数名の前に@記号を付けて、ユーザーセッション変数と呼ばれ、セッションプロセス全体が機能していることを表しています.これはグローバル変数と同じです@global.sort_buffer_size:変数の前に@@記号を付けることをシステム変数と言います
INOUT-入力または出力
入出力の使用も悪くない
1.7.ケース構造
構文:

--    
case case_value
	when when_value then statemnet_list
	[when when_value then statement_list]...
	[else statement_list]
end case;

--    
case 
 when search_condition then statement_list
 [when search_condition then statement_list]
 [else statement_list]
end case;

実例:月を指定し、四半期を計算します.

delimiter $
create procedure pro_test8(month int)
begin
    declare result varchar(20);
    case
        when month >= 1 and month <= 3 then
        set result = '    ';
        when month >= 4 and month <= 6 then
        set result = '    ';
        when month >= 7 and month <= 9 then
        set result = '    ';
        when month >= 10 and month <= 12 then
        set result = '    ';
    end case;
    select concat('    ：',month,'      ：',result) as res;
end $
delimiter ;

call pro_test8(4);

1.8.whileサイクル
構文:

while search_condition do
	statement_list
end while;

**実例:**1からnまでの値を計算

delimiter $
create procedure pro_test9(n int)
begin
    declare total int default 1;
    declare num int default 1;
    while num <= n do
        set total = total+num;
        set num = num + 1;
    end while;
    select total;
end $
delimiter ;

call pro_test9(10);

1.9.repeat構造
条件付きループ制御文は、条件を満たすとループを終了し、whileは条件を満たすと実行され、repeatは条件を満たすとループを終了する
構文:

repeat
    statement_list
    until search_condition
end repeat;

**デモの例:**1からnへ

delimiter $
create procedure pro_test10(n int)
begin
    declare total int default 1;
    repeat
    set total = total + n;
    set n = n - 1;
    until n = 0
    end repeat;
    select total;
end $
delimiter ;

call pro_test10(10);

1.10.Loop文
loopは簡単なループを実現し、ループを終了する条件は他の文定義を使用する必要があり、通常leave文を使用して実現することができ、具体的な構文は以下の通りである.

[begin_label:]loop
 statement_list
end loop[end_label]

statementにいなければlistにループを終了する文を追加すると、loop文は簡単なデッドループを実現するために使用できます.
1.11.leave文
寸法のフロー構造から終了するために使用され、通常はbegin...endまたはループとともに使用されます.
loopとleaveの実装を用いてループを終了する簡単な例

delimiter $
create procedure pro_test11(n int)
begin
    declare total int default 0;
    ins:loop
        if n <= 0 then
            leave ins;
        end if;
        set total = total + n;
        set n = n - 1;
    end loop ins;
    select total;
end $
delimiter ;

call pro_test11(10);

1.12.カーソル/カーソル
カーソルはクエリー結果セットを格納するデータ型で、結果セットをカーソルでループ処理できます.カーソルの使用には、open、fetch、closeというカーソル宣言が含まれます.その構文は次のとおりです.

--     
declare cursor_name cursor for select_statement;

-- open  
open cursor_name;

-- fetch  
fetch cursor_name into var_name[,var_name]...

-- close  
close cursor_name;

例:

--      
create table emp(
 id int(11) not null auto_increment,
 name varchar(50) not null comment '  ',
 age int(11) comment '  ',
 salary int(11) comment '  ',
 primary key (id)
) engine=innodb default charset =utf8;

insert into emp(id,name,age,salary) values (null,'    ',55,3800),(null,'    ',60,4000),(null,'    ',38,2800),(null,'    ',42,1800);

--   emp     ，         
delimiter $
create procedure pro_test12()
begin
 declare e_id int(11);
 declare e_name varchar(50);
 declare e_age int(11);
 declare e_salary int(11);
 declare emp_result cursor for select * from emp;

 open emp_result;

 fetch emp_result into e_id,e_name,e_age,e_salary;
 select concat('id=',e_id,',name=',e_name,',age=',e_age,',   :',e_salary);

 fetch emp_result into e_id,e_name,e_age,e_salary;
 select concat('id=',e_id,',name=',e_name,',age=',e_age,',   ：',e_salary);

 fetch emp_result into e_id,e_name,e_age,e_salary;
 select concat('id=',e_id,',name=',e_name,',age=',e_age,',   ：',e_salary);

 fetch emp_result into e_id,e_name,e_age,e_salary;
 select concat('id=',e_id,',name=',e_name,',age=',e_age,',   ：',e_salary);

 fetch emp_result into e_id,e_name,e_age,e_salary;
 select concat('id=',e_id,',name=',e_name,',age=',e_age,',   ：',e_salary);

 close emp_result;
end$;
delimiter ;

call pro_test12();

#       ，        

delimiter $
create procedure pro_test13()
begin 
 declare id int(11);
 declare name varchar(50);
 declare age int(11);
 declare salary int(11);
 declare has_data int default 1;

 declare emp_result cursor for select * from emp;

 declare exit handler for not found select has_data = 0;

 open emp_result;
 repeat 
 fetch emp_result into id,name,age,salary;
 select concat('id=',id,',name=',name,',age=',age,',   ：',salary);
 until has_data = 0  end repeat;
 close emp_result;
end $
delimiter ;

call pro_test13();

1.13.きおくかんすう
構文:

--   
create function function_name([param type ...])
returns type
begin
    ...
end;

ケース:cityテーブルの合計レコード数を返すストレージ関数を定義します.

delimiter $
create function fun1(countryId int)
returns int
begin
    declare num int;
    select count(*) into num from city where country_id = countryId;
    return  num;
end $
delimiter ;

#          
select fun1(1);

#     
drop function fun1;