Thursday, 14 June 2012

PLSQL LEARNING CLASS1


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;

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)