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, 并授权给用户(组)

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

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

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

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

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

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

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

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

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

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

linenum
1. <%
2.     dim id, title, content
3.     dim sql, rs, cnn
4.     id=1
5.     title="update title where id=1"
6.     content="update content"
7.     sql="exec dbo.t_updateSproc @title='"&title&"',@content='"&content&"',@id="&id
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. %>

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

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

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

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

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

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

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

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