（五）存储过程
本帖最后由 yukari123 于 2023-4-3 19:18 编辑存储过程三大作用：
储存和管理SQL代码 Store and organize SQL
性能优化 Faster execution
数据安全 Data security
创建存储
右键选择 Create Stored Procedure，填空，Apply。
CREATE PROCEDURE 'get_payments' ()
BEGIN
SELECT * FROM payments;
END
删除存储过程
DROP PROCEDURE IF EXISTS new_procedure;
参数USE sql_invoicing;
DROP PROCEDURE IF EXISTS get_invoices_by_client ;
DELIMITER $$
CREATE PROCEDURE get_invoices_by_client
(
client_id INT-- 为何不写INT(11)？
)
BEGIN
SELECT *
FROM invoices i
WHERE i.client_id = client_id;
END$$
DELIMITER ;
CALL get_invoices_by_client(1);
带默认值的参数
CREATE PROCEDURE `获取信息` (
clients int,
payment_method tinyint
)
BEGIN
select *
FROM payment p
where p.client_id=ifnull(client_id,p.client_id)and p.payment_method=ifnull(payment_method,p.payment_method);
END
参数验证
IF 错误参数条件表达式 THEN
SIGNAL SQLSTATE '错误类型'
输出参数
页:
[1]