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.