2023年6月21日发(作者：)
数据库的存储过程1. 存储过程的概念存储过程 (Stored Procedure) 是在⼤型数据库系统中 , ⼀组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第⼀次编译后再次调⽤不需要再次编译 , ⽤户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执⾏它 , 存储过程是数据库中的⼀个重要对象 ; 存储过程中可以包含逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;2. 存储过程的优缺点优点 :由于应⽤程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了⼀个替换位置 ;由于存储过程在创建时即在数据库服务器上进⾏了编译并存储在数据库中 , 所以存储过程运⾏要⽐单个的 SQL 语句块要快 ;由于在调⽤时只需⽤提供存储过程名和必要的参数信息 , 所以在⼀定程度上也可以减少⽹络流量 , 简单⽹络负担 ;可维护性⾼ , 更新存储过程通常⽐更改 , 测试以及重新部署程序集需要较少的时间和精⼒ ;代码精简⼀致 , ⼀个存储过程可以⽤于应⽤程序代码的不同位置 ;增强安全性 :通过向⽤户授予对存储过程 (⽽不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;提⾼代码安全 , 防⽌ SQL注⼊ (但未彻底解决 , 例如将数据操作语⾔ DML 附加到输⼊参数) ;SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的⼀部分 , 可以验证⽤户提供的值类型 (但也不是万⽆⼀失 , 还是应该传递⾄数据库前得到附加验证) ;
缺点 :如果更改范围⼤到需要对输⼊存储过程的参数进⾏更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;可移植性差 , 由于存储过程将应⽤程序绑定到 Server , 因此使⽤存储过程封装业务逻辑将限制应⽤程序的可移植性 ; 如果应⽤程序的可移植性在您的环境中⾮常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是⼀个更佳的选择 ;3. 编写简单存储过程 创建⼀个存储过程：create procedure GetUsers()begin
select * from user;
end; 调⽤存储过程：call GetUsers(); 删除存储过程：drop procedure if exists GetUsers;4. 带参数的存储过程MySql ⽀持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传⼊和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是⼀系列 SQL 语句 ,⽤来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;下⾯的存储过程接受三个参数 ,
分别⽤于获取⽤户表的最⼩ ,
平均 ,
最⼤分数 ,
每个参数必须具有指定的类型 ,
这⾥使⽤⼗进制值(decimal(8,2)) ,
关键字 OUT
指出相应的参数⽤来从存储过程传出：create procedure GetScores( out minScore decimal(8,2), out avgScore decimal(8,2), out maxScore decimal(8,2))begin select min(score) into minScore from user; select avg(score) into avgScore from user; select max(score) into maxScore from user;end;调⽤此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所⽰ :call GetScores(@minScore, @avgScore, @maxScore);
该调⽤并没有任何输出 , 只是把调⽤的结果赋给了调⽤时传⼊的变量 @minScore, @avgScore, @maxScore , 然后即可调⽤显⽰该变量的值 :select @minScore, @avgScore, @maxScore;使⽤ IN 参数 , 输⼊⼀个⽤户 id , 返回该⽤户的名字 :create procedure GetNameByID( in userID int, out userName varchar(200))begin select name from user where id = userID into userName;end;123456789调⽤存储过程 :call GetNameByID(1, @userName);select @userName;12
5. 复杂⼀点⽰例 根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :create procedure GetPriceByID( in prodID int, in isDisc boolean, out prodPrice decimal(8,2))
begin declare tmpPrice decimal(8,2); declare prodDiscRate decimal(8,2); set prodDiscRate = 0.88; select price from products where id = prodID into tmpPrice; if isDisc then select tmpPrice*prodDiscRate into tmpPrice; end if; select tmpPrice into prodPrice;end;17181920该存储过程传⼊三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调⽤如下 :call GetPriceByID(1, true, @prodPrice);select @prodPrice;126. DELIMITERMySql 的命令⾏客户机的语句分隔符默认为分号 ; ，⽽实⽤程序也是⽤ ; 作为分隔符，这会使得存储过程的 SQL 出现语法错误，使⽤ DELIMITER告诉命令⾏实⽤程序将作为新的语句结束分隔符，最后再使⽤ DELIMITER ; 改回来，如下所⽰：DELIMITER $$
create procedure getUsers() begin select * from user;
end $$
DELIMITER ;DELIMITER $$CREATE PROCEDURE getcount()BEGINSELECT*FROMcontract_user;END $$DELIMITER ;CALL getcount();
Processing math: 100%
2023年6月21日发(作者：)
数据库的存储过程1. 存储过程的概念存储过程 (Stored Procedure) 是在⼤型数据库系统中 , ⼀组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第⼀次编译后再次调⽤不需要再次编译 , ⽤户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执⾏它 , 存储过程是数据库中的⼀个重要对象 ; 存储过程中可以包含逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;2. 存储过程的优缺点优点 :由于应⽤程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了⼀个替换位置 ;由于存储过程在创建时即在数据库服务器上进⾏了编译并存储在数据库中 , 所以存储过程运⾏要⽐单个的 SQL 语句块要快 ;由于在调⽤时只需⽤提供存储过程名和必要的参数信息 , 所以在⼀定程度上也可以减少⽹络流量 , 简单⽹络负担 ;可维护性⾼ , 更新存储过程通常⽐更改 , 测试以及重新部署程序集需要较少的时间和精⼒ ;代码精简⼀致 , ⼀个存储过程可以⽤于应⽤程序代码的不同位置 ;增强安全性 :通过向⽤户授予对存储过程 (⽽不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;提⾼代码安全 , 防⽌ SQL注⼊ (但未彻底解决 , 例如将数据操作语⾔ DML 附加到输⼊参数) ;SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的⼀部分 , 可以验证⽤户提供的值类型 (但也不是万⽆⼀失 , 还是应该传递⾄数据库前得到附加验证) ;
缺点 :如果更改范围⼤到需要对输⼊存储过程的参数进⾏更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等 ;可移植性差 , 由于存储过程将应⽤程序绑定到 Server , 因此使⽤存储过程封装业务逻辑将限制应⽤程序的可移植性 ; 如果应⽤程序的可移植性在您的环境中⾮常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是⼀个更佳的选择 ;3. 编写简单存储过程 创建⼀个存储过程：create procedure GetUsers()begin
select * from user;
end; 调⽤存储过程：call GetUsers(); 删除存储过程：drop procedure if exists GetUsers;4. 带参数的存储过程MySql ⽀持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传⼊和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是⼀系列 SQL 语句 ,⽤来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;下⾯的存储过程接受三个参数 ,
分别⽤于获取⽤户表的最⼩ ,
平均 ,
最⼤分数 ,
每个参数必须具有指定的类型 ,
这⾥使⽤⼗进制值(decimal(8,2)) ,
关键字 OUT
指出相应的参数⽤来从存储过程传出：create procedure GetScores( out minScore decimal(8,2), out avgScore decimal(8,2), out maxScore decimal(8,2))begin select min(score) into minScore from user; select avg(score) into avgScore from user; select max(score) into maxScore from user;end;调⽤此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所⽰ :call GetScores(@minScore, @avgScore, @maxScore);
该调⽤并没有任何输出 , 只是把调⽤的结果赋给了调⽤时传⼊的变量 @minScore, @avgScore, @maxScore , 然后即可调⽤显⽰该变量的值 :select @minScore, @avgScore, @maxScore;使⽤ IN 参数 , 输⼊⼀个⽤户 id , 返回该⽤户的名字 :create procedure GetNameByID( in userID int, out userName varchar(200))begin select name from user where id = userID into userName;end;123456789调⽤存储过程 :call GetNameByID(1, @userName);select @userName;12
5. 复杂⼀点⽰例 根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :create procedure GetPriceByID( in prodID int, in isDisc boolean, out prodPrice decimal(8,2))
begin declare tmpPrice decimal(8,2); declare prodDiscRate decimal(8,2); set prodDiscRate = 0.88; select price from products where id = prodID into tmpPrice; if isDisc then select tmpPrice*prodDiscRate into tmpPrice; end if; select tmpPrice into prodPrice;end;17181920该存储过程传⼊三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调⽤如下 :call GetPriceByID(1, true, @prodPrice);select @prodPrice;126. DELIMITERMySql 的命令⾏客户机的语句分隔符默认为分号 ; ，⽽实⽤程序也是⽤ ; 作为分隔符，这会使得存储过程的 SQL 出现语法错误，使⽤ DELIMITER告诉命令⾏实⽤程序将作为新的语句结束分隔符，最后再使⽤ DELIMITER ; 改回来，如下所⽰：DELIMITER $$
create procedure getUsers() begin select * from user;
end $$
DELIMITER ;DELIMITER $$CREATE PROCEDURE getcount()BEGINSELECT*FROMcontract_user;END $$DELIMITER ;CALL getcount();
Processing math: 100%
发布评论