存储过程 mysql 例子_mysql 存储过程简单例子

#存储过程测试

#无内容返回

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<5 do

DECLARE cnt INT DEFAULT 0;

select id into cnt from  cjyh_t1 order by rand() limit 1;

select cnt;

if (select id from cjyh_zj where id=cnt limit 1)=''

then

insert into cjyh_zj select * from cjyh_t1  where id=cnt  limit 1;

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 <5 do

select id into cnt from  cjyh_t1 limit 1;

select cnt;

if (select id from cjyh_zj where id=cnt limit 1)=''

then

insert into cjyh_zj select * from cjyh_t1  where id=cnt;

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 ;