1创建和执行存储过程.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2017/7/6,#,数据库设计与实现,创建和执行存储过程,杨阳,存储过程,学习目标：,理论目标：理解存储过程的运行过程,实践目标：掌握存储过程的创建和执行的操作,存储过程,存储过程的概念,存储过程,(Stored Procedure),类似于,C,语言中的函数，它是一组为了完成特定功能的,T-SQL,语句集合。存储过程存储在数据库内，可以有用户的应用程序，通过指定存储过程名称及相关参数来执行。,存储过程,存储过程的优点。,1.,模块化的程序设计，实现代码多次调用。存储过程只需创建一次，并存储在数据库中，在以后的使用中，便可重复调用，不需要每次重新编写。,2.,加快执行速度。如果某一操作包含大量的或者需多次执行的代码，存储过程要比,T-SQL,代码执行速度要快。因为创建存储过程时，已经被分析和优化，但是对于,T-SQL,代码，每次执行时都要进行编译和优化。,3.,减少网络流量。使用存储过程，可以调用需要若干行,T-SQL,代码的操作，而不需要通过网络传送这些代码。,4.,可以作为安全机制。对用户只授予执行存储过程的权限，而不授予用户直接访问相应表的权限，这样既保证了用户操作数据库中的数据，又能保证用户不能访问相应的表，保证数据的安全。,存储过程,存储过程的分类,1.,系统存储过程。系统存储过程是一组预编的,T-SQL,语句，提供了管理数据库和更新表的功能，系统存储过程位于,master,数据库和,msdb,数据库，并且所有的存储过程的名称均为,sp_*,形式。例如,sp_databases,，代表列出服务器所有的数据库,，,2,.,用户定义的存储过程。由用户创建并能完成某种特定功能的存储过程,，分为,：,T-SQL,存储过程：指保存的,T-SQL,语句集合，可以接受和返回用户提出的参数。,CLR,存储过程：指对,Microsoft.NET Framework,公共语言运行时方法的引用，可以接受和返回用户提供的参数。在,.NET Framework,程序集中作为类的公共静态方法实现的。,3,.,扩展存储过程。扩展存储过程是,SQL Server,实例可以动态加载和运行的,DLL,。扩展存储过程是使用,SQL Server,扩展存储过程,API,编写的，可直接在,SQL Server,实例的地址空间中运行。,创建与执行存储过程,【例,8-1,】为数据库“,Librarymanage,”创建一个存储过程，存储过程名称为,Booksearch,，该存储过程在“,Bookinfo,”中查询书籍“数据库”的具体情况。,1,打开,对象资源管理,，左键单击展开“,数据库,-Librarymanage-,可编程性,”节点，选择存储过程节点，单击右键弹出菜单，左键点击“新建存储过程”,命令,。,2.,在,查询编辑器,中出现存储过程的编程模板，在模板上通过编写,T-SQL,代码，创建存储,过程,。,3.,单击“执行”按钮，运行成功后，在,对象资源管理,窗口，首先，右键单击“,存储过程,”节点，点击“刷新”按钮，然后左键单击展开“,存储过程,”节点，可以看到新建的存储过程,Booksearch,。,4,.,创建完新的存储过程，在,对象资源管理,窗口，首先，右键单击已创建的存储过程,dbo.Booksearch,弹出菜单，左键点击“执行存储过程”,命令,。,创建与执行存储过程,使用,T-SQL,创建存储过程，其语法如下：,CREATE PROCEDURE,参数,1,数据类型,=,默认值,OUTPUT,，,参数,n,数据类型,=,默认值,OUTPUT,AS,BEGIN,T-SQL,语句,END,使用,T-SQL,语句进行调用，语法如下：,EXEC,参数表,创建与执行存储过程,【例,8-2,】为数据库“,Librarymanage,”创建一个存储过程“,sp_bookquantity,”，该存储过程在“,Bookinfo,”表中查询剩余数量少于,5,本的书籍的编号、书名、作者、出版社和剩余数量。,单击“新建查询”按钮，在,查询编辑器,中输入以下代码。,USE Librarymanage,GO,CREATE,PROCEDURE sp_bookquantity,AS,BEGIN,SELECT Book_ID,Book_name,Book_author,Book_press,Book_price,Book_quantity,FROM,Bookinfo,WHERE Book_quantity5,END,单击,“执行,”,按钮，即创建了存储过程“,sp_bookquantity,”。,创建与执行存储过程,调用,此存储,过程,：,EXEC sp_bookquantity,结果如图,创建与执行存储过程,【例,8-3,】为数据库“,Librarymanage,”创建一个存储过程“,proc_bookname,”，该存储过程根据使用者输入的书名在“,Bookinfo,”表中查询书籍的书名、作者、出版社和价钱。,单击“新建查询”按钮，在,查询编辑器,中输入以下代码。,USE Librarymanage,GO,CREATE PROCEDURE proc_bookname bookname NVARCHAR(50),AS,BEGIN,SELECT Book_name,Book_author,Book_press,Book_price,FROM Bookinfo,WHERE Book_name=bookname,END,单击,“执行,”,按钮，即创建了存储过程“,proc_bookname,”,。,创建与执行存储过程,调用存储过程，执行以下代码，查询书名为“数据库”的书籍的情况：,EXEC proc_bookname ,数据库,结果如图,创建与执行存储过程,【例,8-4,】为数据库“,Librarymanage,”创建一个存储过程“,proc_bookpress,”，该存储过程根据使用者输入的出版社名在“,Bookinfo,”表中查询书籍的编号、书名、类型、作者、出版社和价钱等信息，不输入书名时查询“清华大学出版社”出版书籍的信息。,单击“新建查询”按钮，在,查询编辑器,中输入以下代码。,USE Librarymanage,GO,CREATE PROCEDURE,proc_bookpress PRESS,NVARCHAR(50)=,清华大学出版社,AS,BEGIN,SELECT Book_ID,Book_name,Book_type,Book_author,Book_press,Book_price,FROM Bookinfo,WHERE Book_press=PRESS,END,单击,“执行,”,按钮，即创建了存储过程“,proc_bookpress,”,。,创建与执行存储过程,调用存储过程，在不输入书名时候，执行以下代码，：,EXEC,proc_bookpress,结果如图,创建与执行存储过程,调用存储过程，查询科学出版社出版的书籍，执行以下代码，：,EXEC proc_bookpress,科学出版社,结果如图,创建与执行存储过程,【例,8-5,】为数据库“,Librarymanage,”创建一个存储过程“,proc_readerdepartment,”，该存储过程根据使用者输入的读者号在即可显示读者单位和姓名。,单击“新建查询”按钮，在,查询编辑器,中输入以下代码。,USE Librarymanage,GO,CREATE PROCEDURE proc_readerdepartment,ReaderID NVARCHAR(8),Readername NVARCHAR(30)OUTPUT,Readerdepartment NVARCHAR(50)OUTPUT,AS,SELECT Readername=Reader_name,Readerdepartment=Reader_department,FROM Readerinfo,WHERE Reader_ID=ReaderID,单击,“执行,”,按钮，即创建了存储过程“,proc_bookname,”,。,创建与执行存储过程,调用存储过程，执行以下代码，查询书名为“数据库”的书籍的情况：,declare,RID NVARCHAR(8),Readername NVARCHAR(30),Readerdepartment NVARCHAR(50),set RID=12010101,exec proc_readerdepartment RID,Readername=Readername output,Readerdepartment=Readerdepartment output,print,借书证号为,+RID+,的员工为,+Readerdepartment+Readername,结果如图,今天，我们的,课结束了！,