计算机软件及应用SQLSere存储过程教案课件

会计学1计算机软件及应用SQLSere存储过程会计学1计算机软件及应用SQLSere存储过程SQL风险分析数据库结构的暴露有多大危险？等于一个富人告诉了小偷他家的位置，还告诉了他家有几个门，每个门锁有何特征，小偷剩下要做的就只剩配钥匙了。非法用户如何顺利逃避验证？第1页/共32页SQL风险分析数据库结构的暴露有多大危险？等于一个富人告诉了奇怪现象第2页/共32页奇怪现象第2页/共32页奇怪现象分析输入值分析用户名:sino’--用户名：’or1=1--用户名：’or1=1；updatesystemusersetuserpwd=22--根源第3页/共32页奇怪现象分析输入值分析第3页/共32页SQL注入攻击SQL注入攻击SQLinjection，台湾称作SQL资料隐码攻击），简称注入攻击是发生于应用程序之数据库层的安全漏洞。简而言之，是在输入的数据字符串之中夹带SQL指令，在设计不良的程序当中忽略了检查，那么这些夹带进去的指令就会被数据库服务器误认为是正常的SQL指令而运行，因此招致到破坏。第4页/共32页SQL注入攻击SQL注入攻击第4页/共32页解决办法封装后,存放数据库服务器,前台调用。第5页/共32页解决办法封装后,存放数据库服第5页/共32页若该SQL语句的代码量很多select*fromaccountwhereusername=‘..'anduserpassword=‘…‘…同时在某个时刻有成千上万的用户要登陆这个系统,这时候将增大网络的流量封装的意义第6页/共32页若该SQL语句的代码量很多封装的意义第6页/共32页后台性能降低网络堵塞服务器用户前台用户前台……用户前台结果返回处理封装的意义第7页/共32页服务器用户前台用户前台……用户前台结果返回处理封装的意义第7封装的意义第8页/共32页封装的意义第8页/共32页存储过程概念存储过程（StoredProcedure）类似于C语言中的函数是一组编译好的、存储在服务器上的、能完成特定功能的T-SQL语句集合，设计数据库的一种对象；存储过程可以带参数，也可以返回结果第9页/共32页存储过程概念存储过程（StoredProcedure）类似存储过程概念存储过程可以包含数据操纵语句、变量、逻辑控制语句等第10页/共32页存储过程概念存储过程可以包含数据操纵语句、变量、逻辑控制语使用存储过程与函数优点可重用性一旦命名并保存在数据库以后，任何应用都可以调用抽象和数据屏蔽用户只需知道存储过程与函数对外提供的功能，而无需知道其内部实现安全性通过存储过程与函数提供数据对象的操作权限，而不必给出存储过程与函数涉及到每个对象的权限，提高了安全性执行速度快通过缓存计划并在重复执行时重用来降低t-sql代码的编译开销。减少网络流量第11页/共32页使用存储过程与函数优点可重用性第11页/共32页疑问存储过程和用户自定义函数都是将sql代码组织成为可以重复调用的代码块，在服务器端编译执行，那他们有什么区别呢？第12页/共32页疑问存储过程和用户自定义函数都是将sql代码组织成为可以重存储过程和函数函数缺陷一：对于外部数据库的业务操作例如：增删改；无法通过自定义函数封装；大家试想函数都是通过select调用，其无法独立运行；而相比存储过程可以独立执行业务处理。第13页/共32页存储过程和函数函数缺陷一：对于外部数据库的业务操作例如：增删存储过程和函数函数缺陷二：函数返回结果集不能返回多个；而存储过程可以。换句话说存储过程实现了函数不可实现的操作。第14页/共32页存储过程和函数函数缺陷二：函数返回结果集不能返回多个；而存储创建存储过程创建存储过程语法形式如下：

createPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

AS

sql_statement[...n]

存储过程调用Exec存储过程名[参数1，参数2]第15页/共32页创建存储过程创建存储过程语法形式如下：第15页/共32页案例一创建一存储过程显示用户信息创建一存储过程，实现用户登录验证（该存储过程有待登录框验证用户合法性使用）存储过程名称P_check输入参数@uname--用户名@

