精华内容
下载资源
问答
  • 一.对待存储过程和函数的态度在实际项目中应该尽量少用存储过程和函数,理由如下:1.移植性差,在MySQL中的存储过程...所以在互联网时代大型项目应该尽量少使用(不使用)存储过程和函数。二.创建存储过程2.1什么是存...

    一.对待存储过程和函数的态度

    在实际项目中应该尽量少用存储过程和函数,理由如下:

    1.移植性差,在MySQL中的存储过程移植到sqlsever上就不一定可以用了。

    2.调试麻烦,在db中报一个错误和在应用层报一个错误不是一个概念,那将是毁灭性打击,直接一个error:1045什么的更本毫无头绪。

    3.扩展性不高。

    所以在互联网时代大型项目应该尽量少使用(不使用)存储过程和函数。

    二.创建存储过程

    2.1什么是存储过程?

    存储过程和存储函数都是一组sql语句的集合。这些语句集合被当做一个整体存入数据库中。

    2.2创建存储过程的语法:

    create procedure 存储过程名(参数列表)

    sql语句

    例子:

    delimiter //

    create procedure pro()

    reads sql data

    begin

    select * from stu;

    end

    //

    那么我们现在就有一个存储过程pro了,但是这个存储过程他是没有参数的,他只是执行一次查询操作。

    我们现在来讲解一下这个存储过程的结构:

    delimiter //  是将分号转化为//   因为在sql执行时当他遇到分号 ; 时他就讲停止所以我们必须将其转化为 //直到最后一行才会停止执行。

    reads sql  data   解释characteristic的状态在这里是只读模式,其他的模式还有:no sql 没有sql语句 , ins  sql 不包含读和写的语句 , modifies sql data   包含写入数据的语句等等。

    begin /***/  end   在存储过程中当有多条语句集合时我们必须使用begin和end

    //   结束整个存储过程

    2.3使用存储过程

    在只是创建了一个存储过程,那么我们怎么来使用这个存储过程呢?

    语法:call  存储过程名()

    将上一个存储过程pro使用的例子:

    call pro();

    2.4创建一个带参数的存储过程

    参数列表:存储过程的参数有三种类型:in,out,inout 分别表示传入参数和传出参数,和即传入也传出参数。

    例子:首先我们来创建两张表:课程表是学生表的从表

    create table stu(

    stu_id bigint primary key auto_increment,#学号

    stu_name varchar(10) not null,#姓名

    stu_major int not null,#专业号

    stu_sex char,#性别

    stu_in date,#入学日期

    stu_birth date,#出生日期

    foreign key (stu_major) references major(ma_id)#专业外键设置

    );

    create table major(

    ma_id int primary key,

    ma_name varchar(15),

    ma_boss varchar(10)

    );

    insert into major values(1,"信管","张三");

    insert into major values(2,"电子商务","李四");

    insert into stu values(1,"小明",1,"男","2017-09-01","1998-12-23");

    insert into stu values(2,"小高",1,"男","2017-09-01","1998-05-01");

    insert into stu values(3,"小李",2,"男","2017-09-01","1999-04-01");

    我们再来创建一个带有参数的存储过程找到学生的主修课的名字,代码如下:

    delimiter //

    create procedure pro1(in sname varchar(10),out ma varchar(10))

    reads sql data

    begin

    select ma_name into ma from major where ma_id = (select stu_major from stu where stu_name=sname);

    end

    //

    使用这个存储过程:代码如下:

    set @ma="没查询之前";

    call pro1("小李",@ma);

    select @ma;

    结果如下:

    145409172_1_20180927115209472.png

    解释一下代码:首先使用set @ma 定义一个全局变量,然后在使用call 存储过程名语法调用存储过程,同时全局变量ma的值也改变了。

    三.创建一个存储函数

    3.1存储过程和存储函数的不同。

    1.在函数中必须要有return返回值

    2.在存储过程中参数有in  out   inout三种,默认为in类型,但是在函数中只有一种in类型。

    3.2创建一个函数

    语法:create  function 函数名()

    return 返回类型

    sql语句集合

    例子:

    delimiter //

    create function fun1(num int)

    returns int

    begin

    return num 1000;

    end

    //

    显然函数与存储过程的最大的区别就是在于return

    3.3调用函数

    使用语法不在使用关键字call,而是关键字select ,select  函数名

    例子:

    select fun1(100);

    结果:

    145409172_2_20180927115209550.png

    四.删除存储函数和存储过程

    语法:drop   procedure  |   function  存储过程名或者是函数名

    例子:

    drop procedure pro;

    注意他是不带括号的

    五.在存储过程和存储函数中使用游标

    5.1为什么需要游标?

    当我们在使用存储过程的时候可能用到多条数据,那么我们就需要用到游标来存放多条数据。

    5.2使用游标的注意点

    游标不能单独存在,必须在存储过程或者是存储函数中使用。

    5.3使用游标

    语法:

    1.创建游标:declare  游标名  cursor  for  select语句

    2.打开游标:open  游标名

    3.使用游标:fetch  游标名  into  变量名

    4.关闭光标:close 游标名

    5.4例子

    delimiter //

    create function fun3(id int)

    returns int

    reads sql data

    begin

    declare cur cursor for select stu_id from stu;

    open cur;

    fetch cur into id;

    close cur;

    return id;

    end

    //

    使用

    set @id=0;

    select fun3(@id);

    结果

    145409172_3_20180927115209597.png

    可以发现游标只是将第一个值给了变量。

    来源:http://www.icode9.com/content-2-25811.html

    展开全文
  • 要想使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。存储过程通过 CALL 语句来调用,存储函数使用方法与 MysqL 内部函数使用方法相同。执行存储过程和存储函数需要拥有 EXECUTE 权限...

    存储过程和存储函数都是存储在服务器端的 sql 语句集合。要想使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。

    存储过程通过 CALL 语句来调用,存储函数的使用方法与 MysqL 内部函数的使用方法相同。执行存储过程和存储函数需要拥有 EXECUTE 权限(EXECUTE 权限的信息存储在 information_schema 数据库下的 USER_PRIVILEGES 表中)。

    本节主要讲解如何调用存储过程和存储函数。

    调用存储过程

    MysqL 中使用 CALL语句来调用存储过程。调用存储过程后,数据库系统将执行存储过程中的 sql 语句,然后将结果返回给输出值。

    CALL 语句接收存储过程的名字以及需要传递给它的任意参数,基本语法形式如下:

    CALL sp_name([parameter[...]]);

    其中,sp_name 表示存储过程的名称,parameter 表示存储过程的参数。

    例 1

    下面调用《MySQL创建存储过程》一节中创建的存储过程,sql 语句和执行过程如下:

    MysqL> DELIMITER ;

    MysqL> CALL ShowStuscore();

    +--------------+---------------+

    | student_name | student_score |

    +--------------+---------------+

    | Dany | 90 |

    | Green | 99 |

    | Henry | 95 |

    | Jane | 98 |

    | Jim | 88 |

    | John | 94 |

    | Lily | 100 |

    | Susan | 96 |

    | Thomas | 93 |

    | Tom | 89 |

    +--------------+---------------+

    10 rows in set (0.00 sec)

    Query OK,0 rows affected (0.02 sec)

    MysqL> CALL GetscoreByStu('Green');

    +---------------+

    | student_score |

    +---------------+

    | 99 |

    +---------------+

    1 row in set (0.03 sec)

    Query OK,0 rows affected (0.03 sec)

    因为存储过程实际上也是一种函数,所以存储过程名后需要有( )符号,即使不传递参数也需要。

    MysqL 中,存储函数的使用方法与 MysqL 内部函数的使用方法是一样的。换言之,用户自己定义的存储函数与 MysqL 内部函数是一个性质的。区别在于,存储函数是用户自己定义的,而内部函数是 MysqL 开发者定义的。

    例 2

    下面调用《MySQL创建存储函数》一节中创建的存储函数,sql 语句和执行过程如下:

    MysqL> SELECT func_student(3);

    +-----------------+

    | func_student(3) |

    +-----------------+

    | 王五 |

    +-----------------+

    1 row in set (0.10 sec)

    通过例 1 和例 2 的比较,可以看出虽然存储函数和存储过程的定义稍有不同,但它们都可以实现相同的功能,我们应该在实际应用中灵活选择。

    总结

    以上是编程之家为你收集整理的MySQL调用存储过程和函数全部内容,希望文章能够帮你解决MySQL调用存储过程和函数所遇到的程序开发问题。

    如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。

    本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。

    如您喜欢交流学习经验,点击链接加入交流1群:1065694478(已满)交流2群:163560250

    展开全文
  • 介绍 本文是与从Hibernate调用各种关系数据库系统存储过程和数据库函数相关的...Oracle支持存储过程和函数,因此首先我们将从以下存储过程开始,该过程输出一个简单的值。 Oracle存储过程输出一个简单的值 CRE...

    介绍
    本文是与从Hibernate调用各种关系数据库系统存储过程和数据库函数相关的一系列帖子的一部分。写下来的原因是因为有许多与底层JDBC驱动程序支持相关的特性,并不是每个关系数据库都支持每个JPA或Hibernate特性。
    Oracle存储过程
    Oracle支持存储过程和函数,因此首先我们将从以下存储过程开始,该过程输出一个简单的值。
    Oracle存储过程输出一个简单的值

    CREATE OR REPLACE PROCEDURE count_comments (  
       postId IN NUMBER,  
       commentCount OUT NUMBER )  
    AS  
    BEGIN  
        SELECT COUNT(*) INTO commentCount  
        FROM post_comment  
        WHERE post_id = postId; 
    END;

    该存储过程具有两个参数:输入参数(例如postId)和输出参数(例如commentCount),其用于返回post_comment与给定post_id父行相关联的记录的计数。
    要调用此存储过程,可以使用以下Java Persistence API 2.1语法:

    StoredProcedureQuery query = entityManager
        .createStoredProcedureQuery("count_comments")
        .registerStoredProcedureParameter(1, Long.class, 
            ParameterMode.IN)
        .registerStoredProcedureParameter(2, Long.class, 
            ParameterMode.OUT)
        .setParameter(1, 1L);
    
    query.execute();
    
    Long commentCount = (Long) query.getOutputParameterValue(2);

    Oracle存储过程输出SYS_REFCURSOR
    存储过程还可以定义SYS_REFCURSOR与数据库游标关联的输出参数,该数据库游标可以迭代以获取多个数据库记录:

    CREATE OR REPLACE PROCEDURE post_comments ( 
       postId IN NUMBER, 
       postComments OUT SYS_REFCURSOR ) 
    AS  
    BEGIN 
        OPEN postComments FOR 
        SELECT *
        FROM post_comment 
        WHERE post_id = postId; 
    END;

    在Oracle上,可以使用JPA 2.1语法调用此存储过程:

    StoredProcedureQuery query = entityManager
        .createStoredProcedureQuery("post_comments")
        .registerStoredProcedureParameter(1, Long.class, 
             ParameterMode.IN)
        .registerStoredProcedureParameter(2, Class.class, 
             ParameterMode.REF_CURSOR)
        .setParameter(1, 1L);
    
    query.execute();
    
    List<Object[]> postComments = query.getResultList();

    与之前的存储过程调用不同,这次,我们正在使用getResultList()并获取Object[]包含与所选数据库记录关联的所有列值的内容。

    Hibernate长期以来一直提供自己的存储过程API,并且可以按如下方式调用上述存储过程:

    Session session = entityManager.unwrap(Session.class);
    ProcedureCall call = session
        .createStoredProcedureCall("post_comments");
    
    call.registerParameter(1, Long.class, 
        ParameterMode.IN).bindValue(1L);
    
    call.registerParameter(2, Class.class, 
        ParameterMode.REF_CURSOR);
    
    Output output = call.getOutputs().getCurrent();
    if (output.isResultSet()) {
        List<Object[]> postComments = 
            ((ResultSetOutput) output).getResultList();
    }

    Oracle功能
    Oracle还支持数据库函数,与存储过程不同,它不使用输入和输出参数,而是使用一个或多个函数参数和单个返回值。
    Oracle函数返回一个简单的值
    第一个存储过程可以转换为如下所示的函数:

    CREATE OR REPLACE FUNCTION fn_count_comments ( 
        postId IN NUMBER ) 
        RETURN NUMBER 
    IS
        commentCount NUMBER; 
    BEGIN
        SELECT COUNT(*) INTO commentCount 
        FROM post_comment 
        WHERE post_id = postId; 
        RETURN( commentCount ); 
    END;

    不幸的是,在编写(Hibernate 5.1.0)时,Java Persistence 2.1存储过程和特定于Hibernate的API都不能用于调用函数。
    但是,这种限制有几种解决方法。

    首先,我们可以像调用任何其他SQL查询一样简单地调用Oracle函数:
    BigDecimal commentCount = (BigDecimal) entityManager
    .createNativeQuery(
    “SELECT fn_count_comments(:postId) FROM DUAL”
    )
    .setParameter(“postId”, 1L)
    .getSingleResult();
    另一种方法是使用普通JDBC API调用数据库函数:

    Session session = entityManager.unwrap( Session.class );
    
    Integer commentCount = session.doReturningWork( 
        connection -> {
        try (CallableStatement function = connection
            .prepareCall(
                "{ ? = call fn_count_comments(?) }" )) {
            function.registerOutParameter( 1, Types.INTEGER );
            function.setInt( 2, 1 );
            function.execute();
            return function.getInt( 1 );
        }
    } );

    Oracle函数返回一个SYS_REFCURSOR
    就像存储过程一样,Oracle函数也可以返回一个SYS_REFCURSOR,并且,为了使示例更加有趣,我们将获取一个Post与其相关的PostComment子实体。

    Oracle函数如下所示:

    CREATE OR REPLACE FUNCTION fn_post_and_comments ( 
        postId IN NUMBER ) 
        RETURN SYS_REFCURSOR 
    IS
        postAndComments SYS_REFCURSOR; 
    BEGIN
       OPEN postAndComments FOR
            SELECT
                p.id AS "p.id", 
                p.title AS "p.title", 
                p.version AS "p.version", 
                c.id AS "c.id", 
                c.post_id AS "c.post_id", 
                c.version AS "c.version", 
                c.review AS "c.review"
           FROM post p 
           JOIN post_comment c ON p.id = c.post_id 
           WHERE p.id = postId; 
       RETURN postAndComments; 
    END;

    要获取实体,我们需要指示Hibernate底层ResultSet和每个实体属性之间的映射。这可以使用特定于Hibernate的NamedNativeQuery注释来完成,因为与JPA NamedNativeQuery不同,它还支持调用存储过程和数据库函数。

    该NamedNativeQuery映射是这样的:

    @NamedNativeQuery(
        name = "fn_post_and_comments",
        query = "{ ? = call fn_post_and_comments( ? ) }",
        callable = true,
        resultSetMapping = "post_and_comments"
    )
    @SqlResultSetMapping(
        name = "post_and_comments",
        entities = {
            @EntityResult(
                entityClass = Post.class,
                fields = {
                    @FieldResult( 
                        name = "id", 
                        column = "p.id"
                    ),
                    @FieldResult( 
                        name = "title", 
                        column = "p.title"
                    ),
                    @FieldResult( 
                        name = "version", 
                        column = "p.version"
                    ),
                }
            ),
            @EntityResult(
                entityClass = PostComment.class,
                fields = {
                    @FieldResult( 
                        name = "id", 
                        column = "c.id"
                    ),
                    @FieldResult( 
                        name = "post", 
                        column = "c.post_id"
                    ),
                    @FieldResult( 
                        name = "version", 
                        column = "c.version"
                    ),
                    @FieldResult( 
                        name = "review", 
                        column = "c.review"
                    ),
                }
            )
        }
    )

    如您所见,SqlResultSetMapping如果您希望返回实体而不是Object[]数组,则需要提供。

    有了这两个映射,获取Post和PostComment实体看起来像这样:

    List<Object[]> postAndComments = entityManager
        .createNamedQuery("fn_post_and_comments")
        .setParameter(1, 1L)
        .getResultList();
    
    Object[] postAndComment = postAndComments.get(0);
    Post post = (Post) postAndComment[0];
    PostComment comment = (PostComment) postAndComment[1];
    展开全文
  • 存储过程和函数简单的说,存储过程就是一条或者多条SQL语句的集合。可以视为批文件,但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数,以及如何调用、查看、修改、删除存储过程和存储函数等...

    存储过程和函数

    • 简单的说,存储过程就是一条或者多条SQL语句的集合。可以视为批文件,但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数,以及如何调用、查看、修改、删除存储过程和存储函数等。

    创建存储过程和函数

    存储程序分为存储过程和存储函数。Mysql创建存储过程和存储函数的语句分别为CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程,只能用输出变量返回值。存储函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。废话少说,如下步入正文。

    • 创建存储过程

    创建存储过程的基本语法格式为:CREATE PROCEDURE sp_name([proc_parameter]) [characteristics] routine_body

    其中CREATE PROCEDURE为创建存储过程的关键字,sp_name为存储过程的名称,pro_parameter为指定存储过程的参数列表,其中参数列表如下:

    1. [IN|OUT|INOUT] param_name type 其中,IN表述输入参数,OUT表示输出参数,INOUT表示即可输入也可输出;param_name表述参数名称;type表示参数类型,该类型可以是MySQL数据库中的任意类型。
    2. characteristics指定存储过程的特性,有以下取值:
    • LANGUAGE SQL:说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值。
    • [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的,每次执行存储过程时,相同的输入会得到相同的输出,NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出,如果没有指定任意一个值,默认为NOT DETERMINISTIC。
    • [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但不包含读写数据语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA说明子程序包含读数据的语句;MODIFIES SQL DATA表名子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。
    • SQL SECURITY[DEFINER|INVOKER]:指明谁有权限来执行。DEFINER表示只有定义着才能执行。INVOKER表示用友权限的调用者可以执行。默认情况下,系统指定为DEFINER。
    • COMMENT 'string':注释信息,用来描述存储过程或函数。
    1. routine_body是SQL代码的内容,可以用BEGIN...END来表示SQL代码的开始和结束。

    我们从最简单的存储过程开始说起,如下是不包含任何参数的存储过程,代码为:CREATE PROCEDURE Proc() BEGIN SELECT * FROM tb_score; END; 我们定义了一个名称为Proc的存储过程,该过程是用来查询tb_score(该表接上篇博客,已存在)数据库表中的所有数据。

    03ab8c8dfb3e1f618b19d767edef25b8.png
    b5ca3d430b31543bc62e25eb4b4b028a.png
    8273e5081d57c58a5ac5fdf5f0484966.png

    第一张图是通过执行sql语句查询到tb_score表中的数据。第二张图是创建存储过程,其中第一句DELIMITER //是将MySQL的结束符设置为//,因为MySQL默认的结束符为分号,为了避免与存储过程中SQL语句结束符相冲突,需要DELIMITER改变存储过程的结束符,并以“END //”结束存储过程。第三张图是调用存储过程,在调用存储过程之前先将MySQL结束符恢复为默认的分号(DELIMITER ;)然后通过CALL Proc()调用。

    接下来我们讲解MySQL存储过程中的参数IN、OUT、INOUT,IN作为输入,将输入作为参数传输到存储过程的执行当中去;OUT作为输出,将存储过程的输出通过参数传出来,而INOUT参数可以同时作为输入和输出。

    还是通过存储过程查询tb_score表,不过这次我们要查询课程号为1(cID=1)的所有学生的成绩,存储过程定义为:CREATE PROCEDURE Proc_cID(IN classID INT) BEGIN SELECT * FROM tb_score WHERE cID=classID; END;

    536b208661317289a5f641a717e9816c.png
    05bdcb832eb23c237a358896a8451965.png

    如我们需要查询课程号为1的学生的人数和平均成绩,则存储过程定义如下:CREATE PROCEDURE Proc_AVG(IN classID INT,OUT total INT,OUT a_s FLOAT) BEGIN SELECT COUNT(*),AVG(grade) INTO total,a_s FROM tb_score WHERE cID=classID; END;

    f9f66ae87b89c7147677a5404b2c520b.png
    • 创建存储函数

    创建存储函数需要使用CREATE FUNCTION语句,基本语法格式为:CREATE FUNCTION func_name([func_parameter]) returns type [characteristic] routine_body CREATE FUNCTION为用来创建存储函数的关键字,func_name表示存储函数的名称,func_parameter为存储过程的参数列表如下:

    • [IN|OUT|INOUT] param_name type 其参数含义同存储过程(PROCEDURE)相同,其默认为IN参数。

    RETURNS type语句表示函数返回数据的类型,characteristic指定存储函数的特性,取值与创建存储过程时相同。

    查询某个学生某门课程的成绩函数代码为:CREATE FUNCTION Query_score(classID INT,studentID INT) RETURNS INT RETURN (SELECT grade FROM tb_score WHERE cID=classID AND sID=studentID); 通过SELECT Query_score(1,1)查询1号学生1号课程的成绩。

    d27850a5432c17a212b92a6f694051ac.png
    3150003247465bee0a911c6861e660ff.png

    读者可能会发现存储过程的查询结果可能为多个值,而存储函数的查询结果是某一类型的单值。而且存储过程在调用时用CALL而存储函数是SELECT。那么存储过程和函数具体的区别又是什么呢?

    1. 存储过程的功能更加复杂,而函数的功能针对性更强;
    2. 存储过程可以返回参数(通过OUT|INOUT),而函数只能返回单一值或者表对象;
    3. 存储过程作为一个独立的部分来执行,而函数可以作为查询语句的一部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字之后;
    4. 存储过程是通过关键字CALL来调用,作为一个独立的执行部分。而存储函数则可作为SELECT语句的一部分调用,嵌入到SQL语句中;
    5. 当存储过程和函数被执行的时候,SQLManager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQLManager就会对存储过程和函数进行编译。
    • 变量的使用

    变量可以在子程序中声明并使用,作用范围是在BEGIN...END程序中,如下将主要介绍如何定义变量和为变量赋值。

    1. 定义变量。语法格式为:DECLARE var_name[,var_name]...data_type[DEFAULT value]; var_name为局部变量名称,DEFAULT value给变量提供一个默认值。值除了可以被声明为一个常数之外,还可以被指定为一个表达式。如果缺少DEFAULT子句,初始值为NULL。
    2. 为变量赋值。MySQL中使用SET语句为变量赋值,语法格式为:SET var_name=expr[,var_name=expr]...
    • 流程控制的使用

    流程控制与用来根据条件控制语句的执行。MySQL中的用来构造控制流程的语句有:IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。各语句介绍如下:

    1. IF语句。包含多个条件判断,根据判断的结果为TRUE或FALSE执行相应的语句,语法格式为 IF expr_condition THEN statement_list ESLEIF expr_condition THEN statement_list ESLE statement_list END IF 如下我们定义一个学生成绩等级评定函数,将学生成绩以参数的形式传输函数,输出学生成绩等级A(90~100)、B(75~90)、C(60~75)、D(60以下)。代码编写中需要注意,IF中如果有多个限制条件,限制条件间用AND连接,DECLARE的变量声明必须在BEGIN内,以及字符串之间拼接用CONCAT。该功能代码如下图所示:
    da110399afe262f7f565377d402b6f93.png
    1. CASE语句。另外一个进行条件判断的语句,该语句有2种语句格式。
    2. 第一种格式为:CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] [ELSE statement_list] END CASE 其中case_expr参数表示条件判断的表达式,决定哪个WHEN子句会被执行,value表示表达式可能的值,如果case_expr等于某个value,则执行相应value后的statement_list。
    3. 第二种格式为:CASE WHEN expr_condition THEN statement_list [WHEN expr_condition THEN statement_list] [ELSE statement_list] END CASE 其中expr_condition参数表示条件判断语句,该格式下,WHEN语句将被逐个执行,直到某个expr_condition表达式为真,则这行对应THEN关键字后面的statement_list语句。如果没有匹配,ELSE子句里的语句被执行。
    4. LOOP语句。循环语句用来重复执行某些语句,与IF和CASE相比,LOOP只是创建一个循环操作的过程,并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出。跳出循环过程使用LEAVE子句,LOOP语句基本格式为:[label] LOOP statement_list END LOOP[label] label表示LOOP语句的标注名称,该参数可以省略,statement_list表示需要执行的语句。
    fbfc9be9e43bc3e32fd527eb01e01139.png
    1. LEAVE语句。从LOOP语句的例子中可知LEAVE语句用来退出任何被标注的流程控制构造,LEAVE语句基本格式为:LEAVE label
    2. ITERATE语句。将执行顺序转到语句段开头处,语句基本格式为:ITERATE lable ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为再次循环,label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。例子中p1=0,如果p1的值小于10时,重复执行p1加1操作;p1大于等于10并且小于20时,打印消息'p1 is between 10 and 20';p1大于20时,退出循环。
    32f9ac2a64b0914b3b39274a39400beb.png
    1. REPEAT语句。创建一个带条件判断的循环过程,每次语句执行完毕后,会对条件表达式进行判断,表达式为真循环结束,否则重复执行循环中的语句。语句基本格式:[label]:REPEAT statement_list UNTIL expr_condition END REPEAT [label]
    2. WHILE语句。WHILE语句创建一个带条件判断的循环过程,与REPEAT不同,WHILE在执行语句时先对指定的表达式进行判断,为真则执行循环内的语句,否则退出循环。语句基本格式:[label] WHILE expr_condition DO statement_list END WHILE [label]
    32c49406adfe03ad0c0ef72c119ea78f.png

    查看存储过程和函数

    MySQL中,用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看存储过程和函数,也可以直接从系统的information_schema数据库中查询。本节将通过实例来介绍这3种方法。

    • SHOW STATUS语句查看存储过程和函数的状态,其基本语法为:SHOW{PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'] 这个语句返回子程序的特征,如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式,根据使用的语句,所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数,LIKE语句表示匹配存储过程或函数的名称。
    87457ebed3dc2003f5393ec3bf7f94e0.png
    • SHOW CREATE查看存储过程和函数语句格式为:SHOW CREATE {PROCEDURE|FUNCTION} sp_name 它返回一个可以来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数,同样也可是使用LIKE匹配。
    e53b4e02d09b8c55584daa9b2c4efa6d.png
    • 从information_schema.Routines表中查看存储过程和函数的信息。MySQL中存储过程和函数的信息存储在information_schema数据库的Routines表中。通过查询该表的记录查询存储过程和函数的信息。基本语法格式为:SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name' 其中ROUTINE_NAME字段存储的是存储过程和函数的名称,sp_name参数表述存储过程或函数的名称。
    aac5c70157d2bfd5f8147a5ce64e1a12.png

    修改存储过程和函数

    使用ALTER语句可以修改存储过程或函数的特性,本节将介绍如何通过ALTER语句修改存储过程和函数。语法格式为:ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...] 其中,sp_name参数表示存储过程或函数的名称,characteristic参数指定存储函数的特性,可能的取值有:

    1. CONTAINS SQL表示子程序包含SQL语句,但是不包含读或写数据的语句;
    2. NO SQL表示子程序中不包含SQL语句;
    3. READES SQL DATA表示子程序中包含读数据的语句;
    4. MODIFIES SQL DATA表示子程序中包含写数据的语句;
    5. SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行;
    6. DEFINER表示只有定义着自己才能够执行;
    7. INVOKER表示调用者可以执行;
    8. COMMENT 'string'表示注释信息。

    修改存储过程使用ALTER PROCEDURE语句,修改存储函数使用ALTER FUNCTION语句。

    45dfdf02a8a26a8264b2c669df79ad47.png
    34b45f8e57a04c9b65347c13cdc90364.png

    删除存储过程

    删除存储过程和函数可以使用DROP语句,语法格式为:DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name 这个语句被用来移除一个存储过程或函数,sp_name为要移除的存储过程或函数的名称。IF EXISTS子句是一个MySQL的扩展,如果存储过程或函数不存在,以防发生错误,产生一个用SHOW WARNINGS查看的警告。

    44817edf5a2f62c83e9627fde2fa1e1f.png

    最后再说几点值得大家注意的吧:

    1. 目前MySQL不支持对已存在的存储过程代码进行修改,如果必须修改,则先使用DROP语句删除该存储过程,再重新创建新的存储过程;
    2. 存储过程中包含用户定义的SQL语句集合,也可是使用CALL语句调用存储过程,但不能使用DROP删除其他存储过程;
    3. 在定义存储过程参数列表时,应注意把参数名与数据库表中的字段名区别开,否则会报错。
    4. 如果存储过程中需要传入中文参数,这时需要在定义存储过程的时候,在参数后加上character set gbk,不然调用存储过程使用中文参数会出错。如CREATE PROCEDURE userInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)。
    展开全文
  • 1.3 如何使用 1.3.1 创建 语法 create procedure 存储过程名( 参数模式 参数名 参数类型 ) begin 存储过程体(即一组sql语句) end 调用 call 存储过程名(参数); 说明 参数模式有三种: 1. in :表示参数需传入...
  • 存储过程和函数

    2016-08-26 21:49:00
    本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表: create database school; use school; create...
  • [20190118]toad下如何调试存储过程和函数.txt--//工作需要,需要使用toad调试存储过程和函数,自己以前从来没有调试存储过程和函数的经验,少数的几次还是使用PLSQL develop,--//使用PLSQL develop很简单,右键选择...
  • [20190118]toad下如何调试存储过程和函数.txt --//工作需要,需要使用toad调试存储过程和函数,自己以前从来没有调试存储过程和函数的经验,少数的几次还是使用PLS...
  • 存储过程和函数存储过程和函数简单的说,存储过程就是一条或者多条SQL语句的集合。可以视为批文件,但是其作用不仅仅局限于批处理。本文主要介绍如何创建存储过程和存储函数,以及如何调用、查看、修改、删除存储...
  • 存储过程和存储函数都是存储在服务器端的 SQL 语句集合。要想使用这些已经定义好的存储过程和存储函数就必须要通过...本节主要讲解如何调用存储过程和存储函数。 调用存储过程 MySQL 中使用 CALL 语句来调用存储过程
  • 在mysql中,存储过程和存储函数都是数据库中定义的一些SQL语句的集合。其中,存储函数可以通过return语句返回函数值,主要用于计算并返回一个值;而存储过程没有直接返回值,主要用于执行操作。mysql中的存储过程...
  • 在mysql中,存储过程和存储函数都是数据库中定义的一些SQL语句的集合。其中,存储函数可以通过return语句返回函数值,主要用于计算并返回一个值;而存储过程没有直接返回值,主要用于执行操作。mysql中的存储过程...
  • 本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表:create database school;use school;create table ...
  • 本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表:create database school;use school;create table ...
  • 索引,触发器,存储过程和存储函数及游标的基本使用和了解索引口述并理解1. 什么是索引2. 索引的作用有哪些3. 索引的分类有哪些4. 创建索引的原则有哪些动手做1. 如何创建索引2. 如何删除索引触发器1. 什么是触发器2...
  • 本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表: create database school; use school; c..
  • 本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表:create database school;use school;create table ...
  • 本文主要讲解如何创建存储过程和存储函数以及变量的使用如何调用、查看、修改、删除存储过程和存储函数等。使用的数据库和表还是之前写JDBC用的数据库和表: create database school; use school; ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 975
精华内容 390
关键字:

存储过程和函数如何使用