Untitled

mail@pastecode.io avatar
unknown
mysql
2 years ago
1.4 kB
3
Indexable
Never
delimiter $
CREATE OR REPLACE PROCEDURE tugas2()
BEGIN

    DECLARE ID_MERK INT;
    DECLARE ID_BARANG INT;
    DECLARE CEK_ADA1 INT;
    DECLARE CEK_ADA2 INT;
    DECLARE NAMA_BARANG VARCHAR(20);
    DECLARE NAMA_MERK VARCHAR(20);

    DECLARE LIST1 CURSOR FOR
        SELECT M.ID
        FROM MERK M;

    DECLARE LIST2 CURSOR FOR
        SELECT B.ID
        FROM BARANG B
        WHERE B.ID_MERK = ID_MERK;

    DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET CEK_ADA1 = 1;

    OPEN LIST1;
    SET CEK_ADA1 = 0;
    LUPS1: LOOP
        FETCH LIST1 INTO ID_MERK;

        IF CEK_ADA1 = 1 THEN
            LEAVE LUPS1;
        END IF;

        SELECT M.NAMA INTO NAMA_MERK
        FROM MERK M
        WHERE M.ID = ID_MERK;

        SELECT RPAD('=', 25, '=');
        SELECT CONCAT('|', RPAD(LPAD(NAMA_MERK,((25-length(NAMA_MERK))/2)+length(NAMA_MERK)),25), '|');
        SELECT RPAD('=', 25, '=');

        OPEN LIST2;
        SET CEK_ADA2 = 0;
        ITERATE LUPS2;

    END LOOP LUPS1;
    CLOSE LIST1;

    
    LUPS2: LOOP
        FETCH LIST2 INTO ID_BARANG

        IF CEK_ADA2 = 1 THEN
            LEAVE LUPS2;
        END IF;

        SELECT B.NAMA INTO NAMA_BARANG
        FROM BARANG B
        WHERE B.ID = ID_BARANG;

        SELECT NAMA_BARANG;

    END LOOP LUPS2;
    CLOSE LIST2;


END$
delimiter ;