Buat dan Panggi Store Procedure di Mysql

Step Stepnya :

  • Buat Store procedure di mysql
  • Test panggil store tersebut
  • Test query database apakah proces ke 2 jalan

Buat Store procedure di mysql

— DROP PROCEDURE DeleteAuditSheetData;
DELIMITER $$
CREATE PROCEDURE DeleteAuditSheetData (
    IN p_AuditSheetMonth SMALLINT,
    IN p_AuditSheetYear SMALLINT
)
BEGIN
    — Mulai transaksi
    START TRANSACTION;
    — Hapus dari auditsheetitemphoto
    DELETE FROM auditsheetitemphoto
    WHERE AuditSheetItemID IN (
        SELECT AuditSheetItemID FROM auditsheetitem
        WHERE AuditSheetID IN (
            SELECT AuditSheetID FROM auditsheet
            WHERE AuditSheetMonth = p_AuditSheetMonth
            AND AuditSheetYear = p_AuditSheetYear
            AND AuditSheetRevisionID IS NOT NULL
        )
    );
    — Hapus dari auditsheetitem
    DELETE FROM auditsheetitem
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
            AND AuditSheetRevisionID IS NOT NULL
    );
    — Hapus dari auditdivisionnotephoto
    DELETE FROM auditdivisionnotephoto
    WHERE AuditDivisionNoteID IN (
        SELECT AuditDivisionNoteID FROM auditdivisionnote
        WHERE AuditSheetID IN (
            SELECT AuditSheetID FROM auditsheet
            WHERE AuditSheetMonth = p_AuditSheetMonth
            AND AuditSheetYear = p_AuditSheetYear
            AND AuditSheetRevisionID IS NOT NULL
        )
    );
    — Hapus dari auditdivisionnote
    DELETE FROM auditdivisionnote
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
            AND AuditSheetRevisionID IS NOT NULL
    );
    — Hapus dari auditphoto
    DELETE FROM auditphoto
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
            AND AuditSheetRevisionID IS NOT NULL
    );
    — Hapus dari auditsheet
    DELETE FROM auditsheet
    WHERE AuditSheetMonth = p_AuditSheetMonth
    AND AuditSheetYear = p_AuditSheetYear
    AND AuditSheetRevisionID IS NOT NULL;
— hapus revisian setelah ini
    — Hapus dari auditsheetitemphoto
    DELETE FROM auditsheetitemphoto
    WHERE AuditSheetItemID IN (
        SELECT AuditSheetItemID FROM auditsheetitem
        WHERE AuditSheetID IN (
            SELECT AuditSheetID FROM auditsheet
            WHERE AuditSheetMonth = p_AuditSheetMonth
            AND AuditSheetYear = p_AuditSheetYear
        )
    );
    — Hapus dari auditsheetitem
    DELETE FROM auditsheetitem
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
    );
    — Hapus dari auditdivisionnotephoto
    DELETE FROM auditdivisionnotephoto
    WHERE AuditDivisionNoteID IN (
        SELECT AuditDivisionNoteID FROM auditdivisionnote
        WHERE AuditSheetID IN (
            SELECT AuditSheetID FROM auditsheet
            WHERE AuditSheetMonth = p_AuditSheetMonth
            AND AuditSheetYear = p_AuditSheetYear
        )
    );
    — Hapus dari auditdivisionnote
    DELETE FROM auditdivisionnote
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
    );
    — Hapus dari auditphoto
    DELETE FROM auditphoto
    WHERE AuditSheetID IN (
        SELECT AuditSheetID FROM auditsheet
        WHERE AuditSheetMonth = p_AuditSheetMonth
        AND AuditSheetYear = p_AuditSheetYear
    );
    — Hapus dari auditsheet
    DELETE FROM auditsheet
    WHERE AuditSheetMonth = p_AuditSheetMonth
    AND AuditSheetYear = p_AuditSheetYear;
    — Commit transaksi
    COMMIT;
END$$
DELIMITER ;

Test panggil store tersebut

CALL DeleteAuditSheetData(8, 2021);