mysql存储过程快速入门

最新推荐文章于 2024-03-07 14:39:49 发布

Hall_P

最新推荐文章于 2024-03-07 14:39:49 发布

阅读量249

点赞数

文章标签： mysql sql

本文链接：https://blog.csdn.net/Hall_P/article/details/106590235

版权

概述

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

准备工作

建表语句：
create.sql

存储过程的创建和调用

存储过程的创建

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 }

声明语句结束符

在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
```
DELIMITER $$
或
DELIMITER // 
```

声明存储过程

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号
```
BEGIN .... END
```
存储过程体
- 存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
- 过程体格式：以begin开始，以end结束(可嵌套)

标签: 增强代码的可读性; 在某些语句(例如:leave和iterate语句)，需要用到标签

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

参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

实例

/*
*
*mysql存储过程 第一个个示例，展示tb_user中的user_name；
* 默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。
*/
delimiter $$
	DROP PROCEDURE IF EXISTS find_user_by_id;
	CREATE PROCEDURE find_user_by_id(IN user_id CHAR(16))
	BEGIN
		DECLARE user_name varchar(64) DEFAULT NULL;
		SELECT 
			u.user_name 
			into user_name
			FROM tb_user AS u
			WHERE u.user_id = user_id;
		 SELECT user_name;
	END$$
delimiter ;

CALL find_user_by_id('0000000000000001'); -- 存储过程的调用；

/**
* mysql存储过程 第二个示例， 展示out参数的使用
*/

delimiter //
	DROP PROCEDURE IF EXISTS show_out_pram;
	CREATE PROCEDURE show_out_pram(OUT user_name VARCHAR(32))
		COMMENT 'example about how to use out param'
	body_label:BEGIN
		DECLARE user_id CHAR(16) DEFAULT NULL;
		SET user_id='0000000000000001';
		SELECT user_name; -- 此处输出null;
		SELECT 
			u.user_name
			into user_name
			FROM tb_user AS u
			WHERE u.user_id=user_id;
		SELECT user_name; -- zhangsan
	 END body_label //
delimiter ;

SET @out_param='张三';
CALL show_out_pram(@out_param);

变量

声明
```
DECLARE variable_name datatype(size) DEFAULT default_value;
```
在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。

指定变量的数据类型及其大小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。

当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。
```
DECLARE x, y INT DEFAULT 0;
DECLARE name VARCHAR(64);
```

赋值

SET variable_name='value';
SELECT col_name INTO variable_name 
	FROM table_name
	WHERE id='1';

作用域

一个变量有自己的范围(作用域)，它用来定义它的生命周期。如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

如果您在BEGIN END块内声明一个变量，那么如果达到END，它将超出范围。可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

控制流程

条件语句

