INTERACTING WITH ORACLE DATABASE SERVER

1. Write a program that displays the Net Salary (SAL + COMM) of an employee entered at run time from the
EMP table. Use the NVL function to handle NULL commission values.

SET SERVEROUTPUT ON;

DECLARE

v_empno emp.empno%TYPE := &empno; -- runtime input

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

v_comm emp.comm%TYPE;

v_netsal NUMBER;

BEGIN

SELECT ename,

sal,

NVL(comm,0)

INTO v_ename,

v_sal,

v_comm

FROM emp

WHERE empno = v_empno;

v_netsal := v_sal + v_comm;

DBMS_OUTPUT.PUT_LINE('Employee: '||v_ename||' (EMPNO '||v_empno||')');

DBMS_OUTPUT.PUT_LINE('Salary : '||v_sal);

DBMS_OUTPUT.PUT_LINE('Comm : '||v_comm);

DBMS_OUTPUT.PUT_LINE('NetSal : '||v_netsal);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No employee found for EMPNO '||v_empno);

END;

/
WRITING CONTROL STRUCTURES

Great—here are PL/SQL solutions for all four hands-on tasks. You can run them in SQL*Plus or SQL
Developer. I’ve kept prompts (&...) and added minimal error handling/messages.

1) Insert/Update EMP by EMPNO (prompt for EMPNO, ENAME, SAL)

SET SERVEROUTPUT ON;

DECLARE
v_empno emp.empno%TYPE := &empno;
v_ename emp.ename%TYPE := UPPER('&ename');
v_sal emp.sal%TYPE := &sal;
v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM emp WHERE empno = v_empno;

IF v_exists = 0 THEN
-- INSERT with sensible defaults; change as per your lab if needed
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (v_empno, v_ename, 'CLERK', NULL, SYSDATE, v_sal, NULL, 10);

DBMS_OUTPUT.PUT_LINE('Inserted EMPNO='||v_empno||' ENAME='||v_ename||'

SAL='||v_sal);
ELSE
UPDATE emp
SET ename = v_ename,
sal = v_sal
WHERE empno = v_empno;

DBMS_OUTPUT.PUT_LINE('Updated EMPNO='||v_empno||' to ENAME='||

v_ename||', SAL='||v_sal);
END IF;

-- COMMIT; -- uncomment if your lab expects commit here

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;
/

2) Prompt a number and display ODD/EVEN

SET SERVEROUTPUT ON;

DECLARE
v_n NUMBER := &n;
BEGIN
IF MOD(v_n, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_n || ' is EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE(v_n || ' is ODD');
END IF;
END;
/

3) Update salary based on department rules (prompt EMPNO)

SET SERVEROUTPUT ON;

DECLARE
v_empno emp.empno%TYPE := &empno;
v_deptno emp.deptno%TYPE;
v_comm emp.comm%TYPE;
v_before emp.sal%TYPE;
v_after emp.sal%TYPE;
BEGIN
-- Fetch current details
SELECT deptno, NVL(comm,0), sal
INTO v_deptno, v_comm, v_before
FROM emp
WHERE empno = v_empno;

-- Apply the rule

IF v_deptno = 10 THEN
UPDATE emp SET sal = sal * 1.10 WHERE empno = v_empno;
ELSIF v_deptno = 20 THEN
UPDATE emp SET sal = sal * 1.15 WHERE empno = v_empno;
ELSE
UPDATE emp SET sal = sal + v_comm WHERE empno = v_empno;
END IF;

SELECT sal INTO v_after FROM emp WHERE empno = v_empno;

DBMS_OUTPUT.PUT_LINE('EMPNO '||v_empno||' updated: SAL '||v_before||' ->

'||v_after||
' (DEPT '||v_deptno||')');

-- COMMIT; -- uncomment if required

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found for EMPNO '||v_empno);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;
/

4) Create MYTABLE1(result NUMBER) and insert 1..10 skipping 6 & 8

