精华内容
下载资源
问答
  • 问题描述: 1、存储过程逻辑无问题,已在其他库验证无误;...没有重复执行的情况。 配置如下: wait_timeout=86400 interactive_timeout=7200</p>
  • C#获取SQL server数据库存储过程脚本,可重复执行.用于给数据库升级的打升级包的时候,十分方便
  • 概念:存储在数据库当中的可以执行特定工作(查询和更新)的一组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语句量增加,系统将消耗大量资源在解析和优化上面,降低数据库整体性能。

    展开全文
  • 很多公司都要求再生产上打得sql脚本允许反复执行(防止某一个sql报错以后要拎出来...所以需要存储过程出马,这么简单的存储过程很多人都会写,但是这里我踩了一个坑。。。 没错,就是表名不能当参数。。。 第一次...

    很多公司都要求再生产上打得sql脚本允许反复执行(防止某一个sql报错以后要拎出来执行)。
    所以就产生了需要先判断索引是否存在,再做添加索引或者删除索引的操作(若索引不存在,删除索引会报错)
    这时候单独sql已经无法完成这个要求了(添加和删除索引的语句不支持IF EXISTS)
    所以需要存储过程出马,这么简单的存储过程很多人都会写,但是这里我踩了一个坑。。。
    没错,就是表名不能当参数。。。
    第一次写的存储过程,不幸的挂了。

    DROP PROCEDURE IF EXISTS add_index;  
    DELIMITER //
    CREATE PROCEDURE add_index(IN p_db_name VARCHAR (64),IN p_table_name VARCHAR (64),IN p_index_name VARCHAR (64),IN p_index VARCHAR (64)) BEGIN 
    IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = p_db_name AND table_name = p_table_name AND index_name = p_index_name) THEN  
       ALTER TABLE p_table_name ADD INDEX p_index_name ( p_index );
    END IF;  
    END//  
    DELIMITER ;  
    

    这里 ALTER TABLE p_table_name ADD INDEX p_index_name ( p_index ); 这句话是错误的,因为存储过程里表名无法直接作为参数使用。
    还好,还可以使用MySQL提供的PREPARE语法
    关于PREPARE的具体使用方法,可以参考网址:https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html
    于是修改存储过程为

    DROP PROCEDURE IF EXISTS add_index;  
    DELIMITER //
    CREATE PROCEDURE add_index(IN p_db_name VARCHAR(64),IN p_table_name VARCHAR(64),IN p_index_name VARCHAR(64),IN p_index VARCHAR(64)) BEGIN 
    DECLARE StrAdd VARCHAR(250);
    DECLARE StrDrop VARCHAR(250);
    set @StrAdd=concat('ALTER TABLE ',p_table_name,' ADD INDEX ',p_index_name,'(',p_index,')');
    set @StrDrop=concat(' drop index ',p_index_name,' on ',p_table_name);   
    IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = p_db_name AND table_name = p_table_name AND index_name = p_index_name) THEN  
        PREPARE stmt FROM @StrAdd;  
        EXECUTE stmt ;  
    ELSE
        PREPARE stmt FROM @StrDrop;  
        EXECUTE stmt ; 
        PREPARE stmt FROM @StrAdd;  
        EXECUTE stmt ; 
    END IF;   
    END//  
    DELIMITER ;  
    

    参数(p_db_name 库名,p_table_name 表名,p_index_name 索引名,p_index 索引字段)
    set @StrDrop=concat(’ drop index ‘,p_index_name,’ on ',p_table_name);
    这句是拼接一个sql语句,然后 PREPARE stmt FROM @StrDrop; 执行该sql ,最后通过DEALLOCATE释放该Prepared Statements
    该过程可以实现先判断有无该索引,有则删除然后新增,没有则直接新增。

    展开全文
  • 今天客户提了一个需求,需要处理下用户我的产品重复数据...对应的写了一个存储过程: 这里用到了游标和sql拼接及动态执行sql等部分 create or replace procedure test is  -- 条件sql  vc_sql varchar2(4000); 

    今天客户提了一个需求,需要处理下用户我的产品重复数据,是根据产品名称和产品编码、规格型号等字段判断是否有重复数据,如果有重复数据,删除多余的重复数据只保留一个即可;对应的写了一个存储过程:

    这里用到了游标和sql拼接及动态执行sql等部分

    create or replace procedure test is
        -- 条件sql
        vc_sql varchar2(4000);  
        --临时sql
        vc_tempSql varchar2(4000); 
        --删除sql 主要执行的sql 
        vc_deleteSql varchar(4000) ; 
        --计算sql,主要用于计算有几条重复数据 
        vc_selectSql varchar(4000) ;
        --接收vc_selectSql计算的结果
        vc_count number;  
       --spfl表游标定义
      cursor a_spfl is
      select * from test_table t where t.validate_flag = 1 and t.user_id is not null and t.nsrsbh is not null;
     
    begin
      --vc_sql :='select * from test_table a where  a.validate_flag = 1'; 
        
      for a in a_spfl loop
         vc_deleteSql :='delete from  test_table t  where  t.validate_flag = 1 ';
         vc_selectSql :='select count(1) from  test_table t  where  t.validate_flag = 1 ';
         vc_tempSql :=' and t.id = '''||a.id||'''';
       --  vc_sql := ' and t.user_id = '||a.user_id||' and t.nsrsbh ='''||a.nsrsbh||'''' ;
         --用户id
          if a.user_id is null  then
            vc_sql := '';
           else
           vc_sql := ' and t.user_id = '||a.user_id||' and t.nsrsbh ='''||a.nsrsbh||'''' ;
         end if;
          --nsrsbh
          if a.nsrsbh is null  then
             vc_sql := vc_sql;
           else
           vc_sql :=vc_sql ||' and t.nsrsbh ='''||a.nsrsbh||'''' ;
         end if;
         --产品名称
         if a.product_name is null  then
           vc_sql := vc_sql ||' and t.product_name is null';
         else
            vc_sql := vc_sql ||' and t.product_name ='''||a.product_name||'''' ;
         end if;
         --价格
         if a.price is null then
           vc_sql := vc_sql ||' and t.price is null';
         else
            vc_sql := vc_sql ||' and t.price ='''||a.price||'''' ;
         end if;
         --product_category_code 税收分类编码
         if a.product_category_code is null then
           vc_sql := vc_sql ||' and t.product_category_code is null';
         else
            vc_sql := vc_sql ||' and t.product_category_code ='''||a.product_category_code||'''' ;
         end if;
         
          --规格型号
         if a.specification is null then
           vc_sql := vc_sql ||' and t.specification is null';
         else
            vc_sql := vc_sql ||' and t.specification ='''||a.specification||'''' ;
         end if;
         --拼接删除sql
         vc_deleteSql := vc_deleteSql || vc_tempSql || vc_sql;
         --拼接计数sql
         vc_selectSql := vc_selectSql || vc_sql;
        ---  dbms_output.put_line(vc_selectSql);
         ---执行计数sql并且把查询出的数量赋值给vc_count
          execute immediate vc_selectSql into vc_count;  
          --如果计算大于2说明有重复数据         
          if vc_count >1 then
            --执行删除sql
            execute immediate vc_deleteSql ;
             commit;
            else
              vc_count := 0;
          end if;                
    
    
      end loop;
    end;
    


    展开全文
  • 1、MySQL修改表-防止SQL重复执行 ...可以通过存储过程来实现。 1.1 具体实现参考下面链接 原文链接: https://blog.csdn.net/happy_life123/article/details/39523077 2、了解schema中相关信息 首先要知道informat...

    1、MySQL修改表-避免SQL重复执行

    如何防止SQL被重复执行,一个很简单的办法就是在执行之前加上判断,如果满足给定条件,则执行,否则不执行。可以通过存储过程来实现。

    1.1 具体实现参考下面链接

    原文链接: https://blog.csdn.net/happy_life123/article/details/39523077

    2、了解schema中相关信息

    首先要知道information_schema是什么,在MySQL中,把 information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权 限等。

    列出test数据库中所有的表名,类型(普通表还是view)和使用的引擎

    select table_name, table_type, engine
    FROM information_schema.tables
    WHERE table_schema = ‘test’
    ORDER BY table_name DESC;

    解释: 对表的meta data的查询需要使用information_schema.tables, table_schema是数据库的名称,table_name是具体的表名,table_type指的是表的类型

    原文链接:https://blog.csdn.net/Dennis_Wu_/article/details/75516147

    展开全文
  • 在生产环境中脚本一般都要是可重复执行的,在这里记录一下相sql脚本的重复执行的规范写法 往表中添加字段: DROP PROCEDURE ADD_TABLE_COLUMN; CREATE PROCEDURE ADD_TABLE_COLUMN() BEGIN IF NOT EXISTS (SELECT * ...
  • Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。Ø 存储过程的概念...
  • 之前在开发过程中,利用sql查询一个对象是否存在时,总是报异常,异常的意思是说不能把多个对象set进一个对象中,然后通过group by,发现数据中重复数据有十几万条,于是编写了一个存储过程用来删除重复执行了一下...
  • HashSet不重复存储元素的执行过程

    千次阅读 2015-10-04 21:28:45
    在前几篇博客中写了关于ArrayList的一些知识,现在就学一个Set集合的东西,Set集合:元素不可重复,是...在HashSet集合中对每一个新对象都hash一个新值,这样子就认为是新元素,那么如何重写代码让它实现不重复存储呢?
  • 点击上方“格姗知识圈”,设为置顶或星标第一时间送达实用干货Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中...
  • USE CuService GO alter Proc dbo.OA_LeaseContract_Finance_Proce_Init @LTID int, @LogName varchar(30) ... IF EXISTS (SELECT * FROM ...这其中在AS下面那个IF判断的代码,写错了,执行不了,请教该如何修改?
  • 存储过程执行速度优化

    千次阅读 2018-08-23 13:10:58
    1.首先查看存储过程中是否有很多重复的实体表(一般给报表写存储过程,第一步就是使用临时表);  2.如果反复使用的实体表很多,就要考虑将这个实体表最小范围的数据放到临时表中,这样就可以大大提高查询效率;  ...
  • 存储过程是什么存储过程(Stored Procedure)是在数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户调用存储过程并传递参数来执行它。存储过程的优点存储过程是数据库中...
  • 原文连接https://www.cnblogs.com/geaozhang/p/6797357.html#chuangjian#SQL语句:先编译后执行存储过程(Stored Procedure):一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可...
  • c#执行存储过程

    2016-10-25 17:43:47
    用SQL SERVER,为了使数据库的效率更好,一般都会才取存储过程,因存储过程执行速度快,并且可以实现一些高级的查询等功能。比如传入一些数据参数,但执行的SQL过程可能不同等。 下面就来个例子,建立一新的角色,...
  • mysql存储过程中下面是一个较常见的场景,判断表中某列是否存在某值,如果存在执行某操作需要注意的是不能用if exists;exists可以在where后面或者在create object是使用,但是在if语句中不可以使用,只能用变通的...
  • /* 添加字段 */ DELIMITER $$ DROP PROCEDURE IF EXISTS `add_column` $$ CREATE PROCEDURE add_column() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.columns WHERE table_schema='dbName' AND table_...
  • 我有一个表student,表中有一个列stu_No(学号),想插入100行,学号从001递增到100,利用存储过程实现,代码该怎么敲呢?
  • mysql存储过程中 下面是一个较常见的场景,判断表中某列是否存在某值,如果存在执行某操作 需要注意的是不能用if exists; exists可以在where后面或者在create object是使用,但是在if语句中不可以使用,只能用变通的...
  • 之前在开发过程中,利用sql查询一个对象是否存在时,总是报异常,异常的意思是说不能把多个对象set进一个对象中,然后通过group by,发现数据中重复数据有十几万条,于是编写了一个存储过程用来删除重复执行了一下...
  • IF @run_flag=0 --今日未执行 Begin declare @d1 datetime,@d2 datetime,@Term int,@PriceMonth numeric(18,2),@d3 int,@sysFinanceNumber bigint -- ALTER TABLE [dbo].[OA_LeaseContract_Finance] drop [DF...
  • Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念 ...
  • 一般的SQL语句每执行一次就需要编译一次,所以使用存储过程提高了效率。(3)减少网络流量。存储过程位于服务器上,调用的时候只需要传递存储过程的名称以及参数就可以了,因此降低了网络传输的数据...
  • 存储过程

    2020-12-14 11:32:31
    2.如果某次操作需要执行多次SQL ,使用存储过程比单纯SQL语句执行要快。 3.可重复使用,能减少网络流量,还具有安全性。 4、存储过程缺点: 1.调试麻烦,但是用 PL/SQL Developer 调试很方便!弥补这个缺点。 2.移植...
  • Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念 ...
  • 通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。而我们常用的操作数据库语言SQL语句在执行的时 候需要要先编译,然后执行,所以执行的效率没有存储过程高。  存储过程优点如下: 重复使用。...
  • SQL Server尝试通过创建编译执行计划来优化你的存储过程执行。通常是在第一次执行存储过程时候会生成并缓存查询执行计划。当SQL Server数据库引擎编译存储过程中侦测到有参数值传递进来的时候,会创建基于这些...
  • 一、存储过程存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。而我们常用的...

空空如也

空空如也

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

存储过程重复执行