MySQL存储过程与函数的区别

创建一个无参的存储过程

delimiter//
CREATE PROCEDURE showTime() //创建一个无参的存储过程
BEGIN
    SELECT NOW();
END//
delimiter;

CALL showTime();

创建一个只有IN参数的存储过程

delimiter//
CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)//创建一个只有IN参数的存储过程
BEGIN
SELECT *
FROM course WHERE cid = uid;
END//
delimiter;

CALL selectById(2)

包含IN参数和OUT参数

delimiter//
CREATE PROCEDURE deleteById(IN uid SMALLINT ,OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM course WHERE Cid = uid;
SELECT ROW_COUNT() INTO num;
END//
delimiter;

CALL deleteById(2,@changeLine);
SELECT @changeLine;

查看已有存储过程

1	SHOW PROCEDURE STATUS;//查看已有存储过程

存储过程与自定义函数的区别:

1、’存储过程实现较为复杂，自定义函数针对性强’
2、’存储过程允许有多个返回值，而自定义函数只有一个返回值’
3、’存储过程一般独立的来执行，自定义函数是往往与其他SQL语句的一部分来使用’

存储过程的好处

‘存储过程就是把经常使用的SQL语句或业务逻辑封装起来，预编译保存在数据库中，
当需要的时候从数据库中直接调用，省去了编译过程，进而提高了运行速度。同时降低
网络数据的传输量’

查看存储过程创建的代码

1	SHOW CREATE PROCEDURE showTime; // 查看存储过程创建的代码

存储过程

1	SELECT 'deleteById' FROM mysql.proc WHERE db = 'test' AND 'type' = 'PROCEDURE';//存储过程

创建视图

语法

CREATE [OR REPLACE]
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW  [db_name.]view_name[(column_list)
AS
[SELECT  statement]

语法介绍

[or replace]：若给定了[or replace]，则表示当已具有同名的视图时，将覆盖原视图。

[SELECT statement]：select_statement是一个查询语句，这个查询语句可从表或其它的视图中查询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。

ALGORITHM：算法属性允许您控制MySQL在创建视图时使用的机制，MySQL提供了三种算法：MERGE，TEMPTABLE和UNDEFINED。

使用MEGRE算法，MySQL首先将输入查询与定义视图中的SELECT语句组合成单个查询，然后MySQL执行组合查询并返回结果集。如果SELECT语句包含集合函数（如：MIN | MAX | SUM | COUNT | AVG 等）或DiSTINCT、GROUP BY、HAVING、LIMIT、UNION、UNION ALL、子查询，则不允许使用MEGRE
使用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE算法要比MEGRE算法效率要低。另外TEMPTABLE算法的视图是不可更新的。
当创建视图而不指定显示算法时，UNDEFUNED是默认算法。UNDEFINED算法使MySQL可以选择使用MEGRE或TEMPTABLE算法。MySQL优先使用MEGRE算法。

查看名称

在数据库中，视图和表共享相同的命名空间，因此视图和表不能具有相同的名称。另外，视图的名称必须遵循表的命名规则。

创建/修改示例

CREATE VIEW test_detail  AS 
SELECT  
    student.sid AS sid,
    student.Sname AS sName,
    sc.score AS score
FROM 
    student s,sc sc
WHERE 
    s.Sid = sc.Sid;

-----------修改视图----------------
ALTER VIEW test_detail AS
SELECT
    s.Sid AS sid,
    sc.score AS score
FROM 
    student s,sc sc
WHERE 
    s.Sid = sc.Sid;

查看视图命令

1	SHOW FULL TABLES; //查看视图命令

使用视图

1	SELECT * FROM test_detail; //使用视图

创建视图注意事项

1）运行创建视图的语句需要用户具有创建视图（create view）的权限，若加了[OR REPLACEREPLACE]时，还需要用户具有删除视图（drop view）的权限。

2）select 语句不能包含from子句中的子查询

3）select 语句不能引用系统或用户变量

4）select 语句不能引用预处理语句参数

5）在存储子程序内，定义不能引用子程序参数或局部变量

6）在定义中引用的表或视图必须存在。但是，创建了MySQL视图后，能够舍弃定义引用的表或视图。要
想检查视图定义是否存在这类问题，可使用check table语句；

7）在定义中不能引用temporary表，不能创建temporary视图；

8）在视图定义中命名的表必须已存在；

9）不能讲触发程序与视图关联在一起；

10）在视图定义中允许使用order by,但是，如果从特定视图进行了选择，而该视图使用了具有自己order by语句，它将被忽略。

视图

触发器

创建触发器

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为BEFORE或AFTER；
trigger_event：标识触发事件，取值为INSERT、UPDATE、DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者BEGIN或END包含的多条语句。

由此可见，可以建立6种触发器，即：

BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、

AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

触发器

查看触发器

1
2
3

SHOW TRIGGERS;//查看触发器
SELECT * FROM information_schema.triggers;
SELECT * FROM information_schema.triggers WHERE trigger_name='tri_test';

删除触发器

1	DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。

MySQl存储过程、触发器、视图、自定义函数、索引总结