PL/SQL

Today, we will have a look at an amazing extension to SQL called PL/SQL. It is a very simple procedural languange used in combination with SQL (Structured Quary Language). I noticed huge growth in demand of this skill and beleive me, the effort / profit ratio to learning it is off the charts

What it is and how it’s used

PL stands for procedural language and it’s an extension from Oracle to SQL. I tis based on programming language Ada and we use it to run blocks of code that execute diferent procedures. It can use SQL statements to manipulate structures and data in Oracle databases.
Main features to extend SQL:

• Functions and procedures
• New data types
• Handling exceptions during execution of the code

Before we start

Before we get in depth of this language, you should take a look at this example to see if you even want to proceed with the learning proces

---

DECLARE
   qty_on_hand  NUMBER(5);
BEGIN
   SELECT quantity INTO qty_on_hand FROM inventory
      WHERE product = 'TENNIS RACKET'
      FOR UPDATE OF quantity;
   IF qty_on_hand > 0 THEN  -- check quantity
      UPDATE inventory SET quantity = quantity - 1
         WHERE product = 'TENNIS RACKET';
      INSERT INTO purchase_record
         VALUES ('Tennis racket purchased', SYSDATE);
   ELSE
      INSERT INTO purchase_record
         VALUES ('Out of tennis rackets', SYSDATE);
   END IF;
   COMMIT;
END;

---

Don‘t think too hard about the syntax, let‘s just go through the code step by step. First is declaration of a new variable „qty_on_hand“ which we will use later on. After that, we move to execute the procedure. First command is classic SQL used to pull number of tennis rackets out of the database and save it into our newly created variable. Once thats done, it asks if the variable (qty of rackets in inventory) is greater than zero -> meaning we have some rackets left to sell. If it is, substract one racket from the number and put a record into the „purchase_record“ table. If it isn’t, just put a record 'Out of tennis rackets' in there.
If you ever did any programing this should appear very easy to you. For those of you who didn’t worry not, you will understand it soon enough. Should you still wish to continue, read on and evolve together with me!

Code structure

This language has a block based structure, which means basic units of PL/SQL (f.e. functions) are logical blocks. That also means one block can include unlimited number of inner blocks (sub-blocks).
Blocks allow us to group up logicaly related declarations or statements. That is useful because we can keep declarations as close as possible to a place of use. Keep in mind that all declarations are LOCAL which means you cannot use them outside of their own block.
Typical block contains 3 parts DECLARATION -> EXECUTION -> EXCEPTION

---

Example here:

[DECLARE
    -- declarations]
BEGIN
    -- statements
[EXCEPTION
    -- handlers]
END;

---

Very simple and logical order. You make a declaration, you use it in execution and you handle exceptions of that execution.

Declaration

PL/SQL allows you to declare constants and variables which are then usable in procedures anywhere in the code.

Variable declaration

Any SQL data type is availible (f. e. CHAR, DATE, NUMBER…) plus there are also PL/SQL data types such as BOOLEAN.
Declaration itself is in this format: <variable_name> <data_type>

---

Examples:

id NUMBER(4)   
availible BOOLEAN

---

Some of the variables need more data than a name like NUMBER. This data type is defined like this NUMBER(precision, scale) where precision is maximum number of digits and scale is where rounding happens. Default scale is 0 so the example above is correct. If we had a number 123,12 this is how it would look like after execution:

---

    NUMBER(4) = 123
    NUMBER(4,1) = 123,1
    NUMBER(4,-2) = 100

---

I hope that explains it, im sure you can find a whole list of data types and conditions on google :-)

Puting values into variables

There are 2 most common ways you can do that. First is using operand „:=“ and the second is using some SQL statement

---

Using operand:

allowed := FALSE;
bonus := salary * 0.10;
value := function(parametr1, parametr2);

---

Using statements:

SELECT salary * 0.10 INTO bonus FROM employes WHERE empID = 15;

---

Atributes %TYPE and %ROWTYPE

Another amazing feature PL/SQL offers is atribute %TYPE. It represents already specified data type u already declared. When i first read about this function i thought to myself, why would anybody use that? But tthe thing is, you apply this not only to declared variables, but also on existing columns from Oracle databases. That means you can create a new value or even full row without worrying about compatibility with the database. It will just copy whats already there.

---

Example of use:

credit REAL(7,2);                 -- declaring a variable
debit  credit%TYPE;               -- using the same data type
my_name nameTable.firstName%TYPE; -- example with using a table column

---

%ROWTYPE work on exactly the same principle, but it takes into account a whole record that represents table row.

Using Cursors

Very important and very useful tool in PL/SQL is ability to declare cursors. Cursor is either implicit or explicit and it represents some SQL statement.
IMPLICIT - Manipulation with data which returns exactly one row
EXPLICIT - Statements that return more than one row.

---

Example of declaration:
DECLARE
   CURSOR c1 IS
      SELECT empno, ename, job FROM emp WHERE deptno = 20;

---

In order to work with cursors we need to know 3 operations
OPEN - Opens the set of data and puts your "Cursor" on the first row
FETCH - Loads the row with data where your cursor curently is
CLOSE - Closes the set of data. (this happens automaticaly after your cursor moves to the last row)
In order to understand examples of these operations we first need to comprehend what cycles, loops, and conditions are. But that is a little more advanced and will require a whole another post.

---

I really hope you liked and understood everything so far. I really enjoyed writing a guide for others :-) It took me alot of work to write though, so any likes and comments are very aprichiated.
Let me know if this topic interests you, perhaps if you would like a guide to SQL as well.
Thanks for reading