Introduction to PL/SQL

Kristian Torp
Department of Computer Science
Aalborg University
www.cs.aau.dk/torp

torp@cs.aau.dk

December 2, 2011

daisy.aau.dk
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

1 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

2 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

3 / 56

Learning Outcomes

Learning Outcomes
Understand how code can be executed within a DBMS
Be able to design stored procedures in general
Be able to construct and execute stored procedures on Oracle
Knowledge about the pros and cons of stored procedures

Note That
Concepts are fairly DBMS independent
All code examples are Oracle specific

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

4 / 56

Prerequisites
SQL
Knowledge about the SQL select statement
Knowledge about SQL modification statements, e.g., insert and delete
Knowledge about transaction management, e.g., commit and rollback
Knowledge about tables, views, and integrity constraints

Procedural Programming Language

Knowledge about another programming language of the C family
Knowledge about data types, e.g., int, long, string, and Boolean
Knowledge of control structures, e.g., if, while, for, and case
Knowledge of functions, procedures, parameters, and return values

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

5 / 56

Motivation
Purpose
Move processing into the DBMS from client programs (database
applications)

Advantages
Code accessible to all applications
I

Access from different programming languages

Very efficient for data intensive processing

I

Process large data set, small result returned

Enhance the security of database applications

I

Avoid SQL injection attacks

http://en.wikipedia.org/wiki/SQL_injection

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

6 / 56

Motivation
Purpose
Move processing into the DBMS from client programs (database
applications)

Advantages
Code accessible to all applications
I

Access from different programming languages

Very efficient for data intensive processing

I

Process large data set, small result returned

Enhance the security of database applications

I

Avoid SQL injection attacks

http://en.wikipedia.org/wiki/SQL_injection

Missing Standard
Unfortunately, the major DBMS vendors each have their own SQL dialect
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

6 / 56

Overview
Functionality
SQL extended with control structures
I

Control structures like if and loop statements

Used for
I
I
I
I

Stored procedures (and functions)

Package (Oracle specific)
Triggers
Types a.k.a. classes (Oracle specific)

In very widely used in the industry

I see http://www.tiobe.com/index.php/content/paperinfo/
tpci/index.html
In the SQL standard called SQL/PSM
I

PSM = Persistent Storage Model

Focus
The focus is here on stored procedures and packages!
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

7 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

8 / 56

Motivation for Stored Procedures

The Big Four Benefits
Abstraction
I

Increases readability

Implementation hiding
I

Can change internals without effecting clients

Modular programs
I

More manageable and easier to understand

Library
I

Reuse, reuse, and reuse!

Note
This is not different from any other procedural programming language!

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

9 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

10 / 56

A Procedure: Hello, World!

Example (The Start Program)
c r e a t e o r r e p l a c e procedure h e l l o w o r l d i s
begin
dbms output . p u t l i n e ( H e l l o , World ! ) ;
end ;

Note
It is a procedure, i.e., not a function
I

Both a procedure and a function is called a stored procedure

It is a begin and end language, not curly brackets: { and }

It uses a built-in library dbms output.put line
I
I

The package dbms output has the procedure put line

Uses the dot notation for invoking functions myPackage.myProcedure

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

11 / 56

A Function: Calculating Your BMI

Example (A BMI Function)
c r e a t e o r r e p l a c e f u n c t i o n bmi ( h e i g h t i n t , w e i g h t f l o a t )
return f l o a t is
begin
i f h e i g h t <= 0 . 3 o r h e i g h t > 3 . 0 then
dbms output . p u t l i n e ( h e i g h t must be i n [ 0 . 3 , 3 . 0 ] meters ) ;
end i f ;
i f w e i g h t <= 0 then
dbms output . p u t l i n e ( w e i g h t must be p o s i t i v e ) ;
e l s i f w e i g h t > 500 then
dbms output . p u t l i n e ( No human s w e i g h t i s 500 kg ) ;
end i f ;
r e t u r n weight / height 2;
end ;

Note
It takes two parameters height and weight
It is a function, i.e., has a return statement
It is strongly typed language, i.e., parameters and the return value
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

12 / 56

Executing Stored Procedures

Example (Execute on Oracle server)
t o enable o u t p u t from t h e s e r v e r
SQL> s e t s e r v e r o u t p u t on

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

13 / 56

Executing Stored Procedures

Example (Execute on Oracle server)
t o enable o u t p u t from t h e s e r v e r
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute h e l l o w o r l d ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

13 / 56

Executing Stored Procedures

Example (Execute on Oracle server)
t o enable o u t p u t from t h e s e r v e r
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute h e l l o w o r l d ;
execute t h e f u n c t i o n
SQL>exec bmi ( 1 . 8 7 , 9 0 ) ;
r e s u l t s i n an e r r o r , v a l u e r e t u r n e d by f u n c t i o n must be used !

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

13 / 56

Executing Stored Procedures

Example (Execute on Oracle server)
t o enable o u t p u t from t h e s e r v e r
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute h e l l o w o r l d ;
execute t h e f u n c t i o n
SQL>exec bmi ( 1 . 8 7 , 9 0 ) ;
r e s u l t s i n an e r r o r , v a l u e r e t u r n e d by f u n c t i o n must be used !
Wrap t h e f u n c t i o n c a l l
SQL>exec dbms output . p u t l i n e ( bmi ( 1 . 8 7 , 9 0 ) ) ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

13 / 56

Executing Stored Procedures

Example (Execute on Oracle server)
t o enable o u t p u t from t h e s e r v e r
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute h e l l o w o r l d ;
execute t h e f u n c t i o n
SQL>exec bmi ( 1 . 8 7 , 9 0 ) ;
r e s u l t s i n an e r r o r , v a l u e r e t u r n e d by f u n c t i o n must be used !
Wrap t h e f u n c t i o n c a l l
SQL>exec dbms output . p u t l i n e ( bmi ( 1 . 8 7 , 9 0 ) ) ;

Note
Output from server is not enabled by default in a session!
Return value of a function cannot be ignored!

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

13 / 56

Using SQL in Stored Procedures

Example (Use the Data Stored)
c r e a t e o r r e p l a c e f u n c t i o n g e t s t a t u s ( s t u d e n t i d number )
r e t u r n varchar2 i s
v s t a t u s varchar2 ( 5 0 ) ;
begin
s e l e c t s t a . dsc
into
v status
from
s t u d e n t stu , s t a t u s s t a
where s t u . s t a t i d = s t a . s t a t i d
and
stu . sid = student id ;
return v status ;
end ;

Note
The declaration of the variable v status
The usage of the into keyword in the select statement
The usage of the parameter student id in the select statement
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

14 / 56

Calling Other Procedures

Example (Callee)
c r e a t e o r r e p l a c e procedure p ( s t v a r c h a r 2 ) as
begin
dbms output . p u t l i n e ( s t ) ;
end ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

15 / 56

Calling Other Procedures

Example (Callee)
c r e a t e o r r e p l a c e procedure p ( s t v a r c h a r 2 ) as
begin
dbms output . p u t l i n e ( s t ) ;
end ;

Example (Caller)
c r e a t e o r r e p l a c e procedure c a l l p i s
begin
p ( H e l l o ) ; p ( World ! ) ;
end ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

15 / 56

Calling Other Procedures

Example (Callee)
c r e a t e o r r e p l a c e procedure p ( s t v a r c h a r 2 ) as
begin
dbms output . p u t l i n e ( s t ) ;
end ;

Example (Caller)
c r e a t e o r r e p l a c e procedure c a l l p i s
begin
p ( H e l l o ) ; p ( World ! ) ;
end ;

Note
Can call own and built-in stored procedures
Will use the procedure p instead of dbms output.put line
You are now officially a PL/SQL library builder!!!
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

15 / 56

Control Structures: A Crash Course I

Example (The If Statement)
c r e a t e o r r e p l a c e procedure pb ( v a l boolean ) i s
begin
i f v a l = t r u e then
dbms output . p u t l i n e ( t r u e ) ;
e l s i f v a l = f a l s e then
dbms output . p u t l i n e ( f a l s e ) ;
else
dbms output . p u t l i n e ( n u l l ) ;
end i f ;
end ;

Note
Is this stupid?
Recall three-valued logic the root of all evil!
We will use the procedure pb in the code that follows!
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

16 / 56

Control Structures: A Crash Course II

Example (The While Statement)
c r e a t e o r r e p l a c e procedure count 10 i s
i i n t := 1;
begin
w h i l e i < 10 l o o p
dbms output . p u t l i n e ( i ) ;
i := i + 1;
end l o o p ;

Note
What is printed 1 to 9 or 1 to 10?
PL/SQL also has a for statement, very different from C
PL/SQL does not have increment/decrement operators, e.g., i or ++j
PL/SQL does not have compound assignments, e.g., i +=7 or j =2

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

17 / 56

Surprises: In General
Surprises
The code is stored in the DBMS!

{ has been replaced by begin and } by end

SQL and programming logic blends very nicely!
I

A strong-point of PL/SQL

Procedures are different from functions

The assignment operator is := and not =
The comparison operator is = and not ==
Control structures are quite different from the C world
Three-valued logic will time and again surprise you!
Server output is not enabled by default
I

Which is a big surprise

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

18 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

19 / 56

An Example
Example (Various Data Types)

c r e a t e o r r e p l a c e procedure u s e b a s i c t y p e s i s
v s t r v a r c h a r 2 ( 3 0 ) : = A s t r i n g ;
v i n t i n t : = 2 65;
c p i c o n s t a n t f l o a t : = 3.14159265358979323846264338327950288419;
v f l o a t f l o a t := v i n t c p i ;
begin
p( v str ); p( v int ); p( v float );
end ;

Output
A string
36893488147419103232
115904311329233965478,149216911761758199

Note
Forget what you think of data types and size!
Very high precision on all number types in both SQL and PL/SQL
The size of strings must be defined
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

20 / 56

Overview: Scalar Data Types

Scalar Data Types
Description
Integers

Floats

Strings
Boolean
Date/time

Type

Examples

smallint
int/integer
positive
number
dec/decimal
real
varchar2
nvarchar2
char
Boolean
date
timestamp

-100, 0, 100
-1000, 0, 1000
0, 1, 2, 3
10.3
123.456, 3.4
123456.7890
Hello, Theta-Join
Tger, Dmon
World, Noise
True, False
2007-09-09
2009-09-09 12:34:56

Note
Not all of these data types are available from within SQL!
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

21 / 56

Quiz: The Decimal Data Type

Example (Rouding)
create or
v dec
begin
v dec
dbms
v dec
dbms
v dec
dbms
end ;

r e p l a c e procedure u s i n g d e c i m a l i s
decimal ( 4 , 2 ) ;
:= 12.34;
output . p u t l i n e ( v dec ) ;
:= 12.344;
output . p u t l i n e ( v dec ) ;
:= 12.347;
output . p u t l i n e ( v dec ) ;

Questions
Will this compile, note that it is decimal(4,2)?
What will be printed (if it compiles)?
Are you surprised?
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

22 / 56

Overview: Other Data Types

Special Data Types
Description

Type

Composite

Large Objects
Reference Types

Record
Varray
Table
BLOB
CLOB
BFILE
REF
REF CURSOR

Note
We will only use records in this lecture.

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

23 / 56

Anchored Data Types: Type

