pl sql 1

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 ON


A 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 CONTROL


IF and else.....

IF --Condition THEN

--Action

ELSEIF --Condition THEN

--Action

ELSE

--Action

END IF;



SIMPLE LOOP

loop

-- Sequence of statements;

end loop;


the loop ends when u use EXIT WHEN statement --condition


WHILE LOOP

While --condition

loop

--sequence of statements

end loop;


FOR LOOP

FOR i in 1..10

loop

--sequence of statements

end loop;


GOTO (sequential control)

GOTO X;

<< X >>


EXAMPLES


--ADDITION

declare

a number;

b number;

c number;

begin

a:=&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


Declare

a number;

s1 number default 0;

Begin

a:=1;

loop

s1:=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


declare

n number;

sum1 number default 0;

endvalue number;

begin

endvalue:=&endvalue;

n:=1;

for n in 1.. endvalue

loop

if mod(n,2)=1

then

sum1:=sum1+n;

end if

end loop;

dbms_output.put_line('sum = ' || sum1);

end;


--SUM OF 100 ODD NUMBER .. WHILE LOOP


declare

n number;

endvalue number;

sum1 number default 0;

begin

endvalue:=&endvalue;

n:=1;

while (n < endvalue)

loop

sum1:=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)

then

pf:=basic * (5/100);

elsif (basic >= 3000 and basic <= 5000)

then

pf:=basic * (7/100);

elsif (basic >= 5000 and basic <= 8000)

then

pf:=basic * (8/100);

else

pf:=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;

begin

select sal into s1 from emp

where 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 + 500

where ename='SMITH';

end;


--PRIME NO OR NOT


DECLARE

no NUMBER (3) := &no;

a NUMBER (4);

b NUMBER (2);

BEGIN

FOR i IN 2..no - 1

LOOP

a := 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 WHEN


Declare

a number:= 100;

begin

loop

a := 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 loop

j := j+1;

i := i +2;

end loop;

dbms_output.put_line(to_char(i));

end;


--EXAMPLE OF FOR LOOP


Declare

begin

for i in 1..10

loop

dbms_output.put_line(to_char(i));

end loop;

end;


--SEQUENTIAL CONTROL GOTO


declare

--takes the default datatype of the column of the table price

cost price.minprice%type;

begin

select stdprice into cost from price where prodial in (Select prodid from product where prodese = "shampoo");

if cost > 7000 then

goto Upd;

end if;

<< Upd >>

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);


Begin

radius := 3;

While radius <=7

Loop

area := 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_length

loop

inverted_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;