文档章节

php 简单curd

sison
 sison
发布于 2015/01/18 10:37
字数 2979
阅读 3
收藏 0

 
#可能要跑的脚本
#update  Dog_Tag set name ='医疗' WHERE id =12;
#update  Dog_Tag set name ='购物' WHERE id =13;
#update  Dog_Tag set name ='美容' WHERE id =14;
#update  Dog_Tag set name ='摄影' WHERE id =15;
#update  Dog_Tag set name ='寄存' WHERE id =16;



 
#增加字段
 

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `ALTER_Field`$$
CREATE  PROCEDURE `ALTER_Field`()
BEGIN
   IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'latitude ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add latitude   float(200,2) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'longitude ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add longitude   float(200,2) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'telephone ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add telephone   INTEGER(15) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'attestation ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add attestation   varchar(200) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'recomMend ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add recomMend   INTEGER(200) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'address ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add address   varchar(200) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'starLevel ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add starLevel   INTEGER(10) NOT NULL;
END IF;


  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'serviceTag ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add serviceTag   varchar(500) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'windowPic') = FALSE THEN
ALTER TABLE `Dog_Hospital` add windowPic  varchar(200) NOT NULL;
END IF;

  IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name =
'Dog_Hospital' AND column_name = 'hospitalAds ') = FALSE THEN
ALTER TABLE `Dog_Hospital` add hospitalAds   varchar(200) NOT NULL;
END IF;

 
   
   END$$
 
DELIMITER ;
 
CALL ALTER_Field();
DROP PROCEDURE IF EXISTS `ALTER_Field`;






#增加GIF输入接口
 
DELIMITER $$



DROP PROCEDURE IF EXISTS `Ad_SP_AddMyDogGif`$$

CREATE   PROCEDURE `Ad_SP_AddMyDogGif`(IN _memberID INTEGER,
  IN _photo VARCHAR (100) CHARSET utf8,
  IN _intro VARCHAR (1000) CHARSET utf8,
 IN _ip VARCHAR(60),IN `_open` VARCHAR(150),IN `_themeid` VARCHAR(150),IN `_gif` VARCHAR(150), OUT _RTN INTEGER)
SP :
BEGIN
  SELECT `status` FROM Dog_Member WHERE id = _memberID INTO @state ;
  IF @state <> 1 THEN
  SET @query='INSERT INTO Dog_Photo (`memberID`,`photo`,`intro`,`dateline`,`themeid`,`gif`) VALUES ';
  SET @sql = CONCAT(@query , '(\'',_memberID,'\',\'',_photo,'\',\'',_intro,'\',',UNIX_TIMESTAMP(NOW()),',',_themeid,',\'',_gif,'\')');  
 
  CALL Ad_SP_DogIntegaLinherit(@sql,@memberNum,@messageNum,_memberID,_open,_ip,@rtn);
  ELSE
  SET @query='INSERT INTO Dog_Photo (`memberID`,`photo`,`intro`,`status`,`dateline`,`themeid`,`gif`) VALUES ';
  SET @sql = CONCAT(@query, '(\'',_memberID,'\',\'',_photo,'\',\'',_intro,'\',','1',',',UNIX_TIMESTAMP(NOW()),',',_themeid,',\'',_gif,'\')');
 
  CALL Ad_SP_DogIntegaLinherit(@sql,@memberNum,@messageNum,_memberID,_open,_ip,@rtn);
  END IF;
END$$

DELIMITER ;




#改关注增加GIF展示
DELIMITER $$

USE `dog`$$

DROP PROCEDURE IF EXISTS `Ad_SP_GetFriendDynamicIn`$$

