SQL函数和存储过程-CSDN博客

本文链接：https://blog.csdn.net/qq_40940940/article/details/107952774

本文深入探讨了SQL函数和存储过程的创建、调用及删除方法，详细介绍了参数设置与流程控制语句，涵盖了变量声明、赋值及常用函数等关键知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL函数和存储过程

函数：

创建
```
CREATE
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
[characteristic ...] routine_body
```
1. func_parameter存储过程的参数列表type-----函数返回的数据类型
  - 格式：
    - [ IN | OUT | INOUT ] param_name type
  - 介绍：
    - in----输入，out----输出，inout----可以输入也可以输出
    - param_name 参数名称
    - type 参数的类型（MySQL的任意类型）

调用

	select 函数名字（[函数的参数[,……]])

删除
```
DROP FUNCTION [IF EXISTS] sp_name
```

存储过程：

创建：
```
CREATE
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
```
1. proc_parameter：存储过程的参数列表characteristic存储过程的特性
  - 格式：
    - [ IN | OUT | INOUT ] param_name type
  - 介绍：
    - in----输入，out----输出，inout----可以输入也可以输出
    - param_name：参数名称
    - type：参数的类型（MySQL的任意类型）

调用

call 存储过程名字（[存储过程的参数[,……]])

删除
```
DROP PROCEDURE [IF EXISTS] sp_name
```

小计

变量声明
```
DECLARE 变量名 数据类型;
```
变量赋值
```
SET 变量名= ;
```

流程控制语句

if语句

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

case语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

while语句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

begin_label:表示标注名称
search_condition条件表达式

查看存储过程和函数

show status语句查看存储过程和函数的状态

show {procedure|function}status[like’存储过程或存储函数的名称’]

show create语句查看存储过程和函数的定义

show create{procedure|function}存储过程或存储函数的名称

从information_schema.Routines表查看存储过程和函数的信息

select * from information_schema.Routines
where routine_name=’存储过程或存储函数的名称’;

Mysql常用函数

字符串截取

SUBSTRING_INDEX(str,delim,count)

str:要做处理的字符串对象。
delim:处理时用来做标识符的东西。
count:怎么取值。
count是正值：顺着数，标识符第count次出现时在它前面的所有的数据。
count是负值：倒着数，标识符第count次出现时在它后面的所有的数据。
```
select SUBSTRING_INDEX('30,30,30,123', ',', -1)
-- 返回123
select SUBSTRING_INDEX('30,30,30,123', ',', 1)
-- 返回30
```
MySQL LPAD 函数

LPAD - 填充字符串左边。

LPAD(str,len,padstr)
```
select lpad('7',4,'0')
-- 返回0007
```

复制表结构

-- 复制表结构+索引
create table table_name1 like table_name2;
-- 只是复制表结构
create table table_name1 as table_name2;

case 和group by同用

-- 只能group by一项，若项目数多的话报错
group by 
CASE 
	WHEN _result_type = '1' THEN 
		 city_code 
	ELSE 
		 pk_project 
END;

若在存储过程中想要进行if判断，可使用下面的语句替代

(IFNULL(_type,'') = '' or _type = type) 
-- 相当于mapper.xml中的
-- <if test="projectId != null  and projectId != ''  ">project_id = #{projectId}</if>

根据中文首字母排序

# 如果数据库表字段的字符编码是latin1_swedish_ci
select * from tablename order by birary(name) asc ;
   #tablename：数据库表名
   #name：排序字段名
   #birary不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写

# 如果数据库表字段的字符编码是utf8_general_ci（常用）
 select * from tablename WHERE 1=1 order by CONVERT( name USING gbk ) COLLATE gbk_chinese_ci ASC

修改表或表结构常用sql语句

alter table old_name rename new_name; --修改表名

alter table test add  column add_name varchar(10); --添加表列

alter table test drop  column del_name; --删除表列

alter table test modify address char(10) --修改表列类型
# alter table test change address address  char(40)

alter table test change  column address address1 varchar(30)--修改表列名