Oracle PL/SQL

By K.Prajakta
prajakta.karthikeyan@tcs.com
 PL/SQL
PL/SQLstands for Procedural Language/SQL
PL/SQLextends SQL by adding constructs
found in procedural languages, resulting in a
structural language that is more powerful
than SQL

PL/SQLcombines the data manipulating

power of SQL with the data processing
power of procedural languages.
 PL/SQL Architecture
The PL/SQLcompilation and run-time system is a
technology, not an independent product.

PL SQL Engine compiles and executes

PL/SQLblocks and subprograms

The engine can be installed in an Oracle server

or in an Oracle development tool such as Oracle
Forms or Oracle Reports
PL/SQL Architecture
 Execution of a PL/SQL
Block
Front-end OR Back-end Application portion Oracle Server

APPLICATION /
Pro*(C/ C++/ COBOL)/
Forms/ Reports

Embedded Embedded
PL/SQLBLOCK PL/SQLBLOCK
DECLARE DECLARE
Procedural Procedural PL/SQL
BEGIN BEGIN Statement
Procedural Procedural Executor
SQL SQL
Procedural Procedural
SQL SQL
END; END;

Host language
Host language
Host language SQL
Statement
Executor

!
 PL/SQL Block
PL/SQLcode is grouped into structure called blocks.

PL/SQLblocks come in three types

• Anonymous procedure
• Named procedure
• Named function.
 PL/SQL Blocks
An anonymous procedure is an unnamed procedure, it can't
be called. It is placed where it is to be run, normally attached
to a database trigger or application event.

A named procedure may be called, it may accept inbound

parameters but won't explicitly return any value.

A named function may also be called, it may accept inbound

parameters and will always return a value.
 PL/SQL Block
For Example
• DECLARE DECLARE
Definition of any x NUMBER;
variables or objects BEGIN
that are used within X := 15000;
the declared block. DBMS_OUTPUT.PUT_LINE
• BEGIN (‘The values for x is
’);
Statements that DBMS_OUTPUT.PUT_LINE
make up the block. (X);
• EXCEPTION EXCEPTION
All exception WHEN OTHERS THEN
handlers.
DBMS_OUTPUT.PUT_LINE
• END; (‘Error Occurred’);
End of block marker. END;
/
 Execution of Blocks
There are three basic ways to execute PL/SQL Code
1. Cut and paste from notepad

2. Execute a text file using SQL*PLUS

SQL> @c:\samples\test.sql
3. Use SQL*PLUS EDIT command
Types of Variables
PL/SQL Variables:
– Scalar (Char,Number,Varchar,Varchar2)
– Composite(PL-SQL Records)
– Reference (%Type,%RowType)
– LOB,CLOB,BFILE (large Objects) Ex to be
given
Types of Variables
• PL/SQL does not have input or output capability of its own

• You can reference substitution variables within a PL/SQL block

with a preceding ampersand (&)

• SQL*Plus host(or bind)variables can be used to pass run time

values out of the PL/SQL block back to the SQL*Plus
environment

• Non-PL/SQL Variables: Bind variables

!
@
Variable Declarations
Examples
• DECLARE
firstName, lastName CHAR(20); -- illegal
• DECLARE
Emp_No NUMBER NOT NULL := 10109;
Total_Sal NUMBER(3,1);
XYZ NUMBER(2,2) := 31.8;
Salary NUMBER(9,2) := XYZ * 131;
V_CHR2 VARCHAR2(12) :="JANUARY";
TODAY_DATE := SYSDATE;
TRUEFALSE BOOLEAN;
• DECLARE
PI CONSTANT NUMBER(9,3) := 3.142; --Constants
Declaration
• DECLARE
Rec1 Emp%ROWTYPE; Emp –Table Name
 Variable Assignment
• PL/SQL Expressions consist of Variables, Constants,
Literal, and Function Calls.
Syntax:
plsql_variable :=
plsq1_expression;
• Quick Notes :
:= (ASSIGNMENT) = (VALUE EQUALITY)
The datatype on the left and right side of an assignment
must be the same or implicitly convertible to each
other.
N := '7' -- legal
Column or table references are not allowed on either side
of the assignment operator (:=).
SCOTT.EMP.EMPNO := 1234;
 Variable Assignment
Examples
N1 := 5 * N2 * 0.7;
B1 := TRUE;
D1 := '11-JUN-97';
C1 := NULL;
N1 := SQRT( N2 ** 2 – 4 * N3 * N4);

Note:
Nn is Number type
Bn is Boolean
Cn is Char type
Dn is Datetype

!
Scope of Variables and
Constants
SCOPE refers to the visibility of identifiers at
different points in the PL /SQL block

RULES:
1. An identifier is visible in the block in which it is
declared and all its sub-blocks unless rule #2 applies.
– if an identifier declared in an enclosing block is re-
declared in a sub-block, the original identifier
declared in the enclosing block is no longer visible in
the sub-block However, the newly declared identifier
has the rules of scope defined in rule #1. Ex to be
Given
DBMS_OUTPUT.PUT_LINE
An Oracle-supplied packaged procedure
An alternative for displaying data from a PL/SQL block

Must be enabled in SQLPlus or iSQL*Plus with

SET SERVEROUTPUT ON
DECLARE
V_1 Number(30) := 20;
BEGIN
DBMS_OUTPUT.PUT_LINE (‘TEST RUN’ || V_1);
END;
/
 Special
Symbols/Operators
Symbol Meaning
+ Addition operator
- Subtraction
* Multiplication
/ Division
= Relational operator (compare)
@ Remote access indicator
; Statement terminator
<> Relational operator (compare)
!= Relational operator (compare)
|| Concatenation Operator
-- Single line comment
/* Beginning of comment (multi-line)
*/ End of comment (multi-line)
:= Assignment Operator
 Interacting with Oracle
Server
● SQL Data Manipulation Language statement support
INSERT, UPDATE, DELETE, SELECT

● Quick Notes:
The full Oracle syntax is supported for these statements.
A PL/SQL variable may be placed anywhere a construct

may be legally placed.

An identifier is first checked to see if it is a column in the

database. If not, it is assumed to be a PL/SQL identifier.

These statements may not appear as part of an expression
 SQL in PL / SQL
Example: INSERT
DECLARE
my_Sal NUMBER(7, 2) := 3040.55;
my_eName CHAR(10) := 'WANDA';
my_HireDate DATE := '08-Sep-88';
BEGIN
INSERT INTO Emp (empNo, eName, Job,hireDate, Sal,
deptNo)
VALUES (2741, my_eName, my_HireDate,
my_Sal, 20);
END;
 SQL in PL / SQL
Example: UPDATE
DECLARE
max _Allowed CONSTANT NUMBER:= 5000;
BEGIN
UPDATE Emp SET Sal = max_Allowed
WHERE empNo = 7934;
END;

!
 SQL in PL / SQL
Example: DELETE

BEGIN
DELETE FROM Emp
WHERE deptNo = 10 ;
END;
 SQL in PL / SQL
SELECT
Syntax
SELECT col1, col2,.,. into var1,var2
FROM table name
WHERE …

• Quick Notes:
– A SELECT statement is the only DML that returns
data. You must provide a location for this data to be
stored via the INTO clause.
– A SELECT...INTO statement must return exactly one
row. Zero or multiple returned rows results in an error.
– For multi-row SELECT's use cursors (discussed later).
 SQL in PL / SQL
Example: SELECT

DECLARE
part_Name Parts.name%TYPE;
num_In_Stock Parts.num%TYPE;
BEGIN
SELECT Name, Num INTO part_Name,
num_In_Stock
FROM Parts WHERE part_Id = 624;
--manipulate the retrieved data here--
END;
 SQL in PL / SQL
Functional support

• within a SQL Statement:

