存储过程的创建和使用.docx

存储过程的创建和使用一、 特点l 存储过程包含一条或多条Transact-SQL语句。l 存储过程可以接受输入参数并可以返回输出值。l 一个存储过程可以调用另一个存储过程。l 存储过程会返回执行情况的状态代码给调用它的的程序。二、优点l 实现模块化编程，一个存储过程可以被多个用户共享和重用。l 存储过程有对数据库立即访问的的功能。l 使用存储过程可以加快程序的运行速度。l 使用存储过程可以减少网络流量。存储过程存储在数据库内，有应用程序通过一个调用语句就可以执行它，不需要大量Transact-SQL语句传送到服务器端。l 使用存储过程可以提高数据库的安全性。用户可以调用存储过程，实现对表中数据的有限操作，但可以不赋予其直接修改数据表的权限，这样就提高了表中数据的安全性。三、 创建方式l 使用SQL Server 企业管理器创建存储过程。（展开指定数据库可编程性存储过程（右键单击选择）新建存储过程）l 使用创建存储过程向导创建存储过程。l 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。（在“新建查询”中编辑）四、 组成l 所有输入参数以及传递个调用者的的输出参数。l 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。五、 使用Transact-SQL创建存储过程（1） 注意事项：l 不能将CREATE PROCEDURE语句与其他SQL 语句租和到单个批处理中。l 创建存储过程的权限默认属于数据库所有者，该所有者可以将此权限授予其他用户。l 存储过程是数据库对象，其名称必须遵守标识符规则。l 只能在当前数据库中创建存储过程。（2） 语法：CREATE PROCEDURE procedure_name;number parameter data_type VARYING =default OUTPUT ,nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement n （3） 代码意义：l procedure_name: 存储过程名。l number: 可选，用来对同名存储过程分组，一边使用一条 DROP PROCEDURE语句即可将同一组的过程一起删除。例如：DROP PROCEDURE stuInfoProc语句将除去整个组。如果名称中报行定界标识符，则数字不应包含在标识符中，只应在procedure_name 前后是哟好难过适当的定界符。l parameter:过程中的参数。在CREATE PROCERDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以定义2100个参数。l data_type: 用于指定参数的数据类型。在存储过程中，所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。l VARYING: 用于指定作为输出 OUTPUT 参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用与游标参数。l Default: 用于指定参数的默认值。如果指定了默认值，不必指定参数的值即可执行过程。默认值必须是常量或空值。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（% , _ , , ）.l OUTPUT: 表明该参数是一个返回参数。该选项值可以返回给EXECUTE.使用OUTPUT参数可将信息返回给调用过程。Text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。l RECOMPILE： 表明SQL Server 不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。在使用非典型值或临时值时而不希望覆盖保存在内存中的执行计划时，就可以使用RECOMPILE选项。l ENCRYPTION: 表示对存储过程文本进行加密。在系统表syscomments中的text字段是包含CREATE PROCEDURE 语句的存储过程文本。使用ENCRYPTION 关键字无法通过查看syscomments表来查看存储过程的内容。l FOR REPLICATION : 用于指定该存储过程只能在数据复制时使用。本选项不能和WITH RECOMPILE选项一起使用。l AS: 用于指定该存储过程要执行的操作。l sql_statement: 是存储过程中包含的任意数目和类型的Transaction-SQL语句。（4） 示例：-删除已存在的存储过程UER studentIF EXISTS (SELECT name FROM sysobjects WHERE name = stu_Age AND type = P )DROP PROCEDURE stu_AgeGO-创建带参数和输出返回的存储过程USE studentGO CREATE PROCEDURE stu_Age s_number varchar(10) , Age int OUTPUTAS-定义并初始化局部变量，用于保存返回值DECLARE ErrorValue intSET ErrorValue=0-求此学生的年龄SELECT Age=YEAR(GETDATE() YEAR(birthday)FROM t_studentWHERE s_numner=s_number-根据程序的执行结果返回不通的值IF (ERROR0)SET ErrorValue=ERRORRETURN ErrorValueGO六、 执行存储过程： EXEC UTE return_status= procedure_name;number | procedure_name_var parameter= value | variable OUTPUT | DEFAULT , n WITH RECOMPILE l EXECURE: 执行存储过程的命令关键字。如果此语句是批处理中的第一条语句，可以省略此关键字。l return_status: 是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中声明过。l procedure_name: 指定执行的存储过程名称。l ;number: 用来指定该存储过程与其他同名存储过程同组时标识号。l procedure_name_var: 局部定义变量名，代表存储过程名称。l paramenter：在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值有value参数或variable变量提供，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。l WITH RECOMPILE: 指定在执行存储过程时重新便宜执行计划。七、 示例USE studentGODECLARE stuAge int , ReturnValue int, S_number char(10)SET s_number=9952101EXECUTE ReturnValue=stu_Age s_number,stuAge OUTPUTPRINT 本程序的执行结果：PRINT 程序的返回值=+CAST（ReturnValue AS char(2)）PRINT 学号为” +RTRIM(s_number)+ “的学生的年龄是+CAST（试图A个AS 插入（2）+ 岁。）八、 修改存储过程（1） 语法：ALTER PROCEDURE procedure_name;numberparameterdata_typeVARYING =default OUTPUT , . . .n WITH PECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATIONASsql_statement . . . n （2） 注意事项：l 如果在CREATE PROCEDURE 语句中使用过参数，那么在ALTER PROCEDURE 语句中也应该使用这些参数。l 每次只能修改一个存储过程。l 存储过程的创建者、db_owner和db_ddladmin的成员拥有执行ALTER PROCEDURE语句的许可，其他用户不能使用。l 用ALTER PROCEDURE 更改的存储过程的权限和启动属性爆出不变。（3） 示例-修改存储过程USE studentGOALTER PROCEDURE stu_infos_number varchar(10)ASSelect 班级=SUBSTRING(t_student.s_number,1,LEN(t_student.s_number)-2),s_name AS 姓名,sex AS 性别,t_course.c_name AS 课程名称,score AS 考试成绩FROM t_student,t_course,t_scor