DBMS-FUNCTIONS and PROCEDURES

EX.NO:5                                FUNCTIONS AND PROCEDURE

AIM:

            To write PL/SQL(Functions)and to understand stored procedures in SQL.

FUNCTION:

            A function is a subprogram that computes a value. The syntax for creating a function is given below

Create or replace function<function_name>[argument]

Return datatype is

(local declaration)

begin

            (executable statements)

[Exception]

            (exception handlers)

end

PROCEDURE:

CREATE [ORREPLACE] PROCEDURE PROCEDURENAME

                        [PARAMETER[IN/OUT/IN/IN OUT] DATATYPE

                        [:=/DEFAULT EXPRESSION]

                        [(PARAMETER)]

                        IS/AS

                        DECLARATION

                        BEGIN

                        PL/SQL CODES

                        [EXCEPTION]

                        END

Sample Output

           *****FUCTION USING FOR STATEMENT*****

  SQL>create  or replace function fact(a number)return number as

  2  i number;

  3  f number;

  4  begin

  5  f:=1;

  6  for i in 1..a

  7  loop

  8  f:=f*i;

  9  end loop;

 10  return f;

 11* end fact;

SQL> /

Function created.

*********FUNCTION USING WHILE STATEMENT *************

SQL> create or replace function fact(a number) return number as

  2   i number;

  3   f number;

  4   begin

  5   f:=1;

  6   i:=1;

  7   while (i<=a)

  8   loop

  9   f:=f*i;

 10   i:=i+1;

 11   end loop;

 12   return f;

 13*  end fact;

 14  /

Function created.

SQL>begin

  2  dbms_output.put_line('the factorial='||fact(&a));

  3* end;

SQL> /

Enter value for a: 4

old   2: dbms_output.put_line('the factorial='||fact(&a))

new   2: dbms_output.put_line('the factorial='||fact(4));

the factorial=24

PL/SQL procedure successfully completed.

*****PROCEDURE TO FIND WHETHER A GIVEN NUMBER IS ODD OR EVEN*********

SQL> declare

  2  n number;

  3  begin

  4  n:=&n;

  5  if(mod(n,2)=0)then

  6  dbms_output.put_line(n||'is even');

  7  else

  8  dbms_output.put_line(n||'is odd');

  9  end if;

 10  end;

 11  /

Enter value for n: 3

old   4: n:=&n;

new   4: n:=3;

3is odd

PL/SQL procedure successfully completed.

**********PROCEDURE TO DISPLAY 1-10 USING WHILE*******

  1   declare

  2   n number;

  3   i number;

  4   begin

  5   n:=10;

  6   i:=1;

  7   while (i<=n)

  8   loop

  9   dbms_output.put_line(i);

 10   i:=i+1;

 11   end loop;

 12*  end;

SQL> /

1

2

3

4

5

6

7

8

9

10

PL/SQL procedure successfully completed.

***********Procedure to display some numbers lesser than given number**********

  1  declare

  2  num number;

  3  i number;

  4  begin

  5  num:=&num;

  6  i:=1;

  7  loop

  8  dbms_output.put_line(i);

  9  exit when(i>num);

 10  i:=i+1;

 11  end loop;

 12* end;

SQL> /

Enter value for num: 4

old   5: num:=&num;

new   5: num:=4;

1

2

3

4

5

PL/SQL procedure successfully completed.

RESULT: Thus the functions and stored procedures are executed in SQL.