---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第5章 バッチ、スクリプト、トランザクション
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 5.1 バッチ
---------------------------------------------------------------------

CREATE TABLE Part(
    PartId INT IDENTITY,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Type VARCHAR(50)
)


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7010CT', 'Notebook')

INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')

INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7030CT', 'Notebook')


IF EXISTS
(
    SELECT * FROM sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[prPartList]')
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
    DROP PROCEDURE [dbo].[prPartList]
GO
CREATE PROCEDURE prPartList
AS
    SELECT * FROM Part
RETURN 0
GO


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELEC * FROM Part


INSERT INTO art (Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELECT * FROM Part


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELECT * FROM art


SELECT PartId, Make + ' ' + Model Part FROM Part
INSERT INTO Part(PartId, Make, Model, Type)
VALUES(1, 'IBM', 'Thinkpad 390D', 'Notebook')
SELECT PartId, Make + ' ' + Model Part FROM Part


CREATE TABLE TmpPart(
    PartId INT,
    Make VARCHAR(50),
    Model VARCHAR(50)
)
GO
INSERT INTO TmpPart(PartId, Make, Model)
SELECT PartId, Make, Model FROM Part
GO
DROP TABLE Part
GO
CREATE TABLE Part(
    PartId INT,
    Make VARCHAR(50),
    Model VARCHAR(50)
)
GO
INSERT INTO Part(PartId, Make, Model)
SELECT PartId, Make, Model FROM TmpPart
GO
DROP TABLE TmpPart
GO


ALTER TABLE Part ADD Cost MONEY NULL
SELECT PartId, Cost FROM Part
GO


ALTER TABLE Part ADD Cost MONEY NULL
GO
SELECT PartId, Cost FROM Part
GO


DECLARE @Name AS VARCHAR(50)
GO
SELECT @Name = 'Dejan'
GO

---------------------------------------------------------------------
-- 5.3 トランザクション
---------------------------------------------------------------------

CREATE PROCEDURE ap_LeaseShedule_Clear
    @intLeaseScheduleId INT
AS
    BEGIN TRANSACTION
    -- リーススケジュールが満期になったすべての
    -- 備品のリース料を0に設定する
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- リース総額を0にリセットする
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'データベースからリース料を削除できません'
    ROLLBACK TRANSACTION
    RETURN 1


CREATE PROCEDURE ap_Order_Complete_1
    @intOrderId INT,
    @dtsCompletionDate SMALLDATETIME
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
            @i INT,
            @intCountOrderItems INT,
            @intOrderItemId INT

    SELECT @intErrorCode = @@ERROR
    IF @intErrorCode = 0
        BEGIN TRANSACTION

    -- 注文を完了する
    IF @intErrorCode = 0
    BEGIN
        UPDATE dbo.OrderHeader
        SET CompletionDate = @dtsCompletionDate,
            OrderStatusId = 4 -- 注文完了
        WHERE OrderId = @intOrderId
        SELECT @intErrorCode = @@ERROR
    END

    -- 注文明細の収集
    IF @intErrorCode = 0
    BEGIN
        CREATE TABLE #OrderItems(
            id INT IDENTITY(1, 1),
            OrderItemId INT
        )
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        INSERT INTO #OrderItems(OrderItemId)
            SELECT ItemId
            FROM dbo.OrderItem
            WHERE OrderId = @intOrderId
        SELECT @intErrorCode = @@ERROR
    END

    -- ループにより、注文明細中の各明細を1つずつ完了していく
    IF @intErrorCode = 0
    BEGIN
        SELECT @intCountOrderItems = MAX(id),
               @i = 1
        FROM #OrderItems
        SELECT @intErrorCode = @@ERROR
    END

    WHILE @intErrorCode = 0 AND @i <= @intCountOrderItems
    BEGIN
        IF @intErrorCode = 0
        BEGIN
            SELECT @intOrderItemId = OrderItemId
            FROM #OrderItems
            WHERE id = @i
            SELECT @intErrorCode = @@ERROR
        END

        IF @intErrorCode = 0
            EXEC @intErrorCode = dbo.ap_OrderItem_Complete_1 @intOrderItemId

        IF @intErrorCode = 0
            SET @i = @i + 1
    END

    IF @intErrorCode = 0 AND @@TRANCOUNT > 0
        COMMIT TRANSACTION
    ELSE
        ROLLBACK TRANSACTION
    RETURN @intErrorCode

GO

CREATE PROCEDURE dbo.ap_OrderItem_Complete_1
    @intOrderItemId INT
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT
    SELECT @intErrorCode = @@ERROR

    IF @intErrorCode = 0
        BEGIN TRANSACTION

    -- 注文明細の完了日を、その注文明細と関連付けられている
    -- 請求履歴の最後の日付に設定
    IF @intErrorCode = 0
    BEGIN
        UPDATE dbo.OrderItem
            SET CompletionDate = (SELECT MAX(ChargeDate)
                                  FROM dbo.ChargeLog
                                  WHERE ItemId = @intOrderItemId)
            WHERE ItemId = @intOrderItemId
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0 AND @@TRANCOUNT > 0
        COMMIT TRANSACTION
    ELSE
        ROLLBACK TRANSACTION
    RETURN @intErrorCode


PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    COMMIT TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
COMMIT TRANSACTION
PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)


PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    ROLLBACK TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
ROLLBACK TRANSACTION
PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)


BEGIN TRANSACTION t1
    BEGIN TRANSACTION t2
    ROLLBACK TRANSACTION t2
ROLLBACK TRANSACTION t1


