tutorial on pl/sql

PL/SQL BLOCK
The pl/sql block contains the following section:-------The DECLARE section.-----The Master BEGIN and END section that contains the EXCEPTION section.The declare section contains declaration of memory variables, constants, cursors etc. The begin section contains sql executable statements and pl/sql executable statements. The exception section contains code to handle errors that may arise during the execution of the code block. The end declares the end of pl/sql block.A bit about it's working. When you typed out the pl/sql block for execution. It is sent to the pl/sql engine, where procedural statements are executed; and sql statements are sent to the sql executor in the oracle engine. Since pl/sql engine resides in the oracle engine, the codes executes smoothly and efficiently.




PL/SQL DATA-TYPE
This is easy since it includes almost all the data types which u have used in sql such as date, varchar, number, char etc etc... Some of the attributes such as %TYPE is also used. This attribute automatically takes in the default data type of the sql table from which u have passed the query. We will discuss this later.Remember in pl/sql a variable name must begin with a character and can be followed by maximum of 29 other characters. Reserved words can't be used unless enclosed within double quotes. Variables must be separated from each other by at least one space or by a punctuation mark. You can assign values of operator using := operator. I won't discuss about logical comparisons operators such as <, > , >=, NOT, TRUE, AND, OR, NULL etc since they r quite easy to understand.








HOW TO DISPLAY MESSAGES ON SCREEN ---DBMS_OUTPUT :
is a package that includes a number of procedure and functions that accumulate information in a buffer so that it can be retrieved later. These functions can also be used to display messages to the user.PUT_LINE : Put a piece of information in the package buffer followed by an end-of-line marker. It can also be used to display message to the user. Put_line expects a single parameter of character data type. If used to display a message, it is the message 'string'.EG: dbms_output.put_line(x);REMEMBER:

To display messages to the user the SERVEROUTPUT should be set to ON. SERVEROUTPUT is a sql*plus environment parameter that displays the information pased as a parameter to the PUT_LINE function.EG: SET SERVEROUTPUT ONA bit about comments. A comment can have 2 forms i.e.-- The comment line begins with a double hyphen (--). The entire line will be treated as a comment.-- The C style comment such as /* i am a comment */



CONDITIONAL CONTROL AND ITERATIVE CONTROL AND SEQUENTIAL CONTROLIF and else.....IF --Condition THEN--ActionELSEIF --Condition THEN--ActionELSE--ActionEND IF;


SIMPLE LOOPloop--

Sequence of statements;end loop;the loop ends when u use EXIT WHEN statement --conditionWHILE LOOPWhile --conditionloop--sequence of statementsend loop;FOR LOOPFOR i in 1..10loop--sequence of statementsend loop;GOTO (sequential control)GOTO X;<<>>



EXAMPLES--ADDITIONdeclarea number;b number;c number;begina:=&a;b:=&b;c:=a+b; dbms_output.put_line('Sum of ' a ' and ' b ' is ' c);
Here & is used to take user input at runtime.....



--SUM OF 100 NUMBERS
Declarea number;
s1 number default 0;
Begin
a:=1;
loops1:=s1+a;
exit
when (a=100);
a:=a+1;end loop;
dbms_output.put_line('Sum between 1 to 100 is 's1);
End;



--SUM OF odd NUMBERS USING USER INPUT...
for loop
declaren number;
sum1 number default 0;
endvalue number;
begin
endvalue:=&endvalue;
n:=1;for n in 1.. endvalueloopif mod(n,2)=1
then
sum1:=sum1+n;
end ifend loop;
dbms_output.put_line('sum = ' sum1);
end;


