During my studies at the University of South Wales, I was exposed to PL/SQL for the first time. This fascinating approach to working with databases made me think differently about how to store and efficiently handle data that would otherwise require multiple and complex SQL queries.
In this article, I will guide you through the basics of getting started with PL/SQL and explain some of the rationale and philosophy behind the choices, based on my experience.
PL/SQL vs SQL
The essential basics
Traditional relational database SQL relies on a combination of built-in processes provided by the DBMS, and the SQL queries sent by an application to do something with that data.
With PL/SQL, you can add operations such as functions, procedures, and triggers to make the DBMS perform logical operations that an application can use as an abstraction, much like ORM (Object-Relational Mapping) systems do within a web framework. But it can potentially do it more efficiently and with less network traffic.
While learning PL/SQL, I exclusively used Oracle's FreeSQL because it offered a simple, intuitive user interface and a linter with autocomplete.
Create / Drop table
Very simililar to regular SQL
Creating a table in Oracle PL/SQL looks a lot like what I'm used to in traditional relational databases.
CREATE TABLE CUSTOMERS(
id INT NOT NULL,
name VARCHAR (20) NOT NULL,
age INT NOT NULL,
address CHAR (25),
salary DECIMAL (18, 2),
PRIMARY KEY (id)
);Deleting a table and all its content is similar to traditional SQL.
DROP TABLE CUSTOMERS;Insert / Update
Also similar...
Inserting a row of data into a table is also just like you might be used to with traditional relational databases.
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Stanley', 38, 'Norway', 1400.00 );
COMMIT;The main difference from traditional SQL queries is that you must commit the insertion before closing the database connection. Otherwise, the data will not be stored persistently.
Updating a record is also similar to traditional SQL.
UPDATE CUSTOMERS
SET SALARY = 20000
WHERE ID = 1;
COMMIT;Procedure (IN)
Reusable logic that results in change.
Procedures are PL/SQL operations that typically result in a change in the database. A procedure does not normally return a value. In the example procedure below, a new row is added to the customers table.
CREATE OR REPLACE PROCEDURE add_customer (
v_id IN CUSTOMERS.ID%type,
v_name IN CUSTOMERS.NAME%type,
v_age IN CUSTOMERS.AGE%type,
v_address IN CUSTOMERS.ADDRESS%type,
v_salary IN CUSTOMERS.SALARY%type,
)
IS
-- Here you can declare local variables if needed.
-- This entire block is optional.
BEGIN
-- This is the logic of the procedure
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (v_id, v_name, v_age, v_address, v_salary);
COMMIT;
END add_customer;In the PL/SQL above, a procedure is created and defined with 5 input parameters. To run this procedure, you must use an anonymous procedure or another procedure that provides those parameters when calling the procedure, like this:
BEGIN
add_customer(2, 'John Doe', 55, 'Wales', 3000.00);
END;Not the use of an anonymous procedure in the example above. Operations like functions and procedures must be run like this, or from a named procedure or function.
Variable
PL/SQL handles variables in a way that might look familiar to other programming languages. What makes PL/SQL variables special is that they must be declared in the IS block of the procedure or function. Once declared they can be used like a traditional variable, using the PL/SQL assignment operator.
The naming convention for variables in PL/SQL is to prefix the variable name with "v_". For example, "v_count".
CREATE OR REPLACE PROCEDURE check_if_customer_exists (
v_id IN CUSTOMERS.ID%type
)
IS
v_count INT; -- Declared a variable
BEGIN
SELECT COUNT(*)
INTO v_count -- Stored the query result into the variable
FROM CUSTOMERS
WHERE ID = v_id;
IF v_count = 0 THEN -- Compared the variable
DBMS_OUTPUT.PUT_LINE('No customer found with ID: ' || v_id);
ELSE
DBMS_OUTPUT.PUT_LINE('A customer with ID: ' || v_id || ' exist!');
END IF;
END check_if_customer_exists;In the example above, we have a procedure that accepts the parameter v_id, which is of the same type as the ID column in the CUSTOMERS table. This procedure has a local variable called v_count that can store an integer. The result of the SELECT query is stored in the v_count variable, and later the procedure checks if v_count is equal to 0. If it is, the procedure prints a message to the terminal indicating that the customer was not found. If it is found, a message indicating that the customer was found will print to the terminal.
The procedure above can be run like this:
BEGIN
check_if_customer_exists (2);
END;Procedure (IN/OUT)
It is possible to make the procedure return a value... kind of...
A procedure can have output parameters as well as input parameters. The output parameter is a pointer to a variable. The nice thing (and also a bit confusing) in PL/SQL is that you don't have to explicitly specify that the parameter is a pointer. It will automatically understand it.
In the example below a status output paramter is added to the procedure:
CREATE OR REPLACE PROCEDURE add_customer_v2 (
v_id IN CUSTOMERS.ID%type,
v_name IN CUSTOMERS.NAME%type,
v_age IN CUSTOMERS.AGE%type,
v_address IN CUSTOMERS.ADDRESS%type,
v_salary IN CUSTOMERS.SALARY%type,
v_status OUT VARCHAR -- Output parameter!
)
BEGIN
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (v_id, v_name, v_age, v_address, v_salary);
COMMIT;
v_status := 'Juhuu! This was successfull!'; -- updates the output
END add_customer_v2;To call this procedure you must have a variable that can be passed into the procedure that can be updated by the called procedure.
DECLARE
v_status VARCHAR2(255);
BEGIN
add_customer_v2(3, 'Jane Doe', 22, '123 Main St', 50000, v_status);
DBMS_OUTPUT.PUT_LINE(v_status); -- Prints the status
END;Function
Reusable logic that "only" returns a value
A function is typically only used when you want to make PL/SQL perform some operations that don't result in a change in the database, and only returns a value.
CREATE OR REPLACE FUNCTION get_customer_age(
v_name IN CUSTOMERS.NAME%type
) return NUMBER
IS
v_customer CUSTOMERS%rowtype;
BEGIN
SELECT *
INTO v_customer
FROM CUSTOMERS
WHERE NAME = v_name;
RETURN v_customer_record.AGE;
END get_customer_age;The function above accepts a single parameter (customer ID), and returns an integer value (customer age). To make it possible to do this, a variable called v_customer is declared, and the row that was returned from the SELECT query was stored INTO the v_customer variable. The function then accesses and returns the AGE column on that row.
To run this function, you can simply execute it like this:
BEGIN
DBMS_OUTPUT.PUT_LINE('Stanley is: ' || get_customer_age('Stanley') || ' years old');
END;Record
Logical grouping of data
A record is simply a way to group related data into a logical unit. It might look like a struct in the C language or a row in a relational database. A record can be placed in either a global namespace or a specific namespace, like a package, procedure, or function.
The naming convention for PL/SQL record is to prefix the record with "r_".
Below is a simple example procedure that declares and uses a record type named "r_customer_simple":
CREATE OR REPLACE PROCEDURE test_get_customer_details_simple(
v_id IN CUSTOMERS.ID%type
)
IS
TYPE r_customer_simple IS RECORD (
name CUSTOMERS.NAME%type,
address CUSTOMERS.ADDRESS%type
); -- Declared the record
v_customer_record r_customer_simple; -- Use record as variable type
BEGIN
SELECT
name,
address
INTO v_customer_record -- Store query result into the variable
FROM CUSTOMERS
WHERE ID = v_id;
DBMS_OUTPUT.PUT_LINE('--- Found Customer ---');
DBMS_OUTPUT.PUT_LINE('Name: ' || v_customer_record.name);
DBMS_OUTPUT.PUT_LINE('Address: ' || v_customer_record.address);
END test_get_customer_details_simple;The procedure above illustrates how the member values of the record can be accessed using the "." member operator, just like you would on a column in a table row.
Call this procedure like this:
BEGIN
test_get_customer_details_simple(1);
END;Collection
A list of records
If you want to get and use a list of records, you must declare a collection. I like to compare them to a named typed array of structs in the C language, or a table in a database.
The nice thing about collections is that you can perform looping logic on a collection, and for each iteration in the loop, you can access each data just like you would on a record.
A collection can also be used as input/output from procedures and functions. This allows PL/SQL operations to be very flexible, and able to be built with modularity and reusability in mind, like regular programming languages.
A collection can be declared and used like this:
CREATE OR REPLACE PROCEDURE GENERATE_CUSTOMERS_CSV
IS
TYPE r_customer_simple IS RECORD (
name CUSTOMERS.NAME%type,
address CUSTOMERS.ADDRESS%type
); -- Declared the record
TYPE t_customers IS TABLE OF r_customer_simple; -- Declared the collection
v_customers t_customers; -- Use collection as variable type
BEGIN
SELECT
name,
address
BULK COLLECT INTO v_customers -- Store query result into the collection
FROM CUSTOMERS;
DBMS_OUTPUT.PUT_LINE('name,address');
FOR i IN 1..v_customers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_customers(i).name || ',' ||
v_customers(i).address
);
END LOOP;
END GET_CUSTOMER_DETAILS_SIMPLE;The procedure above takes the result from a query and stores it in a collection. This collection is then looped over to create a CSV output.
This was just a simple proof-of-concept to get your creativity going.
Package
Packing operations and types into a namespace.
To prevent accidental overwriting of operations and to simplify their use, it is possible to group operations (functions and procedures) and types (records and collections) into a package.
A package is used much like a module in JavaScript or Python, using dot notation. What makes PL/SQL packages special is that they are still stored in the DBMS, like the constructs demonstrated earlier in this article, but they are declared in two steps.
First, you declare a package specification, which is similar to interfaces in OOP languages. You define the operations, the parameters used by those operations, and the data type returned from a function. You also declare custom types that you want to bundle in the package.
A package specification for a package that includes all the functionality defined in this article might look like this:
-- Package specification
CREATE OR REPLACE PACKAGE pkg_users AS
-- Record
TYPE r_customer IS RECORD (
name CUSTOMERS.NAME%type,
address CUSTOMERS.ADDRESS%type
);
-- Collection
TYPE t_customers IS TABLE OF r_customer;
-- Procedure with multiple input parameters
PROCEDURE add (
v_id IN CUSTOMERS.ID%type,
v_name IN CUSTOMERS.NAME%type,
v_age IN CUSTOMERS.AGE%type,
v_address IN CUSTOMERS.ADDRESS%type,
v_salary IN CUSTOMERS.SALARY%type
);
-- Function
FUNCTION get_age(
v_name IN CUSTOMERS.NAME%type
) return NUMBER;
-- Procedure that uses a collection type
PROCEDURE generate_csv;
END pkg_users;Once the compilation is complete in the DBMS, the package body can be declared.
-- Body of the pkg_users package
CREATE OR REPLACE PACKAGE BODY pkg_users AS
-- Procedure with multiple input parameters
PROCEDURE add (
v_id IN CUSTOMERS.ID%type,
v_name IN CUSTOMERS.NAME%type,
v_age IN CUSTOMERS.AGE%type,
v_address IN CUSTOMERS.ADDRESS%type,
v_salary IN CUSTOMERS.SALARY%type
)
IS
BEGIN
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (v_id, v_name, v_age, v_address, v_salary);
COMMIT;
END add;
-- Function
FUNCTION get_age(
v_name IN CUSTOMERS.NAME%type
) return NUMBER
IS
v_customer CUSTOMERS%rowtype;
BEGIN
SELECT *
INTO v_customer
FROM CUSTOMERS
WHERE NAME = v_name;
RETURN v_customer.AGE;
END get_age;
-- Procedure that uses a collection type
PROCEDURE generate_csv
IS
v_customers t_customers;
BEGIN
SELECT
name,
address
BULK COLLECT INTO v_customers -- Store query result into the collection
FROM CUSTOMERS;
DBMS_OUTPUT.PUT_LINE('name,address');
FOR i IN 1..v_customers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_customers(i).name || ',' ||
v_customers(i).address
);
END LOOP;
END generate_csv;
END pkg_users;As you can read from the examples above, the operations can have much less verbose names because they are within a package namespace.
What should be noted is that, unlike ORMs, it's not possible to have private or protected operations in PL/SQL. Even if an operation is only designed to be used by operations within a package, it must be declared in the package specification.
This package can now be accessed using dot notation like this:
-- TEST add procedure
BEGIN
pkg_users.add(1, 'Alice', 30, '123 Main St', 60000);
pkg_users.add(2, 'Bob', 25, '456 Oak Ave', 55000);
END;
-- TEST get_age function
DECLARE
v_age NUMBER;
BEGIN
v_age := pkg_users.get_age('Alice');
DBMS_OUTPUT.PUT_LINE('Alice''s age: ' || v_age);
END;
-- TEST generate_csv procedure
BEGIN
pkg_users.generate_csv();
END;Trigger
Events in the DBMS
In my opinion, the killer feature of PL/SQL is triggers. These are much like event listeners in JavaScript that will listen for a specific event in the database.
With a trigger, you can, for example, perform an operation whenever a change is made to a row in the database, or if a row is updated, etc.
Here is a simple example of a trigger that listens for insertions into the users table and writes them to an audit log table.
CREATE OR REPLACE TRIGGER trg_audit_on_user_insert
AFTER INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
INSERT INTO audit_users (sql_user, user_id, old_name, new_name, action_date)
VALUES (USER, :NEW.id, :OLD.name, :NEW.name, SYSDATE);
END;In the example above, the old and new names of the user are stored in an audit table along with the SQL user who performed the insert and the time it occurred.
The possibilities with triggers are endless, and the best part is that they work without any application logic.
Summary
Thank you for reading this article. I hope it gave you a taste of what PL/SQL can be used for, and maybe you gained a new perspective on what a DBMS with similar capabilities can be used for.
I barely scratched the surface on PL/SQL in this article, and I would highly recommend the following book if you want more insight, better and deeper explanations.
Oracle PL/SQL by Example
Elena Rakhimov - 480 sider
Excellent explanation on the practical use of PL/SQL
"It was to the point, did not use unnecessary jargon, and was easy to read and understand!"