main banner

Development

Oracle PL/SQL fundamentals

We will learn the basics about this powerful tool and be able to manipulate a lot of data without affecting the application with multiple requests

As a young software engineer, I have encountered many difficulties as of how I could implement the business logic more effectively and  better manage a huge amount of information without affecting an application.

Here’s where Oracle PL/SQL comes in handy, this is because it's a procedural language that gives us the ability to write procedural logic, create loops, conditionals and of course allows us to execute regular SQL statements such as Insert, Update, Delete, per say some of the SQL statements. By using this we could have a better implementation of the business logic, and are able to create functions that can be reused and could manipulate data without having to make several calls from the client to the DB in order for it to implement the Business Logic.

PL/SQL Commonly Used Data Types

Oracle PL/SQL supports all the basic SQL types and it has a few specialized types of its own, it supports the Scalar Datatypes, Composite Datatypes, Reference Datatypes and much more. Our main focus will be in the Scalar Datatypes since we are covering the basics of this.
Scalar Datatypes
The scalar datatypes are the basic data types that have no internal components, which are numbers, characters, dates, etc.
Numeric
The first one that we are going to talk about is the Numeric Datatype, this one has 3 subtypes of datatypes which are Number, PLS_Integer/Binary_Integer and Binary_Double/Binary_Float; for the sake of this blog, our main focus will be in the Number subtype since it’s the most commonly used. 

The Number datatype can be defined as fixed point number or floating point number, the first receives its name since you’re setting the scale and precision of the declaring number, for better understanding let’s see the next example. 

    So we would like to declare a number variable with a length (precision) of 4 digits and 2 places for the decimal values (scale), so the code would look like this 

l_number NUMBER(4,2);
    
Now that we have declared our variable called “number_variable”, we can say that this variable could have assigned the number 25.21, because it’s length is 4 and it has 2 decimal places, but if we try to assign the number 125.15 this would cause an error as it exceeds the precision of 4.

Now going deeper into the definitions of precision and scale we could say that the precision is the total number of digits and it could have a max value of 38, while the scale refers to where the rounding occurs and it could have a value between -84 and 127. 

And finally, the second type is called floating point because we do not specify the precision or the scale of the number to be received by the variable, so, for instance, it can receive a number like 125.15 without causing an error. 

Character
It is used to store alphanumeric data; there are different character sets which are CHAR, VARCHAR2 and CLOB, we are going to focus on the types CHAR and VARCHAR 2 which are the common ones. 

CHAR is a fixed length character string, this has to be defined in size or characters it will allow (optional). For instance, if we define a char variable and defined the length of 4 and assign some letters.

l_char CHAR(4):= 'ab';

This will be stored as ‘ab’ but the database will append 2 trailing spaces so that it would match the length of 4. One thing to notice is that for Oracle the character values have to be in single quotation marks.

As for VARCHAR2, it’s a fixed length string like the CHAR, but with this type, you do have to specify the size that it will allow. Contrary to CHAR if we define a variable as VARCHAR2 it will not append the extra spaces for it to match the specified length, a quick example of how this works:.

l_varchar VARCHAR(4):= 'ab';

Loops
    In PL/SQL there are 3 main ways to loop through procedural logic, this is useful for performing numerical computations, executing procedural logic involving SQL statements, or for reading data fetched from database tables. These 3 loops are Simple loop, For loop  and While loop. 

Simple Loop
This loop has the simplest syntax, it starts with the LOOP keyword and it ends with the END LOOP keyword, inside this keyword you would write the statements that will need to be executed for each iteration. This loop doesn’t have an implicit termination, so we will have to define when to exit with the keywords EXIT, EXIT WHEN <condition>, RETURN and GOTO.  Let’s see an example of how it works, and how to declare it. 

DECLARE
    l_counter NUMBER:= 0;
    l_sum NUMBER:= 0;
BEGIN
    LOOP
        l_sum := l_sum + l_counter;
        l_counter := l_counter + 1;
        EXIT WHEN l_counter > 3 ;
    END LOOP;
END;

    
As we can see this will exit once the l_counter variable is greater than 3, so the output will be 0,1,3,6.
    
FOR Loop
    The For loop is the most commonly used of all the loops,  as for its declaration the start keyword will be FOR LOOP and ends with END LOOP keyword just like the last example. One big difference is that this loop has an implicit exit so after reacher the condition it will go out of the loop. Also, we don’t have to declare a variable at the beginning of the script, the variable will be declared after the Keyword FOR and the start value as well as the end value.  Here’s an example of how it’s structure is. 
    
DECLARE
    l_sum NUMBER:= 0;
BEGIN
    FOR l_counter in 1..3 LOOP
        l_sum := l_sum + l_counter;
    END LOOP;
END;


As seen in this loop we set a variable l_counter starting with an initial value of 1 and it will loop until it reaches the value of 3 to then start adding up the value to the l_sum variable which will end with a value of 6. 

While Loop
    The While loop is regularly used for when we don’t exactly know the number of iterations that it will go, this iteration will be dependent on a conditional that we have to define. It starts with the keyword WHILE followed by the <conditional> and the LOOP keyword and it ends with the keyword END LOOP, also inside of the loop you would set a variable which is going to be part of the evaluation of the conditional. 

DECLARE
l_check INTEGER:= 1;
BEGIN
    WHILE l_check < 5 LOOP
        l_check := DBMS_RANDOM.VALUE(1,10);
    END LOOP;
END;


As we can see we defined a variable called l_check which is going to be the variable which will determine whether the WHILE loop continues iterating or exits, l_check inside of the loop will be re-assigned with a random value thanks to the built-in function of Oracle which we sent 2 parameters that defined the ranged within which the values are generated randomly.

Conditional Executions
The Conditional executions, are divided into two kinds,  the first one is the IF statement and the second one is the CASE; this last one could make the code simpler and compact in some cases compared to the IF statement. 

IF Statements
    This statement starts with the Keyword IF followed by the <conditional> which if evaluated and true it will execute the code inside of the IF clause; adding at the end the keyword THEN, and close with the keyword END IF. Let’s see an example on how to declare this statement. 

DECLARE
        l_sales_amt NUMBER := 40000;
    l_commission NUMBER := 0;
BEGIN
    IF l_sales_amt > 50000 THEN
        l_commission := 10;
    END IF;
END;

As we can see we have two variables in which one is the sales amount and the commission, then we have the if statement declaring that if sales is greater than 50000, it should go inside of the IF statement and assign the value of 10  to the commission variable, otherwise it will not execute the code. 

There is a second form of the IF statement which involves the ELSE clause, this is used when the condition inside the IF does not meet the requirements to be true, then it comes to this part of the block. One thing to notice is that the ELSE keyword won’t be followed by the THEN keyword, let’s follow this up with the same example as above, but with the difference that if the sales are not greater than 50000 we will give a commission of 5 percent.

DECLARE
    l_sales_amt NUMBER := 40000;
    l_commission NUMBER := 0;
BEGIN
    IF l_sales_amt > 50000 THEN
        l_commission := 10;
    ELSE
        l_commission := 5;
    END IF;
END;


 Now a third option for this conditional is the ELSIF, which this is a secondary option for you to compare the required values based on a different conditional. Let’s see in the next example, where we have the same example case but now we are going to limit the commission to be 5 percent if the sales are greater than 35000 otherwise it will be only 3 percent.

DECLARE
    l_sales_amt NUMBER := 40000;
    l_commission NUMBER := 0;
BEGIN
    IF l_sales_amt > 50000 THEN
        l_commission := 10;
    ELSIF l_sales_amt > 35000 THEN
        l_commission := 5;
    ELSE
        l_commission := 3;
    END IF;
END;

 
CASE Statement
    This statement is an alternative to the IF Statement and sometimes the code can become more compact and readable. With this statement we can execute conditional logic based on a value, also this can be written as an expression where we can assign the value and return it to a variable. Let's see the basic structure of the CASE statement.

DECLARE
    l_ticket_priority VARCHAR2(8) := 'MEDIUM';
    l_support_tier NUMBER;
BEGIN
    CASE l_ticket_priority
WHEN 'HIGH' THEN
                l_support_tier := 1;
            WHEN 'MEDIUM' THEN
                l_support_tier := 2;
            WHEN 'LOW' THEN
                l_support_tier := 3;
            ELSE
                l_support_tier := 0;
    END CASE;
END;


As we can see, the CASE structure is pretty basic like any other programming language, in which we have the CASE keyword followed by the value or expression to be evaluated; then we have the WHEN keyword, it is here where the expression will be evaluated based on the value declared after the keyword and if it matched, it will execute the block of code inside the conditional. 

Conclusion
As we could see, the PL/SQL can have so many common things with other programming languages, which makes this tool a powerful one, especially if we need to implement business logic, in a way that we have to manipulate a lot of data and without affecting the application with multiple requests. As we could see we’ve just learned about some of the different data types that Oracle PL/SQL has by default, as well as the different types of loops and conditionals that might come handy. So, for me as a young developer that has encountered so many doubts on how and when it’s the best way to implement the business logic within the application without affecting its performance, but so far I’ve been able to find resourceful ways to work with it and I believe it can be a useful, powerful as well as enriching tool for business purposes.

Cesar M

Cesar, born and raised in Monterrey, enjoys movies, basketball, swimming, traveling and he happens to be one of our bloggers. A fresh web developer with a B.S. in Software Engineering, he has experience using tools such as PHP and HTML/CSS, as well as iOS development, he has some great things to say about this technologies and many other subjects.

Articles