|
Sybase
|
|
|
|
|
Продукты
|
|
|
|
|
Сообщества
|
|
|
|
|
Меню
|
|
|
|
|
Вход
|
|
|
|
|
Рассылка
|
|
|
|
|
Статистика
|
|
|
|
|
Сброс счетчика таблиц(ы) на близжайшее свободное
|
Сброс счетчика таблиц(ы) на близжайшее свободноеCREATE PROCEDURE "DBA"."sp_asc_ResetIdentity" (
IN @Table_Name char(128),
IN @Owner_Name char(128) DEFAULT NULL
)
BEGIN
DECLARE @Column char(128);
DECLARE @IdentityValue bigint;
declare @GlobalID bigint;
declare @PartitionSize bigint;
DECLARE @DefaultValue char(128);
DECLARE @ColumnWidth integer;
DECLARE @SQLExp long varchar;
DECLARE @Pos1 integer;
DECLARE @Pos2 integer;
IF @Owner_Name IS NULL
THEN
SET @Owner_Name = User_Name();
END IF;
SELECT c.Column_Name, c."default", c.width
INTO @Column, @DefaultValue, @ColumnWidth
FROM SysColumn c
INNER JOIN SysTable t ON t.Table_id = c.Table_id AND
t.Creator = User_id(@Owner_Name) AND
t.Table_Name = @Table_Name
WHERE c."default" LIKE '%AUTOINCREMENT%';
IF @Column IS NULL
THEN
RAISERROR 20000 'Table "' || @Owner_Name || '"."' || @Table_Name || '" not have IDENTITY';
RETURN;
END IF;
select CONNECTION_PROPERTY('GLOBAL_DATABASE_ID') into @GlobalID;
IF @GlobalID < 0 OR @GlobalID = 2147483647 THEN
Set @GlobalID = -1;
END IF;
set @PartitionSize=0;
IF Locate(@DefaultValue, 'GLOBAL AUTOINCREMENT') > 0 AND @GlobalID >= 0 THEN
SET @Pos1 = Locate(@DefaultValue, '(');
SET @Pos2 = Locate(@DefaultValue, ')');
IF @Pos1 > 0 AND @Pos2 > 0 THEN
SET @PartitionSize = CAST(SubStr(@DefaultValue, @Pos1 + 1, @Pos2 - @Pos1 - 1) AS bigint);
ELSEIF @ColumnWidth = 8 THEN
SET @PartitionSize = 4294967296; // значение по умолчанию для Bigint
ELSE
SET @PartitionSize = 65536; // значение по умолчанию для остальных типов
END IF;
END IF;
SET @SQLExp = 'SELECT Max("' || @Column || '") INTO @IdentityValue ' ||
'FROM "' || @Owner_Name || '"."' || @Table_Name || '" WITH(SERIALIZABLE)' ||
if @GlobalID >= 0 AND @PartitionSize > 0 then
' WHERE ' || @Column || '>' || string(@GlobalID * @PartitionSize) || ' AND ' ||
@Column || '<' || string((@GlobalID+1) * @PartitionSize)
endif;
EXECUTE IMMEDIATE WITH RESULT SET OFF @SQLExp;
IF IsNull(@IdentityValue, 0) <= 0
THEN
SET @IdentityValue = @GlobalID*@PartitionSize;
END IF;
CALL sa_Reset_Identity (@Table_Name, @Owner_Name, @IdentityValue);
COMMIT;
MESSAGE 'Complete new identity ' || @IdentityValue || ' from table "' || @Owner_Name || '"."' || @Table_Name || '"' TO CLIENT DEBUG
ONLY;
END
CREATE PROCEDURE "DBA"."sp_asc_ResetIdentity_All" (
IN @Owner_Name char(128) DEFAULT NULL,
IN @Table_Name_LikeExp char(128) DEFAULT NULL
)
BEGIN
IF @Owner_Name IS NULL
THEN
SET @Owner_Name = User_Name();
END IF;
FOR lTables AS cTables NO SCROLL CURSOR FOR
SELECT Table_Name AS @Table_Name
FROM SysTable t
WHERE Creator = User_id(@Owner_Name) AND
Table_Type = 'BASE' AND
isnull(remote_location, '') = '' AND
(@Table_Name_LikeExp IS NULL OR Table_Name LIKE @Table_Name_LikeExp) AND
EXISTS( SELECT *
FROM SysColumn c
WHERE t.Table_id = c.Table_id AND
c."default" LIKE '%AUTOINCREMENT%')
ORDER BY Table_Name
DO
CALL sp_asc_ResetIdentity (@Table_Name, @Owner_Name);
END FOR;
END
|
Дата публикации: Monday 27 March 2006 17:11:56 Материал прочитан: 15287 раз(а) [ Назад ] |
|
|
|
|
|
|
Copyright©2005 . Все права защищены.
|