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 ...
Get The Database Hacker's Handbook: Defending Database Servers now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.