mysql学习九：存储引擎、存储过程和函数的引入

存储引擎：

存储引擎是mysql特有的，共有7种，常用的有myisam、memory、innodb

查看表的存储引擎：

show create table 表名;

修改表的存储引擎：

alter table 表名 engine=存储引擎名称;

1.myisam存储引擎：可转换为压缩只读表来节省空间

使用三个文件表示每个表：

格式文件--存储表的结构表名.frm
数据文件---存储表的数据表名.MYD
索引文件--存储表的索引表名.MYI

2.innodb存储引擎的特征：

每个innodb表，在数据库目录中以.frm格式文件表示
innodb表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用commit提交、savepoint 及rollback(回滚)支持事务处理
提供全部acid兼容
在mysql服务器崩溃后，提供自动恢复
多版本mvcc和行级锁
支持外键，及引用的完整性，包括级联更新和删除

3.memory存储引擎，以前被称为headp引擎

使用memory存储引擎的表，因为数据存储在内存中，且行的长度固定，所以使得memory存储引擎非常快

memory存储引擎管理的表具有下列特征

在数据库目录内，每个表均以.frm格式文件表示
表数据及索引被存储在内存中
表级锁机制
字段属性不能包含text或blob字段

选择合适的存储引擎：

myisam表最适合于大量的数据读而少量数据更新的混合操作。myisam表的另一种适用情形是使用压缩的只读表。
如果查询中包含较的数据更新操作，应使用innodb。其行级锁机制和多版本的支持为数据读取和更新的混合提供了良好的并发机制。
使用memory存储引擎存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

存储过程和函数，是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的sql语句。

存储过程和函数，可以避免开发人员重复的编写相同的sql语句；而且，存储过程和函数是在mysql服务器中存储和执行的，可以减少客户端和服务端的数据传输；

一、创建存储过程和存储函数

1.创建存储过程

create procedure 存储过程的名称 ([proc_parameter[,......]]) [characteristic......] routine_body

proc_parameter：表示存储过程的参数列表；由3分部分组成，输入输出类型、参数名和参数类型---[in | out | inout] param_name type

其中，in表示输入参数；out表示输出参数；inout表示既可以是输入，也可以是输出
param_name，是存储过程的参数的名称
type：指定存储过程的参数类型，该类型可以是mysql数据库的任意数据类型

characteristic：指定存储过程的特性、配置；有多个可取值

language sql：说明 routine_body部分是由sql语言的语句组成，这也是数据库系统默认的语言
[not] deterministic：指明存储过程的执行结果是否是确定的。deterministic表明每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
{contains sql | no sql | reads sql data | modifies sql data }：指明子程序使用sql语句的限制

　　contains sql表示子程序包含sql语句；
　　no sql表示子程序中不包含sql语句
　　reads sql data表示子程序中包含读数据的语句
　　modifies sql data表示子程序中包含写数据的语句
默认情况下，系统会指定为contains sql

sql security {definer |invoker}：指明谁有权限来执行。definer表示只有定义者自己才能够执行；invoker表示调用者可以执行。默认情况下，系统指定的权限是definer。
comment "string"：注释信息

routine_boy：是sql代码的内容，可以用begin......end来标志sql代码的开始和结束。如果bign,end中有多条语句，则要在begin，end的前后再加上delimiter |......| delimiter

delimiter &&
CREATE PROCEDURE pro_book ( IN bt INT, OUT count_num INT ) READS SQL DATA 
BEGIN
    SELECT COUNT(*) FROM book WHERE    bookTypeId=bt;
END &&
delimiter;

调用存储过程：1，是输入的参数；@total是输出的参数；@表示定义的是全局参数

call pro_book(1, @total);

2.创建存储函数

create function 存储函数的名称 ([func_parameter[,......]]) 
returns type [characteristic....] routine_body;

func_parameter：存储函数的参数列表。可以由多个参数组成，每个参数由参数名称和参数类型组成，形式如下：

　　param_name type：其中，param_name参数是存储函数的参数名称；type参数是指定存储函数的参数类型，该类型可以是mysql数据库的任意数据类型

returns type：指定返回值的类型

characteristic：指定存储过程的特性，该参数的取值与存储过程中的聚会是一样的

routine_body：是sql的代码内容，可以用begin....end来标志sql代码的开始和结束

delimiter &&
CREATE FUNCTION func_book ( bookId INT ) RETURNS VARCHAR ( 20 ) 
BEGIN
    RETURN ( SELECT bookName FROM book WHERE id = bookId );    
END && 
delimiter;

调用存储函数：

select func_book(1);
select func_book(2);

3.变量的使用

1.）定义变量

declare 变量名称[,...] type [default value]

创建示例表：

create table test_user(id int not null auto_increment primary key,
userName varchar(20),
password varchar(64)
)

创建存储过程，定义变量：

delimiter &&
CREATE PROCEDURE pro_user ( ) BEGIN
    DECLARE    a,b VARCHAR ( 20 );
    INSERT INTO test_user values(null, a, b );    
END && 
delimiter;

调用存储过程：

call pro_user();

调用存储过程的结果：

2）.为变量赋值

set 变量名=expr[, 变量名=expr].........

创建存储过程，定义变量，给变量赋值：

delimiter &&
CREATE PROCEDURE pro_user2 ( )
BEGIN
    DECLARE    a,b VARCHAR ( 20 );
        set a='java123',b='1234235';
    INSERT INTO test_user values(null, a, b ); 
END && 
delimiter;

调用存储过程：

call pro_user2;

另一种赋值方式： select ..... into 变量名

delimiter &&
CREATE PROCEDURE pro_user3 ( )
BEGIN
    DECLARE a,b VARCHAR ( 20 );
    SELECT userName, password INTO a,b FROM test_user where id=1;
    INSERT INTO test_user values(null, a, b ); 
END && 
delimiter;

call pro_user3;

4.游标的使用

查询语句可能查询出多条记录，在存储过程和存储函数中使用游标来逐条读取查询结果集中的记录。

游标的使用，包括声明游标、打开游标、使用游标和关闭游标。

游标必须声明在处理程序之前，并且声明在变量和条件之后。

1).声明游标：

declare 游标名称 cursor for select查询语句;

2).打开游标

open 游标名称;

3).使用游标

fetch 游标名称 into 变量名称[,变量名称......]

创建存储过程，定义变量，定义打开使用关闭游标：使用游标查询的结果，赋值给变量

delimiter &&
CREATE PROCEDURE pro_user4 ( )
BEGIN
    DECLARE a,b VARCHAR ( 20 );
    DECLARE cur_test_user CURSOR FOR SELECT userName,password FROM test_user where id=2;
    OPEN cur_test_user;
    FETCH cur_test_user INTO a, b;
    INSERT INTO test_user values(null, a, b ); 
    CLOSE cur_test_user;
END && 
delimiter;

调用存储过程：

call pro_user4;

5.流程的控制

存储过程和存储函数中，可以使用流程控制来控制语句的执行。

mysql中，可以使用if、case、loop、leave、iterate、repeat、while语句来控制流程

1).IF语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]
    ........
    [ELSE statement_list]
END IF

示例：

delimiter &&
CREATE PROCEDURE pro_user5 (in id int )
BEGIN
    SELECT count(*) INTO @numm from user1 WHERE id=id;
        IF @num > 0 THEN UPDATE user1 SET userName='java12345' WHERE id=id;
        ELSE
             INSERT INTO user1 VALUES(null, 'java12345', '123123');
        END IF;
END && 
delimiter;

call pro_user5(5);

call pro_user5(5);

2).CASE语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]
    ......
    [ELSE statement_list]
END CASE

示例：

delimiter &&
CREATE PROCEDURE pro_user6 (in id int )
BEGIN
    SELECT count(*) INTO @numm from user1 WHERE id=id;
        CASE @num
        WHEN 1 THEN UPDATE user1 SET userName='python123' where id=id;
        WHEN 2 THEN INSERT INTO user1 VALUES(null,'python123', '123456');
        ELSE INSERT INTO user1 VALUES(null, 'JS123', '1111111');
        END CASE;
END && 
delimiter;

call pro_user6(5);

3).LOOP、LEAVE语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须遇到LEAVE语句等，才会停止。

[begin_label: ] LOOP
    statement_list
END LOOP [end_label]

示例：

delimiter &&
CREATE PROCEDURE pro_user7 (in totalNum int )
BEGIN
    aaaLable:LOOP
            IF totalNum=0 THEN LEAVE aaaLable;
            ELSE INSERT INTO user1 VALUES (totalNum, '123', '123');
            END IF;
            SET totalNum=totalNum-1;
        END LOOP aaaLable;
END && 
delimiter;

call pro_user7(10);

4.ITERATE语句：也是用来跳出循环。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。相当于，continue

ITERATE label;

delimiter &&
CREATE PROCEDURE pro_user8 (in totalNum int )
BEGIN
    aaaLable:LOOP
                SET totalNum=totalNum-1;
            IF totalNum=0 THEN LEAVE aaaLable;
            ELSEIF totalNum=3 THEN ITERATE aaaLable;
            END IF;
            INSERT INTO user1 VALUES (totalNum, '123456', '123456');
        END LOOP aaaLable;
END && 
delimiter;

call pro_user8(11);

5.REPEAT语句：是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。

[label:] REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [label]

delimiter &&
CREATE PROCEDURE pro_user9 (in totalNum int )
BEGIN
    aaaLable:REAPEAT
            SET totalNum=totalNum-1;
            INSERT INTO user1 VALUES (totalNum, '111', '111');
                        UNTIL totalNum=1;
        END REPEAT aaaLable;
END && 
delimiter;

6.WHILE语句

[label:] WHILE search_condition DO
    statement_list
END WHILE [label]

示例：

delimiter &&
CREATE PROCEDURE pro_user10 (in totalNum int )
BEGIN
    WHILE totalNum > 0 DO
         INSERT INTO user1 VALUES (totalNum, '111', '111');
         SET totalNum=totalNum-1;
      END WHILE;
END && 
delimiter;

 call pro_user10(10);

二、调用存储过程和函数

1.调用存储过程

call 存储过程的名称([parameter[,........]);

2.调用存储函数

call 存储函数的名称([parameter[,........]);

三、查看存储过程和函数

1 show status：查看存储过程和函数的状态

show {procedure|function} status [like 'pattern'];

2.show create：查看存储过程的函数的定义

show create {procedure|function} 存储函数的名称；

3.从information_schema.Routins表中，查看存储过程和函数的信息

四、修改存储过程和函数

alter {procedure|function} 名称 [characteristic.......]

characteristic：

contains sql | no sql | reads sql data | modifies sql data

| sql security {definer | invoker}

|comment 'string'

contains sql：表示子程序包含sql读或写语句，默认值

no sql：表示子程序中不包含sql语句

reads sql data：表示子程序中包含读数据的语句

modifies sql data：表示子特别子程序中包含写数据的语句

sql security {definer | invoker}：指明谁有权限执行

definer---表示只有定义者自己才能够执行；invoker--表示调用者可以执行

comment 'string'：注释信息

alter procedure pro_book comment '测试....';

删除存储过程或函数：

drop produce pro_book;

posted on 2018-09-16 22:35 myworldworld 阅读(360) 评论(0) 收藏举报

刷新页面返回顶部

myworldworld