Tutorial Week 9-10
PL/SQL Advance
Today we are going to do some simple exercises involving PL/SQL. It is a programming language for Oracle Database that allows you to do much more complex things than SQL itself.
We are going to use HR database.
Exercise 1
Create and execute a simple anonymous block that outputs “Hello World”.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello World’)
END;
Exercise 2
Create a PL/SQL block that selects the maximum department ID in the Department table and stores it in a variable. Display the maximum department ID. Steps involved in that process:
a) Declare a variable of type NUMBER in the declarative section.
b) Start the executable section with the BEGIN keyword and include a SELECT statement to retrieve the maximum department ID¨
c) Display the variable and end the executable block.
Modify the above PL/SQL block to insert a new department (Name=’EDUCATION’) into the departments table. Use SQL%ROWCOUNT to display the number of rows that are affected. Execute a SELECT statement to check whether new department is inserted or not. Include a DELETE statement to delete the department that you added.
SET SERVEROUTPUT ON
DECLARE
v_dept_name departments.department_name%TYPE:= ‘Education’;
v_dept_id NUMBER;
v_max_deptno NUMBER;
BEGIN
SELECT MAX(department_id) INTO v_max_deptno FROM departments;
DBMS_OUTPUT.PUT_LINE(‘The maximum department_id is : ‘ ||
v_max_deptno);
v_dept_id := 10 + v_max_deptno;
INSERT INTO departments (department_id, department_name, location_id)
VALUES (v_dept_id,v_dept_name, NULL);
DBMS_OUTPUT.PUT_LINE (‘ SQL%ROWCOUNT gives ‘ || SQL%ROWCOUNT);
END;
/
SELECT * FROM departments WHERE department_id=280;
Exercise 3 (Loops and IF/ELSE statements)
Create a message table using following command:
DROP TABLE messages;
CREATE TABLE messages (results VARCHAR2(80));
Write a PL/SQL block to insert numbers in messages table from 1 to 10 excluding 6. Commit before end of the block
BEGIN
FOR i in 1..10 LOOP
IF i = 6 THEN
null;
ELSE
INSERT INTO messages(results)
VALUES (i);
END IF;
END LOOP;
COMMIT;
END;
/
SELECT * FROM messages;
Exercise 4 (Loops and IF/ELSE statements)
Execute following commands to create an emp table.
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
ALTER TABLE emp ADD stars VARCHAR2(50);
Create a PL/SQL block that inserts an asterisk in the stars column for every column for every $1000 of an employee’s salary. You can use default value for employee number as 176.
SET VERIFY OFF
DECLARE
v_empno emp.employee_id%TYPE := 176;
v_asterisk emp.stars%TYPE := NULL;
v_sal emp.salary%TYPE;
BEGIN
SELECT NVL(ROUND(salary/1000), 0) INTO v_sal
FROM emp WHERE employee_id = v_empno;
FOR i IN 1..v_sal
LOOP
v_asterisk := v_asterisk ||’*’;
END LOOP;
UPDATE emp SET stars = v_asterisk
WHERE employee_id = v_empno;
COMMIT;
END;
/
SELECT employee_id,salary, stars
FROM emp WHERE employee_id =176;
Exercise 5 (Cursors)
In this exercise,
· First, you use an explicit cursor to process a number of rows from a table and
populate another table with the results using a cursor FOR loop.
· Second, you write a PL/SQL block that processes information with two cursors,
including one that uses a parameter.
Create a PL/SQL block to perform following:
a) Declare a cursor which retrieves the last_name, salary, and manager_id of employees working in the department specified (deptno is a varialble)
b) In the executable section, use the cursor for loop to operate on the data retrieved. If the salary of the employee is less than 5,000 and if the manager ID is either 101 or 124, display the message “<
c) Test your PL/SQL block for the following Department IDs: 10,20,50,80
Now modify above PL/SQL block, to write two cursors-one without a parameter and one with a parameter. The first cursor retrieves the department number and the department name from the Department table for all departments whose ID number is less than 100. The second cursor receives the department number as a parameter, and retrieves employee details for those who work in that department and whose employee_id is less than 120.
You need to declare variables to hold the values retrieved from each cursor. Use the %Type attribute while declaring variables.
SET SERVEROUTPUT ON
SET VERIFY OFF
SET ECHO OFF
DECLARE
v_deptno NUMBER := 10;
CURSOR c_emp_cursor IS
SELECT last_name, salary,manager_id
FROM employees
WHERE department_id = v_deptno;
BEGIN
FOR emp_record IN c_emp_cursor
LOOP
IF emp_record.salary< 5000 AND (emp_record.manager_id=101 OR emp_record.manager_id=124) THEN
DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Due for a raise');
ELSE
DBMS_OUTPUT.PUT_LINE (emp_record.last_name || ' Not Due for a raise');
END IF;
END LOOP;
END;
SET SERVEROUTPUT ON
DECLARE
CURSOR c_dept_cursor IS
SELECT department_id,department_name
FROM departments
WHERE department_id< 100
ORDER BY department_id;
CURSOR c_emp_cursor(v_deptno NUMBER) IS
SELECT last_name,job_id,hire_date,salary
FROM employees
WHERE department_id = v_deptno
AND employee_id< 120;
v_current_deptno departments.department_id%TYPE;
v_current_dname departments.department_name%TYPE;
v_ename employees.last_name%TYPE;
v_job employees.job_id%TYPE;
v_hiredate employees.hire_date%TYPE;
v_sal employees.salary%TYPE;
BEGIN
OPEN c_dept_cursor;
LOOP
FETCH c_dept_cursor INTO v_current_deptno, v_current_dname;
EXIT WHEN c_dept_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Department Number : ' || v_current_deptno || ' Department Name : ' || v_current_dname);
IF c_emp_cursor%ISOPEN THEN
CLOSE c_emp_cursor;
END IF;
OPEN c_emp_cursor (v_current_deptno);
LOOP
FETCH c_emp_cursor INTO v_ename,v_job,v_hiredate,v_sal;
EXIT WHEN c_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_ename || ' ' || v_job || ' ' || v_hiredate || ' ' || v_sal);
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------');
CLOSE c_emp_cursor;
END LOOP;
CLOSE c_dept_cursor;
END;
Exercise 6 (Exception)
In this practice, you write a PL/SQL block that applies a predefined exception in order to process only one record at a time. The PL/SQL block selects the name of the employee with a given salary value.
1) Execute following command to re-create the message table
DROP TABLE messages;
CREATE TABLE messages (results VARCHAR2(80));
2) In the declarative section, declare two variables: v_ename of type employees.last_name and emp_sal of type employees.salary. Initialize the latter to 6000.
3) In the executable section, retrieve the last names of employees whose salaries are equal to the value emp_sal. If the salary entered returns only one row, insert into the message table the employee’s name and the salary amount.
Note: Do not use explicit cursors.
4) If the salary entered does not return any rows, handle the exception with an appropriate exception handler and insert into the messages table the message “No employee with a salary of
5) f the salary entered returns multiple rows, handle the exception with an appropriate exception handler and insert into the messages table the message More than one employee with a salary of
6) Handle any other exception with an appropriate exception handler and insert into the messages table the message Some other error occurred.
7) Display the rows from the messages table to check whether the PL/SQL block has executed successfully.
8) Change the initialized value of emp_salto 2000 and re-execute.
SET VERIFY OFF
DECLARE
v_ename employees.last_name%TYPE;
v_emp_sal employees.salary%TYPE := 6000;
BEGIN
SELECT last_name
INTO v_ename
FROM employees
WHERE salary = v_emp_sal;
INSERT INTO messages (results)
VALUES (v_ename || ‘ – ‘ || v_emp_sal);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO messages (results)
VALUES (‘No employee with a salary of ‘|| TO_CHAR(v_emp_sal));
WHEN too_many_rows THEN
INSERT INTO messages (results)
VALUES (‘More than one employee with a salary of ‘||
TO_CHAR(v_emp_sal));
WHEN others THEN
INSERT INTO messages (results)
VALUES (‘Some other error occurred.’);
END;
/
SELECT * FROM messages;
Exercise 7 (Trigger)
We would like to create a trigger on insert and update operation on employee table such that the employees’ salary should remain within a range according to their job type. Please follow these steps for that:
1. Create a trigger on employees table that fires before insert or update operation on each row. Trigger must should check the salary whether it is between the minimum and maximum range for salary for a specified job. If job salary does not fall into the range, then raise an error. To raise an application error, you can use following statement:
RAISE_APPLICATION_ERROR(-20100, “error in the salary range);
CREATE OR REPLACE TRIGGER check_salary_trg
BEFORE INSERT OR UPDATE OF job_id, salary
ON employees
FOR EACH ROW
Declare
v_minsal jobs.min_salary%type;
v_maxsal jobs.max_salary%type;
BEGIN
SELECT min_salary, max_salary INTO v_minsal, v_maxsal
FROM jobs
WHERE job_id = UPPER(:new.job_id);
IF :new.salary NOT BETWEEN v_minsal AND v_maxsal THEN
RAISE_APPLICATION_ERROR(-20100,
‘Invalid salary $’ ||:new.salary ||’. ‘||
‘Salaries for job ‘|| :new.job_id ||
‘ must be between $’|| v_minsal ||’ and $’ || v_maxsal);
END IF;
END;
Testing the trigger:
0. Now try by updating the salary of employee 115 to 2000. What is the result
0. Now update the salary of employee 115 to 2800. What is the result?
Now we want to write a trigger that prevent rows from being deleted during business hours i.e. Monday to Friday (9AM to 6PM). To get the hours from current date you use following function: TO_NUMBER(TO_CHAR(SYSDATE, ‘HH24’));
CREATE OR REPLACE TRIGGER delete_emp_trg
BEFORE DELETE ON employees
DECLARE
the_day VARCHAR2(3) := TO_CHAR(SYSDATE, ‘DY’);
the_hour PLS_INTEGER := TO_NUMBER(TO_CHAR(SYSDATE, ‘HH24’));
BEGIN
IF (the_hour BETWEEN 9 AND 18) AND (the_day NOT IN (‘SAT’,’SUN’)) THEN
RAISE_APPLICATION_ERROR(-20150,
‘Employee records cannot be deleted during the business hours of 9AM and 6PM’);
END IF;
END;
/
SHOW ERRORS
DELETE FROM employees
WHERE job_id = ‘SA_REP’
AND department_id IS NULL;