CREATE PROCEDURE dbo.ap_ScrapOrder_Save
-- 注文を作成する
     @intOrderId INT,
     @dtsOrderDate SMALLDATETIME,
     @intRequestedById INT,
     @dtsTargetDate SMALLDATETIME,
     @insOrderTypeId SMALLINT,
     @inyOrderStatusId TINYINT
AS
    SET NOCOUNT ON
    INSERT dbo.OrderHeader(
        OrderId,
        OrderDate,
        RequestedById,
        TargetDate,
        OrderTypeId,
        OrderStatusId
    )
    VALUES(
        @intOrderId,
        @dtsOrderDate,
        @intRequestedById,
        @dtsTargetDate,
        @insOrderTypeId,
        @inyOrderStatusId
    )
GO

CREATE PROCEDURE dbo.ap_ScrapOrderItem_Save
-- 注文明細を追加する
-- ストアドプロシージャか、トランザクションを開始するバッチから
-- 呼び出さなければならない
    @intItemId INT,
    @intOrderId INT,
    @intInventoryId INT
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
            @chvInventoryId VARCHAR(10)

    -- セーブポイントを設定
    SET @chvInventoryId = CONVERT(VARCHAR, @intInventoryId)
    SAVE TRANSACTION @chvInventoryId

    -- 注文明細を挿入
    INSERT dbo.OrderItem(ItemId, OrderId, InventoryId)
    VALUES(@intItemId, @intOrderId, @intInventoryId)
    SELECT @intErrorCode = @@ERROR

    -- エラーがあった場合はセーブポイントまでロールバック
    IF @intErrorCode <> 0
    BEGIN
        ROLLBACK TRANSACTION @chvInventoryId
        RETURN @intErrorCode
    END

    RETURN 0


DECLARE @OrderId INT
DECLARE @ErrNum INT
SET @OrderId = 117

BEGIN TRAN
EXEC @ErrNum = dbo.ap_ScrapOrder_Save
    @intOrderId = @OrderId,
    @dtsOrderDate = '2007/2/1',
    @intRequestedById = 1,
    @dtsTargetDate = '2007/2/5',
    @insOrderTypeId = 3, -- 廃棄
    @inyOrderStatusId = 1 -- 注文済み
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    130,
    @OrderId,
    5
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    131,
    @OrderId,
    6
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    132,
    @OrderId,
    8
SELECT @ErrNum

COMMIT TRAN


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ


SELECT * FROM Inventory WITH (HOLDLOCK)
WHERE InventoryId = @intInventoryId


UPDATE Inventory WITH (TABLOCKX, HOLDLOCK)
SET StatusId = 4
WHERE StatusId = @intStatusId


CREATE PROCEDURE ap_LeaseShedule_Clear_distributed
-- リーススケジュールが満期になったすべての備品のリース料と
-- リース総額をすべて0に設定
-- リーススケジュールの完了をリース会社に通知
    @intLeaseScheduleId INT
AS
    DECLARE @chvLeaseNumber VARCHAR(50),
            @intError INT

    -- リースが満期になったかどうかを確認
    IF GETDATE() < (SELECT EndDate
                    FROM dbo.LeaseSchedule
                    WHERE ScheduleId = @intLeaseScheduleId)
        RAISERROR(N'指定されたリーススケジュールが満期になっていません', 16, 1)

    IF @@ERROR <> 0
    BEGIN
        PRINT N'データベースからリース情報を取得できません'
        RETURN 50000
    END

    -- リース番号を取得
    SELECT @chvLeaseNumber = Lease.LeaseNumber
    FROM dbo.Lease Lease INNER JOIN dbo.LeaseSchedule LeaseSchedule
    ON Lease.LeaseId = LeaseSchedule.LeaseId
    WHERE LeaseSchedule.ScheduleId = @intLeaseScheduleId

    BEGIN DISTRIBUTED TRANSACTION

    -- 該当する備品のリース料をすべて0に設定
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- リース総額を0にリセット
    UPDATE LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- リース会社へ通知
    EXEC @intError = lease_srvr.LeaseSchedules.dbo.prLeaseScheduleComplete
        @chvLeaseNumber, @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    IF @intError <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'リーススケジュールを更新できません'
    ROLLBACK TRANSACTION
    RETURN 50000


BEGIN TRANSACTION
UPDATE dbo.Inventory
SET Lease = 0
WHERE LeaseScheduleId = 141
GO

UPDATE dbo.LeaseSchedule
SET PeriodicTotalAmount = 0
WHERE ScheduleId = 141
COMMIT TRANSACTION
GO


CREATE PROCEDURE dbo.ap_LeaseSchedule_Clear_1
-- リース期間の過ぎたすべての備品の
-- リース料とリース総額をすべて0に設定
    @intLeaseScheduleId INT
AS
    -- リースが満期になったかどうかを確認
    IF GETDATE() < (SELECT EndDate
                    FROM dbo.LeaseSchedule
                    WHERE ScheduleId = @intLeaseScheduleId)
        RAISERROR(N'指定されたリーススケジュールが満期になっていません', 16, 1)

    -- ここでエラーが起こると、トランザクションの開始前であるにもかかわらず、
    -- ロールバックが実行され、新たなエラーとなる
    IF @@ERROR <> 0 GOTO PROBLEM

    BEGIN TRANSACTION
    -- リース期間の過ぎたすすべての
    -- 備品のリース料を0に設定する
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- リース総額を0に設定する
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'データベースからリース料を削除できません'
    ROLLBACK TRANSACTION
    RETURN 1

