例1 :嵌套游标
CREATE PROCEDURE card_rollback() BEGIN DECLARE done INT DEFAULT 0; -- 游标结束标志 DECLARE value_ INT ; DECLARE cur CURSOR FOR select id from test ;-- table or view DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ; -- 读取完是标志结束 open cur; set @@autocommit=0; -- 手动提交 REPEAT FETCH cur INTO value_ ; -- 重游标中取值 if not done then select '1' ; -- do something BEGIN DECLARE cur_0 CURSOR FOR select id from test ;-- 嵌套游标 end ; end if; UNTIL done END REPEAT; CLOSE cur; end ;
例2:异常处理
delimiter // -- 重新定义换行符 drop PROCEDURE if EXISTS t_insert_table// create procedure t_insert_table() begin /** 标记是否出错 */ declare t_error int default 0; declare t_warn int default 0; /** 如果出现sql异常,则将t_error设置为1后退出操作 */ declare CONTINUE handler for SQLWARNING set t_warn = 1; -- 出错处理 DECLARE EXIT HANDLER FOR SQLEXCEPTION set t_error = 1 ; /** 显式的开启事务,它开启后,事务会暂时停止自动提交*/ -- start transaction; /** 关闭事务的自动提交 */ set autocommit = 0; insert into t_bom_test(parent_id,child_id) values('D','abc'); insert into t_trigger_test(name,age) values('zhangsan',null); /** 标记被改变,表示事务应该回滚 */ if t_error=1 then select 'ee' ; rollback; -- 事务回滚 else commit; -- 事务提交 end if; -- rollback; -- commit; end// delimiter ;
语法定义:
14.1、创建存储过程和函数
14.1.1、创建存储过程
CREATE PROCEDUREsp_name ([proc_parameter[,...]])
[characteristic...] routine_body
procedure 发音 [prə'si:dʒə]
proc_parameter IN|OUT|INOUT param_name type
characteristic n. 特征;特性;特色
LANGUAGESQL 默认,routine_boyd由SQL组成
[NOT]DETERMINISTIC 指明存储过程的执行结果是否是确定的,默认不确定
CONSTAINSSQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA指定程序使用SQL语句的限制
CONSTAINS SQL 子程序包含SQL,但不包含读写数据的语句,默认
NO SQL 子程序中不包含SQL语句
READS SQL DATA 子程序中包含读数据的语句
MODIFIES SQL DATA 子程序中包含了写数据的语句
SQLSECURITY {DEFINER|INVOKER},指明谁有权限执行。
DEFINER,只有定义者自己才能够执行,默认
INVOKER 表示调用者可以执行
COMMENT‘string’ 注释信息
CREATEPROCEDURE num_from_employee (IN emp_id, INT, OUT count_num INT)
READS SQL DATA
BEGIN
SELECTCOUNT(*) INTOcount_num
FROMemployee
WHEREd_id=emp_id;
END
14.1.2、创建存储函数
CREATE FUNCTIONsp_name ([func_parameter[,...]])
RETURNS type
[characteristic...] routine_body
CREATEFUNCTION name_from_employee(emp_id INT)
RETURNSVARCHAR(20)
BEGIN
RETURN (SELECT name FROM employee WHEREnum=emp_id);
END
14.1.3、变量的使用
1.定义变量
DECLARE var_name[,…]type [DEFAULT value]
DECLAREmy_sql INT DEFAULT 10;
2.为变量赋值
SETvar_name=expr[,var_name=expr]…
SELECT col_name[,…]INTO var_name[,…] FROM table_name WHERE condition
14.1.4、定义条件和处理程序
1.定义条件
DECLARE condition_nameCONDITION FOR condition_value
condition value:
SQLSTATE[VALUE] sqlstate_value | mysql_error_code
对于ERROR 1146(42S02)
sqlstate_value: 42S02
mysql_error_code:1146
//方法一
DECLARE can_not_find CONDITION FOR SQLSTATE ‘42S02’
//方法二
DECLARE can_not_find CONDITION FOR 1146
2.定义处理程序
DECLAREhander_type HANDLER FOR condition_value[,…] sp_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE] sqlstate_value | condition_name |SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code
UNDO目前MySQL不支持
1、捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’CANNOT FIND’;
2、捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=’CAN NOT FIND’;
3、先定义条件,然后调用
DECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info=’CANNOT FIND’;
4、使用SQLWARNING
DECLARE EXITHANDLER FOR SQLWARNING SET @info=’CANNOT FIND’;
5、使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’CANNOT FIND’;
6、使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’CANNOT FIND’;
14.1.5、光标的使用
存储过程中对多条记录处理,使用光标
1.声明光标
DECLAREcousor_name COURSOR FOR select statement;
DECLAREcur_employee CURSOR FOR SELECT name, age FROM employee;
2.打开光标
OPENcursor_name;
OPENcur_employee;
3.使用光标
FETCHcur_employee INTO var_name[,var_name…];
FETCH cur_employeeINTO emp_name, emp_age;
4.关闭光标
CLOSEcursor_name
CLOSE cur_employee
14.1.6、流程控制的使用
1.IF语句
IFsearch_condition THEN statement_list
[ELSEIF search_condition THENstatement_list]…
[ELSE statement_list]
END IF
IF age>20THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END
2.CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]…
[ELSE statement_list]
END CASE
CASE
WHEN search_condition THENstatement_list
[WHEN search_condition THENstatement_list]…
[ELSE statement_list]
END CASE
CASE age
WHEN 20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
CASE
WHERE age=20 THEN SET@count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
3.LOOP语句
[begin_label:]LOOP
statement_list
ENDLOOP[end_label]
add_num:LOOP
SET @count=@count+1;
END LOOPadd_num;
4.LEAVE语句
跳出循环控制
LEAVE label
add_num:LOOP
SET @count=@count+1;
LEAVE add_num;
END LOOPadd_num;
5.ITERATE语句
跳出本次循环,执行下一次循环
ITERATE label
add_num:LOOP
SET @count=@count+1;
IF @count=100 THEN LEAVE add_num;
ELSEIF MOD(@count,3)=0 THEN ITERATEadd_num;
SELECT * FROM employee;
END LOOPadd_num;
6.REPEAT语句
有条件循环,满足条件退出循环
[begin_label:]REPEAT
statement_list
UNTIL search_condition
ENDREPEAT[end_label]
REPEAT
SET @count=@count+1;
UNTIL @count=100;
ENDREPEAT;
7.WHILE语句
[begin_label:]WHILEsearch_condition DO
statement_list
ENDREPEAT[end_label]
WHILE@count<100 DO
SET @count=@count+1;
ENDWHILE;
14.2、调用存储过程和函数
存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。执行存储过程和存储函数需要拥有EXECUTE权限。EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中
14.2.1、调用存储过程
CALL sp_name([parameter[,…]]) ;
14.2.2、调用存储函数
存储函数的使用方法与MySQL内部函数的使用方法是一样的
14.3、查看存储过程和函数
SHOW { PROCEDURE| FUNCTION } STATUS [ LIKE ' pattern ' ];
SHOW CREATE {PROCEDURE | FUNCTION } sp_name ;
SELECT * FROMinformation_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;
14.4、修改存储过程和函数
ALTER {PROCEDURE| FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY {DEFINER | INVOKER }
| COMMENT'string'
14.5、删除存储过程和函数
DROP {PROCEDURE| FUNCTION } sp_name;
相关推荐
MySQL视图及存储过程学习笔记
一、存储过程 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户 通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。而...
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该...
MySQL存储过程之java调用 9 MySQL存储过程实现动态查询 12 MySQL应用总结 12 MySQL数据管理 15 数据管理 15 MySQL数据导出 15 MySQL数据还原 15 MySQL灾难性复制恢复总结 16 MySQL授权管理 17 未解决及已解决问题 19...
mysql 的黑框开发 在学习mysql过程中的 黑框连接 包括 登陆数据库 mysql 查表 建表等一些代码
主要给大家介绍了关于mysql存储过程教程之遍历多表记录后插入第三方表中的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起看看吧
MySQL存储过程与函数 MySQL3 MySQL安全机制 MySQL日志管理 MySQL数据备份 备份概述 mysqldump [逻辑] mydumper [逻辑] into outfile [逻辑] xtrabackup [物理] snapshot [物理] tar打包备份 [物理] ...
然后重点讲解了MySQL存储过程的创建、调用、参数、条件和循环语句等知识,同时提供了详细的代码示例进行解释说明,内容系统全面。 适合人群: 需要掌握MySQL高级应用的数据库学习者。文中具体的代码示例可助力开发。 ...
MySQL学习笔记(含基础、运维、进阶三部分) MySQL学习笔记(含基础、运维、进阶三部分) MySQL学习笔记(含基础、运维...高级内容,如存储过程、触发器和索引优化等 MySQL锁、读写分离、分库分表等 实际案例和示例代码
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,约束,分页,建库建表,数据类型,标识列,级联删除...
MySQL核心技术学习笔记,包括从MySQL产品的安装到DQL语言的学习、DML语言的学习、DDL语言的学习、TCL语言的学习、视图、存储过程等,以及相关案例实现语句等等。
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知识点,查询,子查询,分组,排序,常用函数,多表连接,视图,变量,存储过程,函数,分支结构,循环结构等等
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知 识点,查询,子查询,分组,排序,常用函数,多表连接,视图,约束,分页,建库建表,数据类型, 标识列,级联...
MySQL5.5学习笔记,里边是MySQL基础知识,包括对数据库,数据表,视图,数据类型和运算符,MySQL函数,存储过程和函数,以及对数据的增删改查等知识,还有MySQL用户管理以及数据备份与还原的知识!适合初学者学习~
这是我自己学习mysql时的学习笔记,每一个知识点都是自己动手写的,涵盖了mysql中的几乎全部的基础知 识点,查询,子查询,分组,排序,常用函数,多表连接,视图,约束,分页,建库建表,数据类型, 标识列,级联...
存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候调用他就行了。...
个人原创,根据自己的工作总结的,对工作的人事来说帮助很大。。里面包含 mysql的基本知识 还有优化 部分 存储过程 等 里面的语句都完全正确,全部在数据库中验证过
MySQL5.7是一款非常流行...最后,我们将讲解MySQL5.7的高级特性,包括事务的处理、存储过程的使用、触发器的实现等。通过本文的学习,读者将能够掌握MySQL5.7的基本使用技巧,为企业级应用的开发和维护提供有力的支持。
这是我学习mysql数据库的时候做的学习笔记,增删查改,视图,自定义函数,自定义存储过程,触发器,索引,sql编程等等。