With SQL queries you can easily extract or manipulate data in the database. But in order to make it sophisticated and implement some logic; SQL is simply not enough. This is the time when PL/SQL comes into play.
PL/SQL stands for Procedural Language extension for SQL. It brings great features such as procedures, functions, loops, arrays, packages and many more. It allows you to log your processes, debug them and also handle exceptions. It allows you to combine it with SQL language so you can easily modify data as well.
We distinguish two major types of PL/SQL:
- Anonymous blocks
- Named blocks
Anonymous blocks
- cannot have a name
- cannot be stored in a database (therefore, they cannot be recalled)
- cannot accept parameters (however, you can use variables in the DECLARE part)
- are compiled only at a time of execution
- can call a named block
Example of an anonymous block
DECLARE -- declaration part, used for variable, cursor, nested tables, arrays, .... declaration
v_iter_start NUMBER;
v_iter_stop NUMBER;
BEGIN -- start of the execution
v_iter_start := 1;
v_iter_stop := 10;
FOR i IN v_iter_start..v_iter_stop
LOOP
dbms_output.put_line(i);
END LOOP;
END;
Named blocks
- have names
- are stored and therefore can be recalled whenever needed
- can have parameters
- IN – input parameter; default (doesn’t have to be specified)
- OUT – output parameter; must be specified
- INOUT – input and output parameter at the same time; must be specified
- are stored in the shared pool of SGA (Shared/System Global Area)
- there are multiple types of named blocks (see above) … to get more details about them, click the link and read the associated article
Benefits
The biggest benefit of PL/SQL is the support for transactions followed by the second most important feature called procedural language support. Transactions are a very specific set of operations where you can split the process into multiple blocks where you can return back to the original step in case of error/need or cancel all preceding steps. For these, you can use the following commands:
- COMMIT
- ROLLBACK
- SAVEPOINT
Example of a transaction:
-- this is an anonymous block but you can use named blocks as well (which is most likely)
DECLARE
... some variables
BEGIN
... do some action here ...
savepoint action1; -- this will create a checkpoint to which you can go back in case of any need further in your code
... do another action here ...
savepoint action2; -- another checkpoint
... check the result data from previous actions
... if the result is not as expected you have two choices
rollback; -- this will revert all steps done before and the whole transaction is canceled
-- or
rollback to savepoint action1; -- revert back all actions until action1 savepoint ...
... do some other actions ...
commit; --confirm changes and write them permanently to the database files, release undo and write changes into the REDO log file
END;
To see more details about what is going on when ROLLBACK or COMMIT is executed, see the article about DML processing.
Another advantage is performance and centralization. With PL/SQL, all your code is centralized and unified due to the fact that is stored in the database and you only call the procedure. This will not only improve the performance (because of avoiding the hard parse) but also by allowing users to use bind variables (again – avoiding the hard parse). You can use only the procedure name in your applications during the call instead of the full code; this brings another advantage because when you will have the need of changing the code, you can do so inside the procedure instead of touching the application at all. Not to mention when you use this process in multiple parts of the applications or in multiple applications. Your developers won’t need to bother with the code itself. Same when you need to change the database design – your application is not affected and this is the last advantage I wanted to mention – application and database layer separation.