《SQL Server 2008 从入门到精通》 学习笔记 第四天
2012-02-02 15:45 CodeCy 阅读(783) 评论(0) 编辑 收藏 举报数据操作
插入数据
INSERT语句
USE SQL2008SBS
GO
INSERT INTO LookupTables.AddressType
(AddressType)
VALUES('Corporate'),('Warehouse'),('Home'),('Work')
GO
--填充产品类别
declare @CategoryID int
select @CategoryID = CategoryID
from LookupTables.ProductCategory
where Category = 'Power Tools'
insert into LookupTables.ProductSubCategory
(CategoryID,SubcategoryName)
Values (@CategoryID,'Drill Press'),(@CategoryID,'Table Saw'),
(@CategoryID,'Drill Press'),(@CategoryID,'Belt Sander'),
(@CategoryID,'Jointer')
GO
--添加新客户地址
declare @CustomerID int,
@AddressTypeID int,
@StateProvinceID int,
@CountryID int
select @CustomerID = CustomerID
from Customers.Customer
where CompanyName = 'Wide World Importers'
select @AddressTypeID = AddressTypeID
from LookupTables.AddressType
where AddressType = 'Warehouse'
select @StateProvinceID = StateProvinceID
from LookupTables.StateProvince
where StateProvinceAbbrev = 'WA'
select @CountryID = CountryID
from LookupTables.Country
where CountryName = 'United States'
insert into Customers.CustomerAddress
(CustomerID,AddressTypeID,AddressLine1,AddressLine2,AddressLine3,
City,StateProvinceID,CountryID)
values(@CustomerID,@AddressTypeID,'123 Elm St.',null,null,'Seattle',@StateProvinceID,@CountryID)
go
select AddressID,CustomerID,AddressTypeID,AddressLine1,AddressLine2,AddressLine3,City,
StateProvinceID,CountryID
from Customers.CustomerAddress
SELECT INTO 语句
USE SQL2008SBS
GO
select CustomerID,CompanyName,FirstName,LastName,ModifiedDate
into #Customer
from Customers.Customer
go
更新数据
更新数据
use SQL2008SBS
go
update Products.ProductOptions
set UnitPrice = UnitPrice * 1.1
go
--提高4.99-5.99 价格10%
update Products.Product
set ListPrice = ListPrice * 1.1
where ListPrice between 4.99 and 5.99
go
--提高钢材刀片价格30.%，客户会为刀片支付额外10.%价格
update a
set a.ProductCost = a.ProductCost*1.3,
a.ListPrice = a.ListPrice * 1.1
from Products.Product a inner join LookupTables.ProductSubCategory b
on a.SubcategoryID = b.SubcategoryID
where b.SubcategoryName = 'Saw Blade'
go
--全部/4手柄价格调整到新的定价
update a
set a.UnitPrice = b.ListPrice
from Products.ProductOptions a inner join Products.Product b on a.ProductID = b.ProductID
where b.ProductName like '1/4" Shank%'
go
删除数据
删除数据
use SQL2008SBS
go
--向ProductOption 表中插入新行
USE SQL2008SBS
GO
INSERT INTO Products.ProductOptions
(SKU, ProductID, OptionDesc, UnitPrice)
VALUES('123SKU',1,'Dummy description',1.99)
GO
SELECT SKU, ProductID, OptionDesc, UnitPrice
FROM Products.ProductOptions
WHERE SKU = '123SKU'
GO
--执行语句将刚才插入数据删除
delete from Products.ProductOptions
where SKU = '123SKU'
go
select SKU,ProductID,OptionDesc,UnitPrice
from Products.ProductOptions
where SKU = '123SKU'
go
TRUNCATE语句
use SQL2008SBS
go
--TRUNCATE 语句 想要高效删除表中全部数据 truncate 语句性能胜于不带where 的delete 语句
--生成Product表的副本然后截断表
USE SQL2008SBS
GO
SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
INTO dbo.TestProduct
FROM Products.Product
GO
SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM dbo.TestProduct
GO
TRUNCATE TABLE dbo.TestProduct
GO
SELECT ProductID, SubcategoryID, ProductName, ProductCost, ListPrice, ProductMargin, ProductDescription
FROM dbo.TestProduct
GO
DROP TABLE dbo.TestProduct
GO
MERGE语句
作为SQL Server 2008 的新特性， MERGE 提供了一种在单个语句中执行多项 INSERT,UPDATE和DELETE操作的方法。 根据MERGE语句中规定的选择标准，您可以有条件的在表中执行INSERT,UPDATE 和 DELETE操作
根据条件执行多项操作
创建表
USE SQL2008SBS
GO
CREATE TABLE Products.ProductInventory
(ProductID INT NOT NULL,
Quantity INT NOT NULL,
CONSTRAINT pk_productinventory PRIMARY KEY CLUSTERED (ProductID))
GO
ALTER TABLE Products.ProductInventory
ADD CONSTRAINT fk_producttoproductinventory FOREIGN KEY (ProductID)
REFERENCES Products.Product (ProductID)
GO
CREATE TABLE Products.ProductShipment
(ProductID INT NOT NULL,
Quantity INT NOT NULL,
ReceiveDate DATE NOT NULL CONSTRAINT df_productshipment_receivedate DEFAULT GETDATE(),
CONSTRAINT pk_productshipment PRIMARY KEY CLUSTERED (ProductID))
ALTER TABLE Products.ProductShipment
ADD CONSTRAINT fk_producttoproductshipment FOREIGN KEY (ProductID)
REFERENCES Products.Product (ProductID)
GO
use SQL2008SBS
go
--创建ProductInventory 和FinishedProduct表
--执行下列查询维护ProductInventory 表
USE SQL2008SBS
GO
INSERT INTO Products.ProductInventory
(ProductID, Quantity)
VALUES(1,100)
GO
INSERT INTO Products.ProductShipment
(ProductID, Quantity, ReceiveDate)
VALUES(1,25,GETDATE()),(2,50,GETDATE())
GO
SELECT ProductID, Quantity
FROM Products.ProductInventory
SELECT ProductID, Quantity, ReceiveDate
FROM Products.ProductShipment
GO
MERGE Products.ProductInventory AS target
USING (SELECT ProductID, Quantity
FROM Products.ProductShipment
WHERE ReceiveDate = CAST(GETDATE() AS DATE)) AS source
ON (target.ProductID = source.ProductID)
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity + source.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, Quantity)
VALUES(source.ProductID, source.Quantity);
GO
SELECT ProductID, Quantity
FROM Products.ProductInventory
SELECT ProductID, Quantity, ReceiveDate
FROM Products.ProductShipment
GO
OUTPUT子句
在DML操作中，可以通过两种方法使用OUTPUt子句
1. 将deleted和/或 inserted的内容作为结果集直接返回给应用
2. 将deleted和/或inserted 的内容插入某张表或表变量
事务处理
更改跟踪
更改跟踪
操作更改跟踪
use SQL2008SBS
go
--在数据库上启用更改跟踪
alter database SQL2008SBS
set CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON)
GO
--执行代码，在Employee表上启用更改跟踪
use SQL2008SBS
go
alter table HumanResources.Employee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO
--执行下列代码，返回更改跟踪版本并插入新行
select CHANGE_TRACKING_CURRENT_VERSION()
go
insert into HumanResources.Employee
(FirstName,LastName,JobTitle,BirthDate,HireDate)
Values('Dan','Park','Engineer','1/1/1972','3/7/1994')
go
select CHANGE_TRACKING_CURRENT_VERSION()
go
--执行代码查看跟踪信息
select a.EmployeeID,a.SYS_CHANGE_VERSION, a.SYS_CHANGE_CREATION_VERSION,
a.SYS_CHANGE_OPERATION,a.SYS_CHANGE_COLUMNS,a.SYS_CHANGE_CONTEXT
from CHANGETABLE(CHANGES HumanResources.Employee,null) as a
select a.EmployeeID,a.SYS_CHANGE_VERSION,a.SYS_CHANGE_CREATION_VERSION,
a.SYS_CHANGE_OPERATION,a.SYS_CHANGE_COLUMNS,a.SYS_CHANGE_CONTEXT
from CHANGETABLE(CHANGES HumanResources.Employee,0) as a
go
--执行下列代码修改一行并且查看新的版本号
update HumanResources.Employee
set HireDate = '4/7/1996'
where FirstName = 'Dan' and LastName = 'Park'
go
select CHANGE_TRACKING_CURRENT_VERSION()
go
--执行下列代码查看更改跟踪信息
select a.EmployeeID,a.SYS_CHANGE_VERSION,a.SYS_CHANGE_CREATION_VERSION,
a.SYS_CHANGE_OPERATION,a.SYS_CHANGE_COLUMNS,a.SYS_CHANGE_CONTEXT,
case when CHANGE_TRACKING_IS_COLUMN_IN_MASK
(columnproperty(OBJECT_ID('HumanResources.Employee'),'HireDate','ColumnId')
,a.SYS_CHANGE_COLUMNS) = 1 then 'ColumnChanged' Else 'ColumnNotChanged'
end HireDateChanged
from CHANGETABLE(CHANGES HumanResources.Employee,1) as a
go
--执行下列代码，以金庸更改跟踪
Alter table HumanResources.Employee
disable CHANGE_TRACKING
go
alter database SQL2008SBS set CHANGE_TRACKING = OFF
go
变更数据捕获
操作变更数据捕获
use SQL2008SBS
go
--向数据库中添加新文件与文件组用于变更数据捕获记录表
ALTER DATABASE SQL2008SBS
ADD FILEGROUP CDC
GO
ALTER DATABASE SQL2008SBS
ADD FILE (NAME = CDC, FILENAME = 'D:\Documents\SQL Server\Data\CDC.ndf')
TO FILEGROUP CDC
GO
--执行下列代码在数据库和Employee表上启用变更数据捕获
use SQL2008SBS
go
exec sys.sp_cdc_enable_db
go
exec sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Employee',
@role_name = Null,
@filegroup_name = N'CDC',
@capture_instance = N'HumanResources_Employee',
@supports_net_changes = 1
go
--向Employee表中添加新行
insert into HumanResources.Employee
(FirstName,LastName,JobTitle,BirthDate,HireDate)
values('Kim','Akers','Engineer','1/1/1986','3/6/2000')
go
select EmployeeID,FirstName,LastName,JobTitle,BirthDate,HireDate
from HumanResources.Employee
go
--观察变更数据捕获信息
declare @from_lsn binary(10),
@to_lsn binary(10)
set @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Employee')
set @to_lsn =sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_all_changes_HumanResources_Employee(@from_lsn,@to_lsn,N'all')
go
--修改Employee表中的一条数据
update HumanResources.Employee
set HireDate = '4/6/2000'
where FirstName = 'Kim' And LastName = 'Akers'
go
select EmployeeID,FirstName,LastName,JobTitle,Birthdate,HireDate
from HumanResources.Employee
go
--观察变更数据捕获信息
declare @from_lsn Binary(10),
@to_lsn binary(10)
set @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Employee')
set @to_lsn =sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(@from_lsn, @to_lsn,N'all')
go
--禁用数据库和Employee表上的变更数据捕捉
exec sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name = N'Employee',
@capture_instance = N'HumanResources_Employee'
go
|
向表中添加新行 |
使用INSERT语句 |
|
根据select语句生成新表 |
使用 select into语句 |
|
修改表中的行 |
使用update语句 |
|
删除表中的行 |
使用delete语句 |
|
在最少日志事务中删除表中所有的行 |
使用truncate语句 |
|
根据选择标准有条件的添加，删除，修改表中的行 |
使用MERGE语句 |
|
显示启动一个事务 |
发布begin tran命令 |
|
显示保存事务内所做的所有更改 |
发布commit tran命令 |
|
放弃事务内所做的所有更改 |
发布rollback tran命令 |
|
启用更改跟踪 |
ALTER DATABASE <database name> SET CHANGE_TRACKING = ON ALTER TABLE <table name> ENABLE CHANGE_TRACKING |
|
获取更改跟踪信息 |
使用更改跟踪函数：CHANGETABLE, CHANGE_TRANCKING_MIN_BALID_ERSION, HANGE_TRANCKING_CURRENT_VERSION和WITH_CHANGE_TRACKING_CONTEXT |
|
启用变更数据捕获 |
执行sys.sp_cdc_enable_db |
|
访问有关变更数据捕获的更改与元素据信息 |
使用cdc模式中的对象和sys.fn_cdc_*元数据函数 |
高级数据库对象设计
视图
创建视图
use SQL2008SBS
go
--创建视图
create view Customers.CustomerOrders
as
select case when a.CompanyName is not null then a.CompanyName
else a.FirstName+''+a.LastName end CustomerName,
b.AddressLine1,b.AddressLine2,b.AddressLine3,b.City,d.StateProvinceAbbrev,
e.CountryName,c.OrderDate,c.GrandTotal,c.FinalShipDate
from Customers.Customer a
inner join Customers.CustomerAddress b on a.CustomerID = b.CustomerID
inner join Orders.OrderHeader c on a.CustomerID = c.CustomerID
inner join LookupTables.StateProvince d on b.StateProvinceID = d.StateProvinceID
inner join LookupTables.Country e on b.CountryID = e.CountryID
go
-- 执行查询
select CustomerName,AddressLine1,AddressLine2,AddressLine3,City,StateProvinceAbbrev,
CountryName,OrderDate,GrandTotal,FinalShipDate
from Customers.CustomerOrders
go
通过视图修改数据
需要条件：
1. 数据修改必须确切地引用某表
2. 视图中的列必须直接引用表中的列
- 列不能来源于聚合
- 列不能是UNION/UNION ALL,CROSS JOIN,EXCEPT 或INTERSECT 的计算结果
3. 被修改的列不能接受DISTINCT,GROUP BY或 HAVING子句的影响
4. 不能使用TOP操作符
通过视图修改数据
use SQL2008SBS
go
--针对sql2008sbs数据库执行代码
USE SQL2008SBS
GO
CREATE VIEW HumanResources.v_Employees
AS
SELECT a.EmployeeID, a.FirstName, a.LastName, a.JobTitle, a.BirthDate, a.HireDate,
c.AddressType, b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvince,
e.CountryName
FROM HumanResources.Employee a LEFT JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID
LEFT JOIN LookupTables.AddressType c ON b.AddressTypeID = c.AddressTypeID
LEFT JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID
LEFT JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
--执行下述select 语句观察刚才创建的视图结果
select EmployeeID,FirstName,LastName,JobTitle,BirthDate,HireDate,AddressType,AddressLine1,AddressLine2,AddressLine3,
City,StateProvince,CountryName
from HumanResources.v_Employees
go
--执行下述INsert 语句向视图插入一行
insert into HumanResources.v_Employees
(FirstName,LastName,JobTitle,BirthDate,HireDate)
values('Diane','Prescott','Vice-President','4/11/1980','1/2/2008')
go
--执行下述select语句观察刚才插入数据的结果
select *
from HumanResources.v_Employees
go
创建索引视图
创建索引视图的要求
1. select 语句不能引用其他视图
2. 所有的函数必须是确定的
3. 不允许使用avg,min,max,stdev,stdevp,var,varp
4. 所创建视图以及视图所引用的任意表时，ansi_nulls参数必须设置为on
5. 创建视图时必须设置schemabinding选项
6. Select语句不能包含子查询，外部联接，except,intersect,top,union,order by,distinct,compute/compute by,cross/outer apply,pivot或unpivot
创建索引视图
use SQL2008SBS
go
--创建
--创建索引视图
create view Archive.v_OrderItems
with SCHEMABINDING
AS
select a.OrderID,a.CustomerID,a.OrderDate,a.SubTotal,a.TaxAmount,a.ShippingAmount,a.GrandTotal,
a.FinalShipDate,b.OrderDetailID,b.SKU,b.Quantity,b.UnitPrice,b.Quantity+b.UnitPrice LineItemTotal
from Archive.OrderHeader a inner join Archive.OrderDetail b on a.OrderID = b.OrderID
go
--在视图上创建索引
create unique clustered index iucx_orderritems on Archive.v_OrderItems (OrderID,OrderDetailID)
go
查询替代
use SQL2008SBS
go
--查询替代
SELECT a.OrderID, CASE WHEN c.CompanyName IS NOT NULL THEN c.CompanyName ELSE c.FirstName + ' ' + C.LastName END CustomerName,
a.OrderDate, a.SubTotal, a.TaxAmount, a.ShippingAmount, a.GrandTotal, a.FinalShipDate, b.OrderDetailID, b.SKU, b.Quantity,
b.UnitPrice, b.Quantity + b.UnitPrice LineItemTotal
FROM Archive.OrderHeader a INNER JOIN Archive.OrderDetail b ON a.OrderID = b.OrderID
INNER JOIN Customers.Customer c ON a.CustomerID = c.CustomerID
GO
|
将select语句存入数据库以备将来使用 |
执行Create view命令 |
|
创建索引视图 |
在视图上创建唯一的聚合索引 |
存储过程
创建索引视图
存储过程只不过是T-SQL的批处理文件，被命名并存储入数据库而已
代码注释
两种不同注释结构：
--This is a single line comment
/*
This is a
Multi-lime comment
*/
变量、参数和返回代码
变量
可以创建、读取、写入本地变量，但不能创建或写入全局变量
全局变量
|
@@ERROR |
由最后一条执行语句返回的错误代码 |
|
@@IDENTITY |
连接内最后一个标示值 |
|
@@ROWCOUNT |
受最后一条语句影响的行的数量 |
|
@@TRANCOUNT |
在连接内打开的事务数据 |
|
@@VERSION |
SQL SERVER 版本 |
可使用 DECLARE 子句来实例化一个变量，在此处指定变量的名称与类型。变量可以被定义为除text,ntext以及image之外的任意类型，例如：
Declate @intvariable int,
@datevvariale date
Declare @tablevar table
(ID int not null,
Customer varchar(50) not null)
虽然可用单个declare语句来实例化多个变量，但表变量的实例化必须使用单独的declare语句
除使用 set或select语句完成赋值外，还可以在变量的实例化时为其分配一个值
Declare @intvariable int = 2,
@datevvariable date = getdate(),
@maxorderdate date = (select max(OrderDate) from Orders.OrderHeader),
@counter1 int,
@counter2 int
Set @counter1 = 1
Select @counter2 = -1
Select @intvariable, @datevariable,@ maxorderdate,@counter1,@counter2
使用set 或 select语句 执行变量计算
--SQL Server 2005 and below
Decalare @var int
Set @var = 1
Set @var = @var+1
Select @var
Set @var = @var*2
Select @var
Set @var = @var/4
Select @var
Go
--Sql Server 2008
Declare @var int
Set @var =1
Set @var +=1
Select @var
Set @var *=4
Select @var
Set @var /=4
Select @var
Go
参数
参数是本地变量，用于存储过程执行时向其传递数据。执行期间，所有参数都可被当成变量来用，可读可写。
可创建两种类型的参数：输入与输出。输出参数有关键字OUTPUT来标识。
Create procedure <procedure name> @parm1 int , @parm3 varchar(20) = ‘Default value’,
@orderid int output
As
--Code block
需要向应用程序返回单个值是可使用输出参数。如需返回整个结果集，则必须在存储过程中使用select语句，以生成结果集并返回给应用程序。
Create procedure <procedure name> @parm1 int , @parm2 varchar(50) = ‘Default value’
As
--This will return the results of this query to an application
Select orderId, customerId,orderdate,subtotal ,taxamount,shippingamount,grandtotal
From orders.orderheader
返回代码
返回代码用来向应用程序返回过程的执行状态。它并不传递数据，而是由于报告执行状态。
Create procedure <procedure name> @parm1 int , @parm2 varchar(20) = ‘Default valre’
As
--This will return the value 1 back to this caller
Return 1
执行存储过程
可使用exec语句来访问存储过程。
不带参数
Exec <stored procedure>
带参数
Declare @variable1 <data type>,
@variable2 <datatype>
Exec <stored procedure> @parameter1, @variable1 output,@variable2 out
要捕获返回代码
Declare @variable1 <data type>,
@variable2 <datatype>,
@returncode int
Exec <stored procedure> @parameter1, @variable1 output,@variable2 out
控制流结构
存储过程中可以使用如下几种控制流结构：
n Return
n If…else
n Begin…end
n While
n Break/continue
n Waitfor
n Goto
错误处理
--TRY…CATCH
Truncate table dbo.mytable
Begin try
Begin tran
Insert into dbo.mytable values(1)
Insert into dbo.mytable values(1)
Insert into dbo.mytable values(2)
Commit tran
End try
Begin catch
Rollback tran
Print ‘Cath’
End catch
Select * from dbo.mytable
动态执行
游标
游标有五个组成部分。Declare 用于定义一个select 语句，该语句生成游标中数据行的基础。Open使select语句执行，并将结果导入内存结构中，fetch用于从游标中一次获取一行。Close则用于关闭游标操作。Deallocate用于删除游标，然后重新分配之前存储游标结果集的内存结构。
声明游标的三种方法：
Declare curproducts cursor FAST_FORWARD for
Select productID, productName,ListPrice From Products.Product
Go
Declare curproducts cursor READ_ONLY FOR
Select ProductID, ProductName,listPrice from products.Product
Go
Declare curproducts cursor for
Select productId, ProductName,ListPrice From Products.Product
For read only
Go
一旦游标被声明，就可以发布Open命令执行select 语句
Open suiproducts
然后就可以用fetch语句从游标中的行获取数据。首次执行fetch命令时，指针位于游标 结果集的第一行数据处，再执行一次，指针便在游标中前进一行，直至超出游标结果集的范围。
每次执行Fetch语句会在全局变量@@FETCH_STATUS中放置一个值。您可以使用while循环来遍历游标，循环获取游标中的行。
CLR过程
创建管理过程
--创建数据库
CREATE DATABASE DBAdmin
GO
USE DBAdmin
GO
CREATE PROCEDURE dbo.asp_reindex @database SYSNAME, @fragpercent INT
AS
DECLARE @cmd NVARCHAR(max),
@table SYSNAME,
@schema SYSNAME
-- 用于演示目的使用光标。
-- 还可以做表变量和一个WHILE循环
DECLARE curtable CURSOR FOR
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id, database_id) SchemaName, OBJECT_NAME(object_id,database_id) TableName
FROM sys.dm_db_index_physical_stats (DB_ID(@database),NULL,NULL,NULL,'SAMPLED')
WHERE avg_fragmentation_in_percent >= @fragpercent
FOR READ ONLY
OPEN curtable
FETCH curtable INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @schema + '.' + @table +
' REBUILD WITH (ONLINE = ON)'
BEGIN TRY
EXEC sp_executesql @cmd
END TRY
BEGIN CATCH
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @database + '.' + @schema + '.' + @table +
' REBUILD WITH (ONLINE = OFF)'
EXEC sp_executesql @cmd
END
END CATCH
FETCH curtable INTO @schema, @table
END
CLOSE curtable
DEALLOCATE curtable
GO
exec dbo.asp_reindex 'SQL2008SBS',0
exec dbo.asp_reindex 'SQL2008SBSFS',0
|
向存储过程传递参数值 |
使用input参数。可以根据名称或位置来传递参数。如果为input参数指定了默认值，则参数可选，且如果未向存储过程传递参数值的话将使用所指定的默认值 |
|
从存储过程中返回单个值 |
使用output参数。单个存储过程中可以有多个output参数。如需返回一个结果集，则在存储过程中需包含select语句 |
|
执行存储过程 |
如果存储过程是批处理的第一条命令，您可以只指定过程的名称。否则，需要使用exec语句 |
|
有条件的执行代码 |
存储过程中的if…else因用于执行下一条语句或下一个语句块 while用于多次执行一个代码块 |
|
动态执行命令 |
使用exec(<command>)或sp_executesql<command> |
|
捕获集处理错误 |
将代码封装进try…catch中 |
|
一次处理结果集中的一行 |
使用游标 |
