(第8讲存储过程.pptVIP

第8讲 存储过程和用户自定义函数 8.1 SQL扩展 8.2 存储过程 8.3 用户自定义函数 8.4 使用文本和图像数据 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile . Copyright 2004-2011 Aspose Pty Ltd. 存储过程的概念 SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。  Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile . Copyright 2004-2011 Aspose Pty Ltd. 8.1存储过程扩展 BEGIN statement_1 statement_2 …… END 8.1.1 语句块 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile . Copyright 2004-2011 Aspose Pty Ltd. 8.1.2 IF语句 IF (SELECT COUNT(*) FROM works_on WHERE project_no=p1‘ GROUP BY project_no)3 PRINT The number of employees in the project p1 is 4 or more ELSE BEGIN PRINT The following employees work for the project p1 SELECT emp_fname,emp_lname FROM employee,works_on WHERE employee.emp_no=works_on.emp_no AND project_no=p1 END 例8.1 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile . Copyright 2004-2011 Aspose Pty Ltd. 8.1.3 WHILE语句 WHILE((SELECT SUM(budget) FROM project) 500000.0) BEGIN UPDATE project SET budget=budget*1.1 IF(SELECT MAX(budget) FROM project)240000 BREAK ELSE CONTINUE END 例8.2 所有项目的预算都将增加10%直到预算的总和大于$500000。然而，如果其中的一个项目的预算大于$240000,那么重复的执行将被停止。 Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile . Copyright 2004-2011 Aspose Pty Ltd. 8.1.4 局部变量 DECLARE @avg_budget MONEY,@extra_budget MONEY SET @extra_budget=15000 SET @avg_budget=(SELECT AVG(budget) FROM project) IF(SELECT budget FROM project WHERE project_no=p1) @avg_budget BEGIN UPDATE project SET budget=budget+@extra_budget WHERE project_no=p1 PRINT Budget for p1 increased by @extra_budget END ELSE PRINT Budget for p1 unchanged 每一个局部变量都必须用DECLARE语句来定义。每个变量的定义中都包含他的名字和相应的数据类型。在批处理中引用变量通常要使用@。用以下方法可对一个局部变量进行赋值。 Evaluation only. Created with Aspose.Slides for .NET 3.