PageRenderTime 15ms CodeModel.GetById 11ms app.highlight 1ms RepoModel.GetById 1ms app.codeStats 0ms

/Script/storeproc_v4.sql

http://awoe.googlecode.com/
SQL | 317 lines | 245 code | 35 blank | 37 comment | 34 complexity | 72bc869756b7f7f268cbeb00d067c12d MD5 | raw file
  1/*# ------------------------------------------------------*/
  2/*# Host:                         192.168.1.102
  3/*# Server version:               5.1.41-3ubuntu12.10
  4/*# Server OS:                    debian-linux-gnu
  5/*# HeidiSQL version:             6.0.0.3603
  6/*# Date/time:                    2011-04-12 17:36:29
  7/*# ------------------------------------------------------*/
  8
  9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */|
 10/*!40101 SET NAMES utf8 */|
 11/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */|
 12/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */|
 13delimiter |
 14
 15USE psquare|
 16/*---------------------------------------------------------------*/
 17/*---------------------------------------------------------------*/
 18/*---------------------------------------------------------------*/
 19/*---------------------------------------------------------------*/
 20/*------------------------------Players--------------------------*/
 21
 22drop procedure if exists load_players|
 23create procedure load_players(IN vaid INT)
 24begin
 25select 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;
 26end|
 27
 28drop procedure if exists load_player_detail|
 29create procedure load_player_detail(IN vaid INT)
 30begin
 31select money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left, datax, datay, dataz from player where pid = vpid;
 32end|
 33
 34drop procedure if exists load_players_ex|
 35create procedure load_players_ex(IN vaid INT)
 36begin
 37select player.pid, role.hid, player.name, player.gender, player.features, role.lvl, role.elvl, player.cid, player.did from player, role where player.aid = vaid and role.pid = player.pid and role.rid = 1;
 38end|
 39
 40drop procedure if exists load_player_3attr|
 41create procedure load_player_3attr(IN vpid INT)
 42begin
 43select role.exp, role.hp, role.maxhp, role.mp, role.maxmp from role where role.pid = vpid and role.rid = 1;
 44end|
 45
 46
 47drop procedure if exists create_player|
 48create procedure create_player(IN vpid INT, IN vaid INT,
 49IN vname VARCHAR(50), IN vgender TINYINT, IN vfeatures TINYINT)
 50begin
 51	select max(pid)+1 into vpid from player;
 52	insert into player (pid, aid, name, gender, features, money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left)
 53	values (vpid, vaid, vname, vgender, vfeatures, 0, 0, 0, 0, 0, 0, 0);
 54	select pid from player where pid = vpid;
 55end|
 56
 57drop procedure if exists create_player_ex|
 58create procedure create_player_ex(IN vpid INT, IN vaid INT, IN vhid SMALLINT, IN vname VARCHAR(50), IN vgender TINYINT, IN vfeatures TINYINT, IN vcid TINYINT, IN vdid TINYINT)
 59begin
 60	select max(pid)+1 into vpid from player;
 61	insert into player (pid, aid, name, gender, features, cid, did, money_g, money_s, prop_vit, prop_str, prop_agi, prop_int, prop_left)
 62	values (vpid, vaid, vname, vgender, vfeatures, vcid, vdid, 0, 0, 0, 0, 0, 0, 0);
 63	insert into role (pid, rid, hid, lvl, exp, hp, maxhp, mp, maxmp, eslot1, eslot2, eslot3, eslot4, eslot5, eslot6, eslot7, datax, datay, dataz)
 64	values (vpid, 1, vhid, 1, 0, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
 65	select pid from player where pid = vpid;
 66end|
 67
 68drop procedure if exists update_player|
 69create procedure update_player(IN vpid INT, IN vaid INT,
 70IN vname VARCHAR(50), IN vgender TINYINT, IN vfeatures TINYINT,
 71IN vmoney_g INT, IN vmoney_s INT,
 72IN vprop_vit SMALLINT, IN vprop_str SMALLINT, IN vprop_agi SMALLINT, IN vprop_int SMALLINT, IN vprop_left SMALLINT,
 73IN vdatax INT, IN vdatay INT, IN vdataz INT)
 74begin
 75	declare cnt smallint;
 76	select count(*) from player where pid = vpid and aid = vaid into cnt;
 77	if cnt=0 then
 78		select max(pid)+1 into vpid;
 79		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)
 80		values (vpid, vaid, vname, vgender, vfeatures, vmoney_g, vmoney_s, vprop_vit, vprop_str, vprop_agi, vprop_int, vprop_left, vdatax, vdatay, vdataz);
 81	else
 82		update player
 83		set name = vname,
 84		gender = vgender, features = vfeatures,
 85		money_g = vmoney_g, money_s = vmoney_s,
 86		prop_vit = vprop_vit, prop_str = vprop_str, prop_agi=vprop_agi, prop_int=vprop_int, prop_left=vprop_left,
 87		datax = vdatax, datay=vdatay, dataz=vdataz
 88		where pid=vpid and aid = vaid;
 89	end if;
 90end|
 91
 92drop procedure if exists remove_player|
 93create procedure remove_player(IN vpid INT)
 94begin
 95	delete from player where pid=vpid;
 96	delete from role where pid=vpid;
 97	delete from item where pid=vpid;
 98	delete from form where pid=vpid;
 99	delete from progress where pid=vpid;
