MyDAC

Stored Procedures

This tutorial describes how to work with stored procedures using the TMyStoredProc component and insert data into tables.

  1. Requirements
  2. General information
  3. Input parameters
  4. Output parameters
  5. Input/output parameters
  6. Using MySQL Stored Functions
  7. Returning result sets

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To MySQL" and "Connecting To MySQL Embedded"), how to create necessary objects on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

General information

A stored procedure is a database object that consists of a set of SQL statements, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Large or complex processing that might require execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.

Objects similar to stored procedures are stored functions. Almost everything that is true for procedures, holds for functions as well. The main difference between these objects is that function has a return value, and procedure has not. Also, stored procedures may have input, output, and input/output parameters.

Note: stored procedures and stored functions are supported since MySQL 5.0.

Input parameters

Input parameter (IN) is a parameter which value is passed into a stored procedure/function module. The procedure might modify the value, but the modification is not visible to the caller when the procedure is returned. The following procedure inserts a new row into the table dept:

CREATE PROCEDURE InsertDept(
IN p_deptno INT,
IN p_dname VARCHAR(14),
IN p_loc VARCHAR(13)
)
BEGIN
  INSERT INTO dept(deptno, dname, loc) VALUES(p_deptno, p_dname, p_loc);
END;

It needs to receive the values to be inserted into the new record, and thus the procedure has three input parameters, corresponding to each field of the table. This procedure may be executed as follows:

call InsertDept(10,'ACCOUNTING','NEW YORK');

To execute the InsertDept stored procedure using the TMyStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMyStoredProc;
begin
  sp := TMyStoredProc.Create(nil);
  try
    // con is either TMyConnection or TMyEmbConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'InsertDept';

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // assign parameter values
    sp.ParamByName('p_deptno').AsInteger := 10;
    sp.ParamByName('p_dname').AsString := 'ACCOUNTING';
    sp.ParamByName('p_loc').AsString := 'NEW YORK';

    // execute the stored procedure
    sp.Execute;
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMyStoredProc* sp = new TMyStoredProc(NULL);
  try
  {
    // con is either TMyConnection or TMyEmbConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "InsertDept";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // assign parameter values
    sp->ParamByName("p_deptno")->AsInteger = 10;
    sp->ParamByName("p_dname")->AsString = "ACCOUNTING";
    sp->ParamByName("p_loc")->AsString = "NEW YORK";

    // execute the stored procedure
    sp->Execute();
  }
  __finally
  {
    sp->Free();
  }
}

Output parameters

Output parameter (OUT) is a parameter which value is passed out of the stored procedure/function module. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure is returned. The following stored procedure returns the count of records in the table dept:

CREATE PROCEDURE CountDept (
OUT cnt INT
)
BEGIN
  SELECT count(*) FROM dept into cnt;
END;

To execute the CountDept stored procedure using the TMyStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMyStoredProc;
begin
  sp := TMyStoredProc.Create(nil);
  try
    // con is either TMyConnection or TMyEmbConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'CountDept';

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // execute the stored procedure
    sp.Execute;

    // show the value of the output parameter
    ShowMessage(IntToStr(sp.ParamByName('cnt').AsInteger));
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMyStoredProc* sp = new TMyStoredProc(NULL);
  try
  {
    // con is either TMyConnection or TMyEmbConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "CountDept";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // execute the stored procedure
    sp->Execute();

    // show the value of the output parameter
    ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
  }
  __finally
  {
    sp->Free();
  }
}

Input/output parameters

An input/output parameter (INOUT) is a parameter that functions as an IN or an OUT parameter, or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. It can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.

For example, the following stored procedure returns the salary with five percents bonus:

CREATE PROCEDURE GiveBonus(INOUT sal FLOAT)
BEGIN
  SET sal = sal * 1.05;
END

To execute the GiveBonus stored procedure using the TMyStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMyStoredProc;
begin
  sp := TMyStoredProc.Create(nil);
  try
    // con is either TMyConnection or TMyEmbConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'GiveBonus';

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // assign parameter values
    sp.ParamByName('sal').AsFloat := 500.5;

    // execute the stored procedure
    sp.Execute;

    // show the value of the input/output parameter
    ShowMessage(sp.ParamByName('sal').AsString);
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMyStoredProc* sp = new TMyStoredProc(NULL);
  try
  {
    // con is either TMyConnection or TMyEmbConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "GiveBonus";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp->PrepareSQL();

    // assign parameter values
    sp->ParamByName("sal")->AsFloat = 500.5;

    // execute of the stored procedure
    sp->Execute();

    // show the value of the input/output parameter
    ShowMessage(sp->ParamByName("sal")->AsString);
  }
  __finally
  {
    sp->Free();
  }
}

Using MySQL Stored Functions

The tasks described above can be performed using stored functions. For example, the following stored function returns the bonus salary like the GiveBonus stored procedure:

CREATE FUNCTION GiveBonusFunc(sal FLOAT)
RETURNS FLOAT
BEGIN
  RETURN sal * 1.05;
END

This function may be executed as follows:

select GiveBonusFunc(500.5);

To execute the GiveBonusFunc stored function using the TMyStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMyStoredProc;
begin
  sp := TMyStoredProc.Create(nil);
  try
    // con is either TMyConnection or TMyEmbConnection already set up
    sp.Connection := con;

    // choose a stored function name to execute
    sp.StoredProcName := 'GiveBonusFunc';

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // assign parameter values
    sp.ParamByName('sal').AsFloat := 500.5;

    // execute the stored procedure
    sp.Execute;

    // show the returned value
    ShowMessage(sp.ParamByName('result').AsString);
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMyStoredProc* sp = new TMyStoredProc(NULL);
  try
  {
    // con is either TMyConnection or TMyEmbConnection already set up
    sp->Connection = con;

    // choose a stored function name to execute
    sp->StoredProcName = "GiveBonusFunc";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp->PrepareSQL();

    // assign parameter values
    sp->ParamByName("sal")->AsFloat = 500.5;

    // execute of the stored procedure
    sp->Execute();

    // show the returned value
    ShowMessage(sp->ParamByName("result")->AsString);
  }
  __finally
  {
    sp->Free();
  }
}

Note: To retrieve the result returned by the stored function using TMyStoredProc, the 'result' parameter created automatically should be used.

Returning result sets

Besides scalar variables, a stored procedure can return result sets, i.e. the results of a SELECT statement. This question is discussed in details in the tutorial "Working With Result Sets Using Stored Procedures".

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback