精华内容
下载资源
问答
  • 执行修改存储过程

    万次阅读 2018-06-22 17:39:51
    1.执行存储过程 要运行某个存储过程,只要简单地通过名字就可以引用它。如果对存储过程的调用不是批处理中的第一条语句,则需要使用EXECUTE关键字。下面是执行存储过程的语法格式。[[EXEC[UTE]]] {[@return_status...

    1.执行存储过程 

     要运行某个存储过程,只要简单地通过名字就可以引用它。如果对存储过程的调用不是批处理中的第一条语句,则需要使用EXECUTE关键字。下面是执行存储过程的语法格式。

    [[EXEC[UTE]]]

       {[@return_status=]

       procedure_name[;number]|@procedure_name_var}

    [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]]

    [,...n]

    [WITH RECOMPILE]

    各选项的含义如下:

    (1)@return_status:一个可选的整型变量,保存存储过程的返回状态。这个变量在EXECUTE语句使用前,必须已声明。

    (2)@procedure_name_var:一个局部变量名,用来代表存储过程的名称。

    (3)@patameter:过程参数,在CREATE PROCEDURE语句中定义。

    (4)value:过程中参数的值。如果参数名称没有指定,参数值必须以CREATE PROCEDURE语句中定义的顺序给出。

    其他数据和保留字的含义与CREATE PROCEDURE中价绍的一样。

    例如,执行上例的存储过程goods_info。在SQL查询分析其中输入如下命令。EXEC goods_info

    运行结果如下图所示:

    存储过程的执行结果
     goods_nameclasification_nameunit_price
    1IBM R51笔记本计算机9999.00
    2旭日160-D1.7G笔记本计算机9499.00

    2.修改存储过程

    修改存储过程可以通过SQL Server管理平台和Transact-SQL语句实现。

    1)使用SQL Server管理平台修改存储过程

    修改存储过程的操作步骤如下:

    (1)打开SQL Server管理平台,展开结点“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”,选择要修改的存储过程,并右击,在弹出的快捷菜单中选择“修改”命令。

    (2)此时在右边的编译器窗口中出现存储过程的源代码(将CREATE PROCEDURE 改为ALTER PROCEDURE),可以直接进行修改。修改完后单击工具栏中的“执行”按钮该存储过程,从而达到目的。

    2)使用ALTER PROCEDUR语句修改存储过程

    修改用CREATE PROCEDURE语句创建的存储过程,并且不改变权限的授予情况,不影响任何其他独立的存储过程或触发器,常使用ALTER PROCEDURE 语句。其语法规则如下:

    ALTER PROC[EDURE] procedure_name[,number]

    [{@parameter data_type}

    [VARYING][=default][OUTPUT]][,...n]

    [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

    [FOR REPLICATION]

    AS sql_statement[,...n]

    其中的参数和保留字的含义与CREATE PROCEDURE语句中的相似。

    举例:使用ALTER PROCEDURE 语句更改存储过程。

    ①创建存储过程employee_dep,以获取总经理办的男员工。

    CREATE PROCEDURE employee_dep AS 

    SELECT employee_name,sex,address,department_name 

    FROM employee e INNER JOIN department d

    ON e.department_id=d.department_id

    WHERE sex='男'AND  e.department_id='D001'

    GO

    执行存储过程employee_dep,结果如下

    执行结果
     employee_namesexaddressdepartment_name
    1钱大理东风路79号销售部
    2东方牧五一北路24号销售部

    ②用SELECT语句查询系统表sysobjects和syscomments,查看employee-dep存储过程的文本信息的代码如下:

    SELECT o.id,c.text 

    FROM sysobjects o INNER JOIN syscomments c ON o.id=c.id

    WHERE o.type='P' AND o.name='employee_dep'

    GO 

    ③使用ALTER PROCEDURE 语句对employee_dep过程进行修改,使其能够显示出所有男员工,并使employee_dep过程以加密方式存储在表syscomments中,其代码如下:

    ALTER PROCEDURE employee_dep

    WITH ENCRYPTION

    SELECT employee_name,sex,address,department_name

    FROM employee e INNER JOIN department d

    ON e.department_id=d.department_id

    WHERE sex='男'

    GO

    执行修改后的存储过程employee_dep,结果如下表所示。

    修改后employee_dep存储过程的执行结果
     employee_namesexaddressdepartment_name
    1钱大理东风路29号销售部
    2郭文斌五一北路25号市场部

    这是由于在ALTER PROCEDURE 语句中使用WITH ENCRYPTION 关键字对存储过程employee_dep文本进行了加密,其文本信息显示为NULL。

    也可以使用系统存储过程sp_helptext显示存储过程的定义(存储在syscomments系统表内),其命令如下:sp_helptext employee_dep

    结果为“对象‘employee_dep’的文本已加密“。

    加密后存储过程的执行结果
     idtext
    1837578022NULL



    展开全文
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...

                                                  oracle存储过程

    目录

             一.什么是存储过程

    二.为什么要写存储过程

    三.存储过程基础

    1.存储过程结构

    2.存储过程语法

    3.pl/sql处理存储过程

    四.存储过程进阶

    1.BUIK COLLECT

    2.FORALL

    3.pl/sql调试存储过程

    4.案例实战

    附.参考资料


    一.什么是存储过程

    存储过程,百度百科上是这样解释的,存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。

    简单的说就是专门干一件事一段sql语句。

    可以由数据库自己去调用,也可以由java程序去调用。

    在oracle数据库中存储过程是procedure。

    二.为什么要写存储过程

    1.效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如:记事本  上),都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2.降低网络流量

    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。

    3.复用性高

    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4.可维护性高

    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5.安全性高

    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

    三.存储过程基础

    1.存储过程结构

    (1).基本结构

    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)

    (2).无参存储过程

    CREATE OR REPLACE PROCEDURE demo AS/IS
    	变量2 DATE;
    	变量3 NUMBER;
    BEGIN
    	--要处理的业务逻辑
    	EXCEPTION    --存储过程异常
    END 
    

    这里的as和is一样任选一个,在这里没有区别,其中demo是存储过程名称。

    (3).有参存储过程

    a.带参数的存储过程

    CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
    AS/IS
    name student.name%TYPE;
    age number :=20;
    BEGIN
      --业务处理.....
    END

    上面脚本中,

    第1行:param1 是参数,类型和student表id字段的类型一样。

    第3行:声明变量name,类型是student表name字段的类型(同上)。

    第4行:声明变量age,类型数数字,初始化为20

     

    b.带参数的存储过程并且进行赋值

    CREATE OR REPLACE PROCEDURE 存储过程名称(
           s_no in varchar,
           s_name out varchar,
           s_age number) AS
    total NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
      dbms_output.put_line('符合该年龄的学生有'||total||'人');
      EXCEPTION
        WHEN too_many_rows THEN 
        DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
    END

    上面脚本中:

    其中参数IN表示输入参数,是参数的默认模式。
    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去

    第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。

    第8行:输出查询结果,在数据库中“||”用来连接字符串

    第9—11行:做异常处理

    2.存储过程语法

    (1).运算符

    这里s,m,n是变量,类型是number;

    分类

    运算符

    含义

    示例表达式

     

     

     

    算术运算符

    +

    s := 2 + 2;

    -

    s := 3 – 1;

    *

    s := 2 * 3;

    /

    s := 6 / 2;

    mod(,)

    取模,取余

    m : = mod(3,2)

    **

    乘方

    10**2 =100

     

     

     

    关系运算符

    =

    等于

    s = 2

    <>或!=或~=

    不等于

    s != 2

    <

    小于

    s < 3

    >

    大于

    s > 0

    <=

    小于等于

    s <= 9

    >=

    大于等于

    s >= 1

     

     

    比较运算符

    LIKE

    满足匹配为true

    ‘li’ like ‘%i’返回true

    BETWEEN

    是否处于一个范围中

    2 between 1 and 3 返回true

    IN

    是否处于一个集合中

    ‘x’ in (‘x’,’y’) 返回true

    IS NULL

    判断变量是否为空

    若:n:=3,n is null,返回false

     

    逻辑运算符

    AND

    逻辑与

    s=3 and c is null

    OR

    逻辑或

    s=3 or c is null

    NOT

    逻辑非

    not c is null

     

    其他

    :=

    赋值

    s := 0;

    ..

    范围

    1..9,即1至9范围

    ||

    字符串连接

    ‘hello’||’world’

     

    (2).SELECT INTO STATEMENT语句

    该语句将select到的结果赋值给一个或多个变量,例如:

    CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
    s_name VARCHAR2;   --学生名称
    s_age NUMBER;      --学生年龄
    s_address VARCHAR2; --学生籍贯
    BEGIN
      --给单个变量赋值
      SELECT student_address INTO s_address
      FROM student where student_grade=100;
       --给多个变量赋值
      SELECT student_name,student_age INTO s_name,s_age
      FROM student where student_grade=100;
      --输出成绩为100分的那个学生信息
      dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address);
    END

    上面脚本中:

    存储过程名称:DEMO_CDD1, student是学生表,要求查出成绩为100分的那个学生的姓名,年龄,籍贯

    (3).选择语句

    a.IF..END IF

    学生表的sex字段:1-男生;0-女生

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    END IF

    b.IF..ELSE..END IF

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    ELSE
      dbms_output.put_line('这个学生是女生');
    END IF

    (4).循环语句

    a.基本循环

    LOOP
      IF 表达式 THEN
        EXIT;
      END IF
    END LOOP;

    b.while循环

    WHILE 表达式 LOOP
      dbms_output.put_line('haha');
    END LOOP;

    c.for循环

    FOR a in 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
    END LOOP;

    (5).游标

        Oracle会创建一个存储区域,被称为上下文区域,用于处理SQL语句,其中包含需要处理的语句,例如所有的信息,行数处理,等等。

        游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)SQL语句返回。行集合光标保持的被称为活动集合。

    a.下表是常用的游标属性

    属性

    描述

    %FOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回true。否则,返回false。

    %NOTFOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回false。否则,返回true。

    %ISOPEN

    游标打开时返回true,反之,返回false。

    %ROWCOUNT

    返回DML执行后影响的行数。

    b.使用游标

    声明游标定义游标的名称和相关的SELECT语句:

    CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

    打开游标游标分配内存,使得它准备取的SQL语句转换成它返回的行:

    OPEN cur_cdd;

    抓取游标中的数据,可用LIMIT关键字来限制条数,如果没有默认每次抓取一条:

    FETCH cur_cdd INTO id, name ;

    关闭游标来释放分配的内存:

    CLOSE cur_cdd;

    3.pl/sql处理存储过程

    (1).新建存储过程:右键procedures,点击new,弹出PROCEDURE框,再点击OK,如下图:

     

    (2).在下面的编辑区,编写存储过程脚本

     

    (3).在这里我们编写一个demo_cdd存储过程,要求输出“hello world”,如下图:

     

    (4).右键刚才新建的存储过程名称,点击“Test”,在点击执行按钮

     

    4.案例实战

    场景:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    分析:

    如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。

    CREATE OR REPLACE PROCEDURE process_student is
    total NUMBER := 0;
    i NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student;
      WHILE i<=total LOOP
        UPDATE student SET grade=grade+1 WHERE s_no=i;
        i := i + 1;
        IF i >= 100000 THEN
          COMMIT;
        END IF;
      END LOOP;
      dbms_output.put_line('finished!');
    END;
    

    四.存储过程进阶

           在上面的案例中,我们的存储过程处理完所有数据要多长时间呢?事实我没有等到它执行完,在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况,存储过程是不是束手无策呢?答案是否定的,接下来我们看看其他绝招。

           我们先来分析下执行过程的执行过程:一个存储过程编译后,在一条语句一条语句的执行时,如果遇到pl/sql语句就拿去给pl/sql引擎执行,如果遇到sql语句就送到sql引擎执行,然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新,则执行焦点(正在执行的,状态处于ACTIVE)会不断的来回切换。

           Pl/SQL与SQL引擎之间的通信则称之为上下文切换,过多的上下文切换将带来过量的性能负载。最终导致效率降低,处理速度缓慢。

           从Oracle8i开始PL/SQL引入了两个新的数据操纵语句:FORALLBUIK COLLECT,这些语句大大滴减少了上下文切换次数(一次切换多次执行),同时提高DML性能,因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

    1.BUIK COLLECT

        Oracle8i中首次引入了Bulk Collect特性,Bulk Collect会能进行批量检索,会将检索结果结果一次性绑定到一个集合变量中,而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT,接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

    (1).SELECT INTO

    查出来一个结果集合赋值给一个集合变量。

    语法结构是:

    SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

    说明:

           field:要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)。

           var_collect:集合变量(联合数组等),用来存放查到的结果。

           table:表名,要查询的表。

           colStatement:后面过滤条件语句。比如s_age < 10;

    例子:查出年龄小于10岁的学生姓名赋值给数组arr_name变量

    SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

    (2).FETCH INTO

    从一个集合中抓取一部分数据赋值给一个集合变量。

    语法结构如下:

    FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

    说明:

            cur1:是个数据集合,例如是个游标。

            var_collect:含义同上。

            [LIMIT rows]:可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据。

    例子:给年龄小于10岁的学生的年级降一级。

    --查询年龄小于10岁的学生的学号放在游标cur_no里
    CURSOR cur_no IS 
    		SELECT s_no FROM student WHERE s_age < 10;
    
    --声明了一个联合数组类型,元素类型和游标cur_no每个元素的类型一致
    TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
    
    --声明一个该数组类型的变量no
    no ARR_NO;
    BEGIN
      FETCH cur_no BULK COLLECT INTO no LIMIT 100;
      FORALL i IN 1..no.count SAVE EXCEPTONS
    	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
    END;
    

    说明:先查出年龄小于10岁的学生的学号放在游标里,再每次从游标里拿出100个学号,进行更新,给他们的年级降一级。

    (3).RETURNING

    BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)。

    再看一眼学生表的字段情况:student(s_no, s_name, s_age, s_grade)

    语法结构如下:

    DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

    说明:

            DMLStatement:是一个DML语句。

            field:是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)。

            var_field:一个类型为该字段类型的集合,多个的话用逗号隔开,如下:

            (var_field1, var_field2, var_field3)

     

    例子:获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

    TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
    names NAME_COLLECT;
    BEGIN
      UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
      RETURNING s_name BULK COLLECT INTO names;
    END;

    说明:

           NAME_COLLECT:是一个集合类型,类型是student表的name字段的类型。

           names:定义了一个NAME_COLLECT类型的变量。

    (4).注意事项

    a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

    b.只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

    c.BULK COLLECT INTO 的目标对象必须是集合类型。

    d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

    e.如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。

    f.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

    2.FORALL

    (1).语法

    FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement;

    说明:

            index是指下标;

            bounds是一个边界,形式是start..end

            [SAVE EXCEPTIONS] 可写可不写,这个下面介绍;

            sqlStatement是一个DML语句,这里有且仅有一个sql语句;

    例子:

    --例子1:移除年级是5到10之间的学生
    FORALL i IN 5..10
           DELETE FROM student where s_grade=i;
    --例子:2,arr是一个数组,存着要升高一年级的学生名称
    FORALL s IN 1..arr.count SAVE EXCEPTIONS
           UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

    (2).SAVE EXCEPTIONS

    通常情况写我们在执行DML语句时,可能会遇到异常,可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来。

           如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段,例如:(1, 02300);

           ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号;

           ERROR_CODE:存储对应异常的,oracle错误代码;

    SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示,即:

           SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    (3). INDICES OF

    在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF。

    接下来我们来看看这个INDICES OF语句,用于处理稀疏数组或包含有间隙的数组(例如:一个集合的某些元素被删除了)。

    该语句语法结构是:

    FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

    说明:

    i:集合(嵌套表或联合数组)下标。

    collection:是这个集合。

    [SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

    例子:arr_std是一个联合数组,每个元素包含(name,age,grade),现在要向student表插入数据。

    FORALL i IN INDICES OF arr_stu
           INSERT INTO student VALUES(
               arr_stu(i).name,
                  arr_stu(i).age,
                  arr_stu(i).grade
           );

    (4). VALUES OF

    VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

    该语句的语法结构是:

    FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

    说明:i和collection含义如上

    联合数组请看文章(或自行百度):https://blog.csdn.net/leshami/article/details/7372061

    3.pl/sql调试存储过程

    首先,当前这个用户得有能调试存储过程的权限,如果没有的话,以数据库管理员身份给你这个用户授权:

    --userName是你要拿到调试存储过程权限的用户名
    GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

    (1).右键一个存储过程名称,点击测试,如下图:

    这里我用的pl/sql是12.0.4版本的,下面截图中与低版本的pl/sql按钮位置都相同,只是图标不一样。

     

    (2).点击两次step into按钮,进入语句调试,如下图:

     

    (3).每点击一次step into按钮,会想下执行一条语句,也可以查看变量和表达式的值,如下图:

     

    查看变量值:在查看变量区域,在Variable列输入变量i,在Value列点击下,该变量的值就显示出来了。

    4.案例实战

    场景和上面的案例实战是同一个,如下:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    编写存储过程:

    (1).存储过程1

    名称为:process_student1,student表的s_no字段类型为varchar2(16)。

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT s_no FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

            把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT,每次从这个游标里抓取10万条数据赋值给数组students,每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

            FETCH .. BULK COLLECT INTO .. LIMIT rows语句中:这个rows我测试目前最大可以为10万条。

    (2).存储过程2(ROWID)

           如果我们这个student表没有主键,也没有索引呢,该怎么来做呢?

    分析下:

           ROWNUM是伪列,每次获取结果后,然后在结果集里会产生一列,从1开始排,每次都是从1开始排。

            ROWID在每个表中,每条记录的ROWID都是唯一的。在这种情况下,我们可以用ROWID。但要注意的是,ROWID是一个类型,注意它和VARCHAR2之间的转换。有两个方法:ROWIDTOCHAR()是把ROWID类型转换为CHAR类型;CHARTOROWID()是把CAHR类型转换为ROWID类型。

    接下来我们编写存储过程process_student2,脚本如下:

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

           我们首先查到记录的ROWID并把它转换为CHAR类型,存放到游标CUR_STUDENT里,

    再每次抓取10万条数据赋值给数组进行更新,更新语句的WHERE条件时,又把数组元素是CAHR类型的rowid串转换为ROWID类型。

    附.参考资料

    存储过程基础:

           https://www.yiibai.com/plsql/plsql_basic_syntax.html

    存储过程进阶之FORALL:

           https://blog.csdn.net/leshami/article/details/7536926

           https://blog.csdn.net/jie1336950707/article/details/49966753

    存储过程进阶之BUIL COLLECT:

           https://blog.csdn.net/leeboy_wang/article/details/7991021

           https://blog.csdn.net/leshami/article/details/7545597

    联合数组:

           https://blog.csdn.net/leshami/article/details/7372061

    展开全文
  • 这里通过一个例子展示将ORACLE存储转化为MYSQL存储过程的一些必须修改的地方。 ORACLE存储过程 修改后的MYSQL存储过程 两者比较: 语法格式上的不同与修改: 1、CREATE OR REPLACE 改为 CREATE 和 2、入参:入参中...

    不同数据库的存储过程有不同的格式,数据类型。这里通过一个例子展示将ORACLE存储转化为MYSQL存储过程的一些必须修改的地方。
    ORACLE存储过程

    修改后的MYSQL存储过程

    两者比较:

    语法格式上的不同与修改:

    1、CREATE OR REPLACE 改为 CREATE 和DROP IF EXISTS
    2、入参:入参中的IN 要删除,类型要转换为MYSQL支持的类型,比如VARCHAR2等类型改成VARCHAR(255)等mysql类型
    3、局部变量定义:IS定义变量改为declare并移动到begin后面的行

    4、异常处理: oracle中的异常处理要修改为mysql中的hanlder方式
    EXCEPTION WHEN OTHERS THEN ROLLBACK;声明方式修改为 DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    5、–注释:要在–后增加空格 修改为 — 空格注释
    另外常见的还有,
    变量的赋值 oracle的:=赋值需要改为在开头增加set关键字赋值
    游标定义: CURSOR cur1 IS 游标定义修改为mysql格式DECLARE cur1 CURSOR FOR
    打开和获取游标:需要修改为open cur1并增加接收的变量FETCH ur1 INTO cur1

    6、常见函数上的不同:
            1、获取当前时间在mysql中使用NOW()函数
            2、mysql不存在DECODE函数,使用IF函数或者case when来等价替换。
            3、日期函数处理
    to_date(UPDATEDATE,’yyyy-mm-dd hh24:mi:ss’)” 修改为对应的:date_format(UPDATEDATE, ‘%Y%m%d %H%i%s’)
    时间相减使用TIMESTAMPDIFF函数,例如TIMESTAMPDIFF(SECOND,ENDDATE,NOW())
            4、nvl 修改为 IFNULL
            5、chr() 修改 CHAR()
            6、||字符串连接要改为使用CONCAT函数
            7、获取时间的毫秒数使用UNIX_TIMESTAMP,时间的加减要使用date_add和date_sub
            8、decode替换为if函数,或者case when then end

    7、语法细节上的不同:
    MySQL中from 后的表如果是(select…….)这种子查询,那么后面必须有别名;
    rownum要使用limit来设法等价替换

    还有很多不能列举,如果修改后报错,可以根据报错到网上查询原因,使用二分法定位到报错的行,并结合mysql的文档 https://dev.mysql.com/doc/refman/5.7/en/preface.html 来解决。或使用Debugger for MySQL软件来debugger逐行运行查看定位.

    https://blog.csdn.net/shy_snow/article/details/78262644

    展开全文
  • MySQL修改、删除存储过程和函数

    千次阅读 2019-09-02 16:49:04
    使用alter语句可以修改存储过程或函数的特性。 alter {procedure | function } sp_name {characteristic ...} 其中,sp_name参数表示存储过程或函数的名称,characteristic参数指定存储函数的特性,可能取的取值有...

    一、修改

    使用alter语句可以修改存储过程或函数的特性。

    alter {procedure | function } sp_name {characteristic ...}
    

    其中,sp_name参数表示存储过程或函数的名称,characteristic参数指定存储函数的特性,可能取的取值有:

    • contains SQL 表示子程序包含SQL语句,但不包含读写数据的语句。
    • no SQL 表示子程序中不包含SQL语句。
    • READS SQL DATA 表示子程序中包含读数据的语句。
    • MODIFIES SQL DATA 表示子程序中包含写数据的语句。
    • SQL security {definer | invoker } 指明谁有权限来执行。
    • DEFINER 表名只有定义者自己才能够执行。
    • INVOKER 表示调用者可以执行。
    • COMMENT ‘string’ 表示注释信息。

    修改存储过程使用alter procedure语句。修改存储函数使用alter function语句,但是,这两个语句的结构是一样的,语句中的所有参数也是一样的。而且,它们与创建存储过程或存储函数的语句中的参数也是基本一样的。

    【例1】修改存储过程CountProc的定义。将读写权限改为MODIFUES SQL DATA,并且指明调用者可以执行,代码如下:

    mysql> alter procedure CountProc
        -> modifies sql data
        -> sql security invoker;
    Query OK, 0 rows affected (0.01 sec)
    

    查询修改后的CountProc表的信息

    select specific_name,sql_data_access,security_type
    from information_schema.Routines
    where routine_name='CountProc' and routine_type = 'procedure';
    Empty set (0.00 sec)
    

    在这里是一张空表,如果是正常修改就可以看到数据的权限改变了,安全类型也改变了。

    【例2】修改村粗函数NameByZip的定义,将读写权限改为reads sql data,并加上注释信息"find name",SQL语句如下:

    mysql> alter function NameByZip
        -> reads sql data
        -> comment 'find name' ;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> select specific_name,sql_data_access,routine_comment
        -> from information_schema.Routines
        -> where routine_name='NameByZip' and routine_type = 'function';
    Empty set (0.00 sec)
    

    如果表不为空,就会看到访问数据的权限改编为reads sql data,函数注释变成find name。

    二、删除

    删除存储过程和函数,使用drop函数,语法形式如下:

    drop {procedure | function} [ if exists] sp_name
    

    这个语句被用来移除一个存储过程或函数,sp_name为要移除的存储过程或函数的名称。
    if exists 子句是一个MySQL的扩展,如果程序或函数不存储,那么它可以防止发生错误,产生一个用show warnings查看的警告。

    【例3】删除存储过程和存储函数,SQL语句如下:

    drop procedure CountProc;
    drop function ContProc;
    

    语句的执行结果如下:

    mysql> drop procedure CountProc;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> drop function NameByZip;
    Query OK, 0 rows affected (0.05 sec)
    

    上述语句的作用就是删除存储过程CountProc和存储函数NameByZip

    展开全文
  • pl/sql 修改包下存储过程步骤:假定有如下过程:pkg_ypgl_query.PROC_KCZQUERY;1. pl/sql 右侧objects面板中选择Package bodies&gt;PKG_YPGL_QUERY;2. 单击右键&gt;edit&gt;工具自动打开一个修改主页面;3...
  • MySQL 修改存储过程

    千次阅读 2020-03-14 09:37:32
    MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。 MySQL 中修改存储过程的语法格式如下: ALTER PROCEDURE 存储过程名 [ 特征 ... ] 特征指定了存储过程的特性,可能的取值有: CONTAINS SQL 表示子程序包含 SQL...
  • MySQL修改存储过程

    万次阅读 2017-07-28 23:37:24
    在实际开发中,业务需求修改的情况时有发生,这样,不可避免的需要修改存储过程的特征,MySQL中,使用ALTER语句修改存储过程的特性语法格式 ALTER {PROCEDURE | FUNCTION} sp_name [characteristic……]参数说明Sp_...
  • Oracle新建了一个存储过程,里面包含CREATE和DROP,逐条依据可以执行,但是执行整个存储过程报权限不足的错误。  在网上看到,Oracle规定,在默认的情况下,在调用存储过程用户的角色不起作用,即在执行存储过程时...
  • 摘要:C#源码,数据库应用,存储过程 C#利用存储过程修改数据,存储过程的应用例子,以前发过一个使用存储过程删除数据库的,今天再次分享一个利用存储过程修改数据记录的例子,相信会很实用的。
  • SQLServer之修改存储过程

    千次阅读 2018-11-02 10:53:24
    修改存储过程注意事项 只能修改先前在 SQL Server 中通过执行 CREATE PROCEDURE 语句创建的过程。 Transact-SQL 存储过程修改为 CLR 存储过程,反之亦然。 ALTER PROCEDURE 不会更改权限,也不影响相关的存储...
  • 本文为大家分享了MYSQL将表名称修改成大写的存储过程,具体内容如下 1. 条件: 1.1 Mysql设置对大小写敏感 2. 执行下述存储过程:  #call uppercase('库名') DROP PROCEDURE IF EXISTS uppercase; CREATE ...
  • 1.存储过程的分类 系统存储过程 本地存储过程(用户自定义) 临时存储过程(局部【#】、全局【##】临时存储过程) ...执行存储过程: execute sp_goods_price 200 2000 3.修改存储过程 create procedure sp_goods_
  • Mysql修改存储过程相关权限问题

    万次阅读 2020-09-21 21:44:14
    在使用mysql数据库经常都会遇到这么一个问题,其它用户定义的存储过程,现在使用另一个用户却无法修改或者删除等;正常情况下存储过程的定义者对它有修改、删除的权限;但是其它的用户就要相于的授权,不然无法查看...
  • MySQL——修改、删除存储过程和函数

    万次阅读 2018-05-22 23:30:01
    1. 修改存储过程和函数 使用 ALTER 语句可以修改存储过程或函数的特性,语法格式如下: ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ] 其中,那 sp_name 参数表示存储过程或函数...
  • 修改存储过程 修改先前通过执行 CREATE PROCEDURE 语句创建的过程。ALTER PROCEDURE 不会更改权限,也不影响相关的存储过程或触发器。但是,当修改存储过程时,QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含...
  • 上一节我们留下一个尾巴,我们这一节说,顺便说一下修改存储过程或者存储函数 存储过程和存储函数有什么不同 一、这个字面意思,过程表示的是一个SQL的执行块,我们一般会让存储过程去完成一些特定的任务、操作等...
  • 批量修改数据库存储过程、函数定义者 UPDATE mysql.proc SET DEFINER=’adm@%’ WHERE db = ‘数据库名称’; 修改指定的某个函数/存储过程定义者 UPDATE mysql.proc SET DEFINER=’adm@%’ WHERE db = ...
  • 1. 查看存储过程修改时间select * from sys.procedures WHERE name = 'proc_name'2. 查看存储过程最近执行时间...3. 查看存储过程修改时间和最近执行时间SELECT a.name,b.* FROM sys.procedures a LEFT JOIN sys.dm...
  • 摘要:C#源码,数据库应用,存储过程 C#利用存储过程修改数据,存储过程的一个简单应用,学习C#的用户,请参考吧,源代码开源免费,请使用VisualStudio2010编译源程序。
  • 存储过程的名字,参数,操作语句写好后,点击语法分析,没有错误就直接“F5”运行就好了,存储过程创建完毕,以下是一个基本的存储过程的代码: CREATE PROCEDURE Get_Data ( @Dealer_ID VARCHAR(<strong>50) ...
  • Oracle定时执行存储过程

    千次阅读 2018-02-01 18:54:00
    首先用一个完整的例子来实现定时执行存储过程。 任务目标:每小时向test表中插入一条数据 实现方案: 1.通过 oracle 中 dbms_job 完成存储过程的定时调用 2.在存储过程中完成相应的逻辑操作 实现步骤: ...
  • 运行存储过程报错: 原因 查询资料: 1305错误,由于当前用户没用权限,对用户进行授权后可以执行。 解决 MySQL创建存储过程/函数需要的权限: alter routine---修改与删除存储过程/函数 create routine--...
  • 注意:关于示例所讲到的存储过程和函数及数据库表的创建... 04MySQL----查看、修改存储过程和存储函数 存储过程和存储函数创建以后,用户可以查看存储过程和存储函数的状态和定义。用户可以通过SHOW STATUS语句查看...
  • oracle定时器执行存储过程

    千次阅读 2018-12-20 09:40:28
    最近做充流量项目,由于有时候极少数充值成功但是订单里的状态还是没有改为充值成功,刚开始需要每天去查看并修改相关订单状态,后来决定使用存储过程和定时任务去定时执行存储过程为: create or replace ...
  • 在Pathon中如何执行存储过程呢?可以使用如下方法:存储过程定义基本如下:ALTER procedure [dbo].[mysp] @Station varchar(50), @SN varchar(50), @Info va
  • 在项目开发中如果有时修改了一个存储过程,但是如何能够快速的查找到使用了这个存储过程的其它存储过程
  • 它第一次编译后,只要没有修改,处处都可以直接调用且不用重新编译,用户通过指定存储过程的名字和参数(若该存储过程存在参数)来执行它。 二、存储过程的特点 1:能够完成复杂的判断和运算; 2:可编程性强,且使用...
  • 运行界面上选择开始时间和结束时间获取这个时间段所有的修改文件及存储过程.

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 935,698
精华内容 374,279
关键字:

修改正在执行的存储过程