Database Management Systems

1
Database Management Systems

• Server Side Programming

2
Agenda

• Motivation of server side programming
• Oracle Server Programming
• MS SQL Server Programming

3
Problems

• Traditional Environment
• DBMS stores only data
• Business rules are implemented by the client
  applications
• Consequences
• Increased network traffic
• Several consistency checks are implemented
  independently of the DBMS
• If a business rule has been changed, it is
  necessary to modify each of the clients

4
Solution

• Programming on DBMS level
• It allows increasing the functionality of the
  DBMS
• DBMS can become an active component. It allows
  not only storing data in DBMS but also providing
  data related and data dependent functionality
• Consequences
• The business rules can be part of the databases ?
  Databases can save their data.
• Client applications just call the implemented
  program modules
• Decreased network traffic

5
Advantages of the server side programming (1)

• Memory usage
• Multiple users can use same code ? DBMS loads it
  only once
• Productivity
• Every client can use same methods? code re-usage,
  everybody uses common modules, they are
  implemented only once
• Easier to follow the business logic changes
• Data integrity
• Business rules are implemented on DBMS level ?
  DBMS can perform all consistency check

6
Advantages of the server side programming (2)

• Security
• It is not necessary to give direct data access
  rights to the users
• It is enough to have execute access rights on the
  necessary program modules
• Performance
• Lower network traffic
• Program modules are stored in compiled and
  optimized state on the server
• DBMS usually has stored procedure cache

7
Disadvantages of server side programming

• The load of the DBMS can be increased
• Platform dependent
• Every DBMS has own programming language
• Programming possibility and capability depend on
  the DBMS

8
Programming Oracle Server
9
Stored Procedure

• PL/SQL
• Programming language of the Oracle Server
• SQL flow control elements (Cycles, conditional
  branches, exceptions )
• Stored procedure
• Collection of PL/SQL statements which implements
  a functionality
• It dos not have return value
• It can have multiple output parameters
• Stored Function
• Similar to stored procedure
• It has return value
• It can be used where the return value can be used

10
Simple Example
CREATE OR REPLACE PROCEDURE Hire_Emp(name
VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate
DATE, sal NUMBER, comm NUMBER, deptno
NUMBER) AS BEGIN INSERT INTO emp
VALUES(emp_sequence.nextval, name, job, mgr,
hiredate, sal, comm,
deptno) END

• EXECUTE Hire_Emp('Queen', 'SALESMAN', 7698,
  '11-Nov-2000',2005,NULL,30)

11
Stored procedure categories

• Standalone
• Part of a package
• External
• Usually written in C/C
• Oracle executes it in separate address space ?
  Separate process
• Used for implementing extra functionalities which
  are not typical DBMS functionalities
• Send Email, SMS
• Integration to an external system

12
Creating a stored procedure

• Oracle Server compiles the code
• It stores the following data in the data
  dictionary
• Name, source code
• Explain plain
• Pseudo code (P Code)
• Error messages which were generated during the
  compilation
• Status of the stored procedure

13
Status changes of a program module

• Become invalid if
• Any referred object has been changed
• Any access right has been changed that is
  necessary for executing the program module
• If it has become invalid the Oracle Server will
  recompile it during the next execution

14
Trigger

• Trigger is an event handler stored procedure
• It is executed automatically if an appropriate
  event has been generated

15
Why do we use triggers?

• Calculating derived attributes
• Implementing business rules
• Logging
• Auditing
• Collecting statistics

16
Type of triggers

• DML Statement triggers
• It is called automatically if a DML statement has
  been executed
• Insert, Update, Delete on a table
• DDL triggers
• It is called automatically if a DDL statement has
  been executed
• Create/Alter/Drop Table/View/Procedure
• System event triggers
• It is called automatically if a system related
  event has been happened
• User Log on/ Log off
• Server Instance Start up/ Shut down
• Server Error

17
Triggers and transactions

• DML trigger
• Part of the transaction of the appropriate DML
  statement
• DDL trigger
• A new transaction is created automatically, which
  is impersonated by the active user and commit it
  automatically
• System event triggers
• A new transaction is created automatically, which
  is impersonated by the SYS user and commit it
  automatically

