mysql 存储过程 for opetoin_Mysql 存储过程

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 ;