PL/SQL ¶

Many DBMS have their own procedural languages which extend SQL and allow us to write procedural code (if statement, loops...) that can be executed within the database.

• 💵 Oracle: PL/SQL (Procedural Language for SQL)
• 🍰 PostgreSQL: PL/pgSQL (similar to PL/SQL)
• 🐭 Microsoft SQL Server: T-SQL (Transact-SQL)
• ...

This course covers PL/SQL and PL/pgSQL.

PL/SQL-specific ¶

You need to enable server output to see prints.

set serveroutput on
show errors -- call it each time you got an error

To print something, use:

dbms_output.put_line('something'); 

You also need to add a / after the END; of a block:

[...]
END;
/

In Oracle, raising your own exceptions is done using:

exception_name EXCEPTION;
PRAGMA exception_init(exception_name, code);
raise_application_error(code, "message");

PL/pgSQL-specific ¶

To print something, you need to use raise notice.

RAISE NOTICE 'something';
RAISE NOTICE 'Some message with a parameter: %', 5;
RAISE NOTICE '... % ... % ...', 5, 7;

The usual block of code:

DECLARE
-- declare variables
BEGIN
-- instructions
-- (if applicable) COMMIT or ROLLBACK
EXCEPTION
-- handle exceptions
END;

Variables are needed to store the result of an SQL request, otherwise they are used to store constants.

The syntax is (v_ is optional, "v" stands for variable):

v_name [CONSTANT] type [NOT NULL] [ := expression ]

✍️ Use some_table.some_attribute%type or v_name%type to use the type of some attribute or another variable.

✍️ Use the type record to store any one-line SQL result. Use some_table%ROWTYPE to ensure the result is from one specific table.

Examples 🔥

-- constant
v_five CONSTANT integer := 5;
-- not null
v_real real NOT NULL := 3.0;
-- simple variable
v_date date;
-- array
v_array int array[3] := '{0,1,2}';

Usual branching statement.

IF condition THEN
    -- ELSIF another_condition THEN
    -- ELSE THEN
END IF;

Execute instructions based on a variable's value.

-- IF ELSEIF ... ELSE
CASE variable
	WHEN value1 THEN instructions  -- variable == value1
	WHEN value2 THEN instructions  -- ...
	ELSE instructions              -- default
END CASE;

Do something until condition is satisfied.

LOOP
    -- something
    EXIT WHEN condition
END LOOP;

While condition is not satisfied, do something.

WHILE condition LOOP
    -- something
END LOOP;

Loops using indexes.

-- for i in seq / for (i=min; i<max; i++){}
FOR i IN min AND max LOOP some_code END LOOP;
-- reverse loop
FOR i IN REVERSE min AND max LOOP some_code END LOOP;

To exploit the result of a SELECT, you need to store it. You must first declare variables, then you can use them as follows:

SQL requests returning zero or one rows ¶

-- one variable per selected field
SELECT 42, 'Hello' INTO v_id, v_name FROM /* ... */;
-- using a variable of type "record"
SELECT * INTO v_record FROM /* ... */;

SQL requests returning n rows ¶

We move the cursor to iterate the result.

-- use IS or FOR, can use CURSOR before v_cursor
-- use v_cursor%ROWCOUNT to the number of rows
v_cursor CURSOR IS sql_request;

In the code, you can use a FOR..LOOP.

FOR v_entry IN v_cursor LOOP
    -- use "v_entry.XXX" to access the RECORD fields
END LOOP;

Cursors do not raise exceptions. Use these:

v_cursor%FOUND    -- true if there are still rows, NULL if fetch was never called
v_cursor%NOTFOUND -- true if no more rows, NULL if fetch never called
v_cursor%ISOPEN   -- true if the cursor is open

OPEN v_cursor;
LOOP
    FETCH v_cursor INTO v_entry; -- store in v_entry
    EXIT WHEN v_cursor%NOTFOUND;
    -- v_entry.XXX to access the RECORD fields
    -- you may use the MOVE clause
END;
CLOSE v_cursor;

You can raise an exception with RAISE

RAISE 'same as raise notice format' [, values]
RAISE '...' USING ERRCODE = 'error_code'
RAISE SOME_EXCEPTION
RAISE SOME_EXCEPTION USING MESSAGE = 'additional message'

Some examples

RAISE 'Error: no records matching id=%', id;
RAISE DIVISION_BY_ZERO;

You can catch an exception, in EXCEPTION, with WHEN. You can make multiples of them, for each exception.

WHEN NO_DATA_FOUND THEN some_code;
WHEN DIVISION_BY_ZERO THEN some_code; -- ZERO_DIVIDE in Oracle
WHEN UNIQUE_VIOLATION THEN some_code;
-- you tried a select into, with a request
-- returning more than one row.
WHEN TOO_MANY_ROWS THEN some_code;
-- every exception, fallback
WHEN OTHERS THEN some_code;

You can create functions using:

CREATE OR REPLACE FUNCTION name(args) 
RETURNS r_type -- AS $$
-- declare
BEGIN
    -- code
    -- RETURN result; (if any)
END; -- $$ LANGUAGE plpgsql;

To use a function: SELECT some_function().

Args

• args is a list of arguments : arg1 [IN/OUT] type1[, ...].
• IN and OUT are optional. By default, all parameters are IN. Parameters IN are read-only, write those OUT must be written to.
• $n is an alias to the nth argument ($1 == the first one).

Result

• r_type is a return type such as VOID, INT...

✍️ When using PL/pgSQL, uncomment "AS $$" and "$$ LANGUAGE plpgsql;". Both $$ are matching, if you change one, change the other.

It's possible to call a trigger function when events (insert, update, delete...) occur. 🔥 Usages: calculate derived fields, archiving...

CREATE TRIGGER trigger_name
BEFORE /* or AFTER */ some_event ON some_table
-- FOR EACH ROW
-- WHEN some_condition
-- Then:
--    some code (ex: BEGIN ... END)
--    or
--    EXECUTE PROCEDURE function(args);

• Triggers are either executed BEFORE or AFTER an event.
• Events are INSERT, UPDATE, UPDATE(column) or UPDATE OF column, DELETE... Separate events using OR such as INSERT OR UPDATE.
• Add OR REPLACE after CREATE to update a trigger.
• Add FOR EACH ROW to execute the code for each updated row instead of once for the whole query.
• Use WHEN to add another condition to execute the trigger

The code executed by the trigger has access to two pseudo records: NEW and OLD. The code returns RETURN NEW by default (type=TRIGGER).

✍️ Returning NULL cancels INSERT/UPDATE/DELETE.

⚠️ Do not commit/rollback in a commit, or it will be done twice.

🔥 Some rename OLD/NEW to :old/:new using:

REFERENCING OLD :old NEW :new

To delete a trigger:

DROP TRIGGER trigger_name ON some_table;
DROP TRIGGER IF EXISTS trigger_name ON some_table;

Procedures are not available in PL/pgSQL. They are similar to functions, but they

• 🥯 don't return a result
• 🧳 write any result in variables marked OUT
• 🌍 can be called outside a SELECT statement

To execute a procedure, you can use:

procedure_name(args);
execute procedure_name(args);
call procedure_name(args);

CREATE OR REPLACE PROCEDURE
procedure_name(arg1 IN type, arg2 OUT type) IS
-- variables
BEGIN
    -- code
    arg2 := arg1 -- write in arg2 the result
END;
/ -- compile 

• INSTEAD OF
• a = 5 or a := 5 in PL/SQL