CREATE OR REPLACE FUNCTION get_employee_info(emp_id IN NUMBER) RETURN VARCHAR2 IS
 
    v_emp_name SCOTT.EMP.ENAME%TYPE;
    v_emp_job SCOTT.EMP.JOB%TYPE;
    v_emp_mgr SCOTT.EMP.MGR%TYPE;
    v_emp_hiredate SCOTT.EMP.HIREDATE%TYPE;
    v_emp_salary SCOTT.EMP.SAL%TYPE;
    v_emp_comm SCOTT.EMP.COMM%TYPE;
    v_emp_deptno SCOTT.EMP.DEPTNO%TYPE;
    v_emp_output VARCHAR2(4000);
 
BEGIN
    SELECT ename, job, mgr, hiredate, sal, comm, deptno
    INTO v_emp_name, v_emp_job, v_emp_mgr, v_emp_hiredate, v_emp_salary, v_emp_comm, v_emp_deptno
    FROM SCOTT.EMP
    WHERE empno = emp_id;
 
    v_emp_output := 'Employee ID: ' || emp_id || CHR(10) || 
                    'Name: ' || v_emp_name || CHR(10) || 
                    'Job: ' || v_emp_job || CHR(10) || 
                    'Manager ID: ' || NVL(TO_CHAR(v_emp_mgr), 'N/A') || CHR(10) || 
                    'Hire Date: ' || TO_CHAR(v_emp_hiredate, 'DD-MON-YYYY') || CHR(10) || 
                    'Salary: ' || TO_CHAR(v_emp_salary) || CHR(10) || 
                    'Commission: ' || NVL(TO_CHAR(v_emp_comm), 'N/A') || CHR(10) || 
                    'Department Number: ' || TO_CHAR(v_emp_deptno);
 
    RETURN v_emp_output;
 
END;
/
 
 
DECLARE
    v_employee_id SCOTT.EMP.EMPNO%TYPE := 7369; 
 
 
    v_employee_info VARCHAR2(4000); 
BEGIN
 
    v_employee_info := get_employee_info(v_employee_id);
 
 
    DBMS_OUTPUT.PUT_LINE(v_employee_info);
 
END;
/
 

Q1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gZ2V0X2VtcGxveWVlX2luZm8oZW1wX2lkIElOIE5VTUJFUikgUkVUVVJOIFZBUkNIQVIyIElTCgogICAgdl9lbXBfbmFtZSBTQ09UVC5FTVAuRU5BTUUlVFlQRTsKICAgIHZfZW1wX2pvYiBTQ09UVC5FTVAuSk9CJVRZUEU7CiAgICB2X2VtcF9tZ3IgU0NPVFQuRU1QLk1HUiVUWVBFOwogICAgdl9lbXBfaGlyZWRhdGUgU0NPVFQuRU1QLkhJUkVEQVRFJVRZUEU7CiAgICB2X2VtcF9zYWxhcnkgU0NPVFQuRU1QLlNBTCVUWVBFOwogICAgdl9lbXBfY29tbSBTQ09UVC5FTVAuQ09NTSVUWVBFOwogICAgdl9lbXBfZGVwdG5vIFNDT1RULkVNUC5ERVBUTk8lVFlQRTsKICAgIHZfZW1wX291dHB1dCBWQVJDSEFSMig0MDAwKTsKICAgIApCRUdJTgogICAgU0VMRUNUIGVuYW1lLCBqb2IsIG1nciwgaGlyZWRhdGUsIHNhbCwgY29tbSwgZGVwdG5vCiAgICBJTlRPIHZfZW1wX25hbWUsIHZfZW1wX2pvYiwgdl9lbXBfbWdyLCB2X2VtcF9oaXJlZGF0ZSwgdl9lbXBfc2FsYXJ5LCB2X2VtcF9jb21tLCB2X2VtcF9kZXB0bm8KICAgIEZST00gU0NPVFQuRU1QCiAgICBXSEVSRSBlbXBubyA9IGVtcF9pZDsKCiAgICB2X2VtcF9vdXRwdXQgOj0gJ0VtcGxveWVlIElEOiAnIHx8IGVtcF9pZCB8fCBDSFIoMTApIHx8IAogICAgICAgICAgICAgICAgICAgICdOYW1lOiAnIHx8IHZfZW1wX25hbWUgfHwgQ0hSKDEwKSB8fCAKICAgICAgICAgICAgICAgICAgICAnSm9iOiAnIHx8IHZfZW1wX2pvYiB8fCBDSFIoMTApIHx8IAogICAgICAgICAgICAgICAgICAgICdNYW5hZ2VyIElEOiAnIHx8IE5WTChUT19DSEFSKHZfZW1wX21nciksICdOL0EnKSB8fCBDSFIoMTApIHx8IAogICAgICAgICAgICAgICAgICAgICdIaXJlIERhdGU6ICcgfHwgVE9fQ0hBUih2X2VtcF9oaXJlZGF0ZSwgJ0RELU1PTi1ZWVlZJykgfHwgQ0hSKDEwKSB8fCAKICAgICAgICAgICAgICAgICAgICAnU2FsYXJ5OiAnIHx8IFRPX0NIQVIodl9lbXBfc2FsYXJ5KSB8fCBDSFIoMTApIHx8IAogICAgICAgICAgICAgICAgICAgICdDb21taXNzaW9uOiAnIHx8IE5WTChUT19DSEFSKHZfZW1wX2NvbW0pLCAnTi9BJykgfHwgQ0hSKDEwKSB8fCAKICAgICAgICAgICAgICAgICAgICAnRGVwYXJ0bWVudCBOdW1iZXI6ICcgfHwgVE9fQ0hBUih2X2VtcF9kZXB0bm8pOwogICAgCiAgICBSRVRVUk4gdl9lbXBfb3V0cHV0OwoKRU5EOwovCgoKREVDTEFSRQogICAgdl9lbXBsb3llZV9pZCBTQ09UVC5FTVAuRU1QTk8lVFlQRSA6PSA3MzY5OyAKICAgIAoKICAgIHZfZW1wbG95ZWVfaW5mbyBWQVJDSEFSMig0MDAwKTsgCkJFR0lOCgogICAgdl9lbXBsb3llZV9pbmZvIDo9IGdldF9lbXBsb3llZV9pbmZvKHZfZW1wbG95ZWVfaWQpOwogICAgCgogICAgREJNU19PVVRQVVQuUFVUX0xJTkUodl9lbXBsb3llZV9pbmZvKTsKICAgIApFTkQ7Ci8K