数据库之——存储过程（学习笔记）
⼀、存储过程和函数的引⼊
存储过程和函数时在数据库中定义⼀些SQL语句的集合，然后直接调⽤这些存储过程和函数来执⾏已经定义好的SQL语句。存储过程和函数可以避免开发⼈员重复的编写相同的SQL语句。⽽且，存储过程和函数是在MySQL服务器中存储和执⾏的，可以减少客户端和服务器段的数据传输；
⼆、创建存储过程和函数
2.1 创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,...]])
[]routine_body
sp_name 参数是存储过程的名称；
proc_parameter 表⽰存储过程的参数列表；
metatroncharacteristic 参数指定存储过程的特性；
pg insert into select
routine_body 参数是 SQL 代码的内容，可以⽤ END 来标志 SQL 代码的开始和结束。
proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输⼊输出类型、参数名称和参数类型。
[IN |OUT|INOUT]param_nametype
其中，IN 表⽰输⼊参数；OUT 表⽰输出参数；INOUT 表⽰既可以是输⼊，也可以是输出；param_name 参数是 存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型； Characteristic 参数有多个取值。其取值说明如下：
LANGUAGESQL：说明 routine_body 部分是由 SQL 语⾔的语句组成，这也是数据库系统默认的语⾔。
python趣味编程100例pdf下载[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)
2.2创建存储函数
CREATE FUNCTION sp_name([func_parameter[,...]])
RETURNS type
[]routine_body
sp_name 参数是存储函数的名称；func_parameter 表⽰存储函数的参数列表；RETURNStype 指定返回值的 类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是⼀样的；routine_body 参数 是 SQL 代码的内容，可以⽤ END 来标志 SQL 代码的开始和结束；
func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型， 该类型可以是MySQL 数据库的任意数据类型；
//创建存储函数
DELIMITER &&
CREATE FUNCTION func_book(bookId INT)
RETURNS VARCHAR(20)
BEGINmysql查看所有存储过程
RETURN(SELECT bookName FROM t_book WHERE id=bookId);
END
&&
DELIMITER;
//调⽤存储函数
SELECT func_book(2)
2.3 变量的使⽤
1，定义变量
DECLARE var_name [,...] type [DEFAULT value].
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user()
BEGIN
DECLARE a,b VARCHAR(20);//定义变量
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER;
//调⽤存储过程
CALL pro_user();
2，为变量赋值
SET var_name = expr[,var_name=expr]...
SELECT col_name[,..] INTO var_name[,...]
FROM table name WHERE condition
/
/创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user2()
BEGIN
DECLARE a,b VARCHAR(20);//定义变量
//SET a='java1234',b='123456';//⽅式⼀：直接为变量赋值
SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;//⽅式⼆：从其它表中查询给a,b赋值进去
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER;
//调⽤存储过程
CALL pro_user2;
2.4 游标的使⽤
查询语句可能查询出多条记录，在存储过程和函数中使⽤游标来逐条读取查询结果中的记录。游标的使⽤包括声明游标、打开游标、使⽤游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。
1，声明游标
DECLARE cursor_name CURSOR FOR select_statement;
2，打开游标
OPEN cursor_name;
3，使⽤游标
FETCH cursor_name INTO var_name[,];
4，关闭游标
CLOSE cursor_name
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;
2.5 流程控制的使⽤
存储过程和函数中可以使⽤流程控制来控制语句执⾏。MySQL中可以使⽤IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE 语句、REPEAT语句和WHILE语句来进⾏流程控制。
1，IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF
//创建存储过程
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user2 WHERE id = bookId;//将查询到的数据放到全局变量中@num代表全局变量
IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id = bookId;//有数据，那么就更新⼀条数据
ELSE
INSERT INTO t_user VALUES(NULL,'23123321','23123321');//没有数据就插⼊⼀条数据
END IF;
END
&&
DELIMITER;
//调⽤存储过程
CALL pro_user5(5);//有id=5的就更新数据，没有数据iu插⼊⼀条新数据
2，CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user2 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,'213123','123123');
ELSE INSERT INTO t_user VALUES(NULL,'213123123','213123')；
END
&&
DELIMITER;
个人网站html源码//调⽤存储过程
CALL pro_user6(5);//有id=5的就更新数据，没有数据iu插⼊⼀条新数据
3，LOOP，LEAVE语句
LOOP语句可以使某些特定的语句重复执⾏，实现⼀个简单的循环。但是LOOP语句本⾝没有停⽌循环的语句，必须是遇到LEAVE语句等才能停⽌循环。LOOP语句的语法的基本形式如下
[begin_label:]LOOP
Statement_list
END LOOP[end_label]
LEAVE语句主要⽤于跳出循环控制。语法形式如下：
LEAVE label
//创建存储过程
运行shell脚本的命令DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum = bobalNum-1;
IF totalNum=0 THEN LEAVE aaa;
ELSE INSERT INTO t_user VALUES(totalNum,'213123','123123');
END IF;
END LOOP aaa;
END
&&
DELIMITER;
//调⽤存储过程
CALL pro_user7;
4，ITERATOR语句
ITERATE语句也是⽤来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进⼊下⼀次循环。基本语法：
ITERATE label;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论