存储过程 订阅
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升 展开全文
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
信息
外文名
Stored Procedure
中文名
存储过程
存储过程格式
sql中的存储过程及相关介绍:CREATE PROCEDURE [拥有者.]存储过程名[;程序编号][(参数#1,…参数#1024)][WITH{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}][FOR REPLICATION]AS 程序行其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数(SQL Server 7.0以上版本),参数的使用方法如下:@参数名数据类型[VARYING] [=内定值] [OUTPUT]每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外,其他SQL Server所支持的数据类型都可使用。[内定值]相当于我们在建立数据库时设定一个字段的默认值,这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的,也就是在调用了这个存储过程时,如果所指定的参数值是我们需要输入的参数,同时也需要在结果中输出的,则该项必须为OUTPUT,而如果只是做输出参数用,可以用CURSOR,同时在使用该参数时,必须指定VARYING和OUTPUT这两个语句。例子:例子说明:该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的订单ID号码(@o_id),由订单明细表 (orderdetails)中计算该订单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序。
收起全文
精华内容
下载资源
问答
  • 存储过程

    千次阅读 2020-05-10 11:12:09
    一、存储过程 1.定义: 一般在大型的数据库系统中,一组为了完成特定功能的sql语句集,存储在数据库中,经过第一次编译后,再次调用时就不需要再次编译了,用户只需要指定存储过程名称并给定参数就可以执行完成任务...

    一、存储过程
    1.定义:
    一般在大型的数据库系统中,一组为了完成特定功能的sql语句集,存储在数据库中,经过第一次编译后,再次调用时就不需要再次编译了,用户只需要指定存储过程名称并给定参数就可以执行完成任务。
    在开发程序中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭是很耗费资源的),需要对数据库进行多次I/O读写,性能比较低。如果把这些放到存储过程中,就可以实现连接关闭一次数据库来实现业务,大大提高了效率。
    2.语法:
    (1)创建存储过程
    create procedure 存储过程名(参数)
    begin
    sql语句…
    sql语句…
    end;

       例:
       create procedure pro_b()
       begin
            select * from t_man;
       end;
       注意:sql语句是以“;”结尾的,这样“end;”就不会执行,造成存储过程不完整,需要重新定义结束符,一般
          使用delimiter定义,如:delimiter #,表示遇到#算结束。
       create procedure pro_b()
       begin
            select * from t_man;
       end#
    

    (2)调用存储过程
    call pro_b() #
    (3)删除存储过程
    drop procedure (if exists) 存储过程名;
    3.带参数的存储过程:in out inout
    (1)in类型:in可以省略不写,默认是in类型,即入参
    例:给存储过程一个学号,存储过程显示学生信息
    create procedure pro_c(in id int)
    begin
    select * from t_student where sid=id;
    end#

       调用:call pro_c(12)#
       例:给存储过程一个班级编号和英文名,查询这些学生信息(or)(多参数)
       create procedure pro_d(in class int,in ename varchar(20))
       begin 
            select * from t_student where sclass=class or sename=ename;
       end;
       注意:存储过程中limit后面不能使用参数,应写明具体值,下面写法错误:
       create procedure pro_e(in n int,in m int)
       begin 
            select * from t_student limit m,n;
       end;
    (2)out类型
           例:写一个存储过程,返回3班的最高分
       create procedure pro_r1(out x int)
       begin
            select max(sscore) into x from t_student where sclass=3;
       end;
       注意:执行后最高分放到变量x里,调用out存储过程前,需要先定义一个变量:set @变量名=值;
             call 存储过程名(@变量名);
    		 
    	例:给存储过程一个系编号,获取对应的系名称;
    	create procedure pro_r2(in x int,out name varchar(20))
    	begin 
    	     select dname into name from t_dept where dclass=x;
        end#
    	执行过程:
        ①执行存储过程;
    	②定义输出变量:set @n=‘abc’#
    	③调用存储过程:call pro_r2(3,@n)#
    	④显示@n结果:select @n #
    (3)inout类型:入参和出参使用同一个参数
               例:传入一个班级编号,返回这个班级的最高分
         create procedure pro_r3(inout x int)
         begin  
             select max(sscore) into x from t_student where sclass=x;
          end#
           执行过程:
           ①执行存储过程;
           ②定义输出变量:set @a=1 #
           ③调用存储过程:call pro_r2(3,@a) #
           ④显示@n结果:select @a #
    

    二、PLSQL编程:
    1.定义:PLSQL(Procedure Language SQL),是Oracle对sql语句的过程化扩展,在SQL语言中添加过程处理语句(如分支、循环等),使SQL语言具有过程处理能力。
    PLSQL语言的基本结构:
    在这里插入图片描述
    2.变量:常见变量分为两大类
    ①普通数据类型:char、varchar2、date、number、boolean、long;
    ②特殊变量类型:引用型变量、记录型变量
    声明变量的方式:
    变量名 变量类型(变量长度)
    name varchar2(20);
    (1)普通变量:赋值的方式有两种
    ①直接赋值语句:使用:= 赋值
    ②语句赋值:使用select…into…赋值,即select 值 into 变量
    例:
    在这里插入图片描述
    (2)引用型变量:变量的类型和长度取决于表中字段的类型和长度。通过
    表名.列名%TYPE指定变量的类型和长度,例如:name emp.ename%TYPE
    例:
    在这里插入图片描述
    (3)记录型变量:接受表中的一整行记录,相当于Java中的一个对象
    语法:变量名称 表名%ROWTYPE,例如:v_emp emp%ROWTYPE
    例:
    在这里插入图片描述
    3.流程控制:
    (1)条件分支:
    语法:
    BEGIN
    IF 条件1 THEN 执行1
    ELSIF 条件2 THEN 执行2
    ELSE 执行3
    END IF
    END;
    例:
    在这里插入图片描述
    (2)循环:在Oracle中有3种循环方式,这里介绍:loop循环
    语法:
    BEGIN
    LOOP
    EXIT WHEN 退出循环条件
    END LOOP;
    END;
    例:
    在这里插入图片描述
    4.游标:
    (1)定义:用于临时存储一个查询返回的多行数据(结果集,类似于Java的 JDBC连接返回的ResultSet集合),通过遍历游标逐行访问处理该结果集的数据 。
    (2)使用方式:声明—>打开—>读取—>关闭
    (3)语法:
    ●游标声明:
    CURSOR 游标名[(参数列表)] IS 查询语句;
    ●游标打开:
    OPEN 游标名;
    ●游标取值:
    FETCH 游标名 INTO 变量列表;
    ●游标关闭:
    CLOSE 游标名;
    (4)游标的属性:
    在这里插入图片描述
    (5)无参游标:
    例:
    在这里插入图片描述
    在这里插入图片描述
    (6)有参游标:
    在这里插入图片描述
    在这里插入图片描述
    存储过程:
    在这里插入图片描述
    三、在Java程序中调用存储过程(JDBC连接中):
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • 存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字给出参数来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好 的数据库应用程序都应该用到存储过程。 ...

    存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字给出参数来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好 的数据库应用程序都应该用到存储过程。
    优点:
    (1)允许模块化程序设计,就是说只要创建一次过程,以后在程序中就可以调用该过程任意次。
    (2)允许更快执行,如果某操作需要执行大量sql语句或重复执行,存储过程比sql语句执行的要快。
    (3)减少网络流量,例如需要数百行的sql代码的操作有一条执行语句完成,不需要在网络中发送数百行代码。
    (4)更好的安全机制,对于没有权限执行存储过程的用户,也可授权他们的执行存储过程。

    展开全文
  • 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

    展开全文
  • mysql存储过程学习笔记

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

    本文主要记录了本人学习mysql存储过程时的笔记,文档中用到的表结构在文档结尾处有说明

    一、定义

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

    二、存储过程的特点

        1、能完成较复杂的判断和运算
        2、可编程行强,灵活
        3、SQL编程的代码可重复使用
        4、执行的速度相对快一些
        5、减少网络之间的数据传输,节省开销    

    三、创建一个简单的存储过程

        1、创建存储过程的简单语法

    create procedure 名称()
    begin
    .........
    end

        2、创建一个简单的存储过程

    create procedure testa()
    begin
        select * from users;
        select * from orders;
    end;

        3、调用存储过程

    call testa();   

    运行结果如图(1)和图(2):

                                     图(1)

     

                                     图(2)

    四、存储过程的变量

        1、先通过一个简单的例子来学习变量的声明和赋值

    create procedure test2()
    begin
      -- 使用 declare语句声明一个变量
      declare username varchar(32) default '';
      -- 使用set语句给变量赋值
      set username='xiaoxiao';
      -- 将users表中id=1的名称赋值给username
      select name into username from users where id=1;
      -- 返回变量
      select username;
    end;

        2、概括
            (1)、变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用;

            (2)、变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能制定默认值、字符集和排序规则等;
            (3)、变量可以通过set来赋值,也可以通过select into的方式赋值;
            (4)、变量需要返回,可以使用select语句,如:select 变量名。
           

    五、变量的作用域

        1、变量作用域说明:
            (1)、存储过程中变量是有作用域的,作用范围在begin和end块之间,end结束变量的作用范围即结束。
            (2)、需要多个块之间传值,可以使用全局变量,即放在所有代码块之前
            (3)、传参变量是全局的,可以在多个块之间起作用
        2、通过一个实例来验证变量的作用域

             需求: 创建一个存储过程,用来统计表users、orders表中行数数量和orders表中的最大金额和最小金额

    create procedure test3()
    begin
      begin
        declare userscount int default 0; -- 用户表中的数量
        declare ordercount int default 0; -- 订单表中的数量
        select count(*) into userscount from users;
        select count(*) into ordercount from orders;
        select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
      end;
      begin 
        declare maxmoney int default 0; -- 最大金额
        declare minmoney int default 0; -- 最小金额
        select max(money) into maxmoney from orders;
        select min(money) into minmoney from orders;
        select maxmoney,minmoney; -- 返回最金额、最小金额
       end;
    end;

    调用以上存储过程,结果如图(3)和图(4):

               

                       (3)    

         
                           (4)

        3、我将过程test(3)改为如下:

        create procedure test3()
        begin
          begin
            declare userscount int default 0; -- 用户表中的数量
            declare ordercount int default 0; -- 订单表中的数量
            select count(*) into userscount from users;
            select count(*) into ordercount from orders;
            select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
          end;
          begin 
            declare maxmoney int default 0; -- 最大金额
            declare minmoney int default 0; -- 最小金额
            select max(money) into maxmoney from orders;
            select min(money) into minmoney from orders;
            select userscount,ordercount,maxmoney,minmoney; -- 返回最金额、最小金额
           end;
        end;

    再次调用call test3(); 会报错如图(5):

                                   图(5)

        4、将userscount,ordercount改为全局变量,再次验证

        create procedure test3()
        begin

            declare userscount int default 0; -- 用户表中的数量
        
        declare ordercount int default 0; -- 订单表中的数量
            begin
                select count(*) into userscount from users;
                select count(*) into ordercount from orders;
                select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
          end;
          begin 
            declare maxmoney int default 0; -- 最大金额
            declare minmoney int default 0; -- 最小金额
            select max(money) into maxmoney from orders;
            select min(money) into minmoney from orders;
            select userscount,ordercount,maxmoney,minmoney; -- 返回最金额、最小金额
           end;
        end;

    再次调用call test3(); 会报错如图(6)和图(7):

           

                        图(6)       

                                                                 

                        图(7)

    因此,存储过程中变量的作用域,作用范围在begin和end块之间,end结束变量的作用范围即结束
        

    六、存储过程参数


      1、基本语法

    create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
    begin
    .........
    end

     存储过程的参数类型有:IN,OUT,INOUT,下面分别介绍这个三种类型:
        
      2、存储过程的传出参数IN

         说明:        

            (1)、传入参数:类型为in,表示该参数的值必须在调用存储过程事指定,如果不显示指定为in,那么默认就是in类型。
            (2)、IN类型参数一般只用于传入,在调用过程中一般不作为修改和返回
            (3)、如果调用存储过程中需要修改和返回值,可以使用OUT类型参数

    通过一个实例来演示:

    需求:编写存储过程,传入id,根据id返回name

     create procedure test4(userId int)
        begin
                declare username varchar(32) default '';
                declare ordercount int default 0;
                select name into username from users where id=userId;
                select username;
        end;

    运行如图(8)


        

                                                           图(8)
      
        
       3、存储过程的传出参数out
        
            需求:调用存储过程时,传入userId返回该用户的name
            create procedure test5(in userId int,out username varchar(32))
            begin
                select name into username from users where id=userId;
            end;

            调用及运行结果如图(9):

                                       图(9)
            
      概括:
            1、传出参数:在调用存储过程中,可以改变其值,并可返回;
            2、out是传出参数,不能用于传入参数值;
            3、调用存储过程时,out参数也需要指定,但必须是变量,不能是常量
            4、如果既需要传入,同时又需要传出,则可以使用INOUT类型参数
        
        (3).存储过程的可变参数INOUT
        
            需求:调用存储过程时,传入userId和userName,即使传入,也是传出参数。

    create procedure test6(inout userId int,inout username varchar(32))
    begin
        set userId=2;
        set username='';
        select id,name into userId,username from users where id=userId;
    end;

     调用及运行结果如图(10)


            

                                           图(10)
        概括:
            1、可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值;
            2、INOUT参数集合了IN和OUT类型的参数功能;
            3、INOUT调用时传入的是变量,而不是常量;

     

    七、存储过程条件语句

       1、基本结构

       (1)、条件语句基本结构:

    if() then...else...end if;

       (2)、多条件判断语句:

    if() then...
    elseif() then...
    else ...
    end if;

       2、实例
        实例1:编写存储过程,如果用户userId是偶数则返回username,否则返回userId

    create procedure test7(in userId int)
    begin
       declare username varchar(32) default '';
       if(userId%2=0)
       then 
          select name into username from users where id=userId;
          select username;
       else
          select userId;
          end if;
    end;

        调用及运行结果如图(11)和图(12):

           

                            图(11)           

                                         

                                   图(12)
        
        2、存储过程的多条件语句应用示例
            需求:根据用户传入的uid参数判断
            (1)、如果用户状态status为1,则给用户score加10分;
            (2)、 如果用户状态status为2,则给用户score加20分;
            (3)、 其他情况加30分

    create procedure test8(in userid int)
    begin
       declare my_status int default 0;
       select status into my_status from users where id=userid;
       if(my_status=1)
       then 
           update users set score=score+10 where id=userid;
        elseif(my_status=2)
        then 
           update users set score=score+20 where id=userid;
        else 
           update users set score=score+30 where id=userid;
        end if;
    end;

    调用过程之前的users表的数据如图(13),调用 call test8(1); 及运行结果图(14):


               
                                       图(13)       

         

                                        图(14)
          

    八、存储过程循环语句


        1、while语句

           (1)、while语句的基本结构

    while(表达式) do 
       ......  
    end while;

             (2)、示例
        需求:使用循环语句,向表test1(id)中插入10条连续的记录

    create procedure test9()
    begin
      declare i int default 0;
      while(i<10) do 
        begin 
            select i;
            set i=i+1;
            insert into test1(id) values(i);
         end;
      end while;
    end;

        调用及运行结果结果如图(15)和图(16):


        

                                                                        图(15)

       

             图(16)


        2、repeat语句
        (1)、repeat语句基本的结构:

    repeat...until...end repeat;

         (2)、示例

    需求:给test1表中的id字段插入数据,从1到10

    create procedure test10()
    begin
        declare i int default 0;
        repeat 
        begin 
            select i;
            set i=i+1;
            insert into test1(id) values(i);
        end;
        until i>=10 -- 如果i>=10,则跳出循环
        end repeat;
    end;

       调用及运行结果结果如图(17)和图(18)

       

        图(17)             

       

                  图(18)
            
        概括:
            until判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式,只有当until语句为真是,循环结束。
            

    九、存储过程游标的使用


        1、什么是游标
            游标是保存查询结果的临时区域
        2、示例
        需求:编写存储过程,使用游标,把users表中 id为偶数的记录逐一更新用户名
        

    create procedure test11()
        begin
            declare stopflag int default 0;
            declare username VARCHAR(32);
            -- 创建一个游标变量,declare 变量名 cursor ...
            declare username_cur cursor for select name from users where id%2=0;
            -- 游标是保存查询结果的临时区域
            -- 游标变量username_cur保存了查询的临时结果,实际上就是结果集
            -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾,将变量stopflag设置为1,用于循环中判断是否结束
            declare continue handler for not found set stopflag=1;
    
            open username_cur; -- 打卡游标
            fetch username_cur into username; -- 游标向前走一步,取出一条记录放到变量username中
            while(stopflag=0) do -- 如果游标还没有结尾,就继续
                begin 
                    -- 在用户名前门拼接 '_cur' 字符串
                    update users set name=CONCAT(username,'_cur') where name=username;
                    fetch username_cur into username;
                end;
            end while; -- 结束循环
            close username_cur; -- 关闭游标
        end;

    调用结果如图(19):

                                         图(19)


    十、自定义函数


        函数与存储过程最大的区别是函数必须有返回值,否则会报错
        
        1、一个简单的函数

    create function getusername(userid int) returns varchar(32)
        reads sql data  -- 从数据库中读取数据,但不修改数据
        begin
            declare username varchar(32) default '';
            select name into username from users where id=userid;
            return username;
        end;

        
        调用及运行结果如图(20):

                    图(20)
        
        概括:
        1.创建函数使用create function 函数名(参数) returns 返回类型;
        2.函数体放在begin和end之间;
        3.returns指定函数的返回值;
        4.函数调用使用select getusername()。
        
        2、示例
        需求:根据userid,获取accoutid,id,name组合成UUID作为用户的唯一标识

      create function getuuid(userid int) returns varchar(64)
        reads sql data  -- 从数据库中读取数据,但不修改数据
        begin
            declare uuid varchar(64) default '';
            select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;
            return uuid;
        end;

    调用及运行结果如图(21)

                 图(21)

       
    十一、触发器


        1、什么是触发器


        触发器与函数、存储过程一样,触发器是一种对象,它能根据对表的操作时间,触发一些动作,这些动作可以是insert,update,delete等修改操作。

        2、示例1

    (1)、需求:出于审计目的,当有人往表users插入一条记录时,把插入的userid,username,插入动作和操作时间记录下来。
        

    create trigger tr_users_insert after insert on users
        for each row 
        begin 
            insert into oplog(userid,username,action,optime)
            values(NEW.id,NEW.name,'insert',now());
        end;


        创建成功后,给uses表中插入一条记录:
        

    insert into users(id,name,age,status,score,accontid)
        values(6,'小周',23,1,'60','10001');

      执行成功后,打开oplog表,可以看到oplog表中插入了一条记录如图(22)
        

                                                         图(22)
      

      (2)、总结

            1、创建触发器使用create trigger 触发器名
            2、什么时候触发?after insert on users,除了after还有before,是在对表操作之前(before)或者之后(after)触发动作的。
            3、对什么操作事件触发? after insert on users,操作事件包括insert,update,delete等修改操作;
            4、对什么表触发? after insert on users
            5、影响的范围?for each row
     

    3、示例2


        需求:出于审计目的,当删除users表时,记录删除前该记录的主要字段值
        

    create trigger tr_users_delete before delete on users
        for each row 
        begin 
            insert into oplog(userid,username,action,optime)
            values(OLD.id,OLD.name,'delete',now());
        end;


        删除users表中的一条记录
        

    delete from users where id=6;

        执行成功后,打开oplog表,可以看到oplog表中插入了一条记录如图(23)

                                                  图(23)


             
    十二、流程控制

     1、case分支

       (1)、基本语法结构

    case ...
    when ... then....
    when.... then....
    else ... 
    end case;

    (2)、示例

    users表中,根据userid获取status值,如果status为1,则修改score为10;如果status为2,则修改为20,如果status3,则修改为30;否则修改为40。
      

     create procedure testcate(userid int)
        begin 
            declare my_status int default 0;
            select status into my_status from users where id=userid;
    
            case my_status
                when 1 then update users set score=10 where id=userid;
                when 2 then update users set score=20 where id=userid;
                when 3 then update users set score=30 where id=userid;
                else update users set score=40 where id=userid;
            end case;
        end;


        调用过程 call testcate(1); ,执行结果如图(24);

                                 图(24)

     

    十四、存储过程+event(事件)

     

         1、使用存储过程+事件事件一个简单的实现福彩3D开奖
            
            需求:设计一个福彩的开奖过程,没3分钟开奖一次
                第一步:先编写一个存储过程open_lottery,产生3个随机数,生成一条开奖记录
                第二步:编写一个时间调度器,每3分钟调用一次这个过程
              

    create procedure open_lottery()
            begin 
                insert into lottery(num1,num2,num3,ctime)
                select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now();
            end;

     

    create event if not exists lottery_event -- 创建一个事件
            on schedule every  3 minute  -- on schedule 什么时候来执行,没三分钟执行一次
            on completion preserve 
            do call open_lottery;


            运行结果如图(25)


            

                                                             图(25)

    注意,如果event之一没有运行,请按照以下办法解决:

    (1)、 show variables like '%event_scheduler%';
            set global event_scheduler=on;

    (2)、 alert event lottery_event enable;


        2、解析event的创建格式
        (1)、基本语法

    create event[IF NOT EXISTS]event_name -- 创建使用create event
        ON SCHEDULE schedule -- on schedule 什么时候来执行
        [ON COMPLETION [NOT] PRESERVE] -- 调度计划执行完成后是否还保留
        [ENABLE | DISABLE] -- 是否开启事件,默认开启
        [COMMENT 'comment'] -- 事件的注释
        DO sql_statement; -- 这个调度计划要做什么?

    (2)、执行时间说明

        1.单次计划任务示例
            在2019年2月1日4点执行一次

            on schedule at    '2019-02-01 04:00:00'
             
        2. 重复计划执行
            on schedule every 1 second 每秒执行一次
            on schedule every 1 minute 每分钟执行一次
            on schedule every 1 day 没天执行一次
            
        3.指定时间范围的重复计划任务
            每天在20:00:00执行一次
            on schedule every 1 day starts '2019-02-01 20:00:00'
            

    十五、本文所用到的表

     

    1、lottery表

    2、oplog表

    3、orders表

    4、test1表

    5、user表

         

    展开全文
  • 《MySQL 入门教程》第 31 篇 存储过程(一)

    千次阅读 多人点赞 2020-09-27 12:33:55
    MySQL 存储过程(Stored procedure)和存储函数(Stored function)是一种存储在数据库中的程序,可以包含多个 SQL 语句,提供许多过程语言的功能,例如变量定义、条件语句、循环语句、游标以及异常处理等。...
  • Mysql存储过程调试工具

    热门讨论 2012-10-22 13:40:29
    Mysql存储过程调试工具,可以方便的调试MYSQL的存储过程
  • 开始之前 首先创建如下两张表,并初始化一些数据。 创建存储过程(CREATE PROCEDURE) ...基本语法格式如下: ...CREATE PROCEDURE sp_name (parameters) ...sp_name为存储过程的名称(唯一性,没有所谓的重...
  • SpringBoot调用存储过程

    千次阅读 2020-09-01 20:39:23
    程序中需要直接调用存储过程的场景 第一种:返回带参数的存储过程 第二种:返回结果集 第三种:第一种+第二种(不讨论) 数据库环境准备 数据库:MySQL 创建表及数据 CREATE TABLE `order` ( `uuid` varchar(32...
  • 数据库存储过程简单介绍

    千次阅读 2019-03-05 15:37:35
    存储过程 存储过程就是一条或多条SQL语句的集合 当对数据库进行一系列的操作时 存储过程就可以将这些复杂的操作封装成一个代码块 以便重复使用 大大减少了数据库开发人员的工作量 创建存储过程 创建存储过程的基本...
  • MySQL数据库存储过程讲解与实例

    万次阅读 多人点赞 2018-06-03 00:48:17
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • MySQL——存储过程详解及实例分析

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

    千次阅读 2018-12-18 23:49:23
    MySQL中的存储过程 首先来看两个问题: 1.什么是存储过程存储过程(Stored Procedure)是在数据库系统中,一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数...
  • MySQL存储过程和存储函数(看不懂你打我系列)

    千次阅读 多人点赞 2020-05-24 08:20:25
    MySQL存储过程和存储函数 MySQL中提供存储过程与存储函数机制,我们先将其统称为存储程序,一般的SQL语句需要先编译然后执行,存储程序是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,当用户通过指定...
  • hive实现存储过程

    千次阅读 2020-04-24 16:38:03
    1、hive存储过程简介 1.x版本的hive中没有提供类似存储过程的功能,使用Hive做数据开发时候,一般是将一段一段的HQL语句封装在Shell或者其他脚本中,然后以命令行的方式调用,完成一个业务或者一张报表的统计分析。...
  • 系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管...
  • 1、定义所谓存储过程(Stored Procedure),就是一组用于完成特定数据库功能的SQL语句集,该SQL语句集经过编译后存储在数据库系统中。在使用时候,用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用...
  • 1.定义一个存储过程(不带参数) 使用sql语句定义一个存储过程挺简单的,参考这一篇博文https://blog.csdn.net/qq_44973159/article/details/91491985 create procedure ccgc as select * from t_student 而使用...
  • MySQL存储过程异常处理

    千次阅读 2018-12-27 13:53:44
    MySQL 存储过程异常处理 在使用MySQL存储过程时,经常会遇到一些问题,导致实际的结果并不是按照代码逻辑得到的,此时需要将存储过程中的SQL语句错误,捕捉并打印出来 需要知道的概念 condition hanlder ...
  • SQL Server数据库存储过程——以自定义存储过程为例 1、什么是数据库存储过程 存储过程就是预编译SQL语句的集合,这些语句存储在一个名称下并作为一个单元处理。存储过程代替了传统的逐条执行SQL语句的方式 SQL...
  • oracle存储过程中if语句

    万次阅读 2019-07-10 09:18:43
    1.完整的存储过程如下: 这个存储过程内容可能有点多,这是我平时的项目用到的存储过程,刚好里面用到了if语句,所以我就拿来了给大家看一下 create or replace procedure p_censor_stat_status_org_real(r_codes ...
  • 存储过程详解

    千次阅读 2017-05-01 09:53:54
    什么是存储过程存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候...
  • 面试题之存储过程

    千次阅读 2020-02-06 10:27:00
    一、什么是存储过程 简单来说,存储过程就是一组预先编译好的sql语句集。 二、创建语法 create procedure 存储过程名(参数列表) begin //....存储过程体 end; 参数列表主要有三种形式: IN : 需要调用者传入 ...
  • mybatis使用存储过程

    千次阅读 2018-11-04 21:31:36
    在讲解mybatis的存储过程调用之前谈谈存储过程的优劣: 优点 1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度...
  • Oracle/PLSQL存储过程详解

    万次阅读 多人点赞 2018-03-14 17:31:55
    如果新建毫无反应直接文件-新建-程序窗口-空白,新建一个程序窗口: 存储过程创建语法: create [or replace] procedure 存储过程名(param1 in type,param2 out type) as 变量1 类型(值范围); 变量2 类型(值...
  • 本文是存储过程,通过书本和网上的参考代码和讲解,大致把存储过程理解为一个自定义函数,在多次重复使用某一个功能的时候,可以把这个功能写成一个自定义函数(存储过程),这样使代码可读性增加,逼格也更高...... ...
  • Mysql修改存储过程相关权限问题

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

    千次阅读 多人点赞 2019-08-22 20:15:36
    这是一篇学习路上的(伪)详解,为了能灵活使用存储过程

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,395,437
精华内容 958,174
关键字:

存储过程