函数与存储过程

MYSQL常用函数

• 聚焦函数
  1.avg
  2.count
  3.max
  4.min
  5.sum
• 字符串函数
  1.concat(str1,str2,str3) 合并字符串函数
  2.strcmp(str1,str2) 比较字符串大小
  3.length(str) 获取字符串字节数函数
  4.char_length(str) 获取字符串字符数函数
• 日期函数
  1.curdate()，current_date() 获取当前日期
  2.curtime()，current_time() 获取当前时间
  3.now()获取当前日期时间
  4.month(date),monthname(date)从日期中选择出月份数
  5.week(date)从日期中选出周数
  6.weekday(date),dayname(date) 从时间中选择出今天是周几
  7.minute(time) 从时间中选择出分钟数

自定义函数定义

-- Not allowed to return a result set from a function
create function f() returns int
begin
select * from student;
return 100;
end;

create function 函数名([参数列表]) returns 数据类型
begin 
  sql语句;
return 值;
end;

-- 最简单的仅有一条SQL的函数
create function f() returns int return 666;
select f(); -- 调用函数

-- 定义变量&使用into
create function f() returns int
begin
  declare c int;
  select id from class where cname ="python" into c;
  return c;
end;
-- 带传参的函数
create function f(name varchar(15)) returns int
begin 
  declare @c int;
  select id from class where cname = name into @c;
  return @c;
end;
select f("python");

自定义函数操作

// 调用
select f();
// 查看函数创建语句
show create function fun_name;
// 查看所有函数
show function status [like 'pattern']
// 函数修改，只能修改如comment的选项，而不能修改函数主题
alter function 函数名 选项;
// 删除函数
drop function fun_name;

循环和条件语句

• IF语句使用示例

declare @a int
set @a = 12
if @a>100
  begin
    print @a
  end
else 
  begin
    print 'no'
  end

• WHILE语句使用示例

declare @i int
set @i = 1
while @i<30
  begin
    insert into test(userid) values(@i)
    set @i=@i+1
  end

• 临时表和try

-- 增加临时表
select * into #csj_temp from csj
-- 删除临时表，用到try
begin try -- 检测代码开始
  drop table #csj_temp
end try 
begin catch --错误开始
end catch

• 游标循环记录

declare @temp_temp int 
-- 创建游标 --local(本地游标)
DECLARE aaa CURSOR for select House_Id from House_House where Deleted=0 or deleted is null
Open aaa
-- 遍历和获取游标
fetch next from aaa into @temp_temp
--print @@fetch_status = 0;
begin 
select * from House_monthEnd where House_Id = @temp_temp
fetch next from aaa into @temp_temp -- 取值赋给变量
end 
-- 关闭游标
Close aaa
-- 删除游标
Deallocate aaa

存储过程

• 示例

create procedure test2()
begin
   declare username varchar(32) defalt '';
   -- 通过select into 进行变量赋值
   select name into username from users where id =1;
  select username;
end;

• 变量作用域
  (1)、存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。
  (2)、需要多个块之间传值，可以使用全局变量，即放在所有代码块之前
  (3)、传参变量是全局的，可以在多个块之间起作用
• 存储过程参数

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型)
begin
......
end

-- 传入参数,相当于自定义函数使用
create procedure test4(userId int)
begin 
  declare username varchar(32) default '';
  declare ordercount int default 0;
  select name into username from users where id = userId;
  select username;
end;
-- 调用
call test4(1)

-- 传出参数
create procedure test5(in userId int,out username varchar(32))
begin 
  select name into username from users where id = userId;
end;
-- 调用
set @uname = '';
call test5(1,@uname);
select @uname as username;

-- 可变参数INOUT
create procedure test6(inout userId int , inout username varchar(32))
begin 
  set userId =2 ;
  set username = '';
  select id,name into userId,username from users where id = userId;
end;
-- 调用
set @uname = '';
set @userId = 0;
call test6(@userId,@uname);
select @userId,@uname as username;

存储过程分支,循环语句

-- 条件判断
if() then...else...end if;
-- 多条件判断
if() then...elseif() then...else...end if;
-- while循环
while(表达式) do
  ...
end while;

-- repeat 循环语句
create procedure test10()
begin 
  declare i int default 0;
  repeat
  begin
    select i;
    set i = i+1;
    insert into test1(id) values (i)
  end
  until i > = 10
  end repeat
end
// 函数和存储过程最大的区别是函数必须要有返回值

触发器

-- 场景:当users表插入一条数据，把插入的userid,username,插入动作和操作时间记录下来
create trigger trigger_name after insert on table_name
  for each row 
  begin
    insert into log_table (userid,username,action,optime) values (NEW.id,NEW.name,'insert',now());
  end;

流程控制语句

-- 基本语法
case ...
when ... then ...
when ... then ...
else ...
end case ;