批量向权限表插入数据
Mysql 中,单个 Store Procedure(SP) 不是原子操作,而 oracle 则是原子的。所以Mysql需要自己控制事务的开启与提交。
在mysql中如果不适用过程,直接
sql:insert into t_rbac_role_node(role_id,node_id) values(3,4),(3,5),(3,3);
如果一条插入信息有问题,就会全部插入失败。所以要根据情况看使用什么方式。
存储过程
DROP PROCEDURE
IF
EXISTS proc_general_role;
DELIMITER $
CREATE PROCEDURE proc_general_role (
IN roleId INT,
IN beginIndex INT, #node_id的初始值
IN endIndex INT,#node_id的结束值,包含关系
out vReturnValue varchar(500),
out iResult int
)
BEGIN
#声明异常
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
#捕获异常
CAT:BEGIN
GET DIAGNOSTICS CONDITION 1
iResult = RETURNED_SQLSTATE, vReturnValue = MESSAGE_TEXT;
END;
START TRANSACTION;
WHILE
beginIndex <= endIndex DO
INSERT INTO t_rbac_role_node (role_id, node_id)VALUES( roleId, beginIndex );
SET beginIndex = beginIndex + 1;
END WHILE;
COMMIT;
END $
DELIMITER ;
CALL proc_general_role ( 4, 1, 15,@ReturnValue, @Result);
select @Result;
select @ReturnValue;
异常
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number | SQLSTATE error-string | condition}
SQL statement
上述定义包括:
Handler Type (CONTINUE,EXIT)//处理类型 继续或退出
Handler condition (SQLSTATE,MYSQL ERROR,CONDITION)//触发条件
Handler actions(错误触发的操作)
注意:
1、exit只退出当前的block。exit 意思是当动作成功提交后,退出所在的复合语句。即declare exit handler for… 所在的复合语句。
2、如果定义了handler action,会在continue或exit之前执行
发生错误的条件有:
1、MYSQL错误代码
2、ANSI-standard SQLSTATE code
3、命名条件。可使用系统内置的SQLEXCEPTION,SQLWARNING和NOT FOUND
DECLARE EXIT HANDLER FOR SQLEXCEPTION 语句后面可以跟一个 begin end的复合语句块,也可以直接跟一个简单语句例如 :DECLARE EXIT HANDLER FOR SQLEXCEPTION v_succ=0;
获取异常信息GET DIAGNOSTICS CONDITION
GET DIAGNOSTICS CONDITION 语句来获取错误缓冲区的内容,然后把这些内容输出到不同范围域的变量里,以便我们后续灵活操作
GET [CURRENT] DIAGNOSTICS
{
statement_information_item
[, statement_information_item] ...
| CONDITION condition_number
condition_information_item
[, condition_information_item] ...
}
statement_information_item:
target = statement_information_item_name
condition_information_item:
target = condition_information_item_name
statement_information_item_name:
NUMBER
| ROW_COUNT
condition_information_item_name:
CLASS_ORIGIN
| SUBCLASS_ORIGIN
| RETURNED_SQLSTATE
| MESSAGE_TEXT
| MYSQL_ERRNO
| CONSTRAINT_CATALOG
| CONSTRAINT_SCHEMA
| CONSTRAINT_NAME
| CATALOG_NAME
| SCHEMA_NAME
| TABLE_NAME
| COLUMN_NAME
| CURSOR_NAME
condition_number, target:
(see following discussion)