Example (Anchor for a Column)
create or replace f u n c t i o n get status anchored (
s t u d e n t i d s t u d e n t . s i d%t y p e )
r e t u r n s t a t u s . dsc%t y p e i s
v s t a t u s s t a t u s . dsc%t y p e ;
begin
s e l e c t s t a . dsc
into
v status
from
s t u d e n t stu , s t a t u s s t a
where s t u . s t a t i d = s t a . s t a t i d
and
stu . sid = student id ;
return v status ;
end ;

Note
The anchored type using the %type
Very convenient of maintenance reasons (avoid hard-wiring types)
I

Widely used, you are encouraged to use it!

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

24 / 56

Anchored Data Types: Rowtype

Example (Anchor for a Table)
c r e a t e o r r e p l a c e procedure g e t c o u r s e r o w t y p e (
c o u r s e i d course . c i d%t y p e )
is
v row course%rowtype ;
v tmp v a r c h a r 2 ( 5 0 0 ) ;
begin
select
into
v row
from
course c
where c . c i d = c o u r s e i d ;
v tmp : = v row . cname | | : | | v row . dsc ;
p ( v tmp ) ;
end ;

Note
The anchored type using the rowtype
I

Creates a record

The dot notation for access elements of the record

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

25 / 56

Surprises: Data Type

Note
Strings are a basic type, not an object like in Java or C#
I

A maximum size must be specified

The sizes of the basic data type are very different from C and Java
Date and time are basic data types!
I

This is very handy

The anchored types is something new compared to C and Java

Booleans are not a basic data type in SQL but in PL/SQL
I

This sometimes leads to very annoying problems

Support for composite data type is not very good in PL/SQL

compared to C and Java
LOB objects are plain stupid
I

But sometimes necessary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

26 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

27 / 56

Overview
Example
create or
v tmp
begin
v tmp
v a l
end ;

r e p l a c e procedure p i n ( v a l i n i n t ) i s
int ;
:= val + 5;
: = v a l + 5 ; / i l l e g a l v a l i s read o n l y /

Example
c r e a t e o r r e p l a c e procedure p i n o u t ( v a l i n o u t i n t ) i s
begin
val := val + 5;
end ;

Example
c r e a t e o r r e p l a c e procedure c a l l p s i s
v in
i n t := 10;
v i n o u t i n t := 10;
begin
p in ( v in ) ; p( v in ) ;
p in out ( v in out ) ; p( v in out ) ;
end ;

When execute call ps prints 10 and 15, why?

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

28 / 56

Quiz
Example
c r e a t e o r r e p l a c e procedure p i n o u t ( v a l i n o u t i n t ) i s
begin
val := val + 5;
end ;

Example
c r e a t e o r r e p l a c e procedure c a l l p s i s
v in
i n t := 10;
v i n o u t i n t := 10;
begin
p in ( v in ) ; p( v in ) ;
p in out ( v in out ) ; p( v in out ) ;
end ;

Questions
What are the formal parameter(s)?
What are the actual parameter(s)?
Is it call-by-value or call-by-reference?
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

29 / 56

Out Parameters
Example
c r e a t e o r r e p l a c e procedure g e t x y c o o r (
c o o r i d i n i n t , x coor out i n t , y coor out i n t )
is
begin
x c o o r : = round ( c o o r i d / 4 . 2 ) ; s t u p i d c a l c u l a t i o n s
y c o o r : = round ( c o o r i d / 7 . 5 ) ;
end ;

Note
in

and out parameters can both be used in same procedure

The out parameters are write-only

More than one value is returned by the procedure
The calculation is naturally plain stupid
round

is the built-in rounding function

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

30 / 56

Parameter Mode

Mode
Mode

Description

in

Formal parameter is read-only

Formal parameter is write-only
Formal parameter is read-write

out
in out

Note
in

is the default parameter mode if the mode is not specified

Stored procedures cannot be overloaded on the parameter signature

There is a nocopy compiler hint for in out parameters

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

31 / 56

What is Wrong Here?

c r e a t e procedure p r o c 1 ( i i n t )
is
begin
s n i p c o m p l i c a t e d s t u f f
return i ;
end ;

