Showing posts with label PL/SQL Introduction. Show all posts

How to convert the anonymous block to label block.

0
How to convert the anonymous block to label block:
  • We need to place a label before the DECLARE keyword.
  • The label can also can use appear after the END keyword and it is optional.
  • The advantage of labelled block is that labelled blocks allow you to access those variables that would not be visible when using anonymous block.
SQL> <<Block1>> DECLARE
                  m NUMBER:=100;
              BEGIN
                   
                  m:=200; 
                  DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m);
         <<Block2>> DECLARE
 
                  m NUMBER:=300; 
                  v_tol NUMBER; 
                  m number:=400; 
          BEGIN 
                  Block1.m:=500; 
                  n:=600; 
                  v_tol:=block1.m+n;
                  DBMS_OUTPUT.PUT_LINE(‘The sum of block1 m,n is:’||v_tol); 
                  DBMS_OUTPUT.PUT_LINE(‘The value of Inner block is:’||m); 
          END 
                  block2;                  DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m); 
                  END block1;

SQL>create table nlj_acc_tab(
 
                  accno varchar2(20) constraint pk_accno primary key, 
                  name varchar2(20) constraint nn_name not null, 
                  acctype char constraint chk_atype check(acctype in(‘C’,’S’,’R’)), 
                  doo timestamp DEFAULT sysdate, 
                  bal number(7,2) constraint nn_bal not null);

 Click here to go to Back page

Continue reading →

Variable scope and Nested Blocks.

0
Variable scope and Nested Blocks:
Scope:

  • The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier.
  • Once the scope of the variable is lost it means that the life of the variable no more.
  • All variables loose their scope as soon as the PL/SQL block ends or terminates.
  • Oracle automatically release the space upon that variable once its scope is closed.
Concepts of scoping:
  • With in the same scope, all identifiers must be unique.
  • Even when the data types are differing, variables and parameters cannot share the same name.
Nested blocks:
  • PL/SQL blocks can be nested wherever an executable statement is allowed.
  • A nested block becomes a statement.
  • An exception section can contain nested blocks.

Scope  and visibility diagram:
Ex:   
SQL>DECLARE
            x NUMBER;
         BEGIN
              DECLARE
                 y NUMBER;
              BEGIN
                 y:=x;
              END;
            …
         END;
SQL>DECLARE
               
              m NUMBER:=250;
         BEGIN
               
              DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m);
              DECLARE
                   
                     m NUMBER:=550;
              BEGIN
                  
                     DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m);
             END;
               
         DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m);          
       END;

SQL>DECLARE
               
               m NUMBER:=100;
         BEGIN

              m:=200;
              DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m);
              DECLARE

                   n NUMBER:=300;
                   v_tol NUMBER;
              BEGIN

                   m:=500;
                   n:=600;
                   v_tol:=m+n;
                  DBMS_OUTPUT.PUT_LINE(‘The sum of m,n is:’||v_tol);
             END;

           DBMS_OUTPUT.PUT_LINE(‘The value of Outer m:’||m) 
            END;

 Click here to go to Back page

Continue reading →

Debugging statement

0
Debugging statement:
  • The debugging statement of  PL/SQL is DBMS_OUTPUT.PUT_LINE();
  • For producing outputs on the video device we need the assistance of DBMS_OUTPUT Package.
  • The procedure PUT_LINE() outputs information to a buffer in the SGA.
  • This procedures is used to identify the error during runtime of the PL/SQL block.
  • This procedure requires a single parameter of any of the data types varchar2 or number or data.
  • To see the output of PUT_LINE() on the console of the client side enter the following SQL plus command on the SQL prompt.

Syntax:
DBMS_OUTPUT.PUT_LINE(‘Message’||’variablename);

  • The DBMS_OUTPUT packages is owned by the oracle user SYS.
  • The size of the buffer can be set between 2000 to 10,00,000 bytes.
  • The specification to set buffers are
            1) SET SERVEROUTPUT ON
            2) SET SERVEROUTPUT ON SIZE 4000
            3) SET SERVEROUTPUT OFF
SQL>BEGIN
   
        DBMS_OUTPUT.ENABLE;
   
        DBMS_OUTPUT.PUT_LINE(‘First program in PL/SQL’);
   
        DBMS_OUTPUT.PUT_LINE(‘Illustrating by’);
   
        DBMS_OUTPUT.PUT_LINE(‘‘‘net lo java’’’);
   
    END;
  • DBMS_OUTPUT.ENABLE;  Once declare will make the SERVEROUTPUT  process to get activated.

SQL>DECLARE
   
        V_FirstNum NUMBER:=&fno;
   
        V_SecondNum NUMBE:=&sno;
   
        V_tol NUMBER;
   
     BEGIN
   
        V_tol:=v_FirstNum+v_SecondNum;
   
        DBMS_OUTPUT.PUT_LINE(‘The sum of  Frist and second num is ‘||v_tol);
   
    END;


SQL>BEGIN
   
        DBMS_OUTPUT.PUT_LINE(‘The sum of  first and second num is ‘||(&FirstNum+&secondNum));
   
     END;

 Click here to go to Back page

Continue reading →

Declaring PL/SQL Variables.

0
Declaring PL/SQL Variables:
Syntax
Identifier name [CONSTRAINT] datatype [NOT NULL] [:=DEFAULT]  Expr;
 

Identifier name:
  • Specifies the name of the relevant variable for that block.
Constant:
  • Constant the variable such that its value cannot change during the program process.
  • Constants must be initialized else raise an exception.
Datatype:
  • It is a scalar, composite , reference of  LOB data type as applicable to the required situation.
NOTNULL:
  • Constraints a variable such that it must contain a value and raises if NULL is identified.
  • NOT NULL variables should be initialized else raise an exception.
Default:
  • Sets the default value for the value in the PL/SQL program if not attended.
Expr:
  • It is any PL/SQL expression that can be a literal, another variable or an expression involving operations and functions for initialization.

Illustration:
 

DECLARE
     Vno number(4):= 7788;
     Vname varchar2(20) not null:=’SCOTT’;
            --must not hold null value through out program
     Doj date default sysdate;
     Flag Boolean:=true;
            --for Boolean variable accept only TRUE or FALSE value and value should not be in single code.

     pcode constant number(6):=567892;
           --cannot be used as an assignment target.


 Click here to go to Back page

Continue reading →

Assigning values to a variable.

0
Assigning values to a variable:
In PL/SQL a variable can be assigned in three ways

  • By using assignment operator(:=)
  • By selecting or fetching database values(INTO).
  • By passing it as IN or INOUT parameter to a subprogram.
 Click here to go to Back page

Continue reading →

Operators in PL/SQL

0
Operators in PL/SQL:
Logical                :   AND,OR,NOT
Arithmetic           :   +,-,*,/
Concatenation    :   | |
Comparison        :  =,!=,<,>,<=,>=
SQL*Plus            :   ISNULL,LIKE,BETWEEN,IN


 Click here to go to Back page

Continue reading →

Types of Variables in PL/SQL.

0
Types of Variables in PL/SQL:
1) Scalar.
2) Composite.
3) Reference.
4) LOB (large objects).

1) Scalar Data Types:
  • They holds a single value.
  • Main data types are those that correspond to column types in oracle server tables.
  • Supports Boolean variables.

2) Composite data types:
  • A variable of a composite type contains one or more scalar variables.
  • Composite data types are similar to structures in ‘C’ language.
  • They help in keeping all the related data items together as one collection.

3) Reference Data types:
  • They hold values, acting as pointers, which designate other program items.
  • They are very essential when manipulating collection of data items in sub programs.

4) LOB data types:
  • They holds values called locations, specifying the location of large objects that are stored  out of line in the PL/SQL program.
 Click here to go to Back page

Continue reading →

Handling variables in PL/SQL

0
Handling variables in PL/SQL:
  • Declare and initialize variables in the declaration section.
  • Declarations allocate storage space for a value according to its defined size.
  • Declarations can be assigned with an Initial  value and can be imposed with a NOT NULL constraint or DEFAULT option.
  • We reassign new values to variables in the executable section.
  • Pass values into PL/SQL blocks through parameters.
  • There are three parameter modes, they are IN(The default), OUT and IN OUT
  • View results from a PL/SQL blocks through parameters.
  • Reference variables can be used for Input or Output in SQL data manipulation statements.

 Click here to go to Back page

Continue reading →

Variables in PL/SQL

0
Variables in PL/SQL:
Use of variables
Variables can be used for
1) Temporary storage of data:
  • Data can be temporarily stored in one or more variables for use when validating data Input for Processing the Data in the Data flow process.

2) Manipulation of stored values:
  • Information is transmitted between PL/SQL and database with variables.
  • Variables help in the data manipulation of stored values.
  • Variables can be used for calculations and others data manipulations without accessing the database each and every time, Hence they help in reducing the I/O Cycles.

3) Reusability:
  • Once declared can be used repeatedly by even in other blocks.

4) Ease of maintenance:
  • Variables can be declared based on the declarations of the definitions of database columns.
  • Provides Data Independence, reduces maintenance costs and allows programs to adapt the changes as the Database changes.
 Click here to go to Back page

Continue reading →

Comments in PL/SQL

0
Comments in PL/SQL:
  • PL/SQL compiler ignores comments.
  • Comments promote readability and aids understing.
  • PL/SQL supports two types comments
              -> Single line comments(--)
              -> /* Multi Line comments */
Comment Example:
Store the employee No is NOT NULL(compulsory), employee Name and constant the employee Department fixed on Web Designer using the Variable.
Exmple Code:
SQL> set serveroutput on
SQL> DECLARE
    --assign value into eno variable
         eno number(4) NOT NULL := 1001;   
         ename varchar2(10) := Net Lo Java;

    /* Constant value is fixed for job value is "Web Designer"
    is fixed all program not required declare all times. */


          job CONSTANT varchar2(10) := Web Designer;
          BEGIN
          dbms_output.put_line('Declared Value:');
          dbms_output.put_line(' Eno: ' || eno || ' EName: ' || ename);
          dbms_output.put_line(' Job: ' || job);   
          END;


 Click here to go to Back page

Continue reading →

Types of blocks in PL/SQL

0
Types of blocks in PL/SQL:
A PL/SQL program can be written in various types of blocks, they are 

1) Anonymous block:
  • The block having no name.
  • They are declared at the point in an application, where they are to be executed and are passed to PL/SQL engine for execution at runtime.
  • It cannot be called.
  • There are used in D2K form.

SQL>DECLARE
         BEGIN
               NULL;
         EXCEPTION
         WHEN OTHERS THEN
               NULL;
         END;

2) Named Block:

  • The block having name and they have all the features as specified for the anonymous blocks.
  • Named blocks help in associating with the scope and resolution of variables in different blocks.
  • They give the specifications of the named spaces as provided in high level OOPs languages like C++ and JAVA.
  • Named blocks are conveniences for variable management.
 SQL><<FirstBlock>>          DECLARE
          BEGIN
                 NULL;
          EXCEPTION
          WHEN OTHERS THEN
                 NULL;
          END FirstBlock;

  • Named blocks make the PL/SQL blocks more clear and readable.
  • Named blocks increase the clarity of programming when we attempt the nesting process, and control structures.

3) Sup-Programmed blocks:

  • These are named PL/SQL blocks that can take parameters and can be invoked with in the other anonymous or sub-programmed PL/SQL blocks.
  • These blocks are either is declared as procedures or functions.
  • A procedure block is used for performing an action, and a functional block is used for performing calculation.
  • These sub-programs provides modularity and reusability.
 Click here to go to Back page


Continue reading →

Executing statements and PL/SQL blocks from SQL*Plus

0
Executing statements and PL/SQL blocks from SQL*Plus:
  • Place a semicolon(;) at the end of the SQL statement or PL/SQL control statement.
  • Use a Forward slash(/) to run the Anonymous PL/SQL block in SQL*plus buffer.
  • Place a period(.) to close a SQL*Plus Buffer without running the PL/SQL program.
  • A PL/SQL block is treated as one continuous statement in the SQL*Plus buffer.
  • Semicolon within the PL/SQL block does not closes or Runs the SQL Buffer.
  • In PL/SQL an Error is called as an Exception.
  • Sections keywords "DECLARE","BEGIN", and "EXCEPTION" should not contain A semicolon.
  • "END" and all other PL/SQL statements should be applied with a semicolon at the END.

Note:

  • Declaration part of PL/SQL block is optional, but the body of the PL/SQL is must. (The area between begin and end is called as body of the PL/SQL block.).
  • Every PL/SQL block is terminated by END statement followed by a semicolon.
  • PL/SQL is not a data formatting language.
 Click here to go to Back page


Continue reading →

A simple PL/SQL Block

0
A simple PL/SQL Block:
1) SQL>BEGIN

                       NULL;
              END;
->NULL Block.

2) SQL>BEGIN
                      RETURN;
               END;


->BLOCK with RETURN clause.

3) SQL>DECLARE
              BEGIN
                      NULL;
              END;


 

4) SQL>DECLARE
              BEGIN
                       NULL;
              EXCEPTION
                       WITH OTHERS THEN
              NULL;
              END;
Continue reading →

Block Structure approach

0
Block Structure approach:
  • The basic units that make up a PL/SQL program are logical blocks.
  • The block in PL/SQL can be nested with one another.
  • A block groups related declarations and statements into one single unit.
The basic parts of a PL/SQL block are:
  1. Declarative part (optional).
  2. Executable part (Mandatory).
  3. Exception handling (Optional).

1.Declarative part:It is used to define user defined types, variables which can be used, in the executable part for further manipulations. 

2.Executable part:
All  procedural statements are included between the BEGIN and END statement.
It must have one executable statement.


3.Exception Handling part:
Error that occur during execution are dealt in the exception handling part.

Notes:

  • A PL/SQL program is a logical block, which contain any number of nested sub blocks.
  • Block can be nested in the executable and exception handling parts of a PL/SQL block, or a sub program.
  • A PL/SQL is marked with either a "DECLARE" or "BEGIN" keyword and ends with the keyword "END".
  • Only BEGIN and END keywords are mandatory.
  • A semicolon(;) has to be placed after the "END" keyword.

Block syntax:
SQL> DECLARE
    [Variable declarations;
    Cursor declaration;
    User_defined exception;]
          BEGIN
          <SQL statements;>
          PL/SQL statement
          EXCEPTION
          Action to perform when errors occurs.
          END;
  • Only executable section is required.
  • The declarative and exception handling section are optional.
  • We can define local sub programs in the declarative part of any block.
  • However, we can call local subprograms only from the block in which they are define.
 Click here to go to Back page

Continue reading →

PL/SQL

0

PL/SQL
  • PL/SQL is a procedural extension to a non-procedural language SQL.
  • PL/SQL is one of the utility of Oracle dbserver and it is not a tool like SQL*plus.
  • It is a block-structured languager.
  • PL/SQL block is a logical collection of procedural as well as non-procedural statements. It is a data processing language, but  SQL is a powerful Data manipulation Language.
  • PL/SQL is having all the features of procedural language as well as all non-procedural features (because of SQL support).
  • PL/SQL can used for both Server-Side and Client-Side Development.
  • PL/SQL block is a logical collection of procedural as well as non-procedural statements.
 Click here to go to Back page

Continue reading →

Recent Posts