SET SERVEROUTPUT ON;

DECLARE
e_table_missing EXCEPTION;
PRAGMA EXCEPTION_INIT(e_table_missing, -00942); -- table or view does not
exist
BEGIN
-- Drop if exists
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE mytable1';
DBMS_OUTPUT.PUT_LINE('Dropped existing MYTABLE1.');
EXCEPTION
WHEN e_table_missing THEN
 NULL; -- ignore
END;

-- Create table
EXECUTE IMMEDIATE 'CREATE TABLE mytable1 (result NUMBER)';
DBMS_OUTPUT.PUT_LINE('Created MYTABLE1(result NUMBER).');

-- Insert 1..10, skip 6 and 8

FOR i IN 1..10 LOOP
IF i IN (6,8) THEN
CONTINUE;
END IF;
INSERT INTO mytable1(result) VALUES (i);
END LOOP;

DBMS_OUTPUT.PUT_LINE('Inserted values 1..10 skipping 6 and 8.');

-- COMMIT; -- uncomment if required

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;
/
-- Optional verification
SELECT * FROM mytable1 ORDER BY result;

WORKING WITH COMPOSITE DATA TYPES

Write a PL/SQL program that accepts an EMPNO at runtime and displays the entire row from the EMP table.
Use a composite variable declared with emp%ROWTYPE.

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE show_emp_row (p_empno IN emp.empno%TYPE) IS

r emp%ROWTYPE;

BEGIN

SELECT * INTO r

FROM emp

WHERE empno = p_empno;

DBMS_OUTPUT.PUT_LINE('EMPNO : '|| r.empno);

DBMS_OUTPUT.PUT_LINE('ENAME : '|| r.ename);

DBMS_OUTPUT.PUT_LINE('JOB : '|| r.job);

DBMS_OUTPUT.PUT_LINE('MGR : '|| NVL(TO_CHAR(r.mgr),'NULL'));

DBMS_OUTPUT.PUT_LINE('HIREDATE: '|| TO_CHAR(r.hiredate,'DD-MON-YYYY'));

DBMS_OUTPUT.PUT_LINE('SAL : '|| r.sal);

 DBMS_OUTPUT.PUT_LINE('COMM : '|| NVL(TO_CHAR(r.comm),'NULL'));

DBMS_OUTPUT.PUT_LINE('DEPTNO : '|| r.deptno);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No employee found for EMPNO '|| p_empno);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('More than one row matched EMPNO '|| p_empno);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error: '|| SQLERRM);

END;

-- Run it:

EXEC show_emp_row(&empno);

EXPLICIT CURSOR

1) Question

Write an implicit-cursor program that prompts for a JOB and deletes the employees working in that job.
Store the number of rows deleted in a session variable.

Solution
-- Session variable package (create once per session)
CREATE OR REPLACE PACKAGE sess_pkg IS
g_deleted_count NUMBER := 0;
END;
/

SET SERVEROUTPUT ON;

DECLARE
v_job VARCHAR2(30) := UPPER('&job'); -- prompt
BEGIN
-- DELETE uses an implicit SQL cursor
DELETE FROM emp WHERE job = v_job;

-- Save affected-row count in a session variable

sess_pkg.g_deleted_count := SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE(sess_pkg.g_deleted_count||' row(s) deleted for

JOB='||v_job);
-- COMMIT; -- keep commented if you will do Q2 (ROLLBACK)
END;
/
2) Question

Rollback the previous delete statement.

Solution
SET SERVEROUTPUT ON;
BEGIN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(
'Rollback done. Last delete count stored = '||
NVL(sess_pkg.g_deleted_count,0)
);

-- Optional: reset the session variable

sess_pkg.g_deleted_count := 0;
END;
/

3) Question

Write a program that displays the Top N salaries using a simple explicit cursor.

Solution
SET SERVEROUTPUT ON;

DECLARE
v_n PLS_INTEGER := &N; -- prompt