create function func 1 ( i i n t )

return i n t is
begin
s n i p c o m p l i c a t e d s t u f f
return hello ;
end ;

create function func 2 ( i i n t )

return i n t is
begin
s n i p c o m p l i c a t e d s t u f f
return i 2;
p ( h e l l o world ) ;
end ;

create function func 3 ( i i n t )

return i n t is
begin
s n i p c o m p l i c a t e d s t u f f
p ( h e l l o world ) ;
end ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

32 / 56

Avoid This

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

33 / 56

Additional Comments on Parameters

Items to Notice
A default value can be provided for each parameter
Stored procedures cannot be overloaded on the parameter signature
Stored procedures can be called by position or by name
Works like in most programming languages, however different syntax!

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

34 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

35 / 56

Overview

Definition
A cursor is a mechanism that ensure a result set can be identified by a
declarative language such as SQL and processed by a procedural
language such as PL/SQL or C#

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

36 / 56

Overview

Definition
A cursor is a mechanism that ensure a result set can be identified by a
declarative language such as SQL and processed by a procedural
language such as PL/SQL or C#

Solution
Solves the well-known impedance mismatch problem!

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

36 / 56

Overview

Definition
A cursor is a mechanism that ensure a result set can be identified by a
declarative language such as SQL and processed by a procedural
language such as PL/SQL or C#

Solution
Solves the well-known impedance mismatch problem!

Generality
Knowledge about cursors in PL/SQL is directly transferable to many other
programming languages.

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

36 / 56

The Unix ls command

Example (List Tables)
c r e a t e o r r e p l a c e procedure l s i s
cursor c tables i s
s e l e c t from c a t ;
v t a b le n a m e c a t . table name%t y p e ;
c a t . t a b l e t y p e%t y p e ;
v type
begin
open c t a b l e s ;
loop
f e t c h c t a b l e s i n t o v table name , v t y p e ;
e x i t when c t a b l e s%n o t f ou n d ;
p ( v t a b le n a m e ) ;
end l o o p ;
close c tables ;
end ;

Note
The view tab is a table that contains all table names
The cursor is declared, opened, fetched, and closed
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

37 / 56

Cursor Attributes

Attributes
Attribute
notfound
found
rowcount
isopen

Type
Boolean
Boolean
Integer
Boolean

Description
True if a record is fetched unsuccessfully
True if a record is fetched successfully
The number of records fetched from the cursor
True if cursor is open

Note
There are additional attributes for bulk operations.

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

38 / 56

Quiz: Using rowcount

Example
c r e a t e o r r e p l a c e procedure l s c n t i s
cursor c tables i s
s e l e c t table name from c a t ;
v t a b le n a m e c a t . table name%t y p e ;
begin
open c t a b l e s ;
loop
f e t c h c t a b l e s i n t o v t a b le n a m e ;
e x i t when c t a b l e s%n o t f ou n d ;
p ( c t a b l e s%rowcount | | | | v t a b le n a m e ) ;
end l o o p ;
close c tables ;
end ;

Question
What is printed?
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

39 / 56

Quiz: Using isopen?

Example
c r e a t e o r r e p l a c e procedure l s i s o p e n i s
cursor c tables i s
s e l e c t table name from c a t ;
v t a b le n a m e c a t . table name%t y p e ;
v s t a t u s boolean : = f a l s e ;
begin
v s t a t u s : = c t a b l e s%isopen ; pb ( v s t a t u s
open c t a b l e s ;
v s t a t u s : = c t a b l e s%isopen ; pb ( v s t a t u s
loop
f e t c h c t a b l e s i n t o v t a b le n a m e ;
e x i t when c t a b l e s%n o t f ou n d ;
end l o o p ;
v s t a t u s : = c t a b l e s%isopen ; pb ( v s t a t u s
close c tables ;
v s t a t u s : = c t a b l e s%isopen ; pb ( v s t a t u s
end ;

);
);

);
);

