Sqlserver 存储过程
来源:互联网 发布:win7网吧系统优化 编辑:程序博客网 时间:2024/06/08 00:59
1、功能描述: 有一个名叫OGG_EVENT的表,里面有个一段event_txt里面记录了ddl操作的文本,我们需要用一个存储过程来执行里面的ddl, 然后将EVENT_IMP设置为‘Y',表示已经执行了
OGG_EVNT表的定义
存储过程的概要
存储过程的内容:
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[execAllOGGDDL] Script Date: 09/30/2015 14:45:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[execAllOGGDDL]
AS
BEGIN
DECLARE @EVT_ID VARCHAR(10)
DECLARE @EVT_ITM BIGINT
DECLARE CUR CURSOR LOCAL FOR
SELECT EVENT_ID, EVENT_ITM FROM OGG_EVENT WHERE EVENT_IMP='N' and EVENT_TXT <> 'END EVENT' order by EVENT_ID , EVENT_ITM
-- 打开游标
OPEN CUR
FETCH NEXT FROM CUR INTO @EVT_ID, @EVT_ITM
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE OGG_EVENT SET EVENT_IMP='P' WHERE EVENT_ID=@EVT_ID AND EVENT_ITM=@EVT_ITM
PRINT N'UPDATE OGG_EVENT SET EVENT_IMP=P WHERE EVENT_ID='+@EVT_ID +N' EVENT_ITM=' +NCHAR(@EVT_ITM) +N'.'
EXEC execOGGDDL @EVT_ID, @EVT_ITM
FETCH NEXT FROM CUR INTO @EVT_ID,@EVT_ITM
END
-- 关闭游标
CLOSE CUR
-- 释放游标
DEALLOCATE CUR
END
GO
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[execOGGDDL] Script Date: 09/30/2015 14:46:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[execOGGDDL] @evtId_ nvarchar(10), @evtItm_ bigint
AS BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE @evtTxt nvarchar(max)
DECLARE @evtId nvarchar(10)
DECLARE @evtItm bigint
set @evtId = @evtId_
set @evtItm = @evtItm_
-- Test for any Events to process
IF (SELECT COUNT (*) FROM dbo.OGG_EVENT WHERE EVENT_ID = @evtId AND EVENT_ITM = @evtItm
AND UPPER(EVENT_IMP) = N'P') = 0
PRINT N'There are no Events to process for EventID= '+ @evtId +N' EventITM='+NCHAR(@evtItm)+N'.'
-- Start looping through the records
WHILE (SELECT COUNT (*) FROM dbo.OGG_EVENT WHERE EVENT_ID = @evtId AND EVENT_ITM=@evtItm
AND UPPER(EVENT_IMP) = N'P') > 0
BEGIN
-- Grab the first record out
SELECT @evtTxt = EVENT_TXT FROM dbo.OGG_EVENT WHERE EVENT_ID =@evtId AND EVENT_ITM=@evtItm
AND EVENT_ITM = @evtItm AND UPPER(EVENT_IMP) = N'P'
-- execute the statements
IF @evtTxt != N'END EVENT'
exec(@evtTxt)
-- Update the record to set the EVENT_IMP to 'Y'
UPDATE dbo.OGG_EVENT SET EVENT_IMP = N'Y', EVENT_IMP_DATE =
GETDATE() WHERE EVENT_ID = @evtId AND EVENT_ITM = @evtItm
END END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
特别说明:这不能完全说是我写的,开始是熊老师写的,然后我发现不能适合需求,在他的基础上改的
执行存储过程dbo.execALLOGGDDL之前表dbo.ogg_event的内容
执行存储过程: exec dbo.execALLOGGDDL
执行完之后
- 分页 SQLServer存储过程
- 分页 SQLServer存储过程
- SqlServer分页存储过程
- sqlserver存储过程
- 分页 SQLServer存储过程
- SQLserver 存储过程入门
- 分页 SQLServer存储过程
- sqlserver 存储过程 语法
- 【SQLSERVER】存储过程基础
- SQLServer 分页存储过程
- 【SQLSERVER】存储过程基础
- SQLSERVER 存储过程 语法
- SQLSERVER存储过程基础
- 分页SQLServer存储过程
- SQLSERVER存储过程解密
- SQLSERVER存储过程基础
- 【SQLSERVER】存储过程基础
- SQLServer存储过程分页
- HDOJ 题目5446 Unknown Treasure(Lucas+费马小定理+CRT)
- Python解析json文件报错:'utf8' codec can't decode byte 0xbb in position 0: invalid start byte
- 25个SSH命令收藏备用
- linux wc命令
- Linux下md5sum 校验文件完整性
- Sqlserver 存储过程
- OFBIZ webservice简介
- Android:通过自定义ImageView实现带边框的ImageView
- zigbee学习之睡眠定时器
- UIView 中常见的方法总结
- error LNK2026: 模块对于 SAFESEH 映像是不安全的
- protobufのjava应用
- java list转树形
- JSON 转成 C# 动态类