Transcript where not exists

Database Systems
SQL Queries, PL/SQL, Triggers
Gergely Czuczy
Cause Light Wounds: I call upon chaos to cause
unbalanced parentheses.
Todays Database
• FIRST, run this script:
http://phoemix.harmless.hu/dbs/20040430/shema.sql
• _AFTER_ the first, run this one:
http://phoemix.harmless.hu/dbs/20040430/data.sql
(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.
(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.
(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)
)
;
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.
PL/SQL
• The PL/SQL block:
• DECLARE
<variable delcarations>
BEGIN
<PL/SQL code>
END
PL/SQL
•
•
•
•
Declaring variables:
Right after the DECLARE keyword
<variable name> <type>;
Example:
emp_name varchar2(1024);
current date;
nextid integer;
PL/SQL
• IF-statements:
• IF <statement> THEN
<block>
ELSIF <statemenet> THEN
END IF;
• The ELSIF branch is optional
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
Trigger + PL/SQL
• Triggers are mostly written in PL/SQL
• Special variables that you can use:
– NEW: the newly inserted row’s 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
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;
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;
Sequence
• A sequence stores a value that can be
incremented by a function call. It’s useful
for automagicaly creating ID’s.
• To create a sequence use:
CREATE SEQUENCE some_seq;
• To get the next value use:
some_seq.nextval();
Exercise
Implement a trigger in your project!
Have a nice weekend!