代码改变世界

MySQL存储过程

2014-02-25 21:43  youxin  阅读(642)  评论(0)  编辑  收藏  举报

1.1         CREATE  PROCEDURE  （创建）

CREATE PROCEDURE存储过程名 (参数列表)
   BEGIN
         SQL语句代码块
END
 
注意：
由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT
在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。
当使用delimiter命令时，你应该避免使用反斜杠(‘"’)字符，因为那是MySQL的转义字符。
如：
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

（

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec) ）

  • SELECT ... INTO var_list selects column values and stores them into variables.

  • SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.

  • SELECT ... INTO DUMPFILE writes a single row to a file without any formatting.）

 @a什么意思?

http://dev.mysql.com/doc/refman/5.1/zh/language-structure.html#variables

9.3. 用户变量
可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

用户变量的形式为@var_name，其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。 默认字符集是cp1252 (Latin1)。可以用mysqld的--default-character-set选项更改字符集。参见5.10.1节，“数据和排序用字符集”。用户变量名对大小写不敏感。

设置用户变量的一个途径是执行SET语句：

SET @var_name = expr [, @var_name = expr] ...对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。

也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符：

mysql> SET @t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;+----------------------+------+------+------+| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |+----------------------+------+------+------+|          5 |  5 |  1 |  4 |+----------------------+------+------+------+用户变量可以用于表达式中。目前不包括明显需要文字值的上下文中，例如SELECT语句的LIMIT子句，或者LOAD DATA语句的IGNORE number LINES子句。

如果使用没有初始化的变量，其值是NULL。

 

1.2         ALTER  PROCEDURE （修改）

ALTER PROCEDURE 存储过程名SQL语句代码块
 
这个语句可以被用来改变一个存储程序的特征。

1.3         DROP  PROCEDURE （删除）

DROP PROCEDURE  IF  EXISTS存储过程名

eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存储过程名)

 
这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

1.4         SHOW  CREATE  PROCEDURE（类似于SHOW CREATE TABLE，查看一个已存在的存储过程）

SHOW CREATE PROCEDURE 存储过程名

1.5         SHOW  PROCEDURE  STATUS （列出所有的存储过程）

SHOW  PROCEDURE  STATUS

1.6         CALL语句（存储过程的调用）

CALL 存储过程名(参数列表)

 
CALL语句调用一个先前用CREATE PROCEDURE创建的程序。
CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。
存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

1.7         BEGIN ... END（复合语句）

[begin_label:] 
BEGIN
    [statement_list]
END 
[end_label]

存储子程序可以使用BEGIN ... END复合语句来包含多个语句。

statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。

1.8         DECLARE语句（用来声明局部变量）

DECLARE语句被用来把不同项目局域到一个子程序：局部变量

DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

1.9         存储程序中的变量

1.1             DECLARE局部变量

DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。
要给变量提供一个默认值，请包含一个DEFAULT子句。
值可以被指定为一个表达式，不需要为一个常数。
如果没有DEFAULT子句，初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。
它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

1.2             变量SET语句

SET var_name = expr [, var_name = expr] 
在存储程序中的SET语句是一般SET语句的扩展版本。
被参考变量可能是子程序内声明的变量，或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。
其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。
这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

1.3             SELECT ... INTO语句

SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。
因此，只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意，用户变量名在MySQL 5.1中是对大小写不敏感的。

重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

1.10     MySQL 存储过程参数类型（in、out、inout）

此小节内容来自：

参见地址：http://www.blogjava.net/nonels/archive/2009/04/22/233324.html

MySQL 存储过程参数（in）

MySQL 存储过程 “in” 参数：跟 C 语言的函数参数的值传递类似， MySQL 存储过程内部可能会修改此参数，但对 in 类型参数的修改，对调用者（caller）来说是不可见的（not visible）。

MySQL 存储过程参数（out）

MySQL 存储过程 “out” 参数：从存储过程内部传值给调用者。在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值

MySQL 存储过程参数（inout）

MySQL 存储过程 inout 参数跟 out 类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过 inout 参数传递值给存储过程。

总结

如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。

1.11     例子：

1.1            创建存储过程

带(输出参数)返回值的存储过程：

--删除存储过程

DROP PROCEDURE IF EXISTS proc_employee_getCount

--创建存储过程

CREATE PROCEDURE proc_employee_getCount(out n int)

BEGIN

     SELECT COUNT(*) FROM employee ;

END

--MYSQL调用存储过程

CALL proc_employee_getCount(@n);

带输入参数的存储过程：

--删除存储过程

DROP PROCEDURE IF EXISTS proc_employee_findById;

--创建存储过程

CREATE PROCEDURE proc_employee_findById(in n int)

BEGIN

     SELECT * FROM employee where id=n;

END

--定义变量

SET @n=1;

--调用存储过程

CALL proc_employee_findById(@n);

操作存储过程时应注意:

1.          删除存储过程时只需要指定存储过程名即可，不带括号；

2.          创建存储过程时，不管该存储过程有无参数，都需要带括号；

3.          在使用SET定义变量时应遵循SET的语法规则；

SET @变量名=初始值；

4.          在定义存储过程参数列表时，应注意参数名与数据库中字段名区别开来，否则将出现无法预期的结果

1.12     Java代码调用存储过程(JDBC)

相关API：java.sql.CallableStatement

使用到java.sql.CallableStatement接口，该接口专门用来调用存储过程;

该对象的获得依赖于java.sql.Connection;

通过Connection实例的prepareCall()方法返回CallableStatement对象

prepareCall()内部为一固定写法{call 存储过程名(参数列表1,参数列表2)}可用?占位

eg: connection.prepareCall("{call proc_employee(?)}");

存储过程中参数处理:

输入参数:通过java.sql.CallableStatement实例的setXXX()方法赋值，用法等同于java.sql.PreparedStatement

输出参数:通过java.sql.CallableStatement实例的registerOutParameter(参数位置, 参数类型)方法赋值,其中参数类型主要使用java.sql.Types中定义的类型

Java代码调用带输入参数的存储过程 (根据输入ID查询雇员信息)

publicvoid executeProcedure()

    {

        try {

            /**

             *callableStatementjava.sql.CallableStatement

             *connectionjava.sql.Connection

             *jdbc调用存储过程原型

             *{call存储过程名(参数列表1,参数列表2)}可用?代替

             */

            callableStatement=connection.prepareCall("{call proc_employee_findById(?)}");

            callableStatement.setInt(1, 1); //设置输入参数

            resultSet=callableStatement.executeQuery();//执行存储过程

            if(resultSet.next())

            {

                System.out.println(resultSet.getInt(1)+""t"+resultSet.getString(2));

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }  

Java代码调用带输出参数的存储过程 (返回数据库中的记录数)

publicvoid executeProcedure()

    {

        try {

            /**

             *callableStatementjava.sql.CallableStatement

             *connectionjava.sql.Connection

             *jdbc调用存储过程原型

             *{call存储过程名(参数列表1,参数列表2)}可用?代替

             */

            callableStatement=connection.prepareCall("{call proc_employee_getCount(?)}");

            //设置输出参数

            callableStatement.registerOutParameter(1, Types.INTEGER);

            //执行存储过程

            resultSet=callableStatement.executeQuery();

            if(resultSet.next())

            {

                System.out.println(resultSet.getInt(1));

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

1.13       声明:

此文档中除MySQL 存储过程参数类型（in、out、inout）小节来自网上，其余均为本人原创，欢迎大家转载，如有不足，请指教。

由于是在word中编辑好拷贝过来的所以字体较小大家点此下载电子文档

 上面转自:http://www.oschina.net/question/272738_104787?sort=time

 

存储过程示例： 

BEGIN
#Roudecntine body goes here...

declare n int ;
  set n=1 ;
  while n<=10
do
   insert into user(username,password) value(concat('user',n),concat('pwd',n) );
  set n=n+1;
 end while;


END

注意；号的使用。

 

 mysql存储过程中用变量做表名

首先要知道的是，表名create table tableName ，这个tablename不是字符串，你试试用字符串运行就知道报错了。

mysql存储过程中用变量做表名
 
1.用变量做表名： 
 
简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。在其他的sql数据库中也是如此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。 
 
这在mysql5.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似sp_executesql的功能（仅对procedure有效，function不支持动态查询）： 
 
Sql代码  
PREPARE stmt_name FROM preparable_stmt;   
EXECUTE stmt_name [USING @var_name [, @var_name] ...];   
{DEALLOCATE | DROP} PREPARE stmt_name;   
 
 
MySQL prepare语法： 
PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ 
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/ 
{DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ; 

PREPARE语句用于预备一个语句，并指定名称statement_name，以后引用该语句。语句名称对大小写不敏感。preparable_stmt可以是一个文字字符串，也可以是一个包含了语句文本的用户变量。该文本必须表现为一个单一的SQL语句，而不是多个语句。在这语句里，‘?'字符可以被用于标识参数，当执行时，以指示数据值绑定到查询后。‘?'字符不应加引号，即使你想要把它们与字符串值结合在一起。参数标记只能用于数据值应该出现的地方，而不是SQL关键字，标识符，等等。 
如果预语句已经存在，则在新的预语句被定义前，它会被隐含地删掉。 

 

为了有一个感性的认识， 
下面先给几个小例子： 
 
Sql代码  
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';   
mysql> SET @a = 3;   
mysql> SET @b = 4;   
mysql> EXECUTE stmt1 USING @a, @b;   
+------------+   
| hypotenuse |   
+------------+   
| 5 |   
+------------+   
mysql> DEALLOCATE PREPARE stmt1;   
  
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';   
mysql> PREPARE stmt2 FROM @s;   
mysql> SET @a = 6;   
mysql> SET @b = 8;   
mysql> EXECUTE stmt2 USING @a, @b;   
+------------+   
| hypotenuse |   
+------------+   
| 10 |   
+------------+   
mysql> DEALLOCATE PREPARE stmt2;   
 
 
如果你的MySQL 版本是 5.0.7 或者更高的，你还可以在 LIMIT 子句中使用它，示例如下： 
Sql代码  
mysql> SET @a=1;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";   
mysql> EXECUTE STMT USING @a;   
mysql> SET @skip=1; SET @numrows=5;   
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";   
mysql> EXECUTE STMT USING @skip, @numrows;   
 
 
使用 PREPARE 的几个注意点： 
A：PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。 
B： 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。 
C： 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。 
 
D： PREPARE stmt_name 的作用域是当前客户端连接会话可见。 
E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。 
F： EXECUTE stmt_name 句法中，如果 stmt_name 不存在，将会引发一个错误。 
G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。 
H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。 
 
I： PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！ 
 
 
下面给个示例： 
  
Sql代码  
CREATE PROCEDURE `p1`(IN id INT UNSIGNED,IN name VARCHAR(11))    
BEGIN lable_exit:    
BEGIN    
SET @SqlCmd = 'SELECT * FROM tA ';    
IF id IS NOT NULL THEN  SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE id=?');    
PREPARE stmt FROM @SqlCmd;  SET @a = id;    
EXECUTE stmt USING @a;    
LEAVE lable_exit;    
END IF;    
IF name IS NOT NULL THEN  SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');    
PREPARE stmt FROM @SqlCmd;    
SET @a = CONCAT(name, '%');    
EXECUTE stmt USING @a;    
LEAVE lable_exit;    
END IF;    
END lable_exit;    
END;    
CALL `p1`(1,NULL);    
CALL `p1`(NULL,'QQ');    
DROP PROCEDURE `p1`;  
  
了解了PREPARE的用法，再用变量做表名就很容易了。不过在实际操作过程中还发现其他一些问题，比如变量定义，declare变量和set @var=value变量的用法以及参数传入的变量。 
 
测试后发现，set @var=value这样定义的变量直接写在字符串中就会被当作变量转换，declare的变量和参数传入的变量则必须用CONCAT来连接。具体的原理没有研究。 
EXECUTE stmt USING @a;这样的语句USING后面的变量也只能用set @var=value这种，declare和参数传入的变量不行。 
另外php调用mysql存储过程的时候也碰到很多问题，总是出现PROCEDURE p can't return a result set in the given context这样的问题。
 
 

返回字符串str，左填充len个字符的长度的字符串padstr。如果str的长度大于len，则返回值被缩短至len个字符。

SQL> SELECT LPAD('hi',4,'??');
+---------------------------------------------------------+
| LPAD('hi',4,'??')                                       |




mysql 存储过程动态创建表名
set @tablename='tt';
set @sql=concat('select * from ',@tablename);
prepare dd from @sql;
execute dd;
例如:
BEGIN
    #Routine body goes here...
 
  set @n=1;
 
  while @n<=12
  do  

      set @c=concat('log2016',LPAD(@n,2,'0'));
           set @sql=concat("CREATE TABLE  ", @c, "(
                `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `date` datetime NOT NULL 
             
            PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=243 DEFAULT CHARSET=utf8
     "  );
       
      PREPARE stmt from @sql;
       
      execute stmt ;
      set @n=@n+1;
    
  end while;

END

执行就会创建log201601,log201602,一直到Log201612的表。


http://blog.csdn.net/super_ufo/article/details/11215795