C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 19

• M.P. Johnson
• Stern School of Business, NYU
• Spring, 2005

2
Homework

• Project part 3 due today
• Project part 4 up soon
• Topic populating your tables with data
• Using MySQLs bulk loader
• Can get errors parsing
• Start early!

3
NYU Infrastructure News

• The Oracle client should be working as usual
  again
• Can run sqlplus and ProC programs without
  touching TWO_TASK

4
Agenda Programming for SQL

• Embedded SQL
• ProC, SQLJ
• CLI
• SQL/CLI in C
• JDBC in Java
• DBI/DBDs in Perl, PHP
• Stored Procedures
• PL/SQL, Triggers
• MySQL Bulk Loader

5
Goals

• Have more confidence in writing
  scripts/functions/procedures
• Be able to respond to ftn/proc errors
• Be able to define a simple trigger
• So code executes at the right time
• Understand how to load a text file of records
  into MySQL

6
PL/SQL

• Procedural Language for SQL
• Oracles language for stored procedures
• Simple, interpreted, procedural language
• But Pascal-like
• BEGIN END, not
• AND OR, not
• vars defined at top of procedre
• how return works

7
Hello, World

• Try again

SET SERVEROUTPUT ON BEGIN -- print out
message DBMS_OUTPUT.PUT_LINE('Hello World,
from PL/SQL') END /
8
Example procedure

• Define the procedure
• Now we can call it

CREATE PROCEDURE testProcedure AS BEGIN INSERT
INTO Student VALUES (5, Godel') END
EXEC testProcedure
9
Procedure I/O example

• A procedure to take a beer and price and add it
  to Joe's menu Sells(bar, beer, price)

CREATE PROCEDURE izzyMenu( b IN
Sells.beerTYPE, p IN Sells.priceTYPE)
AS BEGIN INSERT INTO Sells VALUES(Izzys', b,
p) END /
10
Function example

• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /maxval.sql

CREATE OR REPLACE FUNCTION maxval(a IN int, b IN
int) RETURN int AS BEGIN IF a gt b THEN RETURN
a ELSE RETURN b END IF END maxval
INSERT INTO R VALUES(abc, maxval(5,10))
11
Word count program
http//pages.stern.nyu.edu/mjohnson/dbms/plsql/wo
rdcount.sql
CREATE OR REPLACE FUNCTION wordcount (str IN
VARCHAR2) RETURN PLS_INTEGER AS / words
PLS_INTEGER 0 Commented out for
intentional error / len PLS_INTEGER
NVL(LENGTH(str),0) inside_a_word
BOOLEAN BEGIN FOR i IN 1..len 1 LOOP
IF ASCII(SUBSTR(str, i, 1)) lt 33 OR i gt len
THEN IF inside_a_word THEN
words words 1
inside_a_word FALSE END IF
ELSE inside_a_word TRUE END
IF END LOOP RETURN words END
12
Getting errors

• Simple says
• To get actual errors, say SHOW ERR(ORS)
• Can also get errors per object
• Warning must get object type right!
• Can also look at user_errors tbl directly

Warning Function created with compilation errors.
SQLgt show errors function wordcount
13
Stored ftns procs persist

• Once a function or procedure is created, it
  persists until its dropped
• Stored procs are stored in the DB itself
• In user_procedures in Oracle
• Also, can describe ftns and procs

CREATE OR REPLACE FUNCTION
SELECT object_name from user_procedures
SQLgt describe wordcount
14
Calling functions and procedures

• Procedures can simple executed, ftns cant
• How to just call a ftn?
• Can use dbms_output, as seen
• Can also select the ftn value from dual

SQLgt select(wordcount(hi there) from dual
15
Programs and rights

• By default, only the creator of a program may run
  it (apart from the admin)
• If others should run, must GRANT them permission
• Permissions can be revoked
• Can also grant to particular roles or everyone
• Wider/narrower grant ops are independent

SQLgt GRANT EXECUTE ON wordcount TO george
SQLgt REVOKE EXECUTE FROM wordcount TO george
SQLgt GRANT EXECUTE ON wordcount TO everyone
16
Branching

• IFTHEN statements use THEN
• Must end with END IF
• Use ELSIF in place of ELSE IF
• Example
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /maxval.sql

IF ltconditiongt THEN ltstatement(s)gt ELSIF ltstatem
ent(s)gt END IF
17
More ifs
IF ltconditiongt ELSE END IF
IF ltexpressiongt ELSEIF ltexpressiongt ELSE END IF
18
Multiple elsifs

• An if statement can have multiple elseifs

IF salary gt 10000 AND salary lt 20000 THEN
give_bonus(employee_id, 1500) ELSIF salary gt
20000 AND salary lt 40000 THEN give_bonus(employee
_id, 1000) ELSIF salary gt 40000 THEN
give_bonus(employee_id, 400) END IF
19
Loop example
DECLARE     i NUMBER 1 BEGIN     LOOP
        INSERT INTO T1 VALUES(i,i)        
i i1         EXIT WHEN igt100    
END LOOP END /
20
More loops
LOOP executable_statements END LOOP

• Infinite loop
• while loop

WHILE condition LOOP executable_statements END
LOOP
21
For loops

• Integer for loop
• Cursor for loop

FOR for_index IN low_value .. high_value LOOP
executable_statements END LOOP
FOR record_index IN my_cursor LOOP
executable_statements END LOOP
22
For loop example

• Example
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/fo
  r.sql

FOR my-rec IN my-cursor LOOP END LOOP
23
PL/SQL v. SQL

• There are some things SQL cant do (e.g.,
  factorial), but some problems can be solved in
  both

DECLARE CURSOR checked_out_cur IS
SELECT pet_id, name, checkout_date FROM
occupancy WHERE checkout_date IS NOT
NULL BEGIN FOR checked_out_rec IN
checked_out_cur LOOP INSERT INTO
occupancy_history (pet_id, name, checkout_date)
VALUES (checked_out_rec.pet_id,
checked_out_rec.name,
checked_out_rec.checkout_date) DELETE FROM
occupancy WHERE pet_id checked_out_rec.pet_id
END LOOP END
24
PL/SQL v. SQL

• The same thing can be done w/o a cursor

BEGIN INSERT INTO occupancy_history (pet_id,
NAME, checkout_date) SELECT pet_id, NAME,
checkout_date FROM occupancy WHERE
checkout_date IS NOT NULL DELETE FROM
occupancy WHERE checkout_date IS NOT NULL END
25
Dynamic PL/SQL

• Saw dynamic SQL in the cases of ProC and JDBC
• Ability to run ad-hoc (non-hard-coded) SQL in
  programs/scripts
• Can also do this in PL/SQL
• The string can be passed in, created from
  concatenation, etc.

EXECUTE IMMEDIATE ltstringgt
26
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 /
27
Dynamic PL/SQL for DDL

• Ordinarily cant do DDL in PL/SQL
• But you can in dynamic PL/SQL
• Heres an e.g.

CREATE OR REPLACE procedure dropproc(procname in
varchar2) as begin execute immediate 'drop
procedure ' procname end /
28
More on PL/SQL

• OReillys Oracle PL/SQL Programming
• http//www.unix.org.ua/orelly/oracle/prog2/
• This lecture somewhat follows 3rd edition of this
  book
• PL/SQL Reference Tutorial
• http//www.ilook.fsnet.co.uk/ora_sql/sqlmain2.htm
• Introduction to PL/SQL
• http//www.geocities.com/cliktoprogram/plsql/intro
  duction.html
• Oracle FAQ's Script and Code Exchange
• http//www.orafaq.com/scripts/

29
Live examples

• Factorial function
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/fa
  ct.sql
• Converting between bases
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/nu
  msys.sql
• Directory of examples
• http//pages.stern.nyu.edu/mjohnson/dbms/plsql/

30
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

31
Trigger events

• Trigger code may be triggered by many kinds of
  events
• Oracle start-up/shut-down
• Triggers may replace initialization scripts
• Data updates
• Delete maybe delete related rows
• Inserts
• Updates maybe make other rows consistent
• Delete maybe prevent
• DDL statements
• Log creation of all objects, e.g.

32
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 triggers effect) (deletion,
  update, insertion)
• When the event occurs, DBMS checks the
  constraint, and if it is satisfied, performs the
  action

33
DML trigger options

• The trigger may be
• Statement-level (e.g., a DELETE WHERE statement)
  or
• Row-level (e.g., for each row deleted)
• The trigger may run
• BEFORE
• AFTER or
• INSTEAD OF the statement (in Oracle, not in
  others)
• It may be triggered by
• INSERTs
• UPDATEs
• DELETEs

34
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
35
Trigger type examples
Skip to Extended auditing example

• First run copy_tables.sql
• statement_vs_row.sql
• INSERT INTO to_table SELECT FROM from_table
• before_vs_after.sql
• INSERT INTO to_table SELECT FROM from_table
• one_trigger_per_type.sql
• INSERT INTO to_table VALUES (1)
• UPDATE to_table SET col1 10
• DELETE to_table

36
DML Trigger e.g.
CREATE OR REPLACE TRIGGER validate_employee_change
s 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?

37
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

38
Triggers with WHEN

• Remember WHEN applies only to row-levels
• 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 BEFOR
E INSERT ON frame FOR EACH ROW WHEN (
TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )
...
39
Multiple DML actions

• DML actions may be ORed together

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) END

• To find actual action, check
• INSERTING
• DELETING
• UPDATING

40
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
41
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
42
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
43
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
44
DDL trigger e.g.

• Town crier examples triggered by creates
• uninformed_town_crier.sql
• informed_town_crier.sql

45
Available DDL events
Skip to the bulk loader

• CREATE, ALTER, DROP, GRANT, RENAME, REVOKE,
  TRUNCATE
• DDL any DDL event
• Q Does this work??

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
46
DB Event triggers

• Form similar to DDL triggers
• Triggering events STARTUP, SHUTDOWN,
  SERVERERROR, LOGON, LOGOFF

CREATE OR REPLACE TRIGGER trigger name BEFORE
AFTER database event ON DATABASE
SCHEMA DECLARE Variable declarations BEGIN ...
some code... END
47
DB event restrictions

• Have BEFORE and AFTER as above, but they dont
  always apply
• No BEFORE STARTUP/LOGON/SERVERERROR
• No AFTER SHUTDOWN/LOGOFF

48
DB Trigger e.g.

• Gather stat before shutdown
• Log error messages error_log.sql

CREATE OR REPLACE TRIGGER on_shutdown BEFORE
SHUTDOWN ON DATABASE BEGIN gather_system_stats
END
49
Trigger maintenance

• Enabling disabling
• ALTER TRIGGER emp_after_insert DISABLE
• ALTER TRIGGER emp_after_insert ENABLE
• Deleting
• DROP TRIGGER emp_after_insert
• Viewing
• select trigger_name from user_triggers
• select text from user_source where
  name'TOWN_CRIER'
• Check validity
• select object_name, status from user_objects
  where object_type'TRIGGER'

50
Triggers important points

• Can replace old row (result of event) with new
  row
• Action may be performed before or after event
• Can refer to old row and new row
• WHEN clauses tests whether to continue
• Action may be performed either
• For each row involved in event
• Once per event
• Oracle does triggers as PL/SQL programs
• A trigger runs in the same transaction as the
  event triggering it

51
Elements of Triggers

• Timing of action execution before, after or
  instead of triggering event
• The action can refer to both the old and new
  state of the database
• Update events may specify a particular column or
  set of columns
• A condition is specified with an optional WHEN
  clause
• The action can be performed either for
• once for every tuple or
• once for all the tuples that are changed by the
  database operation

52
Simple trigger example

• R(id, data, last-modified)
• data is a large string
• Last-modified is a newly added date field
• Goal whenever data is modified, update
  last-modified date
• Could modify all scripts/programs that touch this
  table
• Bad idea
• Better user a trigger

• CREATE TRIGGER UpdateDateTrigger
• BEFORE UPDATE OF data ON R
• REFERENCING
• NEW ROW AS NewTuple
• FOR EACH STATEMENT
• BEGIN
• NewTuple.last-modified sysdate
• END

53
Triggers Row-level example

• MovieExec(name, address, cert, netWorth)
• If someone decreases a movie executives net
  worth, I want the database to reset itself to the
  previous net worth.

CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING NEW ROW AS
NewTuple OLD ROW AS OldTuple FOR EACH ROW WHEN
(OldTuple.netWorthgtNewTuple.netWorth) UPDATE
MovieExec SET netWorth oldTuple.netWorth WHERE
cert newTuple.cert)
54
Triggers Table-level example

