PL SQL Cursor And Strings: Tutorial With Code Examples

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.

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:

1. Implicit Cursor
2. 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.

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:

DECLARE 
   -- cursor declaration
CURSOR t_tutorials is 
SELECT code, subject, teacher FROM Tutor; 
t_code Tutor.code%type; 
t_subject Tutor.subject%type; 
t_teacher Tutor.teacher%type; 
BEGIN

   -- opening a cursor 
   OPEN t_tutorials; 
LOOP

    -- fetching values from cursor 
    FETCH t_tutorials into t_code, t_subject, t_teacher; 
    EXIT WHEN t_tutorials%notfound;

    -- printing in console
    dbms_output.put_line('Code is: ' || t_code || ' ' || 'Subject is: ' || t_subject || ' Teacher is: ' || t_teacher); 
END LOOP; 
CLOSE t_tutorials; 
END; 
/

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:

DECLARE
CURSOR c IS
SELECT code, subject, price FROM Tutorial;
...
BEGIN
FOR Tutorial_rec IN c LOOP
...
price_sum:= price_sum + Tutorial_rec.price;
END LOOP;

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:

1. Variable Length string: The length of the string cannot exceed 32,767 and there can be no padding to the string.
2. 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.
3. 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:

'This is Software Testing Help'

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

Syntax:

'We can''t go there'

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

Syntax:

q'[We can't go there]'

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:

DECLARE
subject varchar(10);

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:

DECLARE
subject char := 'P';

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:

DECLARE
l CLOB;

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:

DECLARE 
    subject varchar2(30); 
    teacher varchar2(40); 
    syllabus clob; 
    options char(1); 
BEGIN 

    -- Initializing values to variables
    subject := 'Selenium'; 
    teacher := 'Arun'; 
    syllabus := 'Java, WebDriver Methods, Synchronization, WebTables.'; 
    options := 'S';

    -- checking condition and if true
    IF options = 'S' THEN

       -- printing in console
       dbms_output.put_line(subject); 
       dbms_output.put_line(teacher); 
       dbms_output.put_line(syllabus); 
   END IF; 
END; 
/

The output of the above code should be:

PL/SQL Functions And Operators In String

Code implementation with some string functions:

 
DECLARE 
    name varchar2(30) := ' software testing help!'; 
BEGIN 
    dbms_output.put_line(UPPER(name)); 
    dbms_output.put_line(LOWER(name)); 

    dbms_output.put_line(LENGTH(name)); 

    dbms_output.put_line(INITCAP(name)); 

    /* get the first word in the string */ 
    dbms_output.put_line ( SUBSTR (name, 1, 8)); 

    /* get the location of the first "w" */ 
    dbms_output.put_line ( INSTR (name, 'w')); 

    /* replace a string */ 
   dbms_output.put_line ( REPLACE( name, 'help', 'solution')); 

   /* trim a string from right */ 
   dbms_output.put_line ( RTRIM(name,'!')); 

    /* trim a string */ 
    dbms_output.put_line ( TRIM(name)); 
END; 
/

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.

Q #3) How do I run a cursor in Oracle?

Answer: To run a cursor in Oracle, the syntax is: OPEN <<CURSORNAME>>. The CURSORNAME refers to the name of the cursor available in the declaration section of the PL/SQL block of code.

Q #4) Can we declare cursor inside begin?

Answer: Yes we can declare more than one cursor within a PL/SQL block of code.

Q #5) How do you find the length of a string in PL/SQL?

Answer: We can find the length of a string in PL/SQL with the help of function LENGTH(str). Here str is the string for which we want to get the length. This function returns a numeric value.

Q #6) What is SUBSTR in Oracle?

Answer: The SUBSTR function gives a particular number of characters from a specific position of a string. The syntax is SUBSTR(n, start, l). It returns a substring from the string n that starts from the position mentioned in start. The parameter l is optional and represents the length of the substring.

Conclusion

In this tutorial, we have discussed in detail some of the basic concepts of PL/SQL.

We have covered the following topics listed below:

• PL SQL Cursor: Implicit Cursor and Explicit Cursor
• PL/SQL Strings.
• Functions and Operators in String.

<< PREV Tutorial | NEXT Tutorial>>