存储过程

--创建存储过程
create procedure p1() select * from Users

--JOIN用法
select name,place 
from Users
inner join Address
on Users.ID=address.userid

-- =============================================
-- Author:        流逝在夏天
-- Create date:   2011年8月30日20:55:27
-- Update Date：  2011年9月2日3:44:51
-- Description:      简单复习存储过程
-- =============================================
USE test
--用户表
CREATE TABLE Users 
(
ID INT PRIMARY KEY IDENTITY(1,1),
UserName VARCHAR (20) UNIQUE NOT NULL,
Pwd VARCHAR(20) NOT NULL,
Sex CHAR(2)CHECK (sex = '女' OR sex ='男'), --or bit
Address VARCHAR(150) NOT NULL
)
INSERT INTO  Users VALUES (N'关二爷','123456',N'男',N'三国时代')
INSERT INTO  Users VALUES (N'刘备','123456',N'男',N'三国时代')
INSERT INTO  Users VALUES (N'张飞','123456',N'男',N'三国时代')
INSERT INTO  Users VALUES (N'吕布','123456',N'男',N'三国时代')
--产品表
CREATE  TABLE Product
(
ID INT PRIMARY KEY IDENTITY(1,1),
ProName NVARCHAR(50),
ProPrice MONEY ,
ProAddress NVARCHAR (50),
ProType INT     
)
INSERT INTO Product VALUES (N'诺基亚N9000',4623,N'安徽经销总店',1)
--产品类型表
CREATE TABLE ProType
(
ID INT  PRIMARY KEY IDENTITY (1,1),
TypeName NVARCHAR (50),    
)

--常见的系统存储过程
EXEC SP_DATABASES                  --列出所有服务器的数据库
EXEC SP_HELPDB                     --报告指定数据库或所有数据库的信息
EXEC SP_RENAMEDB LinqDB ,ReviewSql -- 改数据库名称
EXEC SP_TABLES                     --查看可查询对象的列表
EXEC SP_COLUMNS Users              --查询表列的信息
EXEC SP_HELP Users                 ---查询表的所有信息
EXEC SP_HELPCONSTRAINT Users       --查询表的约束
EXEC SP_HELPINDEX Users            --查询表的索引
EXEC SP_STORED_PROCEDURES Users    --查询当前环境所有存储过程
EXEC SP_HELPTEXT '参数'            --查询未加密的存储过程
                                 
   
/***********************自定义存储过程*******
注意:括号和引号可以不加,不等于是"<>",执行存储过程是传值勿等于 */                      

--添加用户信息(输出参数(引用传值))
CREATE PROCEDURE SP_AddTableUserProc
( 
@UserName VARCHAR(20),
@Pwd VARCHAR(20),
@Sex CHAR(2),
@Address VARCHAR(150),
@ResultID INT OUTPUT 
)
AS
BEGIN
      INSERT INTO Users VALUES (@UserName,@Pwd,@Sex,@Address)
      SELECT @ResultID = MAX(ID) FROM Users
      --SET @ResultID=@@ROWCOUNT
END                      

DECLARE @ResultID INT 
EXEC SP_AddTableUserProc N'关二爷','123456',N'男',N'三国时代',@ResultID OUTPUT
SELECT @ResultID

--添加产品信息(输入参数)
CREATE PROCEDURE SP_ProductAddProc
(
  @ProName NVARCHAR (50),
  @Price MONEY ,
  @ProAddress NVARCHAR(50),
  @ProType INT 
)
AS
BEGIN
    INSERT INTO dbo.Product VALUES (@ProName,@Price,@ProAddress,@ProType)
END
GO

/*********************储存过程与视图的联合简单使用********************/

--查询产品详细信息
CREATE PROCEDURE SP_GetProInfoProc
(
@TypeID INT
)
AS
BEGIN 
 SELECT p.ID,p.ProName,p.ProPrice,p.ProAddress,p.ProType,pt.TypeName FROM Product p   
 INNER JOIN ProType pt ON p.ProType=pt.ID WHERE P.ID =@TypeID
END

--产品信息视图
CREATE VIEW View_GetProInfo 
AS
 SELECT p.ID,p.ProName,p.ProPrice,p.ProAddress,p.ProType,pt.TypeName FROM Product p   
 INNER JOIN ProType pt ON p.ProType=pt.ID

