精华内容
下载资源
问答
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    MySQL 存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存在数据库的数据...

    MySQL 存储过程


    存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象

    存储过程就是具有名字的一段代码,用来完成一个特定的功能。
    创建的存储过程保存在数据库的数据字典中

    优点:
    存储过程可封装,并隐藏复杂的商业逻辑。
    存储过程可以回传值,并可以接受参数。
    存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
    存储过程可以用在数据检验,强制实行商业逻辑等。
    缺点:
    存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
    存储过程的性能调校与撰写,受限于各种数据库系统。

    存储过程的参数 :
    参数类型:
    IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
    OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
    INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

    MYSQL 存储过程中的关键语法:

    delimiter//                            声明语句结束符,可以自定义(默认分号)
    create procedure 名称(声明参数1,...)                声明存储过程
    begin...end                            存储过程开始和结束符号
    declare 变量名 变量类型 (unsigned default 默认值)        定义变量
    set 变量名=变量值                        变量赋值
    参数类型(in|out|inout) 参数名 数据类型(int、varchar等)    声明参数


    例子:
    delimiter //                -- 将SQL语句的结束标记设置为//
    drop procedure if exists testprint;    -- 如果存在该名称的存储过程则删除
    create procedure testprint()        -- 创建一个名字为testprint的存储过程
    begin                     -- 代码部分开始
    select '您好!' as infos;        -- 存储过程的代码部分
    end;                     -- 代码部分结束
    //                    -- 结束标记
        
    delimiter ;                -- 编写变异结束后将SQL语句的结束标记还原
    call testprint;                -- 调用存储过程

    in输入参数:
    多个参数用逗号分开,SQL语句最后需加分号
    delimiter //
    create procedure addteacher(in p_tname varchar(20),in p_tsex varchar(4))
    begin 
    insert into teacher (tname,tsex) values(p_tname,p_tsex);
    end;//
    --调用存储过程使用call
    --@pp_tname临时变量:@+变量名
    delimiter ;
    set @pp_tname='王五';
    set @pp_tsex='男';
    call addteacher(@pp_tname,@pp_tsex);

    OUT输出参数:
    delimiter //
    drop procedure if exists selecttname;
    create PROCEDURE selecttname(in p_sname varchar(20),out p_tname varchar(20))
    begin 
    --使用select ... into ...,将查询结果赋值给参数
    select tname into p_tname from teacher 
    inner join class on ctid=tid
    inner join student on scid=cid
    where sname=p_sname;
    end;
    //
    delimiter ;
    set @sname='一一';
    --调用带有out输出参数的存储过程,作为输出参数的变量需要被赋值
    set @tname='';
    call selecttname(@sname,@tname);
    select @tname;

    条件语句

    if-then-else 语句
    以if开始 + 条件 + then + 操作 (+ else + 操作)+ 以end if结尾

    delimiter //
    create procedure proc(in parameter int)  
    begin 
    declare var int;  
    set var=parameter+1;  
    if var=0 then 
    insert into t values(17);  
    end if;  
    if parameter=0 then 
    update t set s1=s1+1;  
    else 
    update t set s1=s1+2;  
    end if;  
    end;  
    //

    case语句
    case + 变量|参数 + when + 值1 + then + 操作 + when + 值2 + then + 操作... + else + 操作 + end case

    delimiter //
    create procedure proc(in parameter int)  
    begin 
    declare var int;  
    set var=parameter+1; 
    case var  
    when 0 then   
    insert into t values(17);  
    when 1 then   
    insert into t values(18);  
    else   
    insert into t values(19);  
    end case;  
    end;  
    //  

    循环语句
    一:
    while 条件 do  循环体 end while

    delimiter //
    create procedure proc()  
    begin 
    declare var int;  
    set var=0;  
    while var<6 do  
    insert into t values(var);  
    set var=var+1;  
    end while;  
    end;  
    //  

    二:
    repeat 循环体 until 循环条件  end repeat;

    delimiter //
    create procedure proc()  
    begin 
    declare v int;  
    set v=0;  
    repeat  
    insert into t values(v);  
    set v=v+1;  
    until v>=5  
    end repeat;  
    end;  
    //
      
    三:
    名称(自定义):loop 循环体 if 条件 leave 名称 end if end loop

    delimiter //
    create procedure proc()  
    begin 
    declare v int;  
    set v=0;  
    LOOP_LABLE:loop  
    insert into t values(v);  
    set v=v+1;  
    if v >=5 then 
    leave LOOP_LABLE;  
    end if;  
    end loop;  
    end;  
    //  

    JDBC调用存储过程

    不带输出参数:

    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获得连接
    String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
    String user = "root";
    String userPass = "123456";
    Connection con = DriverManager.getConnection(url, user, userPass);
    //创建存储过程的对象  
    java.sql.CallableStatement  cs = con.prepareCall("{call addstu(?,?)}");
    //给存储过程的参数设置值
    cs.setString(1, "张三");
    cs.setInt(2, "男");
    //执行存储过程 
    n = cs.executeUpdate();

    带输出参数:
    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获得连接
    String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
    String user = "root";
    String userPass = "123456";
    Connection con = DriverManager.getConnection(url, user, userPass);
    //创建存储过程的对象  ,querystu根据id查姓名
    java.sql.CallableStatement  cs = con.prepareCall("{call querystu(?,?)}");
    //给存储过程的参数设置值,第一个参数为id
    cs.setInt(1, 2);
    //第二个参数,为out name varchar(20)
    //注册存储过程的第二个参数registerOutParameter(),
    cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    //执行存储过程
    cs.execute();
    //得到存储过程的输出参数值,如果输出为int类型则使用getInt();
    String name = cs.getString(2);


    Mybatis调用存储过程

    有输出参数时:
    XML映射文件内书写语句需要加属性statementType="CALLABLE"
    注释书写SQL语句需要声明:@Options(statementType=StatementType.CALLABLE)
    输出参数写法:#{name,mode=OUT,jdbcType=VARCHAR} ,OUT、VARCHAR需要大写

    XML文件内书写
    不带输出参数:statementType="CALLABLE"可以省略    
    <insert id="addclass" parameterType="map" statementType="CALLABLE">
        call addstu(#{name},#{sex})
    </insert>

    带输出参数:
    注意!传入的参数类型直接为:parameterType="String"无法获得输出参数
    <select id="selecttname" statementType="CALLABLE" parameterType="map">
        call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})
    </select>
    带输出参数的调用:
    String str = "config/mybatis-config.xml";
    InputStream is = Resources.getResourceAsStream(str);
    SqlSessionFactory ss = new SqlSessionFactoryBuilder().build(is);
    SqlSession s = ss.openSession();
    Map<Object,String> map = new HashMap<>();
    map.put("id", 1);
    s.selectOne("selectname",map);
    System.out.println(map.get("name"));

    注解模式,带输出参数写法:
    @Select("call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType=StatementType.CALLABLE)
    public void selecttname1(Map<Object,String> map);
     

    展开全文
  • Mysql 存储过程简介--实现存储过程

    万次阅读 多人点赞 2019-01-23 13:35:44
    Mysql中的数据用各种不同的技术存储在文件(或者内存)中, 每一种技术都使用不同的存储机制, 索引技巧 锁定水平并且最 终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外 的速度或者功能从而...

    什么是存储引擎:

    Mysql中的数据用各种不同的技术存储在文件(或者内存)中, 每一种技术都使用不同的存储机制,
    索引技巧 锁定水平并且最 终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外 的速度或者功能从而改 善你的应用整体功能。不同的存储引擎性能是不一的你能够获得额外的速度或者功能,从而改善你的应用的整体功能。查看Mysql可用的引擎可以用:show engines查询出来后边有 yes 的 是支持的 no 是不支持的。

    2.Mysql 的常见引擎 :

    1.MEMORY:

    memory使用 存在内存中的内容来创建表,每个MEMORY表实际对应一个磁盘文件,格式是.frm(就是表里的结构)。memory类型的表访问速度非常快,因为它的数据放在内存中 并且默认使用 HASH索引,但是一旦服务器关闭 表中的数据就会丢失, 但是表还会继续存在。​

    2.MylSAM:

    拥有较高的插入(如 insert into 新增),查询速度,但不支持事物,也不支持外键。 每个MylSAM在磁盘上存放3个文件,其中文件名和表名相同,但是扩展名 分别为 .frm 存储表的一个定义 . MYD:存储真是的数据 .MYI:存储索引 MyISAM适合: (1)做很多count 的计算; (2)插入不频繁,查询非常频繁; (3)没有事务。

    3.InnoDB:

    5.5版本后Mysql的默认数据库,事物型数据库的首选引擎,支持ACID事物,支持行级锁定 innoDB写的处理效率差,他会占用空间来保存数据和索引。 InnoDB适合: (1)可靠性要求比较高,或者要求事务; (2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定数据引擎的创建 让所有的灵活性成为可能的开关是提供给ANSI SQL的MySQL扩展——TYPE参数。 MySQL能够让你在表格这一层指定数据库引擎,所以它们有时候也指的是table formats。 下面的示例代码表明了如何创建分别使用MyISAM、ISAM和HEAP引擎的表格。 下面的代码显示了如何使用ALTER TABLE把MyISAM表格移动到InnoDB的引擎: 代码:ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB 3. Memory:所有数据置于内存的存储引擎,拥有的极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 Mysql 重新启动时丢失。 4.Merge:将一定数量的 MyISAM 表联合而成一个整体,在超大规模数据存储时很有用。 5.Archive:非常适合存储大量的独立的,作为历史纪录的数据。因为它们不经常被读取。 Archive 拥有高效的插入速度,但其对查询的支持相对较差。 6.CSV:逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .CSV文件这是一个普通的文本文件,每个数据行占用一个文本行。CSV存储引擎不支持索引。7.BlackHole:黑洞引擎,写入的任何数据都会消失,一般用于记录 binlog 做复制的中继。

    ACID:

    A 事务的原子性(Atomicity):指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行 了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱 却没出来.这两步必须同时完成.要么就不完成. C 事务的一致性(Consistency):指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了 a+b=10,一个事务改变了a, 那么b也应该随之改变. I 独立性(Isolation):事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态. 因为这样可能会导致数据不一致. D 持久性(Durability):事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久 的保存在数据库之中, 不会无缘无故的回滚.

    Innodb介绍:

    innoDB是一种可靠性高的高性能 Mysql 数据库存储引擎,它有如下优点:​
    1.它遵循ACID模式设计,具有与事物(Transactions),回滚和保护用户数据的崩溃恢复能力。 2.提供行锁。 3.InnoDB表基于主键在磁盘上安排数据,有优化的常见查询功能。每一个InnoDB表都有一个叫做 聚集索引的主键索引,能尽可能减少数据查询次数。 4.为了维护数据完整性,InnoDB还支持外键完整性约束。 5.可以与其它MySQL存储引擎混合使用InnoDB表。列如,能用一个结合操作在一个单独查询中综合源自 InnoDB和InnoDB表的数据 6.InnoDB是针对提高 CPU效率而设计的,并且在处理大数据时表现最佳。MyISAM和InnoDB的区别;

    什么是存储过程:

    一组可编程的函数,是为完成 特定 功能的SQL语句 存储 过程是具有名字的代码段 ,来完成一些特定的功能。 创建的存储过程保存在数据库的字典中。

    为什么用存储过程:

    将重复性高的一些操作,封装到一个存储过程中,简化对Sql的调用。 批量处理 统一接口, 确保数据的安全 相对与oracle数据来说,Mysql的存储过程功能较弱 使用较少

    存储过程的创建 和调用:

    delimiter $ $: 他与存储过程无关 delimiter 语句将标准分割符–分号(;)更改 $ $ , 因为我们想将存储过程 作为一个整体传递给服务器,而不是让mysql 工具一次解析每个语句,告诉mysql解析器 ,该段命令是否已经结束了,mysql是否可以执行了,默认情况下delimiter是分号;。在命令行客户端中,如果有一行以分号结束,那么回车 后执行 改命令,但是有的时候,不希望mysql这么做。在为可能输入较多的语句,且 语句包含有分号,使用delimiterr$ $ 这样只有$ $出现之后mysql解析器解析执行这段语句

    案例:

    创建存储过程:

    delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
     CREATE PROCEDURE user_tt()  -- 给存储过程取一个名字
    BEGIN -- 开始
    SELECT * from t_user; --sql语句执行 注意sql 语句结束有分号
    END $$  --结束  注意 符号 和定义开始一致
    

    在这里插入图片描述
    调用存储过程:

    delimiter;-- 把分号改回来
    
    CALL user_tt;
    

    在这里插入图片描述

    查看存储过程:
    查看所有的存储过程:SHOW PROCEDURE STATUS;
    在这里插入图片描述

    ##查看指定数据库的存储过程:SHOW PROCEDURE STATUS WHERE db=“存储过程名”;

    在这里插入图片描述
    查看 指定存储过程源代码: SHOW CREATE PROCEDURE + 存储过程名

    在这里插入图片描述
    删除存储过程 :drop PROCEDURE +存储过程名;

    在这里插入图片描述
    存储过程变量:

    delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
     CREATE PROCEDURE text_user()  -- 给存储过程取一个名字
    BEGIN -- 开始
    
      -- 声明变量  关键 字declare   `DEFAULT ''  默认值 空字符串
    
    
    
    DECLARE  jie  VARCHAR(255) default ''; 
    
    DECLARE   x,y INT   default 0;
    
    
    SELECT * from t_user; -- sql语句执行 注意sql 语句结束有分号
    
    
    END $$  -- 结束  注意 符号 和定义开始一致
    
    

    在这里插入图片描述
    给变量赋值:
    在这里插入图片描述
    变量的范围:如果在存储过程中声明变量,那么当达到存储过程的 END 语句时 它超出了范围。因此在其他代码块无法 访问

    存储过程参数:

    三种类型:

                                   in  输入类型
                                    
                                    --  根据传入的名称, 获取对应的信息、
    delimiter $$
    CREATE PROCEDURE getName(in name VARCHAR(225))-- 传入参数  in 关键字  VARCHAR类型
    BEGIN 
    
     SELECT * from t_user where  user_code =name; -- 给要查的 赋值
    
    
    END $$
    
    delimiter;
    

    在这里插入图片描述

            out类型的 输出
    
    
    
    delimiter $$
    CREATE PROCEDURE getMima(in name VARCHAR(225),out mima int )-- 传入参数 out  关键字  输出数据
    BEGIN 
    
     SELECT  user_pass into mima  from t_user where  user_code = name; -- 给要查的 user_pass into mima  赋值  name 
    
    
    END $$
    
    
    
    查询结果
    CALL  getMima('鲁班',@m);  -- @m 就时out 的参数  首先执行这句话
    SELECT @m; -- 这里的@m 就是上面所定义的 在这可以直接输出 这只是简写
    
    SELECT @m  from  DUAL 这个是mysql 数据库 提供的一张假表  可以加上参数调用
    
    

    在这里插入图片描述

     inout 类型是又输入又是输出
      delimiter $$
    CREATE PROCEDURE test1808(inout num  int ,in abc  int  )-- 传入参数 out  关键字  输出数据
    BEGIN 
    
    
    set num = num + abc;
     
    END $$
    
    delimiter;
    
    查询结果
    set @num1=220;
    CALL test1808(@num1,10);
    SELECT @num1;
    

    在这里插入图片描述

    存储过程语句:

    if语句: IF expression THEN
    statements;
    END IF;
    IF expression THEN
    statements;
    ELSE
    else-statements;
    END IF;
    CASE语句:
    CASE case_expression
    WHEN when_expression_2 THEN commands …
    ELSE commands
    END CASE;
    循环语句:
    WHILE expression DO
    statements
    END WHILE
    2)REPEAT
    stalemeents;
    UNTIL expression
    END REPEAT

    自定义函数

    案例:

    
      定义 一个自定义函数  在定义函数 和存储过程有区别  定义函数function
    delimiter $$
    CREATE  FUNCTION rand_str(n int) returns  varchar(225)
    BEGIN
    -- 生明一个str 52字母
    DECLARE str VARCHAR(100)  DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    -- 记录 当前是第几个
    DECLARE i INT DEFAULT 0;
    -- 生成的结果
    DECLARE  res_str  VARCHAR(255)   DEFAULT '';
    -- 循环  
    WHILE i < n DO
    -- 随机生成一个指定的字符串 b bc bce 
    --   1+ RAND()*52  floor(1+rand ()*52);
    --  SUBSTR(str FROM (1+rand()*52),1)
    set res_str=CONCAT(res_str,SUBSTR(str,FLOOR(1+RAND()*52),1));
    SET i= i+1;
    END WHILE;
    RETURN res_str;
    END $$
    delimiter ;
    
    
    结果
    SELECT rand_str(5);
    

    在这里插入图片描述

    案例批量新增:

    这里调用了以上的随机数 方法 函数
    
    delimiter $$
    CREATE  PROCEDURE insert_emp10(in  startNum int, in  max_num int) 
                            --  startNum 从几开始
                            --   max_num 要插入多少条数据
    begin 
    -- 声明 一个变量叫记录当前是第几条数据
     DECLARE  i INT default 0;
    -- 默认情况是自动 提交数据
      set autocommit = 0; -- 不让它自动 提交
    repeat 
    set i= i+1; -- 每循环一次 让i+1
    -- 插入数据
    INSERT into  emp VALUES (startNum+i,rand_str(5),FLOOR(10+RAND()*30));
                                 --  startNum代表是id ,+i是每次循环+1
                                 -- rand_str(5) 调用 数据 数 对name 每一次循环
                                   --   都去生成一个5个字母的名字
                                   --  FLOOR(10+RAND()*30) 年龄 也是随机的 +10
                                   --  是应为随机数是从0开始 *30 是到30岁
    until i =max_num  -- 当i=max_num 就让它退出循环
    
    end  repeat;
    commit;  -- 整体提交所有数据  提高效率 
    end $$
    
    delimiter ;
    

    结果:
    在这里插入图片描述

    展开全文
  • Mysql存储过程

    千次阅读 2018-12-02 09:05:20
    Mysql存储过程 一. 过程化sql  过程块sql主要有两种类型,几命名块和匿名块。匿名块每次执行时都要编译,而且不能存储到存储过程中,也不能在其他过程化sql块中调用,过程和函数时命名块,他们在编译时保存在...

    Mysql存储过程

    一. 过程化sql

           过程块sql主要有两种类型,几命名块和匿名块。匿名块每次执行时都要编译,而且不能存储到存储过程中,也不能在其他过程化sql块中调用,过程和函数时命名块,他们在编译时保存在数据库中,成为持久型存储模块(Persistent Stored Module,PSM),可以被反复调用,运行速度较快。

    二. 基本语法

    1. 创建存储过程

    create procedure sp_name()
    begin
    .........
    end

    2. 调用存储过程

    • 基本语法:call sp_name()

           注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递

    3. 删除存储过程

    • 基本语法:

           drop procedure sp_name//

    • 注意事项

           不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

    4. 其他常用命令

    • show procedure status

            显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等

    • show create procedure sp_name

            显示某一个MySQL存储过程的详细信息

    三. 在存储过程中使用用户变量

    1. 在mysql客户端使用用户变量

        mysql> SELECT 'Hello World' into @x;   给x赋值
        mysql> SELECT x;                       查询x   
    
     
        mysql> SET @y='Goodbye Cruel World';      
        mysql> select @y;
    
    
        mysql> SET @z=1+2+3;
        mysql> select @z; 

    2. 在存储过程中使用用户变量

    mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
    mysql> SET @greeting='Hello';
    mysql> CALL GreetWorld( );

    3. 在存储过程间传递全局范围的用户变量

    mysql> CREATE PROCEDURE p1( )   SET @last_procedure='p1';
    mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
    mysql> CALL p1();
    mysql> CALL p2();

    4. 含参的存储过程

    DELIMITER //
    CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
    BEGIN
    SELECT p_in; --查询输入参数
    SET p_in=2; --修改
    select p_in;--查看修改后的值
    END//

      执行结果:
    mysql> set @p_in=1
    mysql> call sp_demo_in_parameter(@p_in)
    以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值       

    对于DELINITER 的用法,请看我的下篇博客 存储过程分号处理办法

     

    展开全文
  • MySQL存储过程

    千次阅读 2016-06-26 19:42:40
    MySQL存储过程存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。1、MySQL执行流程,通过存储过程,可以避免重复语法分和编译,提高执行效率2、存储过程: (1)是SQL语句与控制语句的...

    MySQL存储过程

    存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。

    1、MySQL执行流程,通过存储过程,可以避免重复语法分和编译,提高执行效率

    2、存储过程:

    • (1)是SQL语句与控制语句的【预编译集合】,以【一个名称存储】作为【一个单元处理】

    • (2)优点:
      · 增强了语句的功能和灵活性:可以通过控制语句对流程进行控制和判断
      · 实现较快的执行速度,只在【第一次调用时进行语法分析和编译】 ,以后直接从内存中得到结果
      · 减少网络流量

    这里写图片描述
    自定义存储过程

    1、存储过程语法结构分析

    CREATE
    [DEFINER = {user|CURRENT_USER}]
    PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数
    [characteristic ...] routine_body

    其中参数

    proc_parameter:
    [IN | OUT | INOUT] param_name type
    • IN, 表示该参数的值必须在调用存储过程时指定
    • OUT, 表示该参数的值可以被存储过程改变,并且可以返回
    • INOUT, 表示该参数的值调用时指定,并且可以被改变和返回

    2.特性

    COMMENT 'string'
    | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA|SQL SECURITY{DEFINER|INVOKER}
    • COMMENT:注释
    • CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
    • NO SQL:不包含SQL语句
    • READS SQL DATA:包含读数据的语句
    • MODIFIES SQL DATA:包含写数据的语句
    • SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行

    3.过程体

    • (1)过程体由合法的SQL语句构成;
    • (2)过程体可以是任意SQL语句;
    • (3)过程体如果为复合结构则使用BEGIN…END语句
    • (4)复合结构可以使用条件、循环等控制语句

    创建不带参数的存储过程:

    语法格式:CREATE PROCEDURE 存储过程名 ([参数],[参数]...)

    例如:CREATE PROCEDURE sq1() SELECT VERSION();

    调用存储过程

    CALL 存储过程名[(参数)],...

    例如:CALL sp1();无参可以省略小括号,有参就不能省略

    1.创建没有参数的存储过程

        CREATE PROCEDURE sp1() SELECT VERSION();

    2.调用存储过程

        CALL sp_name([parameter[,...]]) - 带参数的存储过程的调用
        CALL sp_name[()] - 不带参数的存储过程调用
        CALL sp1;
        CALL sp1();

    2.创建带有IN类型参数的存储过程

        创建实例代码(参数名不能和表中的字段名相同)
        1、修改定界符:DELIMITER new_limiter,一般用'//'代替';'作为定界符
        2、通过传入id删除字段
        CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
        BEGIN
        DELETE FROM users WHERE id = p_id(传入的p_id); //【变量名称不能与字段相同,系统无法识别
        END
        //
        DELIMITER ;
    
        3、调用:CALL removeUserById(3); //删除id=3的记录
    
    
    
    
        修改存储过程
        ALTER PROCEDURE sp_name [characteristic]
        COMMENT 'string'
        |{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
        |SQL SECURITY{DEFINER|INVOKER}
        3.删除存储过程
        DROP PROCEDURE [IF EXISTS] sp_name;

    3. 创建带有IN OUT类型参数的存储过程,根据id删除用户,并返回剩余用户数;

        DELIMITER //
        CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT numUsers INT UNSIGNED)
        BEGIN
        DELETE FROM user WHERE id = showID;
        SELECT count(ID) FROM user INTO numUsers;
        END
        //
        SELECT count(ID) FROM user INTO numUsers; /** 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 numUsers 变量中 */
        CALL removerUserAndReturnUserName(10,@nums); /** @nums 所代表的就是用户变量,可用 SELECT @nums 输出 */

    用 DECLARE 声明的变量是局部变量,局部变量只能存在于 BEGIN…END 之间,且声明时必须置于 BEGIN…END 的第一行

    而通过 SELECT…INTO…/SET @id = 07 这种方法设置的变量我们称之为用户变量,只能存在于当前用户所使用的客户端有效。

        CALL rmUserAndRtUserNums(27, @nums);
        SELECT @nums; //@nums - 就是用户变量
        DECLARE声明的变量都是在BEGINEND之间,是局部变量
        SET @i = 7; //通过@或SET设置的变量称为用户变量

    4.创建一带多个OUT类型参数的存储过程

    1、例子:IN通过年龄删除数据行,OUT返回被删除的行数和剩余行数

    2、介绍一个系统函数ROW_COUNT(),无参,返回上一次CRUD操作影响的行数

    DELIMITER //
    CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
    BEGIN
    DELETE FROM users WHERE WHERE age = p_age; //注意变量不同
    SELECT ROW_COUNT() INTO delNums;
    SELECT COUNT(id) FROM users INTO leftNums;
    END
    //
    DELIMITER ;
    SELECT * FROM users;
    SELECT COUNT(id) FROM users WHERE age = 20;
    CALL rmUserByAgeAndRtInfos(20, @a, @b);
    SELECT * FROM users;
    SELECT COUNT(id) FROM users WHERE age = 23;
    CALL rmUserByAgeAndRtInfos(23, @a, @b);
    SELECT @a, @b;

    5.存储过程与自定义函数的区别

    • A、存储过程实现的功能相对复杂,函数针对性较强
    • B、存储过程可以返回多个值,函数只能有一个返回值
    • C、存储过程一般独立执行,函数可以作为 sql 语句的组成部分来出现
      存储过程和自定义函数的区别

    (1)
    存储过程实现的功能较复杂,经常针对表做操作;
    函数针对性比较强,很少用于对表做操作;

    (2)
    存储过程可以有多个返回值;
    而函数只能有一个返回值;

    (3)
    存储过程一般独立执行;
    函数可以作为其他SQL语句的组成部分出现。

    6.总结:
    存储过程:是SQl语句与控制语句的预编译集合,以一个名称存储并作为一个单元处理
    参数:输入类型,输出类型,输入&&输出
    创建:creat.....procedure....
    存储过程(call procedure proc_name[(123,@name,@num...)])
    自定义函数(select func_name(a,b...)
    注意:

    • A、需要修改分隔符(delimiter //)
    • B、复合结构包含在 begin … end 语句块中
    • C、复合结构中的 sql 语句以 分号 结束
    展开全文
  • Mysql 存储过程

    万次阅读 2020-08-25 19:40:04
    -- 调用存储过程proc_5 CALL proc_6() -- 删除存储过程 DROP PROCEDURE proc_5 -- 查看存储过程 SHOW PROCEDURE STATUS -- 查看proc_2构建语句 SHOW CREATE PROCEDURE proc_2 二、JDBC记录 // 获取所有存储过程及其...
  • MYSQL存储过程

    千次阅读 2018-06-23 18:46:09
    存储过程是存储在数据库目录中的一段声明性SQL语句,优点有: 通常存储过程有助于提高应用程序的性能 存储过程有助于减少应用程序和数据库服务器之间的流量 存储的程序对任何应用程序都是可重用的和透明的 存储的...
  • mysql 存储过程

    千次阅读 2016-04-05 17:09:22
    查询存储过程 show procedure status; select name from mysql.proc where db = ‘your_db_name’ and type = ...创建存储过程mysql> DELIMITER // mysql> CREATE PROCEDURE proc1(OUT s int) -> BEGIN ->
  • MySQL存储过程详解 mysql 存储过程

    千次阅读 2016-09-20 15:13:23
    MySQL存储过程详解mysql存储过程 mysql存储过程详解 1.存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(StoredProcedure)是一组为了完成特定功能...
  • MySql 存储过程

    千次阅读 2017-05-26 09:35:09
    存储过程跟普通Sql相比的优点 1、比普通Sql功能更加强大,使用更加灵活 2、执行速度快,除了第一次执行跟Sql处理时间相同外,以后再次调用少了语法分析和编译的时间消耗 3、减少网络带宽,普通Sql有可能比较长,...
  • mysql存储过程学习笔记

    万次阅读 多人点赞 2019-02-22 17:09:36
    本文主要记录了本人学习mysql存储过程时的笔记,文档中用到的表结构在文档结尾处有说明。 一、定义  存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,...
  • mysql存储过程

    千次阅读 2012-10-08 19:59:52
    存储过程如同一门程序设计语言,同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure sp_name() begin .........

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 46,692
精华内容 18,676
关键字:

mysql存储过程

mysql 订阅