假如有表a(col1 int,col2 int,col3 int) 把表a 的数据写入表b create procedure insert2b as declare @var1 int,@var2 int,@var3 int declare cur_row cursor for select col1,col2,col3 from a where 条件 open cur_row fetch next from cur_row into @var1,@var2,@var3 while @@fetch_status=0 begin insert into b(字段1,字段2,字段3) values(@var1,@var2,@var3) fetch next from cur_row into @var1,@var2,@var3 end close cur_row deallocate cur_row
A. 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GOau_info_all 存储过程可以通过以下方法执行:EXECUTE au_info_all -- Or EXEC au_info_all如果该过程是批处理中的第一条语句,则可使用:au_info_allB. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GOau_info 存储过程可以通过以下方法执行:EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'如果该过程是批处理中的第一条语句,则可使用:au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull'C. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合:EXECUTE au_info2 -- Or EXECUTE au_info2 'Wh%' -- Or EXECUTE au_info2 @firstname = 'A%' -- Or EXECUTE au_info2 '[CK]ars[OE]n' -- Or EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%'D. 使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT AS SELECT 'Title Name' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT IF @@TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titles can be purchased for less than $200.' END ELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(@@TOTALCOST AS varchar(20)))下面是结果集:Title Name ------------------------------------------------------------------------ The Busy Executive's Database Guide The Gourmet Microwave The Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.E. 使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先,创建以下过程,在 titles 表上声明并打开一个游标:USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P') DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titlesOPEN @titles_cursor GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor GO
F. 使用 WITH RECOMPILE 选项 如果为过程提供的参数不是典型的参数,并且新的执行计划不应高速缓存或存储在内存中,WITH RECOMPILE 子句会很有帮助。USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%' WITH RECOMPILE AS SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE au_lname LIKE @@LNAME_PATTERN GOG. 使用 WITH ENCRYPTION 选项 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_this GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors GOEXEC sp_helptext encrypt_this下面是结果集:The object's comments have been encrypted.接下来,选择加密存储过程内容的标识号和文本。SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE o.name = 'encrypt_this'下面是结果集:说明 text 列的输出显示在单独一行中。执行时,该信息将与 id 列信息出现在同一行中。 id text ---------- ------------------------------------------------------------ 1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????(1 row(s) affected)H. 创建用户定义的系统存储过程 下面的示例创建一个过程,显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数,该过程将返回表名以 sys 开头的所有表(及索引)。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_showindexes' AND type = 'P') DROP PROCEDURE sp_showindexes GO USE master GO CREATE PROCEDURE sp_showindexes @@TABLE varchar(30) = 'sys%' AS SELECT o.name AS TABLE_NAME, i.name AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE o.name LIKE @@TABLE GO USE pubs EXEC sp_showindexes 'emp%' GO下面是结果集:TABLE_NAME INDEX_NAME INDEX_ID ---------------- ---------------- ---------------- employee employee_ind 1 employee PK_emp_id 2(2 row(s) affected)I. 使用延迟名称解析 下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在,但每个存储过程都可创建。IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc1' AND type = 'P') DROP PROCEDURE proc1 GO -- Creating a procedure on a nonexistent table. USE pubs GO CREATE PROCEDURE proc1 AS SELECT * FROM does_not_exist GO -- Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = 'P' AND o.name = 'proc1' GO USE master GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc2' AND type = 'P') DROP PROCEDURE proc2 GO -- Creating a procedure that attempts to retrieve information from a -- nonexistent column in an existing table. USE pubs GO CREATE PROCEDURE proc2 AS DECLARE @middle_init char(1) SET @middle_init = NULL SELECT au_id, middle_initial = @middle_init FROM authors GO -- Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = 'P' and o.name = 'proc2'
To pengdali(大力 V3.0) : 五星大虾,你分数那么多了,少给你点?呵呵。当然不会了。明天朋友面试,帮问一下存储过程,谢谢。
把表a 的数据写入表b
create procedure insert2b
as
declare @var1 int,@var2 int,@var3 int
declare cur_row cursor for
select col1,col2,col3 from a where 条件
open cur_row
fetch next from cur_row into @var1,@var2,@var3
while @@fetch_status=0
begin
insert into b(字段1,字段2,字段3) values(@var1,@var2,@var3)
fetch next from cur_row into @var1,@var2,@var3
end
close cur_row
deallocate cur_row
下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GOau_info_all 存储过程可以通过以下方法执行:EXECUTE au_info_all
-- Or
EXEC au_info_all如果该过程是批处理中的第一条语句,则可使用:au_info_allB. 使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GOau_info 存储过程可以通过以下方法执行:EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'如果该过程是批处理中的第一条语句,则可使用:au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'C. 使用带有通配符参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合:EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'D. 使用 OUTPUT 参数
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。
参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))下面是结果集:Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.E. 使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先,创建以下过程,在 titles 表上声明并打开一个游标:USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
如果为过程提供的参数不是典型的参数,并且新的执行计划不应高速缓存或存储在内存中,WITH RECOMPILE 子句会很有帮助。USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_by_author' AND type = 'P')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GOG. 使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GOEXEC sp_helptext encrypt_this下面是结果集:The object's comments have been encrypted.接下来,选择加密存储过程内容的标识号和文本。SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'下面是结果集:说明 text 列的输出显示在单独一行中。执行时,该信息将与 id 列信息出现在同一行中。
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????(1 row(s) affected)H. 创建用户定义的系统存储过程
下面的示例创建一个过程,显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数,该过程将返回表名以 sys 开头的所有表(及索引)。IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_showindexes' AND type = 'P')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO下面是结果集:TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
employee employee_ind 1
employee PK_emp_id 2(2 row(s) affected)I. 使用延迟名称解析
下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在,但每个存储过程都可创建。IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc1' AND type = 'P')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc2' AND type = 'P')
DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'
五星大虾,你分数那么多了,少给你点?呵呵。当然不会了。明天朋友面试,帮问一下存储过程,谢谢。