Oracle 8 Training
PL/SQL
PL/SQL
What is PL/SQL?
PL/SQL stands for "Procedural Language extensions to
SQL”. PL/SQL look very similar to the SQL language
but it adds the programming constructs similar to
other languages.
PL/SQL Block
DECLARE
/* Declarative section: variables, types, and local
subprograms. */
BEGIN
/* Executable section: procedural and SQL statements
go here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements
go here. */
END;
PL/SQL Engine
Language constructs in PL/SQL
• Variables and constants
• Cursors
• Exceptions
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance
transaction processing language that offers the
following advantages:
support for SQL
support for object-oriented programming
better performance
higher productivity
full portability
tight integration with Oracle
security
Fundamentals - Comments
PL/SQL is case insensitive, that is uppercase is treated
the same as lowercase.
We have two ways to comment code.
1a. We can use the double '-' to comment a single line
of code. Everything after the -- is ignored until the
end of the line.
e.g..
-- Function to calculate sales commission.
IF sales > 0 THEN
commission := sales * 0.1;
END IF;
Fundamentals - Comments
We have two ways to comment code.
1b. We can also place our comment on a line that
contains code to be executed.
e.g..
IF sales > 0 THEN
commission := sales * 0.1; -- The % commission is
hard coded to 10 percent.
END IF;
Fundamentals - Comments
2. Now when we want to comment multiple lines within
PL/SQL we can use the following characters /* */.
Everything between these characters is ignored and
treated as a comment.
e.g.
/* Procedure: update_balance
Author: CTS
Description: Test Procedure
*/
Identifiers
An identifier is the name for a PL/SQL object. Objects
e.g. : Variables,Constants
PL/SQL identifiers must start with a letter, cannot
contain spaces, may contain the following characters
$, _, and #. The maximum length of an identifier is 30
characters.
Reserved Words
Naming variables: Avoid the following : BEGIN, END, IF,
ELSE, END IF, WHILE, LOOP, OPEN, CLOSE,
FETCH
Literal: A literal is a value which is not represented by an
identifier. A literal con be composed of numbers eg.
123, 98 34.56 , Strings eg. 'Hello World’ and Boolean
values eg. TRUE and FALSE.
The Statement Delimiter
Every PL/SQL program consists of many statements.
Our statements are terminated with the ';' character.
An end of line is not the end of a statement, look at
our IF-THEN statement as an example.
eg.
IF sales > 0 THEN
commission := sales * 0.1;
END IF;
The Statement Delimiter
Our first semicolon terminates the single statement
commission := sales * 0.1 and the second semicolon
terminated the IF-THEN-END-IF statement. The
same piece of code could be written like this
e.g.
IF sales > 0 THEN commission := sales * 0.1; END IF;
Functionally the two statements are identical, the first is
much more preferred for readability.
Declaring Variables
A constant is a special kind of variable, it has a datatype
and its value is set at creation.
When choosing a variable name…
1. choose one that resembles as close as possible the
purpose of the variable
2. always try to use a readable name.
PL/SQL allows the use of the underscore character '_'
so it is very easy to create readable names.
Declaring Variables
All constants and variables contain a datatype. It is this
datatype that determined the storage format, the
restrictions on how the variable can be used and
what values the variable may contain.
A scalar datatype is atomic, this means that it is not
made up of other datatypes.
Scalar datatypes belong to one of four types: Number,
Character, Boolean and Date datatypes.
Initialized to Null
Variable Declarations
Before you can reference a variable, it must be first
defined. When you declare the variable PL/SQL
allocates memory to the storage of the variable. The
syntax for the declaration of variables is as follows.
variable_name datatype [optional assignment];
eg.
Age_of_student number := 16;
Variable Declarations
Our declarations can be bounded or unbounded.
This means we can specify the magnitude of the value
that can be assigned to the variable. Our Number
variable supports up to 38 digits,
if unbounded PL/SQL will allocate all the memory that is
required to store up 38 digits.
If we bound the variable by using number(2), then
PL/SQL will only allocate the memory to store the two
digits.
Anchored Declarations
Anchoring variables refers to the using of the %TYPE
declaration.
What PL/SQL is doing is anchoring the datatype of one
variable to that of another data structure, generally
that of a column of a table.
The benefit of anchoring datatypes of variables in
PL/SQL programs with database columns is when the
underlying declarations change, the PL/SQL code
does not require modifications.
e.g.FirstName employee.fname%type;
Tips for using Variables
• Always use clear names and try to establish a
naming convention for different types of variables.
• Avoid recycling variables. Read this statements as
NEVER recycle variables. Reusing variable names
within the sample PL/SQL program can cause more
problems than you could ever imagine. Just try to
debug one some day!.
• Use named constants wherever possible and avoid
the use of hard coded values.
Tips for using Variables
• Remove unused variables from your PL/SQL
programs. Most PL/SQL programs evolve, in many
cases, over several years and many versions. When
variables are no longer used, remove them from the
program. It will make the code much easier to
understand.
• If the variable represents a database column then
anchor the variable to the database column using the
%type declaration.
Variable declaration
Declare
Name
varchar2(25);
Age
number(2);
Salary
Number(7,2):=3500;
Empno
number(4) NOT NULL:=7869;
DOB
date;
Stock
boolean;
Avail
boolean:=false;
Constant declaration
Declare
Credit_limit Constant real:=5000.00;
Control Structures
• They are normally used to update,delete or
update batches of data
• The structures are common to any 3GL :
IF=THEN-ELSE, FOR,WHILE, GOTO,
EXIT WHEN.
Conditional Controls
The format of the IF-THEN statement is as follows
IF <condition> THEN
executable statements;
END IF;
The value of the condition is evaluated, if TRUE then
the executable statements are processed. If the
condition is FALSE or NULL then the statements are
skipped.
Conditional Controls
The format of the IF-ELSIF statement is as follows.
IF <condition1> THEN
<statements1>
ELSIF <condition2> THEN
<statements2>
ELSIF <conditionN> THEN
<statementsN>
END IF;
The IF-ELSIF statement can be considered a multiple IF
statements where only one condition can be TRUE.
Conditional Controls
Nested IF Statements
It is possible to nest any IF statements within another IF
statement. If your level of nesting is more that three
levels you should really look at reviewing the logic of
your code.
Conditional Control
DECLARE
v_number_seats rooms.number_seats%TYPE;
v_comment VARCHAR2(35);
BEGIN
SELECT
number_seats
FROM
rooms
WHERE
room_id = 9000;
IF
v_number_seats < 50
THEN
v_comment := 'Small';
ELSIF
v_number_seats < 100
THEN
v_comment := 'Big';
ELSE v_comment := 'Very Big';
END IF;
END;
Loops
Syntax:
LOOP
<loop_body> /* A list of statements. */
END LOOP;
At least one of the statements in <loop_body> should
be an EXIT statement of the form
Syntax :
EXIT WHEN <condition>;
Simple Loops
DECLARE v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table VALUES (v_Counter,'Loop
Index');
v_Counter := v_Counter + 1;
EXIT WHEN v_Counter > 50;
END LOOP;
END;
More Loops
EXIT by itself is an unconditional loop break. Use it
inside a conditional if you like.
A WHILE loop can be formed with
WHILE <condition> LOOP
<loop_body>
END LOOP;
While Loop
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
WHILE v_Counter <= 50
LOOP
INSERT INTO temp_table VALUES (v_Counter,'Loop’);
v_Counter := v_Counter + 1;
END LOOP;
END;
More Loops
A simple FOR loop can be formed with:
FOR <var> IN <start>..<finish> LOOP
<loop_body>
END LOOP;
Here, <var> can be any variable; it is local to the forloop and need not be declared. Also, <start> and
<finish> are constants.
For Loop
BEGIN
FOR v_Counter IN 1..50 LOOP
INSERT INTO temp_table
values (v_Counter, 'Loop Index');
END LOOP;
END;
BEGIN
FOR v_Counter IN REVERSE 10..50 LOOP
INSERT INTO temp_table
values (v_Counter,'Loop Index');
END LOOP;
END;
For Loop
•
•
•
•
Break
Continue
Sequential controls using labels <<label>>
And goto label
PL/SQL Attributes
• %TYPE
• %ROWTYPE
%TYPE
This attrribute provides the datatype of a variable or
database column.
Declare
Name
emp.ename%TYPE;
Age
number(2);
Myage
age%TYPE;
If the datatype of ename in the table changes, the
datatype of name changes accordingly at run time
%ROWTYPE
This attribute provides a record type that represents a
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
Declare
dept_rec dept%ROWTYPE;
To reference the fields,
my_deptno:=dept_rec.deptno;
Cursors
When you execute an SQL statement in PL/SQL Oracle
creates a private work area for the statement. This is
where data that is returned from the SQL statement is
stored. The cursor name is basically a pointer to this
area.
Now we will create a cursor.
CURSOR c_customers is
SELECT * from CUSTOMERS;
Cursors
We can open the cursor.
OPEN c_customers;
We can select data from the cursor.
FETCH c_customers into customers_rec;
And we can close the cursor.
CLOSE c_customers;
When we select values from a cursor we are actually
selecting the data from a virtual table defined by the
cursor definition.
Implicit & Explicit Cursors
Every SQL data manipulation statements including
queries that return only one row is an implicit cursor.
An explicit cursor is what we create. For queries that
return more than one row, you must declare an
explicit cursor
Note: In your PL/SQL, always use explicit cursors.
Diff between implicit and explicit
• In order to process an SQL statement,Oracle allocates an area of
memory known as the context area.
• This area contains information necessary to complete the processing,
including the number of rows processed by a statement, a pointer to the
parsed representation of the statement, and in the case of query an
active set , which is the set of rows returned by the query.
•
A cursor is a handle,or pointer to the context area .Through the
cursor, a PL/SQL program can control the context area and what
happens to it as the statement is processed. An explicit cursor is when a
cursor name is explicitly assigned to a select statement via the
CURSOR..IS. An implicit cursor is used for all other SQL statements
DECLARE v_StudentID students.id%TYPE;
v_FirtsName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_Major students.major%TYPE := 'Computer Science';
CURSOR c_Students IS
SELECT id,first_name, last_name
FROM students WHERE major = v_Major;
BEGIN
OPEN c_Students
LOOP
FETCH c_Students INTO
v_StudentID, v_FirstName,v_LastName;
EXIT WHEN c_Students%NOTFOUND;
END LOOP;
CLOSE c_Students;
END;
Cursor Attributes
The SQL cursor attributes are :• %ROWCOUNT: The number of rows processed by a
SQL statement.
• %FOUND : TRUE if at least one row was processed.
•
%NOTFOUND : TRUE if no rows were processed.
•
%ISOPEN : TRUE if cursor is open or FALSE if
cursor has not been opened or has been closed.
Only used with explicit cursors.
Cursor Attributes
• IMPLICIT cursors are known as SQL
cursor , thus if you wish to use the
attributes from an implicit cursor (which in
other words is EVERY SELECT statement
that returns more than one row) you use
SQL%NOTFOUND,
SQL%ROWCOUNT...etc,etc...
Use a PL/SQL record variable
DECLARE
CURSOR c_AllStudents IS
SELECT * from students;
v_StudentInfo
c_AllStudents%ROWTYPE;
BEGIN
....
END;
Cursor FOR loop
DECLARE CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major='History';
BEGIN --Begin Loop the cursor is implicitly
FOR v_StudentData in c_HistoryStudents LOOP
--An implicit fecth INSERT INTO registered_students (student_id,
department,course) VALUES ( v_StudentData.ID,'HIS',301);
INSERT INTO temp_table (num_col,char_col) VALUES (v_StudentData.ID,
v_StudentData.first_name || ' '|| v_StudentData.last_name);
END LOOP;
COMMIT;
END;
Advanced Explicit Cursor
• Concepts
cursor that uses parameters
CURSOR c_students
(p_Department
classes.department%TYPE
p_Course classes.department%TYPE
) IS
SELECT * FROM classes
WHERE department =
p_Department
AND course = p_Course;
To call the cursor
OPEN c_students('CS',101);
Cursors for update
The syntax for this parameter in the SELECT
statement is:
SELECT ... FROM ... FOR UPDATE [OF
column_reference] [NOWAIT]
where column_reference is a column in
the table against which the query
is performed. A list of columns
can also be used.
Example…for update
DECLARE
CURSOR c_AllStudents IS
SELECT *
FROM students
FOR UPDATE OF first_name, last_name;
Or the cursor can select every column by not specifing a range
DECALRE
CURSOR c_AllStudents IS
SELECT *
FROM students
FOR UPDATE;
NOWAIT
If another session already has locks on the rows in the
active set, then the SELECT FOR UPDATE will hang
until the other session releases the lock.
To handle this situation the parameter NOWAIT is
available, which in case the rows are locked,OPEN will
return the error ORA-54 resource busy and
acquire with NOWAIT specified
Fetching Across COMMITS
• Note that the COMMIT statement is done after the fetch loop is
complete.
• This is done because COMMIT will release any locks held by the
session. Since the FOR UPDATE clause acquires locks, these will be
released by the COMMIT.
• When this happens, the cursor is invalidated.Any subsequent fetches
will return the Oracle error: ORA-1002 : fetch out of sequenece .
• if there is a COMMIT inside a SELECT FOR UPDATE fetch loop,
any fetches done after the COMMIT will fail.
• it is not advisable to use a COMMIT inside the LOOP. If the cursor is
not defined with a SELECT FOR UPDATE then there is no problem.
Descargar

Oracle 8 Training