mysql procedure

用MySQL命令行首先要

delimiter //

用mysql 客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql 自己来解释。

 

create procedure procedure_t_a_1(out size int)
begin
    select count(*) into size from t_a;
end
//

 

mysql> call procedure_t_a_1(@a);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
    -> //
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

 

 

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
//

 

是returns,注意

 

select hello('hello');
//

 

+----------------+
| hello('hello') |
+----------------+
| Hello, hello!  |
+----------------+
1 row in set (0.01 sec)

mysql> drop procedure procedure_t_a_1//
Query OK, 0 rows affected (0.09 sec)

mysql> drop function hello//
Query OK, 0 rows affected (0.00 sec)

重新create前两个procedures和function

mysql> show create procedure procedure_t_a_1\G
*************************** 1. row ***************************
       Procedure: procedure_t_a_1
        sql_mode: NO_AUTO_CREATE_USER
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_t_a_1`(
out size int)
begin    select count(*) into size from t_a;end
1 row in set (0.00 sec)

mysql> show create function hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode: NO_AUTO_CREATE_USER
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20))
RETURNS char(50) CHARSET utf8
RETURN CONCAT('Hello, ',s,'!')
1 row in set (0.00 sec)

 

mysql> show function status like 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2010-07-15 21:22:37
      Created: 2010-07-15 21:22:37
Security_type: DEFINER
      Comment:
1 row in set (0.04 sec)

 

 

把t_b表的内容插入到t_a中,可以用

insert into t_a(id,value) select id,value from t_b;

 或者直接插入值

insert into t_a(id,value) values (6,'a6'),(7,'a7');

 

 

往表中插入n条记录,用存储过程

CREATE PROCEDURE procedure_t_a_insert(IN size int)
BEGIN
  DECLARE myIndex int default 0;
  DECLARE max_id int DEFAULT 1;
  select max(id) into max_id from t_a;
  select max_id;
  repeat
   insert into t_a values (max_id+1, concat('v_',(max_id+1)) );
   set myIndex = myIndex + 1;
   set max_id = max_id + 1;
  until (myIndex>=size) end repeat;
END;

 

传入一个数字,想插入多少条就插入多少条

call procedure_t_a_insert(2);

 

成功插入!

 

修改后

 

CREATE PROCEDURE procedure_t_a_insert(IN size int)
BEGIN
  DECLARE myIndex int default 0;
  DECLARE max_id int DEFAULT 1;
  select max(id) into max_id from t_a;
  select max_id;
  repeat
   insert into t_a values (max_id+1, concat('v_',(max_id+1)) );
   set myIndex = myIndex + 1;
   set max_id = max_id + 1;
  until (myIndex>=size) end repeat;
END;
--
drop procedure procedure_t_a_insert;
call procedure_t_a_insert(12);
--

 

分页查询

 

create procedure procedure_t_a_fenye(in currentPage int,in sizeofpage int)
begin
    declare totalSize int default 1;
    declare totalPage int default 1;
    declare index_begin int default 1;

    declare sSql varchar(1000);

    select count(*) into totalSize from t_a;
    set totalPage = CEILING(totalSize/sizeofpage);
    set @limit_begin = currentPage * sizeofpage;
    set @limit_end = sizeofpage;

    select index_begin,totalSize,sizeofpage,totalSize/sizeofpage,totalPage;
    prepare stmt1 from 'select * from t_a limit ?,?';
    execute stmt1 using @limit_begin,@limit_end;
end;
--
drop procedure if exists procedure_t_a_fenye;
select @limit_begin;
select @limit_end;
call procedure_t_a_fenye(2,5);

 

http://jspengxue.iteye.com/blog/46712

 

写的很好