Distributed Database Applications

1
Distributed Database Applications

• COSC 5050
• Week One

2
Outline

• Introduction
• Course overview
• Oracle client environment
• Data dictionary
• Language fundamentals
• Program control

3
Introduction

• Oracle database
• PL/SQL
• Accessing Oracle server
• Database objects
• DDL and DML statements

4
PL/SQL

• Many Oracle applications are built using
  client-server architecture
• The Oracle database resides on the server
• PL/SQL is like any other programming language
• PL/SQL is not a stand-alone programming language
• PL/SQL is a part of the Oracle RDBMS

5
PL/SQL

• Highly structured, readable, accessible language
• Standard and portable language
• Embedded language
• High-performance, highly integrated database
  language

6
Advantages of PL/SQL
7
SQL Example

• The semicolon terminates CREATE, INSERT, SELECT,
  and DROP statements
• CREATE TABLE STUDENT
• ( FIRST_NAME VARCHAR2(20),
• LAST_NAME VARCHAR2(20)
• )
• INSERT INTO STUDENT VALUES (JOHN, LUCKY)
• SELECT FIRST_NAME, LAST_NAME FROM STUDENT
• DROP TABLE STUDENT

8
PL/SQL Example

• Page 7, 8, 9, 39 examples
• When Oracle reads a PL/SQL block, a semicolon
  marks the end of the individual statement within
  the block
• It is not a block terminator!
• The / executes the PL/SQL block

9
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 have written (or co-written) '
• l_book_count ' books.')
• UPDATE books
• SET author REPLACE (author, 'Steven',
  'Stephen')
• WHERE author LIKE 'Feuerstein, Steven'
• END

10
Control and Conditional Logic
CREATE OR REPLACE PROCEDURE pay_out_balance(
account_id_in IN accounts.idTYPE) IS
l_balance_remaining NUMBER BEGIN LOOP
l_balance_remaining account_balance
(account_id_in) IF l_balance_remaining lt
1000 THEN EXIT ELSE
apply_balance ( accont_id_in,
l_balance_remaining) END IF END LOOP END
pay_out_balance
11
When Things Go Wrong
CREATE OR REPLACE PROCEDURE check_account(account_
id_in IN accounts.idTYPE) IS
l_balance_remaining NUMBER
l_balance_below_minimum EXCEPTION
l_account_name accounts.nameTYPE BEGIN
SELECT name INTO l_account_name FROM accounts
WHERE id account_id_in l_balance_remaining
account_balance (account_id_in)
DBMS_OUTPUT.put_line ( 'Balance for '
l_account_name ' ' l_balance_remaining)
IF l_balance_remaining lt 1000 THEN
RAISE l_balance_below_minimum END
IF EXCEPTION WHEN NO_DATA_FOUND THEN
log_error (...) WHEN l_balance_below_mini
mum THEN log_error (...)
RAISE END
12
Creating a Stored Program
CREATE OR REPLACE FUNCTION wordcount (str IN
VARCHAR2) RETURN PLS_INTEGER AS words
PLS_INTEGER 0 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
13
Executing, Showing, and Dropping a Stored Program
BEGIN DBMS_OUTPUT.PUT_LINE( 'There are '
wordcount(CHR(9)) ' words in a tab')
END SELECT FROM USER_OBJECTS DESC
wordcount SELECT TEXT FROM USER_SOURCE WHERE
NAME 'WORDCOUNT' DROP FUNCTION wordcount
14
Access Oracle

• Oracle server (service and host name)
• cronus, icarus.webster.edu
• Oracle client
• Oracle 11g client download
• SQLPlus
• SQL Developer
• Application Express (APEX) Workspace
• Your login and password

15
Oracle SQLPlus
16
Oracle SQL Developer
17
Oracle APEX
18
Data Dictionary

• USER_
• ALL_
• DBA_
• select view_name from all_views where view_name
  like 'USER'

19
Language Fundamentals

• PL/SQL block structure
• Modularization
• Scope
• Anonymous blocks
• Named blocks
• Procedures
• Functions
• Scope and visibility

20
PL/SQL Block Structure

• Header
• Declaration section
• Execution section
• Exception section

PROCEDURE get_happy (ename_in IN VARCHAR2) IS
hiredata DATE BEGIN hiredate SYSDATE 2
INSERT INTO employee (emp_name, hiredate)
VALUES (ename_in, hiredate) EXCEPTION WHEN
dup_val_in_index THEN DBMS_OUTPUT.PUT_LINE
(Cannot insert.) END
21
Anonymous Blocks

• No header
• Begin with either DECLARE or BEGIN
• Cannot be called

BEGIN DBMS_OUTPUT.PUT_LINE (SYSDATE) END DECL
ARE l_right_now DATE SYSDATE BEGIN
DBMS_OUTPUT.PUT_LINE (l_right_now) END
22
Named Blocks

• Procedures
• Functions

procedure Add_employee (ssn in varchar2 ,
fname in varchar2 , lname in varchar2 ,
dept_num in number , code in number ,
sup_ssn in varchar2) is begin insert into
employee values (ssn, fname, lname, dept_num,
code, sup_ssn) end -- Add_employee
23
Named Blocks
function get_department(ssn_in in
employee.ssntype) return department.dept_nam
etype is l_dept_name department.dept_nametyp
e begin select dept_name into l_dept_name
from department inner join employee
on employee.dept_num department.dept_num
where ssn ssn_in dbms_output.put_line('de
partment name ' l_dept_name) return
l_dept_name exception when no_data_found
then dbms_output.put_line(
'no such employee or not in any department!')
return null end
24
Named Blocks in DB2
CREATE PROCEDURE sum( IN p_a INTEGER,
IN p_b INTEGER, OUT p_s INTEGER)
LANGUAGE SQL BEGIN SET p_s p_a p_b
END CALL sum(100,200,?)
25
Language Fundamentals

• PL/SQL character set
• Case-insensitive language
• Identifiers
• Up to 30 characters in length
• Must start with a letter
• Can include , _, and
• Cannot contain spaces
• PL/SQL keywords
• Comments
• Single-line comments using --
• Multi-line comments using / /

26
PL/SQL Character Set
Type Characters
Letters A-Z, a-z
Digits 0-9
Symbols ! _at_ ( ) _ - " ' lt gt , . ? /
Whitespace Tab, space, newline, carriage return
27
Program Control

• IF statements
• CASE
• LOOP
• WHILE loop
• Cursor loop

28
IF Statement

• IF salary gt 40000
• THEN
• give_bonus (employee_id, 500)
• END IF
• IF salary lt 40000
• THEN
• give_bonus (employee_id, 0)
• ELSE
• give_bonus (employee_id, 500)
• END IF
• declare
• salary number 40000
• begin
• IF salary gt 40000
• THEN
• dbms_output.put_line('Salary is greater than
  40000')
• ELSE

29
IF Statement

• declare
• salary number salary
• begin
• IF salary BETWEEN 10000 AND 20000
• THEN
• dbms_output.put_line('Give bonus 1500.')
• ELSIF salary BETWEEN 20000 AND 40000
• THEN
• dbms_output.put_line('Give bonus 1000.')
• ELSIF salary gt 40000
• THEN
• dbms_output.put_line('Give bonus 500.')
• ELSE
• dbms_output.put_line('Give bonus 0.')
• END IF
• end
• /

30
CASE Statement

• Simple CASE statement

declare salary_level number salary begin
case salary_level when 1 then
dbms_output.put_line('give bonus 1500.') when
2 then dbms_output.put_line('give bonus
1000.') when 3 then dbms_output.put_line('g
ive bonus 500.') else dbms_output.put_line(
'give bonus 0.') end case end /
31
CASE Statement

• Searched CASE statement

declare salary number salary begin case
when salary between 10000 and 20000 then
dbms_output.put_line('give bonus 1500.') when
salary between 20000 and 40000 then
dbms_output.put_line('give bonus 1000.') when
salary gt 40000 then dbms_output.put_line('give
bonus 500.') else dbms_output.put_line('gi
ve bonus 0.') end case end /
32
CASE Statement

• CASE expression

declare salary number salary
bonus_amount number begin bonus_amount
case when salary BETWEEN 10000 AND 20000 THEN
1500 when salary BETWEEN 20000 AND 40000 THEN
1000 when salary gt 40000 THEN 500 else 0
end dbms_output.put_line(
'Give bonus ' bonus_amount '.') end /
33
Loop Statement

• Simple loop
• FOR loop
• WHILE loop
• Cursor FOR loop

34
Simple Loop

• PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
• IS
• ranking_level NUMBER (3) 1
• BEGIN
• LOOP
• EXIT WHEN ranking_level gt max_rank_in
• set_rank (ranking_level)
• ranking_level ranking_level 1
• END LOOP
• END set_all_ranks
• Use EXIT or EXIT WHEN to exit loop

35
For Loop

• PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
• IS
• ranking_level NUMBER (3) 1
• BEGIN
• FOR ranking_level IN 1 .. max_rank_in
• LOOP
• set_rank (ranking_level)
• END LOOP
• END set_all_ranks
• Reverse loop
• FOR counter IN REVERSE 1 .. max
• LOOP
• END LOOP

36
WHILE Loop

• PROCEDURE set_all_ranks (max_rank_in IN INTEGER)
• IS
• ranking_level NUMBER (3) 1
• BEGIN
• WHILE ranking_level lt max_rank_in
• LOOP
• set_rank (ranking_level)
• ranking_level ranking_level 1
• END LOOP
• END set_all_ranks

37
Cursor FOR Loop

• Cursor with simple loop
• DECLARE
• CURSOR name_cur IS
• SELECT lname, fname
• FROM employee WHERE ssn like '8'
• name_rec name_curROWTYPE
• BEGIN
• OPEN name_cur
• LOOP
• FETCH name_cur INTO name_rec
• EXIT WHEN name_curNOTFOUND
• DBMS_OUTPUT.PUT_LINE(
• name_rec.fname ' ' name_rec.lname)
• END LOOP
• CLOSE name_cur
• END
• /

38
Cursor FOR Loop

• Cursor with simple loop
• DECLARE
• CURSOR occupancy_cur IS
• SELECT pet_id, room_number
• FROM occupancy
• WHERE occupied_dt TRUNC(SYSDATE)
• occupancy_rec occupancy_curROWTYPE
• BEGIN
• OPEN occupancy_cur
• LOOP
• FETCH occupancy_cur INTO occupancy_rec
• EXIT WHEN occupancy_curNOTFOUND
• update_bill
• (occupancy_rec.pet_id, occupancy_rec.room_nu
  mber)
• END LOOP
• CLOSE occupancy_cur
• END

39
Cursor FOR Loop

• Cursor For loop
• DECLARE
• CURSOR occupancy_cur IS
• SELECT pet_id, room_number
• FROM occupancy
• WHERE occupied_dt TRUNC(SYSDATE)
• BEGIN
• FOR occupancy_rec IN occupancy_cur
• LOOP
• update_bill
• (occupancy_rec.pet_id, occupancy_rec.room_nu
  mber)
• END LOOP
• END

40
Cursor FOR Loop

• Cursor FOR loop
• DECLARE
• CURSOR name_cur IS
• SELECT lname, fname
• FROM employee WHERE ssn like '8'
• BEGIN
• FOR name_rec IN name_cur
• LOOP
• DBMS_OUTPUT.PUT_LINE(
• name_rec.fname ' ' name_rec.lname)
• END LOOP
• END
• /

41
Homework

• Given the table layout, write the SQL to create
  the table
• Create a script file to load database table
• Create an anonymous PL/SQL block to retrieve data
  from above database table
• Create a simple procedure in your DB2 Company
  schema to retrieve employee data
• Project proposal