Heim > Datenbank > MySQL-Tutorial > Hauptteil

Sehr häufig besuchte Kommentarsystem-Datenbank mit gespeicherter Prozedurarchitektur

大家讲道理
Freigeben: 2016-11-10 11:16:57
Original
1120 Leute haben es durchsucht

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[CommentsTables](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Key] [nvarchar](50) NOT NULL,
    [TableName] [nvarchar](80) NOT NULL,
    [StartID] [int] NOT NULL,
    [EndID] [int] NOT NULL,
 CONSTRAINT [PK_SysTables] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
 
 
 
 
--根据SourceID和key获得表名
create function [dbo].[funGetTableName]
(
@SourceID int,
@Key nvarchar(50)
)
RETURNS nvarchar(80)
as
begin
    declare @tableName nvarchar(80);
    declare @tableArea int;
    declare @mod int;
     
    declare @Size int;
    set @Size = 1000;
     
    set @mod = @SourceID % @Size;
    if @mod > 0 
        set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;        
    else   
        set @tableArea = Cast((@SourceID-1) / @Size as int);
             
             
    set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
     
    return @tableName
end
GO
 
 
 
 
 
 
 
--评论写入调用存储过程
 
CREATE proc [dbo].[procAddComment]
(
@ParentID int,
@SourceID int,
@NickName nvarchar(20),
@Content nvarchar(300),
@IP nvarchar(30),
@City nvarchar(30),
@BeFiltered bit,
@Disable bit,
@Key nvarchar(50),
@InsertedID int Output
)
as
begin
    declare @tableName nvarchar(80);
    declare @tableArea int;
    declare @mod int;
     
    declare @Size int;
    set @Size = 1000;
     
    set @mod = @SourceID % @Size;
    if @mod > 0 
        set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;        
    else   
        set @tableArea = Cast((@SourceID-1) / @Size as int);
         
 
 
     
    set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
     
    if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName) 
    begin
     
        declare @StartID int;
        declare @EndID int;
         
        set @EndID = @tableArea * @Size;
        set @StartID = @EndID - (@Size-1);
     
     
        --创建表
        declare @CreateSQL nvarchar(MAX);
        set @CreateSQL = 
        'Create table [dbo].['+@tableName+'](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [ParentID] [int] NOT NULL,
        [SourceID] [int] NOT NULL,
        [NickName] [nvarchar](20) NOT NULL,
        [Content] [nvarchar](300) NOT NULL,
        [Datetime] [datetime] NOT NULL,
        [IP] [nvarchar](30) NOT NULL,
        [City] [nvarchar](30) NOT NULL,
        [BeFiltered] [bit] NOT NULL,
        [Disable] [bit] NOT NULL,
        [Lou] [int] NOT NULL,
        [Ding] [int] NOT NULL,
        [Cai] [int] NOT NULL,
         CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED 
        (
            [ID] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]'
         
         
        EXEC(@CreateSQL);
         
         
        --创建索引 ID DESC
        EXEC('      
        CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+'] 
        (
            [ID] DESC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
         
        --创建索引 Ding DESC
        EXEC('
        CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+'] 
        (
            [Ding] DESC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
         
        --创建索引 SourceID DESC
        EXEC('
        CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+'] 
        (
            [SourceID] DESC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
 
        --创建索引 Lou DESC
        EXEC('
        CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Lou_DESC] ON [dbo].['+@tableName+'] 
        (
            [Lou] DESC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]')
         
         
        --创建默认值
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_ParentID]  DEFAULT ((0)) FOR [ParentID]');
         
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Datetime]  DEFAULT (getdate()) FOR [Datetime]');
         
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_BeFiltered]  DEFAULT ((0)) FOR [BeFiltered]');  
 
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Disable]  DEFAULT ((0)) FOR [Disable]');
     
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Lou]  DEFAULT ((1)) FOR [Lou]');
             
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Ding]  DEFAULT ((0)) FOR [Ding]');
         
        EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Cai]  DEFAULT ((0)) FOR [Cai]');
         
        Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
    end
 
     
    declare @TemLou int;
    declare @SQL nvarchar(MAX);
    set @SQL = N'select @TemLou = Count(ID) from dbo.['+@tableName+N'] where SourceID=@SourceID';
     
    exec sp_executesql @SQL,
    N'@SourceID int,@TemLou int output',
    @SourceID,
    @TemLou output;
     
    if @TemLou = 0
        set @TemLou = 1;
    else
        set @TemLou = @TemLou + 1;
     
     
     
    declare @Lou int;
    set @Lou = @TemLou;
     
    declare @InsertSQL nvarchar(MAX);
    set @InsertSQL = N'Insert Into dbo.['+@tableName+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou])
    values (@ParentID,@SourceID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();';
     
    exec sp_executesql @InsertSQL,
    N'@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Lou int,@InsertedID int output',
    @ParentID,
    @SourceID,
    @NickName,
    @Content,
    @IP,
    @City,
    @BeFiltered,
    @Disable,
    @Lou,
    @InsertedID output;
end
 
 
 
 
 
 
 
 
GO
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
--获得最新评论存储过程
 
CREATE proc [dbo].[procGetNewComments]
(
@SourceID int,
@Key nvarchar(50),
@PageIndex int,
@PageSize int,
@Fields nvarchar(100),
@PageCount int output
)
as
begin
    declare @tableName nvarchar(80);
    set @tableName = dbo.funGetTableName(@SourceID,@Key);
    declare @Rc int;    
     
    declare @SQL nvarchar(MAX);
    set @SQL = N'select @Rc = COUNT(ID) from dbo.['+@tableName+N'] where SourceID = @SourceID'; 
     
    exec sp_executesql @SQL,
    N'@SourceID int,@Rc int output',
    @SourceID,
    @Rc output;
     
    if @Rc % @PageSize > 0 
        set @PageCount = Cast(@Rc / @PageSize as int) + 1;
    else
        set @PageCount = Cast(@Rc / @PageSize as int);
         
 
     
    if @PageIndex = 1 
        begin
            set @SQL = N'select top '+Cast(@PageSize as nvarchar(30))+' '+@Fields + N' from dbo.['+@tableName+N'] where SourceID=@SourceID order by Lou desc';                  
            exec sp_executesql @SQL,
            N'@SourceID int',
            @SourceID;
        end        
    else
        begin
            declare @StartLou int;
            declare @EndLou int;
             
            --1 20  1 - 20,21- 40,41-60
            set @EndLou =  @Rc - (@PageIndex-1) * @PageSize;
             
            if @EndLou > @Rc 
                set @EndLou  = @Rc;         
             
            set @StartLou = @EndLou - @PageSize + 1;
             
            if @StartLou < 1 
                set @StartLou = 1;
             
             
            set @SQL = N&#39;select &#39;+@Fields + N&#39; from dbo.[&#39;+@tableName+N&#39;] where Lou>=@StartLou and Lou<=@EndLou and SourceID = @SourceID order by Lou desc&#39;;
             
             
            exec sp_executesql @SQL,
            N&#39;@SourceID int,@StartLou int,@EndLou int&#39;,
            @SourceID,@StartLou,@EndLou;
        end
     
end
 
 
 
 
GO
 
 
 
 
 
 
 
 
 
 
 
--踩
 
Create proc [dbo].[procCai]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
    declare @tableName nvarchar(80);
    set @tableName = dbo.funGetTableName(@SourceID,@key);
     
    declare @SQL nvarchar(MAX);
    set @SQL = N&#39;update dbo.[&#39;+@tableName+N&#39;] set Cai=Cai+1 where ID=@ID;select @Times=Cai from dbo.[&#39;+@tableName+N&#39;] where ID=@ID&#39;;
     
     
    exec sp_executesql @SQL,
    N&#39;@ID int,@Times int output&#39;,
    @ID,
    @Times output;
end
 
 
GO
 
 
--顶
create proc [dbo].[procDing]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
    declare @tableName nvarchar(80);
    set @tableName = dbo.funGetTableName(@SourceID,@key);
     
    declare @SQL nvarchar(MAX);
    set @SQL = N&#39;update dbo.[&#39;+@tableName+N&#39;] set Ding=Ding+1 where ID=@ID;select @Times=Ding from dbo.[&#39;+@tableName+N&#39;] where ID=@ID&#39;;
     
     
    exec sp_executesql @SQL,
    N&#39;@ID int,@Times int output&#39;,
    @ID,
    @Times output;
end
 
GO
Nach dem Login kopieren

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage