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;
No comments:
Post a Comment