SQL之存储过程及函数

作者: 安静的学点东西 | 来源:发表于2017-09-07 12:14 被阅读102次

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

创建存储过程

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_name type
in 表示输入参数；out表示输出参数；inout表示既可以输入也可以输出;param_name是参数名称；type是参数类型，该类型可以是数据库的任意数据类型。
characteristic参数有多个取值，其取值说明如下：
1、language sql：说明routine_body部分由SQL语句组成，这也是数据库系统的默认语言；
2、[not] deterministic：指明存储过程的执行结果是否是确定的，deterministic表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是非确定的，相同的输入可能得到不同的输出，这也是默认的情况。
3、{contains sql | no sql | reads sql data | modifies sql data}:指明子程序使用SQL语句的限制；
    contains sql：表示子程序包含SQL语句，但不包含读或写数据的语句，系统默认的值；
    no sql：表示子程序中不包含sql语句；
    reads sql data：表示子程序中包含读数据的语句；
    modifies sql data：表示子程序中包含写数据的语句。

4、sql security {definer|invoker}：指明谁有权限来执行；
    definer表示只有定义这自己才能执行；
    invoker表示调用者可以执行。默认情况下，系统指定的权限是definer。
5、comment 'string':注释信息；

举个栗子

DELIMITER &&
CREATE PROCEDURE pro_book (IN bt INT, OUT num INT)//bt是int类型的入参，num是int类型的出参
    READS SQL DATA     //包含读取sql语句
    BEGIN
       SELECT COUNT(*) FROM t_book WHERE bookTypeId=bt;
    END
    &&
    DELIMITER;
    
CALL pro_book(1, @total);

创建存储函数

CREATE FUNCTION sp_name([func_parameter[,...]])
    RETURN TYPE
    [characteristic...] routine_body
sp_name：函数的名称；
func_parameter:函数的参数列表；
return type：返回值的类型；
charactistic:指定存储函数的特性，该参数的取值与存储过程的取值是一样的；
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 bookId=id);
    END
    && 
    DELIMITER;
    
SELECT func_book(1) AS bookName;

变量

在这里先定义两张表格t_user和t_user1，在t_user1中插入一条数据;

//创建t_user1;
CREATE TABLE t_user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userName VARCHAR(20),
    PASSWORD VARCHAR(20)
);
//创建t_user1，并插入一条数据
CREATE TABLE t_user1(
    id INT PRIMARY KEY AUTO_INCREMENT,
    userName1 VARCHAR(20),
    password1 VARCHAR(20)
);

INSERT INTO t_user1 VALUES (NULL, "userName1", "password1");

定义变量

DECLARE var_name[,...] type[DEFAULT value]

变量赋值

SET var_name=expr[,var_name=expr]...

select col_name[,...]into var_name[,...] from table_name where condition;

举个栗子

//定义变量并赋值，插入到t_user表中
DELIMITER &&
CREATE PROCEDURE pro_user()
    BEGIN
        DECLARE a,b VARCHAR(20);
        SET a='c language', b='tan hao qiang';//通过set方式赋值
        INSERT INTO t_user VALUES(NULL, a,b);
    END 
&&
DELIMITER;
CALL pro_user();

CREATE PROCEDURE insert_data_from_user1()
    BEGIN 
    DECLARE a,b VARCHAR(20);
    SELECT userName1, password1 INTO a,b FROM t_user1 WHERE id=1;//通过select方式赋值
    INSERT INTO t_user VALUES (NULL, a,b);
    END
&&
DELIMITER;

CALL insert_data_from_user1();

DELIMITER &&
CREATE PROCEDURE test(IN p1 VARCHAR(20), IN p2 VARCHAR(20))//用参数赋值
        BEGIN
        DECLARE a,b VARCHAR(20);
        SET a = p1, b=p2;
        INSERT INTO t_user VALUES(NULL,a,b);
        END
        &&
        DELIMITER;

CALL test("test account", "1234355");

游标

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

声明游标

DELCARE cursor_name CURSOR FOR select_statement;

打开游标

OPEN cursor_name;

使用游标

FETCH cursor_name INTO var_name[,var_name...];

关闭游标

CLOSE cursor_name;

举个栗子

DELIMITER &&
CREATE PROCEDURE insert_user2()
    BEGIN 
    DECLARE a,b VARCHAR(20);
    DECLARE cursor_temp CURSOR FOR SELECT userName1,password1 FROM t_user1;
    OPEN cursor_temp;
    FETCH cursor_temp INTO a,b;
    INSERT INTO t_user VALUES (NULL, a,b);
    CLOSE cursor_temp;
    END
&&
DELIMITER;

CALL insert_user2();

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

IF语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF
//举个例子
DELIMITER %%
CREATE PROCEDURE testIf(IN id INT)
    BEGIN 
    SELECT COUNT(*) INTO @num FROM t_user1 WHERE id=t_user1.`id`;
    IF @num >0 THEN UPDATE t_user1 SET t_user1.`userName1`="update userName" WHERE t_user1.`id`=id;
    ELSE INSERT INTO t_user1 VALUES (id, "insert id", "12qwrwer");
    END IF;
    END
    %%
DELIMITER;

CALL testIf(1);
CALL testIf(10);

CASE语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
//有点像java中的switch语句
//举个例子
DELIMITER &&
CREATE PROCEDURE testCase(IN id INT)
    BEGIN
    SELECT COUNT(*) INTO @num FROM t_user1 WHERE id=t_user1.`id`;
    CASE @num
        WHEN 1 THEN UPDATE t_user1 SET userName1="update data" WHERE id=t_user1.`id`;
        WHEN 3 THEN UPDATE t_user1 SET userName1="update DATA DATA" WHERE id=t_user1.`id`;
        ELSE INSERT INTO t_user1 VALUES(NULL, "testCase", "123456lekjasd");
    END CASE;
    END
&&
DELIMITER;
CALL testCase(1);
CALL testCase(4);

LOOP、LEAVE语句

LOOP语句可以使用某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。LOOP语句的语法基本形式如下：
[begin_lable: ]LOOP
    statement_list
END LOOP [end_label]

DELIMITER ##
CREATE PROCEDURE testLoop(IN totalNum INT)
    BEGIN
    aaa:LOOP
        SET totalNum=totalNum-1;
        IF totalNum=0 THEN LEAVE aaa;
        ELSE INSERT INTO t_user1 VALUES(totalNum, "adsfasdf", "aadsfasdfadf");
        END IF;
    END LOOP aaa;
    END
##
DELIMITER;

DELETE FROM t_user1;//先清空

CALL testLoop(20);

REPEAT语句

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

[begin_label:]REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [end_label]

DELIMITER ##
CREATE PROCEDURE testRepeat(IN totalNum INT)
    BEGIN 
    aaa: REPEAT
    SET totalNum = totalNum-1;
    INSERT INTO t_user1 VALUES(totalNum, "asdfasdf","asdfasdf");
    UNTIL totalNum=3
    END REPEAT aaa;
    END
##
DELIMITER;

DELETE FROM t_user1;//先清空

CALL testRepeat(49);

WHILE语句

WHILE search_condition DO
    statement_list
    END WHILE;

DELIMITER @@
CREATE PROCEDURE testWhile(IN totalNum INT)
    BEGIN 
    WHILE totalNum >0 DO
    INSERT INTO t_user1 VALUES(totalNum, "4523452345","sdfgsdfg");
    SET totalNum=totalNum-1;
    END WHILE;
    END
@@
DELIMITER;

DELETE FROM t_user1;//先清空

CALL testRepeat(149);

调用存储过程和函数

call sp_name([parameter[,...]])//调用存储过程

func_name([parameter[,...]])//调用方法

查看存储过程和函数

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

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} sp_name([proc_parameter[,...]])
                characteristic:
                {CONTAINS SQL| NO SQL| READS SQL DATA |MODIFIES SQL DATA}|
                SQL_SECURITY{DEFINER|INVOKER}|
                COMMENT 'STRING'}
举个栗子：
ALTER PROCEDURE testWhile COMMENT 'this is test for while';

删除存储过程和函数

DROP {PROCEDURE|FUNCTION} sp_name;

今天就结束了对存储过程和函数的简单介绍，我也是离开大学后再次学习SQL，都是些基础内容，希望能够在以后的开发过程中更加熟练。

数据库的内容可能就介绍到这里了，这几篇文章主要介绍了SQL的基本使用，包括更删改查、触发器、存储过程和函数等。

网友评论

本文标题：SQL之存储过程及函数

本文链接：https://www.haomeiwen.com/subject/ujcijxtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

SQL之存储过程及函数

创建存储过程

创建存储函数

变量

定义变量

变量赋值

游标

声明游标

打开游标

使用游标

关闭游标

流程控制

IF语句

CASE语句

LOOP、LEAVE语句

REPEAT语句

WHILE语句

调用存储过程和函数

查看存储过程和函数

修改存储过程和函数

删除存储过程和函数

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读