Introduction to procedures and cursors in SQL

Published in

Towards Data Science

9 min readNov 17, 2018

Learn how to write procedures and cursors for an RDBMS.

If you want to learn more about SQL specifically from a Data Science perspective, you can take DataCamp’s free “Intro to SQL for Data Science” course.

SQL is a must-have skill for any modern software engineer. Because most of the softwares depend on some kind of data and integrates well with an RDBMS (Relational Database Management System). Be it a web application, be it an API or be it an in-house application, RDBMS is there. And SQL is the language for querying an RDBMS.

As a data scientist, it is very primary to know SQL and its related techniques. For being able to query an RDBMS and get answers to specific questions that you will have about the data you are dealing with, SQL is the minimum need.

In his latest video with DataCamp, David Robinson(Chief Data Scientist @ DataCamp) showed us how he uses SQL in a Data Science problem. Please check it out, his workflow is very interesting.

In this tutorial, you will learn to write procedures and cursors; another important aspect of SQL. Have you ever wanted your RDBMS to automatically perform a certain of actions when a particular action is taken? For example, let’s say you have created a new employee record in table called Employees and you want this to reflect in the other related tables like Departments. Well, you are going to take just the right tutorial.

In this tutorial, you are going to learn:

What is a procedure in an RDBMS?
How can you write one procedure?
Different types of procedures
What is a cursor in an RDBMS?
How to write different types of cursors?
Different types of cursors

Sounds exciting? Let’s get started.

What is a procedure in an RDBMS?

Before proceeding with procedures and cursors, you will need to know a bit about PL/SQL which is a block-structured language that enables developers like you to combine the power of SQL with procedural statements. But you will not learn in a conventional way, you will learn it as you go along and as required.

So if you have an SQL query and you want to execute it multiple times. Procedures are one of the solutions for it. Often procedures are called in this context because they remain stored and get triggered upon a particular action or a series of actions. Procedures are also referred to as Procs.

Now you will see how to write a procedure.

Writing procedures:

The general syntax for writing a procedure is as follows:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Please note that these syntaxes applies to almost any RDBMS be it Oracle, be it PostgreSQL or be it MySQL.

After you have created a procedure you will have to execute it. Following is the syntax for that.

EXEC procedure_name;

Let’s write a simple procedure now. Consider the following snapshot from an RDBMS consisting of a table called Customers.

You will write a procedure named SelectAllCustomers which will select all the customers from Customers.

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute SelectAllCustomers by:

EXEC SelectAllCustomers;

Procedures can be stand-alone blocks of statements as well which makes them independent of any tables unlike the previous one. The following example creates a simple procedure that displays the string ‘Hello World!’ as output when executed.

CREATE PROCEDURE welcome
AS
BEGIN
dbms_output.put_line('Hello World!');
END;

There are two ways of executing a stand-alone procedure.

Using the EXEC keyword
Calling the name of the procedure from a PL/SQL block

The above procedure named ‘welcome’ can be called with the EXEC keyword as:

EXEC welcome;

You will see the next method now i.e. calling a procedure from another PL/SQL block.

BEGIN
welcome;
END;

A procedure can be replaced as well. You just need to add REPLACE keyword when you are creating the procedure. This will replace the already existing procedure (if) otherwise will create a fresh one.

CREATE OR REPLACE PROCEDURE welcome
AS
BEGIN
dbms_output.put_line('Hello World!');
END;

Deleting a stored procedure is no big deal:

DROP PROCEDURE procedure-name;

Procedures can be different based on parameters also. There can be one parameter procedures and also multiple parameters’ procedures. Now you will study these variants.

You will use the same table Customers for this. For your convenience, the snapshot is given again in the below section.

You will write a stored procedure that selects Customers from a particular City from the table:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Let’s dissect the common principles here:

You wrote the first @City and defined its type and size as one of the parameters which will be given when the procedure will be executed.
The second @City is assigned to condition variable City which is nothing but a column in the Customers table.

The procedure is executed as :

EXEC SelectAllCustomers City = "London";

Let’s see the other variant now.

Writing procedures with multiple parameters are exactly the same as the earlier one. You just need to append them.

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the procedure as:

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

