MySQL之视图、存储过程、触发器、函数、事务、动态执行SQL

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

创建视图：

create view v1 asselect * from student where id > 100;

今后可以直接通过:

select * from v1;   #直接访问学生ID大于100的信息

删除视图：

drop view v1;

修改视图：

alter view v1 asselect id,name from student where id<10 and name=dandan;

注意：使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以使用其对真实表进行创建、更新和删除操作，仅能做查询用。

触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

创建触发器：

delimiter //create trigger tg_before_insert_student before insert on student for each rowbegin    if new.name==‘alex‘ then    insert into student_back (name) values (‘alex_back‘);end //delimiter ;

也可以用 after insert 表示插入之后执行触发器中语句，还有其他操作：before update、after update、before delete 、after delete

注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行

删除触发器：drop tgigger tg_before_insert_student

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

-- 创建存储过程delimiter //create procedure p1()BEGIN    select * from t1;END//delimiter ;-- 执行存储过程call p1()

对于存储过程，可以接受参数，其参数类型有三类：

    in 只用于传入参数

    out 用于返回值

    inout 即可以传入又可以当作返回值

有参数的存储过程：

-- 创建存储过程delimiter create procedure p1(    in i1 int,    in i2 int,    inout i3 int,    out r1 int)BEGIN    DECLARE temp1 int;    DECLARE temp2 int default 0;        set temp1 = 1;    set r1 = i1 + i2 + temp1 + temp2;        set i3 = i3 + 100;enddelimiter ;-- 执行存储过程set @t1 =4;set @t2 = 0;CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;

结果集加返回值：

delimiter //                    create procedure p2(                        in n1 int,                        inout n3 int,                        out n2 int,                    )                    begin                        declare temp1 int ;                        declare temp2 int default 0;                        select * from v1;                        set n2 = n1 + 100;                        set n3 = n3 + n1 + 100;                    end //                    delimiter ;

事务型存储过程：

delimiter                         create PROCEDURE p1(                            OUT p_return_code tinyint                        )                        BEGIN                           DECLARE exit handler for sqlexception                           BEGIN                             -- ERROR                             set p_return_code = 1;                             rollback;                           END;                                                    DECLARE exit handler for sqlwarning                           BEGIN                             -- WARNING                             set p_return_code = 2;                             rollback;                           END;                                                    START TRANSACTION;                             DELETE from tb1;                            insert into tb2(name)values(‘seven‘);                          COMMIT;                                                    -- SUCCESS                           set p_return_code = 0;                                                    END                    delimiter ;

游标：

delimiter //                    create procedure p3()                    begin                         declare ssid int; -- 自定义变量1                          declare ssname varchar(50); -- 自定义变量2                          DECLARE done INT DEFAULT FALSE;                        DECLARE my_cursor CURSOR FOR select sid,sname from student;                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                                                open my_cursor;                            xxoo: LOOP                                fetch my_cursor into ssid,ssname;                                if done then                                     leave xxoo;                                END IF;                                insert into teacher(tname) values(ssname);                            end loop xxoo;                        close my_cursor;                    end  //                    delimter ;

动态执行SQL：

delimiter                     CREATE PROCEDURE p4 (                        in nid int                    )                    BEGIN                        PREPARE prod FROM ‘select * from student where sid > ?‘;                        EXECUTE prod USING @nid;                        DEALLOCATE prepare prod;                     END                    delimiter ;

删除存储过程：drop procedure proc_name;

执行存储过程：

-- 无参数call proc_name()-- 有参数，全incall proc_name(1,2)-- 有参数，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)

函数

MySql中的函数分为：内置函数、自定义函数

内置函数参考：

http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions

自定义函数：

delimiter create function f1(    i1 int,    i2 int)returns intBEGIN    declare num int;    set num = i1 + i2;    return(num);END delimiter ;

MySQL之视图、存储过程、触发器、函数、事务、动态执行SQL

标签：mysql 函数 存储过程 事务

小编还为您整理了以下内容，可能对您也有帮助：

数据库里面的表、触发器、存储过程、视图这些都是什么东西？？？

表：里面存储的都是真实的数据

视图：就是一个表或者多个表的一个映射，一般只做查询使用。比如你想要的数据存在两个表里，但你查询时不想每次都写关联，那么你创建一个视图，以后只查询这个视图就可以（查询时视图与查询表语法一样）。

触发器：做数据库操作的时候，还希望相关的数据同步操作就用触发器，比如想要向A表插入数据的时候，同时向B表插入，这样写过触发器每次向A表插入数据之后就会自动向B表插入。

存储过程：这个一般是沉淀数据使用的，当你需要查询的数据不能用一个sql语句查出或者sql语句查询速度特别慢，想要提高效率，就会用到存储过程，先把需要的数据沉淀到结果表里然后直接查询结果表就能提高效率。

SQL Server中什么时候使用存储过程？触发器？事务？视图？游标？有什么区别？帮忙举个例子？

存储过程： SQL比较复杂 逻辑比较多的时候用存储过程比较好。

触发器： 就是给在某一张表改变后其他表也需要改变的时候 用触发器

事务：就是一次执行的新增 、修改、 删除的SQL比较多的时候 或者执行的时间比较长 或者 可能执行的SQL会出现错误的时候 就需要有事务。

视图： 就是几个表关联查询而已。但是如果经常这几张表查询 那就可以考虑建一个视图。

游标： 一般不到万不得已不用的。应为效率低。 游标就是循环用的。

以上是我自己的一个看法。

sql视图 存储过程 触发器各自的优点是什么?

视图的优点：

提高数据安全性，可以不让用户看到表中的某个字段。比如password，你只给他们执行视图的权限，不给执行表的权限，他们就无法查看全部数据。

还有可以建立一个视图，内容包括两个表，更新的时候只需要指定ID，而不用管它来自哪个表，对应表中的数据就会自动更新。

存储过程的优点：

包括视图的所有优点，还可以让不懂数据库的人也能也用数据库，还有就是方便程序计设，比如我负责前台程序设计，你负责写存程，我不用管你是怎么写，最后只接调用，我们分工明确，我也不需要懂你所懂的，这为用不懂语言和不同专业的人在一起合作提供了良好的平台。提高开发效率。

触发器的优点：

保证数据的正确性和逻辑，比如订单表中新增一条数据，对应在库存表中会减少一个产品一样。
还有保证数据的安全性，比如当用户删除表A，我们可以判断他是否为Admin组的用户，如果不是，就会给出错误的提示，并将事务回滚。

我知道的就这么多了。。。嘿嘿。

sql视图 存储过程 触发器各自的优点是什么?

视图的优点：

提高数据安全性，可以不让用户看到表中的某个字段。比如password，你只给他们执行视图的权限，不给执行表的权限，他们就无法查看全部数据。

还有可以建立一个视图，内容包括两个表，更新的时候只需要指定ID，而不用管它来自哪个表，对应表中的数据就会自动更新。

存储过程的优点：

包括视图的所有优点，还可以让不懂数据库的人也能也用数据库，还有就是方便程序计设，比如我负责前台程序设计，你负责写存程，我不用管你是怎么写，最后只接调用，我们分工明确，我也不需要懂你所懂的，这为用不懂语言和不同专业的人在一起合作提供了良好的平台。提高开发效率。

触发器的优点：

保证数据的正确性和逻辑，比如订单表中新增一条数据，对应在库存表中会减少一个产品一样。
还有保证数据的安全性，比如当用户删除表A，我们可以判断他是否为Admin组的用户，如果不是，就会给出错误的提示，并将事务回滚。

我知道的就这么多了。。。嘿嘿。

MySQL存储过程中实现执行动态SQL语句的方法

本文实例讲述了MySQL存储过程中实现执行动态SQL语句的方法。分享给大家供大家参考。具体实现方法如下：
mysql>
mysql>
delimiter
$$
mysql>
mysql>
CREATE
PROCEDURE
set_col_value
->
(in_table
VARCHAR(128),
->
in_column
VARCHAR(128),
->
in_new_value
VARCHAR(1000),
->
in_where
VARCHAR(4000))
->
->
BEGIN
->
DECLARE
l_sql
VARCHAR(4000);
->
SET
l_sql=CONCAT_ws('
',
->
'UPDATE',in_table,
->
'SET',in_column,'=',in_new_value,
->
'
WHERE',in_where);
->
SET
@sql=l_sql;
->
PREPARE
s1
FROM
@sql;
->
EXECUTE
s1;
->
DEALLOCATE
PREPARE
s1;
->
END$$
Query
OK,
0
rows
affected
(0.00
sec)
希望本文所述对大家的MySQL数据库程序设计有所帮助。

MySQL存储过程中实现执行动态SQL语句的方法

本文实例讲述了MySQL存储过程中实现执行动态SQL语句的方法。分享给大家供大家参考。具体实现方法如下：
mysql>
mysql>
delimiter
$$
mysql>
mysql>
CREATE
PROCEDURE
set_col_value
->
(in_table
VARCHAR(128),
->
in_column
VARCHAR(128),
->
in_new_value
VARCHAR(1000),
->
in_where
VARCHAR(4000))
->
->
BEGIN
->
DECLARE
l_sql
VARCHAR(4000);
->
SET
l_sql=CONCAT_ws('
',
->
'UPDATE',in_table,
->
'SET',in_column,'=',in_new_value,
->
'
WHERE',in_where);
->
SET
@sql=l_sql;
->
PREPARE
s1
FROM
@sql;
->
EXECUTE
s1;
->
DEALLOCATE
PREPARE
s1;
->
END$$
Query
OK,
0
rows
affected
(0.00
sec)
希望本文所述对大家的MySQL数据库程序设计有所帮助。

数据库事务、存储过程、函数以及触发器之间的区别和联系

数据库事务、存储过程、函数以及触发器之间的区别和联系如下：

一、事务概念

事务（Transaction）是指构成单一逻辑工作单元的操作集合，要么完整地执行，要么完全不执行。

1、如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）。

2、同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在的运行。

二、事务举例

用一个常用的“A账户向B账户汇钱”的例子来说明如何通过数据库事务保证数据的准确性和完整性。熟悉关系型数据库事务的都知道从账户A到账户B需要6个操作，分别为：

1、从A账户中把余额读出来（500）。

2、对A账户做减法操作（500-100）。

3、把结果写回A账户中（400）。

4、从B账户中把余额读出来（500）。

5、对B账户做加法操作（500+100）。

6、把结果写回B账户中（600）。

三、事务特性

并非任意的对数据库的操作序列都是数据库事务，事务应该具有4个属性，分别为：原子性、一致性、隔离性和持久性。这四个属性通常称为ACID特性。

1、原子性（Atomicity）：一个事务对数据库的所有操作，是一个不可分割的工作单元，这些操作要么全部被执行，要么都不执行；保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。假如执行到第5步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。

2、一致性（Consistency）：事务应确保数据库的状态从一个一致状态转为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束；在转账之前，A和B的账户有500+500=1000元钱。在转账之后，A和B的账户中也应该共有400+600=1000元钱。

也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态，同时一致性还能保证账户余额不会变成负数等。

注：一致性与原子性是密切相关的，原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。

比如上面的例子，在第5步的时候，对B账户做加法时只加了50元。那么，该过程可以符合原子性，但是数据的一致性就出现了问题。因此，事务的原子性与一致性缺一不可。

3、隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；在 A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。

如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作，那么当两个事务都结束的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。

4、持久性（Durability）：一个事务一旦提交，它对数据库的修改应该永久保存在数据库中。

例如我们在数据库，用update语句更新某条记录时，会默认开启一个数据库事务，当我们执行（F8）这条语句后，再次查询这条记录，已经被更新，但没有提交事务，事务并没有对数据库产生影响。此时如果关闭查询窗口（出现故障），并未对数据库记录产生影响，只有将事务提交之后，这条记录才会真正并永久的更新。

四、事务并发

事务并发：一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库，数据库中的相同数据可能同时被多个事务访问，如果没有采取必要的隔离措施，就会导致各种并发问题，破坏数据的完整性。

并发问题归结：数据问题（脏读、幻读、不可重复读）、数据更新问题（更新丢失）。

一、存储过程的概念

①存储过程（Stored Procere）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。

②存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（可以有参数，也可以没有）来执行它。

③存储过程是流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。

④存储过程，可由应用程序通过一个调用来执行，而且允许用户声明变量。

⑤同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

二、存储过程的优点

①存储过程的使用大大增强了SQL语言的功能和灵活性。

存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

②可保证数据的安全性和完整性。

通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性，（就像事务的原子性：要么事务内的所有SQL语句全部执行成功，要么全部不成功）。

③在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成（因为已经提前经过编译），所以存储过程能以极快的速度执行。

④可以降低网络的通信量，客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多（在远程访问时体现出来）。

⑤存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

⑥当对数据库进行复杂操作时（如对多个表进行Update，Insert，Query，Delete时），可将此复杂操作用存储过程封装起来，与数据库提供的事务处理结合一起使用。比如每一步对数据库的操作用一个事务来完成，把这些事务全都放在一个存储过程中。

⑦存储过程可以重复使用，可减少数据库开发人员的工作量。

⑧安全性高，可设定只有某些用户才具有对指定存储过程的使用权。

三、存储过程缺点

①调试麻烦：但是用PL/SQL Developer调试很方便，弥补这个缺点。

②移植问题：数据库端代码当然是与数据库相关的，但是如果是做工程型项目，基本不存在移植问题。

③重新编译问题：因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。比如A存储过程调用B存储过程，使用B的返回值作为参数，如果B的参数或返回值发生改变时，会对调用她的A产生影响，此时存储过程就要重新编译，设置成运行时刻自动编译。

④维护比较困难：如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

四、存储过程的种类

①系统存储过程：一般以“sp_”开头，用来进行系统的各项设定，获取配置信息，相关管理工作。

②本地存储过程：用户创建的存储过程，是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

③临时存储过程：分为两种存储过程。

一是本地临时存储过程，以井字号（#）作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它。

二是全局临时存储过程，以两个井字号（##）号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

④远程存储过程：在SQL Server2005中，远程存储过程（Remote Stored Proceres）是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令（在SQL*Plus命令行窗口中）执行一个远程存储过程。

⑤扩展存储过程：扩展存储过程（Extended Stored Proceres）是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

五、存储过程的特性

1、存储过程与函数的区别

①返回值：函数只能返回一个变量，而存储过程可以返回多个，对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。

②存储过程一般是作为一个的部分来执行（EXECUTE语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。SQL语句中不可用存储过程，而可以使用函数。

③存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强，比较单一。

2、存储过程与事务的区别

①存储位置：事务在程序中被调用，保存在调用以及实现它的代码中，存储过程可以在数据库客户端直接被调用，经编译后存储在数据库中。

②运行方式：事务在每次被调用的时候执行其中的SQL语句，存储过程预先经过编译，并不是每次被调用时都会执行一遍其中的SQL语句。

③事务有严格的一致性和原子性，使用的安全性高，存储过程则没有这些特性，在进行一些复杂的操作时，为了保证操作的准确性，可以在存储过程中调用事务，然后判断事务的执行结果是否成功来确保操作的准确性。

3、触发器

①概念及作用

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、Insert、Delete这些操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

（1）强化约束（Enforce restriction）触发器能够实现比CHECK语句更为复杂的约束。

（2）跟踪变化（Auditing changes）触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。

（3）级联运行（Cascaded operation）触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。

（4）存储过程的调用（Stored procere invocation）为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS（数据库管理系统）本身之外进行操作。

由此可见，触发器可以解决高级形式的业务规则或复杂行为，以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理，此外一个表的同一类（Insert、Update、Delete）的多个触发器能够对同一种数据操作采取多种不同的处理。

总体而言，触发器性能通常比较低，当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中，可见触发器所参照的其它表的位置决定了操作要花费的时间长短。

存储过程、索引、视图、触发器 的区别

存储过程 一些SQL的集合,执行速度快,执行后会存储在数据库中,方便以后调用

索引 加快查询速度,不过,但索引的创建有一定的限速,超过了就会减慢降低效率

视图 视图有点像快照,可以用来查看复杂的结构,它是一张虚表,只能用于做查询用

触发器 用于保证主、外键的安全性，是隐式调用的，主要是行级触发，语句触发和替代触发器