mysql存储过程

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;

END


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