PLSQL Course

Procedural Language

PL/SQL (Procedural Language for SQL), or PL/pgSQL (for PostgreSQL) are extensions of the SQL, allowing you to run code in your database. There are some small differences between the procedural language in Oracle, and the procedural language in PostgreSQL, but aside from that, almost everything is the same 👍.


PL/SQL (Oracle) - particularities

-- enable output, only done once, off by default
set serveroutput on
-- print something
dbms_output.put_line('something'); 
-- show more information about your errors (need to be called multiples times)
show errors;
-- raise an exception
exception_name EXCEPTION;
PRAGMA exception_init(exception_name, code);
raise_application_error(code, "message");

You need to add a / after END to compile your code.

Procedures 🙄

This is a sort of function, with some small differences (no return, parameters OUT, cannot be called in SQL, ...).

CREATE OR REPLACE PROCEDURE
procedure_name(name1 type, name2 type) IS
-- variables
BEGIN
-- code
END;
/ -- compile

-- call (one of these)
execute procedure_name(args);
call procedure_name(args);

PL/pgSQL (PostgreSQL) - particularities

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

Block of code

DECLARE
-- declare variables
BEGIN
-- instructions
-- COMMIT or ROLLBACK
EXCEPTION
-- handle exceptions
END;
  • DECLARE is optional. If you got variables, it's one per line, with a semicolon at the end of each line.
  • BEGIN is the code itself

Take note🤚! If you are doing a DML statement (INSERT, UPDATE, ...), then you must COMMIT (=save) or ROLLBACK (=cancel).

  • EXCEPTION is optional. You will handle exceptional behaviors (ex: errors) here.

Declare variables

We need variables, either to store the result of a SQL request, or because we are a good fellow, and declare constants instead of using hardcoded values 🤮.

The syntax is the following ([a] means that "a" is optional)

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

Pro tip 🚀: we are adding a v_ to make it explicit in the name, that this is a variable. It could be a parameter too (if we are inside a function/...).
Pro tip: you can create types with CREATE TYPE.

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}';
Inter types 😎

You may want your variable to not take a hardcoded type, but a type from an attribute

v_name table.attr%type; -- type of the attribute "attr" in "table"
v_name_copy v_name%type; -- type of the variable v_name
Records/Tuples 👌

We got a type to store only ONE row. You can access an attribute of a record with . (dot).

v_record record; -- record/tuple
v_record table%ROWTYPE; -- store a record/tuple of a table "table"

Statements

IF and CASE (switch)
IF something THEN [ELSIF another_condition THEN]
	[ELSE THEN]
END IF;

-- IF ELSEIF ... ELSE
CASE variable
	WHEN value1 THEN instructions
	WHEN value2 THEN instructions
	ELSE instructions
END CASE;
Loop (do while) and while
-- do ... while
LOOP something
	EXIT WHEN condition
END LOOP;

-- while
WHILE something LOOP some_code END LOOP;
For statement
-- 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;

Select into and Cursors

Requests returning up to one record 📘

You can use Select ... into variables. The variables given will be used to store the attributes of our selection.

-- THE VARIABLES MUST BE DECLARED
SELECT 5, 'Luna' INTO v_id, v_name FROM /* ... */;
SELECT * INTO v_record FROM /* ... */;
Requests returning 0 or more records 📚

A cursor is something storing one row too. But, you can iterate your cursor, to see each record in the result.

  • DECLARE
v_cursor CURSOR IS sql_request;
v_cursor CURSOR FOR sql_request;
CURSOR v_cursor FOR sql_request; -- sometimes
  • Usage
FOR v_entry IN v_cursor LOOP
	-- v_entry is a RECORD
	-- use . (dot) to get an attribute (=field)
END LOOP;
  • Explicit usage
OPEN v_cursor;
LOOP
	FETCH v_cursor INTO v_entry; -- v_entry is a record, DECLARE IT
	EXIT WHEN v_cursor%NOTFOUND;
	-- use . (dot) to get a attribute (=field)
	-- you may use the MOVE clause
END;
CLOSE v_cursor;
  • Inspect your cursor

You can ask your cursor about things

v_cursor%FOUND -- true if there are still rows, NULL if fetch never called
v_cursor%NOTFOUND -- true if no more rows, NULL if fetch never called
v_cursor%ISOPEN -- 🙄
v_cursor%ROWCOUNT -- get the number of rows

Please, note that exceptions are not working on cursors (e.g.: no NO_DATA_FOUND).


Exceptions

An exception is a signal. Most of the time, it's raised because an error happened. You can handle it in the block exception, but if you didn't, then the signal will be sent upward (=to the caller). If we are back on the request, then the request is crashing with an error.

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 WHEN in 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 got a lot of predefined exceptions, check your database documentation.


Functions

A function is a block of code, that may take parameters, and return something.

CREATE OR REPLACE FUNCTION name(args) 
RETURNS r_type AS $$
-- declare ...
BEGIN
    -- code ...
    [RETURN result;]
END; $$ LANGUAGE plpgsql;
  • name is obviously the name of our function
  • args are the argument, with name type for each argument.
  • r_type is the return type, such as VOID, INT, etc.
  • You can call DECLARE, then write your code inside BEGIN-END
  • RETURN is returning the result of your function (if the return type isn't void).

Note: if you add something inside $$, make sure the content is matching the second delimiter $$.
Pro tip: you can use $1, ... instead of the name of the arguments.


Triggers

A trigger (déclencheur) is a function that will be triggered on an event, such as inserting, updating, or deleting an element. You could use it to calculate derived fields, or to archive deleted records.

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

A trigger is executed either BEFORE or AFTER an event, such as INSERT, UPDATE, DELETE, .... You may set a trigger only on one column using UPDATE(column) or UPDATE OF column.

Add OR REPLACE if you are updating an existing trigger.

  • FOR EACH ROW: If you are planning to update every row, then add it. This may be the case, if the grade of each person is based on the median. If a grade changes, we need to update every grade.

  • WHEN: You may add a when clause. If the condition is false, then the trigger is not triggered for this record.

Pro tip: you can make a trigger for multiples events, with OR such as INSERT OR UPDATE.

A function called by a trigger isn't a normal function. Aside from the two variables NEW and OLD, you will have to return (type = TRIGGER)

  • NULL: cancel insert/update/delete
  • NEW/OLD/...: the new record
  • If you omit RETURN, then it's the same as RETURN NEW

Vocabulary 😎

  • Trigger DML: executed on every record that triggered the event
  • Trigger DML line: executed once per event
  • Trigger DDL (Trigger base/schéma): executed once

Pro tip: no COMMIT/ROLLBACK in a trigger, as the request will already do once.

OLD and NEW ✨

When updating a line, you got the old record in OLD, the one after updating in NEW. For INSERT, you got only NEW (OLD is null). For DELETE, you got only OLD (NEW is null).

You will most likely use them inside your trigger ✨. They are called correlation variables. They are records, such use . to get an attribute.

Sometimes, we are renaming them. Right before the FOR EACH ROW (even if you don't have one), you could do this, to use :old and :new.

REFERENCING OLD :old NEW :new
Delete a trigger
DROP TRIGGER trigger_name ON some_table;
DROP TRIGGER IF EXISTS trigger_name ON some_table;

Some templates

Function
CREATE OR REPLACE FUNCTION some_function(param1 INTEGER, param2 INTEGER) RETURNS VOID AS $$
BEGIN
    RAISE EXCEPTION 'param1 is %. param2 is %.', param1, param2;
END; $$ LANGUAGE plpgsql;

Then test

SELECT some_function(5,10);
-- [...] param1 is 5. param2 is 10.
Catch an exception
CREATE OR REPLACE FUNCTION some_function() RETURNS VOID AS $$
BEGIN
    Select 1/0;
EXCEPTION
    WHEN division_by_zero then RAISE EXCEPTION 'catch';
END; $$ LANGUAGE plpgsql;
Select into
CREATE OR REPLACE FUNCTION some_function() RETURNS RECORD AS $$
DECLARE
    v_record RECORD;
BEGIN
    Select 1, 'Luna', 18, 'Woman' INTO v_record;
    RETURN v_record;
END; $$ LANGUAGE plpgsql;

Then test

SELECT some_function()
-- (1,Luna,18,Woman)
Using a cursor
CREATE OR REPLACE FUNCTION some_function() RETURNS void AS $$
DECLARE
v_cursor CURSOR IS SELECT 'Luna' as NAME; -- some real request 🙄
BEGIN
    FOR v_entry IN v_cursor LOOP
        -- do something
        -- raise exception :)
        RAISE '%', v_entry.name;
	END LOOP;
END; $$ LANGUAGE plpgsql;
SELECT some_function();
-- [...] Luna
Triggers
CREATE OR REPLACE FUNCTION some_function() RETURNS TRIGGER AS $$
BEGIN
    RETURN NEW; -- 
END; $$ LANGUAGE plpgsql;

You should check if "BEFORE INSERT" is what you want, and if you should remove "FOR EACH ROW"...

CREATE TRIGGER some_trigger BEFORE INSERT ON some_table FOR EACH ROW
EXECUTE PROCEDURE some_function();

Sources