--SUM OF 100 ODD NUMBER .. WHILE LOOP
declaren number;
endvalue number;
sum1 number
default 0;
beginendvalue:=&endvalue;
n:=1;while (n < endvalue)
loopsum1:=sum1+n;
n:=n+2;end loop;
dbms_output.put_line('Sum of odd numbers between 1 and ' endvalue ' is ' sum1);
end;
--CALCULATION OF NET SALARY
declare
ename varchar2(15);
basic number;
da number;
hra number;
pf number;
netsalary number;
begin ename:=&ename;
basic:=&basic;
da:=basic * (41/100);
hra:=basic * (15/100);
if (basic <>= 3000 and basic <= 5000)
then
pf:=basic * (7/100);
elsif (basic >= 5000 and basic <= 8000)
then
pf:=basic * (8/100);
elsepf:=basic * (10/100);
end if;
netsalary:=basic + da + hra -pf;
dbms_output.put_line('Employee name : ' ename);
dbms_output.put_line('Providend Fund : ' pf);
dbms_output.put_line('Net salary : ' netsalary);
end;

--MAXIMUM OF 3 NUMBERS
Declare
a number;
b number;
c number;
d number;
Begin
dbms_output.put_line('Enter a:');
a:=&a;
dbms_output.put_line('Enter b:');
b:=&b;dbms_output.put_line('Enter c:');
c:=&b;if (a>b) and (a>c)
then
dbms_output.putline('A is Maximum');
elsif (b>a) and (b>c)
then
dbms_output.putline('B is Maximum');
else
dbms_output.putline('C is Maximum');
end if;
End;




--QUERY EXAMPLE--IS SMITH EARNING ENOUGH

declare
s1 emp.sal %type;
beginselect sal into s1 from empwhere ename = 'SMITH';
if(no_data_found)
then
raise_application_error(20001,'smith is not present');
end if;
if(s1 > 10000)
then
raise_application_error(20002,'smith is earning enough');
end if;
update emp set sal=sal + 500where ename='SMITH';
end;

--PRIME NO OR NOT

DECLARE
no NUMBER (3) := &no;
a NUMBER (4);
b NUMBER (2);
BEGIN
FOR i IN 2..no - 1LOOPa := no MOD i;
IF a = 0
THEN
GOTO out;
END IF;
END LOOP;
<>IF a = 1
THEN
DBMS_OUTPUT.PUT_LINE (no ' is a prime number');
ELSE
DBMS_OUTPUT.PUT_LINE (no ' is not a prime number');
END IF;
END;


--SIMPLE EXAMPLE OF LOOP STATEMENT I.E.
EXIT WHENDeclarea number:= 100;
begin
loopa := a+25;
exit when a=250;end loop;
dbms_output.put_line (to_Char(a));
end;


--EXAMPLE OF WHILE LOOP
Declare
i number:=0;
j number:= 0;
begin
while i <=100
loopj := j+1;i := i +2;
end loop;
dbms_output.put_line(to_char(i));
end;


--EXAMPLE OF FOR LOOP
Declare
beginfor i in 1..10loop
dbms_output.put_line(to_char(i));
end loop;
end;--

SEQUENTIAL CONTROL GOTO

declare--takes the default datatype of the column of the table pricecost price.minprice%type;
begin
select stdprice into cost from price where prodial in (Select prodid from product where prodese = "shampoo");
if cost > 7000 thengoto Upd;end if;<<>>Update price set minprice = 6999 where prodid=111;end;

--CALCULATE THE AREA OF A CIRCLE FOR A VALUE OF RADIUS VARYING FROM 3 TO 7. STORE THE RADIUS AND THE CORRESPONDING VALUES OF CALCULATED AREA IN A TABLE AREAS.

Declare
pi constant number(4,2) := 3.14;
radius number(5);
area number(14,2);
Beginradius := 3;
While radius <=7Looparea := pi* power(radius,2);
Insert into areas values (radius, area);
radius:= radius+1;
end loop;
end;

--REVERSING A NUMBER 5639 TO 9365
Declare
given_number varchar(5) := '5639';
str_length number(2);inverted_number varchar(5);
Begin
str_length := length(given_number);
For cntr in reverse 1..str_lengthloopinverted_number := inverted_number substr(given_number, cntr, 1);
end loop;
dbms_output.put_line('The Given no is ' given_number);
dbms_output.put_line('The inverted number is ' inverted_number);
end;