Programming Language(PL) of SQL Examples

 PL/SQL :  Programming Language of SQL
 ---------
 Features :
 - Supports to execute a Block of statements as a       unit
 - Supports variables n Constants
 - Supports conditional constructs
 - Supports Iteration control statements
 - Supports Error handling using Exceptions
 - Supports  to define Composite Datatypes
 - Support to execute a block of statements            automatically based on the event using Database     Triggers
 - Suports to store and share the code using        subprograms


 Pl/sql blocks:
 It is an collection of SQL and prog language stmts.
 2 Types  1. Anonymous Block  - Block with out                                                                 name
              2. Named Block - Block with fixed name
                  Ex:  Subprograms,Triggers

 Block Structure :(Anonymous)
 declare                                   Simple Block:
  [< variable decln>; ]          Begin
  begin                           < exec stmts >;       
  < exec stmts >;              end;
 [ exception                              /
  < exec stmts >; ]
 end;
 /

 Variable  Declaration :
 * All sql data types are supported
 * Boolean is also supported
 Declare
 veno number(4) := 7900;      
                                :=  -- Assignment operator   
                                =  -- comparision operator
 vname varchar2(20);
 vjob varchar2(20) not null := 'CLERK';
 doj date default sysdate;
 flag boolean := TRUE;
 pin constant number(6) := 500038;

 Executable Statements :
 * DML ,TCL are supported
 * DDL , DCL are not supported
 * select .. into stmt ---> Used to retrieve the table      column contents into Pl/sql block.
    select <column list> into <variable list> from
    <table name> where <condition>;
   * dbms_output.put_line(' message text  '||                                                        variables );
      used to print messages / variables on to screen.
   * Comments
   -- single line comment
   /* multi line
       comment */
---------------------------------------------------------------
 

PL/SQL Examples : >set serveroutput on/off
 -- Activates the dbms stmt output buffer
 -- valid per session only

 Ex: PL/SQL Program retrieve the employ details        and calculates the Net salary and Prints.
 declare
   veno number(4) := &employ;  
   vname  varchar2(10);        
   vsal  number(7,2);             
   vcomm number(7,2); 
   net number(7,2);
 begin
   -- Retrieving data from table
 select ename,sal,comm into vname,vsal,vcomm       from emp where empno = veno;
 -- calculating net salary
 net := vsal + nvl(vcomm,0);
 dbms_output.put_line('Employ details are :');
 dbms_output.put_line(veno||'  '||vname||'  '||vsal
 ||'  '||vcomm||'  '||net);
 end;
 /
--------------------------------------------------------------
 save <filename> - save to local OS (.SQL file)
 get <filename> - display the file
 ed <filename> - opens file in editor
 start <filename> / @<filename> - execute the file
---------------------------------------------------------------
 

 Attribute Declaration: Used to define the pl/sql variables dynamically     according to  the Table structure.
 i) %Type - Column Type Declaration Used to define the variables according to the     specific column structure.
 Syntax : 
 Variable  <tablename>.<columnname>%type;

 >declare
    -- Using %type declaration
   veno emp.empno%type := &employ;
   vname emp.ename%type;
   vsal emp.sal%type;  
   vcomm emp.comm%type;
   net emp.sal%type;
 begin
   ---  same as above example  ---
 end;
---------------------------------------------------------------
 

 ii) %Rowtype - Record Type Declaration Used to define the variable according to the
 compleate table structure.
 Syntax :   Variable  <Tablename>%rowtype;

 Using %rowtype declaration : >declare                         -- veno    --->  i.empno
    i emp%rowtype ;       -- vname   --->  i.ename
   net number(12,2);       -- vsal   --->  i.sal
 begin                 -- vcomm  --->  i.comm
  i.empno := &employ;
 select ename,sal,comm into i.ename,i.sal,i.comm     from emp where empno = i.empno;
 net := i.sal + nvl(i.comm,0);
 dbms_output.put_line('Employ details are :');
 dbms_output.put_line(i.empno||'  '||i.ename
 ||'     '||net);
 end;
---------------------------------------------------------------  

create table student (roll number(3), name    varchar2(20), class number(2), m1 number(3),
 m2  number(3), m3 number(3), m4 number(3),  m5  number(3), m6 number(3));

 Ex:2
 * Pl/sql block calculates total marks and average       marks of a student.
  declare
   i student%rowtype ;
   tot_marks number(6);
   avg_marks number(6,2);
 begin
 i.roll := &rollno;  
 select name,class,m1,m2,m3,m4,m5,m6 into
 i.name, i.class, i.m1, i.m2, i.m3, i.m4, i.m5, i.m6    from student where roll = i.roll;
 tot_marks :=  i.m1 + i.m2 + i.m3 + i.m4 + i.m5 +                       i.m6;
 avg_marks :=  tot_marks / 6;
 dbms_output.put_line(' Student details are: ');
 dbms_output.put_line(i.roll||'  '||i.name
 ||'      '||i.class||'  '||tot_marks||'  '||avg_marks);
 end ;
