存储过程

存储过程非常类似于Java语言的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存，这样下次执行的时候直接使用存储中的语句。

概念：

　　存储过程程序是一组为了完成特定功能的sql语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

　　存储过程可以包含逻辑控制语句（if-else,while,case）和数据操纵语句（insert,delete,alter/update，select），它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

ps:alter update区别

从功能上分： 

   1.Alter:修改表结构

     例如：

       (1). 增加表的字段

        Alter table test Add(id,int).

       (2).修改表的字段

        Alter table change(id,varchar(10))

    2.UPdate:修改表数据

       (1).修改字段id列的值

        update test set id=2;

 

从本质上区分：

    1.Alter是数据定义语言（Data difinition Language），在修改表的结构时，不需要Commit和Rollback。

 

    2.Update是数据数据操作语言(Data manipulation Language)，在修改数据值时，需要Commit和Rollback，否则提交的结构无效

 

 

　　由于存储过程在创建时即在数据库服务器上进行编译并存储在数据库中，所以存储过程运行要比单个SQL语句要快。同时由于在调用时只需要提供存储过程名和必须的参数信息即可，所以在一定程度上可以减少网络延迟。

一.创建存储过程create procedure sp_name()begin.........end

二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程1.基本语法：drop procedure sp_name//

2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.其他常用命令

1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name显示某一个mysql存储过程的详细信息

--------------------数据类型及运算符--------------------

一、基本数据类型：略

二、变量：

自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

变量分为用户变量和系统变量，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

1、 在mysql客户端使用用户变量mysql> SELECT 'Hello World' into @x;mysql> SELECT @x;

mysql> SET @y='Goodbye Cruel World';mysql> select @y;

mysql> SET @z=1+2+3;mysql> select @z;

2、 在存储过程中使用用户变量

mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');mysql> SET @greeting='Hello';mysql> CALL GreetWorld( );

3、 在存储过程间传递全局范围的用户变量mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);mysql> CALL p1( );mysql> CALL p2( );

 

三、运算符：1.算术运算符+     加   SET var1=2+2;       4-     减   SET var2=3-2;       1*      乘   SET var3=3*2;       6/     除   SET var4=10/3;      3.3333DIV   整除 SET var5=10 DIV 3; 3%     取模 SET var6=10%3 ;     1

2.比较运算符>            大于 1>2 False<            小于 2<1 False<=           小于等于 2<=2 True>=           大于等于 3>=2 TrueBETWEEN      在两值之间 5 BETWEEN 1 AND 10 TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 FalseIN           在集合中 5 IN (1,2,3,4) FalseNOT IN       不在集合中 5 NOT IN (1,2,3,4) True=             等于 2=3 False<>, !=       不等于 2<>3 False<=>          严格比较两个NULL值是否相等 NULL<=>NULL TrueLIKE          简单模式匹配 "Guy Harrison" LIKE "Guy%" TrueREGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" FalseIS NULL      为空 0 IS NULL FalseIS NOT NULL 不为空 0 IS NOT NULL True

3.逻辑运算符

4.位运算符|   或&   与<< 左移位>> 右移位~   非(单目运算，按位取反)

注释：

mysql存储过程可使用两种风格的注释双横杠：--

该风格一般用于单行注释c风格：/* 注释内容 */ 一般用于多行注释

--------------------流程控制--------------------

一、顺序结构

二、分支结构ifcase

三、循环结构for循环while循环loop循环repeat until循环

注：区块定义，常用begin......end;也可以给区块起别名，如：lable:begin...........end lable;可以用leave lable;跳出区块，执行区块以后的代码

begin和end如同C语言中的{ 和 }。

--------------------输入和输出--------------------

mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUTCreate procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数调用时指定，并且可被改变和返回

IN参数例子：CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)BEGINSELECT p_in; --查询输入参数SET p_in=2; --修改select p_in;--查看修改后的值END;

执行结果:mysql> set @p_in=1mysql> call sp_demo_in_parameter(@p_in)略mysql> select @p_in;略以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子创建:mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)BEGINSELECT p_out;/*查看输出参数*/SET p_out=2;/*修改参数值*/SELECT p_out;/*看看有否变化*/END;

执行结果:mysql> SET @p_out=1mysql> CALL sp_demo_out_parameter(@p_out)略

mysql> SELECT @p_out;略

