ASP + mssql 与 查询分析器 之 使用存储过程 建,添,更,选,删 基本数据操作 By shawl.qiu

ASP + mssql 与 查询分析器 之 使用存储过程 建,添,更,选,删 基本数据操作 By shawl.qiu

摘要: 
本文演示了在 MSSQL查询分析器中使用存储过程和在 ASP 中使用存储过程 对 MSSQL 的基本数据操作, 包括建表, 添加,更新,选取,删除记录.

说明: 
建=建表 / create table
添=添加记录 / insert
更=更新记录 / update
选=选取记录 / select
删=删除记录 / delete

目录:
1. 在查询分析器中建表, 名 t, 并授权给用户(组)

2. 添加记录
2.1 在查询分析器中构造 添加记录的存储过程, 并添加一条新记录
2.2 在 ASP 中 使用存储过程 dbo.t_insertSproc 添加数据 到 MSSQL
2.2.1 构造 ADO 连接与关闭函数 createCnn, closeCnn
2.2.2 使用 cnn 函数和 存储过程 添加数据到 MSSQL

3. 更新记录
3.1 在查询分析器中构造 更新记录用的存储过程, 并更新 ID为1 的记录集
3.2 在 ASP 中使用存储过程执行更新操作
(请检查你有没有创建并引用 2.2.1 的 createCnn 与 closeCnn 函数)

4. 选取记录
4.1 在查询分析器中构造 选取记录集用的存储过程, 并选取 id 为 1 的行
4.2 在 ASP 中使用存储过程, 执行选取数据操作

5. 删除记录
5.1 在查询分析器中构造 删除记录集用的存储过程, 并删除 title 字段中带 update 字符的行
5.2 在 ASP 中使用存储过程, 执行删除数据操作

shawl.qiu
2006-8-28
  http://blog.csdn.net/btbtd

1. 在查询分析器中建表, 名 t, 并授权给用户(组)

1. linenum
2. use shawl --使用数据库 shawl
3. go
4. create table t --建 t 表 
5. (
6.  --添加字段, 名 id, 并定义为识别字段, 且设为主键
7.  id int identity 
8.  constraint pk_t primary key,
9.  
10.  --添加字段, 名 title, 长度为 varchar 255, 不能为空, 默认值为空格
11.  title varchar(255) not null default ' ', 
12.  
13.  --添加字段, 名 content, 长度 varchar 4000, 不能为空, 默认值为空格
14.  content varchar(4000) not null default ' ',
15.  
16.  --添加字段, 名 pdate, 不能为空, 默认值为当前日期时间
17.  pdate datetime not null default getdate()
18. )
19. go
20.  --授权给用户(组)
21.  grant all on t to dbo
22. go

2. 添加记录
2.1 在查询分析器中构造 添加记录的存储过程, 并添加一条新记录

1. linenum
2. --构造添加记录用的存储过程, 名 dbo.t_insertSproc
3. create proc dbo.t_insertSproc
4.  
5.   --定义变量 title 与 content 
6.     @title varchar(255),
7.     @content varchar(4000)
8. as 
9. begin
10.  
11.   --设置不返回受影响的结果为真
12.     set noCount on
13.  
14.   --添加记录
15.     insert t (title,content) values(@title,@content)
16.     
17.     --选取并显示刚添加的新记录
18.   select * from t where id=@@identity
19. end 
20. go
21.   --授权给用户(组)
22.   grant exec on dbo.t_insertSproc to dbo
23. go
24.  
25. --添加一条新记录
26. exec dbo.t_insertSproc
27.   @title='Sproc title',
28.   @content='Sproc content'

2.2 在 ASP 中 使用存储过程 dbo.t_insertSproc 添加数据 到 MSSQL
2.2.1 构造 ADO 连接与关闭函数 createCnn, closeCnn

1. linenum
2. function createCnn(cnn)
3.     set cnn=createObject("adodb.connection")
4. end function
5. function closeCnn(cnn)
6.         cnn.close
7.     set cnn=nothing
8. end function

2.2.2 使用 cnn 函数和 存储过程 添加数据到 MSSQL

1. linenum
2. <%
3.     dim title, content
4.     dim sql, rs, cnn
5.     title="this title"
6.     content="the content"
7.     sql="exec dbo.t_insertSproc @title='"&title&"',@content='"&content&"'"
8.     call createCnn(cnn)
9.         cnn.open conn
10.         set rs=cnn.execute(sql)
11.             response.write "新记录已添加, ID为: "&rs("id")
12.             rs.close
13.         set rs=nothing
14.     call closeCnn(cnn)
15. %>

3. 更新记录
3.1 在查询分析器中构造 更新记录用的存储过程, 并更新 ID为1 的记录集

1. linenum
2. --使用数据库 shawl
3. use shawl
4. go
5.  
6. --构造更新用的存储过程, 名 dbo.t_updateSproc
7. create proc dbo.t_updateSproc
8.  
9.   --定义变量 id, title, content
10.     @id int,
11.     @title varchar(255),
12.     @content varchar(4000)
13. as
14. begin
15.  
16.   --设置不返回受影响行的结果
17.     set noCount on
18.  
19.   --更新内容, 如果变量 id 为空, 则不更新内容
20.     update t set title=@title, content=@content where id=coalesce(@id,0)
21.  
22.   --选取并显示被更新的记录集
23.     select * from t where id=@id
24. end
25. go
26.  
27.   --授权给用户(组)
28.   grant exec on dbo.t_updateSproc to dbo
29. go
30.  
31. --在查询分析器中执行存储过程, 更新列名 为ID 值=1 的行
32. exec dbo.t_updateSproc @id=1, @title='update title', @content='update content'

3.2 在 ASP 中使用存储过程执行更新操作
(请检查你有没有创建并引用 2.2.1 的 createCnn 与 closeCnn 函数)

1. linenum
2. <%
3.     dim id, title, content
4.     dim sql, rs, cnn
5.     id=1
6.     title="update title where id=1"
7.     content="update content"
8.     sql="exec dbo.t_updateSproc @title='"&title&"',@content='"&content&"',@id="&id
9.     call createCnn(cnn)
10.         cnn.open conn
11.         set rs=cnn.execute(sql)
12.             response.write "ID为: "&rs("id")&" 的记录集已更新"
13.             rs.close
14.         set rs=nothing
15.     call closeCnn(cnn)
16. %>

4. 选取记录
4.1 在查询分析器中构造 选取记录集用的存储过程, 并选取 id 为 1 的行

1. linenum
2. --使用数据库 shawl
3. use shawl
4. go
5.  
6. --构造选取记录用的存储过程, 名 dbo.t_selectSproc
7. create proc dbo.t_selectSproc
8.  
9.   --定义变量 id
10.     @id int=null
11. as 
12. begin
13.  
14.   --设置不返回受影响行的结果
15.     set noCount on
16.  
17.   --选取内容, 如果变量 id 为空, 则选取所有行
18.     select * from t where id=coalesce(@id,id)
19. end
20. go
21.  
22.   --授权给用户(组)
23.   grant exec on dbo.t_selectSproc to dbo
24. go
25.  
26. --在查询分析器中执行存储过程
27. exec dbo.t_selectSproc @id=1

4.2 在 ASP 中使用存储过程, 执行选取数据操作

1. linenum
2. <%
3.     dim id, num, i
4.     dim sql, rs, cnn
5.     id=1
6.     sql="exec dbo.t_selectSproc @id="&id
7.     'sql="exec dbo.t_selectSproc @id=null"
8.     call createCnn(cnn)
9.         cnn.open conn
10.         set rs=cnn.execute(sql)
11.             with rs
12.                     num=.fields.count-1
13.                 do until .eof
14.                     for i=0 to num
15.                         response.Write rs(i)
16.                         response.Write " "
17.                     next
18.                     response.Write "<br/>"
19.                     .movenext
20.                 loop    
21.                 .close            
22.             end with
23.         set rs=nothing
24.     call closeCnn(cnn)
25. %>

5. 删除记录
5.1 在查询分析器中构造 删除记录集用的存储过程, 并删除 title 字段中带 update 字符的行

1. linenum
2. --使用数据库 shawl
3. use shawl
4. go
5.  
6. --构造删除用的存储过程, 名 dbo.t_deleteSproc
7. create proc dbo.t_deleteSproc
8.   
9.   --定义变量 qStr
10.     @qStr varchar(255)
11. as
12. begin
13.  
14.   --设置不返回受影响行的结果
15.     set noCount on
16.   
17.   --删除数据操作, 使用 patindex 以模糊方式匹配并删除数据
18.     delete t where patindex('%'+lower(@qstr)+'%',lower(title))>0
19.   
20.   --返回被删除了几行 
21.     select rc=@@rowcount
22. end
23. go
24.   
25.   --授权给用户(组)
26.   grant exec on dbo.t_deleteSproc to dbo
27. go
28.  
29. --在查询分析器中执行存储过程
30. exec dbo.t_deleteSproc @qStr='update'

5.2 在 ASP 中使用存储过程, 执行删除数据操作

1. linenum
2. <%
3.     dim qStr
4.     dim sql, rs, cnn
5.     qStr="sproc"
6.     sql="exec dbo.t_deleteSproc @qStr="&qStr
7.     call createCnn(cnn)
8.         cnn.open conn
9.         set rs=cnn.execute(sql)
10.             response.write "共有 "&rs("rc")&" 行被删除"
11.             rs.close
12.         set rs=nothing
13.     call closeCnn(cnn)
14. %>