MySQL: ストアドプロシージャでINSERTする例 (+ 定義名→内部IDの変換)

gistfile1.sql

-- ユーザーを追加するストアドプロシージャを定義します。
-- 氏名と性別名('male', 'female')を入力し、
-- プロシージャ内部で性別名から性別idを引き出して追加します。

DROP PROCEDURE IF EXISTS insert_user;
DELIMITER $$
CREATE PROCEDURE insert_user(IN name TEXT, IN gender_name TEXT)
  COMMENT "ユーザーを追加します。"
BEGIN
  -- カーソルの値を受け取る変数とカーソルを定義します。
  -- (WHERE name = gender_name とするとプロシージャの引数のnameとgender_nameの値が
  --  等しいかを判定してしまうのでテーブル名(のエイリアス)をnameの頭につけます)
  DECLARE gender INT;
  DECLARE genders CURSOR FOR
    SELECT g.id FROM genders g WHERE g.name = gender_name;

  -- カーソルを開き、最初に見つかった行のidを取得します。
  -- (genderにg.idの値が格納されます)
  OPEN genders;
  FETCH genders INTO gender;
  CLOSE genders;
  
  -- 本来の形でINSERTします。
  INSERT INTO users(name, gender) VALUES(name, gender);
END$$
DELIMITER ;


-- テスト(定義したストアドプロシージャの実行)
call insert_user('*** new ***', 'male');

-- テスト(表示)
SELECT * FROM users;

gistfile2.sql

--
-- 上記ストアドプロシージャをテストするためあらかじめ定義しておくテーブルです。
--

-- ユーザーテーブルを定義します。

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT COMMENT "内部ID",
  name VARCHAR(255) NOT NULL COMMENT "氏名",
  gender INT UNSIGNED NOT NULL COMMENT "性別",
  PRIMARY KEY (id)
) TYPE = InnoDB COMMENT = "ユーザー";

INSERT INTO users(name, gender) values
  ('foo', 1),
  ('bar', 2),
  ('baz', 1)
;


-- 性別テーブルを定義します。

DROP TABLE IF EXISTS genders;
CREATE TABLE genders (
  id INT UNSIGNED COMMENT "内部ID",
  name VARCHAR(255) NOT NULL COMMENT "名前",
  PRIMARY KEY (id)
) TYPE = InnoDB COMMENT = "性別";

INSERT INTO genders(id, name) values
  (1, 'male'),
  (2, 'female')
;