Stored procedure are functions that are executed in the database directly. Like other functions they have parameters to pass data into the stored procedure and they return data that can be read from php. The logic is written in SQL with additional commands like IF or ELSE and can define variables and temporary tables. The advantage of stored procedures: They are precompiled by the database so they are really fast compared to ad hoc sql. Furthermore they are really close to the data. So instead of selecting all data and processing it in php which requires all data being send to php, the selection logic can be build within a stored procedure and only the relevant data is passed back to php.
However creating stored procedures and invoking them is not easy and requires a lot of action by the programmer. Siesta tries to offer an infrastructure that allows to easily create and invoke stored procedures by php.

• Stored Procedures are defined in the entity xml where they "belong to"
• A method will be generated into the service class of the entity
• All parameters are escaped
• Return types can be an entity, a list of entity, a ResultSet or none

Every stored procedures defined in the xml will have a method in the generated Service class.
Creating stored proecedures with mysql

A common use case is to define finder methods for finding a specific object. Therefore a stored procedure section can be added to entity. The following example shows a very simple stored procedure.

First a parameter city is defined. It has a name a php type and a database type dbType. The spName will be the name of the parameter visible in the stored procedure body.

You can see this within the <sql> element. The WHERE clause uses the P_CITY parameter. Furthermore for the table name a placeholder !TABLE! can be used. The stored procedure itself defines if the procedure modifies table or not. Since a SELECT does not modify tables, the modify attribute is set to false. You can also define the resultType. In this case we want a single object. (resultType="single").

    <storedProcedure name="getFirstArtistByCity" modifies="false" resultType="single">
        <parameter name="city" type="string" spName="P_CITY" dbType="VARCHAR(100)"/>
        <sql>
            SELECT * FROM !TABLE! WHERE CITY = P_CITY LIMIT 1;
        </sql>
    </storedProcedure>

Siesta will add the following method to the class in which the stored procedure is defined. The parameter defined in the <parameter> element is visible as parameter in the method. The logic inside the function will invoke the generated stored procedure and return a single object.

    /**
     * @param string $city
     * @param string $connectionName
     *
     * @return Artist
     */
    public function getFirstArtistByCity(string $city = null, string $connectionName = null)

In other cases you want to return a list of objects rather a single one. You just have to change the resultType to "list".

    <storedProcedure name="getArtistByCity" modifies="false" resultType="list">
        <parameter name="city" spName="P_CITY" type="string" dbType="VARCHAR(100)"/>
        <sql>
            SELECT * FROM !TABLE! WHERE CITY = P_CITY;
        </sql>
    </storedProcedure>

Siesta will generate the following static method into the entity class. The method will invoke the generated stored procedure and return a list of Artist objects.

    /**
     * @param string $city
     * @param string $connectionName
     *
     * @return Artist[]
     */
    public function getArtistByCity(string $city = null, string $connectionName = null)

The third use case supported by siesta is the executing of SQL that does not return objects. Therefore you can define the resultType as resultSet. This will return a ResultSet object.

    <storedProcedure name="countArtistByCity" modifies="false" resultType="resultSet">
        <parameter name="city" spName="P_CITY" type="string" dbType="VARCHAR(100)"/>
        <sql>
            SELECT COUNT(ID) FROM !TABLE! WHERE CITY = P_CITY;
        </sql>
    </storedProcedure>

Siesta will generate the following method into the service class. The method will invoke the generated stored procedure and return a ResultSet object.

    /**
     * @param string $city
     * @param string $connectionName
     *
     * @return ResultSet
     */
    public function countArtistByCity(string $city, string $connectionName = null) : ResultSet

ResultSet are an abstraction of the database result. It allows to iterate with hasNext() method and access the data including casting like getIntegerValue("COLUMN_NAME") See driver section for details on ResultSet.

    $countArtistResult = Artist::countArtistByCity("Vienna");
    while ($countArtistResult->hasNext()) {
        $count = $countArtistResult->getIntegerValue("COUNT(ID)");
    }
    $countArtistResult->close();

If you simply want to perform an update operation use the resultType none.

    <storedProcedure name="updateSomething" modifies="false" resultType="none">
        <parameter name="param" spName="P_STRING_PARAM" type="string" dbType="VARCHAR(100)"/>
        <sql>
            UPDATE E2ECustomStoredProcedure SET `column1` = P_STRING_PARAM, `column2` = 4;
        </sql>
    </storedProcedure>

Siesta will generate the following static method into the entity class. The method will invoke the generated stored procedure and return a ResultSet object.

    /**
     * @param string $param
     * @param string $connectionName
     *
     * @return void
     */
    public function updateSomething(string $param, $connectionName = null)

As explained in Entity replication siesta allows to generate every table twice. Once with the memory engine and once with the configured engine. To make sure that this logic also works with custom defined stored procedures you set the attribute modifies="true|false". This way siesta knows if the provided sql should process only on the memory table or on both. Therefore you also use the tableplace holder !TABLE!. In case of modifying stored procedures, the provided statement is executed twice. Once on the memory table and once on the persisted table.