Transcript PPT - NYU Stern School of Business

C20.0046: Database
Management Systems
Lecture #21
M.P. Johnson
Stern School of Business, NYU
Spring, 2008
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
1
Agenda





Stored procedures?
Triggers
Transactions
RAID?
Implementation?
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
2
Integration with SQL
DECLARE
l_book_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_book_count
FROM books
WHERE author LIKE '%FEUERSTEIN, STEVEN%';
DBMS_OUTPUT.PUT_LINE (
'Steven has written (or co-written) ' || l_book_count ||
' books.');
END;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
3
Dynamic PL/SQL

E.g.: write function to return number rows in
an arbitrary table
CREATE OR REPLACE FUNCTION rowCount (
tabname IN VARCHAR2) return integer as
retval integer;
begin
execute immediate 'select count(*) from ' || tabname into
retval;
return retval;
end;
/
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
4
Dynamic PL/SQL for DDL

Ordinarily can’t do DDL in PL/SQL
But you can in dynamic PL/SQL

Here’s an e.g.:

CREATE OR REPLACE procedure
dropproc(procname in varchar2) as
begin
execute immediate 'drop procedure ' || procname;
end;
/
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
5
Live examples

Factorial function:


Converting between bases:


http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/
fact.sql
http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/
numsys.sql
Directory of examples:

http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
6
SPs in MySQL (5.0)

Generally similar, though technical diffs

Need to temporarily redefine ; delimiter
mysql> drop procedure if exists hello;
myslq> delimiter /
mysql> create procedure hello()
-> begin
-> select 'hi';
-> end;
-> /
mysql> delimiter ;
mysql> call hello();
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
7
New topic: Triggers

PL/SQL programs that run automatically (are
“triggered”) when a certain event occurs

E.g.: on insert to some table
On system start-up
On delete from table




Big benefit: need not be called explicitly
However row in table x is deleted, the trigger
gets called
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
8
Trigger events


Trigger code may be “triggered” by many kinds of
events:
Oracle start-up/shut-down


Data updates:





Triggers may replace initialization scripts
Delete: maybe delete related rows
Inserts
Updates: maybe make other rows consistent
Delete: maybe prevent
DDL statements

Log creation of all objects, e.g.
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
9
Triggers

Constraints state what must remain true

DBMS decides when to check

Triggers are instructions to perform at explicitly
specified times

Three aspects:




An event (e.g., update to an attribute)
A condition (e.g., a test of that update value)
An action (the trigger’s effect) (deletion, update, insertion)
When the event occurs, DBMS checks the
constraint, and if it is satisfied, performs the action
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
10
DML trigger options

The trigger may be:



The trigger may run




Statement-level (e.g., a DELETE WHERE statement) or
Row-level (e.g., for each row deleted)
BEFORE
AFTER or
INSTEAD OF the statement (in Oracle, not in others)
It may be triggered by



INSERTs
UPDATEs
DELETEs
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
11
Trigger form
CREATE [OR REPLACE] TRIGGER trigger
name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE | UPDATE OF
column list} ON table name
[FOR EACH ROW]
[WHEN (...)]
[DECLARE ... ]
BEGIN
... executable statements ...
[EXCEPTION ... ]
END [trigger name];
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
12
Trigger type examples

First run copy_tables.sql
1.
statement_vs_row.sql

INSERT INTO to_table SELECT * FROM from_table;
before_vs_after.sql
2.

INSERT INTO to_table SELECT * FROM from_table;
one_trigger_per_type.sql
3.

INSERT INTO to_table VALUES (1);
UPDATE to_table SET col1 = 10;
DELETE to_table;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
13
DML Trigger e.g.
CREATE OR REPLACE TRIGGER
validate_employee_changes
BEFORE INSERT OR UPDATE
ON employee
FOR EACH ROW
BEGIN
check_age (:NEW.date_of_birth);
check_resume (:NEW.resume);
END;

Q: Why is this (maybe) better than client-side
validation?
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
14
Triggers with WHEN
CREATE OR REPLACE TRIGGER check_raise
AFTER UPDATE OF salary, commission
ON employee
FOR EACH ROW
WHEN ((OLD.salary != NEW.salary OR
(OLD.salary IS NULL AND NEW.salary IS NULL))
OR (OLD.commission != NEW.commission OR
(OLD.commission IS NULL AND NEW.commission IS NULL)))
BEGIN
...
END;

NB: WHEN applies only to row-level triggers
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
15
Triggers with WHEN


Parentheses are required
Can only call built-in functions in when

Packages like DBMS_OUTPUT are not allowed
CREATE OR REPLACE TRIGGER
valid_when_clause
BEFORE INSERT ON frame
FOR EACH ROW
WHEN ( TO_CHAR(SYSDATE,'HH24')
BETWEEN 9 AND 17 )
...
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
16
Simple trigger example

R(id, data, last-modified)




Goal: whenever data is modified, update lastmodified date
Could modify all scripts/programs that touch this
table
CREATE TRIGGER UpdateDateTrigger


data is a large string
Last-modified is a newly added date field
Bad idea
Better: user a trigger
AFTER UPDATE OF data ON R
REFERENCING
NEW ROW AS NewTuple
FOR EACH ROW
BEGIN
NewTuple.last-modified = sysdate;
END;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
17
Multiple DML actions


DML actions may be ORed together
To find actual
action, check:



INSERTING
DELETING
UPDATING
CREATE OR REPLACE TRIGGER
three_for_the_price_of_one
BEFORE DELETE OR INSERT OR UPDATE
ON account_transaction
FOR EACH ROW
BEGIN
IF INSERTING
THEN
:NEW.created_by := USER;
:NEW.created_date := SYSDATE;
ELSIF DELETING
THEN
audit_deletion(USER,SYSDATE);
18
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
END;
More on UPDATING

UPDATING may be called for partic. columns
CREATE OR REPLACE TRIGGER validate_update
BEFORE UPDATE ON account_transaction
FOR EACH ROW
BEGIN
IF UPDATING ('ACCOUNT_NO')
THEN
errpkg.raise('Account number cannot be updated');
END IF;
END;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
19
Extended auditing example



Tables: grades, grades_audit
Run: grades_tables.sql, grades_audit.sql
Cases: hacker changes grades, deletes
others
UPDATE grades
SET grade = 'A+'
WHERE student_id = 1
AND class_id = 101;
DELETE grades
WHERE student_id = 2
AND class_id = 101;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
20
Extended auditing example


Run: grades_tables.sql, grades_audit2.sql
Cases: hacker changes student or class ids
UPDATE grades SET student_id = 3
WHERE student_id = 1 AND class_id = 101;
UPDATE grades SET student_id = 1
WHERE student_id = 2 AND class_id = 101;
UPDATE grades SET student_id = 2
WHERE student_id = 3 AND class_id = 101;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
21
DDL Triggers

Respond to DDL events



Creating/dropping tables, indices, etc.
ALTER TABLE etc.
General form:
CREATE [OR REPLACE] TRIGGER trigger name
{BEFORE | AFTER| {DDL event} ON
{DATABASE | SCHEMA}
DECLARE
Variable declarations
BEGIN
... some code...
END;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
22
DDL trigger e.g.

Town crier examples triggered by creates:

uninformed_town_crier.sql

informed_town_crier.sql
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
23
Available DDL events


CREATE, ALTER, DROP, GRANT, RENAME,
REVOKE, TRUNCATE
DDL: any DDL event
no_create.sql
CREATE OR REPLACE TRIGGER no_create
AFTER CREATE ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20000,
'ERROR : Objects cannot be created in the
production database.');
END;

Q: Does this work??
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
24
DB Event triggers

Form similar to DDL triggers:
CREATE [OR REPLACE] TRIGGER trigger name
{BEFORE | AFTER} {database event} ON
{DATABASE | SCHEMA}
DECLARE
Variable declarations
BEGIN
... some code...
END;

Triggering events: STARTUP, SHUTDOWN,
SERVERERROR, LOGON, LOGOFF
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
25
DB event restrictions

Have BEFORE and AFTER as above, but they
don’t always apply:

No BEFORE STARTUP/LOGON/SERVERERROR

No AFTER SHUTDOWN/LOGOFF
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
26
DB Trigger e.g.

Gather stats before shutdown:
CREATE OR REPLACE TRIGGER on_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
gather_system_stats;
END;

Log error messages
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
27
New-old topic: Transactions

So far, have simply issued commands


Recall, though: an xact is an operation/set of
ops executed atomically


Ignored xacts
In one instant
ACID test:


Xacts are atomic
Each xact (not each statement) must leave the DB
consistent
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
28
Default xact behavior (in Oracle)


An xact begins upon login
By default, xact lasts until logoff



Except for DDL statements
They automatically commit
Examples with two views of tbl…


But with TYPE=innodb !
mysql> set autocommit = 0
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
29
Direct xact instructions

At any point, may explicitly COMMIT:




Conversely, can ROLLBACK



SQL> COMMIT;
Saves all statements entered up to now
Begins new xact
SQL> ROLLBACK;
Cancels all statements entered since start of xact
Example: delete from emp; or delete junk;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
30
Direct xact instructions



Remember, DDL statements are autocommitted
 They cannot be rollbacked
Examples:
drop table junk;
rollback;
truncate table junk;
rollback;

Q: Why doesn’t rollback “work”?
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
31
Savepoints

Xacts are atomic
Can rollback to beginning of current xact

But might want to rollback only part way


Make 10 changes, make one bad change
Want to: roll back to before last change

Don’t have Word-like multiple undo


But do have savepoints
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
32
Savepoints

Create a savepoint:
SAVEPOINT savept_name;
--changes
 emp example:
SAVEPOINT sp1;
--changes
 Can skip savepoints
SAVEPOINT sp2;
 But can ROLLBACK --changes
only backwards
SAVEPOINT sp3
--changes
 Can ROLLBACK
only to last COMMIT ROLLBACK TO SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
33
AUTOCOMMIT

Finally, can turn AUTOCOMMIT on:





Oralce: SQL> SET AUTOCOMMIT ON;
Mysql: mysql> SET AUTOCOMMIT=1;
Can put this in your config file
Can specify through JDBC, etc.
Then each statement is auto-committed as its
own xact

Not just DDL statements
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
34
RAID levels


RAID level 1: each disk gets a mirror
RAID level 4: one disk is xor of all others


Each bit is sum mod 2 of corresponding bits
E.g.:




Disk 1: 10110011
Disk 2: 10101010
Disk 3: 00111000
Disk 4:

How to recover?

What’s the disadvantage of R4?

Various other RAID levels in text…
M.P. Johnson, DBMS, Stern/NYU, Spring 2008
35