--简单的存储过程与视图
CREATE PROCEDURE SP_GetProInfoProcByView
(
@TypeID INT,
@RowCount INT OUTPUT 
)
AS
BEGIN 
 SELECT *FROM View_GetProInfo WHERE ProType =@TypeID
 SET @RowCount=@@ROWCOUNT
END

DECLARE @RowCount INT
EXEC SP_GetProInfoProcByView 1 , @RowCount OUTPUT 
SELECT @RowCount

/*******************************储存过程与函数的联合使用***********************/

--基本系统函数
SELECT MAX (ID) FROM Users u
SELECT GETDATE(),GETUTCDATE()
SELECT DATEDIFF(YYYY,'1986-5-6',GETDATE())
SELECT DATENAME (MM,GETDATE())-- 获取字符串月份
SELECT DATEPART (MM,GETDATE ())

--自定义函数

--表值函数(注意dbo,RETURN 括号,无Begin end块)
CREATE FUNCTION Fun_GetProductInfoByProType
(
    @TypeID INT
)
RETURNS TABLE
AS
   RETURN
    (
    SELECT p.ID,p.ProName,p.ProPrice,p.ProAddress,p.ProType FROM Product p WHERE ProType =@TypeID
    )            
--调用
SELECT *FROM dbo .Fun_GetProductInfoByProType(1)


--标量函数(注意返回关键字)(注:可否用到动态加载QQ好友上,Group表与FriendRelation)
CREATE FUNCTION Fun_GetProductNameByID
(
    @TypeID INT 
)
RETURNS  NVARCHAR(50)
AS
BEGIN 
     DECLARE @TypeName NVARCHAR (50)
     SELECT  @TypeName= Typename FROM ProType WHERE ID =@TypeID
     RETURN  @TypeName
END
--调用
SELECT p.proName,p.ProPrice,p.ProAddress,p.ProType ,dbo.Fun_GetProductNameByID(ProType) AS TypeName  FROM Product p

--存储过程与函数的使用
CREATE PROCEDURE SP_GetProductInfo
AS
BEGIN
   --查询产品详细信息(注意思想)
   SELECT p.proName,p.ProPrice,p.ProAddress,p.ProType ,dbo.Fun_GetProductNameByID(ProType) AS TypeName FROM Product p
END
--调用存储过程
EXEC SP_GetProductInfo


/*****************************构造通用存储过程使用*****************************/

--通用删除功能
ALTER PROCEDURE SP_DelProductByCondition
(
@TableName NVARCHAR(50),
@Condition NVARCHAR(50)    
)
AS 
BEGIN 
    DECLARE @SqlText NVARCHAR(50)
    SET @SqlText ='DELETE FROM ' + @TableName +' WHERE '+ @Condition --(注意空格)
    PRINT @SqlText
    EXEC (@SqlText) --括号别忘了
END

EXEC dbo.SP_DelProductByCondition 'Product','ID =6'

--通用修改功能
ALTER PROCEDURE SP_UpdateTableByCondition
(
@TableName NVARCHAR(100),
@Condition NVARCHAR(100),
@Colunms   NVARCHAR(100)    
)
AS
BEGIN
    DECLARE @SqlText NVARCHAR(100)
    SET @SqlText = 'update ' +@TableName + ' set ' +@Colunms + ' WHERE ' +@Condition  --注意空格
    PRINT @SqlText
    EXEC (@SqlText)
END
EXEC dbo.SP_UpdateTableByCondition 'Product','ID=8','ProName =''不垃圾'' ' --注意双引号
                                                                                   
--通用查询功能
CREATE PROCEDURE SP_GetDataByTableName
(
@TableName NVARCHAR (100)    --表的名称
)
AS
BEGIN
  DECLARE @SqlText NVARCHAR(300)
  SET @SqlText ='SELECT *FROM ' + @TableName 
  PRINT @SqlText
  EXEC (@SqlText)
END

EXEC dbo.SP_GetDataByTableName'Product' --查看表信息
EXEC dbo.SP_GetDataByTableName 'dbo.View_GetProInfo' --参看产品信息视图
                                                     --
--通用查询改进
ALTER  PROCEDURE SP_GetColumnDataByTableName
(
 @TableName NVARCHAR (100),       --表的名称
 @Column    NVARCHAR(200),      --列名称
 @Condition NVARCHAR(100)       --条件
)
AS
BEGIN
  DECLARE @SqlText NVARCHAR(500)
  SET @SqlText ='SELECT ' + @Column +' from ' +@TableName +' where 1=1 ' +@Condition --(whrere 1=1是解决无参构造)
  PRINT @SqlText
  EXEC (@SqlText)