CURSOR c_top (p_n NUMBER) IS

SELECT empno, ename, sal
FROM (
SELECT empno, ename, sal,
DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk
FROM emp
)
WHERE rnk <= p_n
ORDER BY sal DESC, ename;

r c_top%ROWTYPE;
BEGIN
OPEN c_top(v_n);
LOOP
FETCH c_top INTO r;
EXIT WHEN c_top%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(r.ename,10)||' '||r.sal);
END LOOP;
CLOSE c_top;
END;
/

4) Question

Create a simple explicit cursor that displays all employees earning more than the average salary of the
EMP table.
Solution
SET SERVEROUTPUT ON;

DECLARE
v_avg NUMBER;

CURSOR c_high (p_avg NUMBER) IS

SELECT empno, ename, sal
FROM emp
WHERE sal > p_avg
ORDER BY sal DESC, ename;

r c_high%ROWTYPE;
BEGIN
SELECT AVG(sal) INTO v_avg FROM emp;

OPEN c_high(v_avg);
LOOP
FETCH c_high INTO r;
EXIT WHEN c_high%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(r.ename,10)||' '||r.sal);
END LOOP;
CLOSE c_high;
END;
/

5) Question

Create a simple explicit cursor that displays the employees who earn more than 2000 and have joined after
15-Jun-1981.
Print the output as:
<ENAME> earns <SAL> and joined the organization on <HIREDATE>.

Solution
SET SERVEROUTPUT ON;

DECLARE
CURSOR c_emp IS
SELECT ename, sal, hiredate
FROM emp
WHERE sal > 2000
AND hiredate > DATE '1981-06-15'
ORDER BY sal DESC, hiredate;

v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_dt emp.hiredate%TYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_name, v_sal, v_dt;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
v_name||' earns '||v_sal||' and joined the organization on '||
TO_CHAR(v_dt,'DD-MON-YYYY')
);
END LOOP;
CLOSE c_emp;
END;
/

6) Question

Create a simple explicit cursor that displays HIREDATE in the format DD-MM-RRRR and Day.
Sort the records by Day starting from Saturday (Sat, Sun, Mon, …).

Solution
SET SERVEROUTPUT ON;

DECLARE
CURSOR c_day IS
SELECT ename,
TO_CHAR(hiredate,'DD-MM-RRRR') AS hire_dt,
-- Day name padded; RTRIM later to remove spaces
TO_CHAR(hiredate,'DAY','NLS_DATE_LANGUAGE=ENGLISH') AS day_name,
-- Order starting from Saturday
CASE TO_CHAR(hiredate,'DY','NLS_DATE_LANGUAGE=ENGLISH')
WHEN 'SAT' THEN 1
WHEN 'SUN' THEN 2
WHEN 'MON' THEN 3
WHEN 'TUE' THEN 4
WHEN 'WED' THEN 5
WHEN 'THU' THEN 6
WHEN 'FRI' THEN 7
ELSE 8
END AS sort_key
FROM emp
ORDER BY sort_key, ename;

v_name emp.ename%TYPE;
v_hire_txt VARCHAR2(20);
v_day VARCHAR2(20);
v_sort NUMBER;
BEGIN
OPEN c_day;
LOOP
FETCH c_day INTO v_name, v_hire_txt, v_day, v_sort;
EXIT WHEN c_day%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
RPAD(v_name,10)||' '||v_hire_txt||' '||RTRIM(v_day)
);
END LOOP;
CLOSE c_day;
END;
/

7) Simple explicit cursor — list employee names and commission, show “No Commission”

SET SERVEROUTPUT ON;

DECLARE
CURSOR c_comm IS
SELECT ename, NVL(TO_CHAR(comm), 'No Commission') AS comm_txt
FROM emp
ORDER BY ename;
 v_name emp.ename%TYPE;
