数据库sql 存储过程及事务

一、增删改

insert into fruits(f_id,s_id,f_name,f_price) values(1,110,'pich',2.3)

update fruits set f_name = 'pick' where f_id ='1'

DELETE from fruits where f_id = '1'

二、存储过程

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

CREATE
    [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

[begin_label:] BEGIN
[statement_list]
……
END [end_label]

2.1 例子1

DROP PROCEDURE IF EXISTS Proc;

DELIMITER //

CREATE PROCEDURE Proc()

BEGIN

    SELECT * FROM t3;

END//

DELIMITER ;

CALL Proc();

这里的逻辑是

1、先判断是否有Proc() 这个存储过程，有就drop掉

2、创建Proc() 存储过程

3、执行Proc() 存储过程

注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程

中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”结束存储过程。

存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符！！！！！！！！！！！

 

2.2 例子2 含参数的存储过程

drop procedure if exists delete_matches;

delimiter //

create procedure delete_matches( in p_f_id char(10))

begin

    delete from fruits

    where f_id = p_f_id;

end //

delimiter ;

 

调用： call delete_matches('b1');

2.3 为语句贴标签

label1: BEGIN
    label2: BEGIN
        label3: BEGIN
            statements;
        END label3 ;
    END label2;
END label1

标签有两个作用：

　　①增强代码的可读性

　　②在某些语句(例如:leave和iterate语句)，需要用到标签

2.4  变量赋值

2.4.1 不使用@  如果在declare 中使用@，会导致存储过程报错

drop procedure if EXISTS out_param;
delimiter //
create procedure out_param(out p_out char(255))
BEGIN
    declare paramk char(255) default 'nk';
    SELECT  f_name into paramk
    from fruits
    where f_id='b1';
    set p_out = paramk;
END //
delimiter ;

调用
call out_param(@param);
select @param

2.4.2 使用@ 将查询结果赋值给变量，则不需要declare

2.4.2.1   

    set @cnt = (select count(*) from test_tbl);
    select @cnt;

2.4.2.2

    select count(*) into @cnt1 from test_tbl;
    select @cnt1;

2.4.2.3  多个列的情况下似乎只能用 into 方式

    select max(status), avg(status) into @max, @avg from test_tbl;
    select @max, @avg;

2.4.2.4 混用的情况

drop procedure if EXISTS out_param;
delimiter //
create procedure out_param(out p_out char(255))
BEGIN
    declare paramk char(255) default 'nk';
    SELECT  f_name,f_price into paramk,@paramp
    from fruits
    where f_id='b1';
    set p_out = paramk;
END //
delimiter ;

调用：

call out_param(@param);
select @paramk,@paramp

 

三、事务

事务的基本原理

 
普通的执行，之所以是立即执行并生效，因为默认的，MySQL对sql语句的执行是自动提交的！所以，开启一个事务的本质，就是关闭了以前的自动提交的功能，而是由用户手动提交（利用commit语句）！

总结事务的步骤：

1，  开启事务

2，  如果执行成功，就提交commit

3，  如果有任何一条sql语句执行失败，则回滚rollback！

1. START TRANSACTION;  
2. UPDATE account SET balance=balance-1000 WHERE id=1;  
3. SAVEPOINT rol_01;  
4. UPDATE account SET balance=balance+1000 WHERE id=2;  
5. ROLLBACK TO SAVEPOINT rol_01;  
6. COMMIT; 

 

转载于:https://my.oschina.net/u/3763295/blog/1625289