Saturday, March 12, 2016

PLSQL: Procedural Structured Query Language.

PLSQL: Procedural Structured Query Language.


It is one of the comprehensive tools of the oracle that is helpful in adding the procedural language features such as
Decision making, looping, exception handling etc... to the powerful SQL.

PLSQL Programs contains 3 sections.
1)Declaration section: In this section we declare the variables ,cursors etc…
2)Executable section: In this section we do the looping, decision making and to the sq. statements…
3)Exception section: In this section we handle the exceptions raised while using the sq. statements.

Every plsql statement should be terminated with semi colon(;)…

Variable: A variable is a named location in memory to which a data can be assigned or from which the data can be           read.

Variable declaration: A variable is declared in the declaration section for the process of telling the compiler,the name memory location its type and size.

Variable Declaration Syntax:

Variable_name datatype(size);
Ex: ln_num number;
      Ln_flag  char(1);
      Ln_msg varchar2(10000);
 
Assigment Operator (:=)
This operator is used to assign the value on its right to the variable on its left.
EX:  ln_num:= 0;
       Ln_falg:= ‘N’;

Intialization:
This is process of assigning a variable an intial value at the time of declaration of the variable.

Ex: ln_num NUMBER:= 0;
      Ln_falg  char(1):= ‘N’;

Server output:
It is an sql environment variable that tells whether the output of the plsql program has to be shown, by default it is OFF;
Inordet to ON please run the below command at the beginning of every session.

SET SERVEROUTPUT ON;

DBMS_OUTPUT.PUT_LINE:
This is one of the built in package.function of the plsql that is used to print the output….







BLOCKS:
BLOCKS
Reusability
Stogare
Scope
Anonymous
Not there
NO
Until the session
Named
Yes
Yes
Permanently


Let’s write some anonymous blocks to get practiced of how to print a message and use the variables and assign a value to it and also assign an intial value to it….

1)   To Print a Message.

begin
 dbms_output.put_line('Welcome to Plsql');
end;
/

declare
ln_msg varchar2(1000);
begin
   dbms_output.put_line('Start');
   ln_msg:= 'Welcome to Plsql';
   dbms_output.put_line(ln_msg);
   dbms_output.put_line('End');
end;
/

declare
ln_num number:=&parameter1;
begin
   dbms_output.put_line('Start');
   dbms_output.put_line(ln_num);
   dbms_output.put_line('End');
end;
/

2)   Addtion of 2 numbers.
declare
ln_num  number:=0;
ln_num1 number:=&parameter1;
ln_num2 number:=&parameter2;
begin
   dbms_output.put_line('Start');
   ln_num := ln_num1 + ln_num2;
   dbms_output.put_line('The sum of '||ln_num1||' and '||ln_num2||' is '||chr(10)||ln_num);
   dbms_output.put_line('End');
end;

3)   Substraction 2 numbers.
4)   Addition of 2 numbers and substracting the 3 number.
5)   Multiplication of 2 numbers.


Decision Making:
If condition Syntax:
    IF condition1 then
        Statement1;
   ELSIF condition2 then
       Statement2;
   ELSIF condition3 then
       Statement3;
   ELSE
      Statement n;
 END IF;

6)    Display Max of 2 numbers
        declare
ln_num1 number:=&parameter1;
ln_num2 number:=&parameter2;
begin
    dbms_output.put_line('Start');
    IF ln_num1 > ln_num2 then
    dbms_output.put_line('The smax number is '||ln_num1);
    else
    dbms_output.put_line('The smax number is '||ln_num2);
    end if;
    dbms_output.put_line('End');
end;
/
       7) Give input as 3 numbers and print the max number
     8) Give input as 3 numbers and print the max number when added 2 numbers.

LOOPING:
   Syntax:    FOR variable in start..end
                         LOOP
                       --any action to be taken place
                     END LOOP;
EX: 1) Print the numbers from 1 to 100/.
                declare
i number:= 0;
begin
for i in 1..100
 loop
 dbms_output.put_line('The number is ' ||i);
end loop;
end;





       2) Print the odd numbers from 1 to 20.
declare
i number:= 0;
begin
for i in 1..20
 loop
   if mod(i,2)!= 0 then
    dbms_output.put_line('The odd number is ' ||i);
  end if;
end loop;
end;
/
       3) print the even numbers from 1 to 20.
            declare
i number:= 0;
begin
for i in 1..20
 loop
   if mod(i,2) = 0 then
    dbms_output.put_line('The odd number is ' ||i);
  end if;
end loop;
end;
/


EXIT:
This is an oracle provided plsql keyword using which we can come out of the loop.

EX: Print the odd numbers from 1 to 50 and exit when ever you find the number 26 in the loop..
declare
i number:= 0;
begin
for i in 1..50
 loop
 if i = 26 then
  exit;
  else
   if mod(i,2) != 0 then
    dbms_output.put_line('The odd number is ' ||i);
  end if;
 end if;
end loop;
dbms_output.put_line('The end ');
end;
/





NOTE:

Whenever we write a sql statement in a plsql block we cannot use the column name directly for any manipulation purpose etc….
We should fetch the values in to the local variables using INTO Clause.
Total No of column in the select clause should be equal to total number of variables in the into clause.
The order of selected columns data type and size sequence should be same as the order of variables sequence data type and size in the into clause.

EX:  Write a Simple plsql block to display employee name,number,salary.

declare
ln_empno number:=0;
ln_ename varchar2(100);
ln_sal number;
begin
  select empno,ename,sal
  into  ln_empno,ln_ename,ln_sal
  from emp
  where empno = &parameter1;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
end;
/

%TYPE: This attribute is used to declare the plsql variables with the same definition as column definition of a table.

Syntax: variable Name Table_Name.Column_Name%type;

EX: Write a Simple plsql block to display employee name,number,salary.

   declare
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
begin
  select empno,ename,sal
  into  ln_empno,ln_ename,ln_sal
  from emp
  where empno = &parameter1;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
end;
/






       Write a Simple plsql block to display employee name,number,salary, department name and location.

declare
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
ln_dname dept.dname%type;
ln_loc   dept.loc%type;
begin
  select empno,ename,sal,dname,loc
  into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
  from emp e,dept d
  where empno = &parameter1
  and e.deptno = d.deptno;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is --'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
end;
/
       Write a simple plsql block to display maximum salary from the emp table.
      declare
 ln_sal   emp.sal%type;
 begin
   select max(sal)
   into  ln_sal
   from emp;
 dbms_output.put_line('The max sal   is --'||ln_sal);
 end;
 /




Procedure:
Create or replace procedure p_name(parameter1 datatype, parameter2 datatype)
 Is
BEGIN
   NULL;
END;













Ex:  create or replace procedure xx_max_sal
is
 ln_sal   emp.sal%type;
 begin
   select max(sal),deptno
   into  ln_sal
   from emp;
 dbms_output.put_line('The max sal   is --'||ln_sal);
 end;
 /

Execution :        
begin
 xx_max_sal;
 end;
 /

Exec  xx_max_sal;


EX: Write a Simple proceudre to display employee name,number,salary, department name and location.

create or replace procedure xxx_emp_dtls(p_empno number)
is
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
ln_dname dept.dname%type;
ln_loc   dept.loc%type;
begin
  select empno,ename,sal,dname,loc
  into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
  from emp e,dept d
  where empno = p_empno
  and e.deptno = d.deptno;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is --'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
end xxx_emp_dtls;
/








Exercise:
Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and grade.


Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and result.


Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and marks. The student marks should be greater then marks parameter value.



No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...