1、视图:临时表的概念,应用于多张表的查询关联,不涉及到事务处理。
2、存储过程:复杂的业务需求以及复杂的操作用于事务的回滚。
以下是多表:
DELIMITER $$
USE `workdatesystemv3`$$
DROP PROCEDURE IF EXISTS `loginuser_procDS`$$
CREATE DEFINER=`hdpushadmin`@`%` PROCEDURE `loginuser_procDS`(p_userid INT,start_counts INT,limit_counts INT)
BEGIN
SET @start_counts=start_counts;
SET @limit_counts=limit_counts;
SET @p_userid=p_userid;
PREPARE STMT FROM 'SELECT r.ResourceName,r.ResourceURL,r.sysPriverID,r.sysPriverName FROM resource r WHERE r.ID=ANY(SELECT rolere.ResourceID FROM roleresource rolere WHERE rolere.RoleID=(SELECT users.RoleID FROM systemuser users WHERE users.ID=? ) ) LIMIT ?,?';
EXECUTE STMT USING @p_userid,@start_counts,@limit_counts;
END$$
DELIMITER ;
CALL loginuser_procDS(1,15,20);
创建视图:
1、