Week 11 gave us a quick introduction to PL/SQL.
The primary tools of PL SQL are:
- Anonymous Blocks
- Named Blocks
- Subprograms
- Triggers
Of these subprograms and triggers would be the most commonly used.
The structure of PL/SQL:
DECLARE
/*Declarative section is where variables and other objects such as cursors, types, exceptions, etc. are declared. Local
procedures and functions can also be declared and will be available for this block only. This section is optional */
BEGIN
/* Executable section can consist of both SQL statement and procedural statements. This section is where the work the
block has to perform is done. This section is mandatory */
EXCEPTION
/* Exception handling section is where errors are handled. The code in this section gets executed only when an error
is encountered. This section is optional. */
END;
/
A couple of examples of PL/SQL blocks:
-- Whenever emp_salary is updated or an employee deleted, this trigger enters a corresponding record into
--'EMPLOYEE_SALARY_CHANGE' table.
CREATE OR REPLACE TRIGGER trg_trackSalaryChange
AFTER UPDATE OR DELETE OF e_salary ON employee
FOR EACH ROW
DECLARE
oper VARCHAR2(10);
new_sal NUMBER(6,2);
BEGIN
IF updating THEN
oper := 'UPDATE';
new_sal := :new.e_salary;
IF :new.e_salary <= 0 THEN
RAISE_APPLICATION_ERROR (-20000, 'SALARY CANNOT BE ZERO OR NEGATIVE.');
END IF;
END IF;
IF deleting THEN
oper := 'DELETE';
new_sal := NULL;
END IF;
INSERT INTO employee_salary_change VALUES (:old.e_no, :old.e_name, :old.e_salary,
new_sal, user, sysdate, oper);
END;
/
For some basics on PL SQL such as syntax and constructs visit: http://plsql-tutorial.com/index.htm
