What is Cursor in PL-SQL with example

Summary

This video script discusses the concept of cursors in SQL, explaining how they function as a work area for the results of SQL queries. It elaborates on the difference between implicit and explicit cursors, their attributes like %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT, and how they are used in various SQL operations like SELECT, UPDATE, and DELETE. The script also provides examples to demonstrate how cursors can be declared, opened, and closed, and how they can be used to check query results and perform updates on a database.

Takeaways

• 😀 Cursors are essential in SQL for processing and handling data in a temporary memory space during query execution.
• 🔍 When executing a simple SQL query like 'SELECT * FROM table', the data is temporarily fetched to a faster memory (not directly from the database) for execution.
• 💾 Databases are stored in permanent storage (e.g., SSD or hard disks), while queries run in temporary memory for faster processing.
• 🖥️ The processor doesn’t work directly with the permanent database but instead operates on a copy of the data stored in a temporary workspace, referred to as a 'cursor'.
• 👔 A cursor is like a display area (akin to a showroom) where data is temporarily brought for users to work with.
• 🤖 There are two types of cursors: implicit and explicit. Implicit cursors are automatically generated by SQL, while explicit cursors are user-defined and named.
• ✔️ Implicit cursors automatically handle data operations like SELECT, DELETE, or UPDATE, while explicit cursors require declaration, opening, and closing.
• 🔄 Cursors have four attributes: %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT, which help in tracking the state of the cursor during query execution.
• 📊 The %ROWCOUNT attribute helps count the number of rows affected by an operation, while %FOUND and %NOTFOUND return true or false based on whether the desired data is found.
• 📖 Explicit cursors provide more flexibility by allowing users to manage and execute multiple queries or specific operations in a controlled way.

Q & A

What is the role of cursors in SQL?

A cursor in SQL is a temporary work area created in memory when a SQL query is executed. It stores the result of the query and allows manipulation of rows one by one. It is commonly used for tasks like fetching and updating data from a table efficiently.

Why is data fetched into temporary memory before executing a SQL query?

Fetching data into temporary memory, like RAM, is done to improve performance. Accessing data directly from a hard disk or SSD is slow, so by moving the relevant data into faster memory, SQL queries can be executed more quickly.

What are implicit and explicit cursors in SQL?

Implicit cursors are automatically created by SQL whenever a query like SELECT, UPDATE, or DELETE is executed, and they handle data without user intervention. Explicit cursors, on the other hand, are defined by users to control the data being processed and give them more flexibility, such as naming the cursor.

How does SQL use temporary memory in relation to cursors?

SQL uses a specific area in temporary memory to store and manipulate the active data during query execution. This area is known as a cursor. The cursor allows the processor to work with data faster without directly interacting with the slower permanent storage.

What is the difference between implicit and explicit cursors in terms of control?

Implicit cursors are managed automatically by SQL and do not require user control, whereas explicit cursors are defined and controlled by the user, allowing more fine-tuned operations, such as naming the cursor or fetching rows individually.

What are some attributes of explicit cursors?

Attributes of explicit cursors include `%ISOPEN` to check if the cursor is open, `%FOUND` to check if a row was found, `%NOTFOUND` to check if no rows were found, and `%ROWCOUNT` to count the number of affected rows during query execution.

What is the use of the `%FOUND` and `%NOTFOUND` attributes in a cursor?

`%FOUND` returns `TRUE` if a row is found during the execution of a query, and `FALSE` otherwise. Conversely, `%NOTFOUND` returns `TRUE` if no row is found, indicating that the search criteria did not match any record in the table.

How does SQL handle multiple rows when using cursors?

When using explicit cursors, SQL can fetch data row by row. After each fetch, the cursor points to the next row until all rows are processed. Implicit cursors process all rows at once and return the entire result set without manual row-by-row control.

What is the purpose of opening and closing a cursor?

Opening a cursor initializes it and prepares it to execute the query. Once all operations are done, closing the cursor frees up the resources allocated to it. Explicit cursors require manual opening and closing, while implicit cursors handle this automatically.

What is the `%ROWCOUNT` attribute used for in cursors?

`%ROWCOUNT` is used to determine how many rows have been affected by a query. It is useful for tracking the impact of UPDATE or DELETE statements to see how many rows were modified.