• MovieExec(name, address, cert, netWorth)
• If someone updates the net worth of one movie
  exec so that the average net worth of all movie
  execs becomes less than 50,000, I want the
  database to reset itself.

CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING OLD TABLE AS
OldStuff, NEW TABLE AS NewStuff FOR EACH
STATEMENT WHEN (50000 gt (SELECT AVG(netWorth)
FROM MovieExec)) BEGIN DELETE FROM
MovieExec WHERE (Name, address, cert, netWorth)
IN NewStuff INSERT INTO MovieExec (SELECT
FROM OldStuff) END
55
Mutating Table Errors

• Its generally impossible to a row-level trigger
  to modify or query the table that triggered it
• ? trigger 2 slides back is not allowed!
• Does not apply to statement-level triggers
• ? trigger 1 slide back is
• Can do the equiv by creating a complicated
  statement-level trigger
• Wont discuss

56
New topic the bulk loader

• To insert data, can insert rows one at a time
  with INSERT INTO lttablegt VALUES(ltgt)
• If data is in/can be computed from other tables,
  can use
• INSERT INTO lttablegt SELECT
• Often, have text file of data
• MySQLs bulk loader will parse file and insert
  all into the database

57
Using the bulk loader

• Use mysqlimport command
• Takes a text file as input
• Each line 1 row
• Tab-delimited

100 Max Sydow 101 Count Dracula
58
Running the bulk loader

• The bulk loader is a command-line program
  mysqlimport, separate from SQLPlus
• At cmd line, specify
• user/pass (pass is optional here)
• Host
• Database
• Input file / table name

sales mysqlimport -hmysql2 -umy-NetID -p --local
my-db imptest.txt
http//pages.stern.nyu.edu/mjohnson/dbms/proj4.ht
ml
59
IMPORT DATA command

• Can also load data while inside mysql
• Does not work without LOCAL
• Means reading from client (your directory), not
  server

mysqlgt LOAD DATA LOCAL INFILE 'imptest.txt' INTO
TABLE imptest
60
More info

• If you want, you can
• Use a different field separator
• Reorder the fields
• Import date values
• For more info, see the webpages
• http//dev.mysql.com/doc/mysql/en/mysqlimport.html
  
• http//dev.mysql.com/doc/mysql/en/load-data.html

61
New-old topic Transactions

• So far, have simply issued commands
• Ignored xacts
• Recall, though an xact is an operation/set of
  ops executed atomically
• In one instant
• ACID test
• Xacts are atomic
• Each xact (not each statement) must leave the DB
  consistent

62
Default xact behavior

• An xact begins upon login
• By default, xact lasts until logoff
• Except for DDL statements
• They automatically commit
• Examples with two views of emp

63
Direct xact instructions

• At any point, may explicitly COMMIT
• SQLgt COMMIT
• Saves all statements entered up to now
• Begins new xact
• Conversely, can ROLLBACK
• SQLgt ROLLBACK
• Cancels all statements entered since start of
  xact
• Example delete from emp or delete junk

64
Direct xact instructions

• Remember, DDL statements are auto-committed
• ? They cannot be rollbacked
• Examples
• Q Why doesnt rollback work?

drop table junk rollback
truncate table junk rollback
65
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
• Dont have Word-like multiple undo
• But do have savepoints

66
Savepoints

• Create a savepoint
• emp example

SAVEPOINT savept_name
--changes SAVEPOINT sp1 --changes SAVEPOINT
sp2 --changes SAVEPOINT sp3 --changes ROLLBACK
TO sp2 ROLLBACK TO sp1

• Can skip savepoints
• But can ROLLBACK only backwards
• Can ROLLBACK only to last COMMIT

67
AUTOCOMMIT

• Finally, can turn AUTOCOMMIT on
• SQLgt SET AUTOCOMMIT ON
• Then each statement is auto-committed as its own
  xact
• Not just DDL statements