v_comm_txt VARCHAR2(30);
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('ENAME',12)||'COMM');
DBMS_OUTPUT.PUT_LINE(RPAD('-',12,'-')||'----');

OPEN c_comm;
LOOP
FETCH c_comm INTO v_name, v_comm_txt;
EXIT WHEN c_comm%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD(v_name,12)||v_comm_txt);
END LOOP;
CLOSE c_comm;
END;
/

8) Parameter cursor — for a given DEPTNO, update STAR column with one * per ₹1000 salary

Uses parameter cursor, FOR LOOP and WHERE CURRENT OF.

Note: EMP should have a column STAR VARCHAR2(100) beforehand.

SET SERVEROUTPUT ON;

DECLARE
v_deptno dept.deptno%TYPE := &deptno;

CURSOR c_emp (p_deptno NUMBER) IS

SELECT empno, sal
FROM emp
WHERE deptno = p_deptno
FOR UPDATE; -- lock the rows for WHERE CURRENT OF

BEGIN
FOR r IN c_emp(v_deptno) LOOP
UPDATE emp
SET star = RPAD('*', TRUNC(NVL(r.sal,0) / 1000), '*')
WHERE CURRENT OF c_emp;
END LOOP;

DBMS_OUTPUT.PUT_LINE('STAR column updated for department '||v_deptno);

-- COMMIT; -- uncomment if your lab expects commit
END;
/
-- Optional check:
-- SELECT empno, ename, sal, star FROM emp WHERE deptno=&deptno ORDER BY
sal;

9) Parameter cursor — promote CLERK (>1000) to SR CLERK and raise salary by 10%

Uses parameter cursor, FOR LOOP, UPDATE … WHERE CURRENT OF.

SET SERVEROUTPUT ON;

DECLARE
v_job VARCHAR2(20) := UPPER('&job'); -- pass CLERK

CURSOR c_promote (p_job VARCHAR2) IS

 SELECT empno, ename, sal
FROM emp
WHERE job = p_job
AND sal > 1000
FOR UPDATE OF sal; -- lock row

BEGIN
FOR r IN c_promote(v_job) LOOP
UPDATE emp
SET job = 'SR CLERK',
sal = ROUND(sal * 1.10)
WHERE CURRENT OF c_promote;

DBMS_OUTPUT.PUT_LINE(r.ename||' promoted to SR CLERK; new SAL='||

ROUND(r.sal*1.10));
END LOOP;

-- COMMIT;
END;
/

10) Parameter cursor — show a DEPT row; update salaries by rule and list employees of that DEPT

 If DEPTNO = 10 → +15%
 If DEPTNO = 20 → +15%
 Otherwise → +5%

SET SERVEROUTPUT ON;

DECLARE
v_deptno dept.deptno%TYPE := &deptno;
r_dept dept%ROWTYPE;

CURSOR c_emp (p_deptno NUMBER) IS

SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = p_deptno
FOR UPDATE OF sal;

v_factor NUMBER;
BEGIN
-- Display DEPT row
SELECT * INTO r_dept FROM dept WHERE deptno = v_deptno;
DBMS_OUTPUT.PUT_LINE('DEPTNO: '||r_dept.deptno||' DNAME: '||
r_dept.dname||' LOC: '||r_dept.loc);

-- Update salaries for employees in that dept and display them

FOR r IN c_emp(v_deptno) LOOP
v_factor := CASE r.deptno WHEN 10 THEN 1.15 WHEN 20 THEN 1.15 ELSE 1.05
END;

UPDATE emp SET sal = ROUND(sal * v_factor)

WHERE CURRENT OF c_emp;

DBMS_OUTPUT.PUT_LINE(RPAD(r.ename,10)||' old='||r.sal||
' new='||ROUND(r.sal * v_factor));
END LOOP;

-- COMMIT;
END;
/

11) REF cursor — prompt for choice; show SAL > 2000 if choice=1 else SAL < 2000

SET SERVEROUTPUT ON;

