一些函数存储过程的记录

1.记录连续的日期

CREATE VIEW `v_day_info` AS SELECT
    date_format(curdate(), '%c/%d') AS `day`
UNION
    SELECT
        date_format(
            (curdate() - INTERVAL 1 DAY),
            '%c/%d'
        ) AS `day`
    UNION
        SELECT
            date_format(
                (curdate() - INTERVAL 2 DAY),
                '%c/%d'
            ) AS `day`
        UNION
            SELECT
                date_format(
                    (curdate() - INTERVAL 3 DAY),
                    '%c/%d'
                ) AS `day`;

2.存储过程，生成一月的日期

DROP PROCEDURE
IF EXISTS dayproduce;
delimiter //

CREATE PROCEDURE dayproduce ()
BEGIN
    -- 声明与列的类型相同的变量
    DECLARE
        begindate datetime ; --开始时间内
        DECLARE
            overdate datetime ; -- 结束时间
        SET begindate = DATE_ADD(
            curdate(),
            INTERVAL - DAY (curdate()) + 1 DAY
        ) ; -- 当月第一天
        SET overdate = last_day(curdate()) ; -- 当月最后一天
        DROP TABLE
        IF EXISTS `t_day` ; CREATE TABLE `t_day` (
            `day` datetime DEFAULT NULL COMMENT '当前月日期'
        ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
        WHILE begindate <= overdate DO
            INSERT INTO t_day
        VALUES
            (begindate) ;
        SET begindate = DATE_ADD(begindate, INTERVAL 1 DAY) ;
        END
        WHILE ;
        END ;//
delimiter ;
3.行列转换，现在也看不懂了

DROP PROCEDURE
IF EXISTS rowTransfer;
delimiter //
CREATE PROCEDURE rowTransfer()
BEGIN
                                        DROP TABLE
                                        IF EXISTS tx;
                                        CREATE TEMPORARY TABLE
                                        IF NOT EXISTS tx (
                                            SELECT
                                                vm3.`day`,
                                                vm3.outgoing_quantity,
                                                vm3. NAME
                                            FROM
                                                (
                                                    SELECT
                                                        vm.`day`,
                                                        vm.outgoing_quantity,
                                                        vm.`name`
                                                    FROM
                                                        (
                                                            SELECT
                                                                DATE_FORMAT(td.`day`, '%e') DAY,
                                                                SUM(
                                                                    ROUND(
                                                                        IFNULL(tord.outgoing_quantity, 0),
                                                                        2
                                                                    )
                                                                ) outgoing_quantity,
                                                                tr.`name`
                                                            FROM
                                                                t_day td
                                                            LEFT JOIN t_out_repertory_detail tord ON DATE_FORMAT(td.`day`, '%c/%d') = DATE_FORMAT(tord.outgoing_time, '%c/%d')
                                                            AND tord.del_flag = 'not_del'
                                                            AND DATE_FORMAT(td.`day`, '%c/%d') = DATE_FORMAT(tord.outgoing_time, '%c/%d')
                                                            LEFT JOIN t_repertory tr ON tr.id = tord.repertory_id
                                                            WHERE
                                                                1 = 1
                                                            AND tr.`name` IS NOT NULL
                                                            GROUP BY
                                                                tr.`name`,
                                                                DATE_FORMAT(td.`day`, '%c/%d')
                                                            ORDER BY
                                                                DATE_FORMAT(td.`day`, '%c/%d')
                                                        ) vm
                                                    UNION
                                                        SELECT
                                                            vm1. DAY,
                                                            vm1.outgoing_quantity,
                                                            vm1.`name`
                                                        FROM
                                                            (
                                                                SELECT
                                                                    DATE_FORMAT(td1.`day`, '%e') DAY,
                                                                    ROUND(
                                                                        IFNULL(
                                                                            SUM(tord1.outgoing_quantity),
                                                                            0
                                                                        ),
                                                                        2
                                                                    ) outgoing_quantity,
                                                                    'all' NAME
                                                                FROM
                                                                    t_day td1
                                                                LEFT JOIN t_out_repertory_detail tord1 ON DATE_FORMAT(td1.`day`, '%c/%d') = DATE_FORMAT(
                                                                    tord1.outgoing_time,
                                                                    '%c/%d'
                                                                )
                                                                AND tord1.del_flag = 'not_del'
                                                                WHERE
                                                                    1 = 1
                                                                GROUP BY
                                                                    DATE_FORMAT(
                                                                        tord1.outgoing_time,
                                                                        '%c/%d'
                                                                    )
                                                                ORDER BY
                                                                    DATE_FORMAT(td1.`day`, '%c/%d')
                                                            ) vm1
                                                ) vm3
                                            ORDER BY
                                                vm3.`day`
                                        );

                                        SET @EE = '';
                                        SELECT
                                            @EE := CONCAT(
                                                @EE,
                                                'SUM(IF(day=\'',
                                                DAY,
                                                '\'',
                                                ', outgoing_quantity,0)) AS \'',
                                                REPLACE (DAY, ' ', ''),
                                                '\','
                                            )
                                        FROM
                                            (SELECT DISTINCT DAY FROM tx) A;
                                        SET @QQ = CONCAT(
                                            'SELECT ifnull(name,\'total\')as name,',
                                            LEFT (@EE, LENGTH(@EE) - 1),
                                            ' FROM tx GROUP BY name order by name'
                                        );
                                        
                                        PREPARE stmt3 FROM @QQ;
                                        
                        END ;//
delimiter ;

4.批量生成二维码编号

DROP PROCEDURE
IF EXISTS batchProduceBarCode;

delimiter //
CREATE PROCEDURE batchProduceBarCode(IN num INT,IN printId VARCHAR(64))
BEGIN
SET @overNum = num;
SET @beginNum = 0;
SET @printId = printId;

WHILE @beginNum < @overNum DO
set @str = (SELECT CONCAT((select nextval('bar_code')),''));
set @lenth = (SELECT LENGTH(@str));

insert into t_transitbox (id,print_id,bar_code,box_date,disposable,
        del_flag)
SELECT
   (SELECT REPLACE(UUID(),'-','')) id,
   @printId print_id,
   CASE
   WHEN @lenth = 1
   THEN CONCAT((SELECT DATE_FORMAT(CURRENT_DATE(),'ZZTM%Y%m%d')),'000',@str)
   WHEN @lenth = 2
   THEN CONCAT((SELECT DATE_FORMAT(CURRENT_DATE(),'ZZTM%Y%m%d')),'00',@str)
   WHEN @lenth = 3
   THEN CONCAT((SELECT DATE_FORMAT(CURRENT_DATE(),'ZZTM%Y%m%d')),'0',@str)
   END AS bar_code,
   NOW() box_date,'yes' disposable,'not_del' del_flag FROM DUAL;
SET @beginNum = @beginNum +1;
END
        WHILE ;
END ; //
delimiter ;

5.关于删除的存储过程

DROP PROCEDURE IF EXISTS deleteBarCodeInfoByCode;
CREATE  PROCEDURE `deleteBarCodeInfoByCode`(IN barCode VARCHAR(30))
BEGIN
DELETE FROM t_transitbox WHERE bar_code = barCode;
DELETE FROM t_transitbox_last_record WHERE bar_code =barCode;
DELETE FROM t_transitbox_record_history WHERE bar_code =barCode;
UPDATE t_repertory_detail SET del_flag='del' WHERE bar_code =barCode;
UPDATE t_transport_transbox SET del_flag='del' WHERE bar_code =barCode;
UPDATE t_region_transport_transbox SET del_flag='del' WHERE bar_code =barCode;
 END;

6.关于批量删除的存储过程

 DROP PROCEDURE
IF EXISTS batchDelBarCode;
delimiter //
CREATE  PROCEDURE `batchDelBarCode`(IN idstr VARCHAR(64))
BEGIN
    -- 声明与列的类型相同的变量
  
    DECLARE
        nodata INT DEFAULT 0 ; #注意：这个变量声明必须放在游标声明前面
    DECLARE
        barCode VARCHAR (20) ; -- 1、定义一个游标mycursor
-- DECLARE id varchar (20) ;
    DECLARE
        mycursor CURSOR FOR 
       SELECT ttlr.bar_code barCode
        FROM t_transitbox ttr
        LEFT JOIN t_transitbox_last_record ttlr ON ttlr.bar_code = ttr.bar_code
        WHERE ttlr.del_flag = 'not_del' AND ttlr.box_type = 'not_warehousing' AND ttr.print_id = idstr;
                 
                 
  DECLARE EXIT HANDLER FOR NOT FOUND
                    SET nodata = 1 ; #当读到数据的最后一条时,设置变量为1
                    -- 2、打开游标
    OPEN mycursor ;
            WHILE nodata = 0 DO
                        #判断是不是到了最后一条数据
                        -- 3、使用游标获取列的值
                        FETCH next FROM mycursor INTO barCode ; -- 4、显示结果
            CALL deleteBarCodeInfoByCode (barCode) ;
            END WHILE ; 
            -- 5、关闭游标
                        CLOSE mycursor ;
                        END;//
delimiter ;

7.关于统计的函数

CREATE  FUNCTION `calculate_remaining_amount`(inventoryQuantity DOUBLE(20,7),
    #库存数量
    prepareQuantity DOUBLE(20,7)) RETURNS double(20,7)
BEGIN
    DECLARE
        x DOUBLE (20,7) ;
    SET x = 0 ;
    IF (
        inventoryQuantity - prepareQuantity
    ) <= 0 THEN

    SET x = 0 ;
    ELSE

    SET x = (
        inventoryQuantity - prepareQuantity
    ) ;
    END
    IF ; RETURN x ;
    END；

8.无题

CREATE  PROCEDURE `check_materials_print_p`(IN id VARCHAR(64))
BEGIN
    -- 声明与列的类型相同的变量
    TRUNCATE TABLE t_check_materials_info_print ; INSERT INTO t_check_materials_info_print (
        `number`,
        productName,
        specification,
        regionName,
        unitName,
        stockQuantity,
        inFactQuantity,
        profitloss,
        proposer
    ) SELECT
        IFNULL(vp.productNumber, '/') number,
        vp.productName,
        vp.specification,
        IFNULL(trr.`name`, '无') regionName,
        vp.unitName,
        ROUND(trcd.stock_quantity, 2) AS stockQuantity,
        ROUND(trcd.checking_quantity, 2) AS inFactQuantity,
        ROUND(
            (
                trcd.stock_quantity - trcd.checking_quantity
            ),
            2
        ) profitloss,
        trcd.operator AS proposer
    FROM
        t_repertory_checking_detail trcd
    LEFT JOIN t_repertory_checking trc ON trc.id = trcd.checking_id
    LEFT JOIN t_repertory tr ON tr.id = trc.repertory_id
    AND tr.del_flag = '0'
    LEFT JOIN t_repertory_region trr ON trr.id = trcd.region_id
    LEFT JOIN view_product_info vp ON vp.productId = trcd.product_id
    WHERE
        trc.id = id
    AND trc.del_flag = '0'
    ORDER BY
        trcd.update_date DESC ;
    END；

9.返回当前序列号的函数：

CREATE  FUNCTION `currval`(v_seq_name VARCHAR(50)) RETURNS int(11)
begin        
    declare value integer;         
    set value = 0;         
    select current_val into value  from sequence where seq_name = v_seq_name;   
   return value;   
end；

10.返回下一个序列号的函数

CREATE  FUNCTION `nextval`(v_seq_name VARCHAR(50)) RETURNS int(11)
begin  
    update sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;  
    return currval(v_seq_name);  
end；

11.关于返回一个查询结果的函数

CREATE  FUNCTION `find_single_prepares_quantity`(
    orderNumber VARCHAR(255),#订单编号
    productNumber VARCHAR (255),
    #料品编号
    repertoryId VARCHAR (255),
    #仓库id
    regionId VARCHAR (255) #库位id 
) RETURNS double(20,2)
BEGIN
    DECLARE
        x DOUBLE(20,2) ;
    SET x = 0 ; SELECT
        IFNULL(SUM(tppmd.quantity), 0) quantity
    FROM
        t_produce_prepares_materials tppm
    LEFT JOIN t_produce_prepares_materials_detail tppmd ON tppmd.prepares_number = tppm.number
    LEFT JOIN t_product tp ON tp.id = tppmd.product_id
    WHERE
        tppmd.repertory_id = repertoryId
    AND tppm.order_number = orderNumber
    AND tppmd.region_id = regionId
    AND tp.number = productNumber
    AND tppm.`status` = 'not_picking'
    AND tppmd.repertory_id IS NOT NULL
    AND tppmd.region_id IS NOT NULL
    AND tppmd.del_flag = 0
    AND tppm.del_flag = 'not_del'
    GROUP BY tppm.order_number, tppmd.repertory_id,tppmd.region_id,tp.id
 INTO x ; RETURN x ; END；

12.出入库表打印记录的存储过程：

BEGIN
    -- 声明与列的类型相同的变量
        TRUNCATE TABLE t_in_out_materials_info_print;
            INSERT INTO t_in_out_materials_info_print(qualityNumber,orderId,relatedOrderNumber,incomingMaterialNumber,qualityPeople,productNumber,productName,unitName,specification,supplier,receiveRepertory,inspectionQuantity,badReasons)
            SELECT
        trqi.number qualityNumber,
        trqi.order_id orderId,
        trqi.related_order_number relatedOrderNumber,
        trqi.incoming_material_number incomingMaterialNumber,
        trqi.quality_people qualityPeople,
        tp.number productNumber,
        tp.`name`  productName,
        tu.`name`  unitName,
        tp.specification specification,
        ts.`name` supplier,
        tr.`name` receiveRepertory,
        trqid.inspection_quantity inspectionQuantity,
        trqid.bad_reasons badReasons
        FROM
        t_repertory_quality_inspection trqi
        LEFT JOIN t_repertory_quality_inspection_detail trqid ON
        trqid.inspection_id = trqi.id
        LEFT JOIN t_product tp ON tp.id = trqid.product_id
        LEFT JOIN t_supplier ts ON ts.id =
        tp.supplier_id
        LEFT JOIN t_unit tu ON tu.id = tp.unit_id AND tu.del_flag = 0
        LEFT JOIN t_category tc ON tc.id = tp.category_id    
        LEFT JOIN t_repertory_detail trd ON trd.product_id = tp.id
        LEFT JOIN t_repertory tr ON tr.id = trd.repertory_id
        WHERE
        trqi.del_flag = 'not_del'
        AND trqid.del_flag = 'not_del'
        AND tp.del_flag = 0
        AND (trqi.id = idstr or trqi.number = idstr)
        Group By tp.number;
        END；

13.比较特殊的一个存储过程，因为他的sql语句是拼接的，可以作为批量处理的一个方法

CREATE DEFINER=`root`@`%` PROCEDURE `return_materials_info_print`( IN idListStr varchar(1000))
BEGIN
  -- 声明与列的类型相同的变量
  TRUNCATE TABLE t_return_materials_info_print;
  set @id = idListStr;
  set @sel = "SELECT
        tp.`name` productName,
        tp.number productNumber,
        tp.specification specification,
        tu.`name`  unitName,
        tr.quantity quantity,
        tr.region_name regionName,
        tr.batch_number batchNumber,
        tr.remark remark,
        ts.`name` supplier,
        tr.apply_people reworkPeople
    FROM
        t_rework tr
    LEFT JOIN t_product tp ON tp.id = tr.product_id AND tp.del_flag = 0
    LEFT JOIN t_unit tu ON tu.id = tp.unit_id AND tu.del_flag = 0
    LEFT JOIN t_supplier ts ON ts.id = tp.supplier_id
    WHERE
    tr.del_flag = 'not_del'
    AND tr.id in('";
  set @sel_2 = "')";
  set @sentence = concat(@sel,@id,@sel_2);      -- 连接字符串生成要执行的SQL语句
  prepare stmt from @sentence;                  -- 预编释一下。 “stmt”预编释变量的名称，
  execute stmt;                                 -- 执行SQL语句
    
  deallocate prepare stmt; 
        END；

14.函数：声明变量，查询给变量赋值，返回变量

BEGIN
DECLARE shihtName VARCHAR(20);
SELECT
    t1.`name` INTO shihtName
FROM
    attendance_shiht t1
LEFT JOIN attendance_group_schedule t2 ON t1.id = t2.shiht_id
WHERE
    t2.group_id = groupId
AND t2.schedule_date = scheduleDate;
return shihtName;
END；

BEGIN
    DECLARE shiht_code VARCHAR(256) DEFAULT '';

    select GROUP_CONCAT(v.shiht_code) from view_group_rule v
        where v.group_id=group_id
    
    into shiht_code; 

    RETURN shiht_code; 
END；

15，函数体中while循环：

BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';
SET sTempChd = orgId;

WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_organization where FIND_IN_SET(superior,sTempChd)>0;
END WHILE;
return sTemp;
END；

BEGIN   
DECLARE fid varchar(100) default '';   
DECLARE str varchar(1000) default rootId;   
  
WHILE rootId is not null  do   
    SET fid =(SELECT superior FROM t_organization WHERE id = rootId);   
    IF fid is not null THEN   
        SET str = concat(str, ',', fid);   
        SET rootId = fid;   
    ELSE   
        SET rootId = fid;   
    END IF;   
END WHILE;   
return str;  
END；

 

16.哈哈，这是一个直接return结果的函数

BEGIN
    RETURN (SELECT
    REPLACE (
        GROUP_CONCAT(
            rule_start_time,
            '-',
            rule_end_time
        ORDER BY
            time_sort ASC
        ),
        ',',
        '  '
    )
FROM
    attendance_shift_time 
WHERE
    shiht_id = shihtId);
end；

16.嗯 ，这是一个汉字转拼音的函数；

BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;
    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;
    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;
    SET mycode = 0;
    SET lp = 1;
    SET NAME = HEX(NAME);
    WHILE lp < LENGTH(NAME) DO
        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED); 
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED); 
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1;
            SET lp = lp + 4;
        ELSE
            SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
    RETURN LOWER(mypy);
END；

17.这是一个查询子集的函数；

BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(BomId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(product_bom_id) INTO sTempChd FROM t_product_bom WHERE FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END；

18：这个存储过程也很有意思。它是一个有关分组排序取前几的一个过程

BEGIN
   set @num := 0, @type := '';
   select x.day,x.count,x.totalPrice,x.ventorName,x.rowNumber
    from (
   select day, IFNULL(count,0)count, IFNULL(totalPrice,0)totalPrice,IFNULL(ventorName,' ')ventorName, 
      @num := if(@type = day, @num + 1, 1) as rowNumber,
      @type := day as dummy
  from v_group_order_num
  order by day, totalPrice desc
  ) as x where x.rowNumber <= 3;
    END；-- @num是序号，@type是分组

19，嗯，这是一个类似bom查询子集的权限查询下级权限的函数

BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(rightId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM rbac_right WHERE FIND_IN_SET(parent_right_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END；

20：这是一个第几周的视图CREATE  VIEW `view_query_sevenweekbefore` AS SELECT
    WEEK (now(), 3) AS `week`
UNION ALL
    SELECT
        REPLACE (
            (yearweek(now(), 0) - 1),
            date_format(now(), '%Y'),
            ''
        ) AS `week`
    UNION ALL
        SELECT
            REPLACE (
                (yearweek(now(), 0) - 2),
                date_format(now(), '%Y'),
                ''
            ) AS `week`
        UNION ALL
            SELECT
                REPLACE (
                    (yearweek(now(), 0) - 3),
                    date_format(now(), '%Y'),
                    ''
                ) AS `week`
            UNION ALL
                SELECT
                    REPLACE (
                        (yearweek(now(), 0) - 4),
                        date_format(now(), '%Y'),
                        ''
                    ) AS `week`
                UNION ALL
                    SELECT
                        REPLACE (
                            (yearweek(now(), 0) - 5),
                            date_format(now(), '%Y'),
                            ''
                        ) AS `week`
                    UNION ALL
                        SELECT
                            REPLACE (
                                (yearweek(now(), 0) - 6),
                                date_format(now(), '%Y'),
                                ''
                            ) AS `week`

18：批量删除

DROP PROCEDURE IF EXISTS `my_batch_delete`;
DELIMITER ;;
CREATE  PROCEDURE `my_batch_delete`()
BEGIN
    -- 声明与列的类型相同的变量
    DECLARE
        nodata INT DEFAULT 0 ; #注意：这个变量声明必须放在游标声明前面
    DECLARE
        barCode VARCHAR (20) ; -- 1、定义一个游标mycursor
    DECLARE
        mycursor CURSOR FOR 
        SELECT
                    ttlr.barCode barCode
                FROM
                    t_barcode ttlr
                 ;
  DECLARE EXIT HANDLER FOR NOT FOUND
                    SET nodata = 1 ; #当读到数据的最后一条时,设置变量为1
                    -- 2、打开游标
    OPEN mycursor ;
            WHILE nodata = 0 DO
                        #判断是不是到了最后一条数据
                        -- 3、使用游标获取列的值
                        FETCH next FROM mycursor INTO barCode ; -- 4、显示结果
                        
            CALL my_batch_delete_barcode (barCode) ;
            END WHILE ; 
            -- 5、关闭游标
                        CLOSE mycursor ;
                        END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS `my_batch_delete_barcode`;
DELIMITER ;;
CREATE PROCEDURE `my_batch_delete_barcode`(IN `barCode` varchar(30))
BEGIN
DELETE FROM t_transitbox WHERE bar_code = barCode;
DELETE FROM t_transitbox_last_record WHERE bar_code =barCode;
DELETE FROM t_transitbox_record_history WHERE bar_code =barCode;
DELETE FROM t_repertory_detail  WHERE bar_code =barCode;
DELETE FROM t_transport_transbox  WHERE bar_code =barCode;
DELETE FROM t_region_transport_transbox  WHERE bar_code =barCode;
DELETE FROM t_purchase_enter_detail  WHERE bar_code =barCode;
DELETE FROM t_prepares_materials_box  WHERE bar_code =barCode;
DELETE FROM t_pick_materials_box  WHERE bar_code =barCode;
DELETE FROM t_repertory_out_detail  WHERE bar_code =barCode;
END
;;
DELIMITER ;