Infolinks

Saturday 12 May 2012

SQL-INTRODUCTION(BRIEF)


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