Scribd
Upload
4 views1 page

Exercise PLSQL

The document outlines a series of exercises for practicing PL/SQL programming. It includes tasks such as creating procedures and functions, handling exceptions, formatting output, and using dynamic SQL. Each exercise is accompanied by hints and examples for implementation and testing.

Uploaded by

oliyad147
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views1 page

Exercise PLSQL

The document outlines a series of exercises for practicing PL/SQL programming. It includes tasks such as creating procedures and functions, handling exceptions, formatting output, and using dynamic SQL. Each exercise is accompanied by hints and examples for implementation and testing.

Uploaded by

oliyad147
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Exercises

Note: Don’t forge to execute this before you start ‘SET SERVEROUTPUT ON’
1. Write a PL/SQL procedure that display the message “My first PL/SQL code”.

2. Write an anonymous PL/SQL block to display the number of employees in each department.

3. Display min, max, and avg salary for each job title, formatted as currency.

Hint: Use to_char to format. Examples:


SELECT TO_CHAR(1234.5, 'L99,999.00') FROM dual;
-- Output: $1,234.50
4. Create a function that divides two numbers with divide-by-zero handling.
-- After creating the function test it like this
SELECT safe_divide(10,0)FROM dual;
5. Write a procedure to display employee details, handling invalid IDs.
-- After creating the procedure test it using
EXEC get_employee(999);

6. Create a function to calculate annual salary (monthly salary * 12 + commission).

Note:- use NVL to handle employees who don’t have commission


The NVL function in PL/SQL is an Oracle-specific function that handles NULL values by
replacing them with a specified default value. It's commonly used to prevent NULL-related errors
in calculations, comparisons, and data display.
Syntax:
NVL(expression, replacement_value)
example:
SELECT student_name, NVL(score, 0) AS adjusted_score
FROM exam_results;
If score is NULL, displays 0 instead.
-- After creating the function test it using
SELECT first_name, salary, get_annual_salary(salary, commission_pct) annual_sal
FROM employees;
[Link] an employee to a new department with validation. The procedure should check whether the
department exists or not.
-- After creating the procedure test it using
EXEC transfer_employee(106,60);

8. Build a procedure that lets users choose which columns to display from the employees table.
Hint:- use dynamic sql. Example
DECLARE
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT COUNT(*) FROM employees';
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Total employees: ' || v_count);
END;
-- After creating the procedure test it using
EXEC show_employee_columns('employee_id, last_name, salary');

You might also like