Suppose You Have Created Tables As Follows: Tables Company C

Suppose You Have Created Tables As Followstablescompanycid Cname C

Suppose you have created tables as follows: Tables: Company(cid, cname, city, state), Employee(eid, ename, street, city, state, salary, cid), Manages(eid, mid).

Write a PL/SQL program for the following:

Problem 1

Create a PL/SQL procedure that, given a company name, prints out the names of employees working at that company. Test your procedure with a company name from your company table.

Problem 2

Create a PL/SQL function get_manager that returns the manager’s ID given the name of an employee. If an employee does not have a manager, the function should return a null value.

Write an anonymous PL/SQL program to call this function with some employee name as input and print out the results.

Paper For Above instruction

In this paper, we explore the implementation of PL/SQL procedures and functions in the context of a relational database that manages information about companies, employees, and managerial relationships. The core objective is to develop two separate PL/SQL routines—one procedure to retrieve and display employee names based on a provided company name, and one function to identify an employee’s manager by name, returning the manager’s ID or null if none exists. These routines facilitate data retrieval and relationship mapping within the database, exemplifying practical SQL programming skills that are crucial for database administration and application development.

Creating the Employee Retrieval Procedure

The first task involves creating a stored procedure that accepts a company name as input and outputs all employees working at that company. The procedure must first identify the company ID (cid) associated with the given company name. This requires querying the Company table. Once the cid is retrieved, the procedure then queries the Employee table to find all employees associated with that cid. The procedure should then output the employee names. This approach ensures modularity and reusability of the code, allowing easy querying for employee lists for any given company.

In implementing this procedure, PL/SQL cursors or SELECT INTO statements can be utilized. A cursor may be preferable if multiple employees are returned, as it allows looping through all employee records. Error handling is also essential to manage cases where the company name is invalid or no employees are found. A typical implementation involves declaring variables for the company ID, opening a cursor for employee names, and printing these names in a loop.

Creating the get_manager Function

The second task is to develop a user-defined function get_manager. This function takes an employee’s name as input and returns the manager’s ID. It relies on the Employee and Manages tables: it first finds the employee's ID (eid) based on the name, then looks up the Manages table to identify the manager’s ID (mid). If the employee does not have an entry in the Manages table, the function should return NULL. This simple lookup encapsulates the logic for role association within one reusable function.

Implementing this function involves SELECT statements to retrieve the employee’s ID and, subsequently, the manager’s ID. Handling cases where the employee’s name does not exist or no manager is assigned is crucial—appropriate exception handling ensures robustness. The function's design promotes clarity and ease of use, enabling other PL/SQL routines or SQL queries to leverage it for managerial information.

Testing the Function with an Anonymous Block

Finally, an anonymous PL/SQL block is required to invoke get_manager with a sample employee name. The block captures the return value of the function and displays it. This test demonstrates the function’s utility and provides immediate feedback on its correctness. It also illustrates the integration of functions within anonymous PL/SQL blocks for ad hoc querying, vital for developers and database administrators.

In conclusion, the combined use of procedures and functions enhances the analytical capabilities of the database system, providing structured, reusable, and efficient methods for extracting relational data. These PL/SQL routines exemplify best practices in database programming, emphasizing clear logic, exception handling, and practical utility in business applications centered on organizational hierarchies and employee management.

References

• Sharma, S. (2010). Oracle PL/SQL Programming. O'Reilly Media.