PageRenderTime 29ms CodeModel.GetById 1ms app.highlight 23ms RepoModel.GetById 1ms app.codeStats 0ms

/Script/storeproc_v2.sql

http://awoe.googlecode.com/
SQL | 44 lines | 40 code | 4 blank | 0 comment | 5 complexity | 98bb0d2e13e0f809442672bd522d8cce MD5 | raw file
 1drop procedure if exists load_players;
 2create procedure load_players(IN vaid INT)
 3begin
 4	select pid, name, gender, features, money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left, datax, datay, dataz from player where aid = vaid;
 5end;
 6
 7drop procedure if exists create_players;
 8create procedure create_players(IN vpid INT, IN vaid INT,
 9IN vname VARCHAR(50), IN vgender TINYINT, IN vfeatures TINYINT)
10begin
11	select max(pid)+1 into vpid;
12	insert into player (pid, aid, name, gender, features, money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left)
13	values (vpid, vaid, vname, vgender, vfeatures, 0, 0, 0, 0, 0, 0, 0);
14end;
15
16drop procedure if exists update_player;
17create procedure update_player(IN vpid INT, IN vaid INT,
18IN vname VARCHAR(50), IN vgender TINYINT, IN vfeatures TINYINT,
19IN vmoney_g INT, IN vmoney_s INT,
20IN vprop_vit SMALLINT, IN vprop_str SMALLINT, IN vprop_agi SMALLINT, IN vprop_int SMALLINT, IN vprop_left SMALLINT,
21IN vdatax INT, IN vdatay INT, IN vdataz INT)
22begin
23	select count(*) from player;
24	select pid from player where pid = vpid and aid = vaid into vpid;
25	if vpid=0 then
26		select max(pid)+1 into vpid;
27		insert into player (pid, aid, name, gender, features, money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left, datax, datay, dataz)
28		values (vpid, vaid, vname, vgender, vfeatures, vmoney_g, vmoney_s, vprop_vit, vprop_str, vprop_agi, vprop_int, vprop_left, vdatax, vdatay, vdataz);
29	else
30		update player where pid=pid and aid = vaid
31		set name = vname,
32		gender = vgender, features = vfeatures,
33		money_g = vmoney_g, money_s = vmoney_s,
34		prop_vit = vprop_vit, prop_str = vprop_str, prop_agi=vprop_agi, prop_int=vprop_int, prop_left=vprop_left,
35		datax = vdatax, datay=vdatay, dataz=vdataz;
36	end if
37end;
38
39drop procedure if exists remove_player;
40create procedure remove_player(IN vpid INT)
41begin
42	delete from player where pid=vpid;
43end;
44