Moving a Java Program into a Database

1
Moving a Java Program into a Database
2
Motivation

• We would like to be able to run a Java program
  from within Oracle. This will allow us to
• call a Java function inside a query
• call a Java function inside a trigger (triggers
  will be explained later on)

3
Basic Procedure

• Create a Java program. Make the function that we
  want to use static.
• Load the Java program into the database.
• Create a PL/SQL procedure that will wrap the Java
  function
• Call the PL/SQL procedure.
• We learn each step in detail

4
Hello World - Java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED
"Hello" ASpublic class Hello public
static void sayHello(String name)     
 System.out.println("Hello World, " name)
public static String returnHello(String
name) return "Hello World, " name
/
5
Hello World PL/SQL (1)
CREATE OR REPLACE PROCEDURE say_hello(name
VARCHAR2) as LANGUAGE JAVA NAME
'Hello.sayHello(java.lang.String)' /
Note Errors in creating a procedure can be seen
with the command show errors
6
Calling say_hello
SET SERVEROUTPUT ON SIZE 1000000 CALL
DBMS_JAVA.SET_OUTPUT (1000000) CALL
say_hello(Sara)
We will see Hello World, Sara on the screen.
7
Hello World PL/SQL
CREATE OR REPLACE FUNCTION return_hello(name
VARCHAR2) return VARCHAR2 as LANGUAGE JAVA NAME
'Hello.returnHello(java.lang.String) return
java.lang.String' /
8
Calling return_hello
SELECT return_hello(Sara) FROM DUAL
We will see
RETURNHELLO('SARA') ------------------------------
---- Hello World, Sara
9
Notes

• Java programs can also be loaded using the
  utility loadjava. Details will be given when
  necessary.
• It may be necessary sometimes to recompile a
  procedure or a java source. This can be done by
• alter procedure say_hello compile
• alter java source Hello compile
• Note the need to use quotes around Java source
  names, since Java is case sensitive.

10
Moving Program Logic into the Database Triggers
11
What is a Trigger?

• A trigger is a procedure that is automatically
  called when certain events occur in the database.
• Triggers can be made to run when rows are
  inserted, deleted or updated.
• Triggers can be run before or after the action.
• Triggers can be run once per statement or once
  per row affected.

12
General Form of a Trigger
CREATE or REPLACE TRIGGER trig_name BEFORE
AFTER INSTEAD OF DELETE INSERT UPDATE
of column , column ... or DELETE
INSERT UPDATE of column , column ...
... on table_name view_name FOR EACH
ROW STATEMENT WHEN (condition) PL/SQL
block
13
Backing Up Data
CREATE TRIGGER backup_sailors_trig AFTER UPDATE
of Rating on Sailors FOR EACH ROW WHEN
(old.rating lt new.rating) BEGIN INSERT INTO
Sailors_Backup VALUES (old.sid, old.sname,
oldage, oldrating) END /
Why AFTER?
14
Ensuring Upper Case
CREATE TRIGGER upper_case_program_type_trig BEFOR
E INSERT or UPDATE on Program FOR EACH
ROW BEGIN new.type UPPER(new.type) END /
Why BEFORE?
15
Calling a Procedure
CREATE TRIGGER notify_ta_trig AFTER INSERT or
UPDATE or DELETE on Db_Grades FOR EACH
STATEMENT WHEN(User ltgt db) BEGIN call
sendEmail(User trying to access table
Db_Grades) END /
16
Views and Triggers
17
Changing a Table through a View (1)

• If a view is based on a single table you can
  insert, update and delete rows from the table
  through the view under the following conditions
• You cant insert if the underlying table has non
  null columns not appearing in the view
• You cant insert or update if any of the view
  columns referenced in the command contains
  functions or calculations

18
Changing a Table through a View (2)

• You cant insert, update or delete if the view
  contains group by or distinct.

19
Inserting Allowed
CREATE VIEW OldSailors as SELECT FROM Sailors
WHERE age gt 50
INSERT INTO OldSailors(sid,sname,age,rating)
VALUES(12,Joe,51,10)
INSERT INTO OldSailors(sid,sname,age,rating)
VALUES(12,Mary,49,10)
20
Inserting Not Allowed
CREATE VIEW SailorsInfo as SELECT sname,
rating FROM Sailors WHERE agegt50
INSERT INTO SailorsInfo VALUES(Joe,10)
Illegal!
21
Updating Allowed
CREATE VIEW SailorsInfo as SELECT sname,
rating FROM Sailors WHERE agegt50
UPDATE SailorsInfo SET rating 6 WHERE sname
Joe
UPDATE Sailors SET rating 6 WHERE sname
Joe and agegt50
22
Updating Not Allowed
CREATE VIEW SailorsInfo2 as SELECT sname, rating
age as ra FROM Sailors WHERE agegt50
UPDATE SailorsInfo2 SET ra 7 WHERE sname
Joe
Illegal!
23
Deleting Allowed
CREATE VIEW SailorsInfo2 as SELECT sname, rating
age as ra FROM Sailors WHERE agegt50
DELETE FROM SailorsInfo2 WHERE sname Joe
and ra 56
DELETE FROM Sailors WHERE sname Joe and
(rating age) gt50 and age gt 50
24
Some More ExamplesWhat will these commands do?
UPDATE OldSailors SET rating 10
UPDATE OldSailors SET age age 1 WHERE age lt
50
DELETE FROM OldSailors
25
Using a Views with Triggers

• Consider the relation
• All_Favorites(url, login)
• This table stores the favorite web sites of CS
  students. Two views have been created over this
  table
• User_Favorites The favorites of current user
• Anon_Favorites All the favorites, but without
  stud_id, to preserve anonymity.

26
The Views
CREATE View User_Favorites as SELECT url FROM
All_Favorites WHERE login user
CREATE View Anon_Favorites as SELECT url FROM
All_Favorites
27
Inserting New URLs

• The table All_Favorites can not be inserted into
  directly by a student because the privileges are
  lacking
• The views cant be inserted through, since login
  is a non null column

CREATE TRIGGER insert_url INSTEAD of INSERT on
User_Favorites for each row begin insert into
All_Favorites(url, login) values(User,new.url)
end
28
Using Triggers to Manipulate Complex Views

• Oracle can sometimes insert through a view based
  on several tables.
• Oracle cant insert into multiple tables through
  a view.

CREATE VIEW SailorsReserves as SELECT S.sid,
sname, rating, age, bid, date FROM Sailors S,
Reserves R WHERE S.sid R.sid
29
Inserting into a Complex ViewWont Work
INSERT INTO SailorsReserves(sid,sname,rating,age,b
id,date) VALUES (1,Joe, 10, 50, 103,
10-10-01)

• This wont work, since we are attempting to
  insert into 2 tables.
• In addition, we are not specifying a value for
  the sid field of Reserves, which is part of the
  key!

30
Using a Trigger to Insert
CREATE TRIGGER insert_sailors_reserves_trig INSTEA
D of INSERT on SailorsReserves for each
row begin insert into Sailors(sid, sname,
rating, age) values(new.sid, new.sname,
new.rating, new.age) insert into
Reserves(sid, bid, date) values(new.sid,
new.bid, new.date) end
31
Materialized Views
32
What and Why?

• What A materialized view is a view that actually
  exists as a table
• Why This can be more efficient than re-computing
  the views query each time it is accessed
• Problem How is the materialized view kept up to
  date when the underlying tables are changed?
• Note We will just see the ideas here and not the
  syntax

33
Simple vs. Complex

• A Simple Materialized View
• Selects rows from only 1 table
• Does not perform set operations, joins or group
  bys
• Otherwise, it is a Complex Materialized View.

34
Options for Materialized Views

• Refresh mode, one of
• Fast (only possible for Simple Materialized
  Views) add minimal changes
• Complete Recompute the query
• Force Fast, if possible otherwise complete
• When is refresh performed?
• On demand When refresh is specifically asked for
• On commit When underlying table has changed

35
Fast Refresh

• Consider a materialized view defined by the query

SELECT sname, age FROM Sailors WHERE rating lt
10 and age gt 50
36
Fast Refresh

• How would the view be updated when the following
  commands are performed?

INSERT INTO Sailors(sid,sname,rating,age) VALUES
(12,Joe,8,52)
DELETE FROM Sailors WHERE age lt 54
UPDATE Sailors SET age age - 1 WHERE rating gt
10
37
More Options

• Query Rewrite Can a materialized view be used
  instead of a table in a query?
• Suppose we had the view defined before, and we
  wanted to compute the query

SELECT age FROM Sailors WHERE age gt 51 and age
lt 85