|
Using Stored Procedures |
|
|
|
Using Stored Procedures |
|
This tutorial describes how to work with stored procedures using the TPgStoredProc component to insert data into tables.
This tutorial assumes that you have already connected to the server (see Connecting to PostgreSQL, 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 PgAccess unit to the uses clause for Delphi or include the PgAccess.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 or function. 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 int,
p_dname varchar(14),
p_loc varchar(13)
)
AS $$
INSERT INTO dept(deptno, dname, loc) VALUES(:p_deptno, :p_dname, :p_loc);
$$ LANGUAGE sql;
The procedure accepts three input arguments that correspond to the fields of the table, and can be executed as follows:
CALL InsertDept(10,'ACCOUNTING','NEW YORK');
The code below demonstrates the use of the TPgStoredProc component to execute the InsertDept stored procedure:
Delphi
var
PgStoredProc1: TPgStoredProc;
begin
PgStoredProc1 := TPgStoredProc.Create(nil);
try
// PgConnection1 was set up earlier
PgStoredProc1.Connection := PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1.StoredProcName := 'InsertDept';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1.PrepareSQL;
// searches parameters by their names and assigns new values
PgStoredProc1.ParamByName('p_deptno').AsInteger := 10;
PgStoredProc1.ParamByName('p_dname').AsString := 'ACCOUNTING';
PgStoredProc1.ParamByName('p_loc').AsString := 'NEW YORK';
// executes the stored procedure
PgStoredProc1.Execute;
finally
PgStoredProc1.Free;
end;
end;C++Builder
TPgStoredProc* PgStoredProc1 = new TPgStoredProc(NULL);
try {
// PgConnection1 was set up earlier
PgStoredProc1->Connection = PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1->StoredProcName = "InsertDept";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1->PrepareSQL();
// searches parameters by their names and assigns new values
PgStoredProc1->ParamByName("p_deptno")->AsInteger = 10;
PgStoredProc1->ParamByName("p_dname")->AsString = "ACCOUNTING";
PgStoredProc1->ParamByName("p_loc")->AsString = "NEW YORK";
// executes the stored procedure
PgStoredProc1->Execute();
}
__finally {
PgStoredProc1->Free();
}
Output parameters are used to return values from the procedure or function 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. PostgreSQL supports output parameters only in stored functions. The following stored function returns the count of records in the dept table:
CREATE FUNCTION CountDept(cnt int)
RETURNS int AS $$
BEGIN
RETURN (SELECT count(*) FROM dept);
END;
$$ LANGUAGE plpgsql;
The code below demonstrates the use of the TPGStoredProc component to execute the CountDept stored procedure:
Delphi
var
PgStoredProc1: TPgStoredProc;
begin
PgStoredProc1 := TPgStoredProc.Create(nil);
try
// PgConnection1 was set up earlier
PgStoredProc1.Connection := PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1.StoredProcName := 'CountDept';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1.PrepareSQL;
// executes the stored procedure
PgStoredProc1.Execute;
// shows the value of the output parameter
ShowMessage(IntToStr(PgStoredProc1.ParamByName('result').AsString));
finally
PgStoredProc1.Free;
end;
end;
C++Builder
TPGStoredProc* PGStoredProc1 = new TPGStoredProc(NULL);
try {
// PGConnection1 was set up earlier
PGStoredProc1->Connection = PGConnection1;
// indicates the name of the stored procedure to call
PGStoredProc1->StoredProcName = "CountDept";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PGStoredProc1->PrepareSQL();
// executes the stored procedure
PGStoredProc1->Execute();
// shows the value of the output parameter
ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
}
__finally {
PGStoredProc1->Free();
}
Input/output parameters (INOUT) act as the IN or OUT parameter, or both. Programs can pass a value to the stored procedure or function, which changes it and returns the updated value. The input value must be set before executing the stored procedure. INOUT parameters act like an initialized variables.
The following stored procedure returns the salary with a 5% percent bonus:
CREATE PROCEDURE GiveBonus(INOUT sal real)
AS $$
BEGIN
sal = sal * 1.05;
END;
$$ LANGUAGE plpgsql;
The code below demonstrates the use of the TPgStoredProc component to execute the GiveBonusFunc stored function:
Delphi
var
PgStoredProc1: TPgStoredProc;
begin
PgStoredProc1 := TPgStoredProc.Create(nil);
try
// PgConnection1 was set up earlier
PgStoredProc1.Connection := PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1.StoredProcName := 'GiveBonus';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1.PrepareSQL;
// searches a parameter by its name and assigns a new value
PgStoredProc1.ParamByName('sal').AsFloat := 500.5;
// executes the stored procedure
PgStoredProc1.Execute;
// shows the resulting value
ShowMessage(PgStoredProc1.ParamByName('sal').AsString);
finally
PgStoredProc1.Free;
end;
end;C++Builder
TPgStoredProc* PgStoredProc1 = new TPgStoredProc(NULL);
try {
// PgConnection1 was set up earlier
PgStoredProc1->Connection = PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1->StoredProcName = "GiveBonus";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1->PrepareSQL();
// searches a parameter by its name and assigns a new value
PgStoredProc1->ParamByName("sal")->AsFloat = 500.5;
// executes the stored procedure
PgStoredProc1->Execute();
// shows the resulting value
ShowMessage(PgStoredProc1->ParamByName("sal")->AsString);
}
__finally {
PgStoredProc1>Free();
}
The tasks above can also be accomplished using stored functions in PostgreSQL. For example, the following stored function returns the salary with a 5% percent bonus:
CREATE FUNCTION GiveBonusFunc(sal real)
RETURNS real AS $$
BEGIN
RETURN sal * 1.05;
END;
$$ LANGUAGE plpgsql;
This function can be executed as follows:
SELECT GiveBonusFunc(500.5);
The code below demonstrates the use of the TPgStoredProc component to execute the GiveBonusFunc stored function:
Delphi
var
PgStoredProc1: TPgStoredProc;
begin
PgStoredProc1 := TPgStoredProc.Create(nil);
try
// PgConnection1 was set up earlier
PgStoredProc1.Connection := PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1.StoredProcName := 'GiveBonusFunc';
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1.PrepareSQL;
// searches a parameter by its name and assigns a new value
PgStoredProc1.ParamByName('sal').AsFloat := 500.5;
// executes the stored procedure
PgStoredProc1.Execute;
// shows the resulting value
ShowMessage(PgStoredProc1.ParamByName('result').AsString);
finally
PGStoredProc1.Free;
end;
end;C++Builder
TPgStoredProc* PgStoredProc1 = new TPgStoredProc(NULL);
try {
// PgConnection1 was set up earlier
PgStoredProc1->Connection = PgConnection1;
// indicates the name of the stored procedure to call
PgStoredProc1->StoredProcName = "GiveBonusFunc";
// constructs a statement based on the Params and StoredProcName
// properties, and assigns it to the SQL property
PgStoredProc1->PrepareSQL();
// searches a parameter by its name and assigns a new value
PgStoredProc1->ParamByName("sal")->AsFloat = 500.5;
// executes the stored procedure
PgStoredProc1->Execute();
// shows the resulting value
ShowMessage(PgStoredProc1->ParamByName("result")->AsString);
}
__finally {
PgStoredProc1->Free();
}
}Note: To retrieve the result returned by the stored function using TPgStoredProc, use the automatically created 'result' parameter.
Besides scalar variables, a stored function can return a result set generated by the SELECT statement. See Using Stored Functions with Result Sets for more information.