精华内容
下载资源
问答
  • 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-01-23 13:35:44
    Mysql中的数据用各种不同的技术存储在文件(或者内存)中, 每一种技术都使用不同的存储机制, 索引技巧 锁定水平并且最 终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外 的速度或者功能从而...

    什么是存储引擎:

    Mysql中的数据用各种不同的技术存储在文件(或者内存)中, 每一种技术都使用不同的存储机制,
    索引技巧 锁定水平并且最 终提供广泛的不同的功能和能力。通过选择不同的额技术你能过够获得额外 的速度或者功能从而改 善你的应用整体功能。不同的存储引擎性能是不一的你能够获得额外的速度或者功能,从而改善你的应用的整体功能。查看Mysql可用的引擎可以用:show engines查询出来后边有 yes 的 是支持的 no 是不支持的。

    2.Mysql 的常见引擎 :

    1.MEMORY:

    memory使用 存在内存中的内容来创建表,每个MEMORY表实际对应一个磁盘文件,格式是.frm(就是表里的结构)。memory类型的表访问速度非常快,因为它的数据放在内存中 并且默认使用 HASH索引,但是一旦服务器关闭 表中的数据就会丢失, 但是表还会继续存在。​

    2.MylSAM:

    拥有较高的插入(如 insert into 新增),查询速度,但不支持事物,也不支持外键。 每个MylSAM在磁盘上存放3个文件,其中文件名和表名相同,但是扩展名 分别为 .frm 存储表的一个定义 . MYD:存储真是的数据 .MYI:存储索引 MyISAM适合: (1)做很多count 的计算; (2)插入不频繁,查询非常频繁; (3)没有事务。

    3.InnoDB:

    5.5版本后Mysql的默认数据库,事物型数据库的首选引擎,支持ACID事物,支持行级锁定 innoDB写的处理效率差,他会占用空间来保存数据和索引。 InnoDB适合: (1)可靠性要求比较高,或者要求事务; (2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定数据引擎的创建 让所有的灵活性成为可能的开关是提供给ANSI SQL的MySQL扩展——TYPE参数。 MySQL能够让你在表格这一层指定数据库引擎,所以它们有时候也指的是table formats。 下面的示例代码表明了如何创建分别使用MyISAM、ISAM和HEAP引擎的表格。 下面的代码显示了如何使用ALTER TABLE把MyISAM表格移动到InnoDB的引擎: 代码:ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB 3. Memory:所有数据置于内存的存储引擎,拥有的极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 Mysql 重新启动时丢失。 4.Merge:将一定数量的 MyISAM 表联合而成一个整体,在超大规模数据存储时很有用。 5.Archive:非常适合存储大量的独立的,作为历史纪录的数据。因为它们不经常被读取。 Archive 拥有高效的插入速度,但其对查询的支持相对较差。 6.CSV:逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .CSV文件这是一个普通的文本文件,每个数据行占用一个文本行。CSV存储引擎不支持索引。7.BlackHole:黑洞引擎,写入的任何数据都会消失,一般用于记录 binlog 做复制的中继。

    ACID:

    A 事务的原子性(Atomicity):指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行 了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱 却没出来.这两步必须同时完成.要么就不完成. C 事务的一致性(Consistency):指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了 a+b=10,一个事务改变了a, 那么b也应该随之改变. I 独立性(Isolation):事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态. 因为这样可能会导致数据不一致. D 持久性(Durability):事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久 的保存在数据库之中, 不会无缘无故的回滚.

    Innodb介绍:

    innoDB是一种可靠性高的高性能 Mysql 数据库存储引擎,它有如下优点:​
    1.它遵循ACID模式设计,具有与事物(Transactions),回滚和保护用户数据的崩溃恢复能力。 2.提供行锁。 3.InnoDB表基于主键在磁盘上安排数据,有优化的常见查询功能。每一个InnoDB表都有一个叫做 聚集索引的主键索引,能尽可能减少数据查询次数。 4.为了维护数据完整性,InnoDB还支持外键完整性约束。 5.可以与其它MySQL存储引擎混合使用InnoDB表。列如,能用一个结合操作在一个单独查询中综合源自 InnoDB和InnoDB表的数据 6.InnoDB是针对提高 CPU效率而设计的,并且在处理大数据时表现最佳。MyISAM和InnoDB的区别;

    什么是存储过程:

    一组可编程的函数,是为完成 特定 功能的SQL语句 存储 过程是具有名字的代码段 ,来完成一些特定的功能。 创建的存储过程保存在数据库的字典中。

    为什么用存储过程:

    将重复性高的一些操作,封装到一个存储过程中,简化对Sql的调用。 批量处理 统一接口, 确保数据的安全 相对与oracle数据来说,Mysql的存储过程功能较弱 使用较少

    存储过程的创建 和调用:

    delimiter $ $: 他与存储过程无关 delimiter 语句将标准分割符–分号(;)更改 $ $ , 因为我们想将存储过程 作为一个整体传递给服务器,而不是让mysql 工具一次解析每个语句,告诉mysql解析器 ,该段命令是否已经结束了,mysql是否可以执行了,默认情况下delimiter是分号;。在命令行客户端中,如果有一行以分号结束,那么回车 后执行 改命令,但是有的时候,不希望mysql这么做。在为可能输入较多的语句,且 语句包含有分号,使用delimiterr$ $ 这样只有$ $出现之后mysql解析器解析执行这段语句

    案例:

    创建存储过程:

    delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
     CREATE PROCEDURE user_tt()  -- 给存储过程取一个名字
    BEGIN -- 开始
    SELECT * from t_user; --sql语句执行 注意sql 语句结束有分号
    END $$  --结束  注意 符号 和定义开始一致
    

    在这里插入图片描述
    调用存储过程:

    delimiter;-- 把分号改回来
    
    CALL user_tt;
    

    在这里插入图片描述

    查看存储过程:
    查看所有的存储过程:SHOW PROCEDURE STATUS;
    在这里插入图片描述

    ##查看指定数据库的存储过程:SHOW PROCEDURE STATUS WHERE db=“存储过程名”;

    在这里插入图片描述
    查看 指定存储过程源代码: SHOW CREATE PROCEDURE + 存储过程名

    在这里插入图片描述
    删除存储过程 :drop PROCEDURE +存储过程名;

    在这里插入图片描述
    存储过程变量:

    delimiter $$  -- 定义好开始  符号 可以 为$$ 或者//
     CREATE PROCEDURE text_user()  -- 给存储过程取一个名字
    BEGIN -- 开始
    
      -- 声明变量  关键 字declare   `DEFAULT ''  默认值 空字符串
    
    
    
    DECLARE  jie  VARCHAR(255) default ''; 
    
    DECLARE   x,y INT   default 0;
    
    
    SELECT * from t_user; -- sql语句执行 注意sql 语句结束有分号
    
    
    END $$  -- 结束  注意 符号 和定义开始一致
    
    

    在这里插入图片描述
    给变量赋值:
    在这里插入图片描述
    变量的范围:如果在存储过程中声明变量,那么当达到存储过程的 END 语句时 它超出了范围。因此在其他代码块无法 访问

    存储过程参数:

    三种类型:

                                   in  输入类型
                                    
                                    --  根据传入的名称, 获取对应的信息、
    delimiter $$
    CREATE PROCEDURE getName(in name VARCHAR(225))-- 传入参数  in 关键字  VARCHAR类型
    BEGIN 
    
     SELECT * from t_user where  user_code =name; -- 给要查的 赋值
    
    
    END $$
    
    delimiter;
    

    在这里插入图片描述

            out类型的 输出
    
    
    
    delimiter $$
    CREATE PROCEDURE getMima(in name VARCHAR(225),out mima int )-- 传入参数 out  关键字  输出数据
    BEGIN 
    
     SELECT  user_pass into mima  from t_user where  user_code = name; -- 给要查的 user_pass into mima  赋值  name 
    
    
    END $$
    
    
    
    查询结果
    CALL  getMima('鲁班',@m);  -- @m 就时out 的参数  首先执行这句话
    SELECT @m; -- 这里的@m 就是上面所定义的 在这可以直接输出 这只是简写
    
    SELECT @m  from  DUAL 这个是mysql 数据库 提供的一张假表  可以加上参数调用
    
    

    在这里插入图片描述

     inout 类型是又输入又是输出
      delimiter $$
    CREATE PROCEDURE test1808(inout num  int ,in abc  int  )-- 传入参数 out  关键字  输出数据
    BEGIN 
    
    
    set num = num + abc;
     
    END $$
    
    delimiter;
    
    查询结果
    set @num1=220;
    CALL test1808(@num1,10);
    SELECT @num1;
    

    在这里插入图片描述

    存储过程语句:

    if语句: IF expression THEN
    statements;
    END IF;
    IF expression THEN
    statements;
    ELSE
    else-statements;
    END IF;
    CASE语句:
    CASE case_expression
    WHEN when_expression_2 THEN commands …
    ELSE commands
    END CASE;
    循环语句:
    WHILE expression DO
    statements
    END WHILE
    2)REPEAT
    stalemeents;
    UNTIL expression
    END REPEAT

    自定义函数

    案例:

    
      定义 一个自定义函数  在定义函数 和存储过程有区别  定义函数function
    delimiter $$
    CREATE  FUNCTION rand_str(n int) returns  varchar(225)
    BEGIN
    -- 生明一个str 52字母
    DECLARE str VARCHAR(100)  DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    -- 记录 当前是第几个
    DECLARE i INT DEFAULT 0;
    -- 生成的结果
    DECLARE  res_str  VARCHAR(255)   DEFAULT '';
    -- 循环  
    WHILE i < n DO
    -- 随机生成一个指定的字符串 b bc bce 
    --   1+ RAND()*52  floor(1+rand ()*52);
    --  SUBSTR(str FROM (1+rand()*52),1)
    set res_str=CONCAT(res_str,SUBSTR(str,FLOOR(1+RAND()*52),1));
    SET i= i+1;
    END WHILE;
    RETURN res_str;
    END $$
    delimiter ;
    
    
    结果
    SELECT rand_str(5);
    

    在这里插入图片描述

    案例批量新增:

    这里调用了以上的随机数 方法 函数
    
    delimiter $$
    CREATE  PROCEDURE insert_emp10(in  startNum int, in  max_num int) 
                            --  startNum 从几开始
                            --   max_num 要插入多少条数据
    begin 
    -- 声明 一个变量叫记录当前是第几条数据
     DECLARE  i INT default 0;
    -- 默认情况是自动 提交数据
      set autocommit = 0; -- 不让它自动 提交
    repeat 
    set i= i+1; -- 每循环一次 让i+1
    -- 插入数据
    INSERT into  emp VALUES (startNum+i,rand_str(5),FLOOR(10+RAND()*30));
                                 --  startNum代表是id ,+i是每次循环+1
                                 -- rand_str(5) 调用 数据 数 对name 每一次循环
                                   --   都去生成一个5个字母的名字
                                   --  FLOOR(10+RAND()*30) 年龄 也是随机的 +10
                                   --  是应为随机数是从0开始 *30 是到30岁
    until i =max_num  -- 当i=max_num 就让它退出循环
    
    end  repeat;
    commit;  -- 整体提交所有数据  提高效率 
    end $$
    
    delimiter ;
    

    结果:
    在这里插入图片描述

    展开全文
  • 存储过程

    千次阅读 2019-12-11 22:24:16
    存储过程(Stored Procedure)是大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数...

    存储过程

    什么是存储过程

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

    存储过程写法

    CREATE PROCEDURE Procedure_Name  
     
    	--Procedure_Name为存储过程名(不能以阿拉伯数字开头),在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
    	 
    	@Param1 Datatype,@Param2 Datatype 
    	
    	--@Param1和@Param2为存储过程的参数,Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。
    	
    AS --存储过程要执行的操作 
     
    BEGIN
    	
    	--BEGIN跟END组成一个代码块,可以写也可以不写,如果存储过程中执行的SQL语句比较复杂,用BEGIN和END会让代码更加整齐,更容易理解。
     	
    END
    GO --GO就代表结操作完毕  
     
    exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
    drop procedure Procedure_Name --删除存储过程Procedure_Name,不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
    show procedure status --显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
    show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
    exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本
    

    存储过程有点

    (1)执行速度快——存储过程只在创建时进行编译,以后每次执行存储过程都不需要重新编译,而一般SQL语句没执行一次就需编译一次,所以使用存储过程可提高数据库的执行速度。

    (2)减少网络通信量——当对数据库进行复杂操作时,(如对多个表进行insert、update、select、delete时)可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作,如果用程序完成就是多条SQL语句,可能要多次连接数据库,而换成存储过程只需一次连接。

    (3)更强的适应性与复用性——存储过程可以重复使用,提高了可重用性,减少数据库开发人员的工作量。

    (4)可维护性高——更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

    (5)安全性高,可设定只有某用户才能对指定存储过程的使用权,且存储过程比多条sql稳定,只要数据库不出现问题,基本上是不会出现什么问题的。

    (6)分布式工作——应用程序和数据库的编码工作可以单独进行,减少耦合度。

    (7)更好的版本控制,通过svn等源代码控制工具可以轻松恢复或引用旧版本的存储过程。

    存储过程的缺点

    (1)开发调试差:无良好的IDE开发工具,存储过程的调试比一般SQL要复杂的多。

    (2)可移植性差:由于存储过程将应用程序绑定到数据库上,因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

    (3)重新编译问题,因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。

    (4)如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦

    因时间关系,暂时写到这里,下回更新存储过程的实例语句

    展开全文
  • MYSQL存储过程调试过程

    万次阅读 2018-07-14 11:57:00
    mysql不像oracle有plsqldevelper工具用来调试存储过程,所以有几种简单的方式追踪...2、直接在存储过程中,增加select xxx,控制台查看结果: 3、把存储过程的sql复制到navicat查询中,然后SET @num使用自定义变量。
     mysql不像oracle有plsqldevelper工具用来调试存储过程,所以有几种简单的方式追踪执行过程:
    1、用一张临时表,记录调试过程;
    2、直接在存储过程中,增加select xxx,在控制台查看结果:
    3、把存储过程的sql复制到navicat查询中,然后SET @num使用自定义变量。
     
    实际应用场景是三种方法都会用到,结合不同的场景使用。
    方法1适用于有多条数据处理,处理结果无法通过变量来存储。
    方法2适用于一般的存储过程,查看结果;
    方法3适用于,需要一步步调优存储过程。执行每一步存储过程sql语句。但是存储过程无法断点测试,所以可以用这个方法来处理。
    ***************************************************************************

    作者:小虚竹
    欢迎任何形式的转载,但请务必注明出处。
    限于本人水平,如果文章和代码有表述不当之处,还请不吝赐教。

     

    我不是个伟大的程序员,我只是个有着一些优秀习惯的好程序员而己

    展开全文
  • SQL存储过程使用介绍

    万次阅读 多人点赞 2017-04-25 13:54:13
    数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;1. 存储过程的概念存储过程 (Stored Procedure) 是大型数据库系统中 , 一组...
  • 存储过程调用存储过程

    千次阅读 2018-03-12 23:58:24
    存储过程引用存储过程在存储过程B中,调用存储过程A。当存储过程A中RETURN时,跳出A过程。但是B过程引用处继续往下执行。CREATE OR REPLACE PROCEDURE A AS BEGIN dbms_output.put_line('调用'); return; dbms...
  • mysql存储过程学习笔记

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

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

    千次阅读 2016-06-13 17:27:02
    在存储过程中调用存储过程
  • Oracle存储过程及举例(几种参数情况的存储过程

    万次阅读 多人点赞 2017-05-06 22:22:50
    Oracle存储过程及举例(几种参数情况的存储过程
  • mysql手册05_存储过程和存储函数

    万次阅读 2020-08-14 21:34:41
    存储过程和存储函数是存储数据库中的一段SQL语句集合。 通过调用存储过程或存储函数,可以简化开发,减少数据数据库和服务器间的传输。 存储过程和存储函数的区别是:存储过程没有返回值。 基本语句: 注:...
  • postgreSQL 存储过程存储过程调用

    千次阅读 2020-10-27 11:11:30
    pgadmin 上执行下面语句,新建存储过程,返回adw.trn_node_variables 表的 count 数,adw 是 shcema 名 create or replace function adw.count() returns integer as $BODY$ declare counts integer; begin ...
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    存储过程(Stored Procedure)是一种数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存数据库的数据字典中 优点: 存储...
  • sql 存储过程

    千次阅读 2018-11-11 21:16:54
    存储过程是存放数据库服务器上的预先定义与编译好的T-SQL语句集合,是一个独立的数据库对象。 2.特点 实现了模块化编程。 存储过程具有对数据库立即访问的功能。 使用存储过程可以加快程序的运行速度。 使用存储...
  • 作为测试存储过程 create or replace procedure pri(a in number default 1,b in number default 1) as begin dbms_output.put_line(‘a:’||a||’—-b:’||b); end pri;查询存储过程信息select * from user_...
  • DB2存储过程

    千次阅读 2018-05-12 16:21:59
    存储过程可以提高程序性能,存储过程创建及运行数据库服务器上,可以减少数据库存取流量。并且可以帮助集中处理业务逻辑,如果对存储过程进行更改,则使用该存储过程的所有客户机应用都立即可以应用这些更改。存储...
  • Oracle存储过程

    千次阅读 多人点赞 2018-08-18 13:50:17
    经编译后存储数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 --存储过程是数据库中的一个重要对象, --任何一个设计良好的数据库应用程序都应该用到存储过程。 --存储...
  • 不同数据库的存储过程有不同的格式,数据类型。这里通过一个例子展示将ORACLE存储转化为MYSQL存储过程的一些必须修改的地方。 ORACLE存储过程 修改后的MYSQL存储过程 两者比较: 语法格式上的不同与修改: 1、...
  • 存储过程就是预编译SQL语句的集合,这些语句存储一个名称下并作为一个单元处理。存储过程代替了传统的逐条执行SQL语句的方式 SQL server提供了三种类型存储过程: 系统存储过程:用户管理SQL server和显示有关...
  • 数据库存储过程

    千次阅读 2019-02-18 14:44:00
     存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,经编译后存储数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 用来执行管理任务或应用复杂的业务规则。...
  • PROCEDURE "Test"--创建Test存储过程 is start_time date;--创建了两个变量,开始时间和结束时间 end_time date; begin start_time := sysdate;--执行开始时间,下面Test1()到Test7()全部都是存储过程,这样调用没...
  • 学习存储过程

    千次阅读 2018-11-12 16:48:08
    存储过程创建后可以程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。 B、 存储...
  • oracle存储过程----入门存储过程

    千次阅读 热门讨论 2018-08-09 08:21:08
      从今天开始,决定先学会写存储过程吧,总是被人问起这个。   首先,我决定以学会oracle 的存储过程为目标了。 存储过程的优点,以下是我百度...我们都知道SQL是先编译再执行的,而存储过程是预编译服务...
  • 创建存储过程,执行存储过程

    千次阅读 2018-07-27 17:18:07
    无参数存储过程 create or replace procedure 存储过程的名称 is begin  xxxx......此处为自己需要执行的sql语句 commit; end 存储过程的名称;   含有入参的存储过程   create or replace procedure ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,350,063
精华内容 940,025
关键字:

存储过程在哪儿