Transact-sql编程 几个问题哎

[shuyu0]

最近在学这个，但是苦于搜不到什么好资料，只得来这求众位大大了···先谢谢啦

就是自定义函数和存储过程，到底有什么不同呢，
例如：编写一个自定义函数，能够实现参数化查询的功能。

这个应该怎样实现，带参数用存储过程不是更好的么

-----------------------

刚学习 很多不解啊 求众位大牛解答下啦
先行谢过啦 O(∩_∩)O~

[水族杰纶]

SQL Server中存储过程与函数的区别
这篇文章还是不错的
楼主可以先看看

[shuyu0]

[Quote=引用 1 楼 wufeng4552 的回复:]
SQL Server中存储过程与函数的区别
这篇文章还是不错的
楼主可以先看看
[/Quote]
谢谢LS这位了先

[dawugui]

[Quote=引用楼主 shuyu0 的回复:]
最近在学这个，但是苦于搜不到什么好资料，只得来这求众位大大了···先谢谢啦

就是自定义函数和存储过程，到底有什么不同呢，
例如：编写一个自定义函数，能够实现参数化查询的功能。

这个应该怎样实现，带参数用存储过程不是更好的么

-----------------------

刚学习 很多不解啊 求众位大牛解答下啦
先行谢过啦 O(∩_∩)O~
[/Quote]

--函数.

示例
A. 计算 ISO 周的标量值用户定义函数
下例中，用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数，必须在调用该函数前唤醒调用 SET DATEFIRST 1。 

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

下面是函数调用。注意 DATEFIRST 设置为 1。

SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。

ISO Week
----------------
52

B. 内嵌表值函数
下例返回内嵌表值函数。

USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)

C. 多语句表值函数
假设有一个表代表如下的层次关系： 

CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid), 
      title nvarchar(30)
      )

表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID，它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来，不过可以实现为用户定义函数。 

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given 
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key, 
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee 
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees 
   WHERE empid = @InEmpId 
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be 
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title 
   FROM @reports
   RETURN
END
GO

-- Example invocation
SELECT * 
FROM fn_FindReports('11234')
GO

[dawugui]

--存储过程.

示例
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
GO

au_info_all 存储过程可以通过以下方法执行：

EXECUTE au_info_all
-- Or
EXEC au_info_all

如果该过程是批处理中的第一条语句，则可使用：

au_info_all

B. 使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。

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
GO

au_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
GO

au_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 titles

OPEN @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
GO

G. 使用 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
GO

EXEC 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'

[shuyu0]

啊 谢谢楼上这位大大
非常感激 我好好看看

[Blocksom]

我也在找答案！

[幸运的意外]

各有各的优点和缺点吧。看情况确定使用什么。

[qiangqiang1235]

感谢各位大侠

[gogodiy]

学习，最近这些基础贴太有用了。