Friday, 26 February 2010

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 ProcedureHeaderProcedure Name
Input Parameter(s)
Output Parameter(s)
BodyLocal 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

2 comments:

  1. Many thanks
    Bryan

    ReplyDelete
    Replies
    1. s The best and a lot of secret drop-shipping suppliers in the world which will provide your products globally available for you directly to your customers doors. Also get the best drop-shipping manufacturer on your organization.

      Delete

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.