1.查询
-- --------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `UserChecker`(
IN tablename VARCHAR(16),
IN username VARCHAR(16),
IN userpwd VARCHAR(16)
)
BEGIN
#SELECT COUNT(*) FROM userdata.userinfo WHERE STRCMP(name,username)=0 AND STRCMP(password,userpwd)=0;
#SELECT COUNT(*) FROM userdata.userinfo WHERE name=username AND password=userpwd;
DECLARE num INT;
DECLARE result INT;
SET result=0,num=1;
WHILE (num<3 AND result=0) DO #注意:num<3表示只有两张表,如果更改表的数目则需要更改此值
SET @queryStr=CONCAT('SELECT COUNT(*) INTO @ret FROM userdata.',tablename,'_',num,' WHERE name=','''',username,'''',' AND password=','''',userpwd,'''',';');
PREPARE smts FROM @queryStr;
EXECUTE smts;
SET result=@ret;
SET num=num+1;
DEALLOCATE PREPARE smts;
END WHILE;
SELECT result;
END
2.插入
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `InsertUserInfo`(
IN tableName VARCHAR(16),
IN uName VARCHAR(16),
IN uPwd VARCHAR(16),
IN uPrivilege VARCHAR(16)
)
BEGIN
SET @cmdStr=CONCAT('INSERT INTO ',tableName,' values(','''',uName,'''',',','''',uPwd,'''',',','''',uPrivilege,
'''',');');
PREPARE cmdST FROM @cmdStr;
EXECUTE cmdST;
DEALLOCATE PREPARE cmdST;
3.删除
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `RemoveUserInfo`(
IN uName VARCHAR(16)
)
BEGIN
DECLARE result INT;
DECLARE num INT;
SET result=0,num=1;
WHILE (num<3 AND result=0) DO #注意:num<3表示只有两张表,如果更改表的数目则需要更改此值
SET @queryStr=CONCAT('SELECT COUNT(*) INTO @ret FROM userdata.userinfo_',num,' WHERE name=','''',uName,'''',';');
PREPARE smts FROM @queryStr;
EXECUTE smts;
SET result=@ret;
SET num=num+1;
DEALLOCATE PREPARE smts;
END WHILE;
IF(result=1) THEN
SET num=num-1;
SET @delStr=CONCAT('DELETE FROM userdata.userinfo_',num,' WHERE name=','''',uName,'''',';');
PREPARE delTS FROM @delStr;
EXECUTE delTS;
DEALLOCATE PREPARE delTS;
END IF;
END