How do I run a cursor in Oracle?

When an SQL statement is processed, Oracle creates a memory area known as context area. A cursor is a pointer to this context area. It contains all information needed for processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it.

A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

Implicit Cursors
Explicit Cursors

1) PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don't use an explicit cursor for the statement.

These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.

Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.

For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected.

The following table soecifies the status of the cursor with each of its attribute.

AttributeDescription%FOUNDIts return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE.%NOTFOUNDIts return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND.%ISOPENIt always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.%ROWCOUNTIt returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.

PL/SQL Implicit Cursor Example

Create customers table and have records:

IDNAMEAGEADDRESSSALARY1Ramesh23Allahabad200002Suresh22Kanpur220003Mahesh24Ghaziabad240004Chandan25Noida260005Alex21Paris280006Sunita20Delhi30000

Let's execute the following program to update the table and increase salary of each customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected:

Create procedure:

Output:

6 customers updated PL/SQL procedure successfully completed.

Now, if you check the records in customer table, you will find that the rows are updated.

IDNAMEAGEADDRESSSALARY1Ramesh23Allahabad250002Suresh22Kanpur270003Mahesh24Ghaziabad290004Chandan25Noida310005Alex21Paris330006Sunita20Delhi35000

2) PL/SQL Explicit Cursors

The Explicit cursors are defined by the programmers to gain more control over the context area. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

Following is the syntax to create an explicit cursor:

Syntax of explicit cursor

Following is the syntax to create an explicit cursor:

Steps:

You must follow these steps while working with an explicit cursor.

Declare the cursor to initialize in the memory.
Open the cursor to allocate memory.
Fetch the cursor to retrieve data.
Close the cursor to release allocated memory.

1) Declare the cursor:

It defines the cursor with a name and the associated SELECT statement.

Syntax for explicit cursor decleration

2) Open the cursor:

It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it.

Syntax for cursor open:

3) Fetch the cursor:

It is used to access one row at a time. You can fetch rows from the above-opened cursor as follows:

Syntax for cursor fetch:

4) Close the cursor:

It is used to release the allocated memory. The following syntax is used to close the above-opened cursors.

Syntax for cursor close:

PL/SQL Explicit Cursor Example

Explicit cursors are defined by programmers to gain more control over the context area. It is defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

Let's take an example to demonstrate the use of explicit cursor. In this example, we are using the already created CUSTOMERS table.

In this tutorial, we will learn what is a PL SQL Cursor and related concepts like Implicit and Explicit Cursors, cursor for loop, etc. We will also learn about String Functions in PL/SQL:

In the PL SQL Collections tutorial of the PL/SQL series, we learned about Collection Methods, Varray, Nested table, and Associative Array with the help of code examples.

In this article, we will explore the various types of cursors that PL/SQL has. We will also see the implementation of different types of cursors with code examples.

Also, we shall discuss strings and some of the inbuilt string functions available in PL/SQL.

What You Will Learn:

PL SQL Cursor
- Implicit Cursors
- Explicit Cursors
- Cursor For Loop
- Cursor Variables
PL SQL Strings
- String Variables Declaration
- PL/SQL Functions And Operators In String
- Frequently Asked Questions And Answers
Conclusion
- Recommended Reading

PL SQL Cursor

Oracle has dedicated memory locations for executing SQL statements and then it holds that processed information, for example, the total number of rows updated.

A cursor in PL/SQL gives a name and acts as a pointer to the area of work called a context area and then uses its information. It keeps the number of rows processed by the SQL statement. These rows are called as an active set. The size of the active set is equal to the count of the rows that meet the condition.

There are two types of cursors which are listed below:

Implicit Cursor
Explicit cursor

Implicit Cursors

The implicit cursors are allocated by Oracle by default while executing SQL statements. It holds the affected rows by the DML operations like UPDATE, DELETE and INSERT. Thus implicit cursors are used when we don’t have an explicit cursor in place.

While we are inserting a row, the cursor keeps that particular data. Similarly, for deletion and updating operations, the affected rows are stored by the cursors. The implicit cursors are not given any names and hence cannot be manipulated by the developers and the data contained on it cannot be used anywhere.

The latest updated cursors can be used with the help of cursor attributes. These attributes are the properties that help to refer to the data type and structure of items without repeating their definitions. All the columns and tables in a database have a common attribute (represented by % sign) characteristics and they can be used as sql%attribute_name.

Sl No.NamePurposes1%FOUNDGives the result in boolean. Returns true if DELETE, INSERT, UPDATE or SELECT statements affect single or multiple rows. Or else false is returned.2%NOTFOUNDGives the result in boolean and has reverse functionality of %FOUND. Returns true if DELETE, INSERT, UPDATE or SELECT statements affect no rows. Or else false is returned.3%ISOPENGives the result in boolean. Returns true if the cursor is currently open. Or else false is returned.4%ROWCOUNTGives the count of the number of rows fetched from DELETE, INSERT, UPDATE or SELECT statements.5%TYPEGives the datatype of the column or variable of the database.6%ROWTYPEGives the record type that is equivalent to a database row.

Let us consider a table which is named TUTOR.