DECLARE
TYPE rc IS REF CURSOR;
c rc;
v_choice NUMBER := &choice; -- 1 => >2000, otherwise <2000
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
IF v_choice = 1 THEN
OPEN c FOR
SELECT empno, ename, sal
FROM emp
WHERE sal > 2000
ORDER BY sal DESC, ename;
DBMS_OUTPUT.PUT_LINE('Employees with SAL > 2000');
ELSE
OPEN c FOR
SELECT empno, ename, sal
FROM emp
WHERE sal < 2000
ORDER BY sal DESC, ename;
DBMS_OUTPUT.PUT_LINE('Employees with SAL < 2000');
END IF;

LOOP
FETCH c INTO v_empno, v_ename, v_sal;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||' '||RPAD(v_ename,10)||' '||v_sal);
END LOOP;
CLOSE c;
END;
/

HANDLING EXCEPTIONS

1) Question (Named Exception Handlers)

Create a table MESSAGES(result VARCHAR2(1000)).

Write a PL/SQL block that prompts for a salary (SAL) and tries to fetch the employee name (ENAME) from
EMP (do not use explicit cursors).

 If exactly one row is found → insert ENAME||' '||SAL into MESSAGES.

 If more than one row is found → insert 'More than one employee with a salary
<SAL>'.
 If no row is found → insert 'No Employee with that salary <SAL>'.
 For any other error → insert 'Other Error'.
Use named exception handlers.
Solution
-- Create table once (ignore if already exists)
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE messages (result VARCHAR2(1000))';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN RAISE; END IF; -- ORA-00955 name already used
END;
/

SET SERVEROUTPUT ON;

DECLARE
v_sal emp.sal%TYPE := &sal;
v_ename emp.ename%TYPE;
BEGIN
-- Single-row select (raises NO_DATA_FOUND / TOO_MANY_ROWS automatically)
SELECT ename INTO v_ename
FROM emp
WHERE sal = v_sal;

INSERT INTO messages(result) VALUES (v_ename || ' ' || v_sal);

DBMS_OUTPUT.PUTLINE('Inserted: '||v_ename||' '||v_sal);

EXCEPTION
WHEN TOO_MANY_ROWS THEN
INSERT INTO messages(result)
VALUES ('More than one employee with a salary '||v_sal);
DBMS_OUTPUT.PUT_LINE('More than one employee at salary '||v_sal);

WHEN NO_DATA_FOUND THEN

INSERT INTO messages(result)
VALUES ('No Employee with that salary '||v_sal);
DBMS_OUTPUT.PUT_LINE('No employee found at salary '||v_sal);

WHEN OTHERS THEN

INSERT INTO messages(result) VALUES ('Other Error');
DBMS_OUTPUT.PUT_LINE('Other Error: '||SQLERRM);
END;
/
-- Optional check:
-- SELECT * FROM messages;

2) Question (Unnamed System-Defined Exception Handler)

Write a PL/SQL block that prompts for EMPNO, ENAME, SAL and inserts a row into EMP.
Use a single unnamed system-defined exception handler (WHEN OTHERS) to report any error.

Solution
SET SERVEROUTPUT ON;

DECLARE
v_empno emp.empno%TYPE := &empno;
v_ename emp.ename%TYPE := UPPER('&ename');
v_sal emp.sal%TYPE := &sal;
BEGIN
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (v_empno, v_ename, 'CLERK', NULL, SYSDATE, v_sal, NULL, 10);
 DBMS_OUTPUT.PUT_LINE('Inserted EMPNO='||v_empno||', ENAME='||v_ename||',
SAL='||v_sal);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Insert failed. Code='||SQLCODE||' Msg='||
SQLERRM);
-- ROLLBACK; -- uncomment if your lab expects rollback on error
END;
/

3) Question (User-Defined Exception)

Write a PL/SQL block that prompts for an EMPNO and deletes that employee from EMP.
If no such employee exists, raise and handle a user-defined exception.

