PLSQL Tutorial - A Whistle Stop Tour of PL/SQL Features (Part 3)

PL/SQL Integration with Oracle databases

This is the third and final part of the introductory PLSQL tutorial. As mentioned earlier in this tutorial, PL/SQL is tightly integrated with SQL. This means there is no need to use ODBC or the Oracle Call Interface (OCI) libraries as you would if you were using other languages.

This tight itegration of SQL and PLSQL enable you to embed SQL statements directly in your PL/SQL modules either by defining an explicit cursor variable for a SELECT statement and then OPENing the cursor,FETCHing a row from the cursor INTO a suitable variable (this can be done in a loop until there is no more data) and then CLOSE the cursor or by using implicit cursors which don't have a variable decalaration associated with them.

For example, let's assume we have in our Oracle database a table called emp which has a column called nam which holds an employee's name and another column called sal which holds an employee's salary. 

We can then retrieve all the employees names from the database with an implicit cursor and write them out as in the follwoing peice of code.

BEGIN

    DBMS_OUTPUT.PUT_LINE('Name salary'); 

    FOR emprec IN (SELECT nam,sal FROM emp) LOOP

        DBMS_OUTPUT.PUT_LINE(emprec.nam||
                             ' £'||
                             emprec.sal);

    END LOOP;

END;

The output from the above code looks like this for the hr schema in the example database.

Name salary
King £24000
Kochhar £17000
De Haan £17000
Hunold £9000
Ernst £6000
Austin £4800

The above piece of code is also an example of an anonymous block (anonymous because it doesn't have a name).

Using an explicit cursor to do the same thing would give us something like the following:-

DECLARE 

    CURSOR emp_cur IS SELECT nam, sal FROM emp;
    emp_rec emp_cur%ROWTYPE;

BEGIN 

    OPEN emp_cur;

    LOOP 

        FETCH emp_cur INTO emp_rec;
        EXIT WHEN emp_cur%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(
                      emprec.nam||' £'||emprec.sal);

    END LOOP ;

    CLOSE emp_cur;

END;

The 2nd example is also an anonymous block and an example of a simple loop which terminates (exits) after the last record has been fetched and the details displayed. NOTFOUND is an attribute of cursors, in this case the emp_cur cursor.

Integration with Oracle enables PL/SQL procedures, functions, packages and triggers to be compiled (either into intermediate code or native binary code, depending on compilation settings and which version of PLSQL you use) and stored in the database and for user-defined functions to be invoked directly from SQL statements in the same way as built-in functions.

PL/SQL is also used for database triggers which are small pieces of code invoked (triggered)  automatically by the database when the triggering event occurs. Triggering events can be database startup or shutdown or insert/update/delete operations on a table, amongst other things.

Conclusion

This PLSQL tutorial only scratches the surface of the capabilities of PL/SQL. There is a great deal more that could be covered but this is only intended as an introductory tutorial. 

Looking for expert Oracle PLSQL training? Contact our partner Smartsoft for Oracle PLSQL training in the UK. 

Click here for the Introduction to Oracle PLSQL tutorial