--------------------基本语法--------------------
一.创建存储过程
create procedure sp_name()
begin
.........
end
二.调用存储过程
1.基本语法:call sp_name()
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递
三.删除存储过程
1.基本语法:
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
四.其他常用命令
1.show procedure status
显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
2.show create procedure sp_name
显示某一个mysql存储过程的详细信息
举例:
A. 使用简单过程 以下存储过程将从视图中返回所有雇员(提供姓和名)、职务以及部门名称。此存储过程不使用任何参数。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees; GO CREATE PROCEDURE HumanResources.uspGetAllEmployees AS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment; GO uspGetEmployees 存储过程可通过以下方式执行: 复制 EXECUTE HumanResources.uspGetAllEmployees; GO -- Or EXEC HumanResources.uspGetAllEmployees; GO -- Or, if this procedure is the first statement within a batch: HumanResources.uspGetAllEmployees; B. 使用带有参数的简单过程 下面的存储过程只从视图中返回指定的雇员(提供名和姓)及其职务和部门名称。此存储过程接受与传递的参数精确匹配的值。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees; GO CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName; GO uspGetEmployees 存储过程可通过以下方式执行: 复制 EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar'; -- Or EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar'; GO -- Or EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman'; GO -- Or, if this procedure is the first statement within a batch: HumanResources.uspGetEmployees N'Ackerman', N'Pilar'; C. 使用带有通配符参数的简单过程 以下存储过程只从视图中返回指定的一些雇员(提供名和姓)及其职务和部门名称。此存储过程模式与所传递的参数相匹配;或者,如果未提供参数,则使用预设的默认值(以字母 D 打头的姓)。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees2; GO CREATE PROCEDURE HumanResources.uspGetEmployees2 @LastName nvarchar(50) = N'D%', @FirstName nvarchar(50) = N'%' AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName; GO uspGetEmployees2 存储过程可使用多种组合执行。下面只显示了几个组合: 复制 EXECUTE HumanResources.uspGetEmployees2; -- Or EXECUTE HumanResources.uspGetEmployees2 N'Wi%'; -- Or EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%'; D. 使用 OUTPUT 参数 以下示例将创建 uspGetList 存储过程。此过程将返回价格不超过指定数值的产品的列表。此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句在过程执行期间访问设置的某个值。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.uspGetList; GO CREATE PROCEDURE Production.uspGetList @Product varchar(40) , @MaxPrice money , @ComparePrice money OUTPUT , @ListPrice money OUT AS SET NOCOUNT ON; SELECT p.[Name] AS Product, p.ListPrice AS 'List Price' FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice; -- Populate the output variable @ListPprice. SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice); -- Populate the output variable @compareprice. SET @ComparePrice = @MaxPrice; GO 执行 uspGetList,返回价格低于 $700 的 Adventure Works 产品(自行车)的列表。OUTPUT 参数 @Cost 和 @ComparePrices 用于流控制语言,以便在“消息”窗口中返回消息。 ms187926.note(zh-cn,SQL.90).gif注意: OUTPUT 变量必须在创建过程时或使用变量时定义。参数名和变量名不一定要匹配;但是,除非使用 @ListPrice= variable 的形式,否则数据类型和参数位置必须匹配。 复制 DECLARE @ComparePrice money, @Cost money EXECUTE Production.uspGetList '%Bikes%', 700, @ComparePrice OUT, @Cost OUTPUT IF @Cost <= @ComparePrice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.' 下面是部分结果集: 复制 Product List Price -------------------------------------------------- ------------------ Road-750 Black, 58 539.99 Mountain-500 Silver, 40 564.99 Mountain-500 Silver, 42 564.99 ... Road-750 Black, 48 539.99 Road-750 Black, 52 539.99 (14 row(s) affected) These items can be purchased for less than $700.00. E. 使用 WITH RECOMPILE 选项 如果为过程提供的参数不是典型的参数,并且新的执行计划不应被缓存或存储在内存中,则 WITH RECOMPILE 子句会很有用。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspProductByVendor; GO CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%' WITH RECOMPILE AS SET NOCOUNT ON; SELECT v.Name AS 'Vendor name', p.Name AS 'Product name' FROM Purchasing.Vendor AS v JOIN Purchasing.ProductVendor AS pv ON v.VendorID = pv.VendorID JOIN Production.Product AS p ON pv.ProductID = p.ProductID WHERE v.Name LIKE @Name; GO F. 使用 WITH ENCRYPTION 选项 以下示例将创建 HumanResources.uspEncryptThis 存储过程。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspEncryptThis; GO CREATE PROCEDURE HumanResources.uspEncryptThis WITH ENCRYPTION AS SET NOCOUNT ON; SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours FROM HumanResources.Employee; GO 如以下示例所示,WITH ENCRYPTION 选项可阻止返回存储过程的定义。 运行 sp_helptext: 复制 EXEC sp_helptext 'HumanResources.uspEncryptThis'; 下面是结果集: 复制 The text for object 'HumanResources.uspEncryptThis' is encrypted. 直接查询 sys.sql_modules 目录视图: 复制 USE AdventureWorks; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis'); 下面是结果集: 复制 definition ---------------------- NULL (1 row(s) affected) G. 使用延迟名称解析 以下示例将创建 uspProc1 过程。该过程使用延迟名称解析。尽管引用的表在编译时不存在,但仍能创建存储过程。但是,执行过程时表必须存在。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspProc1; GO CREATE PROCEDURE dbo.uspProc1 AS SET NOCOUNT ON; SELECT column1, column2 FROM table_does_not_exist GO 若要验证是否已创建了存储过程,请运行以下查询: 复制 USE AdventureWorks; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.uspproc1'); 下面是结果集: 复制 definition ----------------------------------------------------------------------- CREATE PROCEDURE uspproc1 AS SELECT column1, column2 FROM table_does_not_exist (1 row(s) affected) H. 使用 EXECUTE AS 子句 以下示例显示使用 EXECUTE AS 子句指定执行存储过程的安全上下文。在此示例中,选项 CALLER 指定此过程可在调用它的用户上下文中执行。 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL DROP PROCEDURE Purchasing.uspVendorAllInfo; GO CREATE PROCEDURE Purchasing.uspVendorAllInfo WITH EXECUTE AS CALLER AS SET NOCOUNT ON; SELECT v.Name AS Vendor, p.Name AS 'Product name', v.CreditRating AS 'Credit Rating', v.ActiveFlag AS Availability FROM Purchasing.Vendor v INNER JOIN Purchasing.ProductVendor pv ON v.VendorID = pv.VendorID INNER JOIN Production.Product p ON pv.ProductID = p.ProductID ORDER BY v.Name ASC; GO I. 创建 CLR 存储过程 以下示例将创建 GetPhotoFromDB 存储过程,此过程引用 HandlingLOBUsingCLR 程序集中的 LargeObjectBinary 类的 GetPhotoFromDB 方法。创建存储过程前,需要在本地数据库中注册 HandlingLOBUsingCLR 程序集。 复制 CREATE ASSEMBLY HandlingLOBUsingCLR FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll''; GO CREATE PROCEDURE dbo.GetPhotoFromDB ( @ProductPhotoID int, @CurrentDirectory nvarchar(1024), @FileName nvarchar(1024) ) AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB; GO J. 使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。 首先,创建在 Currency 表上声明并打开一个游标的过程: 复制 USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspCurrencyCursor; GO CREATE PROCEDURE dbo.uspCurrencyCursor @CurrencyCursor CURSOR VARYING OUTPUT AS SET NOCOUNT ON; SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @CurrencyCursor; GO 接下来,运行以下批处理:声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。 复制 USE AdventureWorks; GO DECLARE @MyCursor CURSOR; EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO