关于存储过程

原创 2013年12月04日 09:43:33


背景描述

要求:通过存储过程,将临时表里符合要求的数据依次添加到实时数据表里,在同事帮助下该存储过程得以完成。 该存储过程如下:
注意点:

  1. 存储过程的加载顺序:
    • 变量和条件声明
    • Cursor声明
    • 程序代码
  1. 使用loop循环
    • loop循环可以与leave结合使用,这样可以直接退出循环,相当于java中的break或return
  1. 如果遇到摸个变量的值为字符串而无法解析,可以使用转义符将其设定为字符处,具体情况见下面代码103行
     
    1  
    2  
    3 DELIMITER $$
    4  
    5 USE `mrms2`$$
    6  
    7 DROP PROCEDURE IF EXISTS `mrms_procedure_dataHandler`$$
    8  
    9 CREATE DEFINER=`root`@`localhost` PROCEDURE `mrms_procedure_dataHandler`()
    10 BEGIN
    11  
    12     -- declare start ---------------------------------------------------------------------------
    13     -- 创建临时总表的临时变量
    14     DECLARE tmp_id INT(11) DEFAULT 0;
    15     DECLARE tmp_count_code VARCHAR(11) DEFAULT 0;
    16     DECLARE tmp_count_count INT(11) DEFAULT 0;
    17  
    18     -- 创建临时单个编码表的几个临时变量
    19     DECLARE tmp_enterpriseEnglishName  VARCHAR(20);
    20     DECLARE tmp_code VARCHAR(11);
    21     DECLARE tmp_classes VARCHAR(20);
    22     DECLARE tmp_typeName VARCHAR(20);
    23     DECLARE tmp_medicineName VARCHAR(20);
    24     DECLARE tmp_agent VARCHAR(20);
    25     DECLARE tmp_guige VARCHAR(20);
    26     DECLARE tmp_unit VARCHAR(20);
    27     DECLARE tmp_total INT(11);
    28     DECLARE tmp_unitPrice FLOAT;
    29     DECLARE tmp_totalPrice FLOAT;
    30     DECLARE tmp_maker VARCHAR(64);
    31     DECLARE tmp_createTime TIMESTAMP;
    32     DECLARE tmp_comment VARCHAR(20);
    33     DECLARE tmp_status INT(11);
    34     DECLARE tmp_isBackUp TINYINT(1);
    35  
    36     -- 创建记录金额和数量的临时变量
    37     DECLARE tmp_sum_price FLOAT DEFAULT 0;
    38     DECLARE tmp_sum_count INT DEFAULT 0;
    39  
    40     -- 创建记录每种药品,计划金额和数量的变量
    41     DECLARE tmp_plan_price FLOAT DEFAULT 0;
    42     DECLARE tmp_plan_count INT DEFAULT 0;
    43  
    44     -- 创建循环的临时变量i
    45     DECLARE i INT;
    46     -- 创建循环结束的临时变量,其实等于临时总表的最后一个id值
    47     DECLARE finishId INT;
    48  
    49  
    50     DECLARE hasMain BOOLEAN DEFAULT FALSE; 
    51     DECLARE isTmpEmpty INT DEFAULT 0;
    52  
    53     -- 创建临时总表的游标
    54     DECLARE cur_count CURSOR FOR SELECT id,CODE,COUNT FROM tmp_table_code_count;
    55     -- 创建单个编码表的游标
    56     DECLARE cur_singleCURSOR FOR SELECT enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUpFROM tmp_table_single_code;
    57  
    58      -- 创建临时表,用来存储每个编码的个数
    59     DROP TABLE IF EXISTS tmp_table_code_count;
    60     CREATE TEMPORARY TABLE tmp_table_code_count( 
    61             `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    62             `code` VARCHAR(11), 
    63             `count` INT(11), 
    64             PRIMARY KEY (`id`) 
    65     )ENGINE=INNODB DEFAULT CHARSET=utf8;
    66  
    67     -- 创建临时表,用来将按药品编码查询,按照CODE和IsBackUp排序后查询出来的结果临时放入此表中
    68     DROP TABLE IF EXISTS tmp_table_single_code;
    69     CREATE TEMPORARY TABLE tmp_table_single_code( 
    70             `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    71             `enterpriseEnglishName` VARCHAR(20),
    72             `code` VARCHAR(11),
    73             `classes`  VARCHAR(20),
    74       `typeName` VARCHAR(20),
    75       `medicineName` VARCHAR(20),
    76       `agent` VARCHAR(20),
    77       `guige` VARCHAR(20), 
    78       `unit`  VARCHAR(20),
    79       `total` INT(11), 
    80       `unitPrice` FLOAT,
    81       `totalPrice` FLOAT,
    82       `maker`  VARCHAR(64),
    83       `createTime` TIMESTAMP,
    84       `comment`  VARCHAR(20),
    85       `status` INT(11),
    86             `isBackUp` TINYINT(1),           
    87             PRIMARY KEY (`id`) 
    88     )ENGINE=INNODB DEFAULT CHARSET=utf8;
    89  
    90     -- init data start ---------------------------------------------------------------------------
    91      -- 写入临时总表数据
    92     INSERT INTO tmp_table_code_count(CODE,COUNT) SELECT CODE,COUNT(1) cound FROM mrms_medicine_info_tmp GROUP BY CODE;
    93  
    94     -- 记录最后数据的id,用于下面第一层循环的结束标记
    95     SELECT MAX(id) INTO finishId FROM tmp_table_code_count;
    96     SELECT COUNT(1) INTO isTmpEmpty FROM tmp_table_code_count;
    97  
    98     -- handler start ---------------------------------------------------------------------------
    99      -- 第1层循环
    100     IF isTmpEmpty !=0 THEN
    101     OPEN cur_count;
    102     curCountLoop:LOOP
    103         FETCH cur_count INTO tmp_id,tmp_count_code,tmp_count_count;
    104         -- 依据临时总表的编码,把单个编码的所有记录写入临时单个编码表
    105        INSERT INTO tmp_table_single_code(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp) 
    106              SELECT enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUpFROM mrms_medicine_info_tmp
    107                    WHERE CODE = '\'' + tmp_count_code + '\'' ORDER BY CODE,isBackUp;
    108  
    109  
    110         -- 第2层循环
    111          OPEN cur_single;
    112          SET i = 0;
    113          SET tmp_sum_price = 0;
    114          SET tmp_plan_count = 0;
    115          SET hasMain = FALSE;
    116  
    117         WHILE i < tmp_count_count DO
    118              FETCH cur_singleINTO tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp;
    119  
    120              -- 获取计划中的金额和数量
    121              SELECT totalPrice,COUNT INTO tmp_plan_price,tmp_plan_count FROM mrms_plan WHERE CODE = '\'' + tmp_code + '\'' AND  enterpriseEnglishName = '\'' + tmp_enterpriseEnglishName + '\''  LIMIT 1;
    122  
    123        -- 业务判断
    124              IF i = 0 THEN 
    125                   IF tmp_isBackUp =0 THEN
    126                      SET hasMain = TRUE;
    127                      IF tmp_sum_price < tmp_plan_price || tmp_sum_count < tmp_plan_count THEN
    128                          INSERT INTO mrms_medicine_info_cur(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp) 
    129                              VALUES (tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp);
    130  
    131  
    132                          SET tmp_sum_price = tmp_sum_price + tmp_totalPrice;
    133                          SET tmp_sum_count = tmp_sum_count + tmp_total;
    134                      END IF;
    135  
    136                   END IF;
    137              ELSE 
    138                   IF hasMain = TRUE && tmp_isBackUp = 1 && ( tmp_sum_price < tmp_plan_price || tmp_sum_count < tmp_plan_count) THEN
    139                      INSERT INTO mrms_medicine_info_cur(enterpriseEnglishName,CODE,classes,typeName,medicineName,agent,guige,unit,total,unitPrice,totalPrice,maker,createTime,COMMENT,STATUS,isBackUp) 
    140                            VALUES (tmp_enterpriseEnglishName,tmp_code,tmp_classes,tmp_typeName,tmp_medicineName,tmp_agent,tmp_guige,tmp_unit,tmp_total,tmp_unitPrice,tmp_totalPrice,tmp_maker,tmp_createTime,tmp_comment,tmp_status,tmp_isBackUp);
    141  
    142  
    143                       SET tmp_sum_price = tmp_sum_price + tmp_totalPrice;
    144                       SET tmp_sum_count = tmp_sum_count + tmp_total;
    145                   END IF;
    146  
    147              END IF;
    148  
    149              -- i++
    150              SET i = i + 1;
    151         END WHILE;
    152         CLOSE cur_single;
    153  
    154         -- 第2层循环结束后清空临时表:tmp_table_single_code
    155         DELETE FROM tmp_table_single_code;
    156  
    157         -- 如果是最后一个,则执行完上面的逻辑后,就退出循环
    158         IF tmp_id = finishId THEN
    159              LEAVE curCountLoop;
    160         END IF;
    161  
    162  
    163     END LOOP; 
    164     CLOSE cur_count; 
    165  
    166     END IF;
    167     -- end ---------------------------------------------------------------------------
    168    
    169     END$$
    170  
    171 DELIMITER ;

相关文章推荐

存储过程循坏

  • 2017年10月30日 13:44
  • 714B
  • 下载

全能增删查分页存储过程

  • 2017年11月16日 14:02
  • 4KB
  • 下载

C#调用存储过程的几个方法

/数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.         public static string connectionStrin...

mysql存储过程

  • 2017年11月07日 17:14
  • 2KB
  • 下载

MVC+EF+ 存储过程的小案例

  • 2017年11月16日 10:07
  • 6.66MB
  • 下载

MySQL存储过程学习笔记

一、基本语法及简单实例 1、创建简单的测试环境 mysql> use test; Database changed mysql> show tables; Empty set (0.00 se...
  • horace20
  • horace20
  • 2011年12月09日 11:07
  • 17506

jdbc调用存储过程实现增删改查

  • 2016年08月31日 10:49
  • 8KB
  • 下载

Oracle 存储过程中的DDL语句

Oracle的存储过程,是我们使用数据库应用开发的重要工具手段。在存储过程中,我们大部分应用场景都是使用DML语句进行数据增删改操作。本篇中,我们一起探讨一下数据定义语句DDL在存储过程中使用的细节和...

分页存储过程

  • 2016年08月02日 22:50
  • 3KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于存储过程
举报原因:
原因补充:

(最多只允许输入30个字)