See the email of Igor Tandetnik from 18-Dec-2005:
"Vishal Kashyap" wrote
> Is their any way we can write simple stored procedures or functions
> in sqlite. If yes please do guide me I need this functionality in one
> of my open source project.
Not in the usual sense, meaning some language that gets stored in the
database itself together with the data. The only thing that comes
somewhat close is a trigger. It is possible to create a poor man's
stored procedure like this:
create table sp_dosomething (param1 int, param2 char);
create trigger sp_dosomething_impl
instead of insert on sp_dosomething
begin
-- one or more sql statements possibly referring to
-- new.param1 and new.param2
end;
-- To invoke:
insert into sp_dosomething values(1, 'hello');
Note that triggers are rather limited in what they can do. They are just
a bunch of SQL statements, there is no control flow (loops, if then
else, goto) beyond what little you can implement in pure SQL. They
cannot return values, except indirectly by inserting or updating some
table. SQLite does not support cascading triggers, so if your "stored
procedure" manipulates some table to which regular triggers are attached
(perhaps ensuring data integrity), those triggers won't run.
SQLite supports custom functions - see sqlite3_create_function[16]. You
write them in C (or any other language that has bindings to SQLite API)
and you have to install them every time you open a DB handle with
sqlite3_open, before you can refer to them in your SQL statements. They
are not stored in the database file itself.
Finally, SQLite prepared statements (sqlite_prepare) can be thought of
as simple stored procedures defined in your program. Similar to custom
functions, you can prepare a statement right after opening the database,
then keep it around.
Igor Tandetnik
Ran
On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> Can you tell me how to create a stored procedure in an sqlite3 database
> and use the same in a trigger? Please provide an example (as complete as
> possible). In the stored procedure I need to execute few queries on some
> tables. Can you tell me how to do that also?
>
> Any help is deeply appreciated.
>
> Best Regards,
>
> Chethana
>
