D80182GC10 Ag
D80182GC10 Ag
D80182GC10 Ag
Fundamentals
Activity Guide
D80182GC10
Edition 1.0
August 2013
D83267
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 2: Introduction to PL/SQL
Chapter 2 - Page 1
Practices for Lesson 2:
Introduction to PL/SQL
Chapter 2
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 2: Introduction to PL/SQL
Chapter 2 - Page 2
Practices for Lesson 2: Introduction to PL/SQL
Lesson Overview
The /home/oracle/labs/plsf/labs folder is the working directory where you save the
scripts that you create.
The solutions for all the practices are in the /home/oracle/labs/plsf/soln folder.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 2: Introduction to PL/SQL
Chapter 2 - Page 3
Practice 2: Introduction to PL/SQL
1. Which of the following PL/SQL blocks execute successfully?
a. BEGIN
END;
b. DECLARE
v_amount INTEGER(10);
END;
c. DECLARE
BEGIN
END;
d. DECLARE
v_amount INTEGER(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(v_amount);
END;
2. Create and execute a simple anonymous block that outputs Hello World. Execute and
save this script as lab_02_02_soln.sql.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 2: Introduction to PL/SQL
Chapter 2 - Page 4
Solution 2: Introduction to PL/SQL
1. Which of the following PL/SQL blocks execute successfully?
a. BEGIN
END;
b. DECLARE
v_amount INTEGER(10);
END;
c. DECLARE
BEGIN
END;
d. DECLARE
v_amount INTEGER(10);
BEGIN
DBMS_OUTPUT.PUT_LINE(v_amount);
END;
The block in a does not execute. It has no executable statements.
The block in b does not have the mandatory executable section that starts with the BEGIN
keyword.
The block in c has all the necessary parts, but no executable statements.
The block in d executes successfully.
2. Create and execute a simple anonymous block that outputs Hello World. Execute and
save this script as lab_02_02_soln.sql.
Enter the following code in the workspace, and then press F5.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(' Hello World ');
END;
You should see the following output on the Script Output tab:
Click the Save button. Select the folder in which you want to save the file. Enter
lab_02_02_soln.sql as the file name and click Save.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 1
Practices for Lesson 4:
Writing Executable
Statements
Chapter 4
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 2
Practice 4: Writing Executable Statements
Note: If you have executed the code examples for this lesson, make sure you execute the
following code before starting this practice:
DROP sequence my_seq;
In this practice, you examine and write executable statements.
DECLARE
v_weight NUMBER(3) := 600;
v_message VARCHAR2(255) := 'Product 10012';
BEGIN
DECLARE
v_weight NUMBER(3) := 1;
v_message VARCHAR2(255) := 'Product 11001';
v_new_locn VARCHAR2(50) := 'Europe';
BEGIN
v_weight := v_weight + 1;
v_new_locn := 'Western ' || v_new_locn;
END;
v_weight := v_weight + 1;
v_message := v_message || ' is in stock';
v_new_locn := 'Western ' || v_new_locn;
END;
/
1. Evaluate the preceding PL/SQL block and determine the data type and value of each of the
following variables, according to the rules of scoping.
a. The value of v_weight at position 1 is:
b. The value of v_new_locn at position 1 is:
c. The value of v_weight at position 2 is:
d. The value of v_message at position 2 is:
e. The value of v_new_locn at position 2 is:
DECLARE
v_customer VARCHAR2(50) := 'Womansport';
v_credit_rating VARCHAR2(50) := 'EXCELLENT';
BEGIN
DECLARE
v_customer NUMBER(7) := 201;
v_name VARCHAR2(25) := 'Unisports';
BEGIN
v_credit_rating :='GOOD';
END;
END;
1
2
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 3
2. In the preceding PL/SQL block, determine the values and data types for each of the
following cases:
a. The value of v_customer in the nested block is:
b. The value of v_name in the nested block is:
c. The value of v_credit_rating in the nested block is:
d. The value of v_customer in the main block is:
e. The value of v_name in the main block is:
f. The value of v_credit_rating in the main block is:
3. Use the same session that you used to execute the practices in the lesson titled Declaring
PL/SQL Variables. If you have opened a new session, execute lab_03_05_soln.sql.
Then, edit lab_03_05_soln.sql as follows:
a. Use single-line comment syntax to comment the lines that create the bind variables,
and turn on SERVEROUTPUT.
b. Use multiple-line comments in the executable section to comment the lines that assign
values to the bind variables.
c. In the declaration section:
1) Declare and initialize two temporary variables to replace the commented out bind
variables
2) Declare two additional variables: v_fname of type VARCHAR2 and size 15, and
v_emp_sal of type NUMBER and size 10
d. Include the following SQL statement in the executable section:
SELECT first_name, salary INTO v_fname, v_emp_sal
FROM employees WHERE employee_id=110;
e. Change the line that prints Hello World to print Hello and the first name. Then,
comment the lines that display the dates and print the bind variables.
f. Calculate the contribution of the employee towards provident fund (PF).
PF is 12% of the basic salary, and the basic salary is 45% of the salary. Use local
variables for the calculation. Try to use only one expression to calculate the PF. Print
the employees salary and his or her contribution toward PF.
g. Execute and save your script as lab_04_03_soln.sql. The sample output is as
follows:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 4
Solution 4: Writing Executable Statements
In this practice, you examine and write executable statements.
DECLARE
v_weight NUMBER(3) := 600;
v_message VARCHAR2(255) := 'Product 10012';
BEGIN
DECLARE
v_weight NUMBER(3) := 1;
v_message VARCHAR2(255) := 'Product 11001';
v_new_locn VARCHAR2(50) := 'Europe';
BEGIN
v_weight := v_weight + 1;
v_new_locn := 'Western ' || v_new_locn;
END;
v_weight := v_weight + 1;
v_message := v_message || ' is in stock';
v_new_locn := 'Western ' || v_new_locn;
END;
/
1. Evaluate the preceding PL/SQL block and determine the data type and value of each of the
following variables, according to the rules of scoping.
a. The value of v_weight at position 1 is:
2
The data type is NUMBER.
b. The value of v_new_locn at position 1 is:
Western Europe
The data type is VARCHAR2.
c. The value of v_weight at position 2 is:
601
The data type is NUMBER.
d. The value of v_message at position 2 is:
Product 10012 is in stock
The data type is VARCHAR2.
e. The value of v_new_locn at position 2 is:
Illegal because v_new_locn is not visible outside the subblock
1
2
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 5
DECLARE
v_customer VARCHAR2(50) := 'Womansport';
v_credit_rating VARCHAR2(50) := 'EXCELLENT';
BEGIN
DECLARE
v_customer NUMBER(7) := 201;
v_name VARCHAR2(25) := 'Unisports';
BEGIN
v_credit_rating :='GOOD';
END;
END;
2. In the preceding PL/SQL block, determine the values and data types for each of the
following cases:
a. The value of v_customer in the nested block is:
201
The data type is NUMBER.
b. The value of v_name in the nested block is:
Unisports
The data type is VARCHAR2.
c. The value of v_credit_rating in the nested block is:
GOOD
The data type is VARCHAR2.
d. The value of v_customer in the main block is:
Womansport
The data type is VARCHAR2.
e. The value of v_name in the main block is:
Null. name is not visible in the main block and you would see an error.
f. The value of v_credit_rating in the main block is:
EXCELLENT
The data type is VARCHAR2.
3. Use the same session that you used to execute the practices in the lesson titled Declaring
PL/SQL Variables. If you have opened a new session, execute lab_03_05_soln.sql.
Then, edit lab_03_05_soln.sql as follows:
a. Use single-line comment syntax to comment the lines that create the bind variables,
and turn on SERVEROUTPUT.
-- VARIABLE b_basic_percent NUMBER
-- VARIABLE b_pf_percent NUMBER
SET SERVEROUTPUT ON
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 6
b. Use multiple-line comments in the executable section to comment the lines that assign
values to the bind variables.
/*:b_basic_percent:=45;
:b_pf_percent:=12;*/
c. In the declaration section:
1) Declare and initialize two temporary variables to replace the commented out bind
variables
2) Declare two additional variables: v_fname of type VARCHAR2 and size 15, and
v_emp_sal of type NUMBER and size 10
DECLARE
v_basic_percent NUMBER:=45;
v_pf_percent NUMBER:=12;
v_fname VARCHAR2(15);
v_emp_sal NUMBER(10);
d. Include the following SQL statement in the executable section:
SELECT first_name, salary INTO v_fname, v_emp_sal
FROM employees WHERE employee_id=110;
e. Change the line that prints Hello World to print Hello and the first name. Then,
comment the lines that display the dates and print the bind variables.
DBMS_OUTPUT.PUT_LINE(' Hello '|| v_fname);
/* DBMS_OUTPUT.PUT_LINE('TODAY IS : '|| v_today);
DBMS_OUTPUT.PUT_LINE('TOMORROW IS : ' || v_tomorrow);*/
...
...
/
--PRINT b_basic_percent
--PRINT b_basic_percent
f. Calculate the contribution of the employee towards provident fund (PF).
PF is 12% of the basic salary, and the basic salary is 45% of the salary. Use local
variables for the calculation. Try to use only one expression to calculate the PF. Print
the employees salary and his or her contribution toward PF.
DBMS_OUTPUT.PUT_LINE('YOUR SALARY IS : '||v_emp_sal);
DBMS_OUTPUT.PUT_LINE('YOUR CONTRIBUTION TOWARDS PF:
'||v_emp_sal*v_basic_percent/100*v_pf_percent/100);
END;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 7
g. Execute and save your script as lab_04_03_soln.sql. The sample output is as
follows:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 4: Writing Executable Statements
Chapter 4 - Page 8
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 1
Practices for Lesson 5: Using
SQL Statements within a
PL/SQL Block
Chapter 5
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 2
Practice 5: Using SQL Statements within a PL/SQL
Note: If you have executed the code examples for this lesson, make sure you execute the
following code before starting this practice:
DROP table employees2;
DROP table copy_emp;
In this practice, you use PL/SQL code to interact with the Oracle Server.
1. Create a PL/SQL block that selects the maximum department ID in the departments table
and stores it in the v_max_deptno variable. Display the maximum department ID.
a. Declare a variable v_max_deptno of type NUMBER in the declarative section.
b. Start the executable section with the BEGIN keyword and include a SELECT statement
to retrieve the maximum department_id from the departments table.
c. Display v_max_deptno and end the executable block.
d. Execute and save your script as lab_05_01_soln.sql. The sample output is as
follows:
2. Modify the PL/SQL block that you created in step 1 to insert a new department into the
departments table.
a. Load the lab_05_01_soln.sql script. Declare two variables:
v_dept_name of type departments.department_name and
v_dept_id of type NUMBER
Assign 'Education' to v_dept_name in the declarative section.
b. You have already retrieved the current maximum department number from the
departments table. Add 10 to it and assign the result to v_dept_id.
c. Include an INSERT statement to insert data into the department_name,
department_id, and location_id columns of the departments table.
Use values in dept_name and dept_id for department_name and
department_id, respectively, and use NULL for location_id.
d. Use the SQL attribute SQL%ROWCOUNT to display the number of rows that are affected.
e. Execute a SELECT statement to check whether the new department is inserted. You
can terminate the PL/SQL block with / and include the SELECT statement in your
script.
f. Execute and save your script as lab_05_02_soln.sql. The sample output is as
follows:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 3
3. In step 2, you set location_id to NULL. Create a PL/SQL block that updates the
location_id to 3000 for the new department.
Note: If you successfully completed step 2, continue with step 3a. If not, first execute the
solution script /soln/sol_05_02.sql.
a. Start the executable block with the BEGIN keyword. Include the UPDATE statement to
set the location_id to 3000 for the new department (dept_id =280).
b. End the executable block with the END keyword. Terminate the PL/SQL block with /
and include a SELECT statement to display the department that you updated.
c. Include a DELETE statement to delete the department that you added.
d. Execute and save your script as lab_05_03_soln.sql. The sample output is as
follows:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 4
Solution 5: Using SQL Statements within a PL/SQL
In this practice, you use PL/SQL code to interact with the Oracle Server.
1. Create a PL/SQL block that selects the maximum department ID in the departments table
and stores it in the v_max_deptno variable. Display the maximum department ID.
a. Declare a variable v_max_deptno of type NUMBER in the declarative section.
DECLARE
v_max_deptno NUMBER;
b. Start the executable section with the BEGIN keyword and include a SELECT statement
to retrieve the maximum department_id from the departments table.
BEGIN
SELECT MAX(department_id) INTO v_max_deptno FROM
departments;
c. Display v_max_deptno and end the executable block.
DBMS_OUTPUT.PUT_LINE('The maximum department_id is : ' ||
v_max_deptno);
END;
d. Execute and save your script as lab_05_01_soln.sql. The sample output is as
follows:
2. Modify the PL/SQL block that you created in step 1 to insert a new department into the
departments table.
a. Load the lab_05_01_soln.sql script. Declare two variables:
v_dept_name of type departments.department_name and
v_dept_id of type NUMBER
Assign Education to v_dept_name in the declarative section.
v_dept_name departments.department_name%TYPE:= 'Education';
v_dept_id NUMBER;
b. You have already retrieved the current maximum department number from the
departments table. Add 10 to it and assign the result to v_dept_id.
v_dept_id := 10 + v_max_deptno;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 5
c. Include an INSERT statement to insert data into the department_name,
department_id, and location_id columns of the departments table.
Use values in dept_name and dept_id for department_name and
department_id, respectively, and use NULL for location_id.
INSERT INTO departments (department_id, department_name,
location_id)
VALUES (v_dept_id, v_dept_name, NULL);
d. Use the SQL attribute SQL%ROWCOUNT to display the number of rows that are affected.
DBMS_OUTPUT.PUT_LINE (' SQL%ROWCOUNT gives ' || SQL%ROWCOUNT);
e. Execute a SELECT statement to check whether the new department is inserted. You
can terminate the PL/SQL block with / and include the SELECT statement in your
script.
/
SELECT * FROM departments WHERE department_id= 280;
f. Execute and save your script as lab_05_02_soln.sql. The sample output is as
follows:
3. In step 2, you set location_id to NULL. Create a PL/SQL block that updates the
location_id to 3000 for the new department.
Note: If you successfully completed step 2, continue with step 3a. If not, first execute the
solution script /soln/sol_05_02.sql.
a. Start the executable block with the BEGIN keyword. Include the UPDATE statement to
set location_id to 3000 for the new department (dept_id =280).
BEGIN
UPDATE departments SET location_id=3000 WHERE
department_id=280;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 5: Using SQL Statements within a PL/SQL Block
Chapter 5 - Page 6
b. End the executable block with the END keyword. Terminate the PL/SQL block with /
and include a SELECT statement to display the department that you updated.
END;
/
SELECT * FROM departments WHERE department_id=280;
c. Include a DELETE statement to delete the department that you added.
DELETE FROM departments WHERE department_id=280;
d. Execute and save your script as lab_05_03_soln.sql. The sample output is as
follows:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 2
Chapter 12 - Page 1
Additional Practices and
Solutions for Lesson 2
Chapter 12
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 2
Chapter 12 - Page 2
Additional Practices for Lesson 2
Overview
These additional practices are provided as a supplement to the Oracle Database: PL/SQL
Fundamentals course. In these practices, you apply the concepts that you learned in the course.
These additional practices provide supplemental practice in declaring variables, writing
executable statements, interacting with the Oracle Server, writing control structures, and
working with composite data types, cursors, and handle exceptions. The tables used in this
portion of the additional practices include employees, jobs, job_history, and
departments.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 2
Chapter 12 - Page 3
Practice 2: Evaluating Declarations
Overview
These paper-based exercises are used for extra practice in declaring variables and writing
executable statements.
Evaluate each of the following declarations. Determine which of them are not legal and explain
why.
1. DECLARE
name,dept VARCHAR2(14);
2. DECLARE
test NUMBER(5);
3. DECLARE
MAXSALARY NUMBER(7,2) = 5000;
4. DECLARE
JOINDATE BOOLEAN := SYSDATE;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 2
Chapter 12 - Page 4
Solution 2: Evaluating Declarations
Evaluate each of the following declarations. Determine which of them are not legal and explain
why.
1. DECLARE
name,dept VARCHAR2(14);
This is illegal because only one identifier per declaration is allowed.
2. DECLARE
test NUMBER(5);
This is legal.
3. DECLARE
MAXSALARY NUMBER(7,2) = 5000;
This is illegal because the assignment operator is wrong. It should be :=.
4. DECLARE
JOINDATE BOOLEAN := SYSDATE;
This is illegal because there is a mismatch in the data types. A Boolean data type
cannot be assigned a date value. The data type should be date.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 3
Chapter 13 - Page 1
Additional Practices and
Solutions for Lesson 3
Chapter 13
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 3
Chapter 13 - Page 2
Practice 3: Evaluating Expressions
In each of the following assignments, determine the data type of the resulting expression.
1. email := firstname || to_char(empno);
2. confirm := to_date('20-JAN-1999', 'DD-MON-YYYY');
3. sal := (1000*12) + 500
4. test := FALSE;
5. temp := temp1 < (temp2/ 3);
6. var := sysdate;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 3
Chapter 13 - Page 3
Solution 3: Evaluating Expressions
In each of the following assignments, determine the data type of the resulting expression.
1. email := firstname || to_char(empno);
Character string
2. confirm := to_date('20-JAN-1999', 'DD-MON-YYYY');
Date
3. sal := (1000*12) + 500
Number
4. test := FALSE;
Boolean
5. temp := temp1 < (temp2/ 3);
Boolean
6. var := sysdate;
Date
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 3
Chapter 13 - Page 4
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 4
Chapter 14 - Page 1
Additional Practices and
Solutions for Lesson 4
Chapter 14
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 4
Chapter 14 - Page 2
Practice 4: Evaluating Executable Statements
In this paper-based exercise, you evaluate the PL/SQL block, and then answer the questions
that follow by determining the data type and value of each variable, according to the rules of
scoping.
DECLARE
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_ new_custid NUMBER(3) := 500;
BEGIN
DECLARE
v_custid NUMBER(4) := 0;
v_custname VARCHAR2(300) := 'Shape up Sports Club';
v_new_custid NUMBER(3) := 300;
v_new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
v_custid := v_new_custid;
v_custname := v_custname || ' ' || v_new_custname;
END;
v_custid := (v_custid *12) / 10;
END;
Evaluate the preceding PL/SQL block and determine the value and data type of each of the
following variables, according to the rules of scoping:
1. v_custid at position 1:
2. v_custname at position 1:
3. v_new_custid at position 1:
4. v_new_custname at position 1:
5. v_custid at position 2:
6. v_custname at position 2:
1
2
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 4
Chapter 14 - Page 3
Solution 4: Evaluating Executable Statements
Evaluate the following PL/SQL block. Then, answer the questions that follow by determining the
data type and value of each of the following variables, according to the rules of scoping.
DECLARE
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_ new_custid NUMBER(3) := 500;
BEGIN
DECLARE
v_custid NUMBER(4) := 0;
v_custname VARCHAR2(300) := 'Shape up Sports Club';
v_new_custid NUMBER(3) := 300;
v_new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
v_custid := v_new_custid;
v_custname := v_custname || ' ' || v_new_custname;
END;
v_custid := (v_custid *12) / 10;
END;
Evaluate the preceding PL/SQL block and determine the value and data type of each of the
following variables, according to the rules of scoping:
1. v_custid at position 1:
500, and the data type is NUMBER.
2. v_custname at position 1:
Shape up Sports Club Jansports Club, and the data type is VARCHAR2.
3. v_new_custid at position 1:
300, and the data type is NUMBER (or INTEGER).
4. v_new_custname at position 1:
Jansports Club, and the data type is VARCHAR2.
5. v_custid at position 2:
1920, and the data type is NUMBER.
6. v_custname at position 2:
Women Sports Club, and the data type is VARCHAR2.
1
2
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 4
Chapter 14 - Page 4
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 1
Additional Practices and
Solutions for Lesson 5
Chapter 15
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 2
Practice 5-1: Using SQL Statements within a PL/SQL
For this exercise, a temporary table is required to store the results.
1. Run the lab_ap_05.sql script that creates the table described here:
2. Write a PL/SQL block that performs the following:
a. Declares two variables and assigns the following values to these variables:
Variable Data type Contents
V_MESSAGE VARCHAR2(35) This is my first PL/SQL program
V_ DATE_WRITTEN DATE Current date
b. Stores the values from these variables in the appropriate TEMP table columns
3. Verify your results by querying the TEMP table. The output results should appear as follows:
Column Name NUM_STORE CHAR_STORE
DATE_STORE
Key Type
Nulls/Unique
FK Table
FK Column
Data Type Number VARCHAR2 Date
Length 7,2 35
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 3
Solution 5-1: Using SQL Statements within a PL/SQL
For this exercise, a temporary table is required to store the results.
1. Run the lab_ap_05.sql script that creates the table described here:
2. Write a PL/SQL block that performs the following:
a. Declares two variables and assigns the following values to these variables:
Variable Data type Contents
V_MESSAGE VARCHAR2(35) This is my first PL/SQL program
V_ DATE_WRITTEN DATE Current date
b. Stores the values from these variables in the appropriate TEMP table columns
DECLARE
V_MESSAGE VARCHAR2(35);
V_DATE_WRITTEN DATE;
BEGIN
V_MESSAGE := 'This is my first PLSQL Program';
V_DATE_WRITTEN := SYSDATE;
INSERT INTO temp(CHAR_STORE,DATE_STORE)
VALUES (V_MESSAGE,V_DATE_WRITTEN);
END;
/
3. Verify your results by querying the TEMP table. The output results should look similar to the
following:
SELECT * FROM TEMP;
Column Name NUM_STORE CHAR_STORE DATE_STORE
Key Type
Nulls/Unique
FK Table
FK Column
Data Type Number VARCHAR2 Date
Length 7,2 35
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 4
Practice 5-2: Using SQL Statements within a PL/SQL
In this exercise, you use data from the employees table.
1. Write a PL/SQL block to determine how many employees work for a specified department.
The PL/SQL block should:
Use a substitution variable to store a department number
Print the number of people working in the specified department
2. When the block is run, a substitution variable window appears. Enter a valid department
number and click OK. The output results should look similar to the following:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 5
Solution 5-2: Using SQL Statements within a PL/SQL
In this exercise, you use data from the employees table.
1. Write a PL/SQL block to determine how many employees work for a specified department.
The PL/SQL block should:
Use a substitution variable to store a department number
Print the number of people working in the specified department
SET SERVEROUTPUT ON;
DECLARE
V_HOWMANY NUMBER(3);
V_DEPTNO DEPARTMENTS.department_id%TYPE := &P_DEPTNO;
BEGIN
SELECT COUNT(*) INTO V_HOWMANY FROM employees
WHERE department_id = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE (V_HOWMANY || ' employee(s)
work for department number ' ||V_DEPTNO);
END;
/
2. When the block is run, a substitution variable window appears. Enter a valid department
number and click OK.
The output results should look similar to the following:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 5
Chapter 15 - Page 6
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 1
Additional Practices and
Solutions for Lesson 6
Chapter 16
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 2
Practice 6-1: Writing Control Structures
In these practices, you use control structures to direct the logic of program flow.
1. Write a PL/SQL block to accept a year input and check whether it is a leap year.
Hint: The year should be exactly divisible by 4 but not divisible by 100, or it should be
divisible by 400.
2. Test your solution by using the following table. For example, if the year entered is 1990, the
output should be 1990 is not a leap year.
1990 Not a leap year
2000 Leap year
1996 Leap year
1886 Not a leap year
1992 Leap year
1824 Leap year
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 3
Solution 6-1: Writing Control Structures
1. Write a PL/SQL block to accept a year input and check whether it is a leap year.
Hint: The year should be exactly divisible by 4 but not divisible by 100, or it should be
divisible by 400.
SET SERVEROUTPUT ON;
DECLARE
v_YEAR NUMBER(4) := &P_YEAR;
v_REMAINDER1 NUMBER(5,2);
v_REMAINDER2 NUMBER(5,2);
v_REMAINDER3 NUMBER(5,2);
BEGIN
v_REMAINDER1 := MOD(v_YEAR,4);
v_REMAINDER2 := MOD(v_YEAR,100);
v_REMAINDER3 := MOD(v_YEAR,400);
IF ((v_REMAINDER1 = 0 AND v_REMAINDER2 <> 0 ) OR
v_REMAINDER3 = 0) THEN
DBMS_OUTPUT.PUT_LINE(v_YEAR || ' is a leap year');
ELSE
DBMS_OUTPUT.PUT_LINE(v_YEAR || ' is not a leap
year');
END IF;
END;
/
2. Test your solution by using the following table. For example, if the year entered is 1990, the
output should be 1990 is not a leap year.
1990 Not a leap year
2000 Leap year
1996 Leap year
1886 Not a leap year
1992 Leap year
1824 Leap year
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 4
Practice 6-2: Writing Control Structures
1. Write a PL/SQL block to store the monthly salary of an employee in a substitution variable.
The PL/SQL block should:
Calculate the annual salary as salary * 12
Calculate the bonus as indicated in the following table:
Annual Salary Bonus
>= 20,000 2,000
19,99910,000 1,000
<= 9,999 500
Display the amount of the bonus in the Script Output window in the following format:
2. Test the PL/SQL for the following test cases:
Monthly Salary Bonus
3000 2000
1200 1000
800 500
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 5
Solution 6-2: Writing Control Structures
1. Write a PL/SQL block to store the monthly salary of an employee in a substitution variable.
The PL/SQL block should:
Calculate the annual salary as salary * 12
Calculate the bonus as indicated in the following table:
Annual Salary Bonus
>= 20,000 2,000
19,99910,000 1,000
<= 9,999 500
Display the amount of the bonus in the Script Output window in the following format:
SET SERVEROUTPUT ON;
DECLARE
V_SAL NUMBER(7,2) := &B_SALARY;
V_BONUS NUMBER(7,2);
V_ANN_SALARY NUMBER(15,2);
BEGIN
V_ANN_SALARY := V_SAL * 12;
IF V_ANN_SALARY >= 20000 THEN
V_BONUS := 2000;
ELSIF V_ANN_SALARY <= 19999 AND V_ANN_SALARY >=10000 THEN
V_BONUS := 1000;
ELSE
V_BONUS := 500;
END IF;
DBMS_OUTPUT.PUT_LINE ('The Bonus is $ ' ||
TO_CHAR(V_BONUS));
END;
/
2. Test the PL/SQL for the following test cases:
Monthly Salary Bonus
3000 2000
1200 1000
800 500
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 6
Chapter 16 - Page 6
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 1
Additional Practices and
Solutions for Lesson 7:
Working with Composite Data
Types
Chapter 17
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 2
Additional Practices for Lessons Titled Working with Composite
Data Types and Using Explicit Cursors
Overview
In the following exercises, you practice using associative arrays (this topic is covered in the
lesson titled Working with Composite Data Types) and explicit cursors (this topic is covered in
the lesson titled Using Explicit Cursors). In the first exercise, you define and use an explicit
cursor to fetch data. In the second exercise, you combine the use of associative arrays with an
explicit cursor to output data that meets a certain criteria.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 3
Practice 7/8-1: Fetching Data with an Explicit Cursor
In this practice, you create a PL/SQL block to perform the following:
1. Declare a cursor named EMP_CUR to select the employees last name, salary, and hire date
from the EMPLOYEES table.
2. Process each row from the cursor, and if the salary is greater than 15,000 and the hire date
is later than 01-FEB-1988, display the employee name, salary, and hire date in the format
shown in the following sample output:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 4
Solution 7/8-1: Fetching Data with an Explicit Cursor
In this practice, you create a PL/SQL block to perform the following:
1. Declare a cursor named EMP_CUR to select the employees last name, salary, and hire date
from the EMPLOYEES table.
SET SERVEROUTPUT ON;
DECLARE
CURSOR C_EMP_CUR IS
SELECT last_name,salary,hire_date FROM EMPLOYEES;
V_ENAME VARCHAR2(25);
v_SAL NUMBER(7,2);
V_HIREDATE DATE;
2. Process each row from the cursor, and if the salary is greater than 15,000 and the hire date
is later than 01-FEB-1988, display the employee name, salary, and hire date in the format
shown in the following sample output:
BEGIN
OPEN C_EMP_CUR;
FETCH C_EMP_CUR INTO V_ENAME,V_SAL,V_HIREDATE;
WHILE C_EMP_CUR%FOUND
LOOP
IF V_SAL > 15000 AND V_HIREDATE >=
TO_DATE('01-FEB-1988','DD-MON-YYYY') THEN
DBMS_OUTPUT.PUT_LINE (V_ENAME || ' earns '
|| TO_CHAR(V_SAL)|| ' and joined the organization on '
|| TO_DATE(V_HIREDATE,'DD-Mon-YYYY'));
END IF;
FETCH C_EMP_CUR INTO V_ENAME,V_SAL,V_HIREDATE;
END LOOP;
CLOSE C_EMP_CUR;
END;
/
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 5
Practice 7/8-2: Using Associative Arrays and Explicit Cursors
In this practice, you create a PL/SQL block to retrieve and output the last name and department
ID of each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is
less than 115.
In the PL/SQL block, use a cursor FOR loop strategy instead of the OPEN / FETCH / CLOSE
cursor methods used in the previous practice.
1. In the declarative section:
Create two associative arrays. The unique key column for both arrays should be of the
BINARY INTEGER data type. One array holds the employees last name and the other
holds the department ID.
Declare a cursor that selects the last name and department ID for employees whose ID
is less than 115
Declare the appropriate counter variable to be used in the executable section
2. In the executable section, use a cursor FOR loop (covered in the lesson titled Using Explicit
Cursors) to access the cursor values, assign them to the appropriate associative arrays,
and output those values from the arrays. The correct output should return 15 rows, in the
following format:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 6
Solution 7/8-2: Using Associative Arrays and Explicit Cursors
In this practice, you create a PL/SQL block to retrieve and output the last name and department
ID of each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is
less than 115.
In the PL/SQL block, use a cursor FOR loop strategy instead of the OPEN / FETCH / CLOSE
cursor methods used in the previous practice.
1. In the declarative section:
Create two associative arrays. The unique key column for both arrays should be of the
BINARY INTEGER data type. One array holds the employees last name and the other
holds the department ID.
Declare a counter variable to be used in the executable section
Declare a cursor that selects the last name and department ID for employees whose ID
is less than 115
SET SERVEROUTPUT ON;
DECLARE
TYPE Table_Ename IS table of employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE Table_dept IS table of employees.department_id%TYPE
INDEX BY BINARY_INTEGER;
Tename Table_Ename;
Tdept Table_dept;
i BINARY_INTEGER :=0;
CURSOR Namedept IS SELECT last_name,department_id
FROM employees WHERE employee_id < 115;
2. In the executable section, use a cursor FOR loop (covered in the lesson titled Using Explicit
Cursors) to access the cursor values, assign them to the appropriate associative arrays,
and output those values from the arrays.
BEGIN
FOR emprec in Namedept
LOOP
i := i +1;
Tename(i) := emprec.last_name;
Tdept(i) := emprec.department_id;
DBMS_OUTPUT.PUT_LINE ('Employee: ' || Tename(i) ||
' is in department number: ' || Tdept(i));
END LOOP;
END;
/
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 7
The correct output should return 15 rows, similar to the following:
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 7: Working with Composite Data Types
Chapter 17 - Page 8
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 8: Using Explicit Cursors
Chapter 18 - Page 1
Additional Practices and
Solutions for Lesson 8: Using
Explicit Cursors
Chapter 18
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 8: Using Explicit Cursors
Chapter 18 - Page 2
Practices for Lesson 8
Practices Overview
Practices of this lesson are included in Practice 7.
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 9: Handling Exceptions
Chapter 19 - Page 1
Additional Practices and
Solutions for Lesson 9:
Handling Exceptions
Chapter 19
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 9: Handling Exceptions
Chapter 19 - Page 2
Practice 9-1: Handling Exceptions
For this exercise, you must first create a table to store some results. Run the lab_ap_09.sql
script that creates the table for you. The script looks like the following:
CREATE TABLE analysis
(ename Varchar2(20), years Number(2), sal Number(8,2)
);
In this practice, you write a PL/SQL block that handles an exception, as follows:
1. Declare variables for the employee last name, salary, and hire date. Use a substitution
variable for the employee last name. Then, query the EMPLOYEES table for the
last_name, salary, and hire_date of the specified employee.
2. If the employee has been with the organization for more than five years, and if that
employees salary is less than 3,500, raise an exception. In the exception handler, perform
the following:
Output the following information: employee last name and the message due for a
raise, similar to the following:
Insert the last name, years of service, and salary into the ANALYSIS table.
3. If there is no exception, output the employee last name and the message not due for a
raise, similar to the following:
4. Verify the results by querying the ANALYSIS table. Use the following test cases to test the
PL/SQL block.
LAST_NAME MESSAGE
Austin Not due for a raise
Nayer Due for a raise
Fripp Not due for a raise
Khoo Due for a raise
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 9: Handling Exceptions
Chapter 19 - Page 3
Solution 9-1: Handling Exceptions
For this exercise, you must first create a table to store some results. Run the lab_ap_09.sql
script that creates the table for you. The script looks similar to the following:
CREATE TABLE analysis
(ename Varchar2(20), years Number(2), sal Number(8,2)
);
In this practice, you write a PL/SQL block that handles an exception, as follows:
1. Declare variables for the employee last name, salary, and hire date. Use a substitution
variable for the employee last name. Then, query the EMPLOYEES table for the
last_name, salary, and hire_date of the specified employee.
2. If the employee has been with the organization for more than five years, and if that
employees salary is less than 3,500, raise an exception. In the exception handler, perform
the following:
Output the following information: employee last name and the message due for a
raise.
Insert the employee name, years of service, and salary into the ANALYSIS table.
3. If there is no exception, output the employee last name and the message not due for a
raise.
SET SERVEROUTPUT ON;
DECLARE
E_DUE_FOR_RAISE EXCEPTION;
V_HIREDATE EMPLOYEES.HIRE_DATE%TYPE;
V_ENAME EMPLOYEES.LAST_NAME%TYPE := INITCAP( '& B_ENAME');
V_SAL EMPLOYEES.SALARY%TYPE;
V_YEARS NUMBER(2);
BEGIN
SELECT SALARY,HIRE_DATE,MONTHS_BETWEEN(SYSDATE,hire_date)/12
YEARS
INTO V_SAL,V_HIREDATE,V_YEARS
FROM employees WHERE last_name = V_ENAME;
IF V_SAL < 3500 AND V_YEARS > 5 THEN
RAISE E_DUE_FOR_RAISE;
ELSE
DBMS_OUTPUT.PUT_LINE (V_ENAME|| ' not due for a
raise');
END IF;
EXCEPTION
WHEN E_DUE_FOR_RAISE THEN
BEGIN
DBMS_OUTPUT.PUT_LINE (V_ENAME || ' due for a raise');
INSERT INTO ANALYSIS(ENAME,YEARS,SAL)
VALUES (V_ENAME,V_YEARS,V_SAL);
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s
Copyright 2013, Oracle and/or its affiliates. All rights reserved.
Additional Practices and Solutions for Lesson 9: Handling Exceptions
Chapter 19 - Page 4
END;
END;
/
4. Verify the results by querying the ANALYSIS table. Use the following test cases to test the
PL/SQL block.
LAST_NAME MESSAGE
Austin Not due for a raise
Nayer Due for a raise
Fripp Not due for a raise
Khoo Due for a raise
SELECT * FROM analysis;
R
a
s
e
s
h
S
h
a
h
(
r
a
s
e
s
h
s
h
a
h
@
o
r
a
c
l
e
c
o
m
)
h
a
s
a
n
o
n
-
t
r
a
n
s
f
e
r
a
b
l
e
l
i
c
e
n
s
e
t
o
u
s
e
t
h
i
s
S
t
u
d
e
n
t
G
u
i
d
e
U
n
a
u
t
h
o
r
i
z
e
d
r
e
p
r
o
d
u
c
t
i
o
n
o
r
d
i
s
t
r
i
b
u
t
i
o
n
p
r
o
h
i
b
i
t
e
d
C
o
p
y
r
i
g
h
t
2
0
1
4
,
O
r
a
c
l
e
a
n
d
/
o
r
i
t
s
a
f
f
i
l
i
a
t
e
s