数据库之——存储过程（学习笔记）

最新推荐文章于 2023-03-14 21:25:46 发布

尘小新

最新推荐文章于 2023-03-14 21:25:46 发布

阅读量742

点赞数

分类专栏：数据库文章标签：存储过程存储函数游标流程控制 SQL

本文链接：https://blog.csdn.net/weixin_39941298/article/details/81081724

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、存储过程和函数的引入

存储过程和函数时在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器段的数据传输；

二、创建存储过程和函数

2.1 创建存储过程

CREATE PROCEDURE 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)

2.2创建存储函数

CREATE FUNCTION sp_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)

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[,var_name...];

4，关闭游标

CLOSE cursor_name

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;

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;
//调用存储过程
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

//创建存储过程
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;

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,'2131244','12312455');
        END LOOP aaa;
    END
&&
DELIMITER；
//调用存储过程
CALL pro_user8;

5，REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:]REPEAT

Statement_list

UNTIL search_condition

END REPEAT[end_label]

DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
    BEGIN
        REPEAT
            SET total=totalNum-1
            INSERT INTO t_user VALUES(totalNum,'21323','123213');
            UNTIL totalNum=1;
        END REPEAT;
    ENG
&&
DELIMITER;


CALL pro_user9(11)

6，WHILE语句(经典)

[begin_label:]WHILE search_condition DO

Statement_list

END WHILE[end_label]

DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
    BEGIN
        WHILE totalNum>0 DO
            INSERT INTO t_user VALUES(totalNum,'122315','3142321');
            SET totalNum = totalNum-1;
        END WHILE;
    END
&&
DELIMITER；


CALL pro_user10(11);

三、调用存储过程和函数

3.1 调用存储过程

CALL sp_name([parameter[,,,]])

3.2 调用存储函数

fun_name([parameter[,...]])

四、查看存储过程和函数

4.1 SHOW STATUS 语句查看存储过程和函数的状态

SHOW {PROCEDURE | FUNCTION} STATUS[LIKE 'pattern'];

4.2 SHOW CREATE 语句查看存储过程的函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} sp_name

4.3 从information_schema.Routines表中查看存储过程和函数的信息

五、修改存储过程和函数

ALTER{PROCEDURE|FUNCTION}sp_name[characteristic...]

characteristic:

{CONTAINSSQL}NOSQL|READSSQLDATA|MODIFIESSQLDATA}

|SQLSECURITY{DEFINER |INVOKER }

|COMMENT‘string’

其中，sp_name 参数表示存储过程或函数的名称；characteristic 参数指定函数的特性。CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READS SQLDATA 表示子程序中包含数据的语句；MODIFIES SQL DATA 表示子程序中包含写数据的语句。SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行； INVODER 表示调用者可以执行。COMMENT‘string’ 是注释信息。

六、删除存储过程

DROP {PROCEDURE|FUNCTION} sp_name;

尘小新

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
数据库之——存储过程（学习笔记）

一、存储过程和函数的引入存储过程和函数时在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器段的数据传输；二、创建存储过程和函数2.1 创建存储过程 CREATE PROCEDU...
复制链接

扫一扫