PL/SQL Programming Examples - Part 2

This repository contains comprehensive PL/SQL examples demonstrating various techniques for working with databases in Oracle. Each example is organized into separate SQL files with clear documentation.

Table of Contents

1. %ROWTYPE - Whole Row Fetching
2. RECORD TYPE - Custom Columns
3. Basic Cursor - Multiple Rows
4. Cursor with Parameter
5. FOR LOOP with Cursor
6. Exception Handling with Custom Error

---

1. %ROWTYPE - Whole Row Fetching

File: 01_rowtype_example.sql

Description

%ROWTYPE is a PL/SQL attribute that allows you to declare a record variable that represents a whole row from a database table. It automatically includes all columns from the specified table.

When to Use

• When you need all columns from a table
• When you want to avoid declaring each variable manually
• When the table structure might change and you want automatic updates

Key Points

• SELECT INTO only works when exactly one row is returned
• If no rows are found: NO_DATA_FOUND exception is raised
• If multiple rows are found: TOO_MANY_ROWS exception is raised
• Always use exception handling with SELECT INTO

Example Structure

DECLARE
    emp_rec employees%ROWTYPE;
BEGIN
    SELECT * 
    INTO emp_rec
    FROM employees
    WHERE employee_id = 200;
    
    DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ' ' || emp_rec.salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many rows');
END;

Accessing Fields

Access individual fields using dot notation: record_variable.column_name

---

2. RECORD TYPE - Custom Columns

File: 02_record_type_example.sql

Description

Custom RECORD types allow you to define a structure with only the columns you need, giving you control over field names and data types.

When to Use

• When you only need some columns from a table
• When you want custom field names
• When you need to combine columns from multiple tables
• When you want better code readability with meaningful names

Key Points

• Define the record structure using TYPE ... IS RECORD
• Use %TYPE to inherit data types from table columns
• SELECT INTO still requires exactly one row
• More flexible than %ROWTYPE but requires manual definition

Example Structure

DECLARE
    TYPE employee_record IS RECORD(
        v_last employees.last_name%TYPE,
        v_sal employees.salary%TYPE
    );
    
    emp_acces employee_record;
BEGIN
    SELECT last_name, salary
    INTO emp_acces
    FROM employees
    WHERE employee_id = 200;
    
    DBMS_OUTPUT.PUT_LINE(emp_acces.v_last || ' ' || emp_acces.v_sal);
END;

Advantages

• Better performance (only fetches needed columns)
• Custom naming conventions
• Can combine multiple tables
• More readable code

---

3. Basic Cursor - Multiple Rows

File: 03_basic_cursor.sql

Description

Cursors are used to process multiple rows from a query result. Unlike SELECT INTO, cursors can handle zero, one, or many rows.

When to Use

• When you need to process multiple rows
• When the result set can be empty or have many rows
• When you need to iterate through query results

Key Points

• Declare the cursor with a SELECT statement
• OPEN the cursor before fetching
• FETCH rows one at a time in a loop
• CLOSE the cursor when done
• Use %NOTFOUND to check for end of result set

Cursor Lifecycle

1. DECLARE - Define the cursor and SQL query
2. OPEN - Execute the query and create result set
3. FETCH - Retrieve one row at a time
4. CLOSE - Release resources

Example Structure

DECLARE 
    CURSOR books_cur IS
        SELECT * FROM bk_books 
        WHERE authorid = 'J100';
    
    books_rec bk_books%ROWTYPE;
BEGIN
    OPEN books_cur;
    LOOP
        FETCH books_cur INTO books_rec;
        EXIT WHEN books_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(books_rec.title);
    END LOOP;
    CLOSE books_cur;
END;

Cursor Attributes

• %NOTFOUND - Returns TRUE if last fetch returned no row
• %FOUND - Returns TRUE if last fetch returned a row
• %ROWCOUNT - Returns number of rows fetched so far
• %ISOPEN - Returns TRUE if cursor is open

---

4. Cursor with Parameter

File: 04_cursor_with_parameter.sql

Description

Parameterized cursors accept parameters when opened, making them reusable with different values.