Aren’t the above codes very readable? When the code is readable, it is more fun to do. That is all for the procedures. You will now study cursors.

What is a Cursor in an RDBMS?

Databases like Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example — the number of rows processed.

A cursor is a pointer to this context area. PL/SQL controls the context area through a Cursor. A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. Therefore, cursors are used as to speed the processing time of queries in large databases. The reason you may need to use a database cursor is that you need to perform actions on individual rows.

Cursors can be of two types:

Implicit cursors
Explicit cursors

Now you will see how to write different types of cursors.

Writing cursors:

You will start off this section by understanding what implicit cursors are.

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed when there is no explicit cursor defined for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML (Data Manipulation Language) statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with that statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

You can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like:

%FOUND,
%ISOPEN,
%NOTFOUND,
%ROWCOUNT.

Following image describes these attributes briefly:

Let’s consider a snapshot of a database consisting a table called Employee:

Now, you will write a cursor that will increase salary by 1000 of those whose age is less than 30.

DECLARE
total_rows number(2);BEGIN
UPDATE Employee
SET salary = salary + 1000
where age < 30;
IF sql%notfound THEN
    dbms_output.put_line('No employees found for under 30 age');
ELSIF sql%found THEN
    total_rows := sql%rowcount;
    dbms_output.put_line( total_rows || ' employees updated ');
END IF;
END;

Let’s now review what all you wrote:

You defined a variable named total_rows for storing the count of employees that will be affected for the action of the cursor.
You started the cursors block with BEGIN and wrote a simple SQL query which updates the salary of those whose age is less than 30.
You handled the output in case there is no such entry in the DB where an employee’s age is less than 30. You used %notfound attribute for that. Note that the implicit cursor sql here which stores all the relevant information.
Finally, you printed the number of records which got affected for the cursor using %rowcount attribute.

Great! You are doing fine!

When the above code is executed at SQL prompt, it produces the following result:

2 Employees updated (assume there are 2 records where age < 30)

You will now study explicit cursors.

Explicit cursors give more defined controls over context area. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is

CURSOR cursor_name IS select_statement;

If you are working with explicit cursors, you need to follow a sequence of steps which are as follows:

Declare the cursor for initializing in memory
Open the cursor for allocating a memory area
Fetch the cursor for retrieving data
Close the cursor for deallocating the memory

Following image denotes the life cycle of a typical explicit cursor:

You will now study more about each of these steps.

Declaring the cursor:

You declare a cursor along with a SELECT statement. For example:

CURSOR C IS SELECT id, name, address FROM Employee where age > 30;

Opening a cursor:

When you open the cursor, CPU allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement entailed to it. For example, we will open above-defined cursor as follows:

OPEN C;

Fetching the cursor:

Fetching the cursor involves accessing one row at a time from the associated table in the SQL entailed to the cursor.

FETCH C INTO C_id, C_name, C_address;

Closing the cursor:

Closing the cursor means releasing the allocated memory. You will close above-opened cursor as:

CLOSE C;

You will now put all these pieces together in a meaningful way.

Assembling these pieces:

DECLARE
C_id Employee.ID%type;
C_name Employee.NAME%type;
C_address Employee.ADDRESS%type;
CURSOR C is
SELECT id, name, address FROM Employee where age > 30;
BEGIN
OPEN C;
LOOP
FETCH C INTO C_id, C_name, C_address; 
dbms_output.put_line(ID || ' ' || NAME || ' ' || ADDRESS); 
EXIT WHEN C%notfound;
END LOOP;
CLOSE C;
END;

You learned to declare the cursor variables C_id, C_name and C_address as well. C_id Employee.ID%type; - this ensures that C_id gets created with the same data type as the ID's data type in the Employee table.

By using LOOP you looped through the cursor the fetched the records and displayed it. You also handled the case if no record is found by the cursor.

When the code is executed at the SQL prompt, it produces −

Conclusion:

Congrats! You have made it to the end. You covered two of most prevalent topics of the database world — procedures and cursors. These are very common in the applications which deal with huge amount of transactions. Yes, you guessed it right! Banks are using these since time immemorial. You learned how to write a procedure, what are its different types and why they are so. You also studied cursors and its several variants and how you can write them.

Amazing!

Following are some references that were taken to write this tutorial: