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
