Distributed Databases

1
PL/SQL
2

• Procedural Language for SQL (PL/SQL) is an
  extension of Oracle SQL

• The basic intent of PL/SQL is

• increase the expressiveness of SQL

• process query results in a tuple-oriented way

• develop modular database application programs

• reuse program code, and

• reduce the cost for maintaining and changing
  applications.

• The basic construct of PL/SQL is a block

• constants and variables can be declared, and
  variables can be used to store query results.
  Statements in a PL/SQL block include

• SQL statements

• Control structures (loops)

• Condition statements (if-then-else)

• Exception handling

• Calls of other PL/SQL blocks.

3

• Each block builds a (named) program unit, and
  blocks can be nested.

• The structure of a PL/SQL looks as follows

(brackets enclose optional parts)
ltBlock headergt declare ltConstantsgt ltVariablesgt
ltCursorsgt ltUser defined exceptionsgt begin ltPL/SQL
statementsgt exception ltException handlinggt end
4

• Consider the following simple code

Needed to display output
Our local variable of data type DATE
Get todays date and store it in our variable
Display the contents of the variable
Program Output
5

• Numeric operations in PL/SQL

6

• Bind Variables

• Host variables

• Declared in the SQL Host Environment
• Remain active for the length of the SQL Session
• Can be displayed with the SQL Print Command
• Can only be accessed in the program when prefaced
  with a colon ()

7

• Interactive Input

8

• Control Structures IF-THEN

9

• Control Structures IF-THEN-ELSE

10

• Control Structures IF-THEN-ELSIF

(Note Spelling)
11

• Control Structures Basic Loop

12

• Control Structures While

13

• Control Structures For

14

• SQL in PL

• We must be careful about single record/field
  queries and multiple return queries

15

• Single Return Queries

The data type applied to field studentname is
automatically applied
16

• Multiple Record Queries

17

• Remember our problem about calculating a student
  grade?

• Variable Declarations

/ This program calculates a grade / declare
cursor studentgrade is select lastname,
firstname, quiz1, quiz2, quiz3 from grades
studentlastname grades.lastnametype
studentfirstname grades.firstnametype q1
grades.quiz1type q2
grades.quiz2type q3
grades.quiz3type average number sgrade
grades.gradetype nblanks number
blanks char(5)
18

• Remember our problem about calculating a student
  grade?

begin dbms_output.put_line('Student Name
Grade') dbms_output.put_line('-----------------
- -----') open studentgrade loop
fetch studentgrade into studentlastname,
studentfirstname, q1, q2, q3 exit when
studentgradenotfound average (q1 q2
q3)/3 nblanks 20 - (length(trim(studentfi
rstname)) length(trim(studentlastn
ame))) if average gt 90 then sgrade
'A' elsif average gt 80 then sgrade
'B' elsif average gt 70 then sgrade
'C' elsif average gt 60 then sgrade
'D' else sgrade 'F' end if
dbms_output.put_line(trim(studentfirstname) '
' trim(studentlastname) lpad('
',nblanks,' ') sgrade) update grades
set grade sgrade end loop close
studentgrade end
19

• Remember our problem about calculating a student
  grade?

20

• Exceptions

• Act as error handling routines

21

• Types of Exceptions

Exception Name
Description
No_data_found
Single row select returned no data
Too_Many_rows
Single row select returned multiple rows
Zero_Divide
Attempt to divide by zero
Value_Error
Arithmetic, Conversion, Truncation error
Storage_Error
PL/SQL ran out of memory or memory corrupted
Login_Denied
Invalid Username or password
Program_Error
Run Time error
Access_Into_Null
Attempt to assign values to uninitialized object
Invalid_Cursor
Illegal cursor operation
Rowtype_Mismatch
Cursor variable involved in incompatible return
types
--- And Others ---
22

• Procedures

Remember our Grading program?

• We could have created it as stored Procedure

23

• To run the program

24

• Functions, which are called by procedures, can
  also be created and stored

25

• To call the function

26

• Triggers

• A stored block which is implicitly called when an
  event occurs

• A triggering event is based on a Data
  Manipulation Language statement such as

• INSERT

• UPDATE

• DELETE

• Execution of the trigger is known as firing the
  trigger

27

• Recall our problem about determining whether or
  not a student had enrolled for two classes at the
  same time

• Information about students in a class was found
  only in the table enrollment

• Information about when a class met was found only
  in the table class

• If the information were in one table we could
  apply a constraint which would not allow a
  student to enroll in both classes

28

• However, even if we had created this table, it
  still would not stop a student from enrolling in
  two classes that meet at the same time

• Enrollment in a class is done by entering a
  record in table enrollment (not table temp_table)

• One way to stop dual enrollment is to set a
  trigger which tries to insert the record (from
  enrollment) into table temp_table (which contains
  the constraint)

• If the record can be inserted into temp_table, it
  will then be inserted into table enrollment

• If the record can NOT be inserted into
  temp_table, it will NOT be inserted into table
  enrollment

29

• The trigger might appear as

• FOR EACH ROW is a row trigger which fires once
  for each row inserted

• NEW refers to the new record to be inserted

30

• Suppose we look up the Spring 2003 (semester
  102) schedule for Yao Ming (studentid
  21098765)

• There is one other class that meets at the same
  time

31

• If we now try and enroll Yao for the other class

32
(No Transcript)