第7章 存储过程1.pptVIP

第7章 存储过程与触发器 存储过程和触发器都是SQL Server的数据库对象。 存储过程的存在独立于表，存放在服务器上，供客户端调用； 触发器的使用则和表的更新操作紧密结合，是一种特殊的存储过程，使用触发器可以大大提高数据库应用程序的灵活性和健壮性，可以利用触发器来实现复杂的业务规则，更有效地实施数据完整性。 练习 定义具有参数的存储过程。在学生选课数据库中，创建一个名称为Ins_stu的存储过程，该存储过程的功能是向Student表中插入一条记录，新记录的值由参数提供，如果未提供值给@sex时，由参数的默认值’男’代替。 执行存储过程： exec ins_stu ,‘杨光’,default,25,‘计算机系’ exec ins_stu‘美女,女,23,‘信息系‘ 7 . 1 存储过程 7 . 2 触发器 在 Microsoft SQL Server 2005 中，使用T-SQL语句编写存储过程。 存储过程可以接受输入参数、返回表格或标量结果和消息，调用“数据定义语言（DDL）”和“数据操作语言（DML）”语句，然后返回输出参数。 7.1.1 存储过程的类型 在 Microsoft SQL Server 2005 中有下列几种类型存储过程： (1) 系统存储过程。系统存储过程是由SQL Server提供的存储过程，可以作为命令执行。 (2) 扩展存储过程。扩展存储过程是指在SQL Server 2005环境之外，使用编程语言（例如C++语言）创建的外部例程形成的动态链接库（DLL）。 (3) 用户存储过程。Microsoft SQL Server 2005中，用户存储过程可以使用T-SQL语言编写。 1．使用T-SQL命令创建存储过程 创建存储过程的语句是CREATE PROCEDURE或CREATE PROC，二者同义。语法： CREATE { PROC | PROCEDURE } 存储过程名 [ { @参数名 数据类型 } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUT | OUTPUT?]/*定义参数的属性*/ ][ ,...n ] [WITH ENCRYPTION] /*对语句文本加密*/ AS { T-SQL语句 [ ...n ] /*执行的操作*/ } [;] 说明： 1. 不要以sp_为前缀创建任何存储过程。sp_前缀是SQL Server用来命名系统存储过程的，使用这样的名称可能会与以后的某些系统存储过程发生冲突。 2. VARYING：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变，仅适用于cursor参数。 3.default: 指定存储过程输入参数的默认值，默认值必须是常量或NULL。如果定义了默认值，执行存储过程时可以不提供实参。 4. 如果希望其他用户无法查看存储过程的定义，则可以使用WITH ENCRYPTION子句创建存储过程。这样，存储过程定义将以不可读的形式存储。 5. OUT 或 OUTPUT：指定参数为输出参数类型，用于从存储过程返回结果。 2．存储过程的执行 通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。语法格式： [ { EXEC | EXECUTE } ] { [@return_status = ] 存储过程名 [ [ @参数名= ] {值 | 变量名[ OUTPUT ]?| [ DEFAULT ]?}][ ,...n ] } [;] 3．举例 (1) 设计简单的存储过程。 【例7.1】 返学生的成绩情况，该存储过程不使用任何参数。 存储过程定义后，执行存储过程student_info： EXECUTE student_info (2) 使用带参数的存储过程。 【例7.2】 从学生选课数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接收与传递参数精确匹配的值。 执行存储过程student_info1： EXECUTE student_info1 ‘周一, ‘java (3) 使用带OUTPUT参数的存储过程。 【例7.3】 创建一个存储过程do_action，根据条件处理相应数据，处理后输出相应的信息。 接下来执行存储过程do_action，并查看结果： DECLARE @s_str char(8) EXEC do_action