Scripting
in MySQL is useful to automate tasks and procedures. The scripts are
written in the MySQL syntax inside a .sql file, which can be written with any text
editor or directly from the MySQL GUI.
There are
two main types of routines: Procedures and Functions.
Some of the key
differences between them is that Functions must return a value, can only have
input parameters and are typically called with a Select statement, whereas
Procedures are called with a Call statement, can have input, output, inout or
no parameters.
There are
two main ways of executing a Procedure: batch mode and source mode.
batch mode:
cmd> mysql -u root -p -t -vvv mypath\myscript.sql
source mode: mysql> source mypath\myscript.sql
Below is an
example of a Procedure routine created to insert n records into two columns of
a table.
Delimiters are used to change the usual delimiter (;) to a
different one, and ($$) is typically used. This is done in order to enable the
commands that are sent inside the BEGIN and END block are executed only after
the block reaches END$$.
Executing from the terminal prompt:
Calling the Procedure:
No comments:
Post a Comment