MySQL テーブルのすべての行をループする

1. MySQL テーブルのすべての行をループする
2. ストアド プロシージャで WHILE ループを使用して、MySQL テーブルのすべての行をループする
3. ストアド プロシージャで CURSOR を使用して、MySQL テーブルのすべての行をループする
4. WHILE と CURSOR の使い方の比較

今日は、ストアド プロシージャで WHILE と CURSOR を使用して、MySQL テーブルのすべての行をループする方法について学習します。 また、各手法の長所と短所を調べて、どの状況に適しているかを区別します。

MySQL テーブルのすべての行をループする

特定のテーブルまたは複数のテーブルからデータを読み取り/挿入するために、MySQL テーブルのすべての行をループするために使用できるさまざまなアプローチについて学習します。 コード例でそれぞれを学びましょう。

そのために、employees と emp_performance の 2つのテーブルがあります。 employees テーブルには、属性として EMP_ID、FIRSTNAME、LASTNAME、GENDER、および AGE があります (列名とも呼ばれます)。

emp_performance テーブルには PERFORM_ID、FIRSTNAME、LASTNAME、および PERFORMANCE フィールドがあり、FIRSTNAME と LASTNAME は employees テーブルと同じです。

employees テーブルから FIRSTNAME と LASTNAME をコピーし、emp_performance テーブルに挿入して、毎月各従業員の PERFORMANCE を計算する必要があると想像してみてください。

employees テーブルから必要な値を SELECT し、INSERT を emp_performance テーブルに保存し、後で PERFORMANCE の計算を続行する方法が必要です。 employees と emp_perfomance を作成して続行することもできます。 コードを以下に示します。

コード例:

#create an `employees` table
CREATE TABLE employees (
  EMP_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  GENDER VARCHAR(45) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (EMP_ID));

#insert data
INSERT INTO employees (FIRSTNAME, LASTNAME, GENDER, AGE) VALUES
('Mehvish','Ashiq', 'Female', 30),
('Thomas', 'Christopher', 'Male', 22),
('John', 'Daniel', 'Male', 34),
('Saira', 'James', 'Female', 27);

#create a `emp_performance` table
CREATE TABLE emp_performance (
  PERFORM_ID INT NOT NULL AUTO_INCREMENT,
  FIRSTNAME VARCHAR(45) NOT NULL,
  LASTNAME VARCHAR(45) NOT NULL,
  PERFORMANCE  VARCHAR(45) NULL,
  PRIMARY KEY (PERFORM_ID));

ストアド プロシージャで WHILE と CURSOR を使用して、employees テーブルのすべての行をループし、INSERT を emp_performance テーブルに入れることができます。

ストアド プロシージャで WHILE ループを使用して、MySQL テーブルのすべての行をループする

これで、テーブルの準備が整いました。 したがって、次の手順を記述して実行し、employees テーブルから FIRSTNAME と LASTNAME を SELECT し、INSERT を emp_performance テーブルに挿入できます。

コード例:

DROP PROCEDURE IF EXISTS CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE CALCPERFORMANCE()
BEGIN
DECLARE length INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
SELECT COUNT(*) FROM employees INTO length;
SET counter=0;
WHILE counter<length DO
  INSERT INTO emp_performance(FIRSTNAME, LASTNAME)
  SELECT FIRSTNAME, LASTNAME FROM employees LIMIT counter,1;
  SET counter = counter + 1;
END WHILE;
End;
;;

DELIMITER ;
CALL CALCPERFORMANCE();

レコードが挿入されたら、SELECT コマンドを使用して emp_performance 出力を確認します。

SELECT * from emp_performance;

出力 (WHILE ループを使用した emp_performance テーブル):

CALCPERFORMANCE ストアド プロシージャを実行すると、PERFORM_ID、FIRSTNAME、および LASTNAME のみが入力されます。

行番号を使用したコードの説明

