Procedures

SQL also supports procedures, which return multiple values.

The dept_count function could instead be written as a procedure.

Example-1 :

Following is a procedure developed that is given a name of the department, It returns the count of the number of faculty in that department.

Create procedure dept_count_proc
(in  dept_name  varchar2(20) ,
Out  d_count  integer ) :
Begin
Select count(FNo) into d_count
From DEPT, FACULTY
Where FACULTY.DNo = DEPT.DNo
and DName = dept_name
End

Procedures can be invoked either from an SQL procedure or from embedded SQL by the ‘call’ statement.

declare  d_count    integer ;
call  dept_count_proc (‘ECE’, d_count) ;

Persistent Storage Module :

In this module, variables are declared using the ‘declare’ statement.

Assignments are performed with a ‘set’ statement.

A compound statement is of the form ‘begin…..end’ and it may contain multiple SQL statements between ‘begin’ and ‘end’.

There are four control statements in PSM, as follows :

• While: The while statement can be used to write a block of commands for repeated execution of the SQL command. It has the LEAVE statement built-in.

Syntax: 

WHILE <search condition> DO
 <SQL statement(s)>
END WHILE

• Repeat: The repeat statement is like the while statement, but the condition is checked after the execution of the statements.

Syntax:

REPEAT
 <SQL statement(s)> UNTIL <search condition>
END REPEAT

• For: The execution takes place of each row of the result set. This is used to make the FETCH loops simpler. 

Syntax:

FOR <loop variable name> AS [ <Cursor name>
   [ {ASENSITIVE | INSENSITIVE | SENSITIVE} ] CURSOR FOR ]
   <query expression> [ ORDER BY clause ] [ updatability clause ]
DO
  <SQL statement(s)>
END FOR

• If: The if statements are very much like every other if statements, if the condition is satisfied, then execute the command.

Syntax:

IF <search condition> THEN <SQL statement(s)>
   ELSEIF <search condition> THEN <SQL statement(s)>
   ELSE <SQL statement(s)>
END IF