18
Sample code

• AFTER UPDATE OF parts_on_hand ON inventory

WHEN (new.parts_on_hand lt new.reorder_point)
FOR EACH ROW DECLARE NUMBER X BEGIN SELECT
COUNT() INTO X FROM pending_orders WHERE
part_nonew.part_no IF x0 THEN INSERT INTO
pending_orders VALUES(new.part_no,
new.reorder_quantity, sysdate) END IF END
19
DML triggers

• Row level and statement level triggers
• BEFORE and AFTER trigger
• INSTEAD OF trigger

20
Row and Statement triggers

• Row
• Executed for each row which has been modified by
  the DML statement
• The modified data is available in the trigger
  code
• Statement
• Executed only once when the statement has been
  executed
• The modified data is invisible

21
BEFORE and AFTER trigger

• BEFORE
• The trigger is executed before the data
  modification
• AFTER
• The trigger is executed after the data
  modification

22
Execution order of the triggers

• Before statement triggers
• For each row
• BEFORE row level triggers
• Row modification, locking, consistency check
• AFTER row level triggers
• AFTER statement triggers

23
INSTEAD OF trigger

• It is used for views when somebody tries to
  modify a view
• The instead of trigger is executed as it names
  suggests instead of the data modification SQL
  statement

24
SQL Server 2000
25
Stored procedure

• Written in Transact SQL
• Similar role to Oracle stored procedure
• It can returns set of records ? Can be used like
  a table in a SQL statement

26
Creating Stored Procedure

• Using CREATE PROCEDURE statement in the current
  database

Use libraryGO CREATE PROC dbo.overdue_books AS
SELECT FROM dbo.loan WHERE due_date lt
GETDATE() GO
27
Modifying a stored procedure
Use libraryGO ALTER PROC overdue_books AS SELECT
CONVERT(char(8), due_date, 1) date_due, isbn,
copy_no, SUBSTRING(title, 1, 30)title,
member_no, lastname FROM OverdueView ORDER BY
due_date GO
28
Execution of a stored procedure

• Simple execution
• Part of a SQL statement

EXEC overdue_books
INSERT INTO customers EXEC employee_customer
29
Using Parameters
CREATE PROC dbo.find_isbn _at_title longstring,
_at_translation char(8) 'English' AS IF _at_title
is null BEGIN PRINT "Please provide a
title (or partial title) and the translation"
PRINT "find_isbn 'Oliver', 'Japanese'" ...
30
Triggers

• Event handler stored procedure
• Only DML triggers are available
• After statement trigger
• Instead of triggers
• View
• Table ? Possibility of implementing before trigger

31
Creating trigger
CREATE TRIGGER member_delete ON member FOR
DELETE AS IF EXISTS (SELECT d.member_no FROM loan
l INNER JOIN deleted d ON l.member_no
d.member_no ) BEGIN RAISERROR ('Member has Books
Checked Out Cannot Delete', 10, 1) ROLLBACK
TRANSACTION END
32
Modifying a trigger
ALTER TRIGGER loan_insert ON loan FOR INSERT
AS UPDATE copy SET on_loan 'y' FROM copy
INNER JOIN inserted ON copy.isbn
inserted.isbn AND copy.copy_no
inserted.copy_no
33
INSERT trigger execution
INSERT statement to a table with an INSERT
Trigger Defined
INSERT loan VALUES (603, 4, 11, 123, GETDATE(),
(GETDATE() 30))
UPDATE c SET on_loan 'Y' FROM copy c INNER JOIN
inserted I ON c.isbn isbn and c.copy_no
i.copy_no
34
DELETE trigger execution
DELETE Statement to a table with a DELETE Trigger
Defined
DELETE loan WHERE isbn 4 AND copy_no 1
UPDATE c SET on_loan 'N' FROM copy c INNER
JOIN deleted d ON c.isbn d.isbn AND c.copy_no
d.copy.no
35
Az UPDATE trigger execution
UPDATE Statement to a table with an UPDATE
Trigger Defined
UPDATE member SET member_no 10021 WHERE
member_no 1234
UPDATE Statement logged as INSERT and DELETE
Statements