---------------------------------------------------------------
 

 Conditional Constructs: Used to check for multiple conditions while
 manipulating data in pl/sql.
  i> IF       ii> CASE (8i)

 Syntax: Simple If
 if <condition> then
  <exec stmts>;
 end if;
 Syntax: Complex If
  if <condition1> then
   <exec stmts>;
  [ elsif <condition2> then  //  ELSIF -- else if 
   <exec stmts>;
  elsif <condition3> then   
   <exec stmts>;
    .......
    .......
  else
   <exec stmts>; ]
  end if;---------------------------------------------------------------
 

 Using IF statement : declare
   i student%rowtype;
   tot_marks number(6);
   avg_marks number(6,2);
   result varchar2(50);
 begin
 i.roll := &rollno;
 select name,class,m1,m2,m3,m4,m5,m6 into
 i.name,i.class,i.m1,i.m2,i.m3,i.m4,i.m5,i.m6 from    student where roll = i.roll;
 tot_marks := i.m1 + i.m2 + i.m3 + i.m4 + i.m5 +                       i.m6;
 avg_marks := round(tot_marks / 6);
 IF i.m1 < 40 or i.m2 < 40 or i.m3 < 40 or
 i.m4 < 40 or i.m5 < 40 or i.m6 < 40 THEN
 result := 'FAIL';
 ELSIF avg_marks >= 70 THEN
 result := 'DISTINCTION';
 ELSIF avg_marks >= 60 THEN
 result := 'FIRST CLASS';
 ELSIF avg_marks >= 50 THEN
 result := 'SECOND CLASS';
 ELSE
 result := 'THIRD CLASS'; 
 END IF;
 dbms_output.put_line(' Student details are: ');
 dbms_output.put_line(i.roll||'  '||i.name||'  '||   i.class ||'  '||tot_marks||'  '||avg_marks
 ||'    '||result);
 end ;
---------------------------------------------------------------
 

 * PL/SQL Block checks for existing commission and   assign new commission . declare
   veno emp.empno%type  := &employ; 
   vname emp.ename%type;
   vcomm emp.comm%type; 
   x emp.comm%type; 
  begin
   select ename,comm into vname,vcomm from       emp where empno = veno;
  x := vcomm;  -- Storing old commission
  if vcomm is null then
  vcomm := 3000;
  elsif vcomm = 0 then
  vcomm := 2500;
  else
  vcomm := vcomm + vcomm * .25;
  end if;
  update emp set comm = vcomm
  where empno = veno;
  dbms_output.put_line(veno||'  '||vname||'  '||x
  ||'    '||vcomm);
  commit;
  end;
---------------------------------------------------------------  

Case [ 8i ]:  Used to check for multiple conditions easily.
  It will check for "equality" condition in pl/sql.
  * It can be used in "Select" stmt also.
  * Case in select is used to generate the reports

  Syntax: ( Select )
  case
  when <cond1> then  <value1>
  [ when <cond2> then  <value2>
  when <cond3> then  <value3>
  else <value4> ]
  end case

  Syntax: ( Pl/Sql )
  case <variable>
  when <value1> then
   <exe stmts>;
  [ when <value2> then
   <exe stmts>;
    .
    .
  else
   <exe stmts>; ]
  end case;

 Using Case Construct : declare
 grade char(1) := '&grade';
 begin
 case grade
 when 'A' then
  dbms_output.put_line(' Grade is A ');
 when 'B' then
  dbms_output.put_line(' Grade is B ');
 when 'C' then
  dbms_output.put_line(' Grade is C ');
 else
  dbms_output.put_line(' Grade is D '); 
 end case;
 end;

 Case in Select : select empno,ename,sal,job,
 case
 when job = 'CLERK' then 'C'
 when job = 'SALESMAN' then 'B'
 when job in ('MANAGER','ANALYST') then 'B+'
 when job = 'PRESIDENT' then 'A+'
 else 'D'
 end "GRADE" from emp;

 select empno,ename,job,sal,
 case
 when sal <= 3000 then 'LOW'
 when sal > 3000 and sal <= 6000 then
 'BELOW  AVG'
 when sal > 6000 and sal < 10000 then 'AVERAGE'
 when sal between 10000 and 15000 then    'NORMAL'
 when sal > 15000 then 'HIGH'
 end "RANGE" from emp;
---------------------------------------------------------------
 

 Iteration Control statements:( LOOPS ) Supports to execute a block of statements   repeatedly until   conditions are True.
 4 Types
 1. Simple loop             2. While loop
 3. Numeric For loop     4. Cursor For loop

 Simple Loop:
 It is an infinite loop task
 Syntax:
 Loop
 <exec stmts>;
 end loop;

 To break the simple loop :
 i> exit when (condition);
 ii> if ( condition )  then
     exit;
     end if;

  Using Loops:  * Pl/sql block prints first 10 numbers on to screen

  Using Simple Loop  declare
  a number(3) := 1;
  begin
  dbms_output.put_line(' The Numbers are : ');
  loop
  dbms_output.put_line(a);
  a := a + 1;
  exit when (a > 10);
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

 While Loop: It is an pre-tested loop
 Syntax:
 while ( condition ) loop
  <exec stmts>;
 end loop;

 Using While loop  declare
  a number(3) := 1;
  begin
  dbms_output.put_line(' The Numbers are : ');
  while (a <= 10) loop
  dbms_output.put_line(a);
  a := a + 1;
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

 Numeric For loop: Syntax:
 For <variable> in [reverse] <value1> .. <value2>   loop
 <exec stmts>;
 end loop;

 <variable> --- Automatically defined by for loop
 reverse (optional) --- Accepts values in reverse   order
 ..  --- Range operator
 It increments the variable by 1 always
 variable cannot be assigned with a value in For    loop

 Using Numeric For loop  Begin
  dbms_output.put_line(' The Numbers are : ');
  for n in 1 .. 20 loop
  dbms_output.put_line(n);
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

  Using Reverse option:
  Begin
  dbms_output.put_line(' The Numbers are :');
  for n in REVERSE 1 .. 10 loop
  dbms_output.put(n||'  ');
  end loop;
  dbms_output.put_line('   ');
  dbms_output.put_line(' End of numbers ');
  end;

 * dbms_output.put  --- Prints the results in same                                       line
 * dbms_output.put_line  --- Prints the results in                                               new line
 * "Put_line" must be followed with "put" stmt to         activate output buffer.
---------------------------------------------------------------