mysql学习之存储过程和函数(三) mysql

存储过程和存储函数
1.存储过程和函数的引入
存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL 服务器中存储和执行的，可以减少客户端和服务器端的数据传输。
2.创建存储和函数
CREATEPROCEDURE sp_name([proc_parameter[,…]]) [characteristic…]routine_body sp_name 参数是存储过程的名称； proc_parameter 表示存储过程的参数列表； characteristic 参数指定存储过程的特性； routine_body 参数是 SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束。 proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。 [IN |OUT|INOUT]param_nametype 其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型； Characteristic 参数有多个取值。其取值说明如下： LANGUAGESQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。 [NOT]DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC 表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。 {CONTAINS SQL|NO SQL|READSSQLDATA|MODIFIES SQLDATA} ：指明子程序使用 SQL 语句的限制； CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READSSQLDATA 表示子程序中包含读数据的语句；MODIFIES SQLDATA表示子程序中包含写数据的语句。默认情况下，系统会指定为 CONTAINSSQL； SQLSECURITY{ DEFINER |INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行； INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。 COMMENT‘string’ ：注释信息；

DELIMITER && CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT) READS SQL DATA BEGIN SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT; END && DELIMITER ; CALL pro_book(1,@total);

CREATEFUNCTIONsp_name([func_parameter[,…]]) RETURNS type [characteristic…]routine_body sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；RETURNStype 指定返回值的类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数是 SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束； func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下： param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型，该类型可以是 MySQL 数据库的任意数据类型；

DELIMITER && CREATE FUNCTION func_book (bookId INT) RETURNS VARCHAR(20) BEGIN RETURN ( SELECT bookName FROM t_book WHERE id=bookId ); END && DELIMITER ; SELECT func_book(2);

3.流程控制的使用

DELIMITER && CREATE PROCEDURE pro_user() BEGIN DECLARE a,b VARCHAR(20) ; INSERT INTO t_user VALUES(NULL,a,b); END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user2() BEGIN DECLARE a,b VARCHAR(20) ; SET a='java1234',b='123456'; INSERT INTO t_user VALUES(NULL,a,b); END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user3() BEGIN DECLARE a,b VARCHAR(20) ; SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1; INSERT INTO t_user VALUES(NULL,a,b); END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user4() BEGIN DECLARE a,b VARCHAR(20) ; DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2; OPEN cur_t_user2; FETCH cur_t_user2 INTO a,b; INSERT INTO t_user VALUES(NULL,a,b); CLOSE cur_t_user2; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user5(IN bookId INT) BEGIN SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId; ELSE INSERT INTO t_user VALUES(NULL,'2312312','2321312'); END IF ; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user6(IN bookId INT) BEGIN SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; CASE @num WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId; WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312'); ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312'); END CASE ; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user7(IN totalNum INT) BEGIN aaa:LOOP SET totalNum=totalNum-1; IF totalNum=0 THEN LEAVE aaa ; ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); END IF ; END LOOP aaa ; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user8(IN totalNum INT) BEGIN aaa:LOOP SET totalNum=totalNum-1; IF totalNum=0 THEN LEAVE aaa ; ELSEIF totalNum=3 THEN ITERATE aaa ; END IF ; INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); END LOOP aaa ; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user9(IN totalNum INT) BEGIN REPEAT SET totalNum=totalNum-1; INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); UNTIL totalNum=1 END REPEAT; END && DELIMITER ; DELIMITER && CREATE PROCEDURE pro_user10(IN totalNum INT) BEGIN WHILE totalNum>0 DO INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); SET totalNum=totalNum-1; END WHILE ; END && DELIMITER ; CALL pro_user(); CALL pro_user2(); CALL pro_user3(); CALL pro_user4(); CALL pro_user5(5); CALL pro_user6(6); CALL pro_user7(11); CALL pro_user8(11); CALL pro_user9(11); CALL pro_user10(10);

【mysql学习之存储过程和函数(三)】5.存储过程的查看、修改和删除