- 假设你在开发一个应用,应用有一个数据库,你要在哪里写SQL语句?你不会在你的应用代码里写语句,它会让你的应用代码很混乱且难以维护。具体在哪里呢?在存储过程中或函数中。
- 存储过程是一组为了完成特定功能的SQL语句集合,经编译存储在数据库中,常用于执行复杂的业务逻辑和事务处理
- 创建一个存储过程
-
-- 创建一个存储进程 delimiter $$ create procedure get_invoices_balance() begin select * from invoices where invoice_total-payment_total>0; end$$ delimiter ; -- 使用 '$$' 改变默认分隔符,告诉MySQL'$$'之间是一个整体,end$$结束 call get_clients() -- call 语句 可以调用 存储进程
-
- 删除存储过程
- drop procedure if exists 名字;
- 参数
- 我们一般使用参数为存储过程传递值,我们也可以使用参数为调用程序赋值
-
-- 参数 drop procedure if exists get_clients_by_state; delimiter $$ create procedure get_clients_by_state(state char(2)) begin select * from clients c where c.state = state; end $$ delimiter ; call get_clients_by_state('ca')
- 带默认值的参数
- 存储过程调用者无法提供参数,我们为参数配置默认值
-
-- 提供默认参数 drop procedure if exists get_clients_by_state; delimiter $$ create procedure get_clients_by_state(state char(2)) begin if state is null then set state='ca'; end if ; -- 表示 if 语句结束,因为if语句可以是多个 select * from clients c where c.state = state; end $$ delimiter ; call get_clients_by_state(null) -- 使用 if - else drop procedure if exists get_clients_by_state; delimiter $$ create procedure get_clients_by_state(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 ; call get_clients_by_state(null) -- 使用 ifnull() drop procedure if exists get_payments; delimiter $$ create procedure get_payments(client_id int,payment_method_id tinyint) begin select * from payments p where p.client_id=ifnull(client_id,p.client_id) and p.payment_method=ifnull(payment_method_id,p.payment_method); end$$ delimiter ; call get_payments(null,null);
- 参数验证
- 当我们使用存储进程来插入、更新、删除数据时,我们要进行参数验证,确保我们的过程不会意外地往数据库存储错误数据。
-
-- 参数验证 drop procedure if exists make_payment; delimiter $$ create procedure make_payment ( invoice_id int, payment_amount decimal(9,2), payment_date date) begin if payment_amount<=0 then signal sqlstate 'Data Exception' 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$$ delimiter ; call make_payment(1, -20, '2019-03-10');
- 输出参数
- 我们可以 使用 参数 给 调用程序 返回 值
-
drop procedure if exists get_unpaid_invoices_for_client; delimiter $$ create procedure get_unpaid_invoices_for_client ( client_id int, out invoices_count int, -- 标记输出参数 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$$ delimiter ; -- 可以使用 MYSQL工作台提供的图形化工具,更简便 set @invoices_count = 0; -- 用户变量 set @invoices_total = 0; call sql_invoicing.get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total); select @invoices_count, @invoices_total;
- 变量
- 1.用户变量(会话变量)
- 调用有输出参数的存储过程时使用这些变量,通过传递这些变量,来获取输出参数值
- set 语句定义变量,set @invoices_count=0
- 2.本地变量
- 在存储过程或函数中的变量,一旦结束就被清空
- declare 语句声明变量
-
-- 本地变量 drop procedure if exists get_risk_factor; delimiter $$ create procedure get_risk_factor() begin declare riskfactor decimal(9,2) default 0; declare invoices_total decimal; declare invoices_count int; select count(*),sum(invoice_total) into invoices_count,invoices_total from invoices; set riskfactor=invoices_total/invoices_count*5; select riskfactor; end$$ delimiter ;
- 1.用户变量(会话变量)
- 函数
- 函数只能返回单一值,通常用于计算和转换数据
-
-- 设置函数 drop function if exists get_risk_factor_for_client; delimiter $$ create function get_risk_factor_for_client (client_id int) returns int -- 设置函数属性,确定性,能读,能更改 -- deterministic -- modifies sql data reads sql data begin declare riskfactor decimal(9,2) default 0; declare invoices_total decimal; declare invoices_count int; select count(*),sum(invoice_total) into invoices_count,invoices_total from invoices i where i.client_id=client_id; set riskfactor=invoices_total/invoices_count*5; return ifnull(riskfactor,0); end$$ delimiter ; -- 正常内置函数使用 select client_id,name,get_risk_factor_for_client(client_id) from clients;
- 其他约定
- 驼峰命名法
- 下划线命名法
SQL - 存储过程
于 2024-08-20 09:16:56 首次发布