1. 行 1 は、CALCPERFORMANCE という名前で作成済みのプロシージャを削除します。
2. 行 2 は、デフォルトの delimiter を ;; に変更します。 このように、特定のタスクを達成するためのプロシージャを作成しようとしているため、SQL はすべての行を実行するわけではありません。
3. 行 4 は、提供されたプロシージャー名を使用してプロシージャーを作成します。
4. この手順に必要なすべてのステートメントは、5 行目と 15 行目の間に記述されます。
5. 5 行目で手順を開始します。
6. 6 行目と 7 行目は、DEFAULT 値がそれぞれ 0 と 0 である INT 型の length と counter という名前の変数を宣言します。
7. 8 行目では、employees テーブルからカウントを SELECT し、その値を length 変数に代入します。
8. 9 行目で counter に 0 を設定します。
9. 10 行目から 14 行目には、employees テーブルから FIRSTNAME と LASTNAME を SELECT し、emp_performance テーブルに INSERT する WHILE ループがあります。 各反復で 1つのレコードのみが選択され、挿入されることに注意してください。
10. 18 行目は、区切り文字を再びデフォルト値の ; にリセットします。
11. 19 行目でプロシージャを呼び出します。

同様に、CURSOR を使用して、MySQL のすべてのテーブル行をループできます。 次のセクションを見てみましょう。

ストアド プロシージャで CURSOR を使用して、MySQL テーブルのすべての行をループする

CURSOR を使用して、ストアド プロシージャで結果セットを処理します。 これにより、クエリによって返された一連のレコード (行) をループして、すべての行を個別に処理できます。

CURSOR を使用する際には、次のような CURSOR のプロパティが必要です。

1. CURSOR はセンシティブです。 サーバーが結果テーブルのコピーも作成する必要はありません。
2. CURSOR は読み取り専用なので更新できません。
3. CURSOR はスクロールできません。 結果セット内のレコード (行) をスキップしたりジャンプしたりせずに、一方向にのみトラバースできます。

コード例:

DROP PROCEDURE IF EXISTS cursor_CALCPERFORMANCE;
DELIMITER ;;

CREATE PROCEDURE cursor_CALCPERFORMANCE()
BEGIN
DECLARE cursor_FIRSTNAME VARCHAR(45) DEFAULT "";
DECLARE cursor_LASTNAME VARCHAR(45) DEFAULT "";
DECLARE done INT DEFAULT FALSE;
DECLARE cursor_e CURSOR FOR SELECT FIRSTNAME,LASTNAME FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_e;
read_loop: LOOP
  FETCH cursor_e INTO cursor_FIRSTNAME, cursor_LASTNAME;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO emp_performance (FIRSTNAME,LASTNAME)
  VALUES (cursor_FIRSTNAME, cursor_LASTNAME);
END LOOP;
CLOSE cursor_e;
END;
;;

DELIMITER ;
CALL cursor_CALCPERFORMANCE();

レコードが挿入されたら、SELECT コマンドを使用して emp_performance 出力を確認します。

SELECT * from emp_performance;

出力 (CURSOR を使用した emp_performance テーブル):

行番号を使用したコードの説明

1. 行 1 は、同じ名前の既存のプロシージャを削除します。
2. 2 行目で区切り文字を ;; に変更します。
3. 4 行目は、指定されたプロシージャー名を使用してプロシージャーを作成します。
4. 必要なすべてのステートメントは、5 行目と 21 行目の間に記述されます。
5. 5 行目で手順を開始します。
6. 6、7、および 8 行目は、VARCHAR(45)、VARCHAR(45)、および INT 型の cursor_FIRSTNAME、cursor_LASTNAME、および done という名前の変数を宣言します。 それぞれ ""、""、FALSE。
7. 9 行目では、SELECT ステートメントに関連付けられた CURSOR を宣言しています。
8. 10 行目では、finished 変数を使用して CURSOR が結果セットの最後に到達したことを示す NOT FOUND ハンドラを宣言しています。
9. 11 行目で CURSOR を開きます。
10. 行 12-19 では、FIRSTNAME と LASTNAME のリストを繰り返し処理して、INSERT を emp_performance テーブルに挿入します。
11. 行 20 で CURSOR が終了し、行 21 でストアド プロシージャが終了します。
12. 24 行目で区切り文字を再びデフォルトの ; にリセットします。
13. 25 行目でプロシージャを呼び出します。

WHILE と CURSOR の使い方の比較

1つのことを達成するために複数の方法がある場合、それぞれのアプローチの長所と短所を知っておく必要があります。

WHILE ループの長所と短所:

CURSOR の長所と短所: