软件测试学习 之 MySQL 函数和存储过程

函数

-- 函数
-- 去空操作
select trim('  ss  ');
-- 四舍五入
select round(-1.5);     -- 结果为-2
select round(1.5);      -- 结果为2   
-- 随机获得0-10的整数
select round(rand()*10);
-- 随机从一个表中取记录
select *,rand() from student order by rand() limit 1;
-- select 中的rand()可省略
select *from student order by rand() limit 1;

-- 流程控制语句case
select 
case 3 
when 1	then 'one'
when 2 then 'two'
else 'zero'
end as result;

-- 根据性别改变称呼
select name 姓名 ,concat(left(trim(name),1),
case sex
when '男' then '先生'
when '女' then '小姐'
end) as 称呼
from student WHERE name like '__';

-- 创建函数来实现
create function title(name varchar(2),sex varchar(1)) returns varchar (3)
BEGIN
return concat(left(trim(name),1),
case sex
when '男' then '先生'
when '女' then '小姐'
end);
end

-- 查询创建的函数或者存储过程
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='title';

-- 使用创建的函数
select name 姓名 ,title(name,sex) as 称呼
from student WHERE name like '__';

显示结果如下：

姓名	称呼
甄宓	甄小姐
貂蝉	貂小姐
左慈	左先生
于吉	于先生
姜维	姜先生

【问题记录】

命令模式下，输入；代表sql语句结束并且立即执行，创建函数时会出现语法错误

解决方法，分隔符用其他字符代替，执行完之后再改回来

delimiter $$

create procedure gg()
begin
if exists(select column_name from information_schema.columns where  
table_schema='test' and table_name='t_user' andn column_name='point')
then
select 'tt';
end if;
end$$

delimiter ;

存储过程

mysql中三种变量类型

Local variables        本地变量（局部变量）

Session variables    会话变量（用户变量）

Global variables       全局变量（系统变量）

本地变量（局部变量）

本地变量的工作范围是在代码或者一段代码的范围内。一旦代码或者一段代码执行完成，变量就失效了。

在嵌套块的情况下，

　　在外部块中声明的变量可以在内部块中直接使用；

　　在内部块中声明的变量只能在内部块中使用。

例如：

直接执行以下语句会报错

You have an error in your SQL syntax; 

DECLARE num int;

需要包含在语句块中

drop PROCEDURE if EXISTS proc_test;
create PROCEDURE proc_test()
begin
DECLARE num int;
set num=1;
select num;
end;

call proc_test();

 会话变量（用户变量）

会话变量的工作范围是当前连接到MySQL服务器的会话的范围。一个会话从连接到服务器时开始，当连接关闭时结束。会话变量在连接终止后便失效了。会话变量只在当前连接中有效，无法被其他会话进行使用或参考。

要声明或使用一个会话变量，需要在变量名称前加上@符号：

set @param=100;
select @param;

注：无需指定类型

set @param=100;
set @param='一百';
select @param;

结果如下
@param
一百

 全局变量（系统变量）

全局变量可以跨会话使用。全局变量不是自我定义的，但是是绑定到正在运行的服务器的配置中的。

全局变量使用“GLOBAL”关键字

SET GLOBAL max_connections = 300;

赋值语句不同写法

一般语句

set @num=999; 或set @num:=888; //这里要使用变量来保存数据，直接使用@num变量

查询语句

select @num:=1; 
或者 select @num:=字段名 from 表名 wher...
或者 select 字段名 into @mum from 表名 where...

 注意两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值” ，类型不固定

-- 类型自动匹配，无需指定
set @param=1;
select name into @param from student limit 1;
select @param;

-- 换成:=赋值
select@param := name  from student limit 1,1;
select @param;

结果如下
@param
诸葛亮

@param := name
甄宓

应用举例

分别创建存储过程实现添加主键和新列的过程，如果存在则删除重建

-- 判断是否存在列
SELECT * FROM information_schema.columns 
WHERE table_name = 'test' 
AND column_name = 'id'

-- 判断是否存在主键
select * PrimaryNum 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t 
where t.TABLE_NAME ='test'

 创建主键的存储过程

-- 存储过程实现创建主键，如果存在主键则删除重建
drop procedure if exists add_primary;
create procedure add_primary(t_name varchar(25),c_name varchar(25)) 
BEGIN
-- 判断是否存在主键，存在则删除
IF EXISTS(
select * 
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t 
where t.TABLE_NAME = t_name
)
THEN            
      set @sqlStr = CONCAT('ALTER TABLE ',t_name,' drop primary key;');
            PREPARE stmt FROM @sqlStr;  
            EXECUTE stmt;
END if;
-- 添加主键
set @sqlStr = concat('alter table ',t_name,' add primary key(',c_name,')');
PREPARE stmt FROM @sqlStr;  
EXECUTE stmt;
END;

-- 调用存储过程
call add_primary('test','id');

创建新列的存储过程 

-- 存储过程实现添加新列，如果存在则删除重建
drop procedure if exists add_collum;
create procedure add_collum(t_name varchar(25),c_name varchar(25),c_type varchar(50)) 
BEGIN
-- 判断是否存在列，存在则删除
IF EXISTS(
select * FROM information_schema.columns 
WHERE table_name = t_name 
AND column_name = c_name
)
THEN            
      set @sqlStr = CONCAT('ALTER TABLE ',t_name,' drop ',c_name);
            PREPARE stmt FROM @sqlStr;  
            EXECUTE stmt;
END if;
-- 添加新列
set @sqlStr = concat('alter table ',t_name,' add ',c_name,' ',c_type);
PREPARE stmt FROM @sqlStr;  
EXECUTE stmt;
END;

-- 调用存储过程
call add_collum('test','topic','varchar(30)');