Oracle PL/SQL PART 1

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

PL/SQL structure

 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.

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

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;
/

WHAT ELSE

In next part I describe Types, procedures, objects, anonymous transactions and cache in functions etc.