D 2017-11-02T13:24:03.585
L Stored\sProcedures
U sandro
W 7038
<table cellspacing="12" width="100%">
<tr><td colspan="2">
<table width="100%" bgcolor="#f0f0f8">
<tr><td align="center">
</td></tr></table>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0+doc">back to index</a></td>
<td width="33%" align="right"></td>
</tr></table>
<h1>SQL Procedures, Stored Procedures and Stored Variables: a quick intro</h1>
Starting since version <b>4.5.0</b> SpatiaLite supports an advanced mechanism intended to significantly extend the scripting techniques allowing for a powerful and flexible automation of complex workflows.<br>
Think of the nice flexibility allowed by mixing standard <b><i>SQL scripts</i></b> and <b><i>Variables</i></b> to be replaced just before actual execution.
Not only: think of many wonderful opportunities deriving from permanently storing within a database such <b><i>SQL Scripts with Variables</i></b> so to make as simple as possible automating common workflows to be repeatedly executed.
This exactly is the intended scope of this new module.<br><br>
We'll start first by formally defining few basic concepts and objects so to avoid any possible confusion.<br><br>
<hr>
<h2>Basic Definitions and Concepts</h2>
<ol>
<li>
<h4>SQL Statement</h4>
The minimal execution unit allowed by SQL syntax; a stand-alone SQL directive to be atomically executed in a single step. Examples:
<verbatim>
SELECT id, name, measure FROM some_table;
------------
DELETE FROM some_table WHERE measure > 1000;
------------
UPDATE some_table SET measure = measure / 10.0
</verbatim>
<u>Note</u>: SQL Statements are usually expected to be explicitly terminated by a <b><i>semicolon</i></b> (<b><i>;</i></b>).<br>
The semicolon terminator can be safely suppressed only when the SQL Statement is immediately followed by a string terminator (leading and trailing sequences of all <b><i>blanks</i></b> will be always ignored).
</li>
<li>
<h4>SQL Script</h4>
A more or less complex sequence of <b><i>SQL Statements</i></b>, possibly including interleaved <b><i>Comments</i></b> and/or <b><i>Dot Macros</i></b>.<br>
SQL Scripts are intended to be executed in a single monolithic pass (atomic or not, depending on internal <b><i>Transaction</i></b> handling). Example:
<verbatim>
.echo on
--
-- starting a Transaction
--
BEGIN;
--
-- creating a table
--
CREATE TABLE example (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
value DOUBLE);
--
-- populating the table
--
INSERT INTO example (id, name, value) VALUES (NULL, 'first', 12.34);
INSERT INTO example (id, name, value) VALUES (NULL, 'second', 56.78);
INSERT INTO example (id, name, value) VALUES (NULL, 'third", NULL);
--
-- committing the pending Transaction
--
COMMIT;
.quit
</verbatim>
</li>
<li>
<h4>SQL Variable</h4>
A <b><i>SQL Variable</i></b> is just a <i><u>mnemonic symbol</u></i> enclosed between two <i><u>special markers</u></i> (<b><i>@</i></b> or <b><i>$</i></b>). Examples:
<verbatim>
@table_name@
@col_name@
$value$
$radius$
</verbatim>
<u>Note</u>: using the <b><i>@</i></b> or the <b><i>$</i></b> markers is just an <i><u>aesthetic choice</u></i>; both have identical meaning and practical effect.
</li>
<li>
<h4>SQL Body</h4>
A <b><i>SQL Script</i></b> internally using <b><i>Variables</i></b>. Example:
<verbatim>
.echo on
--
-- starting a Transaction
--
BEGIN;
--
-- creating a table
--
CREATE TABLE @table@ (
@pk@ INTEGER PRIMARY KEY AUTOINCREMENT,
@col1@ TEXT NOT NULL,
@col2@ DOUBLE);
--
-- populating the table
--
INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'first', 12.34);
INSERT INTO @table@ (@pk@, @col1@, @col2@) VALUES (NULL, 'second', 56.78);
INSERT INTO @table@ (@pk@, @col2@, @col2@) VALUES (NULL, 'third", NULL);
--
-- committing the pending Transaction
--
COMMIT;
.quit
</verbatim>
<u>Note</u>: this second version is obviously more flexible than the initial SQL script, just because there aren't <u><i>hardwired</i></u> table-names and column-names, all them are now become <u><i>symbolic</i></u>.<br>
So we are now free to reuse more times this SQL Body in different contexts; we just have to appropriately set the Variable Values just immediately before actual execution.<br><br>
<b><i>SQL Bodies</i></b> exist in two different forms:
<ul>
<li><b>raw</b>: still including symbolic Variables.</li>
<li><b>cooked</b>: presenting all symbols properly replaced by their actual values.<br>
Rather obviously, you can successfully execute only the <b><i>cooked</i></b> form.</li>
</ul>
</li>
<li>
<h4>SQL Procedure BLOB object</h4>
This simply is a <b><i>BLOB encoded</i></b> pre-compiled <b><i>SQL Body</i></b>, and is the usual target expected to be handled by practically all SQL functions supporting Stored Procedures and alike.
</li>
<li>
<h4>Stored Procedure</h4>
A <b><i>SQL Procedure BLOB object</i></b> permanently stored into a database. This will make easier reusing more times the same SQL workflow, perhaps so to implement routinely periodic tasks (to be executed daily / weekly / monthly and so on).<br>
Stored Procedures always have the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary Key</i></b>).</li>
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Procedure.</li>
<li>a <b>SQL Procedure BLOB Object</b>.</li>
</ul>
</li>
<li>
<h4>Variable with Value strings</h4>
Before executing a <b><i>SQL Body</i></b> including <b><i>Variables</i></b> you are obviously expected to set some appropriate <b><i>Value</i></b> for each Variable, so to allow for replacing <u><i>Symbols</i></u> with actual <u><i>Values</i></u> (i.e. for properly transforming <b>raw</b> SQL into <b>cooked</b> SQL).<br>
A <b><i>Variable with Value</i></b> string simply is an appropriately well-formatted Text string declaring both the Symbol and the corresponding replacement Value (expressed in its textual representation). Examples:
<verbatim>
'@value_1@=1234' -- integer value
'@value_2@=12.34' -- double value
'@value_3@=New York City' -- text value
'@value_4@=x''0123abcd''' -- blob value
'@value_5@=NULL' -- null value
</verbatim>
</li>
<li>
<h4>Stored Variable</h4>
A <b><i>Variable with Value string</i></b> permanently stored into a database.<br>
Stored Variables always have the following elements:
<ul>
<li>a <b>name</b>, intended to be an unique identifier (<b><i>Primary Key</i></b>).</li>
<li>a <b>title</b>, i.e. a short description of the intended scope of the Stored Variable.</li>
<li>a <b>Variable with Value string</b>.</li>
</ul>
</li>
</li>
</ol><br>
<hr>
<br><br>
<table width="100%"><tr>
<td width="33%" align="left"></td>
<td align="center"><a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0+doc">back to index</a></td>
<td width="33%" align="right"></td>
</tr></table>
Z 47e95cf33904db2d6b7688609f7e55a8