When to Use

• When you need to reuse a cursor with different criteria
• When you want to make your cursor more flexible
• When you need to pass values dynamically

Key Points

• Define parameters in the cursor declaration
• Pass values when opening the cursor: OPEN cursor_name(value)
• Parameters are used in the WHERE clause
• More efficient than using substitution variables

Example Structure

DECLARE 
    CURSOR books_cur(p_authorid bk_books.authorid%TYPE) IS
        SELECT * FROM bk_books
        WHERE authorid = p_authorid;
    
    books_rec bk_books%ROWTYPE;
BEGIN
    OPEN books_cur('J100');
    LOOP
        FETCH books_cur INTO books_rec;
        EXIT WHEN books_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(books_rec.title);
    END LOOP;
    CLOSE books_cur;
END;

Benefits

• Reusability - Same cursor, different parameters
• Performance - Better than dynamic SQL for repeated queries
• Type Safety - Parameters use %TYPE for type checking
• Flexibility - Easy to change search criteria

---

5. FOR LOOP with Cursor

File: 05_for_loop_cursor.sql

Description

The FOR LOOP cursor is the simplest and most efficient way to iterate through cursor results. It automatically handles OPEN, FETCH, and CLOSE operations.

When to Use

• When you want the simplest cursor implementation
• When you don't need fine-grained control over cursor operations
• Recommended for most cursor operations
• When you want automatic exception handling

Key Points

• No need to OPEN, FETCH, or CLOSE - handled automatically
• No need to declare record variable - implicitly created
• Automatic exception handling
• Cleaner and more readable code

Example Structure

DECLARE 
    CURSOR books_cur(p_authorid bk_books.authorid%TYPE) IS 
        SELECT * FROM bk_books
        WHERE authorid = p_authorid;
BEGIN
    FOR book_rec IN books_cur('J100') LOOP
        DBMS_OUTPUT.PUT_LINE(book_rec.title);
    END LOOP;
END;

Advantages

• Less code - No manual OPEN/FETCH/CLOSE
• Automatic cleanup - Cursor closed even if exception occurs
• Better performance - Oracle optimizes FOR LOOP cursors
• Error handling - Automatically handles cursor errors

Comparison

Feature	Manual Cursor	FOR LOOP Cursor
Code Lines	More	Fewer
OPEN/FETCH/CLOSE	Manual	Automatic
Record Declaration	Required	Implicit
Exception Handling	Manual	Automatic
Performance	Good	Better

---

6. Exception Handling with Custom Error

File: 06_exception_handling.sql

Description

Exception handling allows you to catch and handle database errors gracefully. PRAGMA EXCEPTION_INIT associates Oracle error codes with user-defined exceptions.

When to Use

• When you want to handle specific Oracle errors
• When you need user-friendly error messages
• When you want to prevent program crashes
• When dealing with integrity constraints

Key Points

• Define custom exception: exception_name EXCEPTION
• Associate with error code: PRAGMA EXCEPTION_INIT(exception_name, error_code)
• ORA-02292 (-2292): Foreign key constraint violated
• Always use exception handlers for data manipulation

Common Oracle Error Codes

• -0001 - Unique constraint violated
• -01403 - No data found
• -01422 - Too many rows
• -02291 - Foreign key constraint violated (parent key not found)
• -02292 - Foreign key constraint violated (child record exists)

Example Structure

DECLARE
    e_user_book EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_user_book, -2292);
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM bk_books
    WHERE authorid = 'J100';
    
    DELETE FROM bk_author
    WHERE authorid = 'J100';
    
EXCEPTION 
    WHEN e_user_book THEN 
        DBMS_OUTPUT.PUT_LINE('Cannot delete. Author has ' || v_count || ' book(s).');
END;

Exception Handling Best Practices

1. Always handle NO_DATA_FOUND when using SELECT INTO
2. Always handle TOO_MANY_ROWS when using SELECT INTO
3. Handle constraint violations for DELETE/UPDATE operations
4. Provide meaningful error messages to users
5. Log errors for debugging purposes

---

Quick Start Guide

Prerequisites

