Stored Procedures ( Interbase Tutorial # 15 )

Every programming language allows programmers to create procedures, if it doesn’t, we can call it a bad programming language. A stored procedure is a program to be stored in the database server. And it can be called or run manually by other program. 

A stored procedure contains header and body. Header contains procedure name, input parameter(s) and output parameter(s). Body contains local variable(s) ( optional ) and a block of statements.

To be clear, let’s see the table below :

A Stored Procedure	Header	Procedure Name
		Input Parameter(s)
		Output Parameter(s)
	Body	Local Variable(s)
		A Block of Statements

The general syntax of a stored procedure is :

  CREATE PROCEDURE procedure_name (input_variable(s) data_type(s))
  RETURNS (output_variable(s) data_type(s))
  AS
   BEGIN
    Local variable(s)
    A block of Statements if procedure and trigger language
   END

Example :

In the previous tutorial we have learnt how to retrieve data using SELECT. Let us review again how to get employee’s name whose age is above 27. The statement is :

    select name from employee where age>27

The output is :

NAME  
============= 
Isaac Newton
Archimedes
Frank Borland
Max Planck

Now we can get same output, but this time we’re going to create a procedure. The procedure will be :

  create procedure get_name (employee_age integer)
  returns (employee_name char(30))
  as
  begin
    for select name
        from employee
        where age > :employee_age
        into :employee_name
    do
        suspend;
  end

suspend send the value back to the calling application ( Interbase Windows ISQL )

Execute the procedure above in the SQL windows. Now execute this blow statement :

    select * from get_name(27)

You’ll get the same output :

NAME  
============= 
Isaac Newton
Archimedes
Frank Borland
Max Planck

Using Local Variables

Let us modify the procedure above using a local variable

  create procedure get_name_line (employee_age integer)
  returns (line_number integer, employee_name char(30))
  as
  begin
   line_number = 0;
   for select name
       from employee
       where age > :employee_age
       into :employee_name
   do
    begin
     line_number=line_number+1;
     suspend;
    end
  end

after you execute the procedure, you can try this statement below :

    select * from get_name_line(27)

The output is :

LINE_NUMBER   EMPLOYEE_NAME  
============================== 
          1   Isaac Newton 
          2   Archimedes
          3   Frank Borland 
          4   Max Planck

Go to Previous Tutorial or Next Tutorial