END

EXEC dbo.SP_GetColumnDataByTableName 'Product','*',''--表信息
EXEC dbo.SP_GetColumnDataByTableName 'Product','ID,Proname ,ProAddress','' --部分列查询
EXEC dbo.SP_GetColumnDataByTableName 'Product','proname ,ProAddress','and ID =1'--部分列于条件查询
                                                                               
/******************************存储过程构造通用分页**************************/

--列:查询Product表中第21条到30条的数据(30-21+1条数据,也就是第三页10条数据),注:Product表中ID为主键自增且不连续

--Top 写法
SELECT TOP (30-21+1) *FROM Product WHERE ID NOT IN (SELECT TOP (21-1) ID FROM Product);
 --排名函数写法
SELECT*FROM(SELECT ROW_NUMBER () OVER (ORDER BY ID ASC) AS RowNumber,p.ID ,p.ProName,p.ProPrice,p.ProAddress,p.ProType FROM Product p) AS TempTable
WHERE RowNumber BETWEEN 21 AND 30;
--公用表表达式写法
WITH Temp AS(SELECT ROW_NUMBER() OVER (ORDER BY ID ASC)AS RowNumber ,P.ID,P.ProName,P.ProPrice,P.ProAddress,P.ProType FROM Product p)
SELECT * FROM Temp WHERE RowNumber BETWEEN 21 AND 30;

--存储过程构造TOP 版分页
ALTER  PROCEDURE SP_GetDataByPageIndex
(
@TableName NVARCHAR(100), --表名称
@PageSize INT ,           --页大小
@PageIndex INT ,          --页索引
@PkColum NVARCHAR(100),   --表的ID主键
@Condition NVARCHAR(100)  --分页条件
)
AS
BEGIN
    DECLARE @SqlText NVARCHAR(500)
    SET @SqlText= 'select top ' +CONVERT(NVARCHAR(50),@PageSize) +'* from ' + @TableName+' Where ' +@PkColum + ' not in ( select top '
                  +CONVERT (NVARCHAR(50),@PageIndex*@PageSize) +' '+@PkColum +' from ' + @TableName+' where 1=1 '+ @Condition +')' +@Condition
    PRINT @SqlText
    EXEC (@SqlText)
END

EXEC dbo.SP_GetDataByPageIndex'Product',10,0,'ID','' --第1页，每页10条数据
EXEC dbo.SP_GetDataByPageIndex'Product',10,1,'ID','' --第2页
EXEC dbo.SP_GetDataByPageIndex'Product',10,2,'ID' ,''--'select top 10* from Product Where ID not in ( select top 20 ID from Product)与上面相同

--注意where 1=1 的微妙之处, select top 10* from Product Where ID not in ( select top 0 ID from Product where 1=1 and proType =1)and proType =1)
EXEC dbo.SP_GetDataByPageIndex'Product',10,0,'ID','and proType =1' --根据产品类型每页10条分页
   
                                                                   --
--存储过程构造排名函数版分页
Alter PROCEDURE SP_GetSampleDataByPageIndex
(
    @TableName VARCHAR (20),  --分页的表名
    @PkColumName VARCHAR(100),    --分页的列名
    @Sort  VARCHAR (4)='ASC', -- 排序方式    
    @PageIndex INT =1,        --显示的页码       
    @PageSize INT =10         --每一页显示条数
)
AS
BEGIN 
    DECLARE @PageSql VARCHAR (MAX)
    set @PageSql = 'SELECT *FROM (select ROW_NUMBER () over (order by '+@PkColumName+' '+@Sort+') as RowNumber, *from '+@TableName+') TempTable
    where RowNumber between '+CAST((@PageIndex-1)*@PageSize+1 AS VARCHAR(20))+' and '+CONVERT(VARCHAR(20),@PageIndex*@PageSize)
    PRINT @PageSql
    EXEC (@PageSql) 
END

--SELECT *FROM (select ROW_NUMBER () over (order by ID ASC) as RowID, *from Product) NewTable where RowID between 1 and  10 
EXEC dbo.SP_GetSampleDataByPageIndex  'Product','ID','ASC',1,10 --第一页10条数据

