The Database Hacker's Handbook: Defending Database Servers

Oracle's PL/SQL

PL/SQL is the language used for creating stored procedures, functions, triggers, and objects in Oracle. It stands for Procedural Language/SQL and is based on the ADA programming language. PL/SQL is so integral to Oracle I'd recommend getting a book on it and reading it, but in the meantime here's a quick one-minute lesson. Here's the code for the ubiquitous “Hello, world!”:

CREATE OR REPLACE PROCEDURE HELLO_WORLD AS
BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

If you run this procedure with

EXEC HELLO_WORLD

and you don't get any output, run

SET SERVEROUTPUT ON

Essentially, this procedure calls the PUT_LINE procedure defined in the DBMS_OUTPUT package. A PL/SQL package is a collection of procedures and functions (usually) related to the same thing. For example, we might create a bunch of procedures and functions for modifying HR data in a database that allows us to add or drop employees, bump up wages, and so on. We could have a procedure ADD_EMPLOYEE, DROP_EMPLOYEE, and BUMP_UP_WAGE. Rather than have these procedures just free-floating, we could create a package that exports these procedures and call the package HR. When executing the ADD_EMPLOYEE procedure we'd do

EXEC HR.ADD_EMPLOYEE('David');

If this package was defined by SCOTT and PUBLIC had execute permissions to execute the HR package, they could do so by calling

EXEC SCOTT.HR.ADD_EMPLOYEE('Sophie');

So, what's the difference between a PL/SQL procedure and a function? Well, a function returns ...