100	delete from cooldown where pid=vpid;
101end|
102/*---------------------------------------------------------------*/
103/*---------------------------------------------------------------*/
104/*---------------------------------------------------------------*/
105/*---------------------------------------------------------------*/
106/*------------------------------Roles----------------------------*/
107drop procedure if exists load_roles|
108create procedure load_roles(IN vpid INT)
109begin
110select pid, rid, hid, lvl, exp, hp, mp, eslot1, eslot2, eslot3, eslot4, eslot5, eslot6, eslot7, datax, datay, dataz from role where pid = vpid;
111end|
112
113drop procedure if exists create_role|
114create procedure create_role(IN vpid INT, IN vrid SMALLINT, IN vhid SMALLINT, IN vlvl SMALLINT, IN vexp INT, IN vhp INT, IN vmp INT)
115begin
116	select max(rid)+1 from role where pid=vpid into vrid;
117	insert into role (pid, rid, hid, lvl, exp, hp, mp, eslot1, eslot2, eslot3, eslot4, eslot5, eslot6, eslot7, datax, datay, dataz)
118	values (vpid, vrid, vhid, vlvl, vexp, vhp, vmp, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
119end|
120
121drop procedure if exists update_role|
122create procedure update_role(IN vpid INT, IN vrid SMALLINT, IN vhid SMALLINT, IN vlvl SMALLINT, IN vexp INT, IN vhp INT, IN vmp INT,
123IN veslot1 INT, IN veslot2 INT, IN veslot3 INT, IN veslot4 INT, IN veslot5 INT, IN veslot6 INT, IN veslot7 INT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
124begin
125	declare cnt smallint;
126	select count(*) from role where pid = vpid and rid = vrid into cnt;
127	if cnt=0 then
128		select max(rid)+1 from role where pid=vpid into vrid;
129		insert into role (pid, rid, hid, lvl, exp, hp, mp, eslot1, eslot2, eslot3, eslot4, eslot5, eslot6, eslot7, datax, datay, dataz)
130		values (vpid, vrid, vhid, vlvl, vexp, vhp, vmp, veslot1, veslot2, veslot3, veslot4, veslot5, veslot6, veslot7, vdatax, vdatay, vdataz);
131	else
132		update role
133		set hid=vhid, lvl=vlvl, exp=vexp, hp=vhp, mp=vmp,
134		eslot1 = veslot1, eslot2=veslot2, eslot3=veslot3, eslot4=veslot4, eslot5=veslot5, eslot6=veslot6, eslot7=veslot7,
135		datax = vdatax, datay=vdatay, dataz=vdataz
136		where pid=vpid and rid=vrid;
137	end if;
138end|
139
140drop procedure if exists remove_role|
141create procedure remove_role(IN vpid INT)
142begin
143	delete from role where pid=vpid;
144end|
145
146/*---------------------------------------------------------------*/
147/*---------------------------------------------------------------*/
148/*---------------------------------------------------------------*/
149/*---------------------------------------------------------------*/
150/*------------------------------Items----------------------------*/
151drop procedure if exists load_items|
152create procedure load_items(IN vpid INT)
153begin
154	select pid, iid, sid, endurance, count, pos, elvl, slotcnt, slot1, slot2, slot3, datax, datay, dataz from item where pid = vpid;
155end|
156
157drop procedure if exists create_item|
158create procedure create_item(IN vpid INT, IN viid INT, IN vsid INT, IN vendurance SMALLINT, IN vcount SMALLINT, IN vpos SMALLINT, IN elvl TINYINT,
159IN vslotcnt TINYINT, IN vslot1 INT, IN vslot2 INT, IN vslot3 INT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
160begin
161	select max(iid)+1 from item where pid=vpid into viid;
162	insert into item (pid, iid, sid, endurance, count, pos, elvl, slotcnt, slot1, slot2, slot3, datax, datay, dataz)
163	values (vpid, viid, vsid, vendurance, vcount, vpos, velvl, vslotcnt, 0, 0, 0, 0, 0, 0);
164end|
165
166drop procedure if exists update_item|
167create procedure update_item(IN vpid INT, IN viid INT, IN vsid INT, IN vendurance SMALLINT, IN vcount SMALLINT, IN vpos SMALLINT, IN elvl TINYINT,
168IN vslotcnt TINYINT, IN vslot1 INT, IN vslot2 INT, IN vslot3 INT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
169begin
170	declare cnt smallint;
171	select count(*) from item where pid = vpid and iid=viid into cnt;
172	if cnt=0 then
173		select max(iid)+1 from item where pid=vpid into viid;
174		insert into item (pid, iid, sid, endurance, count, pos, elvl, slotcnt, slot1, slot2, slot3, datax, datay, dataz)
175		values (vpid, viid, vsid, vendurance, vcount, vpos, velvl, vslotcnt, vslot1, vslot2, vslot3, vdatax, vdatay, vdataz);
176	else
177		update item
178		set sid=vsid,endurance=vendurance, count=vcount, pos=vpos, elvl=velvl,
179		slotcnt=vslotcnt, slot1=vslot1, slot2=vslot2, slot3=vslot3,
180		datax=vdatax, datay=vdatay, dataz=vdataz
181		where pid=vpid and iid=viid;
182	end if;
183end|
184
185drop procedure if exists remove_item|
186create procedure remove_item(IN vpid INT, IN viid INT)
187begin
188	delete from item where pid=vpid and iid=viid;
189end|
190
191
192/*---------------------------------------------------------------*/
193/*---------------------------------------------------------------*/
194/*---------------------------------------------------------------*/
195/*---------------------------------------------------------------*/
196/*------------------------------Forms----------------------------*/
197drop procedure if exists load_forms|
198create procedure load_forms(IN vpid INT)
199begin
200select pid, type, lvl, fslot1, fslot2, fslot3, fslot4, fslot4 from form where pid = vpid;
201end|
202
203drop procedure if exists create_form|
204create procedure create_form(IN vpid INT, IN vtype TINYINT, IN vlvl TINYINT)
205begin
206	insert into form (pid, type, lvl, fslot1, fslot2, fslot3, fslot4, fslot5)
207	values (vpid, vtype, vlvl, 0, 0, 0, 0, 0);
208end|
209
210drop procedure if exists update_form|
211create procedure update_form(IN vpid INT, IN vtype TINYINT, IN vlvl TINYINT, 
212	IN vfslot1 INT, IN vfslot2 INT, IN vfslot3 INT, IN vfslot4 INT, IN vfslot5 INT)
213begin
214	declare cnt smallint;
215	select count(*) from form where pid = vpid and type=vtype into cnt;
216	if cnt=0 then
217		insert into form (pid, type, lvl, fslot1, fslot2, fslot3, fslot4, fslot5)
218		values (vpid, vtype, vlvl, vfslot1, vfslot2, vfslot3, vfslot4, vfslot5);
219	else
220		update form
221		set lvl = vlvl, fslot1 = vfslot1, fslot2 = vfslot2, fslot3 = vfslot3, fslot4 = vfslot4, fslot5 = vfslot5
222		where pid=vpid and type=vtype;
223	end if;
224end|
225
226drop procedure if exists remove_form|
227create procedure remove_form(IN vpid INT, IN vtype SMALLINT)
228begin
229	delete from form where pid=vpid and type=vtype;
230end|
231
232
233/*---------------------------------------------------------------*/
234/*---------------------------------------------------------------*/
235/*---------------------------------------------------------------*/
236/*---------------------------------------------------------------*/
237/*------------------------------Progress-------------------------*/
238drop procedure if exists load_progresss|
239create procedure load_progresss(IN vpid INT)
240begin
241select pid, cid, did, datax, datay, dataz from progress where pid = vpid;
242end|
243
244drop procedure if exists create_progress|
245create procedure create_progress(IN vpid INT, IN vcid TINYINT, IN vdid TINYINT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
246begin
247	select max(pid)+1 into vpid;
248	insert into progress (pid, pid, cid, did, datax, datay, dataz)
249	values (vpid, vcid, vdid, vdatax, vdatay, vdataz);
250end|
251
252drop procedure if exists update_progress|
253create procedure update_progress(IN vpid INT, IN vcid SMALLINT, IN vdid SMALLINT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
254begin
255	declare cnt smallint;
256	select count(*) from progress where pid = vpid and cid=vcid and did=vdid into cnt;
257	if vpid=0 then
258		select max(pid)+1 into vpid;
259		insert into progress (pid, pid, cid, did, datax, datay, dataz)
260		values (vpid, vcid, vdid, vdatax, vdatay, vdataz);
261	else
262		update progress
263		set datax = vdatax, datay=vdatay, dataz=vdataz
264		where pid=vpid and cid=vcid and did=vdid;
265	end if;
266end|
267
268drop procedure if exists remove_progress|
269create procedure remove_progress(IN vpid INT)
270begin
271	delete from progress where pid=vpid and cid=vcid and did=vdid;
272end|
273
274
275/*---------------------------------------------------------------*/
276/*---------------------------------------------------------------*/
277/*---------------------------------------------------------------*/
278/*---------------------------------------------------------------*/
279/*------------------------------CoolDown-------------------------*/
280drop procedure if exists load_cooldowns|
281create procedure load_cooldowns(IN vpid INT)
282begin
283select pid, type, time, datax, datay, dataz from cooldown where pid = vpid;
284end|
285
286drop procedure if exists create_cooldown|
287create procedure create_cooldown(IN vpid INT, IN vtype SMALLINT, IN vtime INT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
288begin
289	select max(pid)+1 into vpid;
290	insert into cooldown (pid, type, time, datax, datay, dataz)
291	values (vpid, vtype, vtime, vdatax, vdatay, vdataz);
292end|
293
294drop procedure if exists update_cooldown|
295create procedure update_cooldown(IN vpid INT, IN vtype SMALLINT, IN vtime INT, IN vdatax INT, IN vdatay INT, IN vdataz INT)
296begin
297	declare cnt smallint;
298	select count(*) from cooldown where pid = vpid and type=vtype into cnt;
299	if vpid=0 then
300		select max(pid)+1 into vpid;
301		insert into cooldown (pid, type, time, datax, datay, dataz)
302		values (vpid, vtype, vtime, vdatax, vdatay, vdataz);
303	else
304		update cooldown
305		set time = vtime, datax = vdatax, datay=vdatay, dataz=vdataz
306		where pid=vpid and type = vtype;
307	end if;
308end|
309
310drop procedure if exists remove_cooldown|
311create procedure remove_cooldown(IN vpid INT, IN vtype SMALLINT)
312begin
313	delete from cooldown where pid=vpid and type=vtype;
314end|
315
316delimiter ;
317