INTRODUCTION
CHARACTERSTICS
Ø Highly structured,
readable and accessible language.
Ø Standard and Protable
language.
Ø Embedded language.
Ø Improved execution
authority.
10g FEATURES
Ø Optimized compiler
.
To change the optimizer
settings for the entire database, set the database parameter PLSQL_OPTIMIZE_LEVEL. Valid settings are as
follows
0 - No
optimization
1 - Moderate
optimization
2 - Aggressive
optimization
These settings are also
modifiable for the current session.
SQL> alter session set
plsql_optimze_level=2;
Oracle retains optimizer
settings on a module-by-module basis. When you recompile a particular module
with nondefault settings, the settings will stick allowing you to recompile
later on using REUSE SETTINGS.
SQL> Alter procedure proc
compile plsql_optimize_level=1;
SQL> Alter procedure proc
compile reuse settings;
Ø Compile-time warnings.
Starting with oracle
database 10g release 1 you can enable additional compile-time warnings to help
make your programs more robust. The compiler can detect potential runtime
problems with your code, such as identifying lines of code that will never be
run. This process, also known as lint
checking.
To enable these warnings
fo the entire database, set the database parameter PLSQL_WARNINGS. These settings are also
modifiable for the current session.
SQL> alter session set
plsql_warnings = ‘enable:all’;
The above can be achieved
using the built-in package DBMS_WARNING.
Ø Conditional compilation.
Conditional compilation
allows the compiler to allow to compile selected parts of a program based on
conditions you provide with the $IF directive.
Ø Support for non-sequential collections in FORALL.
Ø Improved datatype support.
Ø Backtrace an exception to its line number.
When handling an error,
how can you find the line number on which the error was originally raised?
In earlier release, the
only way to do this was allow you exception to go unhandled and then view the
full error trace stack.
Now you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to obtain that
stack and manipulate it programmatically within your program.
Ø Set operators for nested tables.
Ø Support for regular expressions.
Oracle database 10g supports
the use of regular expressions inside PL/SQL code via four new built-in functions.
ü REGEXP_LIKE
ü REGEXP_INSTR
ü REGEXP_SUBSTR
ü REGEXP_REPLACE
Ø Programmer-defined quoting mechanism.
Starting with oracle
database 10g release 1, you can define your own quoting mechanism for string
literals in both SQL and PL/SQL.
Use the characters q’(q
followed by a single quote) to note the programmer-defined deliemeter for you
string literal.
Ex:
DECLARE
v varchar(10) :=
'computer';
BEGIN
dbms_output.put_line(q'*v
= *' || v);
dbms_output.put_line(q'$v
= $' || v);
END;
Output:
v = computer
v = computer
Ø Many new built-in packages.
DBMS_SCHEDULER
Represents a major update
to DBMS_JOB.
DBMS_SCHEDULER provides much improved functionality for scheduling and executing
jobs defined via stored procedures.
DBMS_CRYPTO
Offers the ability to
encrypt and decrypt common oracle datatype, including RAWs, BLOBs, and CLOBs. It also provides
globalization support for encrypting data across different charactersets.
DBMS_MONITOR
Provides an API to control additional
tracing and statistics gathering of sessions.
DBMS_WARNING
Provides an API into the PL/SQL compiler warnings module,
allowing you to read and change settings that control which warnings are
suppressed, displayed, or treated as errors.
STANDARD PACKAGE
Oracle has defined in this
special package. Oracle defines quite a few identifiers in this package,
including built-in exceptions, functions and subtypes.
You can reference the
built-in form by prefixing it with STANDARD.
The basic unit in any PL/SQL program is block. All PL/SQL programs are composed of
blocks which can occur sequentially or nested.
BLOCK STRUCTURE
Declare
-- declarative section
Begin
-- executable section
Exception
-- exception section
End;
In the above declarative
and exceptiona sections are optional.
BLOCK TYPES
Ø Anonymous blocks
Ø Named blocks
ü Labeled blocks
ü Subprograms
ü Triggers
ANONYMOUS BLOCKS
Anonymous blocks implies
basic block structure.
Ex:
BEGIN
Dbms_output.put_line(‘My
first program’):
END;
LABELED BLOCKS
Labeled blocks are
anonymous blocks with a label which gives a name to the block.
Ex:
<<my_bloock>>
BEGIN
Dbms_output.put_line(‘My first program’):
END;
SUBPROGRAMS
Subprograms are procedures
and functions. They can be stored in the database as stand-alone objects, as
part of package or as methods of an object type.
TRIGGERS
Triggers consists of a PL/SQL block that is associated
with an event that occur in the database.
NESTED BLOCKS
A block can be nested
within the executable or exception section of an outer block.
IDENTIFIERS
Identifiers are used to
name PL/SQL objects, such as
variables, cursors, types and subprograms. Identifiers consists of a letter,
optionally followed by any sequence of characters, including letters, numbers,
dollar signs, underscores, and pound signs only. The maximum length for an
identifier is 30 characters.
QUOTED IDENTIFIERS
If you want to make an
identifier case sensitive, include characters such as spaces or use a reserved
word, you can enclose the identifier in double quotation marks.
Ex:
DECLARE
"a" number := 5;
"A" number := 6;
BEGIN
dbms_output.put_line('a = ' || a);
dbms_output.put_line('A
= ' || A);
END;
Output:
a = 6
A = 6
COMMENTS
Comments improve
readability and make your program more understandable. They are ignored by the PL/SQL compiler. There are two
types of comments available.
Ø Single line comments
Ø Multiline comments
SINGLE LINE COMMENTS
A single-line comment can
start any point on a line with two dashes and continues until the end of the
line.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); -- sample program
END;
MULTILINE COMMENTS
Multiline comments start
with the /* delimiter and ends with */ delimiter.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); /* sample program */
END;
VARIABLE DECLERATIONS
Variables can be declared
in declarative section of the block;
Ex:
DECLARE
a number;
b number := 5;
c number default 6;
CONSTANT DECLERATIONS
To declare a constant, you
include the CONSTANT keyword, and you must supply a default value.
Ex:
DECLARE
b constant number := 5;
c constant number default 6;
NOT NULL CLAUSE
You can also specify that
the variable must be not null.
Ex:
DECLARE
b constant number not null:= 5;
c number not null default 6;
ANCHORED DECLERATIONS
PL/SQL offers two kinds of
achoring.
Ø Scalar anchoring
Ø Record anchoring
SCALAR ANCHORING
Use the %TYPE attribute to define your
variable based on table’s column of some other PL/SQL scalar variable.
Ex:
DECLARE
dno dept.deptno%type;
Subtype t_number is number;
a t_number;
Subtype t_sno is student.sno%type;
V_sno t_sno;
RECORD ANCHORING
Use the %ROWTYPE attribute to define your
record structure based on a table.
Ex:
`DECLARE
V_dept dept%rowtype;
BENEFITS OF ANCHORED
DECLARATIONS
Ø Synchronization with
database columns.
Ø Normalization of local
variables.
PROGRAMMER-DEFINED TYPES
With the SUBTYPE
statement, PL/SQL allows you to define your own subtypes or aliases of predefined
datatypes, sometimes referred to as abstract datatypes.
There are two kinds of
subtypes.
Ø Constrained
Ø Unconstrained
CONSTRAINED SUBTYPE
A subtype that restricts
or constrains the values normally allowd by the datatype itself.
Ex:
Subtype positive is binary_integer range 1..2147483647;
In the above declaration a
variable that is declared as positive can store only ingeger greater than zero
even though binary_integer ranges from -2147483647..+2147483647.
UNCONSTRAINED SUBTYPE
A subtype that does not
restrict the values of the original datatype in variables declared with the
subtype.
Ex:
Subtype float is number;
DATATYPE CONVERSIONS
PL/SQL can handle conversions
between different families among the datatypes.
Conversion can be done in
two ways.
Ø Explicit conversion
Ø Implicit conversion
EXPLICIT CONVERSION
This can be done using the
built-in functions available.
IMPLICIT CONVERSION
PL/SQL will automatically
convert between datatype families when possible.
Ex:
DECLARE
a varchar(10);
BEGIN
select deptno into a
from dept where dname='ACCOUNTING';
END;
In the above variable a is
char type and deptno is number type even though, oracle will automatically
converts the numeric data into char type assigns to the variable.
PL/SQL can automatically convert
between
Ø Characters and numbers
Ø Characters and dates
VARIABLE SCOPE AND
VISIBILITY
The scope of a variable is
the portion of the program in which the variable can be accessed. For PL/SQL variables, this is from
the variable declaration until the end of the block. When a variable goes out
of scope, the PL/SQL engine will free the memory used to store the variable.
The visibility of a
variable is the portion of the program where the variable can be accessed
without having to qualify the reference. The visibility is always within the
scope. If it is out of scope, it is not visible.
Ex1:
DECLARE
a
number; -- scope of a
BEGIN
--------
DECLARE
b number; -- scope of b
BEGIN
-----
END;
------
END;
Ex2:
DECLARE
a
number;
b number;
BEGIN
--
a , b available here
DECLARE
b char(10);
BEGIN
-- a and char type
b is available here
END;
-----
END;
Ex3:
<<my_block>>
DECLARE
a
number;
b number;
BEGIN
--
a , b available here
DECLARE
b char(10);
BEGIN
-- a and char
type b is available here
-- number type b
is available using <<my_block>>.b
END;
------
END;
PL/SQL CONTROL STRUCTURES
PL/SQL has a variety of control
structures that allow you to control the behaviour of the block as it runs.
These structures include conditional statements and loops.
Ø If-then-else
Ø Case
ü Case with no else
ü Labeled case
ü Searched case
Ø Simple loop
Ø While loop
Ø For loop
Ø Goto and Labels
IF-THEN-ELSE
Syntax:
If <condition1>
then
Sequence of statements;
Elsif <condition1>
then
Sequence
of statements;
……
Else
Sequence of statements;
End if;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where
dname = 'ACCOUNTING';
if dno = 10 then
dbms_output.put_line('Location is NEW YORK');
elsif dno = 20 then
dbms_output.put_line('Location is DALLAS');
elsif dno = 30 then
dbms_output.put_line('Location is CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end if;
END;
Output:
Location is NEW YORK
CASE
Syntax:
Case test-variable
When value1
then sequence of statements;
When value2
then sequence of statements;
……
When valuen
then sequence of statements;
Else sequence
of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where
dname = 'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location
is NEW YORK');
when 20 then
dbms_output.put_line('Location
is DALLAS');
when 30 then
dbms_output.put_line('Location
is CHICAGO');
else
dbms_output.put_line('Location
is BOSTON');
end case;
END;
Output:
Location is NEW YORK
CASE WITHOUT ELSE
Syntax:
Case test-variable
When value1
then sequence of statements;
When value2
then sequence of statements;
……
When valuen
then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where
dname = 'ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEW YORK');
when 20 then
dbms_output.put_line('Location
is DALLAS');
when 30 then
dbms_output.put_line('Location
is CHICAGO');
when 40 then
dbms_output.put_line('Location
is BOSTON');
end case;
END;
Output:
Location is NEW YORK
LABELED CASE
Syntax:
<<label>>
Case test-variable
When value1
then sequence of statements;
When value2
then sequence of statements;
……
When valuen
then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where
dname = 'ACCOUNTING';
<<my_case>>
case
dno
when 10 then
dbms_output.put_line('Location
is NEW YORK');
when 20 then
dbms_output.put_line('Location
is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location
is BOSTON');
end case my_case;
END;
Output:
Location is NEW YORK
SEARCHED CASE
Syntax:
Case
When <condition1>
then sequence of statements;
When <condition2>
then sequence of statements;
……
When <conditionn>
then sequence of statements;
End case;
Ex:
DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where
dname = 'ACCOUNTING';
case dno
when dno = 10 then
dbms_output.put_line('Location
is NEW YORK');
when dno = 20 then
dbms_output.put_line('Location
is DALLAS');
when dno = 30 then
dbms_output.put_line('Location
is CHICAGO');
when dno = 40 then
dbms_output.put_line('Location
is BOSTON');
end case;
END;
Output:
Location is NEW YORK
SIMPLE LOOP
Syntax:
Loop
Sequence of
statements;
Exit when <condition>;
End loop;
In the syntax exit when
<condition> is equivalent to
If <condition>
then
Exit;
End if;
Ex:
DECLARE
i number := 1;
BEGIN
loop
dbms_output.put_line('i = ' || i);
i := i + 1;
exit when i > 5;
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
WHILE LOOP
Syntax:
While <condition> loop
Sequence of
statements;
End loop;
Ex:
DECLARE
i number := 1;
BEGIN
While i <= 5 loop
dbms_output.put_line('i = ' ||
i);
i := i + 1;
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
FOR LOOP
Syntax:
For <loop_counter_variable>
in low_bound..high_bound loop
Sequence of
statements;
End loop;
Ex1:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i = ' || i);
end loop;
END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5
Ex2:
BEGIN
For i in reverse 1..5 loop
dbms_output.put_line('i = ' || i);
end loop;
END;
Output:
i = 5
i = 4
i = 3
i = 2
i = 1
NULL STATEMENT
Usually when you write a
statement in a program, you want it to do something. There are cases, however,
when you want to tell PL/SQL to do absolutely nothing, and that is where the NULL comes.
The NULL statement deos nothing
except pass control to the next executable statement.
You can use NULL statement in the
following situations.
Ø Improving program
readability.
Sometimes, it
is helpful to avoid any ambiguity inherent in an IF statement that doesn’t
cover all possible cases. For example, when you write an IF statement, you do
not have to include an ELSE clause.
Ø Nullifying a raised
exception.
When you don’t
want to write any special code to handle an exception, you can use the NULL statement
to make sure that a raised exception halts execution of the current PL/SQL block but does not
propagate any exceptions to enclosing blocks.
Ø Using null after a label.
In some cases,
you can pair NULL with GOTO to avoid having to execute additional statements. For example, I
use a GOTO statement to quickly move
to the end of my program if the state of my data indicates that no further
processing is required. Because I do not have to do anything at the termination
of the program, I place a NULL statement after the label because at least one executable
statement is required there. Even though NULL deos nothing, it is still an executable statement.
GOTO AND LABELS
Syntax:
Goto label;
Where label is a label defined in the PL/SQL block. Labels are enclosed in double angle brackets. When a goto
statement is evaluated, control immediately passes to the statement identified
by the label.
Ex:
BEGIN
For i in 1..5 loop
dbms_output.put_line('i = ' || i);
if i = 4 then
goto exit_loop;
end if;
end loop;
<<exit_loop>>
Null;
END;
Output:
i = 1
i = 2
i = 3
i = 4
RESTRICTIONS ON GOTO
Ø It is illegal to branch
into an inner block, loop.
Ø At least one executable
statement must follow.
Ø It is illegal to branch
into an if statement.
Ø It is illegal to branch
from one if statement to another if statement.
Ø It is illegal to branch
from exception block to the current block.
No comments:
Post a Comment