MySql自定义函数及流程控制语句的使用

测试表结构
CREATE TABLE phpyun_ad (
id int(20) NOT NULL AUTO_INCREMENT,
ad_name varchar(100) NOT NULL,
did varchar(100) NOT NULL DEFAULT ‘0’,
time_start varchar(100) NOT NULL,
time_end varchar(100) NOT NULL,
ad_type varchar(10) NOT NULL,
word_info text NOT NULL,
word_url varchar(100) NOT NULL,
pic_url varchar(100) NOT NULL,
pic_src varchar(100) NOT NULL,
pic_content varchar(200) DEFAULT NULL,
pic_width varchar(100) NOT NULL,
pic_height varchar(100) NOT NULL,
flash_url varchar(100) DEFAULT NULL,
flash_src varchar(100) DEFAULT NULL,
flash_width varchar(100) DEFAULT NULL,
flash_height varchar(100) DEFAULT NULL,
class_id int(20) DEFAULT NULL,
is_check int(2) DEFAULT ‘0’,
is_open int(1) DEFAULT ‘0’,
target int(2) DEFAULT NULL,
hits int(11) DEFAULT ‘0’,
remark varchar(255) DEFAULT NULL,
sort int(11) DEFAULT ‘0’,
lianmeng_url varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

测试数据

insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (1,’首页幻灯’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14992057095.PNG’,’http://www.phpyun.com‘,’PHPYUN人才招聘系统’,”,”,NULL,NULL,NULL,NULL,3,1,1,2,56,”,1,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (2,’首页幻灯2’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14970247553.JPG’,”,”,”,”,NULL,NULL,NULL,NULL,3,1,1,2,14,”,2,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (3,’首页移动广告大幅’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14967040117.PNG’,’http://www.phpyun.com‘,”,’1200’,’300’,NULL,NULL,NULL,NULL,72,1,1,2,3,”,0,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (4,’左侧对联广告’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14956486512.JPG’,’http://www.phpyun.com‘,”,”,”,NULL,NULL,NULL,NULL,11,1,1,2,1,”,0,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (5,’右侧对联对联’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14927345292.PNG’,’http://www.phpyun.com‘,”,”,”,NULL,NULL,NULL,NULL,11,1,1,2,2,”,0,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (6,’首页移动广告小幅’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14934176012.PNG’,’http://www.phpyun.com‘,”,”,”,NULL,NULL,NULL,NULL,73,1,1,2,3,”,0,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (7,’登录页幻灯’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14943852335.JPG’,’http://www.phpyun.com‘,”,’1600’,’410’,NULL,NULL,NULL,NULL,37,1,1,1,52,”,10,NULL);
insert into phpyun_ad(id,ad_name,did,time_start,time_end,ad_type,word_info,word_url,pic_url,pic_src,pic_content,pic_width,pic_height,flash_url,flash_src,flash_width,flash_height,class_id,is_check,is_open,target,hits,remark,sort,lianmeng_url) values (8,’积分商场首页幻灯’,’0’,’2018-01-01’,’2020-01-01’,’pic’,”,”,’../data/upload/pimg/20170418/14947898366.JPG’,”,”,”,”,NULL,NULL,NULL,NULL,34,1,1,2,1,”,0,NULL);

创建函数

CREATE FUNCTION 函数名称(参数列表)
RETURNS 返回值类型
函数体

DELIMITER //
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED)
RETURNS INTEGER
BEGIN
DELETE FROM phpyun_ad WHERE id = uid;
RETURN (SELECT COUNT(id) FROM phpyun_ad);
END//

删除函数

DROP FUNCTION deleteById

调用函数

select deleteById(9)

局部变量

DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]

DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED DEFAULT 10; #设置默认值
SET a = x, b = y; #赋值
RETURN a+b;
END//

DROP FUNCTION addTwoNumber;
select addTwoNumber(1,2);

变量赋值

SET parameter_name = value[,parameter_name = value…]
SELECT INTO parameter_name

DELIMITER //
CREATE FUNCTION locvariable()
RETURNS int
BEGIN
DECLARE x int;
SELECT COUNT(id) FROM phpyun_ad INTO x; #SELECT INTO 赋值
RETURN x;
END//

DROP FUNCTION locvariable;
select locvariable();

用户变量定义语法:(可以理解成全局变量)

DELIMITER //
CREATE FUNCTION golvariable()
RETURNS int
BEGIN
SET @allParam = 100; #只为当前用户的客户端有效
# SELECT @allParam; (不能在函数体中使用) Not allowed to return a result set from a function
RETURN @allParam;
END//

DROP FUNCTION golvariable;
select golvariable();

使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句进行流程控制

IF语句

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF

DELIMITER //
CREATE FUNCTION ifuse(age SMALLINT UNSIGNED)
RETURNS int
BEGIN
SET @count1 = 20;
IF age>20 THEN SET @count1=@count1+1; #自加
ELSEIF age=20 THEN SET @count1=@count1+2;
ELSE SET @count1=@count1+3;
END IF;
RETURN @count1;
END//

DROP FUNCTION ifuse;
select ifuse(20);

CASE语句

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE
另一种形式
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE

DELIMITER //
CREATE FUNCTION caseuse(age SMALLINT UNSIGNED)
RETURNS int
BEGIN
SET @count2 = 20;
CASE age
WHEN 20 THEN SET @count2=@count2+1;
ELSE SET @count2=@count2+2;
END CASE ;
RETURN @count2;
END//

DROP FUNCTION caseuse;
select caseuse(20);

DELIMITER //
CREATE FUNCTION caseuse2(age SMALLINT UNSIGNED)
RETURNS int
BEGIN
SET @count3 = 20;
CASE
WHEN age=20 THEN SET @count3=@count3+1;
ELSE SET @count3=@count3+2;
END CASE ;
RETURN @count3;
END//

DROP FUNCTION caseuse2;
select caseuse2(20);

LOOP语句、LEAVE语句

LEAVE label

DELIMITER //
CREATE FUNCTION loopleave()
RETURNS int
BEGIN
SET @count4=0;
add_num: LOOP
SET @count4=@count4+10;
IF @count4=100 THEN LEAVE add_num;
END IF;
END LOOP add_num;
RETURN @count4;
END//

DROP FUNCTION loopleave;
select loopleave();

ITERATE语句（对比continue,跳出本次循环）

ITERATE label

DELIMITER //
CREATE FUNCTION iterateuse()
RETURNS int
BEGIN
SET @count5=1;
add_num: LOOP
IF @count5=1 THEN SET @count5=@count5+1;iterate add_num;
ELSEIF @count5=2 THEN SET @count5=@count5+2; leave add_num;
END IF;
SET @count5=@count5+1;
#SELECT count(id) FROM phpyun_ad limit 1;
END LOOP add_num;
RETURN @count5;
END//

DROP FUNCTION iterateuse;
select iterateuse();

REPEAT语句

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

DELIMITER //
CREATE FUNCTION repeateuse()
RETURNS int
BEGIN
declare a int default 0;
SET @count=1;
REPEAT
SET a=a+2;
SET @count=@count+1;
UNTIL @count=5
END REPEAT ;
RETURN a;
END//

DROP FUNCTION repeateuse;
select repeateuse();

WHILE语句

DELIMITER //
CREATE FUNCTION whileeuse()
RETURNS int
BEGIN
declare a int default 0;
SET @count=1;
WHILE @count<5 DO
SET a=a+2;
SET @count=@count+1;
END WHILE ;
RETURN a;
END//

DROP FUNCTION whileeuse;
select whileeuse();