DELIMITER定义 sql 语句执行符号,系统默认是分号,系统看见分号就执行,使用 DELIMITER 可以修改
创建存储过程
DELIMITER $$CREATE PROCEDURE proceduce_name([in|out|inout arg0 dataType], [in|out|inout arg1 dataType]...)BEGIN# SQLEND$$
DELIMITER ;
in 表示输入参数;out 表示输出参数;inout 表示即可输入也可输出
创建变量
格式:DECLARE 变量名 变量类型 DEFAULT 默认值;
示例1(声明一个变量):DECLARE score_totle INTEGER DEFAULT 0;
示例2(两个变量):DECLARE score_totle,score_avg INTEGER DEFAULT 0;
为变量赋值
直接修改:SET score_totle = 10;
把查询结果放进变量:SELECT AVG(score) INTO score_avg FROM students;
流程控制
IF 表达式 THEN
--表达式为真执行
SQLELSE
--表达式为假执行
SQLEND IF;
CASE变量或表达式WHEN 值1 THENSQLWHEN 值2 THENSQLELSESQLEND CASE;
IF (表达式, t, f)
上面第一种类似 java 中的 if else 流程控制,第二种类似 switch case,第三种类似三元运算
操作存储过程
查看所有库中的存储过程:SHOW PROCEDURE STATUS;
查看指定库中的存储过程:SHOW PROCEDURE STATUS WHERE db = ‘data_name‘;
查看存储过程 sql:SHOW CREATE PROCEDURE procudure_name;
删除:DROP PROCEDURE procedure_nam;
调用:CALL proceduce_name(arg0, arg1...);
示例1(in):
--创建存储过程,输入 id 和 name 进行查询
DELIMITER $$CREATE PROCEDURE getInfoByIdName(IN stuId INTEGER, IN stuName VARVHAR(255))BEGIN
SELECT * FROM students WHERE stu_id = stuId AND stu_name =stuName;END$$
DELIMITER ;--调用
CALL getInfoByIdName(1001, ‘Marry‘);
示例2(out):
--创建存储过程,输入 id 返回 对应的 name
DELIMITER $$CREATE PROCEDURE getNameById(IN stuId INTEGER, OUT stuName VARCHAR(255))BEGIN
--表示把查询结果放入变量 stuName 中
SELECT stuName INTO stuName FROM students WHERE stu_id =stuId;END$$
DELIMITER ;--调用,调用完成后,把值放进变量 stuName 里
CALL getNameById(1001, @stuName);--查询变量
SELECT @stuName;
示例3(相对复杂点的,以前项目上写的存储过程):
--查询应用账户
DELIMITER $$CREATE PROCEDURE searchAppAccount(in appAccountName VARCHAR(50),in multipleId VARCHAR(50),in idType INTEGER,in concat VARCHAR(50),in concatType INTEGER,in startDate VARCHAR(50),in endDate VARCHAR(50),in balControl INTEGER,in currentPage INTEGER,in pageSize INTEGER)BEGIN
--查询应用账户
SELECT * FROMapp_accountWHERE 1=1 and
--应用账户名
if (appAccountName is null, 1=1, app_account_name like concat(‘%‘,appAccountName,‘%‘))--时间段
and if (startDate is null, 1=1, create_date > str_to_date(startDate,‘%Y-%m-%d %H:%i:%s‘))and if (endDate is null, 1=1, create_date < str_to_date(endDate, ‘%Y-%m-%d %H:%i:%s‘))--余额控制
and if (balControl is null, 1=1, bal_control =balControl)--id
and if (multipleId is null, 1=1,if(idType = 0, (
cus_account_idin(select cus_account_id from app_appid where app_id =multipleId
)
),null)or if(idType = 20, (
cus_account_idin(
multipleId
)
),null)or if(idType = 30, (
cus_account_idin(select cus_account_id from main_account where ope_sub_id =multipleId
)
),null)or if(idType = 10, (
app_account_idin(
multipleId
)
),null)or if(idType = 0, (
app_account_idin(select app_account_id from app_appid where app_id =multipleId
)
),null)
)--联系方式
and if (concat is null, 1=1,if(concatType = 10, (
cus_account_idin(--主体用户
select cus_account_id from main_account where ope_sub_id in(select for_id from user_info where email = concat or tel =concat
)union
--主账户用户
select for_id from user_info where email = concat or tel =concat
)
),null)or if(concatType = 20, (
cus_account_idin(select cus_account_id from main_account where email = concat or concat_tel =concat
)
),null)or if(concatType = 30, (
cus_account_idin(select cus_account_id from main_account where ope_sub_id in(select operation_id from operation_subject where email = concat or concat_tel =concat
)
)
),null)or if(concatType = 10, (
app_account_idin(--user 联系方式 ---- 应用账户用户
if(concatType = 10, (select for_id from user_info where email = concat or tel =concat
),1=0)
)
),null)
)order by create_date asclimit currentPage, pageSize;END$$
DELIMITER ;