mysql procedure

最新推荐文章于 2024-03-05 17:40:13 发布

原创最新推荐文章于 2024-03-05 17:40:13 发布 · 209 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #SQL #Security #Blog

学习笔记专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍MySQL中存储过程及函数的创建与使用方法，包括更改定界符、定义存储过程与函数、调用存储过程及函数、创建分页查询过程等。此外还介绍了如何通过存储过程批量插入数据。

用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

写的很好