精华内容
下载资源
问答
  • 概念:存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段重复调用具有特定功能的存储过程在被创建以后可以在程序中被多次调用,而且对存储过程进行修改对应用程序源代码毫无影响,既减少了开发...

    procedure对大家来说并不陌生,如果要说服未接触过的开发同事应用一些过程到项目中,应该如何去说服呢,我最近遇到这样的问题,自己做了一些过程的优势的总结,性能上做了小测试,大家有什么妙招?

    概念:存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段

    重复调用

    具有特定功能的存储过程在被创建以后可以在程序中被多次调用,而且对存储过程进行修改对应用程序源代码毫无影响,既减少了开发人员的工作量同时提高了程序的可移植性,对存储过程中的sql的语句的优化不会带来代码的更改。

    执行速度

    存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析优化并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL 语句在每次运行时都要进行编译和优化,应用程序中的sql语句数量很大,系统将消耗大量的资源在sql解析上面,而运用过程封装部分sql能有效缓解系统压力,并提高查询响应速度。(详见后续测试)

    网络流量

    一个事务需要用到10个sql,假设每个sql大小为1kb,单从流量上看,使用过程将节省9KB。同时还有交互过程中的网络流量,例如在一个事务性质的sql组合中,每条sql独立提交并获取结果数据做逻辑处理后传值(一个值,一个字符串,甚至一个表)给下一条sql。如果使用存储过程,sql之间的数据交互将在数据库内部进行,减少了大量的网络交互,同时减少的是整个事务的完成时间。

    4安全

    存储过程的运行时依据其创建者的权限。例如用户U不具备A表的任何权限,又需要为其提供部分的数据,可以赋予其调用过程P的权限,U通过存储过来访问数据,在过程中进行参数的审核,一定程度有效防止sql注入攻击,并可以控制其能够得到的数据内容。

    5.事务

    存储过程可结合事务执行。多sql在执行中mysql宕机或网络中断等原因,事务的回滚只能通过程序本身完成,由于程序大部分时间无法确定最后一次sql是否提交成功,回滚的可靠性低,将事务封装在过程中,数据库会有效地进行整个事务的提供和回滚操作。

    二 测试

    测试环境:

    mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline6.1  2G

    初始化:测试的3个表分别初始化50w条数据。

    2.1 执行性能

    业务场景:(完成select,update,delete,insert组合)

    给出一个id,更新t1中此id对应数据,删除t2此id数据。向t2插入t1此id数据,返回t3表的数据。

    Sqls:

    sql0='flush query cache'    刷新查询缓存,保证测试数据有效

    sql1='UPDATE t1 SET a_id=110 AND a_type=110 AND last_modified=NOW()WHERE id = %s'

    sql2='DELETE FROM t2 WHERE id=%s'

    sql3='INSERT INTO t2(guid,t_group,from_a_id,to_a_id) SELECT a_id,a_type,a_status,1FROM t1 WHERE id=%s'

    sql4="SELECT MIN(t3.t_type),MAX(sys_id),COUNT(DISTINCT t2.`guid`)FROM t3 JOIN t2 ON t3.id=t2.id"

    sql5='SELECT a.a_status,b.guid FROM t1 a,t2 b WHERE a.`a_id`=b.`guid`'

    数据及分析:

    Pro:存储过程    sql:多次sql       时间单位:s

    序号

    类别

    开始时间

    结束时间

    时间消耗

    随机id值

    1

    pro

    1330742912.74

    1330742918.3

    5.56233501434

    136369

    sql

    1330742929.56

    1330742938.79

    9.22886991501

    53571

    2

    pro

    1330743059.51

    1330743065.2

    5.69001603127

    115445

    sql

    1330743046.83

    1330743056.14

    9.30604100227

    143042

    3

    Pro

    1330744056.55

    1330744062.16

    5.61571311951

    68169

    Sql

    1330743738.2

    1330743747.48

    9.28886985779

    144629

    4

    Pro

    1330744123.48

    1330744129.07

    5.59134888649

    69447

    Sql

    1330744157.11

    1330744166.36

    9.24880003929

    14114

    5

    Pro

    1330744196.59

    1330744202.26

    5.67109799385

    55165

    sql

    1330744211.09

    1330744220.46

    9.36547088623

    80620

    平均值:sql 9.28761s   pro:5.626102s

    分析:

    存储过程中的sql在编译时mysql会保存优化后的执行计划,所以调用时不需要进行sql解析和优化。

    多次sql执行,需要对每一个sql语句进行解析和优化,如果sql语句量增加,系统将消耗大量资源在解析和优化上面,降低数据库整体性能。

    展开全文
  • dbutils 执行存储过程,dbutils 执行存储过程,dbutils 执行存储过程,dbutils 执行存储过程.   dbutils 好像是不支持直接使用带输出参数存储过程,如果谁直接调用的好方法多多指教。   如果是没有输出参数的...

    dbutils 执行存储过程,dbutils 执行存储过程,dbutils 执行存储过程,dbutils 执行存储过程.

     

    dbutils 好像是不支持直接使用带输出参数存储过程,如果谁有直接调用的好方法多多指教。

     

    如果是没有输出参数的存储过程,直接调用就行,和调用sql没什么区别。

     

    这次主要说怎么 变个法 调用存储过程;

     

    public Map callProcedure(String sql, String params[]) {
      Map map = new HashMap();
      CallableStatement cs = null;
      Connection conn = null;
      ResultSet rs = null;
      try {
       conn = dataSource.getConnection();
       cs = conn.prepareCall(sql);
       // 设置输入参数
       for (int i = 0; i < params.length; i++) {
        cs.setString(i + 1, params[i]);
       }
       System.out.println(params.length);
       // 设置输出参数
       cs.registerOutParameter(8, Types.INTEGER);
       cs.registerOutParameter(9, Types.INTEGER);
       cs.registerOutParameter(10, Types.INTEGER);
       cs.registerOutParameter(11, Types.INTEGER);
       cs.registerOutParameter(12, Types.INTEGER);
       cs.registerOutParameter(13, Types.INTEGER);
       rs = cs.executeQuery();
       while (rs.next()) {
        map.put("visit_bouce_count", rs.getString("visit_bouce_count"));
        map.put("page_count", rs.getString("page_count"));
        map.put("page_bounce_count", rs.getString("page_bounce_count"));
        map.put("play_bounce_count", rs.getString("play_bounce_count"));
        map.put("visit_prog_count", rs.getString("visit_prog_count"));
        map.put("visit_interval", rs.getString("visit_interval"));
       }
      } catch (SQLException e) {
       e.printStackTrace();
      } finally {
       try {
        if (rs != null) {
         rs.close();
        }
        if (cs != null) {
         cs.close();
        }
        if (conn != null) {
         conn.close();
        }
       } catch (Exception ex) {
        ex.printStackTrace();
       }
      }
      return map;
     }

     

    哈哈,用了jdbc 的方式完成。

     

    调用方法采用:

     

    callProcedure("{call pr_bouce_count(?,?,?,?,?,?,?,?,?,?,?,?,?)}", param)

    param 是一个数组里面是参数。 这样就完成了。

     

     

     

     

     

    展开全文
  • 今天我接收到一个任务就是创建一个数据脚本再原来8万条数据的基础上插入剩下的数据,...因此想到了数据脚本写存储过程。 这是我简单插入数据,并根据一定的数量进行时间累加的存储过程脚本: USE [eRAS]GO/*****...

    今天我接收到一个任务就是创建一个数据脚本再原来8万条数据的基础上插入剩下的数据,总共数据为2千万条。之前是创建一个文本文件,进行上传导入数据的,但这个导入数据的时是要有6个月的时间跨度,那么上传一天一个文本,那就要创建180个文本文件,那是得不偿失的。因此想到了数据脚本写存储过程。

    这是我简单插入数据,并根据一定的数量进行时间累加的存储过程脚本:

    USE [eRAS]
    GO

    /****** Object:  StoredProcedure [dbo].[AddSrcCCAUTH]    Script Date: 2019/04/26 14:41:17 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[AddSrcCCAUTH]
        -- Add the parameters for the stored procedure here
        
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        declare @i int,@Card varchar(20),@count int,@datenow DateTime,@oldCount int
        --还需添加的数
        select @i=80001;
        --获取当前的时间
        select @datenow=GETDATE();
        select @oldCount=0;
        while(@i<=20000000)
        begin
        --设置卡号
        select @Card='42019'+RIGHT('00000000000'+CAST(@i as varchar),11);
        --计算需要添加时间的天数
        select @count=(@i-80000)/110667;

        if(@count!=@oldCount)
        begin
        select @oldCount=@count;
        select @datenow=DATEADD(DAY,1,@datenow);
        end

        -- Insert statements for procedure here
        INSERT INTO dbo.SrcCCAUTH ([ID]
          ,[MEROUT_CARD_NBR]
          ,[MEROUT_ORIG_CURR_CODE]
          ,[MEROUT_ORIG_AMT]
          ,[MEROUT_LOCAL_MT]
          ,[MEROUT_MCC]
          ,[MEROUT_CHANNEL_IND]
          ,[MEROUT_TXN_AUTH_DATE]
          ,[MEROUT_TXN_AUTH_TIME]
          ,[MEROUT_TXN_COUNTRY]
          ,[MEROUT_REFERENCE_NBR]
          ,[MEROUT_UNIQUE_DATE]
          ,[MEROUT_UNIQUE_TIME]
          ,[MEROUT_UNIQUE_AUDT_TRACE]
          ,[MEROUT_UNIQUE_ACQ_ID]
          ,[MEROUT_UNIQUE_FWD_ID]
          ,[MEROUT_AVAILBLE_AMOUNT]
          ,[MEROUT_ACCOUNT_NBR]
          ,[MEROUT_PROCESS_CODE]
          ,[MEROUT_EAS_TC]
          ,[MEROUT_INPUT_SOURCE]
          ,[MEROUT_POS_ENTRY_MODE]
          ,[MEROUT_MERCH_NAME_LOC]
          ,[MEROUT_POS_COND_CODE]
          ,[MEROUT_MC_CARD_PRESENT]
          ,[MEROUT_AUTH_CODE]
          ,[MEROUT_LONG_DESC]
          ,[FILLER]
          ,[REPORT_DATE])
          VALUES (
          NEWID(),@Card,'840',10.00,61.68,'05999','5','2019-01-09 00:00:00.000',
          '084352','840','000000000009','20190109','084352','0000009','00000009685',
          '00000000000',54928.32,'0005591390000622638','000000','053','M','8120',
          'Ecom01                 St. Louis     MO','','5','794416','','',@datenow
          );
          select @i=@i+1;
          end
    END
    GO


    在这里面我要提醒一下这个几个地方,参数设置可以在这个截图的位置上添加。设置值与declare(申明)是一样的,@id int这样就申明了一个为int类型的@id值。然后设置则用select关键字,在sql语句中用set关键字,但存储过程中我使用set是没有用的。 SET NOCOUNT ON;这句话则是为了取消执行多条执行语句返回的结果集,否则每次都返回一句“执行一条数据成功”是很浪费时间的。while或是if语句都是没有大括号来包含内容,因此这些语句都一样用begin和end来包含。

    一个字符串想用一些字符来代替长度不够的部分,则可以使用RIGHT('00000000000'+CAST(@i as varchar),11);语句,这里我是以总长度为11个字符进行设置的,cast则将int值转化为varchar值。

    如果相对一个时间进行添加一天则可用这句话DATEADD(DAY,1,@datenow);第一个参数其实是指要添加的位置,day表示是要在时间的天数上加数值。最后一个参数则是目标值。

    好了,其他的模式就没有什么可以多说的,这个存储过程写得不复杂,大家一看就明了了。现在就来讲讲要执行这个存储过程需要执行哪句话,为什么创建或是更新执行的时候是不执行存储过程的?

    要想执行该存储过程我们可以执行exec AddSrcCCAUTH(存储数据表),执行这句话存储过程文件中的语句才被执行。如果停止不了,然后你不知道怎么停止则可以使用kill 56(这个是spid值)

    如果你找不到该值则可以查询

    SELECT t.[text],er.*
      FROM sys.dm_exec_requests AS er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS t
    WHERE er.session_id!=@@SPID

    执行该语句则可以查询到spid值。

    其实就是截图中那个53的值,你执行的语句在哪里执行就关闭哪个查询sql。

    好了最后说一下为什么创建了是不执行的,因为存储过程一般针对于一些比较复杂的,重复性较多的sql语句编写完成特定功能的SQL 语句集,如果该数据有些是比较重要的,需要客户有权限的人才能去执行,如果你一创建就执行了,那么这些数据又该由谁来负责呢?客户可能会认为有人操作而报警,而你则进入了监狱。所以在本地我们创建了会去执行查看是否正确,可将它创建到正式库里去的时候则不能执行的,也就在创建之时是不执行的,与创建视图执行是不一样的概念

    今天的日记随笔就写到这吧……

     

    转载于:https://www.cnblogs.com/Jack-S-Wang/p/10774636.html

    展开全文
  • 原文:存储过程到底有什么用? - 虎哥的回答 - 知乎 https://www.zhihu.com/question/21345483/answer/656171236 存储过程到底有什么用? 最近有个朋友在研究存储过程的事儿。看着他在那里纠结,突然想问下存储...

    原文:存储过程到底有什么用? - 虎哥的回答 - 知乎 https://www.zhihu.com/question/21345483/answer/656171236


    存储过程到底有什么用?

    最近有个朋友在研究存储过程的事儿。看着他在那里纠结,突然想问下存储过程到底有用么?

    bing了一下,在一些解释中:
    1、存储过程集合了sql语句并且缓存,大大提高语言执行效率
    2、增加安全性
    3、保证原子性

    对这些东西有些疑问:
    1、据说现在sql语句也会进行缓存,是否提高效率,提高多少效率还真不好说
    2、安全性为什么要写在数据库里面,不是应该在程序里面就保证好了么
    3、原子性的东西也可以在程序里面实现,为什么不?

    总之,感觉存储过程就是把数据库访问的逻辑与程序分离而放在数据库上,这到底是为了什么


    我觉得凡事不盲从、都问问为什么挺好的,要向你学习。

    感觉bing出来的三条都不是存储过程被使用的主要原因。主要原因应该是大量数据计算的时候,把数据从数据库中搬出来、计算好再存回去,时间太漫长了。搬出来、存回去的时间要远远大于计算的时间。

    但是,大量数据计算的情况,一般只会发生在数据批量处理的场景,也就是每天晚上算好了,存入结果表,第二天上班用来查询。

    回过头来看你bing的三条。事实上,少量数据的增删改查,用java实现挺好的,安全性和原子性可以通过MVC分层来保证。请中高级程序员负责后台,保证数据处理的代码质量,一般也不会出什么乱子。至于java提交SQL给数据库,需要解析的时间成本,这是数据库的强项,不会成为瓶颈。而且java也提供PreparedStatement的预编译缓存机制,所以没问题的。

    你最后说的,存储过程“把数据库访问的逻辑与程序分离而放在数据库上”这其实不是什么好事,1、数据库压力太大,会成为性能瓶颈,扩容太昂贵;2、移植到其他数据库会非常困难;3、开发人员有数据库高权限,数据安全受影响。



    作者:虎哥
    链接:https://www.zhihu.com/question/21345483/answer/656171236
    来源:知乎
    著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

    展开全文
  • 存储过程经过预编译处理 而SQL查询没有SQL语句需要先被数据库引擎处理...因为存储过程有个预先编译的过程.就是.启动服务器后或者第一次执行后(可以设置).就可以把存储过程加载到高速缓存中,这样以后调用起来就不用再...
  • 我正在使用一个名为ceODBC的模块处理Python3,并试图在SQL Server 2005中调用一个存储过程。当我在sqlservermanagementstudio...人能告诉我为什么存储过程会过早停止吗?这是我的Python代码:import ceODBCconn...
  • 若使用ROOT用户来创建一个存储过程 ,然后赋予另一个用户此存储过程执行权限,则此用户将可以进行一些ROOT账号才可以进行的操作。但是这些操作是预先被定义在存储过程里面的。 比如ROOT在test数据库创建了一个读取...
  • oracle函数和存储过程有什么区别 1. 返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有 2.调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用. 函数一般情况下是用来...
  • 计算机上调用Transaction-SQL 编写的一段程序原因在于存储过程具有以下优点   1 存储过程允许标准组件式编程  存储过程在被创建以后可以在程序中被多次调用而不必重新编写该存储过程的SQL语句而且数据库专业...
  • 存储过程跟触发器有点类似,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用 存储过程有哪些特性 有输入输出参数,可以声明变量,有if else case while 等控制语句,通过编写存储...
  • 存储过程一般用于处理比较复杂的任务,基础ms这个平台,可以大大降低耗时,其编译机制也提高了数据库执行速度存储过程不仅仅适用于大型项目,对于中小型项目,使用存储过程也是非常必要的。其威力和优势主要体现在...
  • 用户通过指定存储过程的名字并给出参数(如果该存储过程参数)来执 行它。存储过程是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个 单元处理。存储过程存储在数据库内,可由应用程序通过一...
  • 使用SQL存储过程有什么好处

    千次阅读 2010-03-27 21:15:00
    使用SQL存储过程有什么好处 ■SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时,服务器不必每次执行它时都要分析和编译它。 ■调用存储过程,可以认为是一个三层结构。这使你的程序易于...
  • 1、什么存储过程?...在我们需要使用的时候通过指定存储过程的名字并指定参数(如果该存储过程参数)来调用执行它。一个存储过程是一个可编程的函数,它在数据库中创建被保存,由SQL语句和一些特殊的控制...
  • 1,返回值的区别:函数一个返回值,而存储过程是通过参数返回的,可以多个或者没有。2,调用区别:函数可以在查询语句中直接调用,而存储过程必须单独调用。3,函数一般情况下是用来计算并返回一个计算结果,而...
  • 使用SQL存储过程有什么好处 ■SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时,服务器不必每次执行它时都要分析和编译它。 ■调用存储过程,可以认为是一个三层结构。这使你的程序易于...
  • 允许更快执行,如果某操作需要执行大量SQL语句或重复执行存储过程比SQL语句执行的要快。  3.减少网络流量,例如一个需要数百行的SQL代码的操作一条执行语句完成,不需要在网络中发送数百行代...
  • 定义:存储过程(Stored Procedure )是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程参数)来执行它。存储过程是数据库中的一个重要...
  • 存储过程和触发器的区别:1、存储过程可以采用输入参数,而触发器不可以;2、存储过程可以返回零或n值,而触发器无法返回值;3、存储过程中可以使用事务,而触发器不允许。【推荐课程:MySQL教程】存储过程和...
  • 存储过程执行权限

    2014-04-13 10:02:00
    最后更新时间: 2014年4月13日,星期日存储...为什么两种存储过程呢?比如说用户user02创建了修改表t1的存储过程,当用户user01调用时,是修改的user01自己的t1表还是user02的t1表? 示例:用户user02将存过赋执行...
  • 同样的语句,在存储过程里面执行,然后dbms打印出来之后再执行,速度差距在5倍以上,没有大佬能告诉一下,这种情况要怎么优化,目前还没有找到什么办法。之前在拼接语句后面拼上" AND 1= 1 "后速度正常了两天,...
  • 什么存储过程 系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头...

空空如也

空空如也

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

存储过程有什么执行