/**
* mysql存储过程 第三个示例 展示if条件语句
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_if_exam;
	CREATE PROCEDURE proc_if_exam(IN in_param TINYINT) 
		COMMENT 'EXAMPLE SHOWS HOW TO USE IF SENTENCE'
	body_label:BEGIN
		DECLARE table_name CHAR(16) DEFAULT 'tb_user';
		IF in_param=1 THEN 
			SET table_name = 'tb_order';
		ELSEIF in_param=2 THEN
			SET table_name = 'tb_product';
		ELSE
			SET table_name = 'tb_order_detail';
		END IF;
		SET @to_sql = CONCAT("SELECT * FROM ", table_name); -- 直接连接不可行；动态执行SQL
		PREPARE stmt FROM @to_sql; 
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt; -- 释放prepared statement;
	END body_label $$
delimiter ;
--------------------------------
delimiter $$
	DROP PROCEDURE IF EXISTS proc_case_exam;
	CREATE PROCEDURE proc_case_exam(IN in_param TINYINT) 
		COMMENT 'EXAMPLE SHOWS HOW TO USE CASE STATEMENT'
	body_label:BEGIN
		DECLARE table_name CHAR(16) DEFAULT 'tb_user';
		CASE in_param
			WHEN 1 THEN
				SET table_name = 'tb_order';
			WHEN 2 THEN
				SET table_name = 'tb_product';
			ELSE
				SET table_name = 'tb_order_detail';
		END CASE;
		SET @to_sql = CONCAT("SELECT * FROM ", table_name);
		PREPARE stmt FROM @to_sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	END body_label $$
delimiter ;

循环

/**
* mysql存储过程 第四个示例 展示while循环语句
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_while_exam;
	CREATE PROCEDURE proc_while_exam()
		COMMENT 'EXAMPLE SHOWS HOW TO USE WHILE SENTENCE'
	body_label:BEGIN
		DECLARE user_num SMALLINT DEFAULT 0;
		DECLARE user_name VARCHAR(256) DEFAULT '';
		DECLARE temp_name VARCHAR(16) DEFAULT NULL;
		DECLARE user_id CHAR(16) DEFAULT NULL;
		SELECT count(*) INTO user_num FROM tb_user;
		WHILE user_num > 0 DO 
			SET user_id=CONCAT('000000000000000', user_num);
			SELECT 
				u.user_name INTO temp_name
			FROM tb_user AS u WHERE u.user_id=user_id;
			SET user_name=CONCAT(user_name, ', ');
			SET user_name=CONCAT(user_name, temp_name);
			SET user_num = user_num -1;
		END WHILE;
		SELECT user_name;
	END body_label $$
delimiter ;
CALL proc_while_exam()


----------------------------------------------------
/**
* mysql存储过程 第五个示例 展示repeat循环语句
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_repeat_exam;
	CREATE PROCEDURE proc_repeat_exam()
		COMMENT 'EXAMPLE SHOWS HOW TO USE repeat SENTENCE'
	body_label:BEGIN
		DECLARE user_num SMALLINT DEFAULT 0;
		DECLARE user_name VARCHAR(256) DEFAULT '';
		DECLARE temp_name VARCHAR(16) DEFAULT NULL;
		DECLARE user_id CHAR(16) DEFAULT NULL;
		SELECT count(*) INTO user_num FROM tb_user;
		REPEAT 
			SET user_id=CONCAT('000000000000000', user_num);
			SELECT 
				u.user_name INTO temp_name
			FROM tb_user AS u WHERE u.user_id=user_id;
			SET user_name=CONCAT(user_name, ', ');
			SET user_name=CONCAT(user_name, temp_name);
			SET user_num = user_num -1;
		UNTIL user_num=0 END REPEAT;
		SELECT user_name;
	END body_label $$
delimiter ;
CALL proc_repeat_exam()

-------------------------------------------------------------------
/**
* mysql存储过程 第六个示例 展示LOOP循环语句
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_loop_exam;
	CREATE PROCEDURE proc_loop_exam()
		COMMENT 'EXAMPLE SHOWS HOW TO USE LOOP SENTENCE'
	body_label:BEGIN
		DECLARE user_num SMALLINT DEFAULT 0;
		DECLARE user_name VARCHAR(256) DEFAULT '';
		DECLARE temp_name VARCHAR(16) DEFAULT NULL;
		DECLARE user_id CHAR(16) DEFAULT NULL;
		SELECT count(*) INTO user_num FROM tb_user;
		loop_label:LOOP
			SET user_id=CONCAT('000000000000000', user_num);
			SELECT 
				u.user_name INTO temp_name
			FROM tb_user AS u WHERE u.user_id=user_id;
			SET user_name=CONCAT(user_name, ', ');
			SET user_name=CONCAT(user_name, temp_name);
			SET user_num = user_num -1;
			IF user_num=0 THEN 
				LEAVE loop_label;
			ELSE
				ITERATE loop_label;
			END IF;
		END LOOP loop_label;
		SELECT user_name;
	END body_label $$
delimiter ;
CALL proc_loop_exam()

游标

/*
* mysql存储过程 第七个示例 展示Cursor游标；
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_cursor_exam;
	CREATE PROCEDURE proc_cursor_exam()
		COMMENT 'HOW TO USE CUSOR STATEMENT'
	body_label: BEGIN
		
		
		DECLARE v_user_id CHAR(16) DEFAULT NULL;
		DECLARE flag INT DEFAULT 1;
		DECLARE cr_users CURSOR FOR SELECT user_id FROM tb_user;
		DECLARE EXIT HANDLER FOR NOT FOUND SET flag:=0;
		
		
		OPEN cr_users;
		
		REPEAT 
			FETCH cr_users INTO v_user_id;
			INSERT INTO tb_user_bak 
				SELECT * FROM tb_user AS u WHERE u.user_id = v_user_id;
		UNTIL flag=0 END REPEAT;
		
		CLOSE cr_users;
	
	END body_label $$
delimiter ;

CALL proc_cursor_exam();

异常处理

/*
* mysql存储过程 第八个示例 Exception处理；
*/

delimiter $$
	DROP PROCEDURE IF EXISTS proc_exception_exam;
	CREATE PROCEDURE proc_exception_exam()
		COMMENT 'HOW TO USE EXCEPTION'
	body_label:BEGIN
		DECLARE var_num INT DEFAULT 0;
		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
			ROLLBACK;
			SELECT 'ERROR OCCURED-- TERMINATING';
		END;
		
		REPEAT
			SET var_num = var_num +1;
			IF var_num=3 THEN
				SELECT * FROM tb_name; -- tb_name不存在；
			END IF;
		UNTIL var_num=10
		END REPEAT;
		
	END body_label $$
delimiter ;

事务

/*
* mysql存储过程 第九个示例 事务
*/
delimiter $$
	DROP PROCEDURE IF EXISTS proc_trans_exam;
	CREATE PROCEDURE proc_trans_exam()
		COMMENT 'HOW TO USE TRANSACTION'
	body_label:BEGIN
		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
			ROLLBACK;
			SELECT 'ERROR OCCURED-- TERMINATING';
		END;
		DECLARE EXIT HANDLER FOR SQLWARNING
		BEGIN
			ROLLBACK;
			SELECT 'WARNING OCCURED-- TERMINATING';
		END;
		
		START TRANSACTION;
			DELETE FROM tb_user_bak;
			DROP TABLE tb_user_bak;
		COMMIT;
		
	END body_label $$
delimiter ;

存储过程对象

SHOW CREATE PROCEDURE my_db.proc_trans_exam; -- 查看当前存储过程的详细。
DROP PROCEDURE my_db.proc_case_exam; --删除存储过程；
SHOW PROCEDURE status WHERE db='my_db'; --查看数据库下的存储过程；
ALTER PROCEDURE proc_name --修改存储过程；

Hall_P

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql存储过程快速入门

概述存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。优点存储过程可封装，并隐藏复杂的商业逻辑。存储过程可以回传值，并可以接受参数。存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。存储过程可
复制链接

扫一扫