1.进行多表查询,看用户的信息
CREATE PROCEDURE SP_CLUB_GOMYCLUB
@user_number int,
@user_id varchar(20)
AS
BEGIN
--设置存储过程的脱离时间
SET LOCK_TIMEOUT 1800
BEGIN TRANSACTION
SELECT club_code,club_name FROM club_common_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id =@user_id
UNION
SELECT club_code,club_name FROM club_game_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
UNION
SELECT club_code,club_name FROM club_favor_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
GO
2.分布式事务处理的存储过程
CREATE PROCEDURE RWA_EX_GIVEAPOINTEX
@Uid INT,
@User_name VARCHAR(20),
@User_APoint INT,
@out_check VARCHAR(10) OUTPUT
AS
SET LOCK_TIMEOUT 1500
SET ANSI_WARNINGS ON
SET ANSI_NULLS OFF
--SET @USER_ADD = @APOINT
IF DATALENGTH(@User_name) < 4
BEGIN
SET @out_check = '4BYTE'
RETURN
END
IF DATALENGTH(@User_name) > 20
BEGIN
SET @out_check = '20BYTE'
RETURN
END
--开始分布式事务处理
BEGIN DISTRIBUTED TRANSACTION
--查找玩家现有的APOINT值
--SELECT @U_Point = user_point FROM USER_ITEM WITH (NOLOCK) WHERE user_number = @User_id and user_id = @User_Uname
DECLARE @u_point INT
DECLARE @U_TempPoint INT
DECLARE @PROC_NUMBER int
set @u_point = 0
select @u_point = user_point from USER_ITEM WITH (NOLOCK) where user_number = @Uid and user_id = @User_name
SET @U_TempPoint = @u_point + @User_APoint
--print @User_APoint
IF @@ERROR <> 0
BEGIN
SET @out_check = 'PROFILE'
ROLLBACK TRANSACTION
RETURN
END
--追加新的橘宝石
SELECT @PROC_NUMBER = @Uid
UPDATE USER_ITEM SET USER_POINT = @U_TempPoint WHERE USER_NUMBER = @Uid AND USER_ID = @User_name
IF @@ERROR <> 0
BEGIN
SET @out_check = 'ITEM'
ROLLBACK TRANSACTION
RETURN
END
SET XACT_ABORT ON
--写入LOG文件
EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @PROC_NUMBER, @User_name, 1,3, @U_TempPoint, '',0,''
IF @@ERROR<>0 AND @@rowcount <> 1
BEGIN
SET @out_check = 6
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
SET @out_check = 'OK'
COMMIT TRANSACTION
RETURN
END
GO
3.在服务器端使用游标的实例
CREATE PROCEDURE SP_CLUB_COUNT_UPDATE
AS
DECLARE @CLUB_CODE INT,@Club_L_Index INT,@UPDATE_COUNT INT,@CLUB_COUNT INT
SET @CLUB_CODE = 0
SET @Club_L_Index = 0
SET @UPDATE_COUNT = 0
SET @CLUB_COUNT = 0
DECLARE CUR_ACC Cursor FORWARD_ONLY READ_ONLY For
SELECT CLUB_L_INDEX,CLUB_CODE,CLUB_COUNT FROM CLUB_DATA WITH(NOLOCK)
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @Club_L_Index = 2
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_COMMON_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
UPDATE CLUB_DATA SET CLUB_COUNT = @UPDATE_COUNT WHERE CLUB_CODE = @CLUB_CODE
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT @CLUB_COUNT
PRINT @UPDATE_COUNT
END
END
ELSE IF @Club_L_Index =1
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_GAME_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
ELSE IF @Club_L_Index =3
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_FAVOR_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC
GO
4.对时间类型的统计方法,按年,按月,按日
SELECT COUNT(user_number) AS Expr1,datepart(dd,user_login_time)
-- convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
--where datediff(mm,user_login_time,user_login_time)=0 and datediff(mm,user_login_time,user_login_time)=0
GROUP BY datepart(dd,user_login_time)--,datepart(dayofyear,user_login_time)
--having
方法2
SELECT COUNT(user_number) AS Expr2,
convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
GROUP BY convert(char(10), user_login_time, 120)
月:
SELECT COUNT(user_number) AS Expr2,
convert(char(7), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(7), user_login_time, 120)
年:
SELECT COUNT(user_number) AS Expr2,
convert(char(4), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(4), user_login_time, 120)
5.关于杀死进程的存储过程调用方法
sp_who [[@login_name =] 'login']
KILL {spid | UOW} [WITH STATUSONLY]
6.关于如何实现不定表名的查询
SET @sql = 'SELECT @proc_login = USER_STATE FROM RAS_ACCESS_'+ @Service_SN + ' WHERE USER_NUMBER = ' + cast(@user_number as varchar) + ' and user_id = ''' + @user_id + ''' '
SET @Parmdefinition = '@proc_login int OUTPUT'
EXECUTE sp_executesql @sql, @ParmDefinition, @proc_login = @proc_login OUTPUT
CREATE PROCEDURE User_Level_Validate
@User_id as int,
@User_Name as varchar(20),
@User_GameCode varchar(2),
@Results as varchar(20) OUTPUT
AS
--开始执行分布式查询
BEGIN DISTRIBUTED TRANSACTION
DECLARE @User_GamePoint as bigint
DECLARE @User_Level as int
DECLARE @User_Pev as bigint
DECLARE @User_Next as bigint
DECLARE @SQL as nvarchar(500)
--查询玩家在游戏中的内容信息
SET @SQL = N'SELECT @User_GamePoint = Game_P_Point,@User_Level = Game_P_Level from Game_P_' + @User_GameCode + ' where Game_User_Number = ' + cast(@User_id as varchar(10)) + ' and Game_P_Id = ''' + @User_Name + ''''
EXECUTE sp_executesql @SQL,N'@User_GamePoint bigint OUTPUT,@User_Level int OUTPUT',@User_GamePoint=@User_GamePoint output,@User_Level = @User_Level output
IF @@ROWCOUNT <> 1 OR @@ERROR <> 0
BEGIN
SET @Results = 'Select Error'
ROLLBACK TRANSACTION
RETURN
END
--如果玩家是0级 退出
IF @User_Level <= 0
BEGIN
SET @Results = 'User Level 0'
ROLLBACK TRANSACTION
RETURN
END
ELSE
--开始计算点数
BEGIN
WHILE (@User_Level > 0)
BEGIN
SELECT @User_Pev = buy_money,@User_Next = sell_money FROM GAME_GRADE WHERE grade_num = @User_Level
IF @@error<>0 or @@rowcount<>1
BEGIN
SET @Results = 'GRADE Error'
ROLLBACK TRANSACTION
RETURN
END
SET @User_GamePoint = @User_GamePoint - @User_Next + @User_Pev
IF @User_GamePoint <= 0
BEGIN
SET @Results = 'Point is 0'
ROLLBACK TRANSACTION
RETURN
END
SET @User_Level = @User_Level - 1
--SET XACT_ABORT ON
--写入LOG文件
--EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,53, @User_Next, '',0,''
--EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,52, @User_Pev, '',0,''
END
END
SET @Results = 'Ok'
COMMIT TRANSACTION
GO
7.SQLSERVER的反向匹配
CREATE PROCEDURE DirtyValidate
@User_text varchar(20),
@Results varchar(20) OUTPUT
AS
--启动事务处理
BEGIN TRANSACTION
DECLARE @Str_Temp varchar(20)
DECLARE @Int_Row as int
SELECT dname FROM dirtyname WHERE @User_text Like '%' + dname + '%'
SET @Int_Row = @@rowcount
IF @@error<>0
BEGIN
SET @Results ='Select Error'
ROLLBACK TRANSACTION
RETURN
END
IF @Int_Row > 0
BEGIN
SET @Results ='YES'
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @Results ='NO'
COMMIT TRANSACTION
END
--声明一个服务器端游标
/*DECLARE CUR_ACC CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT dname FROM dirtyname
IF @@error<>0
BEGIN
SET @Results ='Select Error'
ROLLBACK TRANSACTION
RETURN
END
--开始使用游标遍历数据库
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @Str_Temp
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @@ERROR <> 0
BEGIN
SET @Results ='While Error'
ROLLBACK TRANSACTION
END
ELSE
--开始比较
BEGIN
--IF SUBSTRING(@Str_Temp,)
COMMIT TRANSACTION
END
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC */
GO
--关于存储过程控制的翻页程序
SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR(10), @pSize) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE, Item.RCI_Name '
+ 'FROM (SELECT TOP ' + CONVERT(VARCHAR(10), (@tRecordsCount-(@page-1)*@pSize)) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE '
+ 'FROM ' + @table + ' '
CREATE PROCEDURE SP_CLUB_GOMYCLUB
@user_number int,
@user_id varchar(20)
AS
BEGIN
--设置存储过程的脱离时间
SET LOCK_TIMEOUT 1800
BEGIN TRANSACTION
SELECT club_code,club_name FROM club_common_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id =@user_id
UNION
SELECT club_code,club_name FROM club_game_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
UNION
SELECT club_code,club_name FROM club_favor_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
GO
2.分布式事务处理的存储过程
CREATE PROCEDURE RWA_EX_GIVEAPOINTEX
@Uid INT,
@User_name VARCHAR(20),
@User_APoint INT,
@out_check VARCHAR(10) OUTPUT
AS
SET LOCK_TIMEOUT 1500
SET ANSI_WARNINGS ON
SET ANSI_NULLS OFF
--SET @USER_ADD = @APOINT
IF DATALENGTH(@User_name) < 4
BEGIN
SET @out_check = '4BYTE'
RETURN
END
IF DATALENGTH(@User_name) > 20
BEGIN
SET @out_check = '20BYTE'
RETURN
END
--开始分布式事务处理
BEGIN DISTRIBUTED TRANSACTION
--查找玩家现有的APOINT值
--SELECT @U_Point = user_point FROM USER_ITEM WITH (NOLOCK) WHERE user_number = @User_id and user_id = @User_Uname
DECLARE @u_point INT
DECLARE @U_TempPoint INT
DECLARE @PROC_NUMBER int
set @u_point = 0
select @u_point = user_point from USER_ITEM WITH (NOLOCK) where user_number = @Uid and user_id = @User_name
SET @U_TempPoint = @u_point + @User_APoint
--print @User_APoint
IF @@ERROR <> 0
BEGIN
SET @out_check = 'PROFILE'
ROLLBACK TRANSACTION
RETURN
END
--追加新的橘宝石
SELECT @PROC_NUMBER = @Uid
UPDATE USER_ITEM SET USER_POINT = @U_TempPoint WHERE USER_NUMBER = @Uid AND USER_ID = @User_name
IF @@ERROR <> 0
BEGIN
SET @out_check = 'ITEM'
ROLLBACK TRANSACTION
RETURN
END
SET XACT_ABORT ON
--写入LOG文件
EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @PROC_NUMBER, @User_name, 1,3, @U_TempPoint, '',0,''
IF @@ERROR<>0 AND @@rowcount <> 1
BEGIN
SET @out_check = 6
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
SET @out_check = 'OK'
COMMIT TRANSACTION
RETURN
END
GO
3.在服务器端使用游标的实例
CREATE PROCEDURE SP_CLUB_COUNT_UPDATE
AS
DECLARE @CLUB_CODE INT,@Club_L_Index INT,@UPDATE_COUNT INT,@CLUB_COUNT INT
SET @CLUB_CODE = 0
SET @Club_L_Index = 0
SET @UPDATE_COUNT = 0
SET @CLUB_COUNT = 0
DECLARE CUR_ACC Cursor FORWARD_ONLY READ_ONLY For
SELECT CLUB_L_INDEX,CLUB_CODE,CLUB_COUNT FROM CLUB_DATA WITH(NOLOCK)
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF @Club_L_Index = 2
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_COMMON_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
UPDATE CLUB_DATA SET CLUB_COUNT = @UPDATE_COUNT WHERE CLUB_CODE = @CLUB_CODE
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT @CLUB_COUNT
PRINT @UPDATE_COUNT
END
END
ELSE IF @Club_L_Index =1
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_GAME_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
ELSE IF @Club_L_Index =3
BEGIN
BEGIN TRANSACTION
SELECT @UPDATE_COUNT = COUNT(CLUB_CODE) FROM CLUB_FAVOR_MEMBER with(nolock)
WHERE CLUB_CODE = @CLUB_CODE AND CLUB_MEM_LEVEL <4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
FETCH NEXT FROM CUR_ACC INTO @CLUB_L_INDEX,@CLUB_CODE,@CLUB_COUNT
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC
GO
4.对时间类型的统计方法,按年,按月,按日
SELECT COUNT(user_number) AS Expr1,datepart(dd,user_login_time)
-- convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
--where datediff(mm,user_login_time,user_login_time)=0 and datediff(mm,user_login_time,user_login_time)=0
GROUP BY datepart(dd,user_login_time)--,datepart(dayofyear,user_login_time)
--having
方法2
SELECT COUNT(user_number) AS Expr2,
convert(char(10), user_login_time, 120) AS Expr1
FROM RAS_ACCESS_130032
GROUP BY convert(char(10), user_login_time, 120)
月:
SELECT COUNT(user_number) AS Expr2,
convert(char(7), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(7), user_login_time, 120)
年:
SELECT COUNT(user_number) AS Expr2,
convert(char(4), user_login_time, 120) AS Expr1
FROM table
GROUP BY convert(char(4), user_login_time, 120)
5.关于杀死进程的存储过程调用方法
sp_who [[@login_name =] 'login']
KILL {spid | UOW} [WITH STATUSONLY]
6.关于如何实现不定表名的查询
SET @sql = 'SELECT @proc_login = USER_STATE FROM RAS_ACCESS_'+ @Service_SN + ' WHERE USER_NUMBER = ' + cast(@user_number as varchar) + ' and user_id = ''' + @user_id + ''' '
SET @Parmdefinition = '@proc_login int OUTPUT'
EXECUTE sp_executesql @sql, @ParmDefinition, @proc_login = @proc_login OUTPUT
CREATE PROCEDURE User_Level_Validate
@User_id as int,
@User_Name as varchar(20),
@User_GameCode varchar(2),
@Results as varchar(20) OUTPUT
AS
--开始执行分布式查询
BEGIN DISTRIBUTED TRANSACTION
DECLARE @User_GamePoint as bigint
DECLARE @User_Level as int
DECLARE @User_Pev as bigint
DECLARE @User_Next as bigint
DECLARE @SQL as nvarchar(500)
--查询玩家在游戏中的内容信息
SET @SQL = N'SELECT @User_GamePoint = Game_P_Point,@User_Level = Game_P_Level from Game_P_' + @User_GameCode + ' where Game_User_Number = ' + cast(@User_id as varchar(10)) + ' and Game_P_Id = ''' + @User_Name + ''''
EXECUTE sp_executesql @SQL,N'@User_GamePoint bigint OUTPUT,@User_Level int OUTPUT',@User_GamePoint=@User_GamePoint output,@User_Level = @User_Level output
IF @@ROWCOUNT <> 1 OR @@ERROR <> 0
BEGIN
SET @Results = 'Select Error'
ROLLBACK TRANSACTION
RETURN
END
--如果玩家是0级 退出
IF @User_Level <= 0
BEGIN
SET @Results = 'User Level 0'
ROLLBACK TRANSACTION
RETURN
END
ELSE
--开始计算点数
BEGIN
WHILE (@User_Level > 0)
BEGIN
SELECT @User_Pev = buy_money,@User_Next = sell_money FROM GAME_GRADE WHERE grade_num = @User_Level
IF @@error<>0 or @@rowcount<>1
BEGIN
SET @Results = 'GRADE Error'
ROLLBACK TRANSACTION
RETURN
END
SET @User_GamePoint = @User_GamePoint - @User_Next + @User_Pev
IF @User_GamePoint <= 0
BEGIN
SET @Results = 'Point is 0'
ROLLBACK TRANSACTION
RETURN
END
SET @User_Level = @User_Level - 1
--SET XACT_ABORT ON
--写入LOG文件
--EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,53, @User_Next, '',0,''
--EXEC G_CRM.JOYON_CRM.avgama.LOG_CREDIT_EX @User_id, @User_Name, 2,52, @User_Pev, '',0,''
END
END
SET @Results = 'Ok'
COMMIT TRANSACTION
GO
7.SQLSERVER的反向匹配
CREATE PROCEDURE DirtyValidate
@User_text varchar(20),
@Results varchar(20) OUTPUT
AS
--启动事务处理
BEGIN TRANSACTION
DECLARE @Str_Temp varchar(20)
DECLARE @Int_Row as int
SELECT dname FROM dirtyname WHERE @User_text Like '%' + dname + '%'
SET @Int_Row = @@rowcount
IF @@error<>0
BEGIN
SET @Results ='Select Error'
ROLLBACK TRANSACTION
RETURN
END
IF @Int_Row > 0
BEGIN
SET @Results ='YES'
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @Results ='NO'
COMMIT TRANSACTION
END
--声明一个服务器端游标
/*DECLARE CUR_ACC CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT dname FROM dirtyname
IF @@error<>0
BEGIN
SET @Results ='Select Error'
ROLLBACK TRANSACTION
RETURN
END
--开始使用游标遍历数据库
OPEN CUR_ACC
FETCH NEXT FROM CUR_ACC INTO @Str_Temp
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @@ERROR <> 0
BEGIN
SET @Results ='While Error'
ROLLBACK TRANSACTION
END
ELSE
--开始比较
BEGIN
--IF SUBSTRING(@Str_Temp,)
COMMIT TRANSACTION
END
END
CLOSE CUR_ACC
DEALLOCATE CUR_ACC */
GO
--关于存储过程控制的翻页程序
SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR(10), @pSize) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE, Item.RCI_Name '
+ 'FROM (SELECT TOP ' + CONVERT(VARCHAR(10), (@tRecordsCount-(@page-1)*@pSize)) + ' RCH_SEQ,RCH_ITEMCODE,RCH_MONEY,RCH_GAME_POINT,USER_NUMBER,USER_ID,RCH_STATE,RCH_RS_DATE,RCH_RQ_DATE '
+ 'FROM ' + @table + ' '