DB2 Partials

/*Increase Pilot salaries using cursor and print the results*/
DECLARE @pilotID INT;
DECLARE @pilotName VARCHAR(20);
DECLARE @salary FLOAT;

SET NOCOUNT ON;
DECLARE cur CURSOR FOR
SELECT pilotid, firstname, salary FROM pilots
OPEN cur
    print 'Opened cursor';

    IF(@@CURSOR_ROWS > 0)
    BEGIN
        FETCH NEXT FROM cur INTO
        @pilotID, @pilotName, @salary;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            UPDATE pilots
            SET salary=@salary + 1000
            WHERE pilotid = @pilotID;

            print (CONVERT(VARCHAR(20),@pilotID) + ' ' + @pilotName + ', former salary: ' + CONVERT(VARCHAR(20),@salary) + '
                        New salary: ' + CONVERT(VARCHAR(20),@salary+1000));

            FETCH NEXT FROM cur INTO
            @pilotID, @pilotName, @salary;
        END
    END
CLOSE cur
DEALLOCATE cur
SET NOCOUNT OFF

------------------------------------------------------------------------------------------------

/*I think triggers are required in the question but I didn't use them because I wasn't sure if they were really required*/
/*Example IDs, not the ones in the question. Just change them as needed*/
DECLARE @oldID INT = 2;
DECLARE @newID INT = 3;

UPDATE flights
SET pilotid = @newID
WHERE pilotid = @oldID;

DELETE FROM pilots
WHERE pilots.pilotid = @oldID;

--------------------------------------------------------------------------------------------------

/*Use this procedure to create the pilot user from his ID. You can get thi IDs by using a cursor on Pilots table*/
CREATE PROC dbo.grant_pilots
    @pilotID INT
AS
BEGIN
    DECLARE @pilotUsername VARCHAR(30) = 'pilot' + @pilotID;
    DECLARE @grant_query VARCHAR(MAX);
    DECLARE @view_query  VARCHAR(MAX);

    DECLARE @create_user VARCHAR(MAX) = 'CREATE LOGIN ' + @pilotUsername + ' WITH PASSWORD = ''' + @pilotID + '''';
    EXEC @create_user;

    SET @grant_query = 'GRANT SELECT ON Airplanes TO ' + @pilotUsername;
    EXEC @grant_query;
    SET @grant_query = 'GRANT SELECT ON FLIGHTS TO ' + @pilotUsername;
    EXEC @grant_query;
    SET @grant_query = 'GRANT SELECT(pilotid, lastname, firstname, address, experience) ON Pilots TO ' + @pilotUsername;
    EXEC @grant_query;

    SET @view_query =
    'CREATE VIEW ' + @pilotUsername + '_SalaryView AS
    SELECT salary
    FROM Pilots
    WHERE pilotid = ' + @pilotID;
    EXEC @view_query;

    SET @grant_query = 'GRANT SELECT ON ' + @pilotUsername + '_SalaryView' + ' TO ' + @pilotUsername;
    EXEC @grant_query;

    SET @view_query =
    'CREATE VIEW ' + @pilotUsername + '_FlightsView AS
    SELECT departureTime, arrivalTime
    FROM Flights
    WHERE pilotid = ' + @pilotID;
    EXEC @view_query;

    SET @grant_query = 'GRANT UPDATE ON ' + @pilotUsername + 'FlightsView' + ' TO ' + @pilotUsername;
    EXEC @grant_query;
END