复习储存过程

现在的位置: 首页 > 综合 > 正文

复习储存过程

2012年12月11日 ⁄ 综合 ⁄ 共 9163字 ⁄ 字号小中大 ⁄ 评论关闭

USE ReviewSql
--用户表
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'--根据产品类型分页

【上篇】oracle远程连接服务器出现 ORA-12170 TNS:连接超时解决办法
【下篇】使用自定义Effect让指定颜色透明

作者: roost

该日志由 roost 于11年前发表在综合分类下，最后更新于 2012年12月11日.
转载请注明: 复习储存过程 | 学步园 +复制链接

抱歉!评论已关闭.

返回首页

（其他合作也可洽谈）

必威体育

必威电竞

学步园