CREATE   PROCEDURE `Ad_SP_GetFriendDynamicIn`(IN `_memberID` INTEGER ,IN `_page` INTEGER ,IN `_limit` INTEGER ,OUT `_RTN` INTEGER)
SP:BEGIN
  SET @nul  = 'no';
  SET @photo = '1';
  SET @sql   = "
  SELECT T.* , L.`id` AS isLaud
  FROM (
       SELECT d.`id`,d.`memberID`,d.`photo`,d.`gif`,@nul as title,@nul as content,d.`Intro`,d.`discussNum`,d.`laudNum`,from_unixtime(d.`dateline`) as  dateline,from_unixtime(d.`updateTime`) as updateTime,m.`name` as memberName,m.`photo` as memberPhoto,m.`gender` as memberGender,@photo as mark
       FROM Dog_Photo as d
       LEFT JOIN Dog_Member as m
       ON d.`memberID` = m.`id`
       WHERE d.`memberID`
       IN (SELECT `friendID` FROM Dog_Member_Friend WHERE memberID = ";
  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_limit, ',', _limit);
  SET @query = CONCAT(@sql,_memberID,' AND `status` = 3 ) OR d.`memberID` = ',_memberID,') AS T LEFT JOIN Dog_Laud AS L ON T.`mark` = L.`type` AND T.`id` = L.`msgID` AND L.`memberID` = ',_memberID , ' ORDER BY `dateline` DESC ',@limit);
  #SELECT @query;
  #LEAVE SP;
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;

  SET _RTN = 1;
END$$

DELIMITER ;



#精选版块增加GIF
DELIMITER $$

USE `dog`$$

DROP PROCEDURE IF EXISTS `Ad_SP_GetBestDogPhoto`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Ad_SP_GetBestDogPhoto`(IN `_memberID` INTEGER ,IN `_page` INTEGER ,IN `_limit` INTEGER ,IN `_status` VARCHAR(155),OUT `_RTN` INTEGER)
SP:BEGIN
  SET @mark  = "1";
  IF _status = "new" THEN

  SET @query = 'SELECT d.id,d.memberID,d.photo,d.gif,d.Intro,d.discussNum,d.laudNum,from_unixtime(d.dateline) as dateline,from_unixtime(d.updateTime) as updateTime,m.name as memberName,m.photo as memberPhoto,m.gender,@mark as mark,l.id as isLaud FROM Dog_Photo as d LEFT JOIN Dog_Member as m ON d.`memberID` = m.`id` LEFT JOIN Dog_Laud as l ON l.`msgID` = d.`id` AND l.`type` = 1 AND l.`memberID` = ';
  SET @limit = CONCAT(' order by d.dateline desc LIMIT ', (_page - 1)*_limit, ',', _limit);
  SET @query = CONCAT(@query,_memberID,' WHERE d.`status` = 1 ', @limit);

  ELSEIF _status = "laud" THEN
 
  SET @query = 'SELECT d.id,d.memberID,d.photo,d.gif,d.Intro,d.discussNum,d.laudNum,from_unixtime(d.dateline) as dateline,from_unixtime(d.updateTime) as updateTime,m.name as memberName,m.photo as memberPhoto,m.gender,@mark as mark,l.id as isLaud FROM Dog_Photo as d LEFT JOIN Dog_Member as m ON d.`memberID` = m.`id` LEFT JOIN Dog_Laud as l ON l.`msgID` = d.`id` AND l.`type` = 1 AND l.`memberID` = ';
  SET @limit = CONCAT(' order by d.`laudNum` desc LIMIT ', (_page - 1)*_limit, ',', _limit);
  SET @query = CONCAT(@query,_memberID,' WHERE d.`status` = 1 ', @limit);

  END IF;

  PREPARE stmt FROM @query;
  EXECUTE stmt;

  IF FOUND_ROWS() <> 1
  THEN                 
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
    SET _RTN = 1;
END$$

DELIMITER ;


#商家验证码分页BUG修补

DELIMITER $$

USE `dog`$$

DROP PROCEDURE IF EXISTS `News_SP_GetPopularizeCodeDetailNum`$$

CREATE   PROCEDURE `News_SP_GetPopularizeCodeDetailNum`(IN `_codeid` INTEGER ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN
 

  SET @query = CONCAT(" SELECT count(*) as num,FROM_UNIXTIME(a.createDate,'%Y-%m-%d' ) AS createDate1 FROM Dog_PopularizeRecord as a
  inner join Dog_PopularizeCode  as b on a.codeID = b.id where a.codeID = ",_codeid);
  SET @order = CONCAT(' GROUP BY  createDate1 ORDER BY  createDate1 DESC  ');
 
  SET @query = CONCAT(@query, @order );
 
  PREPARE stmt FROM @query;
  EXECUTE stmt;
 

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;





 
delete From Dog_WebSort where sort_id in(340,341,342);
INSERT INTO `Dog_WebSort` (`sort_id`, `sort_name`, `parentid`, `ifdisplay`, `sort_href`, `sort_icon_href`, `cate`, `SortNum`, `cate_name`, `encate_name`, `grade`, `has_child`) VALUES (340, 'index', 122, 1, NULL, 'admin/DogCursory/index', '', 0, '店铺跑马灯', 'index', 2, 2);
INSERT INTO `Dog_WebSort` (`sort_id`, `sort_name`, `parentid`, `ifdisplay`, `sort_href`, `sort_icon_href`, `cate`, `SortNum`, `cate_name`, `encate_name`, `grade`, `has_child`) VALUES (341, 'index', 122, 1, NULL, 'admin/DogStoreConfig/index', '', 0, '店铺设置清除推荐天数', 'index', 2, 2);
INSERT INTO `Dog_WebSort` (`sort_id`, `sort_name`, `parentid`, `ifdisplay`, `sort_href`, `sort_icon_href`, `cate`, `SortNum`, `cate_name`, `encate_name`, `grade`, `has_child`) VALUES (342, 'index', 122, 1, NULL, 'admin/DogStoreFavorable/index', '', 0, '店铺推荐优惠', 'index', 2, 2);


/* 增加SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_addDogStoreFavorable`$$
CREATE   PROCEDURE `DOG_addDogStoreFavorable`(IN `_storeID` VARCHAR(100),IN `_storePic` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  INSERT INTO Dog_Store_Favorable (`storeID`,`storePic`,`updateDate`,`createDate`)
  VALUES (_storeID,_storePic,_updateDate,UNIX_TIMESTAMP(NOW()));

  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
  SET _RTN = 1;

END$$

DELIMITER ;





/* 编辑SQL */

DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_editDogStoreFavorable`$$

CREATE   PROCEDURE `DOG_editDogStoreFavorable`( IN `_id` VARCHAR(100),IN `_storeID` VARCHAR(100),IN `_storePic` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   

UPDATE  Dog_Store_Favorable  SET  `storeID` = _storeID,`storePic` = _storePic,`updateDate` = _updateDate  WHERE id = _id;   
 IF ROW_COUNT() <> 1  
  THEN   
    SET _RTN = 1300;    
     LEAVE SP;  
      END IF;    
      SET _RTN = 1;
      END$$
      
DELIMITER ;
 




/* 删除SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_delDogStoreFavorable`$$

CREATE   PROCEDURE `DOG_delDogStoreFavorable`(IN `_id` INTEGER  ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   
   DELETE FROM Dog_Store_Favorable   WHERE id = _id;   
   IF ROW_COUNT() <> 1  
   THEN     
   SET _RTN = 1300;     
   LEAVE SP;   
   END IF;   
    SET _RTN = 1;
    END$$

DELIMITER ;
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreFavorableNum`$$

CREATE  PROCEDURE `DOG_GetDogStoreFavorableNum`(OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Store_Favorable;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreFavorableList`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreFavorableList`(IN `_page` INTEGER

                                                                       ,IN `_perpage` INTEGER

                                                                      ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Store_Favorable ORDER BY id DESC';

  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_perpage, ',', _perpage);

  SET @query = CONCAT(@query, @limit);

  PREPARE stmt FROM @query;

  EXECUTE stmt;



  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 /* 获取单个查询 */
 
 DROP PROCEDURE IF EXISTS `DOG_GetDogStoreFavorableInfo`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreFavorableInfo`(IN `_id` INTEGER

                                               ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT  *

  FROM Dog_Store_Favorable

  WHERE id = _id;



  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;

 
 



 



 
/* 增加SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_addDogStoreConfig`$$
CREATE   PROCEDURE `DOG_addDogStoreConfig`(IN `_num` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  INSERT INTO Dog_Store_Config (`num`,`updateDate`,`createDate`)
  VALUES (_num,_updateDate,UNIX_TIMESTAMP(NOW()));

  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
  SET _RTN = 1;

END$$

DELIMITER ;





/* 编辑SQL */

DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_editDogStoreConfig`$$

CREATE   PROCEDURE `DOG_editDogStoreConfig`( IN `_id` VARCHAR(100),IN `_num` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   

UPDATE  Dog_Store_Config  SET  `num` = _num,`updateDate` = _updateDate  WHERE id = _id;   
 IF ROW_COUNT() <> 1  
  THEN   
    SET _RTN = 1300;    
     LEAVE SP;  
      END IF;    
      SET _RTN = 1;
      END$$
      
DELIMITER ;
 




/* 删除SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_delDogStoreConfig`$$

CREATE   PROCEDURE `DOG_delDogStoreConfig`(IN `_id` INTEGER  ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   
   DELETE FROM Dog_Store_Config   WHERE id = _id;   
   IF ROW_COUNT() <> 1  
   THEN     
   SET _RTN = 1300;     
   LEAVE SP;   
   END IF;   
    SET _RTN = 1;
    END$$

DELIMITER ;
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreConfigNum`$$

CREATE  PROCEDURE `DOG_GetDogStoreConfigNum`(OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Store_Config;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreConfigList`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreConfigList`(IN `_page` INTEGER

                                                                       ,IN `_perpage` INTEGER

                                                                      ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Store_Config ORDER BY id DESC';

  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_perpage, ',', _perpage);

  SET @query = CONCAT(@query, @limit);

  PREPARE stmt FROM @query;

  EXECUTE stmt;



  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 /* 获取单个查询 */
 
 DROP PROCEDURE IF EXISTS `DOG_GetDogStoreConfigInfo`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreConfigInfo`(IN `_id` INTEGER

                                               ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT  *

  FROM Dog_Store_Config

  WHERE id = _id;



  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;

 
 



 



 
 
 

/* 增加SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_addDogStoreGather`$$
CREATE   PROCEDURE `DOG_addDogStoreGather`(IN `_storeID` VARCHAR(100),IN `_goodsID` VARCHAR(100),IN `_gatherPic` VARCHAR(100),IN `_gatherObject` VARCHAR(100),IN `_gatherObjectValue` VARCHAR(100),IN `_gatherLink` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  INSERT INTO Dog_Store_Gather (`storeID`,`goodsID`,`gatherPic`,`gatherObject`,`gatherObjectValue`,`gatherLink`,`updateDate`,`createDate`)
  VALUES (_storeID,_goodsID,_gatherPic,_gatherObject,_gatherObjectValue,_gatherLink,_updateDate,UNIX_TIMESTAMP(NOW()));

  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
  SET _RTN = 1;

END$$

DELIMITER ;





/* 编辑SQL */

DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_editDogStoreGather`$$

CREATE   PROCEDURE `DOG_editDogStoreGather`( IN `_id` VARCHAR(100),IN `_storeID` VARCHAR(100),IN `_goodsID` VARCHAR(100),IN `_gatherPic` VARCHAR(100),IN `_gatherObject` VARCHAR(100),IN `_gatherObjectValue` VARCHAR(100),IN `_gatherLink` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   

UPDATE  Dog_Store_Gather  SET  `storeID` = _storeID,`goodsID` = _goodsID,`gatherPic` = _gatherPic,`gatherObject` = _gatherObject,`gatherObjectValue` = _gatherObjectValue,`gatherLink` = _gatherLink,`updateDate` = _updateDate  WHERE id = _id;   
 IF ROW_COUNT() <> 1  
  THEN   
    SET _RTN = 1300;    
     LEAVE SP;  
      END IF;    
      SET _RTN = 1;
      END$$
      
DELIMITER ;
 




/* 删除SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_delDogStoreGather`$$

CREATE   PROCEDURE `DOG_delDogStoreGather`(IN `_id` INTEGER  ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   
   DELETE FROM Dog_Store_Gather   WHERE id = _id;   
   IF ROW_COUNT() <> 1  
   THEN     
   SET _RTN = 1300;     
   LEAVE SP;   
   END IF;   
    SET _RTN = 1;
    END$$

DELIMITER ;
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreGatherNum`$$

CREATE  PROCEDURE `DOG_GetDogStoreGatherNum`(OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Store_Gather;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreGatherList`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreGatherList`(IN `_page` INTEGER

                                                                       ,IN `_perpage` INTEGER

                                                                      ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Store_Gather ORDER BY id DESC';

  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_perpage, ',', _perpage);

  SET @query = CONCAT(@query, @limit);

  PREPARE stmt FROM @query;

  EXECUTE stmt;



  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreGatherNumById`$$

CREATE  PROCEDURE `DOG_GetDogStoreGatherNumById`(IN `_id` INTEGER

                                                                       ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Store_Gather WHERE storeID = _id ;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogStoreGatherListById`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreGatherListById`(IN `_id` INTEGER ,IN `_page` INTEGER ,IN `_perpage` INTEGER ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Store_Gather ';
  SET @limit = CONCAT('  ORDER BY id DESC LIMIT ', (_page - 1)*_perpage, ',', _perpage);
  SET @query = CONCAT(@query," where storeID = ",_id, @limit);
  PREPARE stmt FROM @query;
  EXECUTE stmt;

  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 
 /* 获取单个查询 */
 
 DROP PROCEDURE IF EXISTS `DOG_GetDogStoreGatherInfo`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogStoreGatherInfo`(IN `_id` INTEGER

                                               ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT  *

  FROM Dog_Store_Gather

  WHERE id = _id;



  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;

 
 



 



 
 
 


/* 增加SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_addDogGatherConfig`$$
CREATE   PROCEDURE `DOG_addDogGatherConfig`(IN `_storeID` VARCHAR(100),IN `_open` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  INSERT INTO Dog_Gather_Config (`storeID`,`open`,`updateDate`,`createDate`)
  VALUES (_storeID,_open,_updateDate,UNIX_TIMESTAMP(NOW()));

  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
  SET _RTN = 1;

END$$

DELIMITER ;





/* 编辑SQL */

DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_editDogGatherConfig`$$

CREATE   PROCEDURE `DOG_editDogGatherConfig`( IN `_id` VARCHAR(100),IN `_storeID` VARCHAR(100),IN `_open` VARCHAR(100),IN `_updateDate` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   

UPDATE  Dog_Gather_Config  SET  `storeID` = _storeID,`open` = _open,`updateDate` = _updateDate  WHERE id = _id;   
 IF ROW_COUNT() <> 1  
  THEN   
    SET _RTN = 1300;    
     LEAVE SP;  
      END IF;    
      SET _RTN = 1;
      END$$
      
DELIMITER ;
 




/* 删除SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_delDogGatherConfig`$$

CREATE   PROCEDURE `DOG_delDogGatherConfig`(IN `_id` INTEGER  ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   
   DELETE FROM Dog_Gather_Config   WHERE id = _id;   
   IF ROW_COUNT() <> 1  
   THEN     
   SET _RTN = 1300;     
   LEAVE SP;   
   END IF;   
    SET _RTN = 1;
    END$$

DELIMITER ;
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogGatherConfigNum`$$

CREATE  PROCEDURE `DOG_GetDogGatherConfigNum`(OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Gather_Config;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogGatherConfigList`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogGatherConfigList`(IN `_page` INTEGER

                                                                       ,IN `_perpage` INTEGER

                                                                      ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Gather_Config ORDER BY id DESC';

  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_perpage, ',', _perpage);

  SET @query = CONCAT(@query, @limit);

  PREPARE stmt FROM @query;

  EXECUTE stmt;



  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 /* 获取单个查询 */
 
 DROP PROCEDURE IF EXISTS `DOG_GetDogGatherConfigInfo`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogGatherConfigInfo`(IN `_id` INTEGER

                                               ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT  *

  FROM Dog_Gather_Config

  WHERE id = _id;



  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;

 
 



 



 
 
 


/* 增加SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_addDogCursory`$$
CREATE   PROCEDURE `DOG_addDogCursory`(IN `_title` VARCHAR(100),IN `_startTime` VARCHAR(100),IN `_endTime` VARCHAR(100),IN `_cursoryNum` VARCHAR(100),IN `_updateDate` VARCHAR(100),IN `_url` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  INSERT INTO Dog_Cursory (`title`,`startTime`,`endTime`,`cursoryNum`,`updateDate`,`createDate`,`url`)
  VALUES (_title,_startTime,_endTime,_cursoryNum,_updateDate,UNIX_TIMESTAMP(NOW()),_url);

  IF ROW_COUNT() <> 1
  THEN
    SET _RTN = 1300;
    LEAVE SP;
  END IF;
  SET _RTN = 1;

END$$

DELIMITER ;





/* 编辑SQL */

DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_editDogCursory`$$

CREATE   PROCEDURE `DOG_editDogCursory`( IN `_id` VARCHAR(100),IN `_title` VARCHAR(100),IN `_startTime` VARCHAR(100),IN `_endTime` VARCHAR(100),IN `_cursoryNum` VARCHAR(100),IN `_updateDate` VARCHAR(100),IN `_url` VARCHAR(100),OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   

UPDATE  Dog_Cursory  SET  `title` = _title,`startTime` = _startTime,`endTime` = _endTime,`cursoryNum` = _cursoryNum,`updateDate` = _updateDate ,`url` = _url  WHERE id = _id;   
 IF ROW_COUNT() <> 1  
  THEN   
    SET _RTN = 1300;    
     LEAVE SP;  
      END IF;    
      SET _RTN = 1;
      END$$
      
DELIMITER ;
 




/* 删除SQL */
DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_delDogCursory`$$

CREATE   PROCEDURE `DOG_delDogCursory`(IN `_id` INTEGER  ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN   
   DELETE FROM Dog_Cursory   WHERE id = _id;   
   IF ROW_COUNT() <> 1  
   THEN     
   SET _RTN = 1300;     
   LEAVE SP;   
   END IF;   
    SET _RTN = 1;
    END$$

DELIMITER ;
 
 
 
 /* 获取分页数 */
 DELIMITER $$
DROP PROCEDURE IF EXISTS `DOG_GetDogCursoryNum`$$

CREATE  PROCEDURE `DOG_GetDogCursoryNum`(OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT COUNT(*) AS Num FROM Dog_Cursory;

  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;

  SET _RTN = 1;

END$$

DELIMITER ;



/* 获取分页列表 */
DROP PROCEDURE IF EXISTS `DOG_GetDogCursoryList`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogCursoryList`(IN `_page` INTEGER

                                                                       ,IN `_perpage` INTEGER

                                                                      ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SET @query = 'SELECT * FROM Dog_Cursory ORDER BY id DESC';

  SET @limit = CONCAT(' LIMIT ', (_page - 1)*_perpage, ',', _perpage);

  SET @query = CONCAT(@query, @limit);

  PREPARE stmt FROM @query;

  EXECUTE stmt;



  IF FOUND_ROWS() <= 0

  THEN                 

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;
 
 
 
 /* 获取单个查询 */
 
 DROP PROCEDURE IF EXISTS `DOG_GetDogCursoryInfo`;
DELIMITER ;;
CREATE  PROCEDURE `DOG_GetDogCursoryInfo`(IN `_id` INTEGER

                                               ,OUT `_RTN` INTEGER)
    SQL SECURITY INVOKER
SP:BEGIN

  SELECT  *

  FROM Dog_Cursory

  WHERE id = _id;



  IF FOUND_ROWS() <> 1

  THEN

    SET _RTN = 1300;

    LEAVE SP;

  END IF;



  SET _RTN = 1;

END
;;
DELIMITER ;

 
 



 




© 著作权归作者所有

共有 人打赏支持
sison
粉丝 0
博文 1
码字总数 2979
作品 0
深圳
程序员
私信 提问
求推荐个 thinkphp 写的 界面UI 清爽的 轻量级cms (做个后台)

好久没摸php ,突然被通知 要做个简单的 用户 以及文档管理后台 1.需要给用户(手机端用户不需要登后台)设置不同等级权限以及curd。 2.几个子栏目,都是 资料 列表 详情 相关的 curd 3.后台...

ahedy
2015/03/31
5.3K
4
swoole实现数据库连接池

原生 PHP CURD 让我们来回顾一下PHP中数据库的使用 上述代码, 本身是没有任何问题。这里我们也不是来挑出问题的,只是以一位多年PHPer的经验与大家交流下。 上面程序执行过程是什么样子的呢?...

太阳黑子
2016/10/28
23
0
Ruby on Rails的定位!

这几天学习了简单的 页面布局、CURD等例子。 他得万物对象是不错,说是最佳WEB开发体验,但是感觉不上不下的,大点的应用都用JAVA搞,性能有要求的应用 用GO写,流行点得遍地PHP+各种扩展一样...

草榴1024
2015/05/24
469
5
客户关系管理系统

[背景] 公司内部非常简单的客户管理查看系统。使用PHP+MYSQL开发 [需求] 功能: 用户登录模块 1.用户CURD 2.权限管理CURD 客户管理模块 1.客户CURD 1.字段有 姓名 联系方式 年龄 等等.. 2.根...

LinFree
2016/03/11
10
7
微信问答机器人 --wechat_robot_plus

测试环境 apache2.22 + mysql5.5 + php5.4 采用codeigniter框架,初版后台使用了@chekun 的巨作dilicms(一款基于ci的开源后台cms框架) 改版后使用自己写的一个简单的ui实现一般的CURD操作 目前...

墨仝mt
2013/04/17
18.8K
8

没有更多内容

加载失败,请刷新页面

加载更多

容器服务

简介 容器服务提供高性能可伸缩的容器应用管理服务,支持用 Docker 和 Kubernetes 进行容器化应用的生命周期管理,提供多种应用发布方式和持续交付能力并支持微服务架构。 产品架构 容器服务...

狼王黄师傅
昨天
3
0
高性能应用缓存设计方案

为什么 不管是刻意或者偶尔看其他大神或者大师在讨论高性能架构时,自己都是认真的去看缓存是怎么用呢?认认真真的看完发现缓存这一块他们说的都是一个WebApp或者服务的缓存结构或者缓存实现...

呼呼南风
昨天
12
0
寻找一种易于理解的一致性算法(扩展版)

摘要 Raft 是一种为了管理复制日志的一致性算法。它提供了和 Paxos 算法相同的功能和性能,但是它的算法结构和 Paxos 不同,使得 Raft 算法更加容易理解并且更容易构建实际的系统。为了提升可...

Tiny熊
昨天
2
0
聊聊GarbageCollectionNotificationInfo

序 本文主要研究一下GarbageCollectionNotificationInfo CompositeData java.management/javax/management/openmbean/CompositeData.java public interface CompositeData { public Co......

go4it
昨天
3
0
阿里云ECS的1M带宽理解

本文就给大家科普下阿里云ECS的固定1M带宽的含义。 “下行带宽”和“上行带宽” 为了更好的理解,需要先给大家解释个词“下行带宽”和“上行带宽”: 下行带宽:粗略的解释就是下载数据的最大...

echojson
昨天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部