65.函数
(
-- 1、mysql的变量
/*系统变量 :系统自定义的,只能读取,不能修改。show variables like 'char%'; */
(
show variables like 'version%';
select version();
)
-- 2.用户自定义变量:
/*
定义一个变量:set
变量赋值 : set 变量名 = 变量值。
注意:为了区分 系统变量和字段与用户自定义变量,需要在用户变量前,增加@标识符。
使用变量 :通过 select 语句可以获得当前的变量的值。
注意:这种方式创建的变量是 全局变量 ,能通过变量传递到查询语句。
*/
(
set @nam = 'tom';
select @nam;
-- 案例:
/*查询名字叫tom的员工工资*/
select epay from emp where ename=@nam;
select * from emp;
)
-- 3.自定义函数
/*
自定义函数的要素:
函数名 --必须要有
参数列表 -- 可有可没有
函数体 -- 要有
返回值 -- 必须要有
语法:
定义:
returns -- 必须写的
create function 函数名 (参数列表) returns 返回值类型
begin
函数体
end
调用:select 函数名(参数);
注意:函数是与当前的数据库绑定的,可以使用库名.函数名的形式调用;
*/
(
-- 案例:
/* 1、自定义函数,输出 hello world! */
drop function if exists hw;
create function hw() returns varchar(20)
begin
return 'hello world';
end
select hw();
)
-- 4.sql中的流程控制:
/*
-- 分支判断
if 条件1 then -- {
条件1满足执行的语句
elseif 条件2 then
条件2满足执行的语句
….
else
上面的条件全都不满足,执行的语句
end if; -- }
注意:elseif 和 else 都是可以省略的。
*/
(
-- 案例:
/*1、判断当前的时间,大于18点,输出'早就放学了';小于等于18点,输出'继续学习吧'。*/
create function fangxue() returns varchar(20)
begin
set @time = hour(now());
if( @time > 18 ) then
return '早就放学啦';
else
return '继续学习吧';
end if;
end
select fangxue();
/*2、如果存在某些员工的工资小于5000,
则将这些员工的工资加500*/
create function gongzi() returns varchar(20)
begin
if( (select min(epay) from emp) < 5000) then
update emp set epay=epay+500 where epay<5000;
return '工资添加成功';
else
return '没有工资<5000';
end if;
end
select gongzi();
)
-- 5.循环
/*
while 条件 do
循环体
end while;
*/
(
-- 案例:
/*1、计算1-100的和*/
create function jisuan() returns int
begin
declare i int default 1;
declare sum int default 0;
while i<=100 do
set sum = sum+i;
set i = i+1;
end while;
return sum;
end
select jisuan();
/*2、计算10的阶乘*/
create function jiechen() returns int
begin
declare i int default 1;
declare sum int default 1;
while i<=10 do
set sum = sum*i;
set i = i+1;
end while;
return sum;
end
select jiechen();
)
-- 6.循环的提前终止 : leave 和 iterate
/*
leave 相当于 break 终止循环
iterate 相当于 continue 终止当前循环,继续下次循环
注意,不是根据leave和iterate所在的位置来决定终止哪个循环,而是由循环的标签来决定的。
循环的标签,给循环起名字。
标签 : while
end while 标签;
*/
(
/*案例:如果员工的平均工资小于7000,则将每个员工
工资加100,直到平均工资大于7000为止。如果
在加薪过程中,最高工资超过9300则停止本次
加薪过程*/
create function jgz() returns varchar(20)
begin
w:while (select avg(epay) from emp) <7000 do
update emp set epay=epay+100;
if (select max(epay) from emp) >9300 then
leave w;
end if;
end while;
return '添加工资结束';
end
select jgz();
)
-- 7.函数内使用的变量:
/*
1、全局变量
@变量名 的形式,函数内和函数外通用。
2、局部变量,有2种方式实现:
<1>
函数的参数:
参数,同样需要确定类型。
语法: 参数名 类型
一个函数,可以有多个参数,使用 逗号分割。
<2>
函数声明的局部变量:
使用 declare声明局部变量。 需要指定类型,可以指定默认值 default。
语法:declare 变量名 类型 default 默认值;
*/
(
-- 案例:
/*1、自定义函数,实现输入员工的姓名来查询员工的入职时间*/
drop function if exists csj;
create function csj(iname varchar(20)) returns date
begin
return (select estartime from emp where ename = iname);
end
select * from emp;
select csj('jack');
select
/*2、使用声明局部变量的方式,计算10的阶乘。*/
create function jiechen2() returns int
begin
declare i int default 1;
declare sum int default 1;
while i<=10 do
set sum = sum*i;
set i = i+1;
end while;
return sum;
end
select jiechen2();
)
-- 8.case-end多分支语句: 可以使用自定义的方式显示结果列。
/*
语法:
case
when 条件1 then 结果1
when 条件2 then 结果2
……
else 其他结果
end
注意:case表示函数开始,end表示函数结束。如果条件1成立,则返回结果1,如果条件2成立,
则返回结果2,当全部不成立则返回其他结果,而当有一个成立之后,后面的就不执行了。
*/
(
-- 案例:
/*查询员工表姓名和性别,性别男显示‘m’,女显示‘f’*/
select ename,
case
when esex='男' then 'm'
when esex='女' then 'f'
end as '性别'
from emp;
)
)
-- 66.mysql存储过程和触发器
(
/*
1、什么是存储过程?
简单的说,就是一组带有逻辑的sql语句,功能强大,可以实现一些比较复杂的逻辑功能,
类似于java语言中的方法;
2、特性:
1>有输入输出参数,可以声明变量,有if/else, case,while等控制语句,
通过编写存储过程,可以实现复杂的逻辑功能;
2>函数的普遍特性:模块化,封装,代码复用;
3>速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;
2、存储过程与自定义函数的区别:
1>存储过程实现的过程要复杂一些,而函数的针对性较强;
2>存储过程可以有多个返回值,而自定义函数只有一个返回值;
3>存储过程一般是作为一个独立的部分来执行( execute 语句执行),
而函数可以作为查询语句的一个部分来调用(select调用),由于函数可以返回一个表对象,
因此它可以在查询语句中位于from关键字的后面。 sql语句中不可用存储过程,而可以使用函数。
*/
/*
创建存储过程:
create procedure pro_test() --存储过程名称(参数列表)
begin -- 开始
-- 可以写多个sql语句; -- sql语句+流程控制
select * from employee;
end -- 结束 结束符
-- 执行存储过程
call pro_test(); -- call 存储过程名称(参数);
-- 删除存储过程
语法:drop procedure 存储过程名称;
参数:
in:表示输入参数,可以携带数据带存储过程中
语法:in 参数名 数据类型
out: 表示输出参数,可以从存储过程中返回结果
如何获取返回结果?
1)定义一个全局变量 @变量名
2)使用该全局变量接收存储过程的返回值
3)查看返回值 select @变量名
inout: 表示输入输出参数,既可以输入功能,也可以输出功能
存储过程可以直接使用 select 返回值 返回结果。
*/
(
-- 案例1:
/*传入一个员工的id,查询员工信息*/
use
create procedure pro_idx(in e_id int)
begin
select * from emp where eid = e_id;
end
call pro_idx(5);
/*使用存储过程,输出'hello world!!'*/
create procedure pro_hw()
begin
select 'hello world';
end
call pro_hw();
/*输入员工的编号id,查询员工的部门id*/
create procedure pro_ep(inout i int)
begin
set i = (select did from emp where eid = i);
end
set @i=1;
call pro_ep(@i);
select @i;
/*输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,
如果3,返回“星期三”。其他数字,返回“错误输入”;*/
create procedure pro_week(in con int,out str varchar(20))
begin
if con=1 then
set str = '星期一';
elseif con = 2 then
set str = '星期二';
elseif con = 3 then
set str = '星期三';
else
set str = '错误输入';
end if;
end
set @str = '';
call pro_week(5,@str);
select @str;
/*输入一个整数,累加求和。*/
create procedure pro_sum(inout shu int)
begin
declare i int default 0;
declare j int default 0;
while i<=shu do
set j = j+i;
set i = i+1;
end while;
set shu = j;
end
set @shu = 100;
call pro_sum(@shu);
select @shu;
/*使用查询的结果赋值给变量(into)*/
-- 案例:
/*输入员工姓名,查询员工入职时间。*/
drop procedure pro_etime
create procedure pro_etime(in e_name varchar(20), out e_time date)
begin
select estartime into e_time from emp where ename=e_name;
end
set @e_time = '';
call pro_etime('tom',@e_time);
select @e_time;
)
(
-- 案例
/*删除存储过程*/
drop procedure pro_etime;
)
)