SELECT * FROM TUTOR;

We have created a table with the SQL statement given below:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );

Inserted values to this table with SQL statements given below:

INSERT INTO TUTOR (CODE,SUBJECT,TEACHER,REVIEWS) VALUES (1, 'Automation', 'Mukul', 'five stars'); INSERT INTO TUTOR (CODE,SUBJECT,TEACHER,REVIEWS) VALUES (4, 'PLSQL', 'Anand', 'four stars'); INSERT INTO TUTOR (CODE,SUBJECT,TEACHER,REVIEWS) VALUES (2, 'Performance', 'Arvind', 'four stars');

Code implementation with the implicit cursor:

DECLARE total_count number(30); BEGIN --updating a row UPDATE TUTOR SET TEACHER = 'Zen' where CODE = 1; -- result in boolean, true returned if no rows affected IF sql%notfound THEN dbms_output.put_line('no subjects fetched'); -- result in boolean, true returned if any rows affected ELSIF sql%found THEN -- count the number of rows affected rows affected total_count := sql%rowcount; dbms_output.put_line( total_count || ' teacher name updated '); END IF; END; /

The output of the above code should be:

Let us now verify the changes reflected in the table named TUTOR.

We are using a SQL statement to get the columns of the table:

SELECT * FROM TUTOR;

Thus we see that as pointed by the cursor, the name of the teacher with code = 1, gets updated to Zen.

Explicit Cursors

The developers can have their own user-defined context area to run DML operations. Thus they can exercise more power over it. The declaration section of the PL/SQL block of code contains explicit cursors. It is normally built on SELECT operations that fetch multiple rows.

Syntax of explicit cursor:

DECLARE CURSOR <<cursor name>> IS <<select statement>> <<Cursor variable>> BEGIN OPEN <<cursor name>>; FETCH <<cursor name>> INTO <Cursor variable>; . . CLOSE <cursor name>; END;

Explicit Cursor works on the processes listed below:

#1) Cursor declaration for memory initialization. Here, a named context area is created which serves as a cursor name.

Syntax:

CURSOR tutorial_s IS SELECT code FROM TUTORIAL;

#2) Cursor opening for memory allocation. A cursor is now available for fetching the updated rows from the database.

Syntax:

OPEN tutorial_s;

#3) Cursor is fetched for getting the data. After the SELECT operation is done, the rows obtained are put in the memory allocated and these are now considered as active sets. The cursor can access one row at a time.

Syntax:

FETCH tutorial_s INTO c_code;

#4) Cursor is finally closed to free the allocated memory. As all the records are obtained one by one, the cursor is closed to release context area memory.

Syntax:

CLOSE tutorial_s;

Code implementation with explicit cursor:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );0

The output of the above code should be:

Cursor For Loop

While working with explicit cursors, we can use FOR loop instead of using statements like FETCH, OPEN, and CLOSE. Cursor FOR Loop has the loop index as a record which points to the row obtained from the database. Next after opening the cursor, it fetches the multiple rows of data repeatedly from the result set into the record fields.

Finally, the cursor is closed after all the rows are obtained. We use a dot (.) sign to refer to each field in the record. (.) dot sign is actually used for selecting a component.

The syntax for Cursor For loop:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );1

Here, the Cursor FOR loop declares ‘Tutorial_rec’ as a record.

Cursor Variables

A cursor variable is used to refer to the present row in the result set that has more than one row. It can be used for any type of query. It is similar to a variable of PL/SQL, where we can assign values and can be passed via a subprogram in the database. Thus cursor variables provide a lot of flexibility and data can be obtained in a centralized process.

PL SQL Strings

The strings in PL/SQL is a group of characters in a particular order. The size of the string may or may not be available. The characters which can be a part of a string may be a special character, blanks, numbers, and alphanumeric. Thus it is a set of chosen symbols from a group of characters.

There are three categories of strings in PL/SQL. These are listed below:

Variable Length string: The length of the string cannot exceed 32,767 and there can be no padding to the string.
Fixed Length string: The length of the string is mentioned in the string declaration. The string is right padded with spaces to accommodate the specified length of the string.
Character Large Objects (CLOB): This is a variable-length string that has a size up to 128 TB.

The strings in PL/SQL can be either literals or variables. Quotation symbols are used for literals.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );2

Also, we have the option of adding a quote within our string literal. This is achieved by keeping two consecutive single quotes.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );3

We can describe user-defined delimiters for string literal by prefixing it with a ‘q’ letter.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );4

String Variables Declaration

There are multiple data types in PL/SQL like NCHAR, CHAR, VARCHAR, VARCHAR2, CLOB and NCLOB. The national character set data types are prefixed with N. They are used for storing Unicode characters.

In a variable length string, the maximum limit of the string length should be mentioned.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );5

This means that the variable subject is capable of holding up to 10 characters and not more than that. In the case where the maximum length is omitted, compile error is thrown.

In a fixed length string, the CHAR data type can be used. It is not necessary to define the maximum length of a fixed length string. If the limit is omitted, Oracle takes the default value of 1.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );6

If you declare a CHAR variable with the length exceeding 1, Oracle by default pads the value that we have stored to that variable with spaces. This is done until the specified maximum length is reached.

To make a declaration for a character large object, CLOB is used. The maximum length need not be mentioned here and the length is set by the Oracle database itself and is dependent on the size of the database block.

Syntax:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );7

Guidelines to choose Data Types in PL/SQL:

If we are dealing with values that always have a fixed length, for example, the mobile phone number which has constant length and format, we should use CHAR or NCHAR data type.
Else, we should use VARCHAR2 or NVARCHAR2 datatype.
If we are dealing with a string that has greater than 32,767 characters, we should use CLOB or NCLOB data type.

Code implementation with strings:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );8

The output of the above code should be:

PL/SQL Functions And Operators In String

Sl No.NamePurposes1CONCAT(i, j)Appends the strings i and j and returns the new string.2ASCII(n)Returns the equivalent ASCII value of n.3CHR(n)Returns the character along with the equivalent ASCII value of n.4INSTR(i, x, start, n)Finds the substring i in x string and then returns the position of occurrence. The start refers to the beginning position of searching and is an optional parameter. The n is the nth occurrence of the string and is also an optional parameter.5INSTRB(i)Returns the position of a substring in a string in bytes.6INITCAP(k)Converts the initial character of individual words in string k to the uppercase and then gives back the string.7LENGTH(m)Returns the count of the number of characters in string m.8LENGTHB(n)Returns the count of the characters in string m in bytes for single byte character set.9LTRIM(n, x)Removes x characters from left of string n. The x is an optional parameter if not provided , removes all leading spaces of the string n.10RTRIM(n, x)Removes x characters from right of string n. The x is an optional parameter if not provided , removes all trailing spaces of the string n.11TRIM([trim_char FROM) x);Removes spaces or mentioned characters from the start, end or both ends of the string x.12LOWER(i)Converts the characters of string i to lower case and then returns the string.13UPPER(i)Converts the characters of string i to upper case and then returns the string.14LPAD(i, l , x)Pads string x to the left to make the string i length to l. The parameter x is optional, if omitted spaces are padded to the left of string i.15RPAD(i, l , x)Pads string x to the right to make the string i length to l. The parameter x is optional, if omitted spaces are padded to the right of string i.16NANVL(n, val)Returns val if n is equal to the NaN value, else n is returned.17NLSSORT(i)Modifies the sorting method of characters. It should be mentioned prior to any NLS function, else the default sorting will be done.18NLS_INITCAP(i)Similar in functionality as function INITCAP but it can take a different sort of technique as mentioned in function NLSSORT.19NLS_LOWER(m)Similar in functionality as function LOWER but it can take a different sort of technique as mentioned in function NLSSORT.20NLS_UPPER(m)Similar in functionality as function UPPER but it can take a different sort of technique as mentioned IN function NLSSORT.21NVL(n, val)Returns val if x is equal to the NULL value, else n is returned.22NVL2(n, val, val2)Returns val if x is not equal to the NULL value, else if x is equal to NULL, val2 is returned.23SOUNDEX(i)Returns a string having the vocal representation of i.24SUBSTR(n, start, l)Returns a substring of string n that starts from the position mentioned in start. The parameter l is optional and represents the length of the substring.25SUBSTRB(n)Similar in functionality as function SUBSTR but parameters are in bytes and not in characters for a single byte character system.26REPLACE(n, s, r)Replaces the occurrences of s with the string r with in the string n.

Code implementation with some string functions:

CREATE TABLE TUTOR( CODE INT NOT NULL, SUBJECT VARCHAR(15) NOT NULL, TEACHER VARCHAR(15), REVIEWS VARCHAR (10) NOT NULL, PRIMARY KEY (CODE) );9

The output of the above code should be:

Explanation of the above code:

The first output is SOFTWARE TESTING HELP!. Returns the input string ‘ software testing help!’ in the upper case with the help of UPPER function.
The second output is software testing help!. Returns the input string SOFTWARE TESTING HELP! in the lowercase with the help of the LOWER function.
The third output 23. Returns the length of the input string with the help of the LENGTH function.
The fourth output is Software Testing Help!. Returns the first character of each word of the input string in the upper case with the help of the INITCAP function.
The fifth output is softwar. Returns a substring of the input string from the first position to a length of 8 including a space with the help of SUBSTR function.
The sixth output is 6. Returns the position of w in the input string with the help of the INSTR function.
The seventh output is software testing solution!. Returns a new string by replacing help with solution in the input string with the help of the REPLACE function.
The eighth output is software testing help. Returns a new string by trimming the character ! from the right of the input string with the help of RTRIM function.
The ninth output is software testing help!. Returns a new string by trimming the leading and trailing spaces from the input string with the help of the TRIM function.

Frequently Asked Questions And Answers

Q #1) What is cursor in PL/SQL?

Answer: After an SQL statement is executed, the Oracle database builds a memory called context area. A cursor has the information processed from a select statement and contains the rows updated by that SELECT operation.

Q #2) What is a cursor and type of cursor?

Answer: After an SQL statement is executed, the Oracle database builds a memory called context area which is a temporary work area. There are two categories of cursors – Implicit cursor and Explicit cursor.