PL/SQl introduction

The DBMS_OUTPUT is a built-in package that enables you to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers.


DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling>
END;


Note:

1.Every PL/SQL statement ends with a semicolon (;).

2.PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END.

3.The end; line signals the end of the PL/SQL block. To run the code from the SQL command line, you may need to type / at the beginning of the first blank line after the last line of the code.

4.PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner
block. If a variable is declared within an inner block, it is not accessible to the outer block.
However, if a variable is declared and accessible to an outer block, it is also accessible to
all nested inner blocks




A PL/SQL unit is any one of the following:
? PL/SQL block
? Function
? Package
? Package body
? Procedure
? Trigger
? Type
? Type body







PL/SQL Character Data Types and Subtypes
Following is the detail of PL/SQL pre-defined character data types and their sub-types:
Data Type Description
CHAR         Fixed-length character string with maximum size of 32,767 bytes
VARCHAR2    Variable-length character string with maximum size of 32,767 bytes
RAW        Variable-length binary or byte string with maximum size of 32,767 bytes, not         interpreted by PL/SQL
NCHAR        Fixed-length national character string with maximum size of 32,767 bytes
NVARCHAR2     Variable-length national character string with maximum size of 32,767 bytes
LONG         Variable-length character string with maximum size of 32,760 bytes       
LONG RAW    Variable-length binary or byte string with maximum size of 32,760 bytes, not         interpreted by PL/SQL
ROWID         Physical row identifier, the address of a row in an ordinary table
UROWID         Universal row identifier (physical, logical, or foreign row identifier)


DECLARE
message varchar2(30):= 'Data Base Management System';
BEGIN
dbms_output.put_line(message);
END;
/


Variable Declaration in PL/SQL

The syntax for declaring a variable is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]



sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);


BEGIN
dbms_output.put_line( 10 + 5);
dbms_output.put_line( 10 - 5);
dbms_output.put_line( 10 * 5);
dbms_output.put_line( 10 / 5);
dbms_output.put_line( 10 ** 5);
END;
/



DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/




DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
    DECLARE
    -- Local variables
    num1 number := 195;
    num2 number := 185;
    BEGIN
        dbms_output.put_line('Inner Variable num1: ' || num1);
    dbms_output.put_line('Inner Variable num2: ' || num2);
    END;
END;
/





DECLARE
   a number(2) := 10;
BEGIN
   a:= 10;
  -- check the boolean condition using if statement 
   IF( a < 20 ) THEN
      -- if condition is true then print the following  
      dbms_output.put_line('a is less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;
/



DECLARE
a number (2) := 21;
b number (2) := 10;
BEGIN
IF (a = b) then
dbms_output.put_line('Line 1 - a is equal to b');
ELSE
dbms_output.put_line('Line 2 - a is not equal to b');
END IF;
END;
/





DECLARE
   a number(3) := 100;
BEGIN
   -- check the boolean condition using if statement 
   IF( a < 20 ) THEN
      -- if condition is true then print the following  
      dbms_output.put_line('a is less than 20 ' );
   ELSE
      dbms_output.put_line('a is not less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;
/


IF-THEN-ELSIF Statement


DECLARE
   a number(3) := 100;
BEGIN
   IF ( a = 10 ) THEN
      dbms_output.put_line('Value of a is 10' );
   ELSIF ( a = 20 ) THEN
      dbms_output.put_line('Value of a is 20' );
   ELSIF ( a = 30 ) THEN
      dbms_output.put_line('Value of a is 30' );
   ELSE
       dbms_output.put_line('None of the values is matching');
   END IF;
   dbms_output.put_line('Exact value of a is: '|| a ); 
END;
/



DECLARE
   grade char(1) := 'A';
BEGIN
   CASE grade
      when 'A' then dbms_output.put_line('Excellent');
      when 'B' then dbms_output.put_line('Very good');
      when 'C' then dbms_output.put_line('Well done');
      when 'D' then dbms_output.put_line('You passed');
      when 'F' then dbms_output.put_line('Better try again');
      else dbms_output.put_line('No such grade');
   END CASE;
END;
/




DECLARE
   grade char(1) := 'B';
BEGIN
   case 
      when grade = 'A' then dbms_output.put_line('Excellent');
      when grade = 'B' then dbms_output.put_line('Very good');
      when grade = 'C' then dbms_output.put_line('Well done');
      when grade = 'D' then dbms_output.put_line('You passed');
      when grade = 'F' then dbms_output.put_line('Better try again');
      else dbms_output.put_line('No such grade');
   end case;
END;
/




DECLARE
   a number(3) := 100;
   b number(3) := 200;
BEGIN
   -- check the boolean condition 
   IF( a = 100 ) THEN
   -- if condition is true then check the following 
      IF( b = 200 ) THEN
      -- if condition is true then print the following 
      dbms_output.put_line('Value of a is 100 and b is 200' );
      END IF;
   END IF;
   dbms_output.put_line('Exact value of a is : ' || a );
   dbms_output.put_line('Exact value of b is : ' || b );
END;
/