MYSQLストレージプロセスとストレージ関数の入門
一、MYSQLストレージプロセスの概要:ストレージプロセスはプログラム可能な関数であり、データベースに作成され、保存される.SQL文と特殊な制御構造で構成できます.ストレージ・プロシージャは、異なるアプリケーションまたはプラットフォームで同じ関数を実行したり、特定の機能をカプセル化したりする場合に便利です.データベース内のストレージ・プロシージャは,プログラミングにおけるオブジェクト向けメソッドのシミュレーションと見なすことができる.データへのアクセスを制御できます.ストレージ・プロシージャには、通常、次のような利点があります.
1)ストレージプロセスはより速い実行速度を実現できる.オペレーションに多数のTransaction-SQLコードが含まれている場合、またはそれぞれ複数回実行されている場合、ストレージ・プロシージャはバッチの実行よりもはるかに高速です.ストレージ・プロシージャはプリコンパイルされているためです.オプティマイザは、ストレージ・プロシージャを最初に実行するときにクエリーを分析して最適化し、最終的にシステム・テーブルに格納される実行計画を与えます.バッチのTransaction-SQL文は、実行するたびにコンパイルおよび最適化され、比較的遅い速度で実行されます.心得:コンパイル最適化、急げ!
2)ストレージプロセスは、標準コンポーネントがプログラミングであることを可能にする.ストレージ・プロシージャが作成されると、そのストレージ・プロシージャのSQL文を再記述することなく、プログラムで複数回呼び出されます.また、データベース・プロフェッショナルは、アプリケーションのソース・コードに影響を与えることなく、ストレージ・プロシージャをいつでも変更できます.心得:パッケージと抽象、簡単な呼び出し
3)ストレージプロセスはフロー制御文で記述することができ、柔軟性が強く、複雑な判断と複雑な演算を完成することができる.心得:機能が強く、論理が強い
4)ストレージプロセスは、セキュリティメカニズムとして十分に利用することができる.システム管理者はあるストレージプロセスの権限を実行することによって制限を行い、相応のデータに対するアクセス権限の制限を実現することができ、非授権ユーザーのデータに対するアクセスを回避し、データの安全を保証した.心得:制限と安全
5)ストレージプロセスはネットワークトラフィックを過度に減少させることができる.同じデータベース・オブジェクトの操作(クエリー、変更など)に対して、この操作に関連するTransaction-SQL文がプロシージャ・ストレージ・プロシージャに編成されている場合、クライアント・コンピュータ上でストレージ・プロシージャを呼び出すと、ネットワークで転送されるのは呼び出し文だけであり、ネットワーク・トラフィックが大幅に増加し、ネットワーク・ロードが低減されます.心得:ネットワークトラフィックを減らす(パッケージが良い)
二.MySQLが作成する最も簡単なストレージ・プロシージャ「pr_add」は、intタイプの入力パラメータ「a」、「b」の2つのパラメータの和を返す簡単なMySQLストレージ・プロシージャです.
三、MySQLストアド・プロシージャの特徴MySQLストアド・プロシージャを作成する簡単な構文は:create procedureストアド・プロシージャ名([i n|out|inout]パラメータdatatype)begin MySQL文;end; MySQLストアド・プロシージャ・パラメータ「in」、「out」、「inout」を明示的に指定しない場合、デフォルトは「in」です.習慣的に、inのパラメータについては、明示的に指定しません.1 MySQLストアド・プロシージャの名前の後ろにある「()」は必須で、パラメータが1つもなくても「()」2 MySQLストアド・プロシージャのパラメータが必要です.パラメータ名の前に「@a int」を付けることはできません.次の作成ストアド・プロシージャ構文はMySQLでは正しくありません(SQL Serverでは正しい).MySQLストレージ中の変数は、変数名の前に「@」を付ける必要はありません.MySQLクライアントのユーザー変数には「@」を付ける必要がありますが.
3 MySQLストアド・プロシージャのパラメータはデフォルト値を指定できません.4 MySQLストアド・プロシージャは、procedure bodyの前に「as」を追加する必要はありません.SQL Serverストレージ・プロシージャには、「as」キーを付ける必要があります.
5 MySQLストレージに複数のMySQL文が含まれている場合は、begin endキーが必要です.
6 MySQLストレージの各文の末尾には、セミコロン";"を付けます.
7 MySQLストレージで「return」キーを使用することはできません.
8 MySQLストアド・プロシージャを呼び出す場合は、プロシージャ名の後に「()」を付ける必要があります.パラメータが1つもなくても「()」が必要です.呼び出しoutおよびinoutパラメータのフォーマットは@arguments_です.name形式.
9 MySQLストアド・プロシージャ・パラメータにデフォルト値がないため、MySQLストアド・プロシージャを呼び出す際にパラメータを省略することはできません.nullで代用できます.
1,実戦の前提はMySQL 5以上を必要として、私はMYSQLのクライアントNavicat Premiumを使って、貼ったコードはすべて私が変異して間違いがないのです.読者がクライアントをインストールしていないか、パソコンでエラーを報告していない場合は、DELIMITER//とDELIMITERを使用する必要があります.2つの文、DELIMITERは分割記号の意味で、MySQLがデフォルトであるためです;”区切り記号の場合、分割記号を宣言していない場合、コンパイラはストレージ・プロシージャをSQL文として処理します.ストレージ・プロシージャのコンパイル・プロシージャはエラーを報告します.事前にDELIMITERキーワードで現在のセグメント区切り記号を明記しておくと、MySQLは「保存中のコードとして、これらのコードは実行されません.使い終わったら、区切り文字を復元します.
2,変数はDECLAREを使用して宣言され,DEFAULTはデフォルト値を付与し,SETはJavaコードを付与する.
3,条件判断IF THEN,ELSEIF,ELSE,END IF
4,ループLOOP、END LOOP
5、WHILE DO、END WHILE
6、REPEAT、UNTILL
7,ストレージメソッドストレージ方法とストレージプロセスの違い1,ストレージメソッドのパラメータリストはINタイプのパラメータのみを許可し,INキーワード2を指定する必要もなく,ストレージメソッドは単一の値を返し,値のタイプはストレージメソッドのヘッダ定義3,ストレージメソッドはSQL文の内部で4を呼び出すことができ,ストレージメソッドは結果セット構文を返すことができない:
単純なストレージ関数の例
8.フリップフロップがINSERT、UPDATEまたはDELETEなどのDML文でデータベース表を修正する際にフリップフロップをトリガーする典型的な応用シーンは重要なビジネスロジック、性能向上、監視表の修正などのフリップフロップがDML文の実行前または後にトリガーすることができる
1)ストレージプロセスはより速い実行速度を実現できる.オペレーションに多数のTransaction-SQLコードが含まれている場合、またはそれぞれ複数回実行されている場合、ストレージ・プロシージャはバッチの実行よりもはるかに高速です.ストレージ・プロシージャはプリコンパイルされているためです.オプティマイザは、ストレージ・プロシージャを最初に実行するときにクエリーを分析して最適化し、最終的にシステム・テーブルに格納される実行計画を与えます.バッチのTransaction-SQL文は、実行するたびにコンパイルおよび最適化され、比較的遅い速度で実行されます.心得:コンパイル最適化、急げ!
2)ストレージプロセスは、標準コンポーネントがプログラミングであることを可能にする.ストレージ・プロシージャが作成されると、そのストレージ・プロシージャのSQL文を再記述することなく、プログラムで複数回呼び出されます.また、データベース・プロフェッショナルは、アプリケーションのソース・コードに影響を与えることなく、ストレージ・プロシージャをいつでも変更できます.心得:パッケージと抽象、簡単な呼び出し
3)ストレージプロセスはフロー制御文で記述することができ、柔軟性が強く、複雑な判断と複雑な演算を完成することができる.心得:機能が強く、論理が強い
4)ストレージプロセスは、セキュリティメカニズムとして十分に利用することができる.システム管理者はあるストレージプロセスの権限を実行することによって制限を行い、相応のデータに対するアクセス権限の制限を実現することができ、非授権ユーザーのデータに対するアクセスを回避し、データの安全を保証した.心得:制限と安全
5)ストレージプロセスはネットワークトラフィックを過度に減少させることができる.同じデータベース・オブジェクトの操作(クエリー、変更など)に対して、この操作に関連するTransaction-SQL文がプロシージャ・ストレージ・プロシージャに編成されている場合、クライアント・コンピュータ上でストレージ・プロシージャを呼び出すと、ネットワークで転送されるのは呼び出し文だけであり、ネットワーク・トラフィックが大幅に増加し、ネットワーク・ロードが低減されます.心得:ネットワークトラフィックを減らす(パッケージが良い)
二.MySQLが作成する最も簡単なストレージ・プロシージャ「pr_add」は、intタイプの入力パラメータ「a」、「b」の2つのパラメータの和を返す簡単なMySQLストレージ・プロシージャです.
drop procedure if exists pr_add;// pr_add , ; , 。 pr_add ,
//
create PROCEDURE pr_add ( in a int, in b int ) begin declare c int;//declare
if a is null THEN
set a=1;
end if;
if b is null THEN
set b=1;
end if;
set c=a+b;
select c as sum;
end;
call pr_add( null,null );// pr_add
三、MySQLストアド・プロシージャの特徴MySQLストアド・プロシージャを作成する簡単な構文は:create procedureストアド・プロシージャ名([i n|out|inout]パラメータdatatype)begin MySQL文;end; MySQLストアド・プロシージャ・パラメータ「in」、「out」、「inout」を明示的に指定しない場合、デフォルトは「in」です.習慣的に、inのパラメータについては、明示的に指定しません.1 MySQLストアド・プロシージャの名前の後ろにある「()」は必須で、パラメータが1つもなくても「()」2 MySQLストアド・プロシージャのパラメータが必要です.パラメータ名の前に「@a int」を付けることはできません.次の作成ストアド・プロシージャ構文はMySQLでは正しくありません(SQL Serverでは正しい).MySQLストレージ中の変数は、変数名の前に「@」を付ける必要はありません.MySQLクライアントのユーザー変数には「@」を付ける必要がありますが.
create procedure pr_add ( @a int,// b int // )
3 MySQLストアド・プロシージャのパラメータはデフォルト値を指定できません.4 MySQLストアド・プロシージャは、procedure bodyの前に「as」を追加する必要はありません.SQL Serverストレージ・プロシージャには、「as」キーを付ける必要があります.
create procedure pr_add ( a int, b int ) as - ,MySQL “as” begin mysql statement ...;
end;
5 MySQLストレージに複数のMySQL文が含まれている場合は、begin endキーが必要です.
create procedure pr_add
(
a int,
b int
)
begin
mysql statement 1 ...;
mysql statement 2 ...;
end;
6 MySQLストレージの各文の末尾には、セミコロン";"を付けます.
...
declare c int;
if a is null then
set a = 0;
end if;
...
end;
7 MySQLストレージで「return」キーを使用することはできません.
set c = a + b;
select c as sum;
/* return c;- MySQL 。return 。 */
end;
8 MySQLストアド・プロシージャを呼び出す場合は、プロシージャ名の後に「()」を付ける必要があります.パラメータが1つもなくても「()」が必要です.呼び出しoutおよびinoutパラメータのフォーマットは@arguments_です.name形式.
call pr_no_param();
9 MySQLストアド・プロシージャ・パラメータにデフォルト値がないため、MySQLストアド・プロシージャを呼び出す際にパラメータを省略することはできません.nullで代用できます.
call pr_add(10, null);
1,実戦の前提はMySQL 5以上を必要として、私はMYSQLのクライアントNavicat Premiumを使って、貼ったコードはすべて私が変異して間違いがないのです.読者がクライアントをインストールしていないか、パソコンでエラーを報告していない場合は、DELIMITER//とDELIMITERを使用する必要があります.2つの文、DELIMITERは分割記号の意味で、MySQLがデフォルトであるためです;”区切り記号の場合、分割記号を宣言していない場合、コンパイラはストレージ・プロシージャをSQL文として処理します.ストレージ・プロシージャのコンパイル・プロシージャはエラーを報告します.事前にDELIMITERキーワードで現在のセグメント区切り記号を明記しておくと、MySQLは「保存中のコードとして、これらのコードは実行されません.使い終わったら、区切り文字を復元します.
2,変数はDECLAREを使用して宣言され,DEFAULTはデフォルト値を付与し,SETはJavaコードを付与する.
DECLARE counter INT DEFAULT 0;
SET counter = counter+1;
3,条件判断IF THEN,ELSEIF,ELSE,END IF
DROP PROCEDURE IF EXISTS discounted_price;
CREATE PROCEDURE discounted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) BEGIN IF (normal_price > 500) THEN SET discount_price = normal_price * 0.8;
ELSEIF (normal_price > 100 and normal_price<=500) THEN
SET discount_price = normal_price * 0.9;
ELSE
SET discount_price = normal_price;
END IF;
select discount_price as price;
END;
call discounted_price(600.0,@discount);//out @
4,ループLOOP、END LOOP
drop procedure if exists simple_loop;
create procedure simple_loop(out counter int) BEGIN declare temp int default 0;
set counter=0;
my_loop:LOOP
set counter=counter+1;
set temp=temp+1;
if counter=10 THEN
leave my_loop;
end if;
end loop my_loop;
select temp as result;
end;
call simple_loop(@a);
5、WHILE DO、END WHILE
DROP PROCEDURE IF EXISTS simple_while;
CREATE PROCEDURE simple_while(OUT counter INT) BEGIN declare temp int default 0;
SET counter =0;
WHILE counter != 10 DO
SET counter =counter+1;
set temp =temp+1;
END WHILE;
select counter as temp1;
END;
call simple_while(@a);
6、REPEAT、UNTILL
drop PROCEDURE if exists simple_repeat;
create procedure simple_repeat(out counter int) BEGIN set counter=0;
REPEAT
set counter=counter+1;
until counter=10 end repeat;
select counter as temp;
end;
call simple_repeat(@q);
7,ストレージメソッドストレージ方法とストレージプロセスの違い1,ストレージメソッドのパラメータリストはINタイプのパラメータのみを許可し,INキーワード2を指定する必要もなく,ストレージメソッドは単一の値を返し,値のタイプはストレージメソッドのヘッダ定義3,ストレージメソッドはSQL文の内部で4を呼び出すことができ,ストレージメソッドは結果セット構文を返すことができない:
create function ([ [,….]]) Returns Begin If Return ( ) Else Return ( ) end if;
end;
単純なストレージ関数の例
drop function if exists purchase_and_redeem_function;
CREATE function purchase_and_redeem_function(date int) returns varchar(80) BEGIN return (SELECT tbalance FROM user_purchase_and_redeem WHERE report_date=date); // SQL
END;
select purchase_and_redeem_function(20140501);//
8.フリップフロップがINSERT、UPDATEまたはDELETEなどのDML文でデータベース表を修正する際にフリップフロップをトリガーする典型的な応用シーンは重要なビジネスロジック、性能向上、監視表の修正などのフリップフロップがDML文の実行前または後にトリガーすることができる
DROP TRIGGER sales_trigger;
CREATE TRIGGER sales_trigger
BEFORE INSERT ON sales
FOR EACH ROW
BEGIN
IF NEW.sale_value > 500 THEN
SET NEW.free_shipping = 'Y';
ELSE
SET NEW.free_shipping = 'N';
END IF;
IF NEW.sale_value > 1000 THEN
SET NEW.discount = NEW.sale_value * .15;
ELSE
SET NEW.discount = 0;
END IF;
END;