--改进与总结排名函数版分页

--原理图
       PageIndex    PageSize   (PageIndex-1)*PageSize  between((PageIndex-1)*PageSize)  and (PageIndex*PageSize)
         1            10          (1-1)*10 =0                    00                                10
         2            10          (2-1)*10 =10                   10                                20
         3            10          (3-1)*10 =20                   20                                30

Alter PROCEDURE SP_GetProDataByPageIndex
(
    @TableName VARCHAR (20),  --分页的表名
    @PkColumName VARCHAR(100),--分页的列名
    @Sort VARCHAR (4)='ASC', --排序方式    
    @PageIndex INT =1,        --显示的页码       
    @PageSize INT =10,        --每一页显示条数
    @Condition NVARCHAR(100)  --分页条件
)
AS
BEGIN 
    DECLARE @PageSql VARCHAR (MAX)
    set @PageSql = 'SELECT *FROM (select ROW_NUMBER () over (order by '+@PkColumName+' '+@Sort+') as RowNumber, *from '+@TableName+') TempTable
    where RowNumber between '+CAST((@PageIndex-1)*@PageSize+1 AS VARCHAR(20))+' and '+CONVERT(VARCHAR(20),@PageIndex*@PageSize )+' '+@Condition
    PRINT @PageSql
    EXEC (@PageSql) 
END
EXEC dbo .SP_GetProDataByPageIndex 'Product','ID','ASC','1','10','' --无条件分页第一页
EXEC dbo .SP_GetProDataByPageIndex 'Product','ID','ASC','2','10','' --无条件分页第二页
--SELECT *FROM (select ROW_NUMBER () over (order by ID ASC) as RowNumber, *from Product) TempTable where RowNumber between 1 and 10   
EXEC dbo .SP_GetProDataByPageIndex 'Product','ID','ASC','1','10','and ProType = 1'--根据产品类型分页


--有一个这样的字符串 1Q,1T,1Q,1m,1m,1m,1Q,1P,2T,1R
--怎样显示结果为3Q+3T+3m+1P+1R
create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as 

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)


while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return 
end
go

--调用

declare @s varchar(1000)

set @s='1Q,1T,1Q,1m,1m,1m,1Q,1P,2T,1R'

select sum(cast(left(a,1) as int)) c1 , right(a,1) c2 into tmp from dbo.fn_split(@s,',')  group by right(a,1)

go

declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + cast(t.c1 as varchar) + cast(t.c2 as varchar) + '+' from (select c1,c2 from tmp) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)

drop function dbo.fn_split 
drop table tmp

/*
result         
-------------- 
3m+1P+3Q+1R+3T
*/

-- 存储过程 xml 开始
--查询XML节点 value：通过nodes 指定到节点通过Value属性取出值
Declare @Xml xml
set @Xml='<Employee><ID>1</ID><ID>2</ID></Employee>'

SELECT  ID.value('.','Nvarchar(500)') as EmployeeID
    FROM @Xml.nodes('Employee/ID') Employee(ID)  
    
    
Declare @Xml xml
set @Xml='<Employee><EmployeeID ID="1" /><EmployeeID ID="2" /><EmployeeID ID="3" /></Employee>'
  
--查询XML 多节点的值，可以通过子查询实现：              
SELECT  EmployeeID.value('./@ID','Nvarchar(500)') as ReportColumnID
   FROM @Xml.nodes('/Employee/EmployeeID') 
   X(EmployeeID)

--查询XML 多节点的值，可以通过子查询实现：
Declare @Xml xml
Set @Xml = '
<X>
  <T><ID>1</ID><NAME>A1</NAME></T>
  <T><ID>2</ID><NAME>B2</NAME></T>
  <T><ID>3</ID><NAME>C3</NAME></T>
</X>'
SELECT  ID.value('.', 'NVARCHAR(100)') As ID,NAME.value('.','NVARCHAR(100)') As NAME
FROM 
(
    Select  
        T.C.query('ID') As ID, 
        T.C.query('NAME') As NAME
    From 
        @Xml.nodes('/X/T') As T(C)
)BT

--OPENXML 查询方式：

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot" />
</ROOT>'
---创建文档内部格式
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

--OPENXML 查询方式：

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
</ROOT>'
---创建文档内部格式
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')
--存储过程 xml 结束