upassword--密码返回值满足条件的用户信息--创建存储过程createprocedureproc_check@unamechar(10),@upasswordchar(10)as select*fromuserswhereuname=@unameandupsd=@upasswordgo--调用存储过程，测试封装语句是否正确实现功能。execproc_check'jyl','123'第16页/共32页案例一创建一存储过程显示用户信息创建一存储过程，实现用户登案例二创建存储过程显示用户信息存储过程名称P_list输入参数无返回值表存储过程名称P_listinfo输入参数@uname–用户名返回值输入的用户名对应的用户信息)2.创建如下存储过程实现用户信息查询a.存储过程p_list:实现’查询all’：将所有用户信息显示b.存储过程实现‘查询’:模糊查询指定用户名信息createprocp_listas

select*fromaccountgoexecp_list--exec执行存储过程createprocp_list1@unamevarchar(10)as

select*fromaccountwhereusernamelike'%'+@uname+'%'goexecp_list1'su'第17页/共32页案例二创建存储过程显示用户信息存储过程名称P_list输入思考普通用户无法理解提示的含义,所以需要开发人员转化成可理解的业务提示。可选解决办法：通过状态返回；前台读取状态值转换为用户可理解的错误提示。第18页/共32页思考普通用户无法理解提示的含义,所以需要开发人员转化成可理解带参数输出存储过程修改存储过程p_add，如果输入的用户存在，返回0（表示失败），否则实施插入语句，返回1（表示注册成功）。案例要点：存储过程如何返回输出状态。注意：return只能返回一个值，一般用其返回状态值第19页/共32页带参数输出存储过程修改存储过程p_add，如果输入的用户存在第20页/共32页第20页/共32页带参数传递的存储过程举例为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程；分析：输入参数，接收给定的客户id；输出参数，输出联系人姓名，两个参数类型要与建表时候对应的列的数据类型一致；如何调用该存储过程？如查询客户id为20100146客户的联系人第21页/共32页带参数传递的存储过程举例为计费系统创建一个实现输入一个有效客带参数传递的存储过程举例如果输入的客户id不存在，调用存储过程时返回空消息，如何改进存储过程，使得不存在的客户返回“不存在”信息？第22页/共32页带参数传递的存储过程举例如果输入的客户id不存在，调用存储过带参数输出存储过程修改存储过程p_add，如果输入的用户存在，返回0（表示失败），否则实施插入语句，返回1（表示注册成功），输出存储新纪录的标识，以便进行调用的应用程序可立即访问自动生成的ID号。提示：通过@@identity获取新纪录的标识值。案例要点：存储过程如何带多个输出值第23页/共32页带参数输出存储过程修改存储过程p_add，如果输入的用户存在带参数输出存储过程第24页/共32页带参数输出存储过程第24页/共32页26常用的系统存储过程4-1系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。第25页/共32页26常用的系统存储过程4-1系统存储过程说明sp_data27EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系统存储过程4-2修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程演示：常用的存储过程第26页/共32页27EXECsp_databases常用的系统存储过程428常用的扩展存储过程：xp_cmdshell可以执行DOS命令下的一些的操作以文本行方式返回任何输出调用语法：

EXECxp_cmdshellDOS命令[NO_OUTPUT]常用的系统存储过程4-3第27页/共32页28常用的扩展存储过程：xp_cmdshell常用的系统存29常用的系统存储过程4-4USEmasterGOEXECxp_cmdshell'mkdird:\bank',NO_OUTPUTIFEXISTS(SELECT*FROMsysdatabases

WHEREname='bankDB')

