存储过程与函数

存储过程与函数

 存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。

存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和 CREATE FUNCTION.使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。

1.创建存储过程

CREATE PROCEDURE 为用来创建存储函数的关键字；

IN表示输入参数 OUT表示输出参数,INOUT表示既可以输入也可以输出；

-- 不带IN OUT INOUT 的参数
DELIMITER //
CREATE PROCEDURE pro_inst_sel(dept_id INT)
BEGIN
	INSERT INTO dept VALUES(NULL,'采购部');
	SELECT * FROM dept WHERE dept_id=id;
END//
DELIMITER ;

CALL pro_inst_sel(2);

-- 带IN的参数
DELIMITER //
CREATE PROCEDURE pro_inst_sel2(IN dept_id INT)
BEGIN
	INSERT INTO dept VALUES(NULL,'采购部');
	SELECT * FROM dept WHERE dept_id=id;
END//
DELIMITER ;

CALL pro_inst_sel2(3);

-- -- 带out的参数
DELIMITER //
CREATE PROCEDURE pro_inst_sel3(OUT dept_id INT)
BEGIN
	INSERT INTO dept VALUES(NULL,'采购部');
	SELECT * FROM dept WHERE dept_id=id;
END//
DELIMITER ;


CALL pro_inst_sel3(4);

-- -- 带out的参数
DELIMITER //
CREATE PROCEDURE pro_inst_sel4(OUT dept_id INT)
BEGIN
	INSERT INTO dept VALUES(NULL,'采购部');
	SELECT COUNT(*) INTO id FROM dept;
END//
DELIMITER ;


CALL pro_inst_sel4(@num);
SELECT @num;

提示：当使用DELIMITER命令时，应该避免使用反斜杠（\）字符，因为反斜杠是MySQL的转义字符。

--查看有多少存储过程

SHOW PROCEDURE STATUS;

 --单独查看

SHOW PROCEDURE STATUS WHERE Db='ch8_db';

--查看状态

SHOW CREATE PROCEDURE （存储过程的名称）;
SHOW CREATE PROCEDURE 数据表名（存储过程的名称）;

---从INFORMATION_SCHEMA ROUTINES表中查看存储过程。

SELECT * FROM information_shema.Routines WHERE ROUTINE_NAME='sp_name';

 

 

--修改存储过程

修改存储过程CountProc的定义。将读写权限改为MODIFS SQL DATA, 并指明调用者可以执行。 

ALTER PROCEDUE CountProc MODIFIES SQL DATA SQL SECURITY INVOKER;

 

 

 

--删除存储过程

DROP PROCEDURE (存储过程名称);

变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN....END程序中的

1.定义变量

DECLARE var_name date_type;

例：

定义名称为myparam的变量，类型为INT类型，默认值为100,

DECLARE myparam INT DEFAULT 100;

2.为变量赋值

定义变量后，为变量赋值可以改变变量的默认值，MySQL中使用的SET语句为变量赋值。
SET var_name =expr{,var_name=expr}....;

 

定义条件和处理程序

特定条件需要特殊处理。

1.定义条件

DECLARE condition_name CONDITION FOR{condition_type}

{condition_type}:
SQLSTATE {VALUE} sqlstate_value | mysql_error_code

定义ERROR 1148(42000)错误，名称为command_not_allowed。可以用倆种不同的方法来定义。

//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITON FOR 1148;

 2.定义处理程序

DECLARE handler_type HANDLER FOR condition_value{,...}sp_statement handler_type:
    CONTINUE | EXIT | UNDO

condition_value:
    SQLSTATE [VALUE]sqlstate_value
|  condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code

 光标的使用

查询语句返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标

MySQL中使用DECLARE关键字来声明光标，

DECLARE cursor_name CURSOR FOR select_statement

 2.打开光标

OPEN cursor_name{光标名称}

3.使用光标

FETCH curror_name INTO var_name {, var_name}....{参数名称}

4.关闭光标

CLOSE curror_name{光标名称}

 

 

 

设计一个存储过程

-- 有局部变量的
DELIMITER //
CREATE PROCEDURE pro_s1()
BEGIN
	-- 声明一个局部变量id，默认值为2
	DECLARE id INT DEFAULT 2;
	SELECT * FROM dept WHERE dept_id=id;
END //
DELIMITER ;

CALL pro_s1();

 

-- 有局部变量的
DELIMITER //
CREATE PROCEDURE pro_ss()
BEGIN
	-- 声明一个局部变量id，默认值为2
	DECLARE id INT DEFAULT 2;
	-- 给变量重新赋值
	SET id=5;
	SELECT * FROM dept WHERE dept_id=id;
END //
DELIMITER ;

CALL pro_ss();

 

-- 通过一个存储过程判断两个参数是否相等
DELIMITER //
CREATE PROCEDURE pro_than(IN a INT,IN b INT)
BEGIN
	IF a=b THEN
		SELECT '等于' AS 'jk';
	ELSE
	SELECT '不等于' AS 'jk';
END IF;
END//
DELIMITER ;

CALL pro_than(3,2);
-- 成绩在90-100为优秀 80-90良好 60-80合格 0-60不合格
DELIMITER //
CREATE PROCEDURE pro_gre(IN a INT)
BEGIN
	IF a>100 OR a<0  THEN
	SELECT '输入有误'AS '结果';
	ELSEIF a>=90  THEN
		SELECT '优秀' AS '等级';
	ELSEIF a>=80  THEN
		SELECT '良好' AS '等级';
	ELSEIF	a>=60 THEN
		SELECT '合格' AS '等级';
	ELSE 
		SELECT '不合格' AS '结果';
	END IF;
END//
DELIMITER ;

CALL pro_gre(80);

drop PROCEDURE pro_gre;

流程控制的使用

 

1.IF语句

IF语句包含多个条件判断,根据判断的结果为TRUE 或者FALSE执行相应的语句

IF expr_condition THEN statement_list
    {ELSETF expr_condtiion THEN statement_list}
    {ELSE statement_list}
END IF

2.CASE语句

CASE是另一个进行条件判断的语句，有两种语句格式。

CASE case_expr
    WHEN when_value THEN statement_list
    {WHEN when_value THEN statement_list}
    {ELSE statement_list}
END CASE

--第二种格式
CASE
    WHEN expr_condition THEN statement_list
    {WHEN expr_condition THEN statement_list}....
    [ELSE statement_list]
END CASE

 3.LOOP语句

LOOP循环语句用来重复执行语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不是进行条件判断。LOOP内的语句一直重复执行，直到循环被退出，跳出循环过程，使用LEAVE子句，LOOP语句的基本格式：

[loop_label:]LOOP
    statement_list
END LOOP {loop_label}

4.LEAVE语句

LEAVE 语句用来退出任何被标注的流程控制制造，LEAVE 语句

LEAVE label

 5.ITERATE 语句将执行转到语句段开头

ITERATE label

6.REPEAT语句

REPEAT 语句创建一个带条件判断的循环过程,每次语句执行完毕之后，会对条件表达式进行判断

DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTLL id >= 10
END REPEAT;

7.WHILE 语句

{while_label : } WHILE expr_condition DO
statement_list
END WHILE{while_label}