跟我一起学习MySQL技术内幕（第五版）：（第四章学习日记1）

4.1使用视图
4.2使用存储程序
4.2.1符合语句和语句分隔符
4.2.2存储函数和存储过程
4.2.2.1存储函数和存储过程的权限
4.2.2.2存储过程的参数类型
~—————————————————————————–

4.1使用视图

视图是一个虚表,它是在表或其他视图的基础上,使用select语句来定义的.查询视图就等效于查询定义它的那条语句，只是视图将细节隐藏了起来。视图定义里可包含表达式计算和连接等操作，因此查询视图更易于编写出简单的用于获得所要信息的查询.

一个基本的视图可以只选择表的部分列.如果不想用下面这种方式将这几个列都写出来:

select last_name,first_name,city,state from president;

同时,也不想使用select *()这样会显示出并不想要的列.

具体的解决办法是定义一个视图,让它只检索想要的那些列:

create view vpress as
select last_name,first_name,city,state from president; 

现在这个视图就像一个窗口,透过它只能看到你想的那些列.这意味着,在视图里也可以使用select * ,并且只会获得在视图定义里通过名字指定的那些列.

在创建视图函数时,必须拥有对视图的create view 权限,拥有对select语句所选列的操作权限。

如果在查询视图时包含了where子句，那么MySQL在执行该查询时，会把这个子句添加到视图的定义里，以便进一步限制检索结果：

在使用视图时，只能引用在该视图定义里列出的那些列。也就是说，即使某个列属于基表，但它未列在视图定义里，你也不能引用它。

在默认情况下，视图里的列名与其select语句里列出的输出列名相同。如果想要显式的改用其他的列名，那么需要在视图定义里，紧跟视图名字的后面用括号列出那些新名字：

create view vpress2 (in,fn) as 
select last_name,first_name from president;

当引用此视图时，必须使用括号里的列名，而不能使用视图定义的select部分里列出的那些名字

除视图的select语句较为复杂以外，前面的视图定义还是比较简单的。这样可以隐藏复杂性，并轻易的查询到重要的输出信息。

有些视图是可更新的,也就是说,可以通过操作视图,对底层表里的那些行实现插入,更新或删除操作

create table t (i int);
insert into t (i) values (1),(2),(3);
create view v as select i from t;
select i from v;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+

insert into v (i) values(4);
delete from v where i<3;
select i from v;
+------+
| i    |
+------+
|    3 |
|    4 |
+------+ 

update v set i=i+1;
select i from v;
+------+
| i    |
+------+
|    4 |
|    5 |
+------+

如果想要让一个视图可更新,那么必须让它直接映射到一个表上,它所选择的那些列必须是对表中各列的简单引用,并且对视图某行的任何操作必须与底层表中某一行的操作相对应.例如,如果某个视图里有一个由聚合函数计算得到的汇总列,那么此视图函数的每一行都将涉及到底层表的多个行.在这种情况下,这个视图是不可更新的,因此无法辨别出应该更新表的哪一行

4.2使用存储程序

4.2.1符合语句和语句分隔符

对于由单个SQL语句构成程序体的存储程序,在编写时没有特别要求.下面这个程序使用了一条select语句,用于显示sampdb数据库里的各个表的名字:

create procedure sampdb_tables()
    select table_name from information_schema.tables
    where table_schema = 'saampdb' order by table_name; 

不过,存储程序并不局限于是一条简单的声明.里边的代码可以包含多条SQL语句,并且可以使用许多类型的构建形式,如局部变量,条件语句,循环和嵌套块.

如果想要编写出的存储程序使用这些特性,那么可以使用一个符合语句,这些语句由一个begin和end块构成,其间可以包含任意数量的语句.下面这个存储过程会显示出一条消息,并用你的名字向你问好:

create procedure greetings()
begin
    # 77 =16 for username  +60 for hostname +1 for '@'
        declare user char(77) character set utf8;
        set user = (select current_name());
        if instr(user,'@') >0 then
            set user = substing_index (user,'@',1);
        end if;
        if user = '' then
            set user = 'earthling';
        end if;
        select concat('Greetings,',user,'!') as greeting;
end;

