数据库存储过程的实际应用
在django项目中,使用存储过程步骤:
①新建文件: xxx.sh
②在xxx.sh文件内写存储过程(加粗的是强调而已):
delimiter //
drop procedure if exists upload_combo_complete;
CREATE PROCEDURE upload_combo_complete (uid INT,combo VARCHAR(100))
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE tmpcount INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
select count into tmpcount from combocomplete where code = combo;
IF (tmpcount > 0) THEN
IF uid = -1 THEN
update combocomplete set count = tmpcount+1 where code = combo;
ELSE
insert into combouidcomplete (uid,code,time) values(uid,combo,UNIX_TIMESTAMP(NOW()));
update combocomplete set count = tmpcount+1 where code = combo;
END IF;
ELSE
IF uid = -1 THEN
insert into combocomplete (code,count) values(combo,1);
ELSE
insert into combouidcomplete (uid,code,time) values(uid,combo,UNIX_TIMESTAMP(NOW()));
insert into combocomplete (code,count) values(combo,1);
END IF;
END IF;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
delimiter ;
③ 在方法中调用存储过程:
def upload_complete_status(uid,app,SPORTPLANDB,combolist):
for item in combolist:
sql="call upload_combo_complete(%d,'%s')"%(uid,item)
try:
MySQL.callproc(sql=sql,db=SPORTPLANDB)
return 0
except Exception,e:
logutils.info('info','m:upload_complete_status,Exception is %s'%(e))
return -20003
补充:方法中的 MYSQL.callproc 定义如下:
@classmethod
def callproc(cls, sql, params=None, db=GeneralConf.SDB):
try:
cursor = connections[db].cursor()
cursor.execute(sql, params)
finally:
cursor.close()