Oracle to Azure -- Complete Feature Mapping¶

-- Oracle PL/SQL
CREATE OR REPLACE PACKAGE emp_pkg AS
    FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER);
    PROCEDURE transfer_employee(p_emp_id NUMBER, p_new_dept NUMBER);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER IS
        v_salary NUMBER;
    BEGIN
        SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
        RETURN v_salary;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END get_salary;

    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER) IS
    BEGIN
        UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;
        IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
        END IF;
    END update_salary;

    PROCEDURE transfer_employee(p_emp_id NUMBER, p_new_dept NUMBER) IS
    BEGIN
        UPDATE employees SET department_id = p_new_dept WHERE employee_id = p_emp_id;
        INSERT INTO transfer_log (employee_id, new_department_id, transfer_date)
        VALUES (p_emp_id, p_new_dept, SYSDATE);
    END transfer_employee;
END emp_pkg;
/

-- T-SQL (Azure SQL MI)
-- Package becomes a schema
CREATE SCHEMA emp_pkg;
GO

CREATE OR ALTER FUNCTION emp_pkg.get_salary(@emp_id int)
RETURNS decimal(10,2)
AS
BEGIN
    DECLARE @salary decimal(10,2);
    SELECT @salary = salary FROM dbo.employees WHERE employee_id = @emp_id;
    RETURN @salary;  -- Returns NULL if not found (no exception needed)
END;
GO

CREATE OR ALTER PROCEDURE emp_pkg.update_salary
    @emp_id int,
    @new_salary decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.employees SET salary = @new_salary WHERE employee_id = @emp_id;
    IF @@ROWCOUNT = 0
        THROW 50001, 'Employee not found', 1;
END;
GO

CREATE OR ALTER PROCEDURE emp_pkg.transfer_employee
    @emp_id int,
    @new_dept int
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        UPDATE dbo.employees SET department_id = @new_dept WHERE employee_id = @emp_id;
        INSERT INTO dbo.transfer_log (employee_id, new_department_id, transfer_date)
        VALUES (@emp_id, @new_dept, GETDATE());
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

-- PL/pgSQL (Azure Database for PostgreSQL)
-- Package becomes a schema
CREATE SCHEMA IF NOT EXISTS emp_pkg;

CREATE OR REPLACE FUNCTION emp_pkg.get_salary(p_emp_id integer)
RETURNS numeric AS $$
DECLARE
    v_salary numeric;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
    RETURN v_salary;  -- Returns NULL if not found
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE emp_pkg.update_salary(
    p_emp_id integer,
    p_new_salary numeric
) AS $$
BEGIN
    UPDATE employees SET salary = p_new_salary WHERE employee_id = p_emp_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Employee not found' USING ERRCODE = 'P0001';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE emp_pkg.transfer_employee(
    p_emp_id integer,
    p_new_dept integer
) AS $$
BEGIN
    UPDATE employees SET department_id = p_new_dept WHERE employee_id = p_emp_id;
    INSERT INTO transfer_log (employee_id, new_department_id, transfer_date)
    VALUES (p_emp_id, p_new_dept, now());
END;
$$ LANGUAGE plpgsql;