PL/SQL – Part 3

Posted byRajalakshmi MPosted inpl/sqlTags:, , , , ,

%ROWTYPE:

  • %ROWTYPE in PL/SQL is a special keyword that lets us create a variable which automatically has the same structure as a row in a table. It saves us from having to declare individual variables for each column in the table.
variable_name table_name%ROWTYPE;

Screenshot 1:

Screenshot 2:

Screenshot 1, We are fetching specific columns (emp_id and emp_name) from a row in the xx1195_emp table where emp_id is 101. We declare two variables (l_emp_id and l_emp_name) to hold these values separately.

In the Screenshot 2, We are fetching the entire row where emp_id is 101 from the xx1195_emp table. But instead of declaring individual variables for each column, we declare just one variable (l_emp) using %ROWTYPE, which automatically matches the structure of a row in the xx1195_emp table.

So, %ROWTYPE allows us to create a variable that holds all the columns of a row in a table at once, making our code more concise and easier to manage.

Ex: 1

DECLARE
    l_emp xx1195_emp%ROWTYPE; 
BEGIN
    SELECT * INTO l_emp FROM xx1195_emp WHERE emp_id = 101;

    INSERT INTO xx1195_emp VALUES(106, 'Leila', l_emp.salary);
    
    COMMIT;
END;

CURSOR

We can handle cursor in 2 ways:

1] Implicit Cursors:

  • Automatically created by Oracle.
  • No need for explicit declaration.
  • Used for single-row queries (SELECT INTO) or DML statements (INSERT, UPDATE, DELETE) where results are not fetched explicitly.

2] Explicit Cursors:

  • Defined by the programmer using the CURSOR declaration.
  • Provides more control over fetching process.
  • Requires manual opening, fetching, and closing of the cursor.
  • Useful for complex scenarios where manipulation of fetched data is needed.

EXPLICIT CURSOR

  • An explicit cursor acts as a container for the result set returned by a select query.
  • It allows us to work with the rows of data returned by the query one at a time, enabling us to process each row individually through fetch operations.
  • We can think of it as an array-like structure in the sense that it holds the result set, and we can iterate over it to access each row’s data.

Explicit Cursor Declaration:

CURSOR cursor_name IS select_statement;

Explicit Cursor have 2 approach:

  • Open Fetch Close Method
  • For Loop Method

1] Explicit Cursor – Open Fetch Close Method

Opening the cursor:

OPEN cursor_name;

Fetching data:

FETCH cursor_name INTO variable_name;

Closing the cursor:

CLOSE cursor_name;

Ex: 1

DECLARE
  -- Declare a cursor
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
  
  -- Declare variables to hold the data fetched from the cursor
  l_record xx1195_emp%ROWTYPE;
BEGIN
  -- Open the cursor
  OPEN l_cursor;
  
  -- Fetch data from the cursor into variables
  LOOP
    FETCH l_cursor INTO l_record;
    
    -- Exit the loop if no more rows to fetch
    EXIT WHEN l_cursor%NOTFOUND;
    
    -- Process the fetched data
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
  END LOOP;
  
  -- Close the cursor
  CLOSE l_cursor;
END;

Above code explanation using the analogy of a shopping trip, which helps make the concept more understandable:

  • DECLARE: Here, we declare our “shopping list” (CURSOR) named l_cursor, which contains all the items (rows) we want to fetch from the store (database table xx1195_emp). We also declare a placeholder bag (variable) named l_record to hold each item (row) we fetch.
  • BEGIN: This marks the beginning of our shopping trip (PL/SQL block).
  • OPEN l_cursor: We’re entering the grocery store (database) with our shopping list and an empty bag, ready to start fetching items.
  • LOOP: Like going through our shopping list, we enter a loop to fetch items one by one.
  • FETCH l_cursor INTO l_record: We’re picking up an item (row) from the shelf (database table) and putting it in our bag (variable).
  • EXIT WHEN l_cursor%NOTFOUND: We check if there are any more items left on our shopping list. If there aren’t, we exit the loop.
  • PROCESS: We process each fetched item by displaying its details (in this case, printing the employee ID and name).
  • CLOSE l_cursor: We’re leaving the store after getting all our items and closing the door behind us.
  • END: Marks the end of our shopping trip (PL/SQL block).
  • So, in summary, this PL/SQL block opens a cursor to fetch rows from a database table, iterates through each fetched row, processes the data, and finally closes the cursor when done.

Ex 1:

DECLARE
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
  l_record xx1195_emp%ROWTYPE;
BEGIN
  OPEN l_cursor;
  FETCH l_cursor INTO l_record;
  DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
  CLOSE l_cursor;
END;

