PL/SQL procedure that should output the following

Question

Just cant get the output correct, maybe a loop problem ? Have tried a few things but Just cant get the output correct

should look like

Department 1 SALES managed by Alvin
Total number of employees: 5
00110 Alvin
 00103 Ami
00109 Michael
00101 Peter
 00107 Wendy
Department 2 ACCOUNTING managed by Alice
Total number of employees: 2
 00120 Alice
00125 Angela
Department 3 GAMES managed by Bob
Total number of employees: 2
00150 Bob
 00105 Robert

CREATE TABLE Department (
    D#      NUMBER(5)   NOT NULL, /* Department number      */
    DName       VARCHAR2(30)    NOT NULL, /* Department name        */
    Manager#    CHAR(5)     NOT NULL, /* Department manager number  */
    MSDate      DATE,             /* Manager start date         */
    CONSTRAINT Department_PK PRIMARY KEY(D#),
    CONSTRAINT Department_CK UNIQUE(DName)
);


CREATE TABLE Employee (
    E#      CHAR(5)     NOT NULL, /* Employee number        */
    Name        VARCHAR2(30)    NOT NULL, /* Employee name      */
    DOB     Date,             /* Date of birth      */
    Address     VARCHAR2(50),         /* Home address       */
    Sex     CHAR,             /* M-Male, F-Female       */
    Salary      NUMBER(7,2),          /* Salary         */
    Super#      CHAR(5),          /* Supervisor number      */
    D#      NUMBER(5),        /* Department number      */
    CONSTRAINT Employee_PK PRIMARY KEY(E#),
    CONSTRAINT Employee_FK1 FOREIGN KEY (Super#) REFERENCES Employee(E#),
    CONSTRAINT Employee_FK2 FOREIGN KEY (D#) REFERENCES Department (D#)
);

ARE THE TABLES

THE CODE I HAVE BEEN TRYING IS

CREATE OR REPLACE PROCEDURE
INSERT_MANAGER IS

MANAGER_NAME  VARCHAR(40);
DEPT_# NUMBER(5);
DEPT_NAME VARCHAR(40);
EMP_# NUMBER(5);
EMP_NAME VARCHAR(40);
EMP_TOTAL NUMBER(6);

CURSOR MANAGER IS 
SELECT Name, Department.D#,DName
INTO MANAGER_NAME, DEPT_#, DEPT_NAME
FROM Employee
JOIN Department
ON
Department.D# = Employee.D#
WHERE E# = Manager#
ORDER BY DEPT_# ASC;

CURSOR EMPLOYEE IS
SELECT COUNT(NAME),NAME,E#
INTO EMP_TOTAL,EMP_NAME,EMP_#
FROM Employee
JOIN Department ON 
Department.D# = Employee.D#
WHERE E# = Manager#
GROUP BY NAME,E#;

BEGIN
OPEN MANAGER;
OPEN EMPLOYEE;
LOOP
FETCH MANAGER INTO MANAGER_NAME, DEPT_#, DEPT_NAME;
FETCH EMPLOYEE INTO EMP_TOTAL,EMP_NAME,EMP_#;
EXIT WHEN MANAGER%notfound;
DBMS_OUTPUT.PUT_LINE('Department ' || DEPT_# || ' ' || DEPT_NAME || ' Managed By: ' || MANAGER_NAME );
DBMS_OUTPUT.PUT_LINE('Total Number Of Emploees ' || EMP_TOTAL);
DBMS_OUTPUT.PUT_LINE(EMP_# || ' ' || EMP_NAME);
END LOOP;
CLOSE MANAGER;
END;
/

My current output lhas the correct manager but the incorrect TOTAL NUMBER OF EMPLOYEES and is only showing 1 of the Employees under that manager

Department 1 SALES Managed By: Alvin
Total Number Of Emploees 1
150 Bob
Department 2 ACCOUNTING Managed By: Alice
Total Number Of Emploees 1
338 Helmus
Department 3 GAMES Managed By: Bob
Total Number Of Emploees 1
110 Alvin

THANKS IN ADVANCE FOR ANY HELP. LUKE

You don't have a loop for employee so the code is only going to see the first employee for each manager. — P.Salmon, Aug 31 '19 at 09:02
Formatting your code neatly will make it easier to follow and to spot errors. Also, [don't use CHAR](https://stackoverflow.com/a/42165653/230471). — William Robertson, Aug 31 '19 at 09:18

score 0 · Answer 1 · answered Aug 31 '19 at 20:42

The following produces your desired output while for the most part maintaining your logic flow (except for a 2nd loop for the Employee cursor). But I have made a couple syntactical changes. First, names are important. Attempt to name objects according to the function being preformed. For example the MANAGER cursor really gets nothing about the manager (except for name). What is gets is information about the department; also renamed the employee cursor (just because 2 objects with the same name - cursor and table). Structurally, I moved getting the employee count to the department cursor (manager) as it pertains to the department not the employee. As far as naming goes the procedure name itself is misleading as the procedure has nothing to do with inserting managers (but I let it); unless of course it's just part of a larger process.

Definition consistence is also important, and there are problems with that here. For example: Look at table column employee.name vs. the variable manager_name. And what's with employee.e# vs. emp_#? A method to stay consistent is to anchor procedure variables to the table columns they represent.

So instead of "manager_name varchar(40)" use "manager_name employee.name%type" BTW: Varchar is frowned upon use varchar2. But I left these with your original definitions. (Just for fun I played around this the data values a little - just values not structure.) The result then:

  create or replace procedure insert_manager is
   -- department cursor variables
    manager_name   varchar(40);
    dept_#         number(5);
    dept_name      varchar(40);
    emp_total      number(6);

    -- employee cursor variables
    emp_#          number(5);
    emp_name       varchar(40);

    -- cursors definitions
    cursor department_cur is
        select name, d#, dname, demps
          from ( select e.name
                      , d.d#
                      , d.dname
                      , count(e.e#) over (partition by d.d#) demps
                      , e.e#
                      , d.manager# m#
                   from employee   e  
                   join department d  on d.d#  = e.d#
               )
           where  e# = m#
           order by d# asc;  


    cursor employee_cur(ed# integer) is 
        select e.name, e.e#                        
           from employee   e
          where e.d# = ed#;

begin
    dbms_output.put_line( 'Chipmunks Gaming, LLC: Department Employee List as of ' || to_char(sysdate, 'dd-Mon-yyyy')); 

    open department_cur;
    loop
        fetch department_cur into manager_name, dept_#, dept_name, emp_total;
         exit when department_cur%notfound;        
            dbms_output.put_line('Department ' || dept_# || ' ' || dept_name || ' Managed By: ' || manager_name);
            dbms_output.put_line('Total Number Of Employees ' || emp_total);        

            open employee_cur (dept_#);
            loop
                fetch employee_cur into emp_name, emp_#;
                 exit when employee_cur%notfound;
                    dbms_output.put_line(emp_# || ' ' || emp_name);
            end loop;
            close employee_cur;

    end loop;
    close department_cur;
end insert_manager;

-- Generate test data
insert into Department (D#, DName, Manager#)
  select 1, 'SALES', '00150'  from dual union all                       
  select 2, 'ACCOUNTING','00120' from dual union all                      
  select 3, 'GAMES', '00110' from dual ;                              

insert into employee(E#, Name ,D# )
  select '00150', 'Simon' ,1 from dual union all
  select '00103', 'Ami' ,1 from dual union all
  select '00109', 'Michael' ,1 from dual union all
  select '00101', 'Peter' ,1 from dual union all
  select '00107', 'Wendy' ,1 from dual union all
  select '00120', 'Theodore' ,2 from dual union all
  select '00125', 'Angela' ,2 from dual union all
  select '00110', 'Alvin' ,3 from dual union all
  select '00105', 'Robert' ,3 from dual ; 
--------------------------------------------------------------------------------- 
-- run it
begin 
    insert_manager;
end;

The next big step would be using Implicit cursors (cursor for loops) instead of Explicit cursors. That would take of Opening, Looping through, and Closing the cursors. I'll leave that for you.

Good Luck. Hope this helps.

PL/SQL procedure that should output the following

1 Answers1