SQL Server 2000 程序设计学习笔记--第八章 存储过程BCP公用程序及SQL脚本

8.1何谓存储过程

存储过程的种类
系统存储过程 扩展存储过程 用户自定义存储过程
系统存储过程通常以sp_开头 大部分系统存储过程存储在master数

据库中
扩展存储过程的名称通常以xp_开头
用户自定义存储过程存放在所属数据库内的Stored Procedure数据

库对象中

临时性存储过程
一 私有的临时性存储过程
二 全局性的临时性存储过程
命名：在临时性存储过程之前会使用#或##来作为名称的开头以#作

为前置词所代表的是私有的临时性存储过程而以##作为前置词所代

表的是全局性的临时性存储过程

8.2 创建存储过程

创建存储过程的权限默认给数据库拥有者(dbo),dbo可以将权限转给

其他用户

范例
--创建一个名为test_prc的程序
CREATE PROCEDURE test_prc
AS
SELECT * FROM AUTHORS
GO
--执行test_prc程序
EXEC test_prc
GO

范例
--创建一个可以传递参数的add_par程序
--其中@par1为参数名称
--找出qty字段值等于@par1
create procedure add_par
@par1 int
as
select * from sales where qty=@par1
go
--执行add_par程序,并且传递参数值20，以便执行
exec add_par 20
go

8.3 编写存储过程的相关技巧
使用@@NESTLEVEL函数了解目前存储过程所在的层级

范例
--创建名为pr1的存储过程
create procedure pr1
as select @@nestlevel as 'LEVEL1'
go

--创建名为pr2的存储过程
create procedure pr2
as select @@nestlevel as 'LEVEL2'
--调用存储过程pr1之后,会立即到pr1去执行相关操作
exec pr1
go
--调用存储过程pr2之后,会立即到pr2去执行相关操作
exec pr2
go

延缓名称解析

自存储过程中返回值
使用OUTPUT参数将数据返回

范例
--创建名为test_prc的存储过程
create procedure test_prc
@au_id varchar(20),   --创建输入参数@au_id
@qty int,        --创建输入参数@qty
@out1 int output --将out1 当作是要返回数据的参数
as
select @qty=qty from authors where au_id=@au_id
return
go

使用返回码返回数据
通常返回码为1表示失败，而返回码为0则表示成功

使用@@ERROR函数，检查T-SQL语句在执行过程中是否曾发生错误

范例
--创建名为mytest的存储过程
--判断假如所输入的值存在时，则返回-1然后回到调用程序中
create procedure mytest
@au_id varchar(20)
as
if not exists(select * from authors where au_id=@au_id)
return -1
select au_id from @authors where au_id=@au_id
return
go
--声明用来存储存储过程所返回值的@status
--当@status值为-1时，则表示所输入的参数值已，并显示错误信息
DECLARE @status int
exec mytest=mytest '172.32.1176'
if @status=-1
print '发生错误'
go

8.4 自动执行存储过程

使用sp_procoption语句创建
执行sp_procoption系统存储过程之后，会返回0或1的整数值

范例
--将存储过程CustOrderHist设置为自动执行的存储过程
EXEC sp_procoption

'CustOrderHist',@OptionName=startup,@OptionValue='on'
go

8.5 修改 删除 查看及重新编译存储过程

修改存储过程 ALTER PROCEDURE

范例
--创建名称authors_pro的存储过程
--找出state为CA的作者
create procedure authors_pro
as
select au_id,au_lname,au_fname,state from authors where

state='CA'
go
--修改authors_pro存储过程内的定义
--改为找出state为KS的作者

alter procedure authors_pro
as
select au_lname,au_fname,state from authors where

state='KS'
go

删除存储过程 DROP PROCEDURE

范例
--删除名称为authors_pro的存储过程
DROP PROCEDURE authors_pro
go

查看存储过程定义
sp_helptext系统存储过程来浏览存储过程的定义

范例
--浏览authors_pro存储过程的定义
EXEC sp_helptext 'authors_pro'
go

查看存储过程相关信息
sp_help系统存储过程

范例
--查看authors_pro存储过程的相关信息
EXEC sp_help 'authors_pro'
go

将存储过程重新命名

sp_rename
执行sp_rename系统存储过程之后，会返回0或非零的整数值

范例
--将原先的authors_pro,更新为authors_procedure
EXEC sp_rename 'authors_pro','authors_procedure'
go

将存储过程重新编译

重新编译
sp_recompile
范例
--将名称为authors_pro的存储过程强迫重新编译
exec sp_recompile 'authors_pro'
go

8.6 使用sql server enterprise manager 来创建，修改及删除存

储过程

8.7 BCP公用程序
BCP(批量通信程序)

在SQL Server中，提供了两种方式让你可以进行大量数据的复制，

一是使用BCP工具，另一种是使用T-SQL的BULK INSERT语句。
差异：BCP工具可以让你在DOS操作系统下执行大量数据的法制，而

BULK INSERT 语句可以让你使用T-SQL语句来执行，而不用在DOS操

作模式下执行，但却无法将要进行转移或复制的数据导入到文件中

。

并行加载及发送
BCP公用程序依次可以将文件发送给多个客户机但必须符合下列条件
1 BCP必须要处在无记录的模式
2 其复制的目的表格必须是没有索引

T-SQL 的BULK INSERT语句

范例
将c:/dat/customs.dat文件加载到misdb数据库内的customs表格中

，并且将数据以逗号来做分割字符
BULK INSERT MISDB...customs
from 'c:/dat/customs.dat'
with{
fieledterminator=',',fowterminator='/n'
}

8.8 SQL脚本

在查询分析器中保存打开脚本

设置自动生成脚本