ストアド・プロシージャを試してみる
ストアド・プロシージャを使ってみる
ストアド・プロシージャとは？
MySQLなどのデータベース上で実行出来るプログラム言語。
何が出来る？
通常のクエリ(DBへの命令文)では表現出来ない動的なデータの処理
フロントアプリケーションに依存しない共通処理
何が出来ない？
DBへの負荷の重い複雑な処理
自動メモリ解放
手こずった箇所
幾つかありました。
- DECLAREと言う宣言が必須
- 変数への代入時はSETや”SELECT ~ INTO”の構文を守る
- 条件文の行末には”;”無し、他は有り
- SELECTなどで複数レコード取得時はFETCH。CURSOR構文で取得したい状態を記憶
- 区切り文字列(デリミタ)を最初に変更ほぼ必須
最初の宣言はphpなどでは必須ではないのでスクリプトの緩い言語では陥りがち。
|
1 | undeclered valiable ~~ |
というようなエラーが表示されたら要チェック。
実施内容
特定テーブルのデータを複製し、特定カラムだけ連番で置き換える。
以下ではSampleTableで最後のstatusカラムだけ引数で置き換える処理を行っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | DELIMITER // CREATE PROCEDURE copy_fields(IN status INT) BEGIN # 変数の初期宣言 DECLARE _hoge01 LONGTEXT; DECLARE _hoge02 VARCHAR(255); DECLARE _hoge03 INT; DECLARE _hoge04 TINYINT; # カーソルで初期状態のカラム取得 DECLARE myCur CURSOR FOR SELECT hoge01, hoge02, hoge03, hoge04 FROM MailTemplate; # カラムカウント用変数 SET @cnt = 0; # カラムカウント総数 SELECT COUNT(*) INTO @total FROM MailTemplate; # カーソルを開く(この時点の実行結果) OPEN myCur; WHILE (@total > @cnt) DO # カーソル(クエリ)の実行結果取得(次のレコードへ自動的に遷移) FETCH myCur INTO _hoge01, _hoge02, _hoge03, _hoge04; # 挿入 INSERT INTO MailTemplate VALUES(NULL, _hoge01, _hoge02, _hoge03, _hoge04, status); SET @cnt = @cnt + 1; END WHILE; CLOSE myCur; END // DELIMITER ; |
シャープのコメント通り。
最初に変数宣言。
次に取得クエリの宣言。
ループするための数を取得。
クエリオープン。
ループ内、クエリ実行結果取得
取得したクエリをstatus以外インサート
ループ用の変数インクリメント
クエリ閉じる。
実行時は以下を実施
|
1 | CALL copy_fields(2); |
最後に
プログラムを作成して不要となったものは手動で消します。
|
1 | SHOW PROCEDURE STATUS; |
でメモリ上にあるプログラム一覧が表示されます。
|
1 | DROP PROCEDURE copy_fields; |
で削除されます。
Author Profile
スターフィールド編集部
SHARE