• Oracle Database (11g or higher)
• SQL*Plus or SQL Developer
• Appropriate table structures (employees, bk_books, bk_author, s_student, s_subject)

Running the Examples

1. Set up your environment:

   SET SERVEROUTPUT ON;
   SET VERIFY OFF;  -- For substitution variables

2. Run individual examples:

   @01_rowtype_example.sql
   @02_record_type_example.sql
   @03_basic_cursor.sql
   @04_cursor_with_parameter.sql
   @05_for_loop_cursor.sql
   @06_exception_handling.sql

3. Modify table names and values according to your database schema

Table Structures Expected

bk_books

• bookid (Primary Key)
• title
• authorid (Foreign Key)
• category
• pubdate

bk_author

• authorid (Primary Key)
• fname
• lname

employees

• employee_id (Primary Key)
• last_name
• salary

s_student

• studnr (Primary Key)
• surname

s_subject

• DIP_CODE (Primary Key)
• (Other columns with foreign key constraints)

---

Comparison Table

Method	Use Case	Rows Handled	Complexity	Performance
%ROWTYPE	Single row, all columns	1	Low	High
RECORD TYPE	Single row, some columns	1	Medium	High
Basic Cursor	Multiple rows, full control	Many	High	Medium
Cursor + Parameter	Multiple rows, reusable	Many	High	Medium
FOR LOOP Cursor	Multiple rows, simple	Many	Low	High

---

Decision Matrix

When to Use Each Method

Use %ROWTYPE when:

• You need exactly one row
• You need all columns
• Table structure may change

Use RECORD TYPE when:

• You need exactly one row
• You need only some columns
• You want custom field names

Use Cursor when:

• You need multiple rows
• Result set can be empty
• You need fine-grained control

Use FOR LOOP Cursor when:

• You need multiple rows
• You want simple, clean code
• Recommended for most cases

---

Common Issues and Solutions

Issue 1: NO_DATA_FOUND Exception

Problem: SELECT INTO finds no rows
Solution: Always use exception handling

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found');

Issue 2: TOO_MANY_ROWS Exception

Problem: SELECT INTO finds multiple rows
Solution: Use a cursor or add more specific WHERE clause

-- Use cursor instead of SELECT INTO
CURSOR cur IS SELECT * FROM table WHERE condition;

Issue 3: Cursor Not Closed

Problem: Forgetting to close cursor
Solution: Use FOR LOOP cursor or always close in exception handler

EXCEPTION
    WHEN OTHERS THEN
        IF cursor_name%ISOPEN THEN
            CLOSE cursor_name;
        END IF;

Issue 4: PRAGMA Spelling Error

Problem: Typo "PROGMA" instead of "PRAGMA"
Solution: Always use "PRAGMA EXCEPTION_INIT"

---

Best Practices

1. Always use exception handling with SELECT INTO
2. Prefer FOR LOOP cursors over manual cursor management
3. Use %TYPE and %ROWTYPE for type safety
4. Close cursors explicitly or use FOR LOOP
5. Provide meaningful error messages
6. Use parameters for reusable cursors
7. Test with edge cases (no rows, multiple rows)
8. Document your code with comments

---

Key Differences Summary

SELECT INTO vs Cursor

Feature	SELECT INTO	Cursor
Rows	Exactly 1	0, 1, or Many
Exception	Required	Optional
Performance	Faster (single fetch)	Slower (multiple fetches)
Use Case	Single row lookup	Multiple row processing

%ROWTYPE vs RECORD TYPE

Feature	%ROWTYPE	RECORD TYPE
Columns	All columns	Selected columns
Maintenance	Automatic	Manual
Performance	Fetches all	Fetches selected
Flexibility	Low	High
Naming	Table column names	Custom names

---

Additional Resources

• Oracle PL/SQL Documentation
• PL/SQL Language Reference
• Oracle Error Messages

---

Contributing

Feel free to submit issues, fork the repository, and create pull requests for any improvements.

---

License

This repository is for educational purposes. Use the code examples freely in your learning and projects.

---

Author

Created for PL/SQL learning and practice.