九、MySql数据库--存储过程（十）

一、存储过程

1.1 什么是存储过程

  			存储过程: 带有逻辑的sql语句
					  之前的sql没有条件判断，没有循环
					  存储过程带上流程控制语句（if  while）

1.2 存储过程特点

			1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
			2）移植性很差！不同类型数据库的存储过程是不能移植。
			  （Mysql中的存储过程不能移植到oracle中执行。）

二、Mysql的变量

	 	
	 	+++ 全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
	 	
			        查看所有全局变量： show variables
			        查看某个全局变量： select @@变量名
			        修改全局变量： set 变量名=新值
			        
						        character_set_client: mysql服务器的接收数据的编码
						        character_set_results：mysql服务器输出数据的编码
			        
		 +++ 会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。
		              如果连接断开，那么会话变量全部丢失！(一次连接有效)
		           
		            定义会话变量: set @变量=值
		            查看会话变量： select @变量
		            修改会话变量: set @变量=值

        
          +++ 局部变量： 在存储过程中使用的变量就叫局部变量。
                         只要存储过程执行完毕，局部变量就丢失！！（一个存储过程中有效）
						
						在存储过程中定义局部变量：
								定义局部变量：DECLARE i INT DEFAULT 1;
								查看局部变量： select i;
								修改局部变量：set i=i+1;

2.1 例子：全局变量

			SHOW VARIABLES ;   -- 查看所有的全局变量（内置变量）
			SHOW VARIABLES LIKE 'character_%'; -- 查看所有的字符相关的全局变量
			
			select @@endcharacter_set_client ; -- 查看某个全局变量
			set endcharacter_set_client = 'gbk' --设置某个全局变量

图片中的全局变量指的是mysql字符相关的变量。设置其中的属性，可以控制mysql的乱码问题。

2.2 例子：会话变量

	会话变量赋值---方式一
	
					--- 设置存储过程，输出str。
					DELIMITER $
					CREATE PROCEDURE test(OUT str VARCHAR(20))
					BEGIN
						SET str='hello world';
						
					END $
					
					--调用存储过程，给str会话变量赋值
					CALL test(@str)
					
					-- 查看会话变量 。   str=hello world
					SELECT @str;

	会话变量赋值---方式二
		
					SET @str='cc';
					SELECT @str;

例子:局部变量

				DELIMITER $
				CREATE PROCEDURE test2()
				BEGIN
						-- 局部变量声明
						DECLARE i INT DEFAULT 0;
						DECLARE r INT DEFAULT 1;
						
						--操作局部变量
						set i=i+1;
				END $
		
		str 就是局部变量。

三、存储过程的语法

3.1 创建存储过程

	-- 创建存储过程
	
				DELIMITER $      					 -- 声明存储过程的结束符
				CREATE PROCEDURE pro_test()          --存储过程名称(参数列表)
				BEGIN             
					SELECT * FROM employee;
				END $         
				
				-- 执行存储过程
				CALL pro_test();         			 -- CALL 存储过程名称(参数);
	

	
	-- 创建有参数的存储过程
	
				参数：
				IN：   表示输入参数，可以携带数据带存储过程中
				OUT： 表示输出参数，可以从存储过程中返回结果
				INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

3.2 带有输入参数的存储过程

				需求：传入一个员工的id，查询员工信息
				
					DELIMITER $
					CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
					BEGIN
						SELECT * FROM employee WHERE id=eid;
					END $ 
					
					-- 调用
					CALL pro_findById(4);

3.2 带有输出参数的存储过程

			DELIMITER $
			CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
			BEGIN
			        -- 给参数赋值
				SET str='helljava';
			END $


			CALL pro_testOut(@NAME);	--1)定义一个会话变量name, 
										--2)使用name会话变量接收存储过程的返回值

			SELECT @NAME;			-- 查看变量值

3.3 带有输入输出参数的存储过程

			
			DELIMITER $
			CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 输入输出参数
			BEGIN
			   -- 查看变量
			   SELECT n;
			   SET n =500;
			END $
			
			-- 调用
			SET @n=10;
			
			CALL pro_testInOut(@n);
			
			SELECT @n;

3.4 带有条件判断的存储过程

					需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,
									   如果3，返回“星期三”。其他数字，返回“错误输入”;

					DELIMITER $
					CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
					BEGIN
						IF num >100 || num=1 THEN
							SET str='星期一';
						ELSEIF num=2 THEN
							SET str='星期二';
						ELSEIF num=3 THEN
							SET str='星期三';
						ELSE
							SET str='输入错误';
						END IF;
					END $
					
					CALL pro_testIf(4,@str);
					 
					SELECT @str;

3.5 带有循环功能的存储过程

		需求： 输入一个整数，求和。例如，输入100，统计1-100的和

			DELIMITER $
			CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
			BEGIN
				-- 定义一个局部变量
				DECLARE i INT DEFAULT 1;
				DECLARE vsum INT DEFAULT 0;
				WHILE i<=num DO
				      SET vsum = vsum+i;
				      SET i=i+1;
				END WHILE;
				SET result=vsum;
			END $
						
			 
			CALL pro_testWhile(100,@result);
			
			SELECT @result;
			

3.6 使用查询的结果赋值给变量,返回（INTO）

				DELIMITER $
				CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
				BEGIN
					SELECT empName INTO vname FROM employee WHERE id=eid;
				END $
				
				CALL pro_findById2(1,@NAME);
				
				SELECT @NAME;

例子：编写一个存储过程，计算语文成绩的平均分大于90，输出优秀，其余输出良好。

DROP PROCEDURE IF EXISTS cc;
DELIMITER &
CREATE PROCEDURE cc(OUT str VARCHAR(20))
BEGIN
	
	-- 声明一个局部变量
	DECLARE s INT DEFAULT  0;	
	
	--查询语文成绩的平均分，并赋值给局部变量
	SELECT AVG(chinese) INTO s FROM student;
	
	--判断结果
	IF s>= 90 THEN 
		SET str='优秀';
	ELSEIF s<90 AND s>= 70 THEN
		SET str='良好';
	ELSE
		SET str='不及格';
	END IF;
	
END &

CALL  cc(@rr);
SELECT @rr;

总结

	1）数据约束（表约束）
			 默认值： default 默认值
			 非空：   not null
			唯一： unique
			主键： primary key （非空+唯一）
			自增长： auto_increment
			外键： foreign key   约束两种表
		2）关联查询（多表查询）
			2.1 交叉连接（产生笛卡尔积：原因：连接条件不足够）  表数量-1
			2.2 内连接查询： inner join  
					只有满足连接条件的数据才会显示！！！
			2.3 左【外】连接查询：left [outer] join
					左表的数据必须全部显示，用左表去匹配右表的数据，如果右表有符号条件的数据则显示符					合条件的数据；如果不符合条件，则显示null。
			2.4 右【外】连接查询： right [outer] join
					右表的数据必须全部显示，用右表去匹配左表的数据，如果左表有符号条件的数据则显示符					合条件的数据；如果不符合条件，则显示null。
			2.5 自连接查询	
		3）存储过程
				-- 创建存储过程语法
				delimeter 结束符号
				create procedure 名称(IN/OUT/INOUT  参数名称 参数类型)	
				begin
					带逻辑的sql语句
				end 结束符号

				--调用存储过程
				CALL 存储过程名称(实际参数);