Transcript PL/SQL Introduction

Department of Computer and Information Science,
School of Science, IUPUI
PL/SQL Introduction
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
PL/SQL Introduction
The limits of my language mean the limits of my world.
- Ludwig Wittgenstein
PL/SQL (Procedural Language / SQL) extends SQL by adding programming constructs:
variables
types
control structures (if, loop)
Subprograms
PL/SQL Block:
The basic unit of PL/SQL is a block
Blocks can be nested.
Typically each block performs a logical unit of work.
Block structure is (boldfaced part required):
DECLARE
declaration section
BEGIN
executable section
EXCEPTION
error handling section
END;
Dale Roberts
PL/SQL Introduction
Block Sections:
Declaration Section: Defines and initializes the variables and cursors used in the
block.
Executable Section: Uses flow control commands to execute the commands and
assign values to the declared variables.
Exception Handler Section: Customized error handling.
PL/SQL Block Types:
Anonymous Blocks
not stored in Oracle
executed only once
Named Blocks
an anonymous block with a name.
usually executed once.
Subprograms
procedures, functions or packages
These blocks are stored in Oracle and executed many times.
Explicit execution.
Triggers
Stored in Oracle.
Implicit execution when a triggering event happens.
Dale Roberts
PL/SQL Example
A good way to get acquainted with PL/SQL is to look
at a sample program. The program below
processes an order for tennis rackets.
• First, it declares a variable of type NUMBER to
store the quantity of tennis rackets on hand.
• Then, it retrieves the quantity on hand from a
database table named inventory.
• If the quantity is greater than zero, the program
updates the table and inserts a purchase record into
another table named purchase_record.
• Otherwise, the program inserts an out-of-stock
record into the purchase_record table.
Dale Roberts
PL/SQL Example
/**************************************/
-- Example of an anonymous PL/SQL block.
/**************************************/
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand
FROM inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory
SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record VALUES
('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record VALUES
('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;
Dale Roberts
PL/SQL Variables
Variables and Constants
PL/SQL allows you to declare constants and variables, then use them in SQL
and procedural statements anywhere an expression can be used.
Forward references are not allowed -- you must declare a constant or
variable before referencing it in other statements, including other declarative
statements.
Declaring Variables
Variables can have any SQL datatype, such as CHAR, DATE, and NUMBER,
or any PL/SQL datatype, such as BOOLEAN and BINARY_INTEGER.
Example: you want to declare a variable named part_no to hold 4-digit
numbers and a variable named in_stock to hold the Boolean value TRUE or
FALSE. You declare these variables as follows:
part_no NUMBER(4);
in_stock BOOLEAN;
You can also declare records and PL/SQL tables using the RECORD and
TABLE composite datatypes.
Dale Roberts
PL/SQL Variables
•
•
Assigning Values to a Variable
– You can assign values to a variable in two ways. The first way uses the
assignment operator (:=). Some examples follow:
tax := price * tax_rate;
bonus := current_salary * 0.10;
amount := TO_NUMBER(SUBSTR('$750', 2, 3));
valid := FALSE;
– A second way to assign values to a variable is to select or fetch database
values into it. In the following example, you have Oracle compute a 10%
bonus when you select the salary of an employee:
SELECT sal * 0.10 INTO bonus
FROM emp WHERE empno = emp_id;
Then, you can use the variable bonus in another computation or insert its
value into a database table.
Declaring Constants
– Declaring a constant is like declaring a variable except that you must add
the keyword CONSTANT and immediately assign a value to the constant.
– Thereafter, no more assignments to the constant are allowed.
– In the following example, you declare a constant named credit_limit:
credit_limit CONSTANT REAL := 5000.00;
Dale Roberts
Control Structures
Conditional Control (IF-THEN-ELSE)
The IF-THEN-ELSE statement lets
you execute a sequence of
statements conditionally. The IF
clause checks a condition; the THEN
clause defines what to do if the
condition is true; the ELSE clause
defines what to do if the condition is
false or null.
Consider the program below, which
processes a bank transaction.
Before allowing you to withdraw
$500 from account 3, it makes sure
the account has sufficient funds to
cover the withdrawal. If the funds are
available, the program debits the
account; otherwise, the program
inserts a record into an audit table.
DECLARE
acct_balance NUMBER(11,2);
acct
CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) :=
500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = acct
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- insert account, current balance, and message
END IF;
COMMIT;
END;
Dale Roberts
Control Structures
Iterative Control (LOOP)
LOOP statements let you execute a sequence of statements multiple times. You place the keyword
LOOP before the first statement in the sequence and the keywords END LOOP after the last
statement in the sequence. For example:
LOOP
EXIT WHEN i > 100;
END LOOP;
Iterative Control (FOR-LOOP)
The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements
once for each integer in the range. For example:
FOR i IN 1..order_qty LOOP
UPDATE sales SET custno = customer_id
WHERE serial_num = serial_num_seq.NEXTVAL;
END LOOP;
Iterative Control (WHILE-LOOP)
The WHILE-LOOP statement associates a condition with a sequence of statements. Before each
iteration of the loop, the condition is evaluated. If TRUE, loop continues. If FALSE the loop ends
and control passes to the next statement.
For example:
WHILE salary < 4000 LOOP
SELECT sal, mgr, ename
INTO salary, mgr_num, last_name
FROM emp WHERE empno = mgr_num;
END LOOP;
Dale Roberts
Control Structures
Sequential Control
The GOTO statement lets you branch to a label unconditionally. The label, an
undeclared identifierenclosed by double angle brackets, must precede an
executable statement or a PL/SQL block.
POOR PROGRAMMING PRACTICE! Avoid using GOTO!!
Example:
IF rating > 90 THEN
GOTO calc_raise; -- branch to label
END IF;
...
<<calc_raise>>
IF job_title = 'SALESMAN' THEN -- control resumes here
amount := commission * 0.25;
ELSE
amount := salary * 0.10;
END IF;
Dale Roberts
PL/SQL Cursors
Cursors
Oracle uses work areas (cursors) to execute SQL statements and store processing
information.
There are two kinds of cursors:
• Implicit Cursors: PL/SQL implicitly declares a cursor for all SQL data manipulation
statements, including queries that return only one row.
• Explicit Cursors: For queries that return more than one row, you can explicitly declare a
cursor to process the rows individually. Example:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job
FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. An explicit cursor
"points" to the current row in the result set. This allows your program to process the
rows one at a time.
Dale Roberts
PL/SQL Cursors
Manipulating Cursors
You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN statement
executes the query associated with the cursor, identifies the result set, and positions the cursor
before the first row. The FETCH statement retrieves the current row and advances the cursor to the
next row. When the last row has been processed, the CLOSE statement disables the cursor.
Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR
loop instead of the OPEN, FETCH, and CLOSE statements.
A cursor FOR loop implicitly declares its loop index as a record that represents a row in a database
table, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record,
then closes the cursor when all rows have been processed. In the following example, the cursor
FOR loop implicitly declares emp_rec as a record:
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;
END;
You use dot notation to reference individual fields in the record.
Dale Roberts
PL/SQL Cursors
Cursor Variables
Like a cursor, a cursor variable points to the current row in the result set of a multi-row
query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query.
It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you
can assign new values and which you can pass to subprograms stored in an Oracle
database. This gives you more flexibility and a convenient way to centralize data retrieval.
Typically, you open a cursor variable by passing it to a stored procedure that declares a
cursor variable as one of its formal parameters. The following packaged procedure opens
the cursor variable generic_cv for the chosen query:
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
choice IN NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END open_cv;
END emp_data;
Dale Roberts
Attributes
Attributes
PL/SQL variables and cursors have attributes, which are properties that let you reference the
datatype and structure of an object without repeating its definition. Database columns and tables
have similar attributes, which you can use to ease maintenance.
%TYPE
The %TYPE attribute provides the datatype of a variable or database column. Example:
my_title books.title%TYPE;
Declaring my_title with %TYPE has two advantages. First, you need not know the exact datatype of
title. Second, if you change the database definition of title, the datatype of my_title changes
accordingly at run time.
%ROWTYPE
In PL/SQL, records are used to group data. The %ROWTYPE attribute provides a record type that
represents a row in a table. The record can store an entire row of data selected from the table or
fetched from a cursor or cursor variable.
Columns in a row and corresponding fields in a record have the same names and datatypes.
Example:
DECLARE
dept_rec dept%ROWTYPE; -- declare record var.
You use dot notation to reference fields.
Example:
my_deptno := dept_rec.deptno;
Dale Roberts
Attributes
If you declare a cursor that retrieves the last name,
salary, hire date, and job title of an employee, you can
use %ROWTYPE to declare a record that stores the
same information, as follows:
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE; -- record variable that
-- represents a row in the emp table
When you execute the statement
FETCH c1 INTO emp_rec;
the value in the ename column of the emp table is
assigned to the ename field of emp_rec, the value in the
sal column is assigned to the sal field, and so on.
Dale Roberts
PL/SQL Tables and Record Data Types
PL/SQL Tables
Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each
element has a unique index number that determines its position in the ordered
collection.
PL/SQL tables are unbounded (unlike an array) and can be allocated dynamically.
PL/SQL tables do not require consecutive indexnumbers.
You can use a cursor FOR loop to fetch an entire column or table of Oracle data into a
PL/SQL table. In the following example, you fetch a table of data into the PL/SQL table
dept_tab:
DECLARE
TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabTyp;
n BINARY_INTEGER := 0;
BEGIN
FOR dept_rec IN (SELECT * FROM dept) LOOP
n := n + 1;
dept_tab(n) := dept_rec;
END LOOP;
...
END;
Dale Roberts
PL/SQL Tables and Record Data Types
User-Defined Records
You can use the %ROWTYPE attribute to declare a record that represents a
row in a table or a row fetched from a cursor. But, with a user-defined record,
you can declare fields of your own.
Records contain uniquely named fields, which can have different datatypes.
Suppose you have various data about an employee such as name, salary,
and hire date. These items are dissimilar in type but logically related. A
record containing a field for each item lets you treat the data as a logical unit.
Example:
DECLARE
TYPE TimeTyp IS RECORD (minute SMALLINT, hour SMALLINT);
TYPE MeetingTyp IS RECORD (
day
DATE,
time
TimeTyp, -- nested record
place
VARCHAR2(20),
purpose VARCHAR2(50));
Notice that you can nest records. That is, a record can be the component of
another record.
Dale Roberts
Procedures and Packages
Modularity
Modularity lets you break an application down into manageable, well-defined logic
modules. Besides blocks and subprograms, PL/SQL provides the package, which
allows you to group related program objects into larger units.
Subprograms
PL/SQL has two types of subprograms called procedures and functions, which can
take parameters and be invoked (called). A subprogram is like a miniature program,
beginning with a header followed by an optional declarative part, an executable part,
and an optional exception-handling part.
Packages
PL/SQL lets you bundle logically related types, program objects, and subprograms into
a package. Each package is easy to understand and the interfaces between packages
are simple, clear, and well defined. This aids application development.
Packages usually have two parts: a specification and a body. The specification is the
interface to your applications; it declares the types, constants, variables, exceptions,
cursors, and subprograms available for use. The body defines cursors and
subprograms and so implements the specification.
Only the declarations in the package specification are visible and accessible to
applications. Implementation details in the package body are hidden and inaccessible.
Packages can be compiled and stored in an Oracle database, .... When you call a
packaged subprogram for the first time, the whole package is loaded into memory.
Dale Roberts
Error Handling
Error Handling
PL/SQL makes it easy to detect and process predefined
and user-defined error conditions called exceptions.
When an error occurs, an exception is raised. Normal
execution stops and control transfers to the exceptionhandling part of your PL/SQL block or subprogram. To
handle raised exceptions, you write separate routines
called exception handlers.
Predefined exceptions are raised implicitly by the runtime
system.
You must raise user-defined exceptions explicitly with the
RAISE statement.
Dale Roberts
Error Handling
You can define exceptions of your own in the declarative part of any PL/SQL block or
subprogram. In the executable part, you check for the condition that needs special
attention. If you find that the condition exists, you execute a RAISE statement.
Example:
DECLARE
salary
NUMBER(7,2);
commission
NUMBER(7,2);
comm_missing EXCEPTION; -- declare exception
BEGIN
SELECT sal, comm INTO salary, commission FROM emp
WHERE empno = :emp_id;
IF commission IS NULL THEN
RAISE comm_missing; -- raise exception
ELSE
:bonus := (salary * 0.05) + (commission * 0.15);
END IF;
EXCEPTION -- begin exception handlers
WHEN comm_missing THEN
-- process error
END;
Dale Roberts
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
Dale Roberts