The syntax for creating an explicit cursor is − It is created on a SELECT Statement which returns more than one row. An explicit cursor should be defined in the declaration section of the PL/SQL Block. | 1 | Ramesh | 32 | Ahmedabad | 2500.00 |Įxplicit cursors are programmer-defined cursors for gaining more control over the context area. If you check the records in customers table, you will find that the rows have been updated − When the above code is executed at the SQL prompt, it produces the following result − The following program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected −ĭbms_output.put_line('no customers selected') ĭbms_output.put_line( total_rows || ' customers selected ') We will be using the CUSTOMERS table we had created and used in the previous chapters. Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.Īny SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example. Otherwise, it returns FALSE.Īlways returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. The following table provides the description of the most used attributes − S.No The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected. For INSERT operations, the cursor holds the data that needs to be inserted. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. Programmers cannot control the implicit cursors and the information in it. Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. The set of rows the cursor holds is referred to as the active set. A cursor holds the rows (one or more) returned by a SQL statement. PL/SQL controls the context area through a cursor. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement for example, the number of rows processed, etc.Ī cursor is a pointer to this context area. In this chapter, we will discuss the cursors in PL/SQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |