什么是存储过程?
存储过程是一个包含一堆sql代码的数据库对象,
在我们的应用代码里,我们用这些存储过程来获取或保存数据,
使用存储过程来存储或管理Sql代码
创建存储过程
/*SELECT * FROM clients#想把这个查询存在存储过程中*/ #改变默认分隔符,这是新的分隔符,把$$$$之间的内容当作一个整体 DELIMITER $$ CREATE PROCEDURE get_clients()#创建存储过程,起名get_clients BEGIN SELECT * FROM clients;#注意分号,存储过程里每条语句都要用;终止 END$$#begin end 之间的内容称为存储过程的主体 DELIMITER ;#最后把默认分隔符改为分号
调用存储过程
第二个方法
CREATE PROCEDURE `get_payment` () BEGIN SELECT * FROM payments; END #然后点击右下角apply即可
删除存储过程
DROP PROCEDURE get_clients#get_clients要删除的名字
#删除不存在的过程会报错 #加上它IF EXISTS不会 DROP PROCEDURE IF EXISTS get_clients
在存储过程中添加参数
/*我们一般使用参数为存储过程传递值 也可以使用参数为调用程序赋值*/ #获取州名,返回位于那个州的顾客 DELIMITER $$ CREATE PROCEDURE get_clients ( p_state CHAR(2) )#类型设置为char,括号里写上所有参数,如果有很多个参数用逗号分割 BEGIN SELECT * FROM clients WHERE state = p_state; END$$ DELIMITER ;
/*我们一般使用参数为存储过程传递值 也可以使用参数为调用程序赋值*/ #获取州名,返回位于那个州的顾客 DELIMITER $$ CREATE PROCEDURE get_clients ( state CHAR(2) )#类型设置为char,括号里写上所有参数,如果有很多个参数用逗号分割 BEGIN SELECT * FROM clients c WHERE c.state = state; END$$ DELIMITER ;
调用
带默认值的参数
默认州是ca
DELIMITER $$ CREATE PROCEDURE get_clients ( state CHAR(2) ) BEGIN IF state IS NULL THEN SET state = 'CA'; END IF; SELECT * FROM clients c WHERE c.state = state; END$$ DELIMITER ;
默认不给州返回所有顾客
DELIMITER $$ CREATE PROCEDURE get_clients ( state CHAR(2) ) BEGIN IF state IS NULL THEN SELECT * FROM clients; ELSE SELECT * FROM clients c WHERE c.state = state; END IF; END$$ DELIMITER ;
可以改为下面的代码
DROP PROCEDURE get_clients#get_clients DELIMITER $$ CREATE PROCEDURE get_clients ( state CHAR(2) ) BEGIN SELECT * FROM clients c WHERE c.state = IFNULL(state, c.state);#如果第一个参数为空返回第二个 END$$ DELIMITER ;
参数验证
参数不合理时提示
CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`( invoice_id INT, payment_amount DECIMAL(9,2),#代表带有小数点的数字,第一个参数代表位数,第二个参数代表小数点后的位数 payment_date DATE ) BEGIN IF payment_amount <= 0 THEN SIGNAL SQLSTATE '22003'#错误代码数值,搜索sqlstate errors,点ibm.com的链接 SET MESSAGE_TEXT = '不合理的付款金额'; END IF; UPDATE invoices i SET i.payment_total = payment_amount, i.payment_date = payment_date WHERE i.invoice_id = invoice_id; END
用参数来给调用程序返回值
统计未支付用户的未支付发票的个数
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid`( client_id INT ) BEGIN SELECT COUNT(*), SUM(invoice_total) FROM invoices i WHERE i.client_id = client_id AND payment_total = 0; END
输出参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid`( client_id INT, OUT invoices_count INT,#out表示这个参数是输出参数 OUT invoices_total DECIMAL(9,2) ) BEGIN SELECT COUNT(*), SUM(invoice_total) INTO invoices_count, invoices_total FROM invoices i WHERE i.client_id = client_id AND payment_total = 0; END