PLSQL Tutorial - Part 3 of our Whistlestop Tour of PL/SQL

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.

In fact you can embed SQL statements directly in PL/SQL either by defining a variable for a SELECT statement (known as an explicit cursor) and then OPENing the cursor, FETCHing a record 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 creating implicit cursors which aren't predefined.

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

We can extract and write out all the employees names with an implicit cursor like so:

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 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.

If you'd like to learn more and you're in the UK, Smartsoft provide instructor-led training in PL/SQL at training centres across the UK, or for PL/SQL training in New Zealand click here.

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

PL/SQL Integration with Oracle databases

Conclusion