对于复合语句,在块内的语句之间必须使用分号”;”进行分割由于它同时又是客户端程序mysql的默认语句分隔符,因此在尝试使用mysql定义存储程序时,会引起冲突.为解决这一问题,可以使用delimiter 命令重新定义mysql的默认语句分隔符,以保证它不会出现在这个例程的定义里,这样便可以让mysql不用将分号解释为结束符,而是将整个对象定义作为单独的一个语句传递到服务器.在定义完存储程序之后,还可以再次将结束符定义成分号.下面示例是将mysql分隔符临时更改为美元符号,接着在恢复成默认分隔符之后,开始执行该过程.

delimiter $
create procedure show_time()
begin
    select current_timestamp as 'local_time';
    select utc_timestamp as 'utc_time';
end$
delimiter;
call show_time();

+---------------------+
| local_time          |
+---------------------+
| 2016-07-19 16:40:13 |
+---------------------+
1 row in set (0.03 sec)

+---------------------+
| utc_time            |
+---------------------+
| 2016-07-19 08:40:13 |
+---------------------+
1 row in set (0.06 sec)

分隔符不一定非得是美元符号”$” 也不一定非得是单个字符.

需要遵守的原则是,如果存储程序的程序体包含了某些内部符号,那么在定义这个程序时,应该重新定义分隔符.

复合语句不一定非要用在复杂的存储程序里.不管程序体有没有语句,都可以使用复合语句

create procedure do_little()
begin
    do sleep(1);
end
;
create procedure do_nothing ()
begin
end;

为保证风格的一致性，即使在没有严格要求的情况下，你可能也会使用begin和end关键字来封闭每一个存储程序定义的程序体。

4.2.2存储函数和存储过程

存储函数常用在表达式里，它会执行计算并返回一个值。存储过程是使用call语句来调用的独立操作，不能用在表达式里，适合于使用存储过程的情况有两种，只需要通过运算来实现某种效果或动作，不需要返回值，运算结果需要返回多个结果集（函数无法做到这一点）、

存储函数可以用create function语句来创建，

create function fun_name([param_list])
    returns type
    routine_stmt


create procedure proc_name ([param_list])
    routine_stmt

下面这个示例会创建一个函数，它带有一个表示年份的整数值参数(为与表或列的名字有所区别,在给参数命名时将使用前缀p_)该函数使用了一个子查询,用于确定有多少位总统出生于指定年份,并返回统计值

