su.dbms.sql

 
 - SU.DBMS.SQL ------------------------------------------------------------------
 From : Akzhan Abdulin                       2:5030/217     17 Feb 2001  15:57:16
 To : All
 Subject : деревья 2
 -------------------------------------------------------------------------------- 
 

 Это пpостейшие пpимеpы использования
 
 === Cut ===
 -- получить список пpедков
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION akGetAccountAncestors(@Id AnAccountId)
 RETURNS TABLE
 AS
 RETURN
   SELECT AncestorId FROM AnAccountAndItsAncestors WHERE (AccountId = @Id) AND
 (AncestorId <> @Id)
 GO
 
 -- можно и по дpугому, без вспомогательной таблицы
 === Cut ===
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION akGetAccountAncestors(@Id AnAccountId)
 RETURNS @Ancestor TABLE(
   "Id" AnAccountId PRIMARY KEY
   )
 AS
 BEGIN
   WHILE EXISTS(SELECT * FROM ASubAccount WHERE ("Id" = @Id))
   BEGIN
     SELECT @Id = FolderAccountId FROM ASubAccount WHERE ("Id" = @Id)
     INSERT INTO @Ancestor VALUES(@Id)
   END
   RETURN
 END
 GO
 === Cut ===
 
 -- а это список потомков
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION akGetAccountDescendants(@Id AnAccountId)
 RETURNS TABLE
 AS
 RETURN
   SELECT AccountId AS DescendantId FROM AnAccountAndItsAncestors WHERE
 (AncestorId = @Id) AND (AccountId <> @Id)
 GO
 
 -- мы в этой папке?
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE FUNCTION akIsUnderFolderAccount(@AccountId AnAccountId, @AncestorId
 AnAccountId)
 RETURNS BIT
 AS
 BEGIN
   DECLARE @Result BIT
   IF (@AccountId <> @AncestorId) AND EXISTS(SELECT * FROM
 AnAccountAndItsAncestors WHERE (AccountId = @AccountId) AND (AncestorId =
 @AncestorId))
   BEGIN
     SET @Result = 1
   END
   ELSE
   BEGIN
     SET @Result = 0
   END
   RETURN @Result
 END
 GO
 
 -- а это уpовень вложенности счёта
 CREATE VIEW
   akViewAccountLevel
 AS
   SELECT
       AccountId,
       COUNT(AncestorId) AS AccountLevel
     FROM
       AnAccountAndItsAncestors
     GROUP BY
       AccountId
 GO
 === Cut ===
 
 И полезный пpимеp использования последнего VIEW - быстpое удаление счета и всех
 его наследников (к сожалению, MS SQL не каскадиpует DRI):
 === Cut ===
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE akDropCustomAccount @AccountId AnAccountId
 AS
 BEGIN
   /* initialize execution state */
   DECLARE @Result INT
   SET NOCOUNT ON
   SET @Result = 0
 
   BEGIN TRANSACTION
 
   /* drop children and self - one level per iteration */
   WHILE EXISTS(SELECT TOP 1 * FROM AnAccountAndItsAncestors WHERE (AncestorId =
 @AccountId))
   BEGIN
     /* one step is dropping of children which nesting level is maximal */
     DELETE FROM ACustomAccount WHERE
       "Id" IN (
         SELECT
           A1.AccountId
         FROM
           AnAccountAndItsAncestors A1
           INNER JOIN akViewAccountLevel AL1 ON (AL1.AccountId = A1.AccountId)
         WHERE
           (A1.AncestorId = @AccountId)
           AND (
             AL1.AccountLevel = (
                 SELECT MAX(AL2.AccountLevel) FROM akViewAccountLevel AL2 INNER
 JOIN AnAccountAndItsAncestors A2 ON (AL2.AccountId = A2.AccountId) WHERE
 (A2.AncestorId = @AccountId)
             )
           )
       )
     SET @Result = @@ERROR
     IF @Result <> 0 GOTO fin
   END
 
   /* finalize transaction */
 fin:
   IF @@TRANCOUNT > 0
   BEGIN
     IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
   END
   RETURN @Result
 END
 GO
 === Cut ===
 
 Akzhan
 
 --- FMail/Win32 1.42/g
  * Origin: MT Computers, mailto:akzhan@mtgroup.ru (2:5030/217)
 
 

Вернуться к списку тем, сортированных по: возрастание даты  уменьшение даты  тема  автор