/
execution_routines.html
155 lines (148 loc) · 7.46 KB
/
execution_routines.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>execution_routines: common_schema documentation</title>
<meta name="description" content="execution_routines: common_schema" />
<meta name="keywords" content="execution_routines: common_schema" />
<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>
<body>
<div id="main">
<div id="header">
<h1>common_schema</h1> <strong>2.2</strong> documentation
<div class="subtitle">DBA's framework for MySQL</div>
</div>
<div id="contentwrapper">
<div id="content">
<h2><a href="execution_routines.html">execution_routines</a></h2>
<h3>SYNOPSIS</h3>
<p>
Execution routines: stored routines managing dynamic query execution, iteration & evaluation.
<ul>
<li><a href="eval.html">eval()</a>: Evaluates the queries generated by a given query.</li>
<li><a href="exec.html">exec()</a>: Executes a given query or semicolon delimited list of queries.</li>
<li><a href="exec_file.html">exec_file()</a>:Executes queries from given file, residing on server./li>
<li><a href="exec_single.html">exec_single()</a>: Executes a given query.</li>
<li><a href="foreach.html">foreach()</a> aka <a href="foreach.html">$()</a>: Invoke script on each element of given collection.</li>
<li><a href="repeat_exec.html">repeat_exec()</a>: Repeatedly executes given query or queries until some condition holds.</li>
<li><a href="run.html">run()</a>: run a QueryScript code provided as text.</li>
<li><a href="run_file.html">run_file()</a>: run a QueryScript code from file.</li>
<li><a href="script_runtime.html">script_runtime()</a>: number of seconds elapsed since script execution began.</li>
<li><a href="throw.html">throw()</a>: Disrupt execution with error.</li>
</ul>
</p>
<h3>DESCRIPTION</h3>
<p>
These featured routines allow for scripting & semi-scripting capabilities in MySQL. Looping through collections,
row sets, numbers, tables; repeating tasks until a given condition hold, or dynamically evaluating
queries. The execution routines simplify a DBA's maintenance work by providing with a simpler,
cleaner and more familiar syntax.
</p>
<p>
The <i>run()</i> and <i>run_file()</i> routines execute <a href="query_script.html">QueryScript</a> code.
The rest of the routines make for lower level, semi-scripting execution.
</p>
<p>
The majority of operations in these routines use dynamic queries, based on prepared statements.
Note that MySQL does not support invoking a prepared statement from within a prepared statement.
This means you may wish to avoid calling on these routines using prepared statements code (some
frameworks will, by default, invoke queries using prepared statements regardless of the query type).
</p>
<h3>EXAMPLES</h3>
<p>Use <i>foreach()</i> to convert <strong>sakila</strong> tables to InnoDB:
</p>
<blockquote><pre>mysql> call foreach(
'table in sakila',
'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPACT');
</pre></blockquote>
<p>Use <i>repeat_exec()</i> to delete huge amount of rows in smaller chunks, with sleeping interval:
</p>
<blockquote><pre>mysql> call repeat_exec(2,
'DELETE FROM sakila.rental WHERE customer_id=7 ORDER BY rental_id LIMIT 1000',
0);
</pre></blockquote>
<p>Use <i>eval()</i> to kill transactions being idle for over <strong>30</strong> seconds:
</p>
<blockquote><pre>mysql> call eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds > 30");
</pre></blockquote>
<br/>
</div>
<div id="sidebarwrapper">
<div id="search">
Search online documentation
<form id="search_form" name="search_form" method="GET"
action="http://www.google.com/search"
onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
<input type="text" name="search_term" value=""/>
<input type="hidden" name="q" value=""/>
<input type="submit" value="go"/>
</form>
</div>
<div id="menu">
<ul>
<li><a title="Introduction" href="introduction.html">Introduction</a></li>
<li><a title="Documentation" href="documentation.html">Documentation</a></li>
<li><a title="Download" href="download.html">Download</a></li>
<li><a title="Install" href="install.html">Install</a></li>
<li><a title="Risks" href="risks.html">Risks</a></li>
</ul>
<h3>QUERY SCRIPT</h3>
<ul>
<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
</ul>
<h3>DEBUG</h3>
<ul>
<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
</ul>
<h3>ROUTINES</h3>
<ul>
<li><a title="Execution & flow control" href="execution_routines.html">Execution & flow control</a></li>
<li><a title="General" href="general_routines.html">General</a></li>
<li><a title="Process" href="process_routines.html">Process</a></li>
<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
<li><a title="Security" href="security_routines.html">Security</a></li>
<li><a title="Text" href="text_routines.html">Text</a></li>
<li><a title="Time & date" href="temporal_routines.html">Time & date</a></li>
<li><a title="Charting" href="charting_routines.html">Charting</a></li>
</ul>
<h3>VIEWS</h3>
<ul>
<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
<li><a title="Process" href="process_views.html">Process</a></li>
<li><a title="Security" href="security_views.html">Security</a></li>
<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
</ul>
<h3>DATA</h3>
<ul>
<li><a title="tables" href="tables.html">Tables</a></li>
<li><a title="variables" href="variables.html">Variables</a></li>
</ul>
<h3>META</h3>
<ul>
<li><a title="Help" href="help.html">help</a></li>
<li><a title="Metadata" href="metadata.html">metadata</a></li>
<li><a title="status" href="status.html">status</a></li>
</ul>
</div>
</div>
<div class="clear"> </div>
<div id="footnote" align="center">
<a href="">common_schema</a> documentation
</div>
</div>
</div>
</body>
</html>