精华内容
下载资源
问答
  • mysql采用存储过程插入大批量数据

    千次阅读 2018-12-08 18:08:12
    人在年轻的时候,觉得到处都是人,别人的事就是你的事,到了中年以后,才觉得世界...线上库当然不能随意的增加和修改索引测试,但是测试库的数据量较少,测试效果不明显。因此考虑往测试库中增加百万的模拟数据。 2...

    人在年轻的时候,觉得到处都是人,别人的事就是你的事,到了中年以后,才觉得世界上除了家人已经一无所有了。——王小波《爱你就像爱生命》

    1、引言

    最近发现老项目中的首页加载速度较慢,发现做主要的原因由于单表数据已经超过百万了,于是考虑通过优化SQL语句和增加索引来提升加载速度。线上库当然不能随意的增加和修改索引测试,但是测试库的数据量较少,测试效果不明显。因此考虑往测试库中增加百万的模拟数据。

    2、解决方案

    (1)创建存储过程

    这里需要注意的是declare语句是在复合语句中声明变量的指令,通过循环插入,达到批量插入的效果。delimiter //其实就是告诉MySQL解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在这里修改为 // 符号结束。

    delimiter //
    create procedure batchInsert()
    begin
        declare num int; 
        set num=1;
        while num<=1000000 do
            insert into user(`username`,
        `password`) values(concat('测试用户', num),
            '123456');
            set num=num+1;
        end while;
    end
    // 
    delimiter ; #恢复;表示结束
    

    (2)调用存储过程

    写好了存储过程就可以进行调用了,可以通过命令调用:

    CALL batchInsert;
    

    也可以在数据库工具的中Functions的栏目下,找到刚刚创建的存储过程直接执行。

    (3)删除存储过程

    drop procedure batchInsert; 
    
    展开全文
  • 采用存储过程的方式批量更新数据,Oracle中也可采用merge-update的方式更新,采用批量提交更新方式会更快 DECLARE MAX_ROWS NUMBER DEFAULT 5000; ROW_ID_TABLE DBMS_SQL.UROWID_TABLE; DATE_TIME_TABLE DBMS_...

      采用存储过程的方式批量更新数据,Oracle中也可采用merge-update的方式更新,采用批量提交更新方式会更快

    DECLARE
      MAX_ROWS            NUMBER DEFAULT 5000;
      ROW_ID_TABLE        DBMS_SQL.UROWID_TABLE;
      DATE_TIME_TABLE      DBMS_SQL.DATE_TABLE;
      CURSOR C1 IS
        SELECT /*+ use_hash(t1,t2) parallel(t1,2) parallel(t2,2) */
         T1.DATE_TIME, T2.ROWID
          FROM TEST_TABLE_1 T1, TEST_TABLE_2 T2
         WHERE T1.TABLE_ID = T2.TABLE_ID
           AND T2.DATE_TIME IS NULL
           AND T1.DATE_TIME IS NOT NULL
           ORDER BY T2.ROWID;
    BEGIN
      OPEN C1;
      LOOP
        EXIT WHEN C1%NOTFOUND;
        FETCH C1 BULK COLLECT
          INTO DATE_TIME_TABLE, ROW_ID_TABLE LIMIT MAX_ROWS;
        FORALL I IN 1 .. ROW_ID_TABLE.COUNT
          UPDATE TEST_TABLE_2
             SET DATE_TIME = DATE_TIME_TABLE(I), LCD=SYSDATE
           WHERE ROWID = ROW_ID_TABLE(I);
        COMMIT;
      END LOOP;
      CLOSE C1;
    END;
    /


    展开全文
  • SQLServer之修改存储过程

    千次阅读 2018-11-02 10:53:24
    Transact-SQL 存储过程修改为 CLR 存储过程,反之亦然。 ALTER PROCEDURE 不会更改权限,也影响相关的存储过程或触发器。 但是,当修改存储过程时,QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含在该...

    修改存储过程注意事项

    只能修改先前在 SQL Server 中通过执行 CREATE PROCEDURE 语句创建的过程。

    Transact-SQL 存储过程修改为 CLR 存储过程,反之亦然。

    ALTER PROCEDURE 不会更改权限,也不影响相关的存储过程或触发器。 但是,当修改存储过程时,QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含在该存储过程中。 如果设置不同于最初创建存储过程时有效的设置,则存储过程的行为可能会更改。

    如果原来的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的,那么只有在 ALTER PROCEDURE 中也包含这些选项时,这些选项才有效。

    使用SSMS数据库管理工具和T-SQL脚本修改存储过程语法一致。

    使用T-SQL脚本修改存储过程

    语法:

    --声明数据库引用
    use 数据库名;
    go

    --修改存储过程

    alter procedure [schema_name][.]procedure_name [;number]
    [{ @parameter [type_schema_name.] data_type} [ null | not null ][varying] [ = default ] [ out| output ] [readonly] ] [,......n]
    with [encryption][,][recompile][,][execute as clause]
    [for replication]
    as
    begin
    sql_statement;
    end
    go

    语法解析:

    --schema_name
    --过程所属架构的名称。 过程是绑定到架构的。如果在创建过程时未指定架构名称,则自动分配正在创建过程的用户的默认架构。

    --procedure_name
    --过程的名称。 过程名称必须遵循有关标识符的规则,并且在架构中必须唯一。
    --在命名过程时避免使用 sp_ 前缀。 此前缀由 SQL Server 用来指定系统过程。 如果存在同名的系统过程,则使用前缀可能导致应用程序代码中断。
    --可在 procedure_name 前面使用一个数字符号 (#procedure_name) 来创建局部临时程序,使用两个数字符号 (##procedure_name) 来创建全局临时程序。 
    --局部临时程序仅对创建了它的连接可见,并且在关闭该连接后将被删除。 全局临时程序可用于所有连接,并且在使用该过程的最后一个会话结束时将被删除。 对于 CLR 过程,不能指定临时名称。
    --过程或全局临时程序的完整名称(包括 ##)不能超过 128 个字符。 局部临时程序的完整名称(包括 #)不能超过 116 个字符。

    --; number
    --适用范围: SQL Server 2008 到 SQL Server 2017 和 Azure SQL Database。
    --用于对同名的过程分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

    --@parameter
    --在过程中声明的参数。 通过将 at 符号 (@) 用作第一个字符来指定参数名称。 参数名称必须符合标识符规则。 每个过程的参数仅用于该过程本身;其他过程中可以使用相同的参数名称。
    --可声明一个或多个参数;最大值是 2,100。 除非定义了参数的默认值或者将参数设置为等于另一个参数,否则用户必须在调用过程时为每个声明的参数提供值。 
    --如果过程包含表值参数,并且该参数在调用中缺失,则传入空表。 参数只能代替常量表达式,而不能用于代替表名、列名或其他数据库对象的名称。 有关详细信息,请参阅 EXECUTE (Transact-SQL)。
    --如果指定了 FOR REPLICATION,则无法声明参数。

    --[type_schema_name. [ =] data_type
    --参数的数据类型以及该数据类型所属的架构。
    --针对 Transact-SQL 过程的准则:
    --所有 Transact-SQL 数据类型都可以用作参数。
    --您可以使用用户定义的表类型创建表值参数。 表值参数只能是 INPUT 参数,并且这些参数必须带有 READONLY 关键字。 有关详细信息,请参阅使用表值参数(数据引擎)
    --游标数据类型只能是 OUTPUT 参数,并且必须带有 VARYING 关键字。
    --针对 CLR 过程的准则:
    --在托管代码中具有等效值的所有本机 SQL Server 数据类型都可以用作参数。 有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息,请参阅映射 CLR 参数数据。 有关 SQL Server 系统数据类型及其语法的详细信息,请参阅数据类型 (Transact-SQL)。
    --表值或游标数据类型不能用作参数。
    --如果参数的数据类型为 CLR 用户定义类型,则必须对此类型有 EXECUTE 权限。

    --varying
    --指定作为输出参数支持的结果集。 该参数由过程动态构造,其内容可能发生改变。 仅适用于游标参数。 该选项对于 CLR 过程无效。

    --default
    --参数的默认值。 如果为参数定义了默认值,则无需指定此参数的值即可执行过程。 默认值必须是常量或 NULL。 该常量值可以采用通配符的形式,这使其可以在将该参数传递到过程时使用 LIKE 关键字。
    --只有 CLR 过程的默认值记录在 sys.parameters.default 列中。 对于 Transact-SQL 过程参数,该列将为 NULL。

    --out|output
    --指示参数是输出参数。 使用 OUTPUT 参数将值返回给过程的调用方。 除非是 CLR 过程,否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。 OUTPUT 参数可以为游标占位符,CLR 过程除外。 不能将表值数据类型指定为过程的 OUTPUT 参数。

    --readonly
    --指示不能在过程的主体中更新或修改参数。 如果参数类型为表值类型,则必须指定 READONLY。

    --encryption
    --适用范围:SQL Server( SQL Server 2008 到 SQL Server 2017)、 Azure SQL Database。
    --指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。 模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。 
    --但是,可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。 此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。 有关如何访问系统元数据的详细信息,请参阅元数据可见性配置。
    --该选项对于 CLR 过程无效。
    --使用此选项创建的过程不能作为 SQL Server 复制的一部分发布。

    --recompile
    --指示 数据库引擎不缓存此过程的查询计划,这强制在每次执行此过程时都对该过程进行编译。 有关强制重新编译的原因的详细信息,请参阅重新编译存储过程。 在指定了 FOR REPLICATION 或者用于 CLR 过程时不能使用此选项。
    --若要指示 数据库引擎放弃过程内单个查询的查询计划,请在该查询的定义中使用 RECOMPILE 查询提示。 有关详细信息,请参阅查询提示 (Transact-SQL)。

    --execute as 子句
    --指定在其中执行过程的安全上下文。
    --对于本机编译存储过程(从 SQL Server 2016 (13.x) 开始和在 Azure SQL Database 中),EXECUTE AS 子句没有任何限制。 在 SQL Server 2014 (12.x) 中,对于本机编译的存储过程,支持 SELF、OWNER 和 ‘user_name’ 子句。
    --有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)。
    --SELF
    --EXECUTE AS SELF 与 EXECUTE AS user_name 等价,其中指定用户是创建或更改模块的用户。 创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
    --OWNER
    --指定模块内的语句在模块的当前所有者上下文中执行。 如果模块没有指定的所有者,则使用模块架构的所有者。 不能为 DDL 或登录触发器指定 OWNER。
    --' user_name '
    --指定模块内的语句在 user_name 指定的用户的上下文中执行。 将根据 user_name 来验证对模块内任意对象的权限。 不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。 请改用 login_name。
    --user_name 必须存在于当前数据库中,并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。
    --执行上下文的用户 ID 存储在元数据中,可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
    --' login_name '
    --指定模块内的语句在 login_name 指定的 SQL Server 登录的上下文中执行。 将根据 login_name 来验证对模块内任意对象的权限。 只能为具有服务器作用域的 DDL 触发器或登录触发器指定 login_name。
    --login_name 不能为组、角色、证书、密钥或内置帐户,如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。

    --for replication
    --适用范围:SQL Server( SQL Server 2008 到 SQL Server 2017)、 Azure SQL Database。
    --指定为复制创建该过程。 因此,它不能在订阅服务器上执行。 使用 FOR REPLICATION 选项创建的过程可用作过程筛选器,且仅在复制过程中执行。 如果指定了 FOR REPLICATION,则无法声明参数。 对于 CLR 过程,不能指定 FOR REPLICATION。 对于使用 FOR REPLICATION 创建的过程,忽略 RECOMPILE 选项。
    --FOR REPLICATION 过程在 sys.objects 和 sys.procedures 中包含 RF 对象类型。

    --{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    --构成过程主体的一个或多个 Transact-SQL 语句。 您可以使用可选的 BEGIN 和 END 关键字将这些语句括起来。 有关信息,请参阅后面的“最佳实践”、“一般备注”以及“限制和局限”部分。

    示例:

    --声明数据库引用
    use testss;
    go

    --修改存储过程
    alter procedure [dbo].[noreference]
    ;1
    @inputparamter int=null,@outputparamter nvarchar(100)=null output
    with encryption,recompile,execute as owner
    --for replication
    as
    begin
    if @inputparamter is not null
    begin
    select * from test1 where id=@inputparamter;
    set @outputparamter='我是修改过的有参数的输出指定值的存储过程'; 
    end
    else
    begin
    select * from test1;
    set @outputparamter='我是修改过的无参数的输出全部值的存储过程';
    end
    end
    go

    示例结果:本示例演示如何调用存储过程和存储过程执行结果。

    1、连接数据库-》选择数据库-》展开数据库-》展开可编程性-》展开存储过程-》选择存储过程-》右键点击-》选择执行存储过程。

    2、在执行存储过程弹出框-》输入存储过程参数-》点击确定。

    3、存储过程执行结果。

    展开全文
  • 储存过程

    2019-12-09 18:48:57
    从常用的操作数据库的SQL语句在执行的时候需要先编译,然后执行,储存过程,则是采用另外一种方式执行sql语句。 一个储存过程是一个可编程的函数。它在数据库中创建并保存,一般由SQL语句和一些特殊控制的结构组成,...

    储存过程是一组为了完成特定功能的SQL语句表,经过编译后储存在数据库中,用户通过指定过程的名字并给定参数来调用执行它。
    从常用的操作数据库的SQL语句在执行的时候需要先编译,然后执行,储存过程,则是采用另外一种方式执行sql语句。
    一个储存过程是一个可编程的函数。它在数据库中创建并保存,一般由SQL语句和一些特殊控制的结构组成,当希望在不同的平台或者应用程序执行相同的特定功能时,有储存过程是合适的。
    1、声明变量
    在存储过程体中可以声明局部变量,用来存放临时产生的结果。
    局部变量和全局变量相对应,是局部范围内有效变量。
    局部变量的声明:
    declare 变量名 变量类型 default null

    在这里插入图片描述
    二 创建存储过程:
    在mysql中默认是以分号结束sql语句的,但是在创建存储过程时,存储过程体可能包含多条SQL语句,这些SQL也是以;作为标识结束的,这样的话,mysql 就会以第一个分号作为结束符,鉴于这种情况,所以我们要先修改结束标识符。
    delimiter $$
    1 创建不带参数的存储过程:
    在这里插入图片描述
    调用存储过程:
    在这里插入图片描述
    创建带参数的存储过程:
    在这里插入图片描述
    调用带参数的存储过程:
    在这里插入图片描述
    修改存储过程:
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • 该用存储过程

    千次阅读 2018-01-23 15:02:55
    本篇文章讨论并是:不要使用存储过程,因为有些事情还是要存储过程来完成,可能不用。而是关于:"业务逻辑是不是要封装在存储过程中实现,这样子php、java等就是调用存储过程"。   业务逻辑,通俗说就是:比如...
  • 存储过程

    热门讨论 2017-08-12 16:09:29
    简介(what): 存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数...
  • 为什么建议使用存储过程

    千次阅读 2019-07-21 23:28:57
    在公司的系统升级换代中,明确规定在数据库开发中允许再使用存储过程了,以前的老一代系统中,很多复杂的业务逻辑都是存储过程写的,那为什么风光无限的存储过程不再被宠幸了呢?首先了解下什么是存储过程,它有...
  • SQL Server数据库存储过程——以自定义存储过程为例 1、什么是数据库存储过程 存储过程就是预编译SQL语句的集合,这些语句存储在一个名称下并作为一个单元处理。存储过程代替了传统的逐条执行SQL语句的方式 SQL...
  • 为什么推荐使用存储过程

    千次阅读 多人点赞 2019-06-17 18:00:33
    本文作者:菜v菜 | 观点整理:徐刘根 ...存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程...
  • 本文来自:http://rzchina.net/node/31571、 由于应用程序随着时间推移会不断更改,增删功能,SQL过程代码会变得更复杂,存储过程为封装此代码提供了一个替换位置。2、 存储过程可以用于降低网络流量,存储过程代码...
  • 存储过程介绍

    2020-03-17 20:03:49
    存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器...存储过程采用另一种方式来...
  • 当然在系统控制方便方面,例如当系统进行调整时,这是只需要将后台存储过程进行更改,而需要更改客户端程序。也无需重新安装客户端应用程序。 存储过程不仅仅适用于大型项目,对于中小型项目,使用存储过程也是...
  • mysql存储过程详解 1. 存储过程简介   我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在...
  • 存储过程存储函数

    2019-03-01 14:13:16
    存储过程存储函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储...本篇博文将介绍存储过程存储函数的含义、作用,以及创建使用查看修改及删除存储过程存储函数的方法。 在数据库系统中,为了保...
  • 最近在做一个Oracle千万级会员积分数据同步的操作,一开始我是直接写了一个insert操作存储过程,异常时回滚,语句如下: CREATE OR REPLACE PROCEDURE PROC_ZTWD_ADJINTEGRAL_SYNC AS /**************************...
  • MySQL——存储过程详解及实例分析

    千次阅读 2019-05-11 16:27:49
    一、储存过程简介 1、什么是存储过程 2、存储过程优缺点 3、存储过程入门程序 4、在idea中如何调用储存过程? 二、存储过程编程 1、存储过程的变量 2、存储过程中的参数 3、选择结构if 4、分支结构case 5...
  • mysql 存储过程

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

    千次阅读 2014-10-22 17:12:48
    1. 存储过程简介 我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的...
  • oracle存储过程实例

    万次阅读 2016-07-05 12:19:27
    认识存储过程和函数 存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中...
  • Oracle的存储过程

    2016-12-11 10:33:19
    一、存储过程定义 存储过程,是命名的PL/SQL块、能够接受参数、能够被重复调用、用于某项操作、存储在数据库中(凡是create出来的东西都是存在数据库中的)的子程序。二、存储过程与函数的唯一区别 存储过程无...
  • MySQL存储过程

    2014-08-16 23:26:02
    1. 创建 PROCEDURE CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE...(1) 存储过程注释:MySQL支持采用--或者/**/注释,其中前者是行注释,后者是段式注释。 (2) 变量首先用declare申明,其中临时变量可以直接
  • 存储过程的优缺点

    千次阅读 2019-05-20 16:21:33
    在维护公司的几年前的一个项目,虽说上是历史悠久,但也有些年头了,采用三层架构,加上存储过程的这么一个老的架构,本来这个架构也没什么问题,大部分系统都是这么干的,但是由于这个系统存储过程被滥用的问题,...
  • Oracle存储过程_参数

    千次阅读 2019-05-27 00:06:36
    输入模式的参数,用于接收参数,在子程序内部,不能进行修改。 --定义打印的存储过程 CREATE OR REPLACE PROCEDURE println (str varchar) AS BEGIN dbms_output.put_line(string); --输出语句 END; --...
  • SQL Server存储过程

    千次阅读 2018-12-18 18:48:59
    SQL Server存储过程 定义: 用户通过制定存储过程的名称并给出参数来执行它; SQL Server为了实现特定任务,而将一些多次调用的固定操作语句编写成程序段,这些程序段存储在服务器上,由数据库服务器来调用; 命名...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 539,025
精华内容 215,610
关键字:

存储过程的修改不能采用