1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

adding a record

Discussion in 'SQL PL/SQL' started by artistic21, Apr 10, 2014.

  1. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi, can you please help me with a stored procedure in plsql? i have a table employees filled with data. Employees table has attributes: first_name,last_name, email,manager_id,dept_id. I want to insert a new employee, but i have to control the manager_id attribut. If manager have more than 4 employees in supervision,this new employee wuoldn't be added. (so w have to print the message<dbms_output.put_line('the manager'||nameOfManager|| 'have maximum number of employees')>, else the new employee should be added<dbms_output.put_line(nameOfManager|| 'have a new employee')>
     

    Attached Files:

  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Really? You've already written out the solution...all you need to do is code it.

    CREATE OR REPLACE PROCEDURE <procedure name>(MGR_ID in number) as

    BEGIN
    -- Use the MGR_ID to see if you can add another employee
    IF a Manager < 5 employees THEN
    Call my insert routine
    DBMS_OUTPUT.PUT('Message')
    ELSE
    Raise some error or DBMS_OUTPUT.PUT('Message')
    END IF;
    END:


    CJ
     
    artistic21 likes this.
  3. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    The problem for me is coding,cause i'm a beginner in plsql. I've written smth,but it doesnt work,that's why i've written here. I'd really appreciate your help.
    Code (SQL):
    DECLARE
      MngrRepeated NUMBER;
      mngr NUMBER;
      BEGIN
     
      SELECT COUNT(*) INTO MngrRepeated
        FROM employees WHERE employee_id=manager_id;
       --IF AN EMPLOYEE MANAGES LESS THAN 4 EMPLOYEES , THE PROGRAM  HAVE TO ADD A NEW EMPLOYEE IN THE EMPLOYYE TABLE
       --I CHOOSE TO MAKE THE CONTROL BY COUNTING HOW MANY TIMES EMP-ID=MNG_ID.
        IF MngrRepeated<4 THEN
        INSERT INTO employees VALUES (FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,  DEPARTMENT_ID);
        ELSE
        --THIS IS THE CASE WHEN MANAGER IS REPEATED LESS THAN 4 TIMES IN THE EMPLOYEES TABLE AND WE HAVE TO PRINT THE MESSAGE BELOW.
        SELECT FIRST_NAME  INTO mngrnAME FROM employees
        WHERE MANAGER_ID=EMPLOYEE_ID;
        DBMS_OUTPUT.PUT_LINE('Manager :'||mngrNAME||'CAN'T HAVE MORE EMPLOYEES, DUE TO THE MAXIMUM NUMBER OF EMPLOYEES!');
        end if;
        end;
       
     
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Take a look at your given data set and then look at the query in your PL/SQL block :

    SELECT COUNT(*)
    INTO MngrRepeated
    FROM employees
    WHERE employee_id = manager_id;

    Your employee_id is NEVER = to the manager_id so the query returns nothing. You want the count of employees within DEPARTMENTS where the Manager is the same. Thus Mgr12 has 4 employees in department 100 (in your sample).

    Your comment in the block says if a Mgr manages less than 4 employees which is 1,2 or 3 yet the assignment says if the Mgr has more than 4 employees then don't add any...and THAT implies a Mgr can have 1 - 4 employees (inclusive) so your solution is already off by one.

    Does that clear things up a bit?
     
  5. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    it's >=4 exactly. But it doesnt make the problem. the problem is how to count the number of employees have each manager, what condition i can use?