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
Many thanks
ReplyDeleteBryan
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