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!

Procedure to assign employees to a manager

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, sorry for the interruption. 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')>
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: Query to output Hierarchical structure in flat format

    Hi,artistic21.

    No need to shout at each section of the forum.
    We hear you.

    For a start prepare a prototype of the procedure.
    Publish her code here ,
    Write that it is impossible to you by procedure development.
    Give codes and the text of errors.

    See links:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#LNPLS008
     
  3. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    hi,i've written smth but it doesnt work properly. :/
    Code ( (Unknown Language)):

      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;
       
     
    i'd really appreciate your help. Thankkkks.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This:


    Code (SQL):

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

    MUST reference the manager id passed into the procedure. It won't work otherwise. Likewise this:


    Code (SQL):

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

    MUST also reference the manager_id value passed to the procedure. I am using the EMP table but the process would be the same. Here is my modified code, which works:


    Code (SQL):

    SQL> CREATE OR REPLACE PROCEDURE add_employee(pempno IN NUMBER, pename IN varchar2, pjob IN varchar2, pmgr IN NUMBER, phiredate IN DATE, psal IN   NUMBER,
    pcomm IN NUMBER, pdeptno IN  NUMBER)
      2  AS
      3  MngrRepeated NUMBER;
      4  mngrname varchar2(40);
      5    mngr NUMBER;
      6    BEGIN
      7
      8    SELECT COUNT(*) INTO MngrRepeated
      9      FROM emp WHERE mgr=pmgr;
     10     --IF AN EMPLOYEE MANAGES LESS THAN 4 emp , THE PROGRAM  HAVE TO ADD A NEW EMPLOYEE IN THE EMPLOYYE TABLE
     11     --I CHOOSE TO MAKE THE CONTROL BY COUNTING HOW MANY TIMES EMP-ID=MNG_ID.
     12      IF MngrRepeated<4 THEN
     13  --    insert into emp values (FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,  DEPARTMENT_ID);
     14      INSERT INTO emp VALUES (pempno, pename, pjob, pmgr, phiredate, psal, pcomm, pdeptno);
     15     ELSE
     16      --THIS IS THE CASE WHEN MANAGER IS REPEATED LESS THAN 4 TIMES IN THE emp TABLE AND WE HAVE TO PRINT THE MESSAGE BELOW.
     17      SELECT ENAME  INTO mngrnAME FROM emp
     18      WHERE empno=pmgr;
     19      DBMS_OUTPUT.PUT_LINE('Manager :'||mngrNAME||'CAN''T HAVE MORE emp, DUE TO THE MAXIMUM NUMBER OF emp!');
     20      END IF;
     21      END;
     22  /


    PROCEDURE created.


    SQL> EXEC add_employee(9918,'BORGEN','GREEPER',7698, sysdate, 1000, NULL, 20)
    Manager :BLAKECAN'T HAVE MORE emp, DUE TO THE MAXIMUM NUMBER OF emp!


    PL/SQL procedure successfully completed.


    SQL>
     
  5. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    thanks for the solution, but what is mgr? in line 9..
     
  6. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Error(18,3): PL/SQL: SQL Statement ignored.... <mgr=pmgr ,empno=pmgr>???
    i don't know what they mean in your db procedure is created,but it doesnt print the result exactly. thanks again&again :)
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please read the ENTIRE post -- I explained I used the EMP table, NOT the EMPLOEES table. You will need to make changes to my code if you want it to work with your table.
     
  8. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    yes, i made the changes but i don't know how the conditions work. You write mgr=pmgr,but i cant find what do you mean by that? Can you explain me only the conditions pls,because everything else is clear.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Look at how the procedure is defined, especially the parameters required to call it. Those names are used in the queries as you need to reference the manager id you submitted to the procedure.
     
  10. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    it's ok now. Do you know can i manage this exercise with cursor (with loops)?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why do you think you need a cursor?
     
    artistic21 likes this.
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Based on the conditions given in the first post, I'd say it is because you are doing his homework. :)
     
  13. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    My friend said it's more easy to use cursor, but i didnt't find easy in fact. I had no idea how to use cursor in that time. It's ok in that way we did. Thank youuuu :)