Transcript Document

PL SQL Block Structures
What is PL SQL
• A good way to get acquainted with PL/SQL
is to look at a sample program.
• PL/SQL combines the data manipulating
power of SQL with the data processing
power of procedural languages.
Block Structure
• PL/SQL is a block-structured language.
• The basic units (procedures, functions,
and anonymous blocks) that make up a
PL/SQL program are logical blocks, which
can contain any number of nested subblocks.
Logical Blocks
• Each logical block corresponds to a
problem or subproblem to be solved.
• A block (or sub-block) lets you group
logically related declarations and
statements.
• The declarations are local to the block and
cease to exist when the block completes.
Declarative
• First comes the declarative part, in which
items can be declared.
• Once declared, items can be manipulated
in the executable part.
Nesting
• You can nest sub-blocks in the executable
and exception-handling parts of a PL/SQL
block or subprogram but NOT in the
declarative part.
Image
Variables and Constants
• PL/SQL lets you declare constants and
variables, then use them in SQL and
procedural statements anywhere an
expression can be used.
SQL Datatypes
• Variables can have any SQL datatype,
such as CHAR, DATE, or NUMBER, or
any PL/SQL datatype, such as BOOLEAN
or BINARY_INTEGER
Assigning Values
• You can assign values to a variable in
three ways.
• The first way uses the assignment
operator (:=), a colon followed by an equal
sign.
• Examples:
tax := price * tax_rate;
valid_id := FALSE;
2nd Way to Assign Values
• The second way to assign values to a
variable is by selecting (or fetching)
database values into it.
• Example: (Compute a 10% bonus when
you select the salary of an employee. )
SELECT sal * 0.10 INTO bonus FROM emp
WHERE empno = emp_id;
3rd Way to Assign a Value
• The third way to assign values to a variable is by
passing it as an OUT or IN OUT parameter to a
subprogram.
• Example:
DECLARE my_sal REAL(7,2); PROCEDURE
adjust_salary (emp_id INT, salary IN OUT
REAL) IS ... BEGIN SELECT AVG(sal) INTO
my_sal FROM emp; adjust_salary(7788,
my_sal); -- assigns a new value to my_sal
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.
Example:
credit_limit CONSTANT REAL := 5000.00;
Cursors
• Oracle uses work areas to execute SQL
statements and store processing
information.
2 types of Cursors
• A PL/SQL construct called a cursor lets
you name a work area and access its
stored information. There are two kinds of
cursors: implicit and explicit.
Implicit Cursor
• PL/SQL implicitly declares a cursor for all
SQL data manipulation statements,
including queries that return only one row.
Explicit Cursor
• 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;
Cursor For Loops
• A cursor FOR loop implicitly declares its
loop index as a record that represents a
row fetched from the database.
DECLARE CURSOR c1 IS SELECT ename,
sal, hiredate, deptno FROM emp;
Fetch
• Next, it opens a cursor, repeatedly fetches
rows of values from the result set into
fields in the record.
BEGIN FOR emp_rec IN c1 LOOP ...
salary_total := salary_total + emp_rec.sal;
Closes Loop
• Then closes the cursor when all rows have
been processed.
END LOOP;
Full Example of Cursor For Loop
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;
Cursor Variable
• A cursor variable points to the current row in the
result set of a multi-row query.
• 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.
Example for Cursor Variable
•
The following procedure opens the cursor variable generic_cv for the chosen
query:
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice
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;
• The program below processes an order for
a tennis racket.
DECLARE
• First, it declares a variable of type
NUMBER to store the quantity of tennis
rackets on hand.
DECLARE qty_on_hand NUMBER(5);
• Then, it retrieves the quantity on hand
from a database table named inventory.
BEGIN SELECT quantity INTO qty_on_hand
FROM inventory WHERE product =
'TENNIS RACKET' FOR UPDATE OF
quantity;
(condition) IF THEN
• If the quantity is greater than zero, the program
updates the table and inserts a purchase record
into another table named purchase_record.
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
• Otherwise, the program inserts an out-ofstock record into the purchase_record
table.
ELSE INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF; COMMIT; END;