delimiter $
create function count_born_in_year(p_year int)
    returns int
    reads sql data
    begin
        return (select count(*)ferom president where year(birth)=p_year;
    end$
delimeter;

这个函数有一条用来表明其返回值数据类型的returns子句，以及一个用来计算那个值得函数体。函数体至少要包含一条returns语句，用来向调用者返回一个值，将一个计算定义为函数，可以方便的执行它，而无需每次都写出所有的逻辑，并且可以像使用内建函数那样来调用它。

select count_born_in_year(1908);

这些语句都自己调用了那个函数，不过存储函数也可以用在任何复杂的表达式里。

函数无法返回多个值，要让函数返回多个值，一种办法是编写多个函数，然后再同一条语句里调用所有这些函数；另一种办法是使用一个存储过程，通过他的out参数返回这些值。存储过程应该负责计算这些期望值，并把他们赋值给各个参数，在过程返回后，调用者即可访问这些参数。

如果定义的存储函数与某个内建的函数同名，那么在调用它时必须用数据库名对函数名进行限定，以避免出现歧义，例如，要在sampdb数据库里定义了一个名为pi（）的存储函数，则必须使用sampdb.pi（）来调用它，以此表明调用的不是同名的内建函数。

存储过程与存储函数很相似，不同之处在于它没有返回值。因此，它没有returns子句或任何return语句。 下面这个简单的存储过程与count_born_in_year()函数很相似，只是它会显示一个结果集，而不会把计算结果作为其返回值。在结果集里，包含的是与每一位在给定年份出生的总统有关的信息行：

 delimiter $
 create procedure show_born_in_year(p_year int)
 begin 
     select first_name,last_name,birth,death from president 
     where birth year(birth) = p_year; 
end$
delimiter; 

与存储函数有所不同，存储过程不能用在表达式里，只能通过call语句来调用它：

call show_born_in_year(1908);

在本例里，过程体执行了一条select语句。如本例所示，语句的结果集并不是以过程值方式返回的，而是被发送给到了客户端。一个过程可以生成多个结果集，其中的每一个结果会依次发送到客户端。

到目前为止，所有的示例都只是在查询信息，不过，存储例程也可用来修改表。下面这个例程可以用来更新：

create procedure update_expiration (p_id int unsigned ,p_date date)
begin 
    update member set expiration = p_date where member_id = p_id;
end; 

下面的update_expiration()调用，会把指定成员的到期日期设置成明年的今天或者终身成员（NULL表示永不过期）

call update_expiration(61,curdate() + interval 1 year);
call update_expiration(87,NULL)；

存储函数需要服从这样一条限制，即它们不能对调用函数的那条语句正读取或正写入的那个表进行操作。存储过程通常没有这个限制，但如果它们是从存储函数里面调用的，则需要服从这条限制。例如，有一条查询member表里的语句调用了某个存储函数，那么不能再这个存储函数里调用update_expiration().

4.2.2.1存储函数和存储过程的权限

存储函数和存储过程隶属于某个数据库。如果要想创建存储函数或存储过程，则必须拥有该数据库的create routine 权限。默认的情况下，在创建存储例程时，如果你还没有获得 execute 和 alter routine 权限，那么服务器会自动为你授予这些权限，以便你可以执行或删除该例程。在删除例程时，服务器将自动撤销那些权限。如果，不想使用这些自动化的 权限授予/撤销机制，那么 可以把系统变量automatic_sp_privileges设置为0。

如果服务器启用了二进制日志功能，那么存储函数还需要服从另外一些限制条件，这些条件主要用于保证二进制日志在执行备份和复制操作时的安全性，具体做法是，不允许创建不确定或者会修改数据的存储函数。（如果某个函数针对给定的输入值会生成不同的结果，那么通过再次执行二进制日志来恢复数据，将不能保证把数据恢复到原来的样子；而且该函数还可能导致在主、从服务器上的复制结果不一致。）这些限制条件也适用于触发器的创建过程。具体如下所示。

1如果未启用log_bin_trust_function_creators系统变量，你就必须具备super权限才能创建存储函数。另外，你创建的所有函数都必须是确定的，并且不得修改数据。为了表明这一点，需要使用deterministic 、 no sql 、reads sql data 这三者之一来定义存储函数。例如：
create function half(p_value double)
returns double
deterministic 
begin
    return p_value/2;
end;

2.如果启用了log_bin_trust_function_creators 系统变量，就没有任何限制了。只有你相信MySQL服务器上的所有用户都不会去定义不安全的存储函数时，才可以使用这种设置。

4.2.2.2存储过程的参数类型

存储过程的参数分为3种类型。对于in参数，调用者会把一个值传递到过程里。这个过程可以对这个值进行修改，但在过程返回之后任何修改对调用者来说都是不可见的。out参数则正好相反。过程把某个值赋给这个参数，但在过程返回后该值可以被调用者访问。inout参数则允许调用者向过程传递一个值，然后再取回一个值。

显式指定参数类型的方法是，在参数列表里的参数名前面使用in、out、inout。如果没有为参数指定类型，则其默认类型为in。

使用out或inout参数的方法是，在调用过程时指定一个变量名。过程可以设置参数值，相应的变量将在过程返回时获得那个值。如果想让某个存储过程返回多个结果值，那么参数类型out和inout将非常有用。（由于存储函数只能返回一个值，因此它并不适用于这种情形）

下面这个过程演示了out参数的用法，统计student表中男生女生的数量，并返回两个统计值，让调用者可以访问它们：

create procedure count_student_by_sex (out p_male int , out p_female int)
begin
    set p_male = (select count(*) from student where sex = 'M');
    set p_female =(select count(*) from student where sex = 'F');
end;

在调用此过程中，请把各个参数替换成相应的用户定义变量。此过程将把统计值放到这些参数里。在它返回之后，这些变量会包含那些统计值：

call count_student_by_sex (@male_count , @female_count);
select @male_count，@female_count;

不是只有用户定义变量才能作为参数进行传递。如果在另一个存储程序里调用count_student_by_sex(),那么在这个程序中定义的局部变量或参数可以作为参数传递给count_student_by_sex().