Database Systems SQL Queries, PL/SQL, Triggers

1
Database SystemsSQL Queries, PL/SQL, Triggers

• Gergely Czuczy
• Cause Light Wounds I call upon chaos to cause
  unbalanced parentheses.

2
Todays Database

• FIRST, run this scripthttp//phoemix.harmless.hu
  /dbs/20040430/shema.sql
• _AFTER_ the first, run this onehttp//phoemix.ha
  rmless.hu/dbs/20040430/data.sql

3
(NOT) EXISTS

• You can satisfy queries where you have to select
  things which are related to all other things. For
  an example select those pilots who know all the
  planes.

4
(NOT) EXISTS

• First, select all the planes(q1).
• From q1 subtract the planes that the actual pilot
  knows(q2)
• You will get of a set of planes from q2. We have
  to select those pilots where this set is empty.

5
(NOT) EXISTS

• SELECT p1.name
• FROM pilots p1
• WHERE NOT EXISTS (
• (SELECT id FROM planes)
• MINUS
• (SELECT k1.planeid FROM knows k1 WHERE
  k1.pilotid p1.id)
• )

6
PL/SQL

• With PL/SQL you will be able to do many jobs in
  oracle that require programming, such as writing
  triggers, functions, and so on.
• In a PL/SQL block you can use queries such in
  anywhere else.

7
PL/SQL

• The PL/SQL block
• DECLAREltvariable delcarationsgtBEGINltPL/SQL
  codegtEND

8
PL/SQL

• Declaring variables
• Right after the DECLARE keyword
• ltvariable namegt lttypegt
• Exampleemp_name varchar2(1024)current
  datenextid integer

9
PL/SQL

• IF-statements
• IF ltstatementgt THENltblockgtELSIF ltstatemenetgt
  THENEND IF
• The ELSIF branch is optional

10
Trigger

• A trigger executes a procedure for some kind of
  action.
• For an example on every INSERT statement on a
  table a trigger executed that logs the newly
  inserted row.
• Triggers can be fired before or after some kind
  of action

11
Trigger PL/SQL

• Triggers are mostly written in PL/SQL
• Special variables that you can use
• NEW the newly inserted rows data, or the
  updated data in an UPDATE query
• OLD the old data in a DELETE/UPDATE trigger
• Where refering to NEW/OLD you have to prefix it
  by a , ex NEW.personid

12
Trigger Example 1

• CREATE OR REPLACE TRIGGER trg_knows
• BEFORE DELETE OR INSERT ON knows FOR EACH ROW
• DECLARE
• BEGIN
• IF INSERTING THEN
• INSERT INTO logs (systime,action) VALUES
  (sysdate,NEW.pilotid' has learned plane
  'NEW.planeid)
• ELSIF DELETING THEN
• INSERT INTO logs (systime,action) VALUES
  (sysdate,NEW.pilotid' has forgotten plane
  'NEW.planeid)
• END IF
• END

13
Trigger Example 2

• CREATE OR REPLACE TRIGGER trg_knows_update
• BEFORE UPDATE ON knows FOR EACH ROW
• DECLARE
• BEGIN
• NEW.pilotid OLD.pilotid
• NEW.planeid OLD.planeid
• END

14
Sequence

• A sequence stores a value that can be incremented
  by a function call. Its useful for automagicaly
  creating IDs.
• To create a sequence useCREATE SEQUENCE
  some_seq
• To get the next value usesome_seq.nextval()

15
Exercise

• Implement a trigger in your project!

16
Have a nice weekend!