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

,#,单击此处编辑母版标题样式,会计学,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,会计学,1,计算机软件及应用,SQLSere,存储过程,会计学1计算机软件及应用SQLSere存储过程,SQL,风险分析,数据库结构的暴露有多大危险？,等于一个富人告诉了小偷他家的位置，,还告诉了他家有几个门，每个门锁有何,特征，小偷剩下要做的就只剩配钥匙了。,非法用户如何,顺利逃避验证？,第1页/共32页,SQL风险分析数据库结构的暴露有多大危险？等于一个富人告诉了,奇怪现象,第2页/共32页,奇怪现象第2页/共32页,奇怪现象分析,输入值分析,用户名,:sino-,用户名：,or 1=1-,用户名：,or 1=1,；,update systemuser set userpwd=22-,根源,第3页/共32页,奇怪现象分析输入值分析第3页/共32页,SQL,注入攻击,SQL,注入攻击,SQL injection,，台湾称作,SQL,资料隐码攻击），简称注入攻击,是发生于应用程序之数据库层的安全漏洞。简而言之，是在输入的数据字符串之中夹带,SQL,指令,，在设计不良的,程序,当中忽略了检查，那么这些夹带进去的指令就会被,数据库服务器,误认为是正常的,SQL,指令而运行，因此招致到破坏。,第4页/共32页,SQL注入攻击SQL注入攻击第4页/共32页,解决办法,封装后,存放数据库服,务器,前台调用。,第5页/共32页,解决办法封装后,存放数据库服第5页/共32页,若该,SQL,语句的代码量很多,select*from account where username=.and userpassword=,同时在某个时刻有成千上万的用户要登陆这个系统,这时候将增大网络的流量,封装的意义,第6页/共32页,若该SQL语句的代码量很多封装的意义第6页/共32页,后,台性能降低,网络,堵塞,服务器,用户,前台,用户,前台,用户,前台,结,果,返,回,处理,封装的意义,第7页/共32页,服务器用户前台用户前台用户前台结果返回处理封装的意义第7,封装的意义,第8页/共32页,封装的意义第8页/共32页,存储过程概念,存储过程（,Stored Procedure,）类似于,C,语言中的函数,是一组编译好的、存储在服务器上的、能完成特定功能的,T-SQL,语句集合，设计数据库的一种对象；,存储过程可以带参数，也可以返回结果,第9页/共32页,存储过程概念存储过程（Stored Procedure）类似,存储过程概念,存储过程可以包含数据操纵语句、变量、逻辑控制语句等,第10页/共32页,存储过程概念 存储过程可以包含数据操纵语句、变量、逻辑控制语,使用存储过程与函数优点,可重用性,一旦命名并保存在数据库以后，任何应用都可以调用,抽象和数据屏蔽,用户只需知道存储过程与函数对外提供的功能，而无需知道其内部实现,安全性,通过存储过程与函数提供数据对象的操作权限，而不必给出存储过程与函数涉及到每个对象的权限，提高了安全性,执行速度快,通过缓存计划并在重复执行时重用来降低,t-sql,代码的编译开销。,减少网络流量,第11页/共32页,使用存储过程与函数优点可重用性第11页/共32页,疑问,存储过程和用户自定义函数都是将,sql,代码组织成为可以重复调用的代码块，在服务器端编译执行，那他们有什么区别呢？,第12页/共32页,疑问 存储过程和用户自定义函数都是将sql代码组织成为可以重,存储过程和函数,函数缺陷一：对于外部数据库的业务操作例如：增删改；无法通过自定义函数封装；,大家试想函数都是通过,select,调用，其无法独立运行；而相比存储过程,可以独立执行业务处理。,第13页/共32页,存储过程和函数函数缺陷一：对于外部数据库的业务操作例如：增删,存储过程和函数,函数缺陷二：函数返回结果集不能返回多个；而存储过程可以。换句话说存储过程实现了函数不可实现的操作。,第14页/共32页,存储过程和函数函数缺陷二：函数返回结果集不能返回多个；而存储,创建存储过程,创建存储,过程语法形式如下：,create,PROC,EDURE procedure_name;number,parameter data_type,VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATION,AS,sql_statement .n,存储过程调用,Exec,存储过程名,参数,1,，参数,2,第15页/共32页,创建存储过程 创建存储过程语法形式如下：第15页/共32页,案例一 创建一存储过程显示用户信息,创建一存储过程，实现用户登录验证（该存储过程有待登录框验证用户合法性使用）,存储过程名称,P_check,输入参数,uname -,用户名,upassword-,密码,返回值,满足条件的用户信息,-,创建存储过程,create procedure proc_check uname char(10),upassword char(10),as,select*from users where uname=uname and upsd=upassword,go,-,调用存储过程，测试封装语句是否正确实现功能。,exec proc_check jyl,123,第16页/共32页,案例一 创建一存储过程显示用户信息创建一存储过程，实现用户登,案例二 创建存储过程显示用户信息,存储过程名称,P_list,输入参数,无,返回值,表,存储过程名称,P_listinfo,输入参数,uname,用户名,返回值,输入的用户名对应的用户信息,),2.,创建如下存储过程实现用户信息查询,a.,存储过程,p_list:,实现查询,all,：将所有用户信息显示,b.,存储过程实现查询,:,模糊查询指定用户名信息,create proc p_list,as,select*from account,go,exec p_list -exec,执行存储过程,create proc p_list1 uname varchar(10),as,select*from account where username like%+uname+%,go,exec p_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_data,27,EXEC sp_databases,EXEC,sp_renamedb,Northwind,Northwind1,USE stuDB,GO,EXEC,sp_tables,EXEC,sp_columns,stuInfo,EXEC,sp_help,stuInfo,EXEC,sp_helpconstraint,stuInfo,EXEC,sp_helpindex,stuMarks,EXEC,sp_helptext,view_stuInfo_stuMarks,EXEC,sp_stored_procedures,常用的系统存储过程,4-2,修改数据库的名称,(,单用户访问,),列出当前系统中的数据库,当前数据库中查询的对象的列表,返回某个表列的信息,查看表,stuInfo,的信息,查看表,stuInfo,的约束,查看表,stuMarks,的索引,查看视图的语句文本,查看当前数据库中的存储过程,演示：常用的存储过程,第26页/共32页,27EXEC sp_databases常用的系统存储过程 4,28,常用的扩展存储过程：,xp_cmdshell,可以执行,DOS,命令下的一些的操作,以文本行方式返回任何输出,调用语法：,EXEC xp_cmdshell DOS,命令,NO_OUTPUT,常用的系统存储过程,4-3,第27页/共32页,28常用的扩展存储过程：xp_cmdshell 常用的系统存,29,常用的系统存储过程,4-4,USE master,GO,EXEC,xp_cmdshell,mkdir d:bank,NO_OUTPUT,IF,EXISTS,(SELECT*FROM sysdatabases,WHERE name=bankDB),DROP DATABASE bankDB,GO,CREATE DATABASE bankDB,(,),GO,EXEC,xp_cmdshell,dir D:bank-查看文件,创建数据库,bankDB,，要求保存在,D:bank,创建文件夹D:bank,查看,文件夹D:bank,第28页/共32页,29常用的系统存储过程 4-4USE master创建数据库,30,小结,明确前台调用存储过程和,sql,语句相比有什么优点？,明确在数据库设计时，何时选择函数，何时选择存储过程,明确存储过程的创建,明确存储过程的执行调用,第29页/共32页,30小结明确前台调用存储过程和sql语句相比有什么优点？第2,相比函数,项目,用户定义函数,存储过程,参数,允许多个输入参数，不允许输出参数,允许多个输入，输出参数,返回值,有且只有一个返回值,可以没有返回值,调用,在表达式中引用，可以嵌套在查询