Unfortunately I was absent for week 4’s lecture and tutorial. My review of the week will be limited to the printed material. PL/SQL was the topic of week 4.
Ok, so to start with I have not used PL/SQL before so it is worth defining; a procedural programming language developed by Oracle as an extension for their relational databases. It allows for complex applications to further leverage the DB layer.
The general structure of PL/SQL:
DECLARE
<variable declarations>
BEGIN
<program statements>
EXCEPTION
<error handling statements>
END;
Any oracle datatypes can be used (CHAR, VARCHAR2, NUMBER, etc).
Constructs covered in the lecture were:
- IF/THEN/ELSE
- Loops, pretest and posttest
- Cursor (implicit[select,from,where] and explicit[FOR DroomRow IN DCursor LOOP])
- Exception(pre-defined, undefined, user defined) and error handling
Triggers is an interesting topic, I have always been of the opinion that this sort of procedure should be in the application layer (aside from logging). The syntax:
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF]}
{INSERT|UPDATE|DELETE}
[OF <attribute_name>] ON Table_name
[FOR EACH ROW] [WHEN (condition)]
BEGIN
Trigger_body
END;
I will need to work through the tutorial work to get some practice with this material!