《数据库管理与应用》课件-32 存储过程

数据库原理与应用存储过程思考：是否可以用函数实现？要求：根据学生学号查询是否存在该学生，如果存在查询该学生的全部信息；否则，返回“查无此人！”结果1：以返回表的形式返回学生信息。用表值函数。结果2：返回一个字符串。用标量值函数。结果：用函数无法实现！表值函数？标量值值函数？优点：（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，就驻留在高速缓冲存储器，提高了系统性能。（3）使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库信息访问的权限，确保数据库的安全。（4）自动完成需要预先执行的任务。存储过程可以在SQLServer启动时自动执行。1.存储过程2.系统存储过程（1）系统存储过程：由SQLServer提供，可以作为命令执行。定义在master中，其前缀是“sp”。（2）用户存储过程：可以使用T-SQL语言编写。1.存储过程sp_addtype‘学号’,‘char(6)‘--添加用户自定义数据类型sp_attach_db‘xscj',‘c:\xscj.mdf'--附加数据库sp_detach_db‘xscj'--分离数据库sp_helptextsp_addtype--查看源代码2.系统存储过程视频：几个系统存储过程的使用createprocedure存储过程名/*定义过程名*/@参数参数类型/*定义参数的类型*/asbegin

功能语句/*执行的操作*/end创建的语法更加自由3.用户存储过程——定义存储过程的执行语法格式：存储过程名@参数=值调用的语法更加简单3.用户存储过程——调用createprocedurep1@idchar(6)asbeginifexists(select*from学生where学号=@id)select*from学生where学号=@idelseselect'查无此人！'endp1'98001'例1：创建一个存储过程，根据学生学号查询学生信息，如果该学生不存在则返回“查无此人”。3.用户存储过程——案例视频：存储过程的创建、调用和删除例2：创建一个存储过程,目的:根据学生学号查询学生的姓名.如果该学生不存在则返回“查无此人”,学号的默认值是‘98001’。createprocedurep2@idchar(6)=‘98001’asbeginifexists(select*from学生where学号=@id)select*from学生where学号=@idelseselect'查无此人！'end3.用户存储过程——带默认值p2p2‘98002’sp_helptextp23.用户存储过程——带默认值例3：修改例2，使之成为加密的存储过程。alterprocedurep2@idchar(6)=‘98001’withencryptionasbeginifexists(select*from学生where学号=@id)select*from学生where学号=@idelseselect'查无此人！'end3.用户存储过程——加密加密前：sp_helptextp2加密后：sp_helptextp23.用户存储过程——加密createprocedurep3@xmvarchar(8)asbeginifexists(select*from学生where姓名

like@xm)select*from学生where姓名

like@xmelseselect'查无此人！'end例4：创建存储过程，查询指定姓氏的学生的信息。p3'李%'3.用户存储过程——带通配符思考：创建一个存储过程，要求（1）功能：根据课程名求成绩小于指定分数的学生记录。（2）课程名默认值以“数据”开头，分数默认值为60分。（3）可以使用通配符实现模糊查询。倒计时3分钟！createprocedurep4@kcmvarchar(20)=‘数据%',@cjint=60asselect课程.课程号,课程名,学号,成绩from选课,课程where选课.课程号=课程.课程号and课程名like@kcmand成绩<@cj调用：p4@cj=704.练习

5.修改用户存储过程createprocedure存储过程名/*定义过程名*/@参数参数类型/*定义参数的类型*/asbegin

功能语句/*执行的操作*/endalter

语法格式：dropprocedure存储过程1，存储过程2…..永久地删除存储过程前，须确认该存储过程没有任何依赖关系。例5:删除数据库中的存储过程p1