Mistake: This block fetches only the first row from the cursor and then immediately closes the cursor. It doesn’t use any loop to fetch and process all rows.

Ex: 2

DECLARE
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
  l_record xx1195_emp%ROWTYPE;
BEGIN
  OPEN l_cursor;
  LOOP
    FETCH l_cursor INTO l_record;
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
  END LOOP;
  CLOSE l_cursor;
END;

Mistake: This block enters an infinite loop because it don’t have an exit condition. It will keep fetching rows from the cursor endlessly, so the loop won’t terminate, resulting in an infinite loop.

Ex: 3

DECLARE
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
  l_record xx1195_emp%ROWTYPE;
BEGIN
  OPEN l_cursor;
  LOOP
    FETCH l_cursor INTO l_record;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
  END LOOP;
  CLOSE l_cursor;
  DBMS_OUTPUT.PUT_LINE('Outside of loop: -> ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
END;
  • This block correctly exits the loop when no more rows are found, but it attempts to print the values of l_record outside the loop, which would result in printing the last fetched row after the loop has finished, not during the loop execution.

2] Explicit Cursor – For Loop Method

Ex: 1

DECLARE
  -- Declare a cursor
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
BEGIN
  -- Loop through the cursor result set
  FOR l_record IN l_cursor LOOP
    -- Process the fetched data
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id || ', Name: ' || l_record.emp_name);
  END LOOP;
END;

Explanation:

  • DECLARE: We declare a cursor named l_cursor to fetch all columns from the xx1195_emp table.
  • BEGIN: Starts the PL/SQL block.
  • FOR l_record IN l_cursor LOOP: This FOR loop automatically opens the cursor, fetches each row into l_record, processes it, and closes the cursor when done. It handles all the opening and closing operations implicitly, making the code more concise and easier to read.
  • DBMS_OUTPUT.PUT_LINE: Displays the fetched data (employee ID and name in this case).
  • END: Marks the end of the PL/SQL block.
  • Using the FOR loop directly with the cursor avoids the need for explicit opening and closing of the cursor, simplifying the code structure.

Ex: 2

DECLARE
  CURSOR l_cursor IS SELECT * FROM xx1195_emp;
BEGIN
  FOR l_record IN l_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id);
  END LOOP;
    DBMS_OUTPUT.PUT_LINE('ID: ' || l_record.emp_id);
END;

Mistake:

  • After the loop ends, there’s an attempt to access l_record.emp_id again. However, l_record is only defined within the loop scope, so it’s not accessible outside of it.
  • This results in a compilation error because l_record is not recognized outside of the loop scope.

Ex: 3

DECLARE
CURSOR l_cur IS SELECT emp_id ID, emp_name FROM xx1195_emp;
BEGIN
FOR I IN l_cur
LOOP
dbms_output.put_line(I.emp_id);
END LOOP;
END;

Mistake:

  • The mistake lies in trying to access I.emp_id.
  • Since emp_id is aliased as ID in the cursor query, it should be accessed as I.ID instead of I.emp_id.

CURSOR ATTRIBUTES:

  • %ISOPEN: This attribute checks whether a cursor is open or closed. It returns TRUE if the cursor is currently open and FALSE otherwise.
  • %ROWCOUNT: This attribute returns the number of rows fetched or affected by the most recent cursor operation.
  • %FOUND: This attribute returns TRUE if the last fetch or DML operation returned a row. It’s used after a fetch operation to check if a row was found.
  • %NOTFOUND: This attribute returns TRUE if the last fetch or DML operation did not return any rows. It’s the opposite of %FOUND.

Ex: 1

DECLARE
  CURSOR l_cursor IS SELECT emp_id, emp_name FROM xx1195_emp;
  l_record l_cursor%ROWTYPE;
BEGIN
  FOR i IN l_cursor LOOP 
    -- Check if the cursor is open
    IF l_cursor%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('Cursor is open.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Cursor is closed.');
    END IF;

    -- Check if a row was fetched
    IF l_cursor%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Row found: ID=' || i.emp_id || ', Name=' || i.emp_name);
    ELSE
      DBMS_OUTPUT.PUT_LINE('No rows found.');
    END IF;

    -- Check if no rows were fetched
    IF l_cursor%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('No rows fetched.');
    END IF;

    -- Get the number of rows fetched
    DBMS_OUTPUT.PUT_LINE('Number of rows fetched: ' || l_cursor%ROWCOUNT);
  END LOOP;
END;

2 thoughts on “PL/SQL – Part 3”

  2. Pingback: PL/SQL – Part 5 – Hi, This is Rajalakshmi!

Leave a comment

Design a site like this with WordPress.com
Get started