DROPDATABASEbankDBGOCREATEDATABASEbankDB(

…)GOEXECxp_cmdshell'dirD:\bank\'--查看文件创建数据库bankDB，要求保存在D:\bank创建文件夹D:\bank查看文件夹D:\bank第28页/共32页29常用的系统存储过程4-4USEmaster创建数据库30小结明确前台调用存储过程和sql语句相比有什么优点？明确在数据库设计时，何时选择函数，何时选择存储过程明确存储过程的创建明确存储过程的执行调用第29页/共32页30小结明确前台调用存储过程和sql语句相比有什么优点？第2相比函数项目用户定义函数存储过程参数允许多个输入参数，不允许输出参数允许多个输入，输出参数返回值有且只有一个返回值可以没有返回值调用在表达式中引用，可以嵌套在查询语句的表达式中调用必须单独调用，不能结合select语句使用，可以结合insertinto语句使用第30页/共32页相比函数项目用户定义函数存储过程参数允许多个输入参数，不允许感谢您的关注第31页/共32页感谢您的关注第31页/共32页感谢您的观看。第32页/共32页感谢您的观看。第32页/共32页会计学34计算机软件及应用SQLSere存储过程会计学1计算机软件及应用SQLSere存储过程SQL风险分析数据库结构的暴露有多大危险？等于一个富人告诉了小偷他家的位置，还告诉了他家有几个门，每个门锁有何特征，小偷剩下要做的就只剩配钥匙了。非法用户如何顺利逃避验证？第1页/共32页SQL风险分析数据库结构的暴露有多大危险？等于一个富人告诉了奇怪现象第2页/共32页奇怪现象第2页/共32页奇怪现象分析输入值分析用户名:sino’--用户名：’or1=1--用户名：’or1=1；updatesystemusersetuserpwd=22--根源第3页/共32页奇怪现象分析输入值分析第3页/共32页SQL注入攻击SQL注入攻击SQLinjection，台湾称作SQL资料隐码攻击），简称注入攻击是发生于应用程序之数据库层的安全漏洞。简而言之，是在输入的数据字符串之中夹带SQL指令，在设计不良的程序当中忽略了检查，那么这些夹带进去的指令就会被数据库服务器误认为是正常的SQL指令而运行，因此招致到破坏。第4页/共32页SQL注入攻击SQL注入攻击第4页/共32页解决办法封装后,存放数据库服务器,前台调用。第5页/共32页解决办法封装后,存放数据库服第5页/共32页若该SQL语句的代码量很多select*fromaccountwhereusername=‘..'anduserpassword=‘…‘…同时在某个时刻有成千上万的用户要登陆这个系统,这时候将增大网络的流量封装的意义第6页/共32页若该SQL语句的代码量很多封装的意义第6页/共32页后台性能降低网络堵塞服务器用户前台用户前台……用户前台结果返回处理封装的意义第7页/共32页服务器用户前台用户前台……用户前台结果返回处理封装的意义第7封装的意义第8页/共32页封装的意义第8页/共32页存储过程概念存储过程（StoredProcedure）类似于C语言中的函数是一组编译好的、存储在服务器上的、能完成特定功能的T-SQL语句集合，设计数据库的一种对象；存储过程可以带参数，也可以返回结果第9页/共32页存储过程概念存储过程（StoredProcedure）类似存储过程概念存储过程可以包含数据操纵语句、变量、逻辑控制语句等第10页/共32页存储过程概念存储过程可以包含数据操纵语句、变量、逻辑控制语使用存储过程与函数优点可重用性一旦命名并保存在数据库以后，任何应用都可以调用抽象和数据屏蔽用户只需知道存储过程与函数对外提供的功能，而无需知道其内部实现安全性通过存储过程与函数提供数据对象的操作权限，而不必给出存储过程与函数涉及到每个对象的权限，提高了安全性执行速度快通过缓存计划并在重复执行时重用来降低t-sql代码的编译开销。减少网络流量第11页/共32页使用存储过程与函数优点可重用性第11页/共32页疑问存储过程和用户自定义函数都是将sql代码组织成为可以重复调用的代码块，在服务器端编译执行，那他们有什么区别呢？第12页/共32页疑问存储过程和用户自定义函数都是将sql代码组织成为可以重存储过程和函数函数缺陷一：对于外部数据库的业务操作例如：增删改；无法通过自定义函数封装；大家试想函数都是通过select调用，其无法独立运行；而相比存储过程可以独立执行业务处理。第13页/共32页存储过程和函数函数缺陷一：对于外部数据库的业务操作例如：增删存储过程和函数函数缺陷二：函数返回结果集不能返回多个；而存储过程可以。换句话说存储过程实现了函数不可实现的操作。第14页/共32页存储过程和函数函数缺陷二：函数返回结果集不能返回多个；而存储创建存储过程创建存储过程语法形式如下：

createPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

AS

sql_statement[...n]

存储过程调用Exec存储过程名[参数1，参数2]第15页/共32页创建存储过程创建存储过程语法形式如下：第15页/共32页案例一创建一存储过程显示用户信息创建一存储过程，实现用户登录验证（该存储过程有待登录框验证用户合法性使用）存储过程名称P_check输入参数@uname--用户名@

upassword--密码返回值满足条件的用户信息--创建存储过程createprocedureproc_check@unamechar(10),@upasswordchar(10)as select*fromuserswhereuname=@unameandupsd=@upasswordgo--调用存储过程，测试封装语句是否正确实现功能。execproc_check'jyl','123'第16页/共32页案例一创建一存储过程显示用户信息创建一存储过程，实现用户登案例二创建存储过程显示用户信息存储过程名称P_list输入参数无返回值表存储过程名称P_listinfo输入参数@uname–用户名返回值输入的用户名对应的用户信息)2.创建如下存储过程实现用户信息查询a.存储过程p_list:实现’查询all’：将所有用户信息显示b.存储过程实现‘查询’:模糊查询指定用户名信息createprocp_listas

select*fromaccountgoexecp_list--exec执行存储过程createprocp_list1@unamevarchar(10)as

select*fromaccountwhereusernamelike'%'+@uname+'%'goexecp_list1'su'第17页/共32页案例二创建存储过程显示用户信息存储过程名称P_list输入思考普通用户无法理解提示的含义,所以需要开发人员转化成可理解的业务提示。可选解决办法：通过状态返回；前台读取状态值转换为用户可理解的错误提示。第18页/共32页思考普通用户无法理解提示的含义,所以需要开发人员转化成可理解带参数输出存储过程修改存储过程p_add，如果输入的用户存在，返回0（表示失败），否则实施插入语句，返回1（表示注册成功）。案例要点：存储过程如何返回输出状态。注意：return只能返回一个值，一般用其返回状态值第19页/共32页带参数输出存储过程修改存储过程p_add，如果输入的用户存在第20页/共32页第20页/共32页带参数传递的存储过程举例为计费系统创建一个实现输入一个有效客户ID，查询其联系人的存储过程；分析：输入参数，接收给定的客户id；输出参数，输出联系人姓名，两个参数类型要与建表时候对应的列的数据类型一致；如何调用该存储过程？如查询客户id为20100146客户的联系人第21页/共32页带参数传递的存储过程举例为计费系统创建一个实现输入一个有效客带参数传递的存储过程举例如果输入的客户id不存在，调用存储过程时返回空消息，如何改进存储过程，使得不存在的客户返回“不存在”信息？第22页/共32页带参数传递的存储过程举例如果输入的客户id不存在，调用存储过带参数输出存储过程修改存储过程p_add，如果输入的用户存在，返回0（表示失败），否则实施插入语句，返回1（表示注册成功），输出存储新纪录的标识，以便进行调用的应用程序可立即访问自动生成的ID号。提示：通过@@identity获取新纪录的标识值。案例要点：存储过程如何带多个输出值第23页/共32页带参数输出存储过程修改存储过程p_add，如果输入的用户存在带参数输出存储过程第24页/共32页带参数输出存储过程第24页/共32页59常用的系统存储过程4-1系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。第25页/共32页26常用的系统存储过程4-1系统存储过程说明sp_data60EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系统存储过程4-2修改数据库的名称(单用户访问)