mysqlfunction输出语句_MySQLfunctionprocedure CREATE DEFINER=`root`@`%` FUNCTION`cc`() RETURNS intBEGIN#Routine body
lect emp_no into @b from employees where emp_no=10060;
黔驴之技
RETURN @b;END
尖利的意思OR
CREATE DEFINER=`root`@`%` FUNCTION`cc`() RETURNS intBEGIN#Routine body
declare b int default null;
lect emp_no into b from employees where emp_no=10060;
RETURN b;END
局部变量b需使⽤declare最先声明
传参
CREATE DEFINER=`root`@`%` FUNCTION `cc`(lastName varchar(60)) RETURNS int
DETERMINISTICBEGIN#Routine body
暖胃汤
declare b int default null;
lect emp_no into b from employees where last_name=lastName collate utf8mb4_general_ci;
RETURN b;END
意大利旅游签证使⽤navicat查询时，出现如下错误，故使⽤collate明确指定collation
no sql function
CREATE DEFINER=`root`@`%` FUNCTION`cc`(x int,y int) RETURNS int
NOSQL
BEGIN#Routine body
declare b int default null;
t b=x+y;
RETURN b;END
使⽤⽤户变量，累加求和
CREATE DEFINER=`root`@`%` FUNCTION`cc`(x int) RETURNS int
READSSQL DATA
BEGIN#Routine body
t @i=1;
t @sum=0;小型车排行榜
while @i<=x do
t @sum=@sum+@i;
t @i=@i+1;
return @sum;END
使⽤局部变量，累加求和，跳过5的倍数
CREATE DEFINER=`root`@`%` FUNCTION`cc`(x int) RETURNS int
READSSQL DATA
BEGIN#Routine body
declare i int default1;
declare sum int default0;
sumwhile:while i<=x do
if i %5 =0then
t i=i+1;
iterate sumwhile;
end if;
t sum=sum+i;
t i=i+1;
end while;
return sum;END
条件判断 if
CREATE DEFINER=`root`@`%` PROCEDURE `nm`(n int,_gender char(1) chart utf8mb4 collate utf8mb4_general_ci)BEGIN#Routine body
if _gender='m'then
lect* from employees where gender=_gender limit n;
elif _gender='f'then
lect* from employees where gender=_gender limit n;
el
lect*from employees limit n;
end if;END
CREATE DEFINER=`root`@`%` PROCEDURE `loop4`(in x int)BEGIN#Routine body
if x=3 then lect * from employees where emp_no like concat('%',x) limit x;
elif x=4 then lect * from employees where emp_no like concat('%',x) limit x;
elif x=5 then lect * from employees where emp_no like concat('%',x) limit x;
el lect*from employees limit x;
end if;END
ca 有两种格式
CREATE DEFINER=`root`@`%` PROCEDURE `nm`(x int)BEGIN#Routine body declare p int;
t p=8;
ca
when x
when x=p then lect *from dept_emp limit x;
when x>p then lect *from departments limit x;
el lect'x is invalid';
end ca;END盗取qq号
CREATE DEFINER=`root`@`%` PROCEDURE `oo`(in x int)BEGIN#Routine body declare ychar(11) default null;
lect gender into y from employees where emp_no=x;
ca
when y='m' then update employees t first_name='qwert' where emp_no=x;
when y='f' then update employees t first_name='zxcc' where emp_no=x;
el
update employees t first_name='Unkown' where emp_no=x;
end ca;END
单值判断
CREATE DEFINER=`root`@`%` PROCEDURE `nm`(x int)BEGIN#Routine body declare p int;
t p=8;
ca x
when5 then lect *from employees limit x;
when6 then lect *from dept_emp limit x;
when7 then lect *from departments limit x;
el lect'x is invalid';
end ca;END
CREATE DEFINER=`root`@`%` PROCEDURE `loop4`(in x int)BEGIN#Routine body declare v int default null;
lect gender into v from employees where emp_no=x;
ca v
when1 then update employees t first_name='uiop' where emp_no=x;
when2 then update employees t first_name='vbnm' where emp_no=x;
el
update employees t first_name='Unkown' where emp_no=x;
end ca;END
loop
CREATE DEFINER=`root`@`%` PROCEDURE `nm`()BEGIN#Routine body declare i int default0;
add_loop:loop
t i=i+1;
if i>=10then
leave add_loop;
end if;
end loop add_loop;
lect i;END
CREATE DEFINER=`root`@`%` PROCEDURE `nm`()BEGIN#Routine body declare i int default null;
t i=0;
_loop:loop
inrt into p (name) values (concat('zxc',i));
t i=i+1;
if i>=5then
leave _loop;
end if;
end loop _loop;
lect*from p;END
leave
基本格式
leave LABEL
CREATE DEFINER=`root`@`%` PROCEDURE `nm`()BEGIN#Routine body t @count=0;
add_loop:loop
t @count=@count+1;
if @count=50then
leave add_loop;
end if;
end loop add_loop;END
春天是什么样子iterate
iterate 语句将执⾏顺序转到label位置，格式如下，只可以出现在loop,repeat,while内
iterate LABEL
CREATE DEFINER=`root`@`%` PROCEDURE `nm`()BEGIN#Routine body
declare b int default0;
my_loop:loop
小腿有痣t b=b+1;
if b<10then
iterate my_loop;
elif b>20then
leave my_loop;
end if;
lect b;
end loop my_loop;END
repeat 类似于 do while
CREATE DEFINER=`root`@`%` PROCEDURE `nm`()BEGIN#Routine body
declare i int default0;
t i=0;
_repeat:repeat
t i=i+1;
until i>10end repeat _repeat;
lect i;END
while
CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in n smallint)BEGIN#Routine body declare i int default0;
declare s int default0;
while i<=n do
t s=s+i,i=i+1;
end while;
lect s;END
cursor 游标
CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in n smallint)BEGIN#Routine body
