精华内容
下载资源
问答
  • 当然有些程序中无度不区分应用场景使用存储过程和函数,造成性能问题,的另说, 但不能将其归罪与存储过程和函数本身,终究是使用的那些人的水平才应该是被....... 另外最近QQ群满了,是在抱歉不能添加,这边会想办法处理...

    年龄到底是不是一个问题,35岁是一个坎, 招聘中有一些门槛这不奇怪,奇怪的是那这些信息去贩卖的人, 让整体的中国中年人活在焦虑和不安中,这绝对不是一个好人应该做的事情, 小范围的事实是可以控制的, 而肆无忌惮的"大喇叭"

    ,甚至歪曲部分内容,到底要利用这些信息吸引流量,造成全社会的恐惧, 你心安吗? 

    年龄到底是不是一个问题,其实看个体,个人秉着不学点什么就难受的精神并未受到那些歪曲事实的信息的影响, 有开始了新的一个星期的文字.

     DB层关于语言的能力其实是在下降的,尤其在使用了NOSQL, 以及MYSQL 后,数据库本身的语言SQL 的能力变得不再重要,或不再需要,这也就导致,ORALCE SQL SERVER 的先辈们的那些过程语言被抛弃. POSTGRESQL 本身也是有强大PL/PGSQL的, 有些环节和场景, 写好PL/PGSQL也是一种能力.

    正文  本系列也是根据一本POSTGRESQL SERVER Programming的书而来的,其中可能有一些个人的扩展,和例子.

    ___________________________________________________________________________

    The simplest and least powerful way of looking at server programming is to view the database as a data bucket. 

    这段的第一句,在服务器程序领域将数据库看成一个数据容器是最简单和无技术含量的.

    上面这段就不在解释了,有点含沙射影某数据库,从某种角度来看他说的不无道理,如果一次能批处理数据,但还要一行行的通过主键来处理,的确是drawbacks.

    此书的作者是一个有着27年工作经验的人士,上这段话可以看出作者还是有两把刷子的. 其中提到, 以客户为中心的程序开发理论中总是将数据库与存储过程进行隔离, 其设计的目标通常是源于让应用程序来控制整个业务的逻辑,应用开发非常重要,而数据库则就是一个必须存在的"非必须".

    这样的观点就类似你有一台奔驰AMG45 ,但你却那他当一辆奔驰C200开.数据库被大材小用了. 

    所以到此为止,上面的一段解释了为什么要学习PL/PGSQL 以及部分继续使用存储过程人的一些观点. 

    画了一张图,来表达作者想要莫奥数的两种应用程序架构不同的设计思路和想法

    角度1 我们可以理解为, NOSQL, MYSQL ,

    角度2  我们可以理解为 ORACLE  SQL SERVER POSTGERSQL


    个人认为传统数据库和NOSQL NEWSQL 在应用程序的角度的不同点,这里是算是一个重点. 所以你没有听过 MYSQL SQL. 因为只要是MYSQL一定是和高并发,小事务有关的, 存储过程在MYSQL的应用场景是无法存活的.

    下面就从POSTGRESQL 的函数开始, 四个部分 建立一个PG函数, 传递参数, 如何在内部控制函数, 从函数内部返回一个结果.

    CREATE FUNCTION mid(varchar, integer, integer) RETURNS varchar

    AS $$

    BEGIN

    RETURN substring($1,$2,$3);

    END;

    $$

    LANGUAGE plpgsql;

    SELECT public.mid('Kirk L. Roybal',9,1);

    上面的函数定义了, 传入的参数的数据类型,以及返回数据的类型,通过$$来界定存储过程$$ 并且在最下面标注上的函数的语言是plpgsql. 通过return 来讲结果返回.

    这里多说一句POSTGRESQL 的函数和存储过程,是可以通过其他语言来进行撰写, C ,PYTHON 等都是可以.

    下面以一个简单的例子来结束今天的文字

    下面需要一个函数,在程序中调用,输入一个值,对输入的值的原值,原值基础上增加100, 原值与随机值的乘积,以及取输入值中最大的整数值,四舍五入进行插入.如果这个使用程序编写和调用函数比较, 相对的还是调用函数比较简单.

    create  table  camulate (id SERIAL primary key,

    base_number numeric,

    base_100 numeric,

    round numeric,

    ceil_s numeric)

    CREATE or Replace FUNCTION insert_s(numeric) RETURNS varchar

    AS $$

    BEGIN

    insert into camulate (base_number,base_100,round,ceil_s) values ($1,$1+100,random()*$1,ceiling($1));

    RETURN $1;

    END;

    $$

    LANGUAGE plpgsql;

    select insert_s(1);

    select insert_s(1.4444);

    select insert_s(2.7039);

       另外可能还牵扯一个灵活性的问题,尤其函数,如果相关的功能变化,无论是JAVA 还是.NET  都需要重新修改程序和打包,部署.  如果采用了函数的方式,修改和更新都要较程序简单. 当然有些程序中无度不区分应用场景使用存储过程和函数,造成性能问题,的另说, 但不能将其归罪与存储过程和函数本身,终究是使用的那些人的水平才应该是被.......

       

       

    另外最近QQ群满了,是在抱歉不能添加,这边会想办法处理,会开始清理一些僵尸同学,请见谅.

    展开全文
  • 存储过程4.1存储过程介绍4.2简单的存储过程入门4.3:=与=的区别4.4存储过程if用法4.5存储过程循环while….do用法4.6存储过程循环repeat用法4.7存储过程循环loop用法4.8存储过程参数in out4.9存储过程参数inout4.10...

    已经过时了,写在这里给需要的朋友,入门使用

    1.创建数据库、创建表、测试数据

    创建数据库

    在这里插入图片描述

    创建表:

    在这里插入图片描述

    这儿没有使用外键

    #班级表
    CREATE TABLE xmcc_class(
    c_id INT PRIMARY KEY  AUTO_INCREMENT COMMENT '班级编号',
    c_name VARCHAR(10) NOT NULL COMMENT '班级名称'
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    #学生表
    CREATE TABLE xmcc_student(
    s_id INT PRIMARY KEY  AUTO_INCREMENT COMMENT '学生学号',
    s_name VARCHAR(10) NOT NULL COMMENT '学生姓名',
    s_phone VARCHAR(11) NOT NULL UNIQUE COMMENT '学生电话',
    s_cid INT COMMENT '班级id'
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    

    测试数据

    在这里插入图片描述

    #测试数据
    INSERT INTO xmcc_class(c_name) VALUES('java'),('.net'),('php'),('python')
    #三个java班 一个.net 班 2个php 没有python
    INSERT INTO xmcc_student(s_name,s_phone,s_cid) VALUES
    ('小a','11111111111',1),
    ('小b','22222222222',1),
    ('小c','33333333333',1),
    ('小d','44444444444',2),
    ('小e','55555555555',3),
    ('小f','66666666666',3)
    

    2.视图

    1.什么是视图

    通俗的讲,视图就是一条SELECT语句执行后返回的结果集。就是为了隐藏一部分数据或者减少联表查询而创建的一张虚拟表

    2.视图的特性

    视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变);

    可以跟基本表一样,进行增删改查操作(ps:增删改操作有条件限制);

    3.视图的作用

    方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;

    更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别;

    4.使用场合

    权限控制的时候,不希望用户访问表中某些含敏感信息的列,比如salary…

    关键信息来源于多个复杂关联表,可以创建视图提取我们需要的信息,简化操作;

    以例子来学习,首先看看语句:

    创建视图:

    CREATE VIEW 视图名(列1,列2…)

    AS SELECT (列1,列2…)

    FROM 表名;

    删除视图:

    Drop view if exists 视图名

    一般不太会去修改视图

    2.1隐藏字段

    场景:现在项目中有一个学生推荐系统,需要避免企业得知学生的联系方式,不然学生直接就跟企业联系了

    分析:这样就不能把学生表直接给学生推荐系统,可以创建视图隐藏学生的电话字段

    在这里插入图片描述

    2.2将表的关联数据,存储于视图中简化查询

    场景:实际中有很多查询,需要对多张表联合操作,有一定的复杂度,完成需要一定的sql能力,可以通过视图简化查询的难度

    这儿的查询比较简单,但是也能说明问题,比如查询学生的信息及所在班级名字,需要联表查询,就可以创建视图来简化以后的查询

    在这里插入图片描述

    大家只需要理解、记住这两个优点就可以了

    2.3缺点

    性能:从数据库视图查询数据可能会很慢,特别是如果视图是基于其他视图创建的。

    表依赖关系:将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时,都必须更改视图。(视图是虚拟的 跟随基本表变化)

    3.触发器

    触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

    3.1触发器语句

    CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

    trigger_name:触发器的名称

    tirgger_time:触发时机,为BEFORE或者AFTER

    trigger_event:触发事件,为INSERT、DELETE或者UPDATE

    tb_name:表示建立触发器的表名,就是在哪张表上建立触发器

    trigger_stmt:触发器的程序体,可以是一条SQL语句或者是用BEGIN和END包含的多条语句

    删除触发器:

    Drop trigger 触发器名称

    3.2案例

    为student的班级id列增加外键约束

    在这里插入图片描述

    ALTER TABLE xmcc_student ADD CONSTRAINT cid_key FOREIGN KEY(s_cid)
    REFERENCES xmcc_class(c_id);
    

    现在有了外键约束,如果我们删除其中.net班级

    在这里插入图片描述

    这个时候就可以使用触发器,当删除某个班级的时候触发修改当前班级学生的班级id为null,当然这儿不需要去深究逻辑

    创建触发器

    在这里插入图片描述

    #创建触发器来保证数据的完整性
    CREATE TRIGGER trigger_test  #触发器名称为trigger_test
    BEFORE DELETE  #在删除前
    ON xmcc_class FOR EACH ROW  #对xmcc_class每一行
    UPDATE xmcc_student SET  #修改xmcc_student
    #old表示删除前的行数据 如果是修改的old表示修改前 new表示修改后
    s_cid=NULL WHERE s_cid=old.c_id
    

    测试:

    在这里插入图片描述

    查看xmcc_student表

    在这里插入图片描述

    3.3说明及缺点

    这儿只是以一个简单的例子来理解触发器,大家需要记住语句的使用,需要看得懂触发器,也需要能写触发器,实际的中的触发器可能逻辑会难一点,但是语法基本都是这样了,会加一定plsql我们在存储过程中再去学习.

    另外:MySQL可以创建以下六种触发器:

    BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE

    AFTER INSERT,AFTER DELETE,AFTER UPDATE

    虽然触发器能完成一定的自动操作来保证一定的安全性,但是:

    1、 如果需要变动整个数据集而数据集数据量又较大时,触发器效果会非常

    2、 对于批量操作并不适合使用触发器 使用触发器实现的业务逻辑在出现问题时很难进行定位,特别是设计到多个触发器的情况 协同开发时,写业务层代码如果不清楚数据库 触发器的细节,容易搞不清到底触发了那些触发器 大量使用触发器会导致代码结构容易被打乱,阅读源码困难

    3、 会占用物理内存

    4.存储过程

    为了方便存储过程的学习,新建简化的商品表

    在这里插入图片描述

    #为了方便测试,我们新建简化的商品表
    #简化商品表
    CREATE TABLE xmcc_product(
    p_id VARCHAR(30) PRIMARY KEY COMMENT '商品id',
    p_name VARCHAR(11) NOT NULL COMMENT '商品名称',
    p_stock INT COMMENT '商品库存',
    p_price DOUBLE COMMENT '商品价格'
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    

    4.1存储过程介绍

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

    可能概念不太好理解,以场景来介绍

    场景:之前我们做过取消订单的功能,取消订单(这里稍微修改下业务):

    1.需要修改订单状态

    ​ 2.增加商品库存

    ​ 3.去掉购买记录

    在这里插入图片描述

    IO非常耗费资源,使用存储过程可以减少java程序与数据库的交互次数

    为了完成这个功能业务,需要先学习存储过程的相关知识

    4.2简单的存储过程入门

    简单存储过程入门:

    在这里插入图片描述

    #存储过程创建
    #DELIMITER修改语句结束符为$$ 因为mysql是;结尾 
    #但是存储过程是多个语句集 在其中每一个sql都需要;结尾 
    #如果我们不修改遇到分号就结束了就不是完整的存储过程了
    DELIMITER $$
    #如果存在这个存储过程就删除
    DROP PROCEDURE IF EXISTS `MyProcedure01`$$
    #创建存储过程
    # DEFINER=`root`@`localhost`: 指定IP与用户
    #PROCEDURE:存储过程关键字 
    #MyProcedure01:存储过程名称      ()说明没有参数,跟java方法类似
    CREATE DEFINER=`root`@`localhost` PROCEDURE `MyProcedure01`()
    #开始 可以看成java方法的{
    BEGIN
    #DECLARE:定义变量关键字  定义一个int类型的变量test 相当于int test
    	DECLARE test INT;
    #设置test的值为1  相当于test=1
    	SET test:=1;
    #查询test的值  相当于System.out.println(test);
    	SELECT test;
    #结束 可以看成java方法的 }
        END$$
    #存储过程写完了 需要把结束符号换回来
    DELIMITER ;
    

    这个错误是由于mysql库缺失引起的

    恢复步骤:打开cmd

    \1. 执行net stop mysql,停止mysql服务

    \2. 执行mysqld -remove,移除服务

    \3. 执行mysqld -install,安装服务

    \4. 执行mysqld --initialize-insecure,初始化

    \5. 执行net start mysql,开启mysql服务

    如果又出现1067

    修改my.ini(一般在C:\Program Files\MySQL\MySQL Server 5.5下)

    在这里插入图片描述

    1、打开MySQL目录下的my.ini文件,在文件的最后添加一行“skip-grant-tables”,保存并关闭文件。

    2、重启MySQL服务。

    3、在命令行中输入“mysql -uroot -p”(不输入密码),回车即可进入数据库。

    4、执行,“use mysql;” 使用mysql数据库。

    5、执行,“update user set password=PASSWORD(“123456”) where user=‘root’;”(修改root的密码)

    6、打开MySQL目录下的my.ini文件,删除最后一行的“skip-grant-tables”,保存并关闭文件。

    7、重启MySQL服务。

    因为修改了数据库的数据地址,需要从新建,mysql02数据库,与商品表

    在这里插入图片描述

    调用存储过程:

    在这里插入图片描述

    4.3:=与=的区别

    在上面的例子中用到了:=

    1.=

    只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=

    2.:=

    不只在set和update时时赋值的作用,在select也是赋值的作用。

    如果明白了=和:=的区别,那么也就理解了下边的现象。

    @num:=@num+1,:=是赋值的作用,所以,先执行@num+1,然后再赋值给@num,所以能正确实现行号的作用。

    @num=@num+1,此时=是等于的作用,@num不等于@num+1,所以始终返回0,如果改为@num=@num,始终返回1了。mysql数据库中,用1表示真,0表示假。

    4.4存储过程if用法

    在刚才的简单存储过程中,其实可以把存储过程类比为java的方法,sql语句就是方法中的java代码,那么这儿的if也是跟java差不多

    在这里插入图片描述

    DELIMITER$$
    CREATE PROCEDURE my_test1()
    BEGIN
    DECLARE i INT; -- 定义变量i
    SET i:=1; -- 设置值为1
    IF i>1 THEN -- 当i>1时
    SELECT 'i大于1'; -- 打印 i 大于 1
    ELSEIF i<1 THEN  
    SELECT 'i小于1';
    ELSE 
    SELECT 'i等于1';
    END IF; -- 只是结束if 在存储过程中的if、循环语句等基本都需要结束
    END$$
    DELIMITER ;
    #调用
    CALL my_test1();
    

    4.5存储过程循环while….do用法

    在这里插入图片描述

    #while...do 循环用法
    DELIMITER //
    CREATE PROCEDURE my_test2()
        BEGIN
       DECLARE i INT; #定义变量i
       SET i=0;  #设置值为0
       WHILE i<5 DO  #当i小于5就执行 相当于while(i<5)
        INSERT INTO xmcc_product(p_id,p_name,p_stock,p_price) 
        VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100);#我们商品表插入数据
            SET i:=i+1; #设置 i+1 相当于i++
        END WHILE; #结束循环 注意
     END;// #简写 delimiter
    #调用存储过程
    CALL my_test2();
    #查看数据
    SELECT * FROM xmcc_product
    

    4.6存储过程循环repeat用法

    在这里插入图片描述

    #repeat 用法
    DELIMITER //
    CREATE PROCEDURE my_test3()
        BEGIN
     DECLARE i INT DEFAULT 10; #定义变量 i 赋予默认值
        REPEAT #开始循环
           INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100);
            SET i=i+1;
         UNTIL i>=15  #当i大于等于15就执行下面的 结束循环
       END REPEAT;
     END;// #简写 delimiter
     #调用 
     CALL my_test3();
    #查看数据
    SELECT * FROM xmcc_product
    

    4.7存储过程循环loop用法

    在这里插入图片描述

    #loop 用法  
    DELIMITER //
    CREATE PROCEDURE my_test4()
    BEGIN
         DECLARE i INT DEFAULT 100;
         loop_label: LOOP  #相当于java的标记 用于多重循环,loop表示开始循环 也可以不用标记 这儿一起举例了
            INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100);
           SET i=i+1;
            IF i>=105 THEN  #当i大于105的时候 执行下面的
                LEAVE loop_label; #离开标记处的循环
            END IF; #结束if 再次提示 注意
        END LOOP; #离开循环 语法
        END ;// #简写 delimiter
    
     #调用 
     CALL my_test4();
    #查看数据
    SELECT * FROM xmcc_product
    

    4.8存储过程参数in out

    在这里插入图片描述

    DELIMITER$$
    #in 代表输入参数 外面传递进来的 out 代表输出参数 可以传出去的 
    #需要注意类型是写在后面的
    CREATE PROCEDURE my_test5(IN i INT,OUT message VARCHAR(20))
    BEGIN
    IF i>1 THEN
    SET message:='传入的参数大于1';
    ELSE 
    SET message:='传入的参数不大于1';
    END IF;
    END$$
    DELIMITER ;
    #调用存储参数 并将输出参数message 赋值给@message 变量
    CALL my_test5(2,@message);
    SELECT @message;
    

    4.9存储过程参数inout

    在这里插入图片描述

    #存储过程参数inout 使用
    DELIMITER$$
    CREATE PROCEDURE my_test6(INOUT i INT)
    BEGIN
    IF i>1 THEN
    SET i:=10;
    ELSE 
    SET i:=-10;
    END IF;
    END$$
    DELIMITER ;
    
    SET @i:=1; -- 设置变量@i并赋值
    CALL my_test6(@i); -- 先将@i变量传入 然后获得里面的赋值在传出
    SELECT @i;
    

    4.10存储过程异常处理

    当sql出现异常的时候跳出存储过程并设置值为xxx

    Declare exit handler for sqlException set ……

    在这里插入图片描述

    #存储过程异常处理
    DELIMITER//
    CREATE PROCEDURE my_test7()
    BEGIN
    DECLARE i INT DEFAULT 0;
    -- continue继续  exit 退出  HANDLER执行处理器  SQLEXCEPTION;sql异常
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i:=-1;
    START TRANSACTION;#开启事务
    UPDATE xmcc_product SET p_name='苹果' WHERE p_id=10000;
    INSERT INTO xmcc_product VALUES(10000,'主键冲突',10,100); 
    IF i=-1 THEN
    SELECT '出现异常,事务回滚';
    ROLLBACK; #回滚
    ELSE
    COMMIT; #提交
    END IF;
    END//
    DELIMITER ;
    #调用 
    CALL my_test7();
    #查看结果
    SELECT * FROM xmcc_product
    

    查询xmcc_product查看是否回滚

    在这里插入图片描述

    4.11游标介绍

    游标:

    有数据缓冲的思想:游标的设计是一种数据缓冲区的思想,用来存放SQL语句执行的结果先有数据基础:游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。 类似于指针:游标类似于指向数据结构堆栈中的指针,用来pop出所指向的数据,并且只能每次取一个

    不太好理解,但是使用非常简单,简单来说相当于在java中的ResultSet

    因为明天就有实际的应用,今天以一个小例子简单来说明一下。

    商品表的记录:

    在这里插入图片描述

    通过一个存储过程来完成商品名称的拼接

    在这里插入图片描述

    DELIMITER //
    CREATE PROCEDURE my_test8()
    BEGIN
    DECLARE result VARCHAR(100) DEFAULT '';-- 定义一个result 来存储商品名称的拼接
    DECLARE product_name VARCHAR(10);-- 定义一个变量来接收每次游标的商品名称
    DECLARE done INT DEFAULT FALSE; -- 定义done变量 默认值为false
    -- 查询所有的商品名称放入游标中 CURSOR 表示该变量为游标类型
    DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;
    -- 游标遍历结束 会出现not found 设置done的值为true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curl;-- 打开游标
    WHILE(NOT done) DO
    FETCH curl INTO product_name; -- 弹出游标的一条数据赋值给product_name
    SET result:=CONCAT(result,',',product_name);-- 拼接字符串
    END WHILE;
    CLOSE curl;-- 关闭游标 就像java中需要关闭resultset一样
    SELECT result;-- 打印出结果
    END;//
    #调用
    CALL my_test8();
    

    4.12存储过程优缺点(面试常问,标红的需要记住)

    1.优点:

    \1. 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而 一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速 度。

    \2. 存储过程可以重复使用,可减少数据库开发人员的工作量(可以忽略)

    \3. 安全性高,可设定只有某此用户才具有对指定存储过程的使用权

    \4. 减少网络通信量。 Java与mysql的IO交互
    调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL 语句,那么其性能绝对比一条一条的调用SQL 语句要高得多。

    2.缺点:

    a、不可移植性,每种数据库的内部编程语法都不太相同,当你的系统需要兼容多种数据库时最好不要用存储过程。

    b、学习成本高,没有java代码debug方便

    c、业务逻辑多处存在,采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理,这种架构会增加一些系统维护和调试成本。
    业务逻辑集中管理会更易于维护与调试,你很难做到业务逻辑都放在存储过程里,比如关于一些客户输入数当据的简单校验,会话数据的校验,应用服务器缓存数据的校验。

    d、 运行速度:大多数高级的数据库系统都有statement cache的,所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多(检查权限等),所以对于很简单的sql,存储过程没有什么优势。

    e、存储占物理内存

    5.springboot+mybatis 完成存储过程的调用

    案例:当取消订单的时候

    1.修改订单状态为取消(1)

    2.对订单项里面的商品增加库存

    如果我们按照以前的操作需要与mysql有多次的IO交互,当然这儿逻辑比较简单,如果复杂的IO交互更多,我们使用昨天的存储过程的调用,来通过一次IO交互完成上述需求

    2.1存储过程编写

    #编写取消订单的存储过程
    DELIMITER //
    #输入参数为订单order_id 输出参数为result 1 代表存储过程成功 -1代表存储过程出现异常
    CREATE PROCEDURE cancel_order(IN order_id VARCHAR(30),OUT result INT)
    BEGIN 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result:=-1;#如果出现异常设置返回值为-1
    START TRANSACTION;#开启事务
    SET result:=1;#避免传入参数错误 这里设置为1
    UPDATE xmcc_order SET o_statu=1 WHERE o_id=order_id; #修改订单状态
         BEGIN 
    	  DECLARE done INT DEFAULT TRUE;
     #定义变量done用来判断
    	  DECLARE product_id VARCHAR(30); #定义变量 来接收游标的商品id
    	  DECLARE quantity_1 INT; #定义变量来接收游标的商品数量
    	  #定义游标 存储根据订单id查询到订单项中的商品id与数量
    	  DECLARE cur CURSOR FOR SELECT p_id,quantity FROM xmcc_orderdetail WHERE o_id=order_id;
    	  #当游标循环结束 设置done的值为false
    	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=FALSE;
    	  OPEN cur;#打开游标
    	  WHILE done DO #循环
    	  FETCH cur INTO product_id,quantity_1;#将游标的值设置到变量中
    	  IF done THEN #判断 不然会多修改一次
    	  UPDATE xmcc_product SET p_stock=p_stock+quantity_1 WHERE p_id=product_id;
    	  END IF;
    	  END WHILE;
    	  CLOSE cur;
         END ;
     IF result=1 THEN #没有出现异常就提交
     COMMIT;
     ELSE 
     ROLLBACK;
     END IF;
     END ; //
     #测试
    CALL cancel_order('20001',@result);
    #查看结果
    SELECT @result
    

    2.2mybatis调用存储过程

    Xml编写:

    在这里插入图片描述

    <!--第一种写法 通过parameterMap来传递参数
    <parameterMap id="cancelMap" type="map">
        property 参数对应存储过程的参数名字以及传入来map的key名字 其他应该很好理解
        <parameter property="order_id" javaType="string" jdbcType="VARCHAR" mode="IN"></parameter>
        <parameter property="result" javaType="INTEGER" jdbcType="INTEGER" mode="OUT"></parameter>
    </parameterMap>
    <update id="cancelOrder" parameterMap="cancelMap"  statementType="CALLABLE">
        {call cancel_order(?,?)}
    </update>-->
    
    <!--第二种写法-->
    <update id="cancelOrder" parameterType="map"  statementType="CALLABLE">
        {call cancel_order(
          <!--只需要对应传递过来的map key的名字就可以了 一般都是用map来处理 当然其他也是可以的-->
          #{order_id,mode=IN,jdbcType=VARCHAR},
          #{result,mode=OUT,jdbcType=INTEGER}
          )}
    </update>
    
    

    Mapper接口:

    在这里插入图片描述

    测试之前数据库数据:

    在这里插入图片描述

    在这里插入图片描述

    在这里插入图片描述

    测试方法:

    @Test
    public void cancelOrder(){
        HashMap<Object, Object> map = Maps.newHashMap();
        map.put("order_id","20001");
        map.put("result",3);//使用3来查看是否更改了值
        orderMapper.cancelOrder(map);
       log.info("存储过程调用成功,结果为:{}",map.get("result"));
    }
    

    调用之后结果:

    在这里插入图片描述

    在这里插入图片描述

    在这里插入图片描述

    当然可以根据返回值在业务层做对应的事务处理。

    完成了存储过程的简单调用,以后大家遇到复杂的都可以以这个为基础去学习,压力不大

    至于视图、触发器等比较简单,大家自己调用练习就可以了。

    展开全文
  • 存储过程-自动备份并压缩,清除过时备份文件(添加远程备份和远程清理功能), 自己写的, 已经应用于生产中的, 使用前请根据自己的现场做简单修改, 1. 修改一下本地存储路径; 2. 修改远程存储路径; 3.修改远程访问的...
  • 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后...前言:很多人都说存储过程过时了,学这玩意没啥用,你去外面面试的时候根本就不怎么需要

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

    前言:很多人都说存储过程过时了,学这玩意没啥用,你去外面面试的时候根本就不怎么需要使用。其实我觉得呢,技术没有过不过时的,只要能为你的项目提供解决方案那就是有用的技术,技术不能一直追求新,学技术又不是攀比,觉得知道了几个新名词就很牛逼,其实不然。

    公司一个项目里需要用到存储过程,而我正好又负责这一模块,所以就需要开始学习存储过程等知识。对我而言,普通的sql语句是能看懂能写出来的,对于存储过程的认识也只是停留在知道存储过程也是由sql语句组成,仅此而已。对于同事说保存在数据库中的Sql语句,就显得更迷茫了。怎么保存的,保存在哪个地方,怎么找到,怎么使用,java程序又是怎么调用的……等等一系列的问题出现在我的脑袋里。

    下图是pl/sql developer中存储过程,存储函数等保存的结构位置:
    结构图

    存储过程格式:

    create or replace procedure procedure_name (Name1 in type,Name2 out type)   --定义了一个输入变量和一个输出变量
    as 
    --此处可定义变量
    begin
    --pl/sqlend procedure_name;

    包头格式:

    --包头中只声明定义,不写具体逻辑代码,等在包体中实现
    create or replace package package_name as 
      --定义一个游标类型
      type t_cursor is ref cursor;
      --声明一个存储过程,我这里是带参数的,不带参数直接不写括号内的即可
      procedure procedure_name(a in number,b out t_cursor);
      end package_name;
    

    包体格式:

    --包体需要实现包头中声明的所有的方法
     create or replace package body package_name as
     procedure procedure_name(a in number,b out t_cursor) as
     begin
     --因为b 是一个游标类型,使用之前请open一下它 
       open b for select * from emp where userno = a;
     end procedure_name;
     end package_name;
    

    写好了包头包体之后,保存并编译它。然后在java程序中调用它。
    先就此搁笔,过一两天再完成剩余部分。

    展开全文
  • 存储过程入门

    2017-07-24 15:40:01
    最近工作用到了存储过程,这里就简单的给大家描述一下我对存储过程的心得体会。 一、存储过程是什么?  定义:存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库...

    最近工作用到了存储过程,这里就简单的给大家描述一下我对存储过程的心得体会。


    一、存储过程是什么?

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

     说白了,就是一堆sql语句的合并,然后中间加了一点控制逻辑。


    以oracle 为例,其基本语法为:

    Oracle存储过程基本语法 存储过程 
    1 CREATE OR REPLACE PROCEDURE 存储过程名 (param1 in type,param2 out type)

    2 is/as(创建视图的时候as,创建游标的时候用is,平时的时候无大体区别)

    3 变量 类型(范围);

    4 begin

    5  执行体

    6 end 存储过程名;


    实例:

    create or replace procedure pro_test(
    pi_id in varchar2,
    pi_name out varchar2
    )
    is
    begin
    select cname into pi_name from tb_user where id=pi_id;
    dbms_output.put_line('name:'||pi_name);
    end pro_test;


    二、存储过程的优缺点?


    使用存储过程这么几个好处

    1、执行速度快,存储过程只在创造时进行编译,以后每次存储过程都不需要重新编译,而平常的sql语句没执行一次就编译一次,所以使用存储过程可以提高执行速度。

    2、减少了网络通信,党对数据库进行复杂操作时(如对多个表进行增删改查),可以将复杂操作用存储过程封装起来与数据库提供的事物处理结合一起使用,如果用程序来完成的话,可能会执行多个sql,建立多次与数据库的连接,而使用存储的话,则只需要与数据库连接一次就行了。

    3、可复用性高,重复使用,可减少数据库开发人员的工作量。

    4、安全性高,可设定只有某次用户才具有对执行存储过程的使用权。


    缺点:

    1、可移植性差,不同的数据库对应的存储过程语法不同,所以在移植的时候可能会出现问题

    2、sql本身是一种结构化的查询语言,只是加上了一些逻辑控制,还是属于过程化的,当面对一些复杂的业务需求的时候,过程化的处理会比较麻烦



    三、关于存储过程使用的争论? 文章引用于http://blog.csdn.net/zy1691/article/details/3742780

     我不倾向于尽可能使用存储过程,是这么认为的:     
      1.   运行速度:   大多数高级的数据库系统都有statement   cache的,所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多(检查权限等),所以对于很简单的sql,存储过程没有什么优势。     
      2.   网络负荷:如果在存储过程中没有多次数据交互,那么实际上网络传输量和直接sql是一样的。     
      3.   团队开发:很遗憾,比起成熟的IDE,没有什么很好存储过程的IDE工具来支持,也就是说,这些必须手工完成。     
      4.   安全机制:对于传统的C/S结构,连接数据库的用户可以不同,所以安全机制有用;但是在web的三层架构中,数据库用户不是给用户用的,所以基本上,只有一个用户,拥有所有权限(最多还有一个开发用户)。这个时候,安全机制有点多余。     
      5.   用户满意:实际上这个只是要将访问数据库的接口统一,是用存储过程,还是EJB,没太大关系,也就是说,在三层结构中,单独设计出一个数据访问层,同样能实现这个目标。     
      6.   开发调试:一样由于IDE的问题,存储过程的开发调试要比一般程序困难(老版本DB2还只能用C写存储过程,更是一个灾难)。     
      7.   移植性:算了,这个不用提,反正一般的应用总是绑定某个数据库的,不然就无法靠优化数据库访问来提高性能了。     
      8.   维护性:的确,存储过程有些时候比程序容易维护,这是因为可以实时更新DB端的存储过程,但是在3层结构下,更新server端的数据访问层一样能实现这个目标,可惜现在很多平台不支持实时更新而已。     
        
      从上面可知道,存储过程的使用不能有死规定(全用,或全不用),以前Terminal   -   Server,   Client-DB的方式已经过时了,存储过程很多优势已经不明显。     
      现在,我认为的原则是:所有数据访问在应用层封装为数据访问层,在那里,如果SQL简单的话,直接用SQL;如果SQL复杂,或者数据交互多且中间数据最后不会用到,使用存储过程。其他凭经验吧。





    展开全文
  • 存储过程浅述

    2017-10-05 11:12:01
    为什么要用存储过程 几个去 IBM 面试的兄弟回来抱怨:去了好几个不同的 IBM 项目组,几乎每个面试官问到数据库的时候都要问用没用过存储过程,烦人不?大家去面的程序员,又不是 DBA,以前的项目都没有用到存储,不...
  • Hibernate 调用存储过程

    千次阅读 2015-03-26 18:23:13
    不知道为什么,在Hibernate中没有调用存储过程的封装类,当然整个Java在调用存储过程上,使用方法都不是太好。 下面写一个Hibernate使用存储过程的例子: 思路主要还是通过java.sql.connection 这个接口来实现的,...
  • 存储过程的优缺点

    2016-09-18 15:26:15
    为什么要用存储过程几个去 IBM 面试的兄弟回来抱怨:去了好几个不同的 IBM 项目组,几乎每个面试官问到数据库的时候都要问用没用过存储过程,烦人不?大家去面的程序员,又不是 DBA,以前的项目都没有用到存储,不...
  • 该不该用存储过程

    千次阅读 2018-01-23 15:02:55
    转:减少存储过程封装业务逻辑-web开发与传统软件开发的思维模式不同 本篇文章讨论并不是:不要使用存储过程,因为有些事情还是要存储过程来完成,不可能不用。而是关于:"业务逻辑是不是要封装在存储过程中实现,...
  • 为什么不推荐使用存储过程

    千次阅读 多人点赞 2019-06-17 18:00:33
    本文作者:菜v菜 | 观点整理:徐刘根 ...存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,883
精华内容 17,153
关键字:

存储过程过时