Oracle PL/SQL Part 1
Overview
What is PL/SQL? PL/SQL is a database procedural language which can extend functionality of SQL. It gives to the developers ability to handle events from higher layers of application, executes procedures or functions and store the results in appropriate structures. Many organizations like banks, insurance company etc. uses that language. Some of them still uses old technology like i.e Oracle Forms which can execute also PL/SQL blocks of code.
Preliminary phase
Before we start exploring the PL/SQL functionality we have to prepare our environment.
The simplest way is to use docker image to run Oracle DB. I found documentation on https://hub.docker.com/r/oracleinanutshell/oracle-xe-11g. Lets try to use that image.
Lets run the image
Unlock the HR user
Download SQLDeveloper and check connection
It is time to start. The structure of PL/SQL block should be similar to below structure.
Header - block of code could be anonymous or named. Anonymous block of code starts from key word "DECLARE" and it is not stored in DB's structures. Named block of code are i.e. functions, procedures, packages of code, triggers, etc.
Declaration section - this section contains definition of data types, structures and variables which could be used to assign references or store calculated values.
Execution section - this section is always required. It consists of execution code which cover business logic.
Exception section - this section is optional and it is appropriate place to handle errors and process them.
In next part I describe Types, procedures, objects, anonymous transactions and cache in functions etc.
The simplest way is to use docker image to run Oracle DB. I found documentation on https://hub.docker.com/r/oracleinanutshell/oracle-xe-11g. Lets try to use that image.
Lets run the image
Unlock the HR user
Download SQLDeveloper and check connection
PL/SQL structure
Declaration section - this section contains definition of data types, structures and variables which could be used to assign references or store calculated values.
Execution section - this section is always required. It consists of execution code which cover business logic.
Exception section - this section is optional and it is appropriate place to handle errors and process them.
Basic anonymous block of code
Lets create simple block of code. I based on HR example schema. The block of code contains an anonymous cursor and loop. In the declaration section are defined variables: counter v_cnt and new table v_tab which base on new definition of type t_tab;
SET SERVEROUTPUT ON
DECLARE
v_cnt PLS_INTEGER :=0;
--declare new type - table of records
TYPE t_tab IS TABLE OF departments.department_id%TYPE INDEX BY PLS_INTEGER;
--create variable base on table type
v_tab t_tab;
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
--create anonymous cursor AND fill the table with records
FOR dep IN (SELECT department_id FROM departments) LOOP
v_tab(v_cnt) := dep.department_id;
v_cnt := v_cnt + 1;
END LOOP;
--inerate over the table and show the departments
FOR i in v_tab.FIRST .. v_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID: '||v_tab(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('STOP');
END;
/
BULK COLLECT
If there was a big number of record I could be used 'BULK COLLECT' statement to have better performance (there is no necessary to switch between SQL and PL/SQL engines so frequently like in cursor statements). Additionally there could be used also Limit statement for limiting returned number of rows - this prevents form consume all available memory in session.SET SERVEROUTPUT ON
DECLARE
--declare new type - table of records
TYPE t_tab IS TABLE OF departments.department_id%TYPE INDEX BY PLS_INTEGER;
--create variable base on table type
v_tab t_tab;
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
--create 'select' statement with 'BULK COLLECT' phrase
SELECT department_id
BULK COLLECT INTO v_tab
FROM departments;
--inerate over the table and show the departments
FOR i in v_tab.FIRST .. v_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID: '||v_tab(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('STOP');
END;
/
FORALL LOOP
Similar to previous example we can increase performance with updating big number of rows without switching context between SQL and PL/SQL engines. I used additionally SAVEPOINT statement to control how changes will be propagate to persistent layer.SET SERVEROUTPUT ON
DECLARE
--declare new type - table of records
TYPE t_tab IS TABLE OF departments.department_id%TYPE INDEX BY PLS_INTEGER;
--create variable base on table type
v_tab t_tab;
BEGIN
SAVEPOINT s_dep;
DBMS_OUTPUT.PUT_LINE('START');
--create 'select' statement with 'BULK COLLECT' phrase
SELECT department_id
BULK COLLECT INTO v_tab
FROM departments;
--update all select attributes in one statement
FORALL j IN v_tab.FIRST..v_tab.LAST
UPDATE departments SET department_name = department_name || '(OLD)'
WHERE department_id = v_tab(j);
--rollback all changes
ROLLBACK TO s_dep;
DBMS_OUTPUT.PUT_LINE('STOP');
END;
/
CURSORS LOOP
In this example I created named cursor c_cur. Before fetching rows is necessary to open cursor and after processing is necessary to close this cursor. There are many attributes on cursors:
- FOUND
- NOTFOUND
- ISOPEN
- ROWCOUNT
SET SERVEROUTPUT ON
DECLARE
--declare cursor
CURSOR c_cur is
SELECT department_id, department_name
FROM departments;
--declare row base on cursor
r_rec c_cur%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
--open cursor
OPEN c_cur;
--fetch and process each row from cursor
LOOP
FETCH c_cur INTO r_rec;
EXIT WHEN c_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('DEP_ID:'||r_rec.department_id||' DEP_NAME:'||r_rec.department_name);
END LOOP;
--close cursor
CLOSE c_cur;
DBMS_OUTPUT.PUT_LINE('STOP');
END;
/
EXCEPTIONS
Resilient and exception handling is very important in block of code because if something goes wrong It would be captured and fixed. In addition that type of event should be logged. Below is simple definition of few errors in the Exception section. Pre-defined exceptions are described in many pages i.e. https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm
It is possible to define your own simple Exception e_my_own . In addition it is possible to assign error number by using PRAGMA EXCEPTION_INIT.
SET SERVEROUTPUT ON
DECLARE
v_dep_id departments.department_id%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
SELECT department_id INTO v_dep_id
FROM departments;
DBMS_OUTPUT.PUT_LINE('STOP');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND:'||SQLCODE||'; '||SQLERRM);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS:'||SQLCODE||'; '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:'||SQLCODE||'; '||SQLERRM);
END;
/
It is possible to define your own simple Exception e_my_own . In addition it is possible to assign error number by using PRAGMA EXCEPTION_INIT.
SET SERVEROUTPUT ON
DECLARE
v_dep_id departments.department_id%TYPE;
e_my_own EXCEPTION;
PRAGMA EXCEPTION_INIT(e_my_own,-777);
BEGIN
DBMS_OUTPUT.PUT_LINE('START');
SELECT department_id INTO v_dep_id
FROM departments where department_id = 10;
RAISE e_my_own;
DBMS_OUTPUT.PUT_LINE('STOP');
EXCEPTION
WHEN e_my_own THEN
DBMS_OUTPUT.PUT_LINE('e_my_own:'||SQLCODE||'; '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:'||SQLCODE||'; '||SQLERRM);
END;
/