Solution
SET SERVEROUTPUT ON;

DECLARE
v_empno emp.empno%TYPE := &empno;
e_no_such_emp EXCEPTION; -- user-defined exception
BEGIN
DELETE FROM emp WHERE empno = v_empno;

IF SQL%ROWCOUNT = 0 THEN
RAISE e_no_such_emp;
ELSE
DBMS_OUTPUT.PUT_LINE('Deleted employee with EMPNO '||v_empno);
END IF;

EXCEPTION
WHEN e_no_such_emp THEN
DBMS_OUTPUT.PUT_LINE('No employee found with EMPNO '||v_empno);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);
END;
/

4) Question (User-Defined Exceptions on Stock Transactions)

Create a table STOCK(pno NUMBER PRIMARY KEY, pname VARCHAR2(50), rate

NUMBER(10,2), tr_qty NUMBER), and insert a few records.
Write a PL/SQL block that prompts for pno, tr_type ('R'=receive, 'I'=issue) and tr_qty.

 If tr_type='R' → tr_qty := tr_qty + new_qty.

 If tr_type='I' → tr_qty := tr_qty - new_qty (but must not go below zero).
Handle the following as user-defined exceptions: product not found, invalid transaction type,
insufficient stock.

Solution
-- Create table once
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE stock (
pno NUMBER PRIMARY KEY,
pname VARCHAR2(50),
 rate NUMBER(10,2),
tr_qty NUMBER
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN RAISE; END IF;
END;
/

-- Seed data (ignore duplicates if re-run)

BEGIN
INSERT INTO stock VALUES (101, 'PEN', 10.00, 100);
INSERT INTO stock VALUES (102, 'BOOK', 50.00, 40);
INSERT INTO stock VALUES (103, 'BAG', 300.00, 15);
COMMIT;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

SET SERVEROUTPUT ON;

DECLARE
v_pno stock.pno%TYPE := &pno;
v_type CHAR(1) := UPPER('&tr_type'); -- 'R' or 'I'
v_qty NUMBER := &tr_qty;
v_old stock.tr_qty%TYPE;
v_new stock.tr_qty%TYPE;

e_pno_not_found EXCEPTION;
e_invalid_type EXCEPTION;
e_insufficient_stock EXCEPTION;
BEGIN
-- Fetch & lock row; convert NO_DATA_FOUND to user exception
BEGIN
SELECT tr_qty INTO v_old
FROM stock
WHERE pno = v_pno
FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE e_pno_not_found;
END;

-- Validate type
IF v_type NOT IN ('R','I') THEN
RAISE e_invalid_type;
END IF;

-- Apply business rule

IF v_type = 'R' THEN
UPDATE stock SET tr_qty = tr_qty + v_qty WHERE pno = v_pno;
ELSE -- 'I'
IF v_qty > v_old THEN
RAISE e_insufficient_stock;
END IF;
UPDATE stock SET tr_qty = tr_qty - v_qty WHERE pno = v_pno;
END IF;

SELECT tr_qty INTO v_new FROM stock WHERE pno = v_pno;

DBMS_OUTPUT.PUT_LINE('PNO '||v_pno||': qty '||v_old||' -> '||v_new||
' (Type='||v_type||', Qty='||v_qty||')');
 -- COMMIT; -- uncomment if persistence is required

EXCEPTION
WHEN e_pno_not_found THEN
DBMS_OUTPUT.PUT_LINE('Error: PNO '||v_pno||' not found.');
WHEN e_invalid_type THEN
DBMS_OUTPUT.PUT_LINE('Error: Invalid TR_TYPE. Use ''R'' or ''I''.');
WHEN e_insufficient_stock THEN
DBMS_OUTPUT.PUT_LINE('Error: Insufficient stock. Available='||v_old||',
requested='||v_qty);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Other error: '||SQLERRM);
END;
/
-- Optional check:
-- SELECT * FROM stock ORDER BY pno;