How to call Procedure in Oracle Apex.

How to declare Procedure in Oracle Apex

-- Create the depart table
CREATE TABLE depart (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

-- Create the employ table
CREATE TABLE employ (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert into departments
INSERT INTO depart (department_id, department_name) VALUES (1, 'Sales');
INSERT INTO depart (department_id, department_name) VALUES (2, 'Engineering');
INSERT INTO depart (department_id, department_name) VALUES (3, 'HR');

-- Insert into employees
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (101, 'Alice', 1);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (102, 'Bob', 2);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (103, 'Charlie', 1);

CREATE OR REPLACE PROCEDURE transfer_employee (
    p_employee_id IN NUMBER,
    p_new_department_id IN NUMBER
) IS
    v_old_department_id NUMBER;
BEGIN
    -- Find the current department of the employee
    SELECT department_id INTO v_old_department_id
    FROM employ
    WHERE employee_id = p_employee_id;

    -- Update the employee's department
    UPDATE employ
    SET department_id = p_new_department_id
    WHERE employee_id = p_employee_id;

    -- Optionally, you can log the department change or handle exceptions here
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' moved from department ' || v_old_department_id || ' to department ' || p_new_department_id);
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee with ID ' || p_employee_id || ' does not exist.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END transfer_employee;
/