Question
What is printed?
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

40 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

41 / 56

Introduction

Idea
A class like concept
Very good for building libraries
I

A way to cluster related stored procedures

Has a header and a body (think C-style languages)

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

42 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

43 / 56

Introduction
Goal
To build a uniform way to address the data stored in table!

Methods
Name

Description

exist(<primary key>)
to string(<primary key>)
print(<primary key>)

Return true if primary key exists

Return string representation of row
Convenient way to display a row

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

44 / 56

Introduction
Goal
To build a uniform way to address the data stored in table!

Methods
Name

Description

exist(<primary key>)
to string(<primary key>)
print(<primary key>)

Return true if primary key exists

Return string representation of row
Convenient way to display a row

Note
Many more methods can be envisioned
Think object-relational mapping (ORM) tools

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

44 / 56

Header File: Course Table

Example (Header)
c r e a t e o r r e p l a c e package ccourse i s
f u n c t i o n e x i s t ( c i d course . c i d%t y p e ) r e t u r n boolean ;
f u n c t i o n t o s t r i n g ( c i d course . c i d%t y p e ) r e t u r n s t r i n g ;
procedure p r i n t ( c i d course . c i d%t y p e ) ;
end ;

Note
The header lists all the public stored procedures
The naming convention table name course package name ccourse
The design is influenced by the Object class from Java and C#

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

45 / 56

Header File: Course Table

Example (Header)
c r e a t e o r r e p l a c e package ccourse i s
f u n c t i o n e x i s t ( c i d course . c i d%t y p e ) r e t u r n boolean ;
f u n c t i o n t o s t r i n g ( c i d course . c i d%t y p e ) r e t u r n s t r i n g ;
procedure p r i n t ( c i d course . c i d%t y p e ) ;
end ;

Note
The header lists all the public stored procedures
The naming convention table name course package name ccourse
The design is influenced by the Object class from Java and C#

Quiz
Why is the method called exist and not exists?

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

45 / 56

Body File: Private Method and Cursor

Example (Body)
c r e a t e o r r e p l a c e package body ccourse i s
p r i v a t e c o n s t a n t
c e r r o r c i d n u l l c o n s t a n t i n t : = 20001;
a c u r s o r used i n t h e i m p l e m e n t a t i o n
c u r s o r c u r e x i s t ( c v c i d course . c i d%t y p e ) i s
s e l e c t c . c i d , c . cname , c . semester , c . dsc
from
course c
where c . c i d = c v c i d ;
a p r i v a t e method
procedure c h e c k v a l i d c i d ( c i d course . c i d%t y p e ) i s
begin
i f c i d i s n u l l then
raise application error ( c error cid null ,
Course ID i s n u l l ) ;
end i f ;
end ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

46 / 56

Body File: Private Method and Cursor

Example (Body)
c r e a t e o r r e p l a c e package body ccourse i s
p r i v a t e c o n s t a n t
c e r r o r c i d n u l l c o n s t a n t i n t : = 20001;
a c u r s o r used i n t h e i m p l e m e n t a t i o n
c u r s o r c u r e x i s t ( c v c i d course . c i d%t y p e ) i s
s e l e c t c . c i d , c . cname , c . semester , c . dsc
from
course c
where c . c i d = c v c i d ;
a p r i v a t e method
procedure c h e c k v a l i d c i d ( c i d course . c i d%t y p e ) i s
begin
i f c i d i s n u l l then
raise application error ( c error cid null ,
Course ID i s n u l l ) ;
end i f ;
end ;

Note
The method check valid cid is private
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

46 / 56

Body File: The Exist Method

Example (Method)
f u n c t i o n e x i s t ( c i d course . c i d%t y p e ) r e t u r n boolean i s
r e c e x i s t c u r e x i s t%rowtype ;
begin
c h e c k v a l i d c i d ( c i d ) ; p r e c o n d i t i o n
open c u r e x i s t ( c i d ) ;
fetch cur exist into rec exist ;
close c u r e x i s t ;
r e t u r n ( r e c e x i s t . cid i s not n u l l ) ;
end ;

