PL/SQL BLOCKS

PL/SQL BLOCKS

 

BASIC PL/SQL BLOCKS

/* multiline comments  */

–Single line comment

 

1 –First program

 

–DECLARE

BEGIN

dbms_output.put_line(‘Hello Students !! ‘);

END;

 

2 DECLARE

nm varchar2(15);

a CONSTANT varchar2(10):=’hello';

BEGIN

nm:=’&name';

dbms_output.put_line(a||’  ‘||nm);

END;

 

3 /* Write a block that accept empno from user and  display the salary of that emp */

/* SELECT….INTO Statement in Block */

DECLARE

sal_var emp.sal%type;

BEGIN

select sal into sal_var from emp where empno=&eno;

dbms_output.put_line(‘salary  is ‘||sal_var);

END;

 

4 /* Write a block that accept empno from user and  update salary by 1000 Rs */

/* UPDATE Statement in Block */

DECLARE

BEGIN

update emp set sal=sal+1000 where empno=&eno;

dbms_output.put_line(‘ Salary has been updated ! ‘ );

END;

5 /* write a block which accept the empno and update salary of that employee.Also accept the salary to be updated. */

declare

old_sal emp.sal%type;

new_sal emp.sal%type;

eno emp.empno%type;

begin

eno:=&&empno;

new_sal:=&new_salary;

select sal into old_sal from emp where empno=eno;  –Storing OLD salary

update emp set sal=new_sal where empno=eno;

dbms_output.put_line(‘salary of empno ‘||eno||’ has been changed from ‘||old_sal ||’ to ‘||new_sal);

commit;

end;

6 /* WAB to insert values in emp table.  */

/* INSERT Statement in Block */

declare

begin

insert into emp(empno,empname,sal) values(&empno,’&empname’,&sal);

end;

 

7 –WAB which accept the empno and delete that record from the table emp.

/* DELETE Statement in Block  */

BEGIN

DELETE FROM emp where empno=&empno;

END;

8 /* Write a block that accept empno from user and  display the details of that emp */

– Use of %ROWTYPE

declare

e1 emp%ROWTYPE;

begin

select empno,ename,sal,job into e1.empno, e1.ename, e1.sal, e1.job  from emp                 where empno=&eno;

dbms_output.put_line(‘empno  is ‘||e1.empno);

dbms_output.put_line(‘ename  is ‘||e1.ename);

dbms_output.put_line(‘Salary  is ‘||e1.sal);

dbms_output.put_line(‘job  is ‘||e1.job);

end;

 

9. /* WAB which accepts empno from user. If salary of that employee is less than 20000, increment it by 10% of salary. Insert the empno and updated salary in the table UPDATED_EMP(eno,sal,oper_date);

EMP(empno, ename,salary);   */

 

DECLARE

eno emp.empno%TYPE;

sal emp.salary%TYPE;

s number;

BEGIN

eno:=&empno;

SELECT salary INTO sal FROM emp empno=eno;

if sal < 20000 then

UPDATE emp set salary=salary+(salary*0.10) where empno=eno;

s:=sal+(sal*0.10);

INSERT INTO updated_emp VALUES(eno,s,sysdate);

end if;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT_PUT(‘data not found’);

 

END;

 

Nested PL/SQL Blocks

10 /* Demo of Nested blocks and how to access the variable of outer block*/

 

<<b1>>

DECLARE

nm varchar2(10):=’Pal';

BEGIN

dbms_output.put_line(‘b1.nm = ‘||b1.nm);

<<b2>>

DECLARE

                nm varchar2(10):=’Sanvi';

BEGIN

dbms_output.put_line(‘b2.nm = ‘||b2.nm);

dbms_output.put_line(‘b1.nm = ‘||b1.nm);

END b2;

END b1;

/

 

11 /* Demo of Nested blocks and how to access the variable of outer block*/

SET SERVEROUTPUT ON

<< outer_block >>

DECLARE

no NUMBER := 123;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Outer Block, no: ‘||no);

<< inner_block >>

DECLARE

no NUMBER := 456;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Inner Block, no: ‘||no);

DBMS_OUTPUT.PUT_LINE(‘Inner Block, outer_block.no: ‘||Outer_block.no);

END inner_block;

END outer_block;

This example produces the following output:

Outer Block, no: 123

Inner Block, no: 456

Inner Block, outer_block.no: 123

12 << outer_block >>

DECLARE

v_test NUMBER := 123;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Outer Block, v_test: ‘||v_test);

<< inner_block >>

DECLARE

v_test NUMBER := 456;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Inner Block, v_test: ‘||v_test);

DBMS_OUTPUT.PUT_LINE(‘Inner Block, outer_block.v_test: ‘||Outer_block.v_test);

 

<<most_inner_block>>

DECLARE

v_test number:=999;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Most Inner Block, v_test: ‘||v_test);

DBMS_OUTPUT.PUT_LINE(‘Inner Block, inner_block.v_test: ‘||inner_block.v_test);

DBMS_OUTPUT.PUT_LINE(‘Inner Block, outer_block.v_test: ‘||Outer_block.v_test);

END most_inner_block;

 

END inner_block;

END outer_block;

/

OUTPUT:

Outer Block, v_test: 123

Inner Block, v_test: 456

Inner Block, outer_block.v_test: 123

Most Inner Block, v_test: 999

Inner Block, inner_block.v_test: 456

Inner Block, outer_block.v_test: 123

   
13 /* Get the age from user and display message whether user is eligible   for votting or not.  */

– USE of if…else

 

DECLARE

                age number(3);

BEGIN

                age:=&&age;

                if age>18 then

                                dbms_output.put_line(‘Eligible for votting’);

                else

                                dbms_output.put_line(‘NOT Eligible for votting’);

                end if;

END;

/

   
 

USE of if elsif….else

   
14 – Write a block to print odd nos up to 10 using while loop .

declare

                x number:=1;

begin

                while x<10 loop

                                dbms_output.put_line(x);

                                x:=x+2;

                end loop;

 

end;

   
15 /*  Write a block which accept one no from user and display the message whether that no is prime or not. */

 

declare

   x number:=1;

   flag number:=0;

   no number;

   r number;

begin

                 x:=&x;

                no:=x-1;

                while no>1 loop

                                r:=mod(x,no);

                                if r=0 then

                                                flag:=1;

                                end if;

                no:=no-1;

                end loop;

                if flag=0 then

                                dbms_output.put_line(‘no is prime’);

                else

                                dbms_output.put_line(‘no is not prime’);

                end if;

end;

/

   
16 //Print the prime nos between the range from 1 to 20.

 

declare

    x number:=1;

    flag number:=0;

    no number;

    r number;

 begin

    while x<20 loop

   flag:=0;

                  no:=x-1;

            while no>1 loop

                    r:=mod(x,no);

                    if r=0 then

                            flag:=1;

                exit;

                    end if;

            no:=no-1;

            end loop;

            if flag=0 then

                    dbms_output.put_line(x);

            end if;

            x:=x+1;

    end loop;

 end;

/

17 –Write a program which display the square of all nos whose square is less than 50.

– USE of LOOP….END LOOP

declare

                no number(4):=1;

                sqr number(4);

begin

                loop

                                sqr:=power(no,2);

                                exit when sqr>50;             — if sqr>50 then 

                                no:=no+1;                                  — exit;        end if;

                                dbms_output.put_line(sqr);

                end loop;

end;

   
18 –printing the nos from 1 to upto given range.

 

declare

                no number(2);

begin

                no:=&no;

                for i in reverse 1..no

                loop

                                dbms_output.put_line(i);

                end loop;

end;

/

19 – Program to print odd numbers.

declare

                no number(5);

begin

 

                for x in 1..10

                loop

                                if mod(x,2) !=0 then

                                                dbms_output.put_line(x);

                                end if;

               

                end loop;           

 

end;

20 – Program to print reverse no.    i.e. 1234 -> 4321

 

declare

                no number(9);

                x number(9);

                r number(3);

                sum1 number(5):=0;

begin

                no:=&no;

                for i in 1..10

                loop

                                if no <> 0 then

                                r:=mod(no,10);

                                sum1:=(sum1*10)+r;

                                no:=no/10;

                                end if;

                end loop;

                dbms_output.put_line(sum1);

end;

/

   
21 /* WAB which accepts the empno from user and increment the salary by 1000 if the salary is less then 20000. */

– Use of GO TO label

 

DECLARE

                sal number(6);

                eno number(3);

BEGIN

                eno:=&empno;

                select salary into sal from emp

                where empno=eno;

                if sal<20000 then

                                goto increment;

                else

                                dbms_output.put_line(‘salary is not incremented’);

                end if;

<<increment>>

                update emp set salary=salary+1000

                                where empno=eno;

END;

/

   

 

 

 

About these ads

Tagged: , , , , , , , , , , , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: