Set 4 — PL/SQL Blocks, Functions and Procedures (EMP, JOB, ITEM, WORKER)
- Anonymous block
- Cursor FOR loop
- Stored function
- Parameters
- Stored procedure
- ROWNUM
- DBMS_OUTPUT
- Subject
- MC01094031 RDBMS
- Tool
- Oracle 10g or above
- Questions
- 4
- Status
- Question list: official GTU PDF
Introduction
Set 4 moves from single SQL statements to PL/SQL — Oracle's procedural language. A PL/SQL block can declare variables, loop over query results with a cursor and print messages with DBMS_OUTPUT. Stored functions and procedures are named blocks saved in the database so that they can be called again and again.
Learning objectives
- Write and run an anonymous PL/SQL block.
- Use a cursor FOR loop to process query rows one by one.
- Create stored functions that return a value, with and without parameters.
- Create a stored procedure and call it with EXEC.
- Select the top N rows in Oracle 10g using ROWNUM.
Original question list
- a. Write a PLSQL block which will print Employee list (Empno and Name) EMP (empno, empnm, empadd, salary, date_birth, joindt, deptno)
- b. Write a function that returns total number of incomplete jobs, using table JOB (jobid, type_of_job, status)
- c. Write a function which displays the number of items whose weight fall between a given ranges for a particular color using table ITEM (itemno, name, color, weight)
- d. Write a procedure to display top five highest paid workers who are specialized in “PAINTING" using table WORKER (workerid, name, wage_per_hour, specialized_in, manager_id)
Database schema
EMP
| Column | Oracle type | Meaning / constraint |
|---|---|---|
empno | NUMBER(4) | Primary key |
empnm | VARCHAR2(25) | Employee name |
empadd | VARCHAR2(30) | Address |
salary | NUMBER(9,2) | |
date_birth | DATE | |
joindt | DATE | Joining date |
deptno | NUMBER(2) |
JOB
| Column | Oracle type | Meaning / constraint |
|---|---|---|
jobid | NUMBER(4) | Primary key |
type_of_job | VARCHAR2(30) | |
status | VARCHAR2(10) | 'COMPLETE' or 'INCOMPLETE' (assumed values) |
ITEM
| Column | Oracle type | Meaning / constraint |
|---|---|---|
itemno | NUMBER(4) | Primary key |
name | VARCHAR2(20) | |
color | VARCHAR2(10) | |
weight | NUMBER(6,2) | kg |
WORKER
| Column | Oracle type | Meaning / constraint |
|---|---|---|
workerid | NUMBER(4) | Primary key |
name | VARCHAR2(20) | |
wage_per_hour | NUMBER(7,2) | |
specialized_in | VARCHAR2(15) | e.g. PAINTING |
manager_id | NUMBER(4) | FK → WORKER(workerid) — the worker's manager |
Prerequisites
- Run
SET SERVEROUTPUT ONonce per SQL*Plus session; otherwise DBMS_OUTPUT messages are not displayed. - In SQL*Plus, a PL/SQL block or CREATE PROCEDURE/FUNCTION must end with a line containing only
/to be executed. - In SQL Developer, run the whole script with Run Script (F5) and open *View → Dbms Output* if output does not appear.
- Set 6 uses the same EMP structure, so the EMP table created here can be reused there (Set 6 also needs DEPT).
Optional clean-up — run this first if you have attempted the practical before. Errors such as *ORA-00942: table or view does not exist* are harmless on the first run.
DROP TABLE worker PURGE;
DROP TABLE item PURGE;
DROP TABLE job PURGE;
DROP TABLE emp CASCADE CONSTRAINTS PURGE;Table creation & sample data
Step 1 Create the EMP, JOB, ITEM and WORKER tables
CREATE TABLE emp (
empno NUMBER(4) PRIMARY KEY,
empnm VARCHAR2(25) NOT NULL,
empadd VARCHAR2(30),
salary NUMBER(9,2) CHECK (salary > 0),
date_birth DATE,
joindt DATE,
deptno NUMBER(2)
);
CREATE TABLE job (
jobid NUMBER(4) PRIMARY KEY,
type_of_job VARCHAR2(30) NOT NULL,
status VARCHAR2(10) DEFAULT 'INCOMPLETE'
CHECK (status IN ('COMPLETE', 'INCOMPLETE'))
);
CREATE TABLE item (
itemno NUMBER(4) PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
color VARCHAR2(10),
weight NUMBER(6,2) CHECK (weight > 0)
);
CREATE TABLE worker (
workerid NUMBER(4) PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
wage_per_hour NUMBER(7,2) CHECK (wage_per_hour > 0),
specialized_in VARCHAR2(15),
manager_id NUMBER(4) REFERENCES worker (workerid)
);Explanation
Column names follow the PDF. manager_id refers back to the same table (a self-referencing foreign key): a worker's manager is another worker.
Table created. (4 times)
Step 2 Insert sample data
INSERT INTO emp VALUES (1, 'Anil Kapoor', 'Ahmedabad', 52000, TO_DATE('14-02-1985','DD-MM-YYYY'), TO_DATE('01-06-2012','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (2, 'Bina Shah', 'Surat', 38000, TO_DATE('22-08-1992','DD-MM-YYYY'), TO_DATE('15-07-2019','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (3, 'Chetan Rao', 'Vadodara', 61000, TO_DATE('05-11-1983','DD-MM-YYYY'), TO_DATE('10-01-2015','DD-MM-YYYY'), 20);
INSERT INTO emp VALUES (4, 'Deepa Iyer', 'Rajkot', 29000, TO_DATE('17-03-1998','DD-MM-YYYY'), TO_DATE('03-02-2023','DD-MM-YYYY'), 10);
INSERT INTO emp VALUES (5, 'Esha Nair', 'Anand', 47000, TO_DATE('30-09-1990','DD-MM-YYYY'), TO_DATE('20-04-2018','DD-MM-YYYY'), 20);
INSERT INTO job VALUES (1, 'Website redesign', 'COMPLETE');
INSERT INTO job VALUES (2, 'Payroll module', 'INCOMPLETE');
INSERT INTO job VALUES (3, 'Database backup plan', 'COMPLETE');
INSERT INTO job VALUES (4, 'Inventory report', 'INCOMPLETE');
INSERT INTO job VALUES (5, 'Network upgrade', 'INCOMPLETE');
INSERT INTO job VALUES (6, 'User training', 'COMPLETE');
INSERT INTO item VALUES (1, 'Chair', 'RED', 7.50);
INSERT INTO item VALUES (2, 'Table', 'BROWN', 25.00);
INSERT INTO item VALUES (3, 'Lamp', 'RED', 2.20);
INSERT INTO item VALUES (4, 'Shelf', 'RED', 15.00);
INSERT INTO item VALUES (5, 'Stool', 'BLUE', 4.80);
INSERT INTO item VALUES (6, 'Cabinet', 'RED', 40.00);
INSERT INTO item VALUES (7, 'Mirror', 'BLUE', 6.00);
INSERT INTO worker VALUES (1, 'Kamlesh', 450, 'PAINTING', NULL);
INSERT INTO worker VALUES (2, 'Suresh', 380, 'PLUMBING', 1);
INSERT INTO worker VALUES (3, 'Mahesh', 400, 'PAINTING', 1);
INSERT INTO worker VALUES (4, 'Dinesh', 250, 'PAINTING', 1);
INSERT INTO worker VALUES (5, 'Naresh', 330, 'CARPENTRY', 1);
INSERT INTO worker VALUES (6, 'Ramesh', 300, 'PAINTING', 1);
INSERT INTO worker VALUES (7, 'Hitesh', 350, 'PAINTING', 3);
INSERT INTO worker VALUES (8, 'Jignesh', 200, 'PAINTING', 3);
INSERT INTO worker VALUES (9, 'Paresh', 420, 'PAINTING', 1);
INSERT INTO worker VALUES (10, 'Rakesh', 390, 'ELECTRICAL', 1);
COMMIT;Explanation
WORKER has seven painters so that the top-5 procedure has something to leave out. Kamlesh (no manager) is inserted first because the others refer to him.
1 row created. (28 times) Commit complete.
Solutions
Qa Write a PLSQL block which will print Employee list (Empno and Name) EMP (empno, empnm, empadd, salary, date_birth, joindt, deptno)
SET SERVEROUTPUT ON
DECLARE
CURSOR c_emp IS
SELECT empno, empnm
FROM emp
ORDER BY empno;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPNO NAME');
DBMS_OUTPUT.PUT_LINE('----- ---------------');
FOR r IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(r.empno, 8) || r.empnm);
END LOOP;
END;
/Explanation
Structure: DECLARE (optional declarations) → BEGIN (executable statements) → END;. The / on its own line tells SQL*Plus to send the block to Oracle.
The cursor FOR loop opens the cursor, fetches each row into the record r and closes the cursor automatically — no OPEN, FETCH or CLOSE statements are needed. r.empno and r.empnm are the columns of the current row.
DBMS_OUTPUT.PUT_LINE writes one line to a buffer that SQL*Plus prints after the block finishes (only when SERVEROUTPUT is ON). RPAD(x, 8) pads the number to 8 characters so the names line up.
EMPNO NAME ----- --------------- 1 Anil Kapoor 2 Bina Shah 3 Chetan Rao 4 Deepa Iyer 5 Esha Nair PL/SQL procedure successfully completed.
Qb Write a function that returns total number of incomplete jobs, using table JOB (jobid, type_of_job, status)
CREATE OR REPLACE FUNCTION fn_incomplete_jobs
RETURN NUMBER
IS
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM job
WHERE status = 'INCOMPLETE';
RETURN v_count;
END fn_incomplete_jobs;
/
-- call it from SQL
SELECT fn_incomplete_jobs AS incomplete_jobs FROM dual;Explanation
A function must return exactly one value; its type is declared after RETURN. SELECT ... INTO stores the query result in a PL/SQL variable — it must return exactly one row, which COUNT(*) always does.
Because the function only reads data, it can be called inside a SQL statement such as SELECT ... FROM dual, or from PL/SQL: DBMS_OUTPUT.PUT_LINE(fn_incomplete_jobs);.
Function created.
SQL> SELECT fn_incomplete_jobs AS incomplete_jobs FROM dual;
INCOMPLETE_JOBS
---------------
3Qc Write a function which displays the number of items whose weight fall between a given ranges for a particular color using table ITEM (itemno, name, color, weight)
CREATE OR REPLACE FUNCTION fn_count_items (
p_min_weight IN NUMBER,
p_max_weight IN NUMBER,
p_color IN VARCHAR2
) RETURN NUMBER
IS
v_count NUMBER;
BEGIN
IF p_min_weight > p_max_weight THEN
RAISE_APPLICATION_ERROR(-20010,
'Minimum weight cannot be greater than maximum weight.');
END IF;
SELECT COUNT(*)
INTO v_count
FROM item
WHERE weight BETWEEN p_min_weight AND p_max_weight
AND UPPER(color) = UPPER(p_color);
DBMS_OUTPUT.PUT_LINE('Items of colour ' || UPPER(p_color) || ' weighing '
|| p_min_weight || ' to ' || p_max_weight || ': ' || v_count);
RETURN v_count;
END fn_count_items;
/
DECLARE
v NUMBER;
BEGIN
v := fn_count_items(5, 20, 'red');
END;
/Explanation
IN parameters pass the range and colour into the function. Comparing UPPER(color) = UPPER(p_color) makes the colour test case-insensitive, so 'red', 'Red' and 'RED' all work.
The question says the function *displays* the number, so it prints it with DBMS_OUTPUT and returns it, which keeps it usable in other code. For 5–20 kg and red, the matching items are Chair (7.50) and Shelf (15.00); Lamp is too light and Cabinet too heavy. The input check uses RAISE_APPLICATION_ERROR to report a meaningful error instead of silently returning 0.
Function created. SQL> DECLARE 2 v NUMBER; 3 BEGIN 4 v := fn_count_items(5, 20, 'red'); 5 END; 6 / Items of colour RED weighing 5 to 20: 2 PL/SQL procedure successfully completed.
Qd Write a procedure to display top five highest paid workers who are specialized in “PAINTING" using table WORKER (workerid, name, wage_per_hour, specialized_in, manager_id)
CREATE OR REPLACE PROCEDURE p_top5_painters
IS
CURSOR c_top IS
SELECT *
FROM (SELECT workerid, name, wage_per_hour
FROM worker
WHERE specialized_in = 'PAINTING'
ORDER BY wage_per_hour DESC)
WHERE ROWNUM <= 5;
v_rank NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('Top 5 highest paid PAINTING workers');
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
DBMS_OUTPUT.PUT_LINE('RANK ID NAME WAGE/HOUR');
FOR r IN c_top LOOP
v_rank := v_rank + 1;
DBMS_OUTPUT.PUT_LINE(RPAD(v_rank, 6) || RPAD(r.workerid, 6) ||
RPAD(r.name, 14) || r.wage_per_hour);
END LOOP;
IF v_rank = 0 THEN
DBMS_OUTPUT.PUT_LINE('No PAINTING workers found.');
END IF;
END p_top5_painters;
/
EXEC p_top5_paintersExplanation
"Highest paid" is measured by wage_per_hour, the only pay column in WORKER.
ROWNUM is assigned before ORDER BY in the same query, so WHERE ROWNUM <= 5 ORDER BY wage_per_hour DESC would pick any five painters and then sort them. Sorting in an inline view first and applying ROWNUM outside is the correct Oracle 10g pattern.
EXEC p_top5_painters is SQL*Plus shorthand for BEGIN p_top5_painters; END;. A procedure, unlike a function, does not return a value; it performs an action (here, printing).
Procedure created. SQL> EXEC p_top5_painters Top 5 highest paid PAINTING workers ----------------------------------- RANK ID NAME WAGE/HOUR 1 1 Kamlesh 450 2 9 Paresh 420 3 3 Mahesh 400 4 7 Hitesh 350 5 6 Ramesh 300 PL/SQL procedure successfully completed.
Important concepts used
- PL/SQL block
- DECLARE … BEGIN … EXCEPTION … END; — only BEGIN and END are mandatory.
- Cursor FOR loop
- Implicitly opens, fetches and closes a cursor; the loop record holds the current row.
- Function vs procedure
- A function returns one value and can be used in SQL; a procedure performs actions and is called as a statement.
- SELECT … INTO
- Copies a single-row query result into variables; raises NO_DATA_FOUND / TOO_MANY_ROWS otherwise.
- ROWNUM
- Pseudo-column numbering rows as they are returned; apply it after sorting in an inline view.
- Self-referencing foreign key
- WORKER.manager_id refers to WORKER.workerid.
Common mistakes
- Forgetting
SET SERVEROUTPUT ONand seeing only *PL/SQL procedure successfully completed*. - Forgetting the
/after a block, so SQL*Plus keeps waiting for more input. - Using
ROWNUM <= 5andORDER BYin the same query level. - Declaring a parameter with a size, e.g.
p_color VARCHAR2(10)— parameters take a type without size. - Not checking
SHOW ERRORSafter *created with compilation errors*.
Viva questions
What are the sections of a PL/SQL block?
What is the difference between a procedure and a function?
What is a cursor?
What does SET SERVEROUTPUT ON do?
What are IN, OUT and IN OUT parameters?
How do you select the top 5 rows in Oracle 10g?
Summary
An anonymous block runs once; stored functions and procedures are compiled and kept in the database. Use a function when you need a single value (and possibly call it from SQL), a procedure when you need to perform an action. Cursor FOR loops are the simplest safe way to process multiple rows.
Download script
set-04-pl-sql-blocks-functions-and-procedures.sql
Related practicals
Independent educational resource — not an official GTU publication. Verify questions and solutions with your current syllabus and lab instructor. Disclaimer