Write a business transaction that involves multiple tables and implement it usin
ID: 3557354 • Letter: W
Question
- Write a business transaction that involves multiple tables and implement it using PL/SQL. Use at least one Function or Procedure in your code. The transaction may retrieve data from the database and display the result, or it may insert/update data in certain table(s).
- Choose a table that you wish to monitor. Create a table to have monitoring data (say LogTable). Create a trigger that automatically records data into the LogTable whenever some tuple is inserted, updated or deleted in the table being monitored.
Explanation / Answer
part 1
-- get employee's fullname
FUNCTION get_fullname(n_emp_id NUMBER) RETURN VARCHAR2 IS
v_fullname VARCHAR2(46);
BEGIN
SELECT first_name || ',' || last_name
INTO v_fullname
FROM employees
WHERE employee_id = n_emp_id;
RETURN v_fullname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END; -- end get_fullname
-- get salary
FUNCTION get_salary(n_emp_id NUMBER) RETURN NUMBER IS
n_salary NUMBER(8,2);
BEGIN
SELECT salary
INTO n_salary
FROM employees
WHERE employee_id = n_emp_id;
RETURN n_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN TOO_MANY_ROWS THEN
RETURN NULL;
END;
END personnel;
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_salary NUMBER(8,2);
v_name VARCHAR2(46);
n_emp_id NUMBER := &emp_id;
BEGIN
v_name := personnel.get_fullname(n_emp_id);
n_salary := personnel.get_salary(n_emp_id);
IF v_name IS NOT NULL AND
n_salary IS NOT NULL
THEN
dbms_output.put_line('Employee: ' || v_name);
dbms_output.put_line('Salary:' || n_salary);
END IF;
END;
Part 2
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.