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 wheredates='2014-02-13';
else
insert intocount_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 dbnamevarchar(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 wheredates=name limit 10;
else
insert intocount_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 intocount_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 wheredates=name limit 10;
else
insert intocount_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_t1where> 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_t1where> 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_t1nogroupwhere 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_t1nogroupwhere lb='ttfc' order by rand()limit 4;
END $$
DELIMITER ;
页:
[1]