--general
FUNCTION MGRNAME(P_EMPNO IN EMP.EMPNO%TYPE) RETURN EMP.ENAME%TYPE IS

    RESULT EMP.ENAME%TYPE;

    I      INTEGER;

BEGIN

    RESULT := NULL;

    I      := 1;

    IF P_EMPNO IS NULL

    THEN

        -- If empno is null, return an empty name

        RESULT := NULL;

    ELSE

        -- Fetch the name of the manager

        SELECT M.ENAME

          INTO RESULT

          FROM EMP E,

               EMP M

         WHERE E.EMPNO = P_EMPNO

               AND M.EMPNO = E.MGR

               AND D.DEPTNO IN (10, 20, 30, 40);

    END IF;

    RETURN(RESULT);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        RETURN(NULL);

END;

BEGIN

    FOR EMP_CURSOR IN (SELECT * FROM EMP)

    LOOP

        IF EMP_CURSOR.MGR IS NULL

           OR EMP_CURSOR.MGR = 0

        THEN

            DBMS_OUTPUT.PUT_LINE('No manager');

        ELSE

            DBMS_OUTPUT.PUT_LINE('Manager = ' || TO_CHAR(EMP_CURSOR));

        END IF;

    END LOOP;

END;

BEGIN

    -- Select

    SELECT DEPNO AS DEPARTMENT_NUMBER,

           DNAME AS DEPARTMEN_NAME,

           LOC   AS DEPARTMENT_LOCATION

      FROM DEPT,

           EMP

     WHERE EMP.EMPNO = P_EMPNO

           AND DEPT.DEPTNO = EMP.DEPTNO;

    -- Insert

    INSERT INTO DEPT

        (DEPTNO,

         DNAME,

         LOC)

    VALUES

        (10,

         'Accounting',

         'New York');

    -- Update

    UPDATE DEPT

       SET DNAME = 'Accounting',

           LOC   = 'New York'

     WHERE DEPTNO = 10;

END;

PROCEDURE INSERTDEPT(P_DEPTNO IN OUT DEPT.DEPTNO%TYPE,

                     P_DNAME  IN DEPT.DNAME%TYPE,

                     P_LOC    IN DEPT.LOC%TYPE) IS

BEGIN

    -- Determine the maximum department number if necessary

    IF P_DEPTNO IS NULL

    THEN

        SELECT NVL(MAX(DEPTNO), 0) + 1 INTO P_DEPTNO FROM DEPT;

    END IF;

    -- Insert the new record

    INSERT INTO DEPT

        (DEPTNO,

         DNAME,

         LOC)

    VALUES

        (P_DEPTNO,

         P_DNAME,

         P_LOC);

END;

DECLARE

    TYPE DEPT_RECORD IS RECORD(

        DEPTNO NUMBER(2),

        DNAME  VARCHAR2(13),

        LOC    VARCHAR2(13));

BEGIN

    NULL;

END;