PL/SQL Basics for DBAs
What is PL/SQL?
PL/SQL is Oracle's programming language that runs inside the database. It lets you combine SQL with variables, loops and decisions. DBAs use it for maintenance scripts, jobs and quick automation.
The Block Structure
sql
SET SERVEROUTPUT ON
DECLARE
v_name VARCHAR2(30) := 'DBA';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello ' || v_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
| Section | Purpose |
|---|---|
DECLARE | Variables (optional) |
BEGIN ... END | The code to run (required) |
EXCEPTION | What to do when an error happens (optional) |
/ | Tells SQL*Plus to run the block |
Remember: Run
SET SERVEROUTPUT ON once per session or you will not see DBMS_OUTPUT messages.
Variables and IF
sql
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM shop.orders;
IF v_count > 5 THEN
DBMS_OUTPUT.PUT_LINE('Busy shop: ' || v_count || ' orders');
ELSE
DBMS_OUTPUT.PUT_LINE('Quiet shop');
END IF;
END;
/
Loops
sql
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Round ' || i);
END LOOP;
END;
/
-- loop over query rows (a cursor FOR loop)
BEGIN
FOR r IN (SELECT full_name, city FROM shop.customers) LOOP
DBMS_OUTPUT.PUT_LINE(r.full_name || ' - ' || NVL(r.city,'?'));
END LOOP;
END;
/
Stored Procedure
sql
CREATE OR REPLACE PROCEDURE shop.cancel_old_orders (p_days IN NUMBER) AS
v_rows NUMBER;
BEGIN
UPDATE shop.orders
SET status = 'CANCELLED'
WHERE status = 'NEW'
AND order_date < SYSDATE - p_days;
v_rows := SQL%ROWCOUNT;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_rows || ' orders cancelled');
END;
/
EXEC shop.cancel_old_orders(30)
SQL%ROWCOUNT: After an INSERT, UPDATE or DELETE,
SQL%ROWCOUNT tells you how many rows were affected. Great for logging.
Handling Errors
sql
BEGIN
INSERT INTO shop.customers (full_name, email) VALUES ('Dup', 'asha@example.com');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('That email already exists');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
RAISE;
END;
/
A DBA-Style Example
Find the biggest tables in a schema and print them:
sql
BEGIN
FOR r IN (SELECT segment_name, ROUND(bytes/1024/1024,2) AS mb
FROM user_segments
WHERE segment_type = 'TABLE'
ORDER BY bytes DESC
FETCH FIRST 5 ROWS ONLY) LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(r.segment_name,30) || r.mb || ' MB');
END LOOP;
END;
/
Conclusion
PL/SQL blocks, loops and procedures let you automate the boring parts of DBA work. Always handle errors and log what you changed.
Quick Summary: DECLARE, BEGIN, EXCEPTION, END plus a slash. Use SQL%ROWCOUNT and SET SERVEROUTPUT ON.

