TALLER 2 PROGRAM UNITS
PUNTO 1
CONSULTA:
CREATE OR REPLACE PROCEDURE ADD_JOB(
p_job_id IN VARCHAR2,
p_job_title IN VARCHAR2
) AS
BEGIN
INSERT INTO JOBS (JOB_ID, JOB_TITLE)
VALUES (p_job_id, p_job_title);
COMMIT; -- Commit the transaction after insertion
END ADD_JOB;
EXEC ADD_JOB('IT_DBA', 'Database Administrator');
SELECT * FROM JOBS;
EXEC ADD_JOB('ST_MAN', 'Stock Manager');
PUNTO 2
CREATE OR REPLACE PROCEDURE UPD_JOB(
p_job_id IN VARCHAR2,
p_new_title IN VARCHAR2
) AS
-- Declare a variable to count the number of rows affected
v_rows_updated INTEGER;
BEGIN
-- Attempt to update the job title for the specified job ID
UPDATE JOBS
SET JOB_TITLE = p_new_title
WHERE JOB_ID = p_job_id;
-- Get the number of rows updated
v_rows_updated := SQL%ROWCOUNT;
-- If no rows were updated, raise an exception
IF v_rows_updated = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'No job found with the specified job ID.');
ELSE
COMMIT; -- Commit the transaction if update occurs
END IF;
EXCEPTION
-- Handle the exception if no rows were updated
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK; -- Rollback if there is any error
END UPD_JOB;
/
EXEC UPD_JOB('IT_DBA', 'Data Administrator');
SELECT * FROM JOBS WHERE JOB_ID = 'IT_DBA';
EXEC UPD_JOB('IT_WEB', 'Web Master');
PUNTO 3
CREATE OR REPLACE PROCEDURE DEL_JOB(
p_job_id IN VARCHAR2
) AS
-- Declare a variable to count the number of rows affected
v_rows_deleted INTEGER;
BEGIN
-- Attempt to delete the job with the specified job ID
DELETE FROM JOBS
WHERE JOB_ID = p_job_id;
-- Get the number of rows deleted
v_rows_deleted := SQL%ROWCOUNT;
-- If no rows were deleted, raise an exception
IF v_rows_deleted = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'No job found with the specified job ID to delete.');
ELSE
COMMIT; -- Commit the transaction if deletion occurs
END IF;
EXCEPTION
-- Handle the exception if no rows were deleted
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK; -- Rollback if there is any error
END DEL_JOB;
EXEC DEL_JOB('IT_DBA');
EXEC DEL_JOB('IT_WEB');
PUNTO 4
CREATE OR REPLACE PROCEDURE GET_EMPLOYEE(
p_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
p_salary OUT EMPLOYEES.SALARY%TYPE,
p_job_id OUT EMPLOYEES.JOB_ID%TYPE
) AS
BEGIN
-- Query the EMPLOYEES table to retrieve the salary and job ID
SELECT SALARY, JOB_ID
INTO p_salary, p_job_id
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_employee_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- If no employee is found with the given ID, set the output variables to NULL
p_salary := NULL;
p_job_id := NULL;
WHEN OTHERS THEN
-- Handle any other exceptions (e.g., query errors)
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK;
END GET_EMPLOYEE;
VARIABLE v_salary NUMBER;
VARIABLE v_job_id VARCHAR2(10);
-- Execute the procedure for employee ID 120
EXEC GET_EMPLOYEE(120, :v_salary, :v_job_id);
-- Display the output values
PRINT v_salary;
PRINT v_job_id;
-- Execute the procedure for employee ID 300
EXEC GET_EMPLOYEE(300, :v_salary, :v_job_id);
-- Display the output values
PRINT v_salary;
PRINT v_job_id;
TALLER 3
TALLER 3
PUNTO 1
CREATE OR REPLACE FUNCTION GET_JOB(
p_job_id IN VARCHAR2
) RETURN VARCHAR2 AS
v_job_title VARCHAR2(35);
BEGIN
-- Query the JOBS table to get the job title for the provided job ID
SELECT JOB_TITLE
INTO v_job_title
FROM JOBS
WHERE JOB_ID = p_job_id;
-- Return the job title
RETURN v_job_title;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- If no job is found, return NULL
RETURN NULL;
WHEN OTHERS THEN
-- Handle any other exceptions (e.g., query errors)
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RETURN NULL;
END GET_JOB;
-- Declare the host variable with a length of 35 characters to store the job title
VARIABLE b_title VARCHAR2(35);
-- Invoke the function for the job ID 'SA_REP' and store the result in the host variable
EXEC :b_title := GET_JOB('SA_REP');
-- Print the value of the host variable to display the job title
PRINT b_title;
PUNTO 2
CREATE OR REPLACE FUNCTION GET_ANNUAL_COMP(
p_salary IN NUMBER,
p_commission_pct IN NUMBER
) RETURN NUMBER AS
v_annual_salary NUMBER;
BEGIN
-- Calculate the annual salary, considering if commission is NULL
IF p_salary IS NULL THEN
-- If salary is NULL, the commission percentage will be applied to a base salary of 0 (no annual
compensation)
v_annual_salary := 0;
ELSE
v_annual_salary := p_salary * 12; -- Base salary component for the annual salary
-- If commission percentage is not NULL, add the commission to the annual salary
IF p_commission_pct IS NOT NULL THEN
v_annual_salary := v_annual_salary + (p_commission_pct * p_salary * 12);
END IF;
END IF;
-- Return the computed annual salary
RETURN v_annual_salary;
END GET_ANNUAL_COMP;
SELECT
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
COMMISSION_PCT,
GET_ANNUAL_COMP(SALARY, COMMISSION_PCT) AS ANNUAL_COMPENSATION
FROM
EMPLOYEES
PUNTO 3
CREATE OR REPLACE FUNCTION VALID_DEPTID(
p_deptid IN NUMBER
) RETURN BOOLEAN AS
v_exists NUMBER;
BEGIN
-- Check if the department ID exists in the DEPARTMENTS table
SELECT COUNT(*)
INTO v_exists
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = p_deptid;
-- If the department exists, return TRUE; otherwise, return FALSE
IF v_exists > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- In case of any error, return FALSE
RETURN FALSE;
END VALID_DEPTID;
/
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_job IN VARCHAR2 DEFAULT 'SA_REP',
p_mgr IN NUMBER DEFAULT 145,
p_sal IN NUMBER DEFAULT 1000,
p_comm IN NUMBER DEFAULT 0,
p_deptid IN NUMBER DEFAULT 30
) AS
v_employee_id NUMBER;
BEGIN
-- Validate the department ID using the VALID_DEPTID function
IF VALID_DEPTID(p_deptid) THEN
-- Insert the new employee into the EMPLOYEES table
SELECT EMPLOYEES_SEQ.NEXTVAL
INTO v_employee_id
FROM DUAL;
INSERT INTO EMPLOYEES(
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
JOB_ID,
MANAGER_ID,
SALARY,
COMMISSION_PCT,
DEPARTMENT_ID,
HIRE_DATE
) VALUES (
v_employee_id,
p_first_name,
p_last_name,
p_email,
p_job,
p_mgr,
p_sal,
p_comm,
p_deptid,
TRUNC(SYSDATE)
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee added successfully with Employee ID: ' ||
v_employee_id);
ELSE
-- Alert if the department ID is invalid
DBMS_OUTPUT.PUT_LINE('Error: Invalid Department ID ' || p_deptid);
END IF;
END ADD_EMPLOYEE;
/
EXEC ADD_EMPLOYEE('Jane', 'Harris', '[email protected]', 'SA_REP', 145, 1000, 0, 15);
EXEC ADD_EMPLOYEE('Joe', 'Harris', '[email protected]', 'SA_REP', 145, 1000, 0, 80);
TALLER 4
TALLER 4
PUNTO 1
CREATE OR REPLACE PACKAGE JOB_PKG AS
-- Procedure to add a job
PROCEDURE ADD_JOB(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2);
-- Procedure to update a job title
PROCEDURE UPD_JOB(p_job_id IN VARCHAR2, p_new_title IN VARCHAR2);
-- Procedure to delete a job
PROCEDURE DEL_JOB(p_job_id IN VARCHAR2);
-- Function to get a job title by job ID
FUNCTION GET_JOB(p_job_id IN VARCHAR2) RETURN VARCHAR2;
END JOB_PKG;
CREATE OR REPLACE PACKAGE BODY JOB_PKG AS
-- Implement the ADD_JOB procedure
PROCEDURE ADD_JOB(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2) IS
BEGIN
INSERT INTO JOBS (JOB_ID, JOB_TITLE)
VALUES (p_job_id, p_job_title);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Error: Job ID already exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END ADD_JOB;
-- Implement the UPD_JOB procedure
PROCEDURE UPD_JOB(p_job_id IN VARCHAR2, p_new_title IN VARCHAR2) IS
BEGIN
UPDATE JOBS
SET JOB_TITLE = p_new_title
WHERE JOB_ID = p_job_id;
COMMIT;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Job ID not found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Job updated successfully.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END UPD_JOB;
-- Implement the DEL_JOB procedure
PROCEDURE DEL_JOB(p_job_id IN VARCHAR2) IS
BEGIN
DELETE FROM JOBS WHERE JOB_ID = p_job_id;
COMMIT;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Job ID not found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Job deleted successfully.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END DEL_JOB;
-- Implement the GET_JOB function
FUNCTION GET_JOB(p_job_id IN VARCHAR2) RETURN VARCHAR2 IS
v_job_title VARCHAR2(35);
BEGIN
SELECT JOB_TITLE
INTO v_job_title
FROM JOBS
WHERE JOB_ID = p_job_id;
RETURN v_job_title;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RETURN NULL;
END GET_JOB;
END JOB_PKG;
PUNTO 2
CREATE OR REPLACE PACKAGE EMP_PKG AS
-- Public procedure to add an employee
PROCEDURE ADD_EMPLOYEE(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_job IN VARCHAR2 DEFAULT 'SA_REP',
p_mgr IN NUMBER DEFAULT 145,
p_sal IN NUMBER DEFAULT 1000,
p_comm IN NUMBER DEFAULT 0,
p_deptid IN NUMBER DEFAULT 30
);
-- Public procedure to get employee details by employee ID
PROCEDURE GET_EMPLOYEE(
p_employee_id IN NUMBER,
p_salary OUT NUMBER,
p_job OUT VARCHAR2
);
-- Private function to validate department ID
FUNCTION VALID_DEPTID(p_deptid IN NUMBER) RETURN BOOLEAN;
END EMP_PKG;
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
-- Implementation of the ADD_EMPLOYEE procedure
PROCEDURE ADD_EMPLOYEE(
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_job IN VARCHAR2 DEFAULT 'SA_REP',
p_mgr IN NUMBER DEFAULT 145,
p_sal IN NUMBER DEFAULT 1000,
p_comm IN NUMBER DEFAULT 0,
p_deptid IN NUMBER DEFAULT 30
) IS
BEGIN
-- Validate the department ID using the private function VALID_DEPTID
IF VALID_DEPTID(p_deptid) THEN
-- Insert employee into the EMPLOYEES table
INSERT INTO EMPLOYEES(
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
JOB_ID,
MANAGER_ID,
SALARY,
COMMISSION_PCT,
DEPARTMENT_ID,
HIRE_DATE
) VALUES (
EMPLOYEES_SEQ.NEXTVAL,
p_first_name,
p_last_name,
p_email,
p_job,
p_mgr,
p_sal,
p_comm,
p_deptid,
TRUNC(SYSDATE)
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee added successfully');
ELSE
DBMS_OUTPUT.PUT_LINE('Error: Invalid Department ID');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END ADD_EMPLOYEE;
-- Implementation of the GET_EMPLOYEE procedure
PROCEDURE GET_EMPLOYEE(
p_employee_id IN NUMBER,
p_salary OUT NUMBER,
p_job OUT VARCHAR2
) IS
BEGIN
-- Retrieve employee salary and job title based on employee ID
SELECT SALARY, JOB_ID
INTO p_salary, p_job
FROM EMPLOYEES
WHERE EMPLOYEE_ID = p_employee_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END GET_EMPLOYEE;
-- Private function to validate department ID
FUNCTION VALID_DEPTID(p_deptid IN NUMBER) RETURN BOOLEAN IS
v_exists NUMBER;
BEGIN
-- Check if the department exists in the DEPARTMENTS table
SELECT COUNT(*)
INTO v_exists
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = p_deptid;
-- Return TRUE if department exists, otherwise FALSE
IF v_exists > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END VALID_DEPTID;
END EMP_PKG;
