|
Using Stored Procedures |
|
|
|
Using Stored Procedures |
|
This tutorial describes how to work with stored procedures using the TIBCStoredProc component to insert data into tables.
This tutorial assumes that you have already connected to the server (see Connecting to InterBase and Firebird, created the necessary objects on the server (see Creating Database Objects), and inserted data into tables (see Inserting Data Into Tables). To insert data at runtime, add the IBC unit to the uses clause for Delphi or include the IBC.hpp header file for C++ Builder.
A stored procedure is a group of one or more SQL statements grouped as a logical unit and stored in the database. Stored procedures are intended to perform a specific task or a set of related tasks. They combine the ease and flexibility of the SQL language with the procedural functionality of a structured programming language. Complicated business rules and programming logic that may require execution of multiple SQL statements should be kept in stored procedures, which can be called by the client applications.
A stored function is similar to a stored procedure, but there are some differences: a function must return a value, whereas in a stored procedure it is optional; a function can have only input parameters, whereas a procedures can have input or output parameters; a function can be called from a procedure, whereas a procedure cannot be called from a function.
Input parameters are used to pass values from the calling program to the stored procedure. If the procedure changes the input value, the change has effect only within the procedure, and the input variable will preserve its original value when control is returned to the calling program. The following procedure inserts a new row into the table dept:
CREATE PROCEDURE InsertDept(
p_deptno integer,
p_dname varchar(14),
p_loc varchar(13)
) AS
BEGIN
INSERT INTO dept(deptno, dname, loc) VALUES(:p_deptno, :p_dname, :p_loc);
END;The procedure accepts three input arguments that correspond to the fields of the table, and can be executed as follows:
EXECUTE PROCEDURE InsertDept(10,'ACCOUNTING','NEW YORK');
The code below demonstrates the use of the TIBCStoredProc component to execute the InsertDept stored procedure:
Delphi
var
IBCStoredProc1: TIBCStoredProc;
begin
IBCStoredProc1 := TIBCStoredProc.Create(nil);
try
// IBCConnection1 was set up earlier
IBCStoredProc1.Connection := IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1.StoredProcName := 'InsertDept';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1.PrepareSQL;
// searches parameters by their names and assigns new values
IBCStoredProc1.ParamByName('p_deptno').AsInteger := 10;
IBCStoredProc1.ParamByName('p_dname').AsString := 'ACCOUNTING';
IBCStoredProc1.ParamByName('p_loc').AsString := 'NEW YORK';
// executes the stored procedure
IBCStoredProc1.Execute;
finally
IBCStoredProc1.Free;
end;
end;C++Builder
TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
// IBCConnection1 was set up earlier
IBCStoredProc1->Connection = IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1->StoredProcName = "InsertDept";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1->PrepareSQL();
// searches parameters by their names and assigns new values
IBCStoredProc1->ParamByName("p_deptno")->AsInteger = 10;
IBCStoredProc1->ParamByName("p_dname")->AsString = "ACCOUNTING";
IBCStoredProc1->ParamByName("p_loc")->AsString = "NEW YORK";
// executes the stored procedure
IBCStoredProc1->Execute();
}
__finally {
IBCStoredProc1->Free();
}
Output parameters are used to return values from the procedure to the calling application. The initial value of the parameter in the procedure is NULL, and the value becomes visible to the calling program only when the procedure returns it. The following stored procedure returns the count of records in the dept table:
CREATE PROCEDURE CountDept
RETURNS (cnt integer)
BEGIN
SELECT count(*) FROM dept into cnt;
END;
The code below demonstrates the use of the TIBCStoredProc component to execute the CountDept stored procedure:
Delphi
var
IBCStoredProc1: TIBCStoredProc;
begin
IBCStoredProc1 := TIBCStoredProc.Create(nil);
try
// IBCConnection1 was set up earlier
IBCStoredProc1.Connection := IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1.StoredProcName := 'CountDept';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1.PrepareSQL;
// executes the stored procedure
IBCStoredProc1.Execute;
// shows the value of the output parameter
ShowMessage(IntToStr(sp.ParamByName('cnt').AsInteger));
finally
IBCStoredProc1.Free;
end;
end;C++Builder
TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
// IBCConnection1 was set up earlier
IBCStoredProc1->Connection = IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1->StoredProcName = "CountDept";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1->PrepareSQL();
// executes the stored procedure
IBCStoredProc1->Execute();
// shows the value of the output parameter
ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
}
__finally {
IBCStoredProc1->Free();
}
A stored procedure that contains input and output parameters can both accept and return values. Programs can pass a value to the stored procedure, which does something under the hood, and passes the resulting value back to the calling program. The input value must be set before executing the stored procedure. The output value is returned after executing the stored procedure.
The following stored procedure returns the salary with a 5% percent bonus:
CREATE PROCEDURE GiveBonus(sal float)
RETURNS (bonus float)
AS
BEGIN
bonus=sal * 1.05;
END;
The code below demonstrates the use of the TIBCStoredProc component to execute the GiveBonus stored procedure:
Delphi
var
IBCStoredProc1: TIBCStoredProc;
begin
IBCStoredProc1 := TIBCStoredProc.Create(nil);
try
// IBCConnection1 was set up earlier
IBCStoredProc1.Connection := IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1.StoredProcName := 'GiveBonus';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1.PrepareSQL;
// searches a parameter by its name and assigns a new value
IBCStoredProc1.ParamByName('sal').AsFloat := 500.5;
// executes the stored procedure
IBCStoredProc1.Execute;
// shows the resulting value
ShowMessage(IBCStoredProc1.ParamByName('sal').AsString);
finally
IBCStoredProc1.Free;
end;
end;C++Builder
TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
// IBCConnection1 was set up earlier
IBCStoredProc1->Connection = IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1->StoredProcName = "GiveBonus";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1->PrepareSQL();
// searches a parameter by its name and assigns a new value
IBCStoredProc1->ParamByName("sal")->AsFloat = 500.5;
// executes the stored procedure
IBCStoredProc1->Execute();
// shows the resulting value
ShowMessage(IBCStoredProc1->ParamByName("sal")->AsString);
}
__finally {
IBCStoredProc1>Free();
}
The tasks above can also be accomplished using stored functions in Firebird. For example, the following stored function returns the salary with a 5% percent bonus:
CREATE FUNCTION GiveBonusFunc(sal float)
RETURNS float
AS
BEGIN
RETURN sal * 1.05;
END;This function can be executed as follows:
SELECT GiveBonusFunc(500.5);
The code below demonstrates the use of the TIBCStoredProc component to execute the GiveBonusFunc stored function:
Delphi
var
IBCStoredProc1: TIBCStoredProc;
begin
IBCStoredProc1 := TIBCStoredProc.Create(nil);
try
// IBCConnection1 was set up earlier
IBCStoredProc1.Connection := IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1.StoredProcName := 'GiveBonusFunc';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1.PrepareSQL;
// searches a parameter by its name and assigns a new value
IBCStoredProc1.ParamByName('sal').AsFloat := 500.5;
// executes the stored procedure
IBCStoredProc1.Execute;
// shows the resulting value
ShowMessage(IBCStoredProc1.ParamByName('result').AsString);
finally
IBCStoredProc1.Free;
end;
end;C++Builder
TIBCStoredProc* IBCStoredProc1 = new TIBCStoredProc(NULL);
try {
// IBCConnection1 was set up earlier
IBCStoredProc1->Connection = IBCConnection1;
// indicates the name of the stored procedure to call
IBCStoredProc1->StoredProcName = "GiveBonusFunc";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
IBCStoredProc1->PrepareSQL();
// searches a parameter by its name and assigns a new value
IBCStoredProc1->ParamByName("sal")->AsFloat = 500.5;
// executes the stored procedure
IBCStoredProc1->Execute();
// shows the resulting value
ShowMessage(IBCStoredProc1->ParamByName("result")->AsString);
}
__finally {
IBCStoredProc1->Free();
}
}Note: To retrieve the result returned by the stored function using TIBCStoredProc, use the automatically created 'result' parameter.
Besides scalar variables, a stored procedure can return a result set generated by the SELECT statement. See Using Stored Procedures with Result Sets for more information.