INOUT参数例子：mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)BEGINSELECT p_inout;SET p_inout=2;SELECT p_inout;END;

执行结果：set @p_inout=1call sp_demo_inout_parameter(@p_inout) //略select @p_inout;略

ps:

SQL常用命令使用方法：
(1) 数据记录筛选：
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]"
sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
sql="select * from 数据表 where 字段名 in ('值1','值2','值3')"
sql="select * from 数据表 where 字段名 between 值1 and 值2"

(2) 更新数据记录：
sql="update 数据表 set 字段名=字段值 where 条件表达式"
sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"

(3) 删除数据记录：
sql="delete from 数据表 where 条件表达式"
sql="delete from 数据表" (将数据表所有记录删除)

(4) 添加数据记录：
sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)"
sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表)

(5) 数据记录统计函数：
AVG(字段名) 得出一个表格栏平均值
COUNT(*&brvbar;字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加
引用以上函数的方法：
sql="select sum(字段名) as 别名 from 数据表 where 条件表达式"
set rs=conn.excute(sql)
用 rs("别名") 获取统的计值，其它函数运用同上。

(5) 数据表的建立和删除：
CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )
例：CREATE TABLE tab01(name varchar(50),datetime default now())

DROP TABLE 数据表名称 (永久性删除一个数据表)

实例：

mysql -uroot -p123456 -Dseckill
-- 秒杀执行存储过程
DELIMITER $$ -- console ; 转换为 $$
-- 定义存储过程
-- 参数: in 输入参数; out 输出参数
-- row_count():返回上一条修改类型sql(delete,insert,update)的影响行数
-- row_count: 0:未修改数据; >0:表示修改的行数; <0:sql错误/未执行修改sql
CREATE PROCEDURE `seckill`.`execute_seckill`
  (in v_seckill_id bigint,in v_phone bigint,
    in v_kill_time timestamp,out r_result int)
  BEGIN
    DECLARE insert_count int DEFAULT 0;
    START TRANSACTION;
    insert ignore into success_killed
      (seckill_id,user_phone,create_time)
      values (v_seckill_id,v_phone,v_kill_time);
    select row_count() into insert_count;
    IF (insert_count = 0) THEN
      ROLLBACK;
      set r_result = -1;
    ELSEIF(insert_count < 0) THEN
      ROLLBACK;
      SET R_RESULT = -2;
    ELSE
      update seckill
      set number = number-1
      where seckill_id = v_seckill_id
        and end_time > v_kill_time
        and start_time < v_kill_time
        and number > 0;
      select row_count() into insert_count;
      IF (insert_count = 0) THEN
        ROLLBACK;
        set r_result = 0;
      ELSEIF (insert_count < 0) THEN
        ROLLBACK;
        set r_result = -2;
      ELSE
        COMMIT;
        set r_result = 1;
      END IF;
    END IF;
  END;
$$
-- 存储过程定义结束
--显示存储过程的执行代码
show create procedure execute_seckill\G
--将界定符转回分号
DELIMITER ;
--
set @r_result=-3;
-- 获取结果
select @r_result;

select * from seckill where seckill_id=1003;
select * from seckill where seckill_id=1003\G
select * from success_killed;
-- 执行存储过程
call execute_seckill(1003,13502178891,now(),@r_result);
--查看操作过程
select @r_result;
select * from success_killed;
select * from seckill where seckill_id=1003\G
call execute_seckill(1003,13502178891,now(),@r_result);
select @r_result;
-- 存储过程
-- 1:存储过程优化：事务行级锁持有的时间
-- 2:不要过度依赖存储过程
-- 3:简单的逻辑可以应用存储过程
-- 4:QPS:一个秒杀单6000/qps

　　

2. 记录集对象的方法：
rs.movenext 将记录指针从当前的位置向下移一行
rs.moveprevious 将记录指针从当前的位置向上移一行
rs.movefirst 将记录指针移到数据表第一行
rs.movelast 将记录指针移到数据表最后一行
rs.absoluteposition=N 将记录指针移到数据表第N行
rs.absolutepage=N 将记录指针移到第N页的第一行
rs.pagesize=N 设置每页为N条记录
rs.pagecount 根据 pagesize 的设置返回总页数
rs.recordcount 返回记录总数
rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否
rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否
rs.delete 删除当前记录，但记录指针不会向下移动
rs.addnew 添加记录到数据表末端
rs.update 更新数据表记录