Note
Uses the private method check valid cid to check preconditions
Uses the private cursor cur exist
Returns true if a valid primary key is found

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

47 / 56

Body File: The to string Method

Example (Method)
f u n c t i o n t o s t r i n g ( c i d course . c i d%t y p e ) r e t u r n s t r i n g i s
v rv string (512);
r e c e x i s t c u r e x i s t%rowtype ;
begin
c h e c k v a l i d c i d ( c i d ) ; p r e c o n d i t i o n
open c u r e x i s t ( c i d ) ;
fetch cur exist into rec exist ;
close c u r e x i s t ;
v r v : = course name : | | r e c e x i s t . cname | | | |
course desc : | | r e c e x i s t . dsc ;
return v rv ;
end ;

Note
Uses the private method check valid cid to check preconditions
Uses the private cursor cur exist
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

48 / 56

Body File: The print Methods

Example (Method)
procedure p r i n t ( c i d course . c i d%t y p e ) i s
begin
c h e c k v a l i d c i d ( c i d ) ; p r e c o n d i t i o n
dbms output . p u t l i n e ( t o s t r i n g ( c i d ) ) ;
end ;

Note
Uses the private method check valid cid to check preconditions

print calls to string

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

49 / 56

Exercising the Package

Example
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute ccourse . p r i n t ( 4 ) ;

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

50 / 56

Exercising the Package

Example
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute ccourse . p r i n t ( 4 ) ;

Note
Similar to executing a stored procedure
Access member by the dot notation

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

50 / 56

Exercising the Package

Example
SQL> s e t s e r v e r o u t p u t on
execute t h e procedure
SQL> execute ccourse . p r i n t ( 4 ) ;

Note
Similar to executing a stored procedure
Access member by the dot notation

Example
SQL> execute ccourse . p r i n t ( n u l l ) ;

Note
Results in an error ORA-20001: Course ID is null

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

50 / 56

Summary: Packages
Main Points
Can have a public and a private part
I

Has no protected access modifiers as in Java or C#

Is used to cluster related stored procedures

Cursors, constants, and variables can be shared between methods in
a package
The foundation for building larger libraries in PL/SQL
There is a huge library of built-it packages on Oracle
Has very good exception handling facilities

Comparison to Object-Oriented Languages

No inheritance
Only static methods
No concept of an object
Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

51 / 56

Outline

Introduction

Stored Procedures
An Overview
Data Types
Parameter Parsing
Cursors

Packages
Case Study: Table API

Summary

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

52 / 56

Advantages

Advantages
A complete programming language
I

You are not missing stuff as you sometimes are in SQL

In wide-spread usage in the industry

I

Adds to your market value

Very good integration of programming logic and SQL

Impedance mismatch is basically removed
I
I

PL/SQL data types are super set of SQL data types

Cursors enable the processing of sets (or bags)

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

53 / 56

Disadvantages

Disadvantages
Proprietary programming language
There is a very large number (>1000) of reserved words
I

Can be hard to come up with a variable name that is not a reserved

word!

Pascal-family language (C-family more well-known)

I

Which lead to a number of surprises

Object-oriented features are clumsy

I

This has not been covered in this lecture

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

54 / 56

Additional Information

Web Sites
www.oracle.com/technology/tech/pl_sql/index.html
PL/SQLs home

www.psoug.org/library.html A very good and complete wiki with

PL/SQL information

plsql-tutorial.com/ A crash course covering many PL/SQL

features

en.wikibooks.org/wiki/Oracle_Programming/SQL_Cheatsheet
A short overview of PL/SQL

www.java2s.com/Tutorial/Oracle/CatalogOracle.htm Many
good examples, too many commercials

Kristian Torp (Aalborg University)

Introduction to PL/SQL

December 2, 2011

55 / 56