– Numeric (e.g. SQRT, ROUND, POWER)
– Character (e.g. LENGTH, UPPER)
– Date (e.g. ADD_MONTHS, MONTHS_BETWEEN)
– Group (e.g. AVG, MAX, COUNT)
INSERT INTO emp (ename) VALUES (UPPER(ename)
• outside of a SQL Statement:
x := SQRT(y);
lastname := UPPER(lastname);
agediff := MONTHS_ BETWEEN(bday1, bday2)/12;

Note: Most of the SQL functions are available except the group functions.
 Transaction Processing
• Definition
ROLLBACK
SAVEPOINT MARKER A
(ROLLBACK TO)
SAVEPOINT MARKER B
(ROLLBACK TO)
SAVEPOINT MARKER C
(ROLLBACK TO)
COMMIT
Syntax
SAVEPOINT < marker_name >;
ROLLBACK [WORK] TO [SAVEPOINT] < marker_name >;
Transaction Processing
E.G.
BEGIN
INSERT INTO temp(num,des)VALUES (1, 'row 1');
SAVEPOINT A;
… …

INSERT INTO temp (num,des) VALUES (2, 'row 2');

SAVEPOINT B;
… …

INSERT INTO temp (num,des) VALUES (3, 'row 3');

SAVEPOINT C;
… … … ...

ROLLBACK TO SAVEPOINT B;

COMMIT;
END;
 Control Structures
• Conditional: IF statements
• Iterative: LOOP and EXIT statements
• Sequential: GOTO and NULL statements

F
T F T

Condition Iteration Sequence

!
 Conditional Control
• Condition:
Logical Comparisons form the basis of conditional
control in PL/SQL; the results of these comparisons
are always either TRUE, FALSE or NULL.

• Quick Notes:
– Anything compared with NULL results in a NULL
value.
– A NULL in an expression evaluates to NULL(except
concatenation).
– E.g..
5 + NULL -- evaluates to NULL
'PL'||NULL||'/SQL' -- evaluates to PL/SQL

!
Conditional Control: IF
• To conditionally execute a statement or sequence of
statements.
Syntax
If <condition> THEN
<sequence of statements>
[ELSIF <condition> THEN
<sequence of statements>]
-- ELSIFs may be repeated
[ELSE
<sequence of statements>]
END IF;
!
Conditional Control:
DECLARE
IF
num_jobs NUMBER (7);
actor_id actor.actorid%type;
BEGIN
actor_id := &actor_id;
SELECT COUNT(*) INTO num_jobs FROM auditions
WHERE actorid = actor_id AND
called_back = 'YES';
IF num_jobs > 40 THEN
UPDATE actor SET actor_rating = 'OSCAR time'
WHERE actorid = actor_id;
ELSIF num_jobs > 20 THEN
UPDATE actor SET actor_rating = 'daytime
soaps' WHERE actorid = actor_id;
ELSE
UPDATE actor SET actor_rating = 'waiter' where
actorid = actor_id;
END IF;
COMMIT;
END; !
Conditional Control:
The NULL trap
IF
BLOCK 1: BLOCK 2:
If a>=b THEN If b>=a THEN
Do_this…; Do_that…;
ELSE ELSE
Do_that…; Do_this ...;
END IF ; ENDIF;

– Given any pair of non-NULL values for “a” and

“b”,will BLOCK 1 and BLOCK 2 do the same
THING?

– What if either “a” or “b” (or both) is NULL?

!
@
 Iterative Control
• Loops repeat a statement or sequence of statements
multiple times.

Four types of loops:

I. Simple loops
2. Numeric FOR Loops
3. WHILE Loops
4. Cursor FOR loops

!
 Iterative Control: LOOP
and EXIT
• Simple Loops repeat a sequence of statements multiple times.
Syntax
LOOP
<sequence of statements>
END LOOP; -- sometimes called an
'infinite' loop
• Exit Statements exit any type of loop immediately and program
control goes to the immediate statement after the loop
Syntax
EXIT WHEN <condition> ; -- 'infinite' loop insurance

!
Iterative Control: LOOP
and EXIT
DECLARE
ctr NUMBER(3) :=0;
BEGIN
LOOP
INSERT INTO temp(num,des)
VALUES (ctr,'ITERATION COMPLETE');
ctr := ctr+1;
IF ctr = 500 THEN
EXIT;
END IF;
END LOOP;
END; !
 Iterative Control:
• FOR
Numeric FOR Loops repeat a sequence of statements
a fixed number of times.
Syntax
FOR <index> IN
[ REVERSE ]<integer>..<integer> LOOP <sequence of
statements>
END LOOP;
Quick Notes - Index:
– The Loop Index takes on each value in the range, one
at a time, either in forward or reverse order
– It is implicitly of type NUMBER.
– It is only defined within the loop.
– Value may be referenced in an expression, but a new
value may not be assigned to it within the loop

!
Iterative Control: FOR
Example1
BEGIN
FOR I IN 1..500 LOOP
INSERT INTO temp (message)
VALUES ('I will not sleep in class.');
END LOOP;
END;

Example2
DECLARE
my_index CHAR(20) := 'Fettuam'
BEGIN
FOR my_index IN REVERSE 21...30 LOOP
/* redeclares my index */
INSERT INTO temp (des) VALUES (my_index);
/* inserts the numbers 30 through 21 */
END LOOP;
END;
!
@
 Iterative Control:
WHILE
• WHILE Loops repeat a sequence of statements until a specific
condition is no longer TRUE.
Syntax
WHILE <condition> LOOP
<sequence of statements>
END LOOP;

• Quick Notes:
– The term <condition> may be any PL/SQL condition (I.e., it
must return a BOOLEAN value of TRUE, FALSE, or
NULL).
– The sequence of statements will be repeated as long as
<condition> evaluates to TRUE.

!
 Iterative Control:
WHILE
DECLARE
ctr NUMBER (3) := 0;
BEGIN
WHILE ctr < 500 LOOP
INSERT INTO temp (message)
VALUES ('Well, might sleep just a little');
ctr := ctr + 1;
END LOOP;
END;

!
 CASE STATEMENT
CASE selector
WHEN expression1 THEN
result1
WHEN expression2 THEN
result2
...
WHEN expressionN THEN
resultN
[ELSE resultN+1] CASE
END; WHEN search_condition1 THEN
result1
WHEN search_condition2 THEN
result2
...
WHEN search_conditionN THEN
resultN
[ELSE resultN+1]
END;
 CASE EXAMPLE
DECLARE
• Just another step grade CHAR(1);
appraisal VARCHAR2(20);
towards writing BEGIN
...
cleaner, easier to appraisal :=
CASE grade
read and WHEN 'A' THEN
'Excellent'
maintain code Good'
WHEN 'B' THEN 'Very

WHEN 'C' THEN 'Good'

WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END;
...
END;

Case1.sql
 Sequential Control:
NULL
• It does nothing other than pass the control to the next
statement
Syntax
NULL;

• Quick Notes:
– NULL statement and the Boolean non-value NULL are
different
– Make use of NULL in the IF condition for doing
nothing in any branch of the condition

!
 Sequential Control:
NULL
Example1: Minimum PL/SQL block
BEGIN
NULL;
END;
Example2: Use in IF statement
BEGIN
IF <condition> THEN
… Statements …
ELSIF <condition> THEN
NULL; -- do nothing branch of the IF
statement
ELSE
… Statements …
ENDIF; !
 Sequential Control:
GOTO
• “GO TO” Statements jump to a different place in the PL/SQL
block .

Syntax
<<label_name>> x:=x + 1; -- a statement label GOTO
GOTO label_name; -- jumps to x := x + 1
“GO TO” Statements have 2 parts

• A statement with the label: where you want control to

come back using “GO TO” statement somewhere else in
the program

• A statement with “GO TO”: to make control jump to the

!
 Sequential Control:
• Quick Notes:
GOTO
You can only legally GOTO a statement that is either:
1. in the same sequence of statements as the GOTO statement
2 . In the sequence of statements that encloses the GOTO
statement (i.e an outer block)
BEGIN
<<Come_here>> X := X + 1;
... Statements …
GOTO Come_here;-- Now control will jump back
-- to the label Come_here
END;

!
Other Uses for Statement
Labels
• Labels may label any statement
• In addition to their use as targets for GOTO
statements, labels may be used for:
1.Blocks
2.Loops
• Labelling a block and loop allows referencing of
DECLARED objects that would otherwise not be
visible because of scope rules.
• Labelling a loop also allows control to exit in
outer loops
!
@
Other Uses for Statement
Labels
Example1:
BEGIN
<<sample>>
DECLARE
deptno NUMBER :=10;
BEGIN
DECLARE
deptno NUMBER := 20;
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE deptno = sample.deptno;
COMMIT;
END;
END sample;
END; !
Other Uses for Statement
Labels
Example2:

«compute_loop»
FOR i IN 1..100
LOOP
<statements>
DECLARE
I NUMBER := 0;
BEGIN
INSERT INTO temp (num,des)
VALUES (compute_loop.i, 'COMPLETE');
END ;
END LOOP compute_loop; -- must include loop name here

!
Other Uses for Statement
Labels
Example3:
«outer loop»
WHILE a >b LOOP
b:= b +1;
«inner loop»
WHILE b > c LOOP
C := C + 2;
EXIT outer_loop WHEN c > 200;
END LOOP inner loop;
END LOOP outer_loop;
!
User Defined Records
DECLARE
TYPE DeptRectyp IS
RECORD(deptno number(2),dname char(14),loc char(13));
dept_rec DeptRectyp;
..
BEGIN
SELECT deptno ,dname,loc into dept_rec FROM dept
WHERE deptno = 30;
..
END;

!
@
 PL/SQL Tables

• Are composed of two components:

– Primary Key of data type BINARY_INTEGER
– Column of scalar or record data type

• Can increase in size dynamically because they are

unconstrained

• PL/SQL tables are also called INDEX BY table

!
 PL/SQL Tables
• PL/SQL tables declared in two steps
Define table type
TYPE type_name
IS TABLE OF {column_type | variable%type |
table.column%column} [NOT NULL]
INDEX BY BINARY_INTEGER;

Declare PL/SQL table of that type

plsql_ table_name type_name;

• To reference rows in a PL/SQL table , specify

primary key value using the array like syntax
plsql_table_name(primary_key_value) ename_tab(3)
!
 PL/SQL Tables
• Assigning values to rows in PL/SQL
table
ename (2) := emp.ename

• Neither the primary key nor the

column can be named

!
 PL/SQL Tables
DECLARE
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;
sal_tab SalTabTyp;
I BINARY_INTEGER := 0;
...

(Continued on next page)

!
 PL/SQL Tables
BEGIN
--load employee name and salaries into PL/SQL tables
FOR emprec IN (SELECT ename,sal from emp) LOOP
I := I +1;
ename_tab(I) := emprec.ename;
sal_tab(I) := emprec.sal;
END LOOP;
-- process the tables
process_sals(ename_tab,sal_tab);

..
END;

!
 Cursor
• A work area to execute SQL statements and store
processing information.
• Every SQL DML statement processed by PL/SQL
has an associated CURSOR.
• Two Types of CURSORS
– 1. EXPLICIT
• Multiple row SELECT Statements

– 2. IMPLICIT
• All INSERT Statements
• All UPDATE Statements
• All DELETE Statements
• Single row SELECT....INTO Statements

!
@
 Explicit Cursor:
Use
• STEP 1. Declare the cursor
Syntax:
DECLARE
CURSOR <cursor name>
IS <regular select: statement>;

• Quick Notes:
– The <regular select statement> must NOT
include the INTO clause in a single-row
SELECT...INTO statement
– Declared cursors are scoped just like variables.

!
 Explicit Cursor:
Use
Example:
DECLARE
x NUMBER(7,2);
total NUMBER(5);
lower_sal_limit CONSTANT NUMBER(4) := 1200;
CURSOR c1 IS
SELECT ename FROM emp
WHERE sal > lower_ sal _limit;
BEGIN…

• STEP 2. Open the cursor

Syntax:
OPEN <cursor name>; -- excecutes the query and identifies the
result set consisting of all the rows that meet the query criteria.

!
@
 Explicit Cursor:
Use
• STEP 3. Fetch data from the cursor
Syntax:
FETCH <cursor name> into <var1, var2..>;
-- fetches values to the variable and increments the pointer to the
next value.

• Quick Notes:
– Retrieves one row of data from the cursor, and stores it
in the specified variables (similar to how a single-row
SELECT works).
– There must be exactly one INTO variable for each
column selected by the SELECT statement.
– The first column gets assigned to var1, the second
assigned var2, etc.

!
@
 Explicit Cursor: Use
• STEP 4. Close the cursor
Syntax:
CLOSE <cursor name>;
-- closes the cursor , and the result set becomes
unidentified

( NOTE:
Once a cursor is closed ,it can be
reopened . Any other operation on a closed
cursor raises predefined exception
INVALID_CURSOR.)

!
@
 Explicit Cursor:
Attributes
• %NOTFOUND
LOOP
FETCH my_cursor INTO my_ename, my_sal;
EXIT WHEN my_cursor%NOTFOUND;
--- process data here
END LOOP;

• %FOUND
FETCH my_cursor INTO my_ename, my_ sal;
WHILE my_cursor%FOUND LOOP;
-- process data here
FETCH my_cursor INTO my ename, my_sal;
END LOOP;

!
 Explicit Cursor: Attributes
• %ROWCOUNT
LOOP
FETCH my_cursor INTO my_ename, my_sal;
EXIT WHEN (my_cursor%NOTFOUND)
OR (my_cursor%ROWCOUNT>
10);
--process data here
END LOOP;

• %ISOPEN
IF my_cursor%ISOPEN THEN
FETCH my_cursor into my_ename,my_sal;
ELSE
OPEN my_cursor;
END IF;

!
@
 Explicit Cursor
Example: Reopening a cursor
DECLARE
total_bonus NUMBER(8,2) := 0;
min _sal emp.sal%TYPE := 1000;
-- start guessing
bonus_ amt emp . sal%TYPE;
CURSOR bonus IS SELECT sal *.10
FROM emp WHERE sal > min_ sal;

(Continued on next page)

!
@
 Explicit Cursor
Example: Reopening a cursor

BEGIN -- (Continued from previous page)

OPEN bonus; -- uses min_ sal value of 1000
LOOP
FETCH bonus INTO bonus_amt;
EXIT WHEN bonus%NOTFOUND;
total_bonus := total_bonus + bonus_amt;
IF total_bonus > 2000 THEN
/* up the minimum and try again */
min_sal :=min_sal + 500;
total_bonus := 0; -- reset the total
OPEN bonus; -- re-open with new min _sal
END IF;
END LOOP;
--you may want to store min_sal somewhere
CLOSE bonus;
END;

!
@
 Explicit
• Cursor FOR LoopsCursor:
specify aFOR loops
sequence of
statements to be repeated once for each row that is
returned by the cursor.
Syntax
FOR <record_name> IN <cursor_name> LOOP
-- statements to be repeated go
here
END LOOP;

• Numeric FOR Loop Similarities

– Specify a Set of rows from a table by using the cursor's
name v/s. Specifying a set of integers (i.e. 1..10)
– Index takes on the values of each row v/s index taking
on integer values (i.e. 1 through 10).
!
 Explicit
Conceptual CursorCursor:
Loop ModelFOR loops
LOOPS:
– When a cursor loop is initiated, an implicit OPEN
cursor_name is executed.

– For each row that satisfies the query associated with the
cursor, an implicit FETCH is executed into the
components of record_name.

– when there are no more rows left to FETCH, an

implicit CLOSE cursor_name is executed and the loop
is exited.

!
Explicit
DECLARE
Cursor: FOR
sal_limit NUMBER(4) := 0;
loops
total_sal NUMBER(9,2):=0;
CURSOR my_cursor IS SELECT ename,sal FROM
emp WHERE sal > sal_limit;
BEGIN
sal_limit := 1200;
FOR cursor_row IN my_cursor LOOP
INSERT INTO temp(message,num) VALUES
(cursor_row.ename, cursor_row.sal);
total_sal := total_sal +
cursor_row.sal;
END LOOP;
COMMIT;
END;

!
@
 Implicit
• An Implicit Cursor Cursorassociated
is automatically
with any SQL DML statement that does not have
an explicit cursor associated with it.
– INSERT, UPDATE , DELETE, SELECT...INTO

• Quick Notes:
– Implicit cursor is called the "SQL'' cursor - it stores
information concerning the processing of the last SQL
statement not associated with an explicit cursor
– OPEN, FETCH, and CLOSE don't apply.
– All cursor attributes apply.

!
@
• SQL %NOTFOUND
Implicit Cursor: Attributes
UPDATE emp SET sal = sal*10.0 WHERE ename = 'WARD';
IF SQL%NOTFOUND THEN -- WARD wasn't found
INSERT INTO emp(empno, ename, sal)
VALUES (4, 'WARD', 9999);
END IF;

• SQL %FOUND

• SQL%ROWCOUNT
DELETE FROM baseball_team WHERE batting_avg< . 100;

IF SQL%ROWCOUNT > 5 THEN

INSERT INTO temp (message)
VALUES ('Your team needs help.')
END IF;

• SQL%ISOPEN
(always evaluates to FALSE) !
@
BREAK