1.查看存储过程信息的三个方式
(1)#查看存储过程的 状态
SHOW PROCEDURE STATUS like 'ydfbop_oper_loadincome';
(2)#查询存储过程创建信息
SHOW create PROCEDURE ydfbop_oper_loadincome;
(3)#通过 information_schema.ROUTINES查询存储过程信息
SELECT * from information_schema.ROUTINES WHERE ROUTINE_NAME ='ydfbop_oper_loadincome';
2.变量的定义
DECLARE date_start DATE;
3.变量的赋值
直接赋值使用SET,可以赋值常量或者表达式
查询结果赋值给变量,要求返回结果必须只有一行
CREATE FUNCTION get_customer_balance(p_customer_id INT,p_effective_date datetime)
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
...
DECLARE v_payments DECIMAL(5,2);
...
SELECT IFNULL(SUM(payment.amount),0) INTO v_payments FROM payment
WHERE payment.payment_date <= p_effec_date
AND payment.customer_id = p_customer_id;
...
RETURN v_rentfees + v_overfees - v_payments;
END;
4.定义条件和处理
DECLARE condition_name CONDITION FOR condition_value
condition_value: SQLSTATE[value] sqlstate_value | mysql_error_code
示例:主键重复时会退出,不再执行其他语句
CREATE PROCEDURE actor_insert()
BEGIN
set @x=1;
INSERT INTO actor(actor_id,first_name,last_name) values (201,'test','201');
set @x=2;