1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. 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!

PL/SQL Package Example

Discussion in 'SQL PL/SQL' started by tyro, Nov 8, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    18
    Trophy Points:
    260
    Location:
    India
    PL/SQL Package Example

    The following package demonstrates all the features required in a proposed web-based database solution.

    The purpose of the package is to update the planned hours of an employee, to a project. The employee is constrained to the passed in parameter. An error is raised if the employee or project does not exist. The method of calling the procedure will also be illustrated at the end.

    Code (Text):
    CREATE OR REPLACE PACKAGE update_planned_hrs
    IS
          Planned_hours NUMBER(4);  

          PROCEDURE set_new_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER, p_hours IN NUMBER);
           FUNCTION existing_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER) RETURN NUMBER;

    END update_planned_hrs;
    /

    CREATE OR REPLACE PACKAGE BODY update_planned_hrs
    IS

    PROCEDURE set_new_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER, p_hours IN NUMBER)
    IS
    BEGIN
       UPDATE employee_on_activity ea
       SET ea.ea_planned_hours = p_hours
       WHERE
                ea.ea_emp_id = p_emp_id            
                AND ea.ea_proj_id = p_project_id;
             
    EXCEPTION
              WHEN NO_DATA_FOUND THEN
               RAISE_APPLICATION_ERROR (-20100, 'No such employee or project');

    END set_new_planned;

    FUNCTION existing_planned (p_emp_id IN NUMBER, p_project_id IN NUMBER) RETURN NUMBER

    IS

    existing_hours NUMBER(4);

    BEGIN
       SELECT ea.ea_planned_hours INTO existing_hours
       FROM employee_on_activity ea
       WHERE
                ea.ea_emp_id = p_emp_id    
                AND ea.ea_proj_id = p_project_id;
               
       RETURN (existing_hours);

       EXCEPTION
              WHEN NO_DATA_FOUND THEN
               RAISE_APPLICATION_ERROR (-20100, 'No such employee or project');


       END existing_planned;

    END update_planned_hrs;
    /
     
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    18
    Trophy Points:
    260
    Location:
    India
    The function can be called from the SQL command line like so:
    Code (Text):

    DECLARE
    l_emp_id NUMBER;
    l_project_id NUMBER;

    BEGIN
    l_emp_id := 1;
    l_project_id := 12;

    -- Obtain the existing planned hours for this employee using the persistant package variable.

    update_planned.planned_hours = update_planned.existing_planned(l_emp_id,l_project_id);

    -- Now update the employee planned hours

    update_planned.planned_hours := update_planned.planned_hours + 10;
    update_planned.set_new_planned (l_emp_id, l_project_id, update_planned.planned_hours);

    -- Output the results.

    dbms_output.put_line('Employee '||l_emp_id||' Project '||l_project_id || 'Changed planned hours to' || update_planned.planned_hours);
    END;
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It could have been more easy to understand if create and insert table script is provided along with the result using SQL*PLUS session .