PLSQL Tutorial

So PL/SQL is Oracle’s special language for their databases. It allows you to do some extra stuffs. I’m currently reading through a tutorial on ORACLETUTORIAL. If you are not me then I strongly suggest you got there instead and learn from them.

A PL/SQL block has the following general layout:

DECLARE
	variable variable_type;
	-- DECLARE IS AN OPTIONAL SECTION
BEGIN
	variable := 'A value';
	EXCEPTION
		WHEN EXCEPTION_TYPE THEN
			-- HANDLE EXCEPTION
			-- The exception block is optional too
END;

Data Types

There are two different types of values in PL/SQL: scalar and composite.

Scalar types store a distinct value and are singular values and can be a number, boolean, character or datetime. Whereas a composite is a collection of records. Here’s more about composites (records as least).

Numbers

There are several number types. We have NUMBER, BINARY_FLOAT and BINARY_DOUBLE which are all SQL data types. However, we also have access to a PLS_INTEGER type. The interesting thing about PLS_INTEGER types is that they use hardware math so they are much quicker than the rest.

Booleans

These are PL/SQL specific. This means that I can make a BOOLEAN value and then store it in a table.

Characters

Characters can be any of the following:

• CHAR
• VARCHAR2
• LONG
• RAW
• LONG RAW
• ROWID
• UROWID

Variables

There’s a convention that local variables start with l_ whereas the global bad bois start with g_.

Note

To see the output from the following examples ensure you add SET SERVEROUTPUT ON; to the top of the script.

You can assign default values in one of two ways:

DECLARE
	l_employee VARCHAR2( 100 ) := 'Ramona';
	l_employee VARCHAR2( 100 ) DEFAULT 'Ramona';
BEGIN
	NULL;
END;

In this case both would set l_employee to the same data type and default value. Note, this actual code would throw an error due to the fact l_employee has been declared twice.

Here’s a basic variable assignment example:

DECLARE
	l_location VARCHAR2( 100 ) := 'Tokyo';
BEGIN
	l_location := 'Seoul';
	DBMS_OUTPUT.PUT_LINE(l_location);
END;

There’s also a way to anchored declarations. You can see an example in the following code snippet. (Note: this snippet is directly from ORACLETUTORIAL so all credit goes to them.)

DECLARE
    l_credit_limit   customers.credit_limit%TYPE;
    l_average_credit l_credit_limit%TYPE;
    l_max_credit     l_credit_limit%TYPE;
    l_min_credit     l_credit_limit%TYPE;
BEGIN
    -- get credit limits
    SELECT 
        MIN(credit_limit), 
        MAX(credit_limit), 
        AVG(credit_limit)
    INTO 
        l_min_credit,
        l_max_credit, 
        l_average_credit
    FROM customers;
    
    
    SELECT 
        credit_limit
    INTO 
        l_credit_limit
    FROM 
        customers
    WHERE 
        customer_id = 100;
 
    -- show the credits     
    dbms_output.put_line('Min Credit: ' || l_min_credit);
    dbms_output.put_line('Max Credit: ' || l_max_credit);
    dbms_output.put_line('Avg Credit: ' || l_average_credit);
 
    -- show customer credit    
    dbms_output.put_line('Customer Credit: ' || l_credit_limit);
END;

I am powering through some of the pages as it is basic stuff.