创建管理存储过程.doc

存储过程的概念（Stored Procedure）存储过程是一种数据库对象，是为了实现特定任务，将一组预编译的SQL以一个存储单元的形式存储在服务器上，供用户或客户端应用程序调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便被多次调用，以提高代码的执行效率。存储过程与其它编程语言中的过程相似，具有如下特点：(1):存储过程是一种有效地具有封装性可重复进行调用的SQL对象，其执行速度快，网络上数据传输流量低，具有一定的系统安全性。(2):存储过程接受输入参数并可以用输出参数的形式将多个值返回至调用过程或客户端应用程序。(3):存储过程可以包含用于数据库中执行操作（包括调用其他过程）的编程语句。(4):向调用过程或客户端应用程序返回存储过程的状态值，以指明成功或失败（以及失败的原因）。创建/管理简单的存储过程1. 查看存储过程使用系统存储过程查看存储过程信息(1):使用sp_helptext查看存储过程的文本信息，其语法格式如下： exec sp_helptext p_employeeLogin(2): 使用sp_depends查看存储过程的相关性 其语法格式为： exec sp_depends p_employeeLogin(3):使用sp_help查看存储过程的一般信息，如类型，拥有者，创建时间等信息。其语法格式为： exec sp_help p_employeeLogin2. 修改存储过程SQL Server 使用alter proc 语句来修改现有的存储过程，在修改存储过程时将会覆盖以前的存储过程的定义文本。3. 删除存储过程drop proc p_employeeLogin 创建/管理带参的存储过程1. 总体概论 存储过程的参数类型在SQL Server的存储过程中使用参数，可以扩展存储过程的功能，参数可用于存储过程及应用程序之间交换数据。存储过程中可以使用两种类型的参数，即输入参数和输出参数。(1):输入参数允许用户或者客户端应用程序将数据值传递到存储过程或函数中。(2):输出参数允许存储过程将数据值或游标变量传递给用户或者客户端应用成程序。(3):每个存储过程向用户或者客户端应用程序返回一个整数代码，如果存储过程没有显示设置返回代码的值，则返回代码为0。注:存储过程的参数在创建时应在create procedure(proc)和as关键字之间定义，每个参数都要指定参数名和数据类型，参数名必须以符号为前缀，可以为参数指定默认值。如果是输出参数，则应用output关键字描述，各个参数定义之间用逗号隔开。2. 创建/执行带输入参数的存储过程(1) 创建带输入参数的存储过程输入参数指在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息，使用输入参数可以向同一存储过程传递不同的值，从数据库中进行多次查询，返回符合条件的结果。例：use northwindgocreate proc p_getEmployeescity nvarchar(15),hiredate datetimeasselect * from employees where city=city and hiredate=hiredate(2) 执行带输入参数的存储过程在执行带有输入参数的存储过程时，SQL Server提供了如下两种参数传递的方式。a. 按位置传递参数值这种方式是在执行存储过程的语句中，直接给出参数的值，当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。例：exec p_getEmployees London,1990-1-1b. 这种方式是在执行存储过程的语句中，使用“参数名=参数值“的形式给出参数值。其优点是参数可以以任意顺序给出。exec p_getEmployees city=London,hiredate=1990-1-1exec p_getEmployees hiredate=1990-1-1,city=London(3) 在输入参数中使用默认值 在执行存储过程p_getemployees时，如果没有指定参数，则系统运行就会出错，此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时为输入参数指定默认值。因此修改存储过程代码如下:alter proc p_getEmployeescity nvarchar(15)=London,hiredate datetime=1990-1-1asselect * from employees where city=city and hiredate=hiredate注：执行存储过程为：exec p_getEmployees3. 创建/执行带输出参数的存储过程(1) 创建带输出参数的存储过程通过定义输出参数，可以从存储过程中返回一个或者多个值。为了使用输出参数，必须在创建和执行存储过程时为输出参数指定关键字output。例：use northwindgocreate proc p_getCountEmployeeshiredate datetime=1990-1-1,count int outputasselect count=count(*) from employees where hiredate=hiredatego(2) 执行带输出参数的存储过程在执行存储过程时，对输出参数如果忽略output关键字，则存储过程仍会执行但不返回值。为了接收某一存储过程的返回值，需要一个变量来存放返回参数值，该变量在执行时需加上关键字output来声明。declare ecount int exec p_getCountEmployees 1990-1-1,ecount outputselect 员工个数为：+str(ecount)go4. 存储过程的返回值存储过程在执行后都会返回一个整型值。如果执行成功，则返回0，否则返回-199之间的随机数，也可以使用return语句来指定一个存储过程的返回值。例题：在northwind数据库的员工表中增加一列pwd，用于存放员工的密码，并设置各员工的初始密码为员工的姓名。创建一个用于员工登录的存储过程，如果员工编号与密码都正确则存储过程返回值为0，如果员工编号不存在，则返回值为-1，如果员工编号存在，但密码不正确则返回-2./*在员工表中增加密码列pwd*/alter table employeesadd pwd varchar(50)go/*设置各员工密码为员工的姓名*/update employeesset pwd=firstname+lastnamego/*创建员工登录的存储过程*/create proc p_employeeLoginempId int,pwd varchar(50)asif exists(select * from employees where employeeid=empId)beginif exists(select * from employees where employeeid=empId and pwd=pwd)return 0 -返回代表登录成功elsereturn -2 -返回-2代表密码错误endelsereturn -1 -返回-1代表用户不存在go调用存储过程p_ employeeLogin，通过判断其返回值，输出不同的结果。其代码如下： declare result int exec result=p_employeelogin 1,Nancy Davolioif result=0print 登录成功if result=-1print 用户名不存在if result=-2print 密码错误go此程序执行结果为：密码错误老师讲的 视图简介 视图是一个虚拟表，该表中的记录是由一个查询语句执行后所得到的查询结果所构成的，从表面上看，视图和真实表一样，具有结构和数据，包含一系列带有名称的列和行数据。实质上，表是视图的基础，数据库中存储了视图定义，而不存放视图所对应的数据，视图所对应的数据仍存放在视图所引起的基