mysql 存储过程简单例子

52037317

　　#存储过程测试
　　#无内容返回
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`()
　　BEGIN
　　SELECT 'Hello Word!' ;
　　END $$
　　DELIMITER ;
　　#存储过程测试 if语句
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`()
　　DETERMINISTIC
　　BEGIN
　　if (select phone_mun from 0110033_content where phone_mun='13471159875' limit 1)=13471159875
　　then
　　delete from 0110033_content where phone_mun='13471159875';
　　end if;
　　END $$
　　DELIMITER ;
　　#存储过程测试 if else语句
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`()
　　DETERMINISTIC
　　BEGIN
　　if (select dates from count_db where dates='20140213')=2014-02-13
　　then
　　select serviceid,all_count from count_db where  dates='2014-02-13';
　　else
　　insert into  count_db (serviceid,all_count,dates,company) SELECT serviceID,count(*),CURRENT_DATE,left(sourcePeer,8) FROM sms_cmpp.cmpp3_transmitter where   (serviceID='WBHD' or serviceID='DXKJKS' or serviceID='MGJ' or serviceID='-DXZD')  group by serviceID;
　　select serviceid,all_count from count_db ;
　　end if;
　　END $$
　　DELIMITER ;
　　#存储过程测试 if else语句 传参数
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`(in name varchar(10),in dbname  varchar(10)) #设置传递参数的长度和类型
　　DETERMINISTIC
　　BEGIN
　　SET @dbname=12;
　　select name;
　　set @tems=select * from @dbname
　　exec(@tems)
　　END $$
　　DELIMITER ;
　　#存储过程测试 if else语句 传参数作为select条件
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`(in name varchar(10)) #
　　DETERMINISTIC
　　BEGIN
　　if (select dates from count_db where dates=name limit 1)=name
　　then
　　select serviceid,all_count,dates from count_db where  dates=name limit 10;
　　else
　　insert into  count_db (serviceid,all_count,dates,company) SELECT serviceID,count(*),CURRENT_DATE,left(sourcePeer,8) FROM cmpp3_transmitter where   (serviceID='WBHD' or serviceID='DXKJKS' or serviceID='MGJ' or serviceID='KSCS')  group by serviceID;
　　#insert into  count_db (serviceid,all_count,dates,company) values(1,1,name,1);
　　select serviceid,all_count,dates from count_db ;
　　end if;
　　END $$
　　DELIMITER ;
　　#存储过程测试 if else语句 传参数作为select条件
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getCount` $$
　　CREATE PROCEDURE `getCount`(in name varchar(10),in dbname varchar(10)) #
　　DETERMINISTIC
　　BEGIN
　　if (select dates from count_db where dates=name limit 1)=name
　　then
　　select serviceid,all_count,dates from count_db where  dates=name limit 10;
　　else
　　insert into  count_db (serviceid,all_count,dates,company) SELECT serviceID,count(*),CURRENT_DATE,left(sourcePeer,8) FROM dbname.cmpp3_transmitter where   (serviceID='WBHD' or serviceID='DXKJKS' or serviceID='MGJ' or serviceID='KSCS')  group by serviceID;
　　select serviceid,all_count,dates from count_db ;
　　end if;
　　END $$
　　DELIMITER ;
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getzj` $$
　　CREATE PROCEDURE `getzj`()
　　DETERMINISTIC
　　BEGIN
　　declare i int;
　　set i=0;
　　while i　　select cnt;

　　if (select>　　then

　　insert into cjyh_zj select * from cjyh_t1  where>　　set i=i+1;
　　end if;
　　end while;
　　END $$
　　DELIMITER ;
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getzj` $$
　　CREATE PROCEDURE getzj()
　　DETERMINISTIC
　　BEGIN
　　declare i int;
　　DECLARE cnt INT DEFAULT 0;
　　set cnt=0;
　　while i 　　select cnt;

　　if (select>　　then

　　insert into cjyh_zj select * from cjyh_t1  where>　　set i=i+1;
　　end if;
　　end while;
　　END $$
　　DELIMITER ;
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getzj` $$
　　CREATE PROCEDURE getzj()
　　BEGIN
　　SET @y='Goodbye Cruel World';
　　select @y;
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `getzj` $$
　　CREATE PROCEDURE getzj()
　　DETERMINISTIC
　　BEGIN
　　delete from cjyh_zj;
　　delete from cjyh_t1nogroup;
　　insert into cjyh_t1nogroup select * from cjyh_t1 group by phone_mun;
　　insert into cjyh_zj select * from cjyh_t1nogroup  where lb='fcwx' order by rand()  limit 20;
　　insert into cjyh_zj select * from cjyh_t1nogroup   where lb='A' order by rand() limit 5;
　　insert into cjyh_zj select * from cjyh_t1nogroup   where lb='P' order by rand() limit 5;
　　insert into cjyh_zj select * from cjyh_t1nogroup  where lb='ttfc' order by rand()  limit 4;
　　END $$
　　DELIMITER ;