mysql 存储过程项目小结

发布时间：2020-09-01 16:30:34 所属栏目：MySql 来源：互联网

导读：1. false ：0 true 1 切记官方文档：http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.htmlBOOL,BOOLEANThese types are synonyms forTINYINT(1). A value of zero is considered false. Nonzero values are considered true:mysql SELECT IF(0

1. false ：0 true 1 切记

官方文档：http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

. A value of zero is considered false. Nonzero values are considered true:

andare merely aliases forand,respectively,as shown here:


mysql  (  FALSE,
 (  FALSE,) 

 true                           

  (  TRUE,
 (  TRUE,) 

 true                          

  (  TRUE,
 (  TRUE,) 

 false                         

  (  FALSE,
 (  FALSE,) 

 false                          


is equal to neithernor.


2 存储过程中执行动态sql
官方文档：
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html
The first example shows how to create a prepared statement by using a string literal to supply the text of the statement:
mysql  stmt1            stmt1 USING ,
 hypotenuse 

           

mysql   stmt1;

The second example is similar,but supplies the text of the statement as a user variable:
mysql      stmt2            stmt2 USING ,
 hypotenuse 

          

mysql   stmt2;

Here is an additional example which demonstrates how to choose the table on which to perform a query at runtime,by storing the name of the table as a user variable:
mysql    t1 (a      t1  (),(),(),(),(mysql<span style="color: #808080;">> <span style="color: #0000ff;">SET <span style="color: #008000;">@table <span style="color: #808080;">= <span style="color: #ff0000;">'<span style="color: #ff0000;">t1<span style="color: #ff0000;">'<span style="color: #000000;">;

mysql<span style="color: #808080;">> <span style="color: #0000ff;">SET <span style="color: #008000;">@s <span style="color: #808080;">= CONCAT(<span style="color: #ff0000;">'<span style="color: #ff0000;">SELECT * FROM <span style="color: #ff0000;">',<span style="color: #008000;">@table<span style="color: #000000;">);
mysql<span style="color: #808080;">> <span style="color: #0000ff;">PREPARE stmt3 <span style="color: #0000ff;">FROM <span style="color: #008000;">@s<span style="color: #000000;">;

mysql<span style="color: #808080;">> <span style="color: #0000ff;">EXECUTE<span style="color: #000000;"> stmt3;

<span style="color: #808080;">+<span style="color: #008080;">--<span style="color: #008080;">--+

<span style="color: #808080;">| a  <span style="color: #808080;">|

<span style="color: #808080;">+<span style="color: #008080;">--<span style="color: #008080;">--+

<span style="color: #808080;">|  <span style="color: #800000; font-weight: bold;">4 <span style="color: #808080;">|

<span style="color: #808080;">|  <span style="color: #800000; font-weight: bold;">8 <span style="color: #808080;">|

<span style="color: #808080;">| <span style="color: #800000; font-weight: bold;">11 <span style="color: #808080;">|

<span style="color: #808080;">| <span style="color: #800000; font-weight: bold;">32 <span style="color: #808080;">|

<span style="color: #808080;">| <span style="color: #800000; font-weight: bold;">80 <span style="color: #808080;">|

<span style="color: #808080;">+<span style="color: #008080;">--<span style="color: #008080;">--+

<span style="color: #000000;">

mysql<span style="color: #808080;">> <span style="color: #0000ff;">DEALLOCATE <span style="color: #0000ff;">PREPARE stmt3;

总结：
执行动态sql，分三步走：
1. PREPARE；
　Syntax：
stmt_name FROM preparable_stmt
2. EXECUTE；
　　 Syntax：

stmt_name [USING @var_name [,@var_name] ...]

3. DEALLOCATE PREPARE；

　　Syntax：

stmt_name

3. 存储过程中的事务

语法：

START CHAIN] CHAIN] autocommit { }

（编辑：4S站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!