Creating a Procedure in PL/SQL to display all information from a table

Question

As the title states, I am trying to create a procedure to display all information about employees(Employee_ID, NAME, EMAIL_ADDRESS, HIRE_DATE, and UPDATE_DATE). The catch is that if the employee_id is not in the table then the output should display all information about employees. My output I am shooting for is:

EXEC get_employee_info_by_employee_id(4565)

Employee_ID:       4565
NAME:              Bob Doe
EMAIL_ADDRESS:     bobdoe@gmail.com
HIRE_DATE:         30-JUN-10
UPDATE DATE:       13-JULY-12

Here is my current code:

create or replace PROCEDURE get_employee_info_by_employee_id 
(   
p_employee_id   NUMBER DEFAULT -1
)
AS
   v_count   NUMBER;
BEGIN
      SELECT  COUNT(*)
      INTO    v_count
      FROM    employee
      WHERE   employee_id = p_employee_id;

  IF p_employee_id IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
    DBMS_OUTPUT.PUT_LINE('NAME: ' || name);
    DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || email_address);
    DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || hire_date);
    DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || update_date);

  ELSE
      SELECT  COUNT(*)
      INTO    v_count
      FROM    employee
      WHERE   employee_id = p_employee_id;
  END IF;
END;

I know this is far from correct. As a beginner, I am looking for how to edit and work with the code I currently have and learn the next steps to take to solve this. I know my ELSE statement is not correct and I'm kind of stuck as to what to do next. Thanks a lot everyone!

Answer 1

1. you can create New Table as Employee_History_inf as created Employee Table
2. Create Procedure with Parameters as Employee_Columns info on your Employee Table
3. Create Trigger Pre_Update Employee Table can insert new_info in Your New Table Employee_History_inf

Answer 2

One More Way (tm):

create or replace PROCEDURE get_employee_info_by_employee_id 
    (p_employee_id   NUMBER DEFAULT NULL)
AS
BEGIN
   -- This cursor will return
   --   - a single row if p_employee_id is specified and exists in the table
   --   - all rows in the table if p_employee_id is NULL (default value, or
   --     passed in as NULL)
   --   - all rows in the table if p_employee_id is specified but does not
   --     exist in the table

   FOR aRow IN (SELECT EMPLOYEE_ID, Name, Email_Address, Hire_Date, Update_Date
                FROM Employee
                WHERE Employee_ID = p_employee_id OR
                      p_employee_id IS NULL OR
                      0 = (SELECT COUNT(*)
                             FROM EMPLOYEE
                             WHERE EMPLOYEE_ID = p_employee_id)
   LOOP
     DBMS_OUTPUT.PUT_LINE('Employee ID: ' || aRow.EMPLOYEE_ID);
     DBMS_OUTPUT.PUT_LINE('NAME: ' || aRow.NAME);
     DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || aRow.EMAIL_ADDRESS);
     DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || aRow.HIRE_DATE);
     DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || aRow.UPDATE_DATE);
   END LOOP;
END get_employee_info_by_employee_id;

Share and enjoy.

Answer 3

Here's a working example, with annotations. Note that it uses an exception rather than an IF to handle the employee not being found.

Also, remember than when you use DBMS_OUTPUT you have to enable it. Type SET SERVEROUTPUT ON SIZE 10000 at the SQLPlus command line before executing your procedure. The size is the maximum number of characters to report, so 10K is more than enough.

create or replace PROCEDURE get_employee_info_by_employee_id 
(   
p_employee_id   NUMBER DEFAULT -1
)
AS
   -- You need to query the values you're showing into variables. The
   -- variables can have the same name as the column names. Oracle won't
   -- be confused by this, but I usually am - that's why I have the "v_"
   -- prefix for the variable names here. Finally, when declaring the
   -- variable's type, you can reference table.column%TYPE to use the
   -- type of an existing column.
   v_name Employee.Name%TYPE;
   v_email_address Employee.Email_Address%TYPE;
   v_hire_date Employee.Hire_Date%TYPE;
   v_update_date Employee.Update_Date%TYPE;
BEGIN
   -- Just SELECT away, returning column values into the variables. If
   -- the employee ID isn't found, Oracle will throw and you can pick
   -- up the pieces in the EXCEPTION block below.
   SELECT Name, Email_Address, Hire_Date, Update_Date
     INTO v_name, v_email_address, v_hire_date, v_update_date
     FROM Employee
     WHERE Employee_ID = p_employee_id;
   -- Fallthrough to here means the query above found one (and only one)
   -- row, and therefore it put values into the variables. Print out the
   -- variables.
   --
   -- Also note there wasn't a v_employee_id variable defined, because
   -- you can use your parameter value (p_employee_id) for that.
   DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
   DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
   DBMS_OUTPUT.PUT_LINE('EMAIL_ADDRESS: ' || v_email_address);
   DBMS_OUTPUT.PUT_LINE('HIRE_DATE: ' || v_hire_date);
   DBMS_OUTPUT.PUT_LINE('UPDATE_DATE: ' || v_update_date);
EXCEPTION
   -- If the query didn't find a row you'll end up here. In this case
   -- there's no need for any type of fancy exception handling; just
   -- reporting that the employee wasn't found is enough.
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || p_employee_id || ' not found.');
END;

Answer 4

A query such as:

select ...
from   employees
where  employee_id = 123
union all
select ...
from   employees
where  not exists (select null from employees where employee_id = 123)

... will return the single employee if it is found or all employees if it is not.