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 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
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
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.
|1||%FOUND||Gives the result in boolean. Returns true if DELETE, INSERT, UPDATE or SELECT statements affect single or multiple rows. Or else false is returned.|
|2||%NOTFOUND||Gives 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||%ISOPEN||Gives the result in boolean. Returns true if the cursor is currently open. Or else false is returned.|
|4||%ROWCOUNT||Gives the count of the number of rows fetched from DELETE, INSERT, UPDATE or SELECT statements.|
|5||%TYPE||Gives the datatype of the column or variable of the database.|
|6||%ROWTYPE||Gives 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.
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.
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.
#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.
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.
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.
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.
'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.
'We can''t go there'
We can describe user-defined delimiters for string literal by prefixing it with a ‘q’ letter.
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.
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.
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.
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
|1||CONCAT(i, j)||Appends the strings i and j and returns the new string.|
|2||ASCII(n)||Returns the equivalent ASCII value of n.|
|3||CHR(n)||Returns the character along with the equivalent ASCII value of n.|
|4||INSTR(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.|
|5||INSTRB(i)||Returns the position of a substring in a string in bytes.|
|6||INITCAP(k)||Converts the initial character of individual words in string k to the uppercase and then gives back the string.|
|7||LENGTH(m)||Returns the count of the number of characters in string m.|
|8||LENGTHB(n)||Returns the count of the characters in string m in bytes for single byte character set.|
|9||LTRIM(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.|
|10||RTRIM(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.|
|11||TRIM([trim_char FROM) x);||Removes spaces or mentioned characters from the start, end or both ends of the string x.|
|12||LOWER(i)||Converts the characters of string i to lower case and then returns the string.|
|13||UPPER(i)||Converts the characters of string i to upper case and then returns the string.|
|14||LPAD(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.|
|15||RPAD(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.|
|16||NANVL(n, val)||Returns val if n is equal to the NaN value, else n is returned.|
|17||NLSSORT(i)||Modifies the sorting method of characters. It should be mentioned prior to any NLS function, else the default sorting will be done.|
|18||NLS_INITCAP(i)||Similar in functionality as function INITCAP but it can take a different sort of technique as mentioned in function NLSSORT.|
|19||NLS_LOWER(m)||Similar in functionality as function LOWER but it can take a different sort of technique as mentioned in function NLSSORT.|
|20||NLS_UPPER(m)||Similar in functionality as function UPPER but it can take a different sort of technique as mentioned IN function NLSSORT.|
|21||NVL(n, val)||Returns val if x is equal to the NULL value, else n is returned.|
|22||NVL2(n, val, val2)||Returns val if x is not equal to the NULL value, else if x is equal to NULL, val2 is returned.|
|23||SOUNDEX(i)||Returns a string having the vocal representation of i.|
|24||SUBSTR(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.|
|25||SUBSTRB(n)||Similar in functionality as function SUBSTR but parameters are in bytes and not in characters for a single byte character system.|
|26||REPLACE(n, s, r)||Replaces the occurrences of s with the string r with in the string n.|
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.
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.