MySQLのストアドプロシージャ記載方法、教えてください

Question

データベースに親子関係が書かれていて、id番号を与えるとその祖先からのリストを表示するプログラムを考えています。
https://oshiete.goo.ne.jp/qa/11554544.html

例えばチンパンジーに該当するid番号を与えると
哺乳類- サル目（霊長類）- ヒト科 - チンパンジー属
と表示するプログラムです

C言語ならば、

データベースは id番号に該当する名前char* name[id]、親 int mather[id]があるとして
char *str;
int i;

i= id;
str = name[i];
while(mather[i] != 0) {
　i = mother[i];
　str = name[i] + str; // 前に親の名前を追加する。実際は関数で実現
　}

感じで書けますが、これをPHPから呼び出せるMySQLのストアドプロシージャをどう書けばよいのか教えてほしいです。

きっと初歩的な間違いをしているのだと思いますが、はじめの一歩でつまずいています。

＜試した事＞
コマンドラインで
mysql>
create function chainlist(id INT) returns 
begin 
set @i = id;
set @str = "";
while(@i != 0)
　select name,mother form DB where id=@i;
　set $str =　CONCAT( name, @str);
　@i = mother;
end while;
return @str;


mysql> chainlist(10);

で動作確認しようと思ったのですが
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'begin
set @i = id' at line 2 
で動作確認も出来ません。

yambejp · Accepted Answer

DROP PROCEDURE IF EXISTS SET_LR;
DELIMITER //
CREATE PROCEDURE SET_LR()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR 
SELECT id FROM animal WHERE level=0 ORDER BY parent_id ASC,id DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
UPDATE animal SET level=0,l=0,r=0;
UPDATE animal SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
WHERE parent_id IS NULL
ORDER BY id;
OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @id=a;
SET @sql='UPDATE animal as a1,animal as a2,animal as a3 SET a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a2.r=a2.r+2,a3.r=a3.r+2 WHERE a1.parent_id=a2.id AND a2.l<a3.r AND a1.id=?';
PREPARE stmt from @sql;
EXECUTE stmt USING @id;
SET @sql='UPDATE animal as a1,animal as a2,animal as a3 SET a3.l=a3.l+2 WHERE a1.parent_id=a2.id AND a2.l<a3.l and a3.id!=a1.id AND a1.id=?';
PREPARE stmt from @sql;
EXECUTE stmt USING @id;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

yambejp · Answer

>　r=@a=@a+1

前にも書きましたがset以外の構文で変数への値投入は:=です
上記の構文をみると

r=(@a 比較 @a+1)となり、r=0にしか解釈されません

yambejp · Answer

> 「比較の＝」でなく「代入の＝」

UPDATE構文での=は代入の＝になります。
ちょっと頭を切り替えて考えてください
私の例はあくまでも「変数をインクリメントしながら別の式に利用する」という
説明です。

> IF SQLSTATE != '02000' THEN
> と書かない理由

例外処理と処理順番のカラミだったような記憶がありますが
実際に別の書き方で試してみてなぜ一度doneで受けているか検証してみれば
いいでしょう。

yambejp · Answer

> r=@a:=@a+1

これはupdate文でrに「@a+1」を設定しているのと同時に
@aに対して@a+1を投入（インクリメント）しています
代入式は代入返すとということです

こんな感じでわかりますか？
set @a=0;
select 2=(@a:=@a+1); /* @a+1は1なので2!=1でfalse=0を返す */
select @a; /* @aは1 */
select 2=(@a:=@a+1); /* @a+1は2なので2==2でtrue=1を返す */
select @a; /* @aは2 */
select 2=(@a:=@a+1); /* @a+1は3なので2!=3でfalse=0を返す */
select @a; /* @aは3 */

yambejp · Answer

> ':=' の使い分け

変数はset構文では=ですが平文では:=で代入します

> 「キーワード大文字にする」ルール

そうですね、ストアドプロシージャの場合なるべく大文字で処理してください
ANDがぶれてしまったのは諸々拡張してつかっている備忘録を
コピペしたからです、混乱させてすみません

この木構造を使えば、ul-liをつかった階層表示や
各属性ごとの要素の集計、など諸々直感的で簡単に処理できます
デメリットは、データ編集ごとに更新が必要でデータ量にともない重くなること
また同じカテゴリ内の表示順位はidに依存しているので
id以外で表示順を変えたいならソート用のカテゴリをつくって
別途管理が必要です

yambejp · Answer

あ、類から順番にやるならorder byはascです

yambejp · Answer

検索：チンパンジーのid＝50001から、系列を列挙

select group_concat(name order by level desc separator '-') as names from animal as t1
where exists(select 1 from animal where id=50001 and l between t1.l and t1.r)

yambejp · Answer

その後、データを追加・削除・更新するごとに
call SET_LR;
を実行。

yambejp · Answer

ちょっと長くなるので注意

create table animal(id int primary key,parent_id int null,name varchar(50),level int NOT NULL default 0,l int not null default 0,r int not null default 0);

insert into animal(id,parent_id,name) values
(10001,null,'哺乳類'),
(10002,null,'爬虫類'),
(20001,10001,'サル目（霊長類）'),
(20002,10001,'ネコ目'),
(30001,20001,'ヒト科'),
(30002,20002,'イヌ科'),
(40001,30001,'チンパンジー属'),
(40002,30002,'イヌ属'),
(50001,40001,'チンパンジー'),
(50002,40001,'さる'),
(50003,40001,'ゴリラ'),
(50004,40002,'イヌ'),
(50005,40002,'オオカミ');

kmee · Answer

MySQLはあまり使ったことがなく、現在テスト環境もないので、
より詳細には、マニュアル、参考書等を使ってください。

https://dev.mysql.com/doc/refman/5.6/ja/stored-programs-defining.html
・エラーメッセージからして、set @i = id; の ; で切られているように思います。
> mysql クライアントプログラムを使用してセミコロン文字を含むストアドプログラムを定義する(以下略)
にあるようにdelimiter が必要かと

https://dev.mysql.com/doc/refman/5.6/ja/create-procedure.html
・returns の直後には、戻り値の型が来るはずですが、その入力にはありません。

> set $str =　CONCAT( name, @str);
> @i = mother;
select した列は into 変数 を使って一度変数に入れないと後から利用できないと思います

MySQLのストアドプロシージャ記載方法、教えてください

DROP PROCEDURE IF EXISTS SET_LR;

> r=@a=@a+1

> 「比較の＝」でなく「代入の＝」

> r=@a:=@a+1

> ':=' の使い分け

あ、類から順番にやるならorder byはascです

検索：チンパンジーのid＝50001から、系列を列挙

その後、データを追加・削除・更新するごとに

ちょっと長くなるので注意

MySQLはあまり使ったことがなく、現在テスト環境もないので、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>　r=@a=@a+1