MySQL存储过程创建、查看、调用及参数、存储过程权限介绍 - 牧人池塘 - DBA杂谈录

分类: MySQL

2017-12-22

暂无评论

MySQL存储过程创建、查看、调用及参数、存储过程权限介绍

存储过程(Stored Procedure)：
一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点(为什么要用存储过程？)：
　　将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用
　　批量处理：SQL+循环，减少流量，也就是“跑批”
　　统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。
一、存储过程的创建和调用
　　>存储过程就是具有名字的一段代码，用来完成一个特定的功能。
　　>创建的存储过程保存在数据库的数据字典中。
创建存储过程:

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

存储过程权限相关：

CREATE ROUTINE ： 创建存储过程的权限 
ALTER ROUTINE ： 修改存储过程的权限 
EXECUTE ：执行存储过程的权限
GRANT SELECT ON `mysql`.`proc` TO 'bis_enrolment_u'@'10.200.%' identified by 'xxxxx'; ; 普通账号没有查看存储过程或函数的权限，增加后可以看到系统有多少个函数或存储过程·

示例：创建一个存储过程:

DELIMITER $$  #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
DROP PROCEDURE IF EXISTS SP_ADS_SN$$
CREATE PROCEDURE SP_ADS_SN(IN _refreshStartYMD VARCHAR(8))
BEGIN

/*=============================================================================================================================================*/
/* 000.按品类统计日销售（统收类型 - 明细表）*/
/*=============================================================================================================================================*/
/*drop table if exists ADS_SN_CATEGORY_UNITE_DTL;
create table ADS_SN_CATEGORY_UNITE_DTL as*/
delete from ADS_SN_CATEGORY_UNITE_DTL where DATE >= _refreshStartYMD;
insert into ADS_SN_CATEGORY_UNITE_DTL
(select
    'UNITE' as CHARGE_TYPE,
    mall.MALL_NAME,
    mall.MALL_CODE,
    mall.SHORT_NAME,
    itm.ORDER_NO,
    itm.DATE,
    itm.TIME,
    itm.SHOP,
    itm.CMMDITY_CODE,
    itm.LEVEL_FOUR_CATEGORY,
    itm.STORE,
    itm.SAP_CONTRACT_NO,
    itm.SELL_REBATE,
    itm.SELL_MONEY,
    itm.REBATE_MONEY,
    itm.DATE_TIME,
    ord.SELL_BADGE,
    ord.INTEGRAL_AMOUNT,
    cont.MALL_ID,
    cont.CONT_NO,
    cont.CONT_TYPE,
    cont.BRAND_ID,
    cont.STORE_IDS,
    cont.STORE_NOS,
    cont.BRAND_NAME,
    cont.BUILDING_TYPE,
    cont.LAYOUT_NAME,
    cont.LAYOUT,
    cont.RENT_SQUARE,
    cont.SIGN_DATE,
    cont.CONT_BEGIN_DATE,
    cont.CONT_END_DATE,
    cont.STATUS,
    cont.CONT_FAIL_DATE,
    sbj.SUBJECT,
    sbj.BEGIN_DATE,
    sbj.END_TIME,
    pit.POINT,
    pit.MODEL
from
    (select
        ORDER_NO,
        DATE,
        TIME,
        SHOP,
        CMMDITY_CODE,
        LEVEL_FOUR_CATEGORY,
        STORE,
        SAP_CONTRACT_NO,
        SELL_REBATE,
        SELL_MONEY,
        ROUND(SELL_MONEY * IFNULL(SELL_REBATE, 0), 2) as REBATE_MONEY,
        CONCAT(DATE, TIME) as DATE_TIME
    from FIN_ITEMS
    where IS_DEL = '0'
        and DATE >= _refreshStartYMD
    ) itm
left outer join
    (select
        ORDER_NO,
        SELL_BADGE,
        INTEGRAL_AMOUNT
    from FIN_SALES
    where IS_DEL = '0'
    group by ORDER_NO, SELL_BADGE, INTEGRAL_AMOUNT
    ) ord
on itm.ORDER_NO = ord.ORDER_NO
left outer join
    (select
        MALL_ID,
        CONT_NO,
        CONT_TYPE,
        BRAND_ID,
        STORE_IDS,
        STORE_NOS,
        BRAND_NAME,
        BUILDING_TYPE,
        LAYOUT_NAME,
        LAYOUT,
        RENT_SQUARE,
        SIGN_DATE,
        CONT_BEGIN_DATE,
        CONT_END_DATE,
        STATUS,
        CONT_FAIL_DATE
    from BS_CONT
    where IS_DEL = '0'
    ) cont
on itm.SAP_CONTRACT_NO = cont.CONT_NO
left outer join
    (select
        ID,
        MALL_NAME,
        MALL_CODE,
        SHORT_NAME
    from BS_MALL
    where IS_DEL = '0'
    ) mall
on cont.MALL_ID = mall.ID
left outer join
    /*升降扣活动数据*/
    (select
        SUBJECT,
        BEGIN_DATE,
        END_TIME,
        PLAZA_CODE,
        SUBJECT_ID,
        DATE_FORMAT(BEGIN_DATE, '%Y%m%d%H%i%s') as BEGIN_DATE_TIME,
        DATE_FORMAT(END_TIME, '%Y%m%d%H%i%s') as END_DATE_TIME
    from FIN_SUBJECT_CFG
    where DELETE_FLAG = '0'
        and STATUS = '1'
    ) sbj
on mall.MALL_CODE = sbj.PLAZA_CODE and itm.DATE_TIME >= sbj.BEGIN_DATE_TIME and itm.DATE_TIME <= sbj.END_DATE_TIME
left outer join
    (select
        SUBJECT_ID,
        SHOP_CODE,
        COMMDTY_CODE,
        LEVEL_FOUR_CATEGORY,
        POINT,
        MODEL
    from FIN_POINTS_COMMDTY
    ) pit
on sbj.SUBJECT_ID = pit.SUBJECT_ID and itm.STORE = pit.SHOP_CODE and itm.CMMDITY_CODE = pit.COMMDTY_CODE and itm.LEVEL_FOUR_CATEGORY = pit.LEVEL_FOUR_CATEGORY
)
;

/*=============================================================================================================================================*/
/* 001.按品类统计日销售（非统收类型 - 明细表）*/
/*drop table if exists ADS_SN_CATEGORY_DISUNITY_DTL;
create table ADS_SN_CATEGORY_DISUNITY_DTL as*/
delete from ADS_SN_CATEGORY_DISUNITY_DTL where DATE >= _refreshStartYMD;
insert into ADS_SN_CATEGORY_DISUNITY_DTL
(select
    'DISUNITY' as CHARGE_TYPE,
    mall.MALL_NAME,
    mall.MALL_CODE,
    mall.SHORT_NAME,
    NULL as ORDER_NO,
    inc.INCOME_DATE as DATE,
    NULL as TIME,
    NULL as SHOP,
    NULL as CMMDITY_CODE,
    incdtl.CATEGORY_CODE as LEVEL_FOUR_CATEGORY,
    NULL as STORE,
    NULL as SAP_CONTRACT_NO,
    NULL as SELL_REBATE,
    incdtl.MONEY as SELL_MONEY,
    (incdtl.MONEY * APPLY_RATE / 100) as REBATE_MONEY,
    NULL as DATE_TIME,
    '0' as SELL_BADGE,
    0 as INTEGRAL_AMOUNT,
    cont.MALL_ID,
    cont.CONT_NO,
    cont.CONT_TYPE,
    cont.BRAND_ID,
    cont.STORE_IDS,
    cont.STORE_NOS,
    cont.BRAND_NAME,
    cont.BUILDING_TYPE,
    cont.LAYOUT_NAME,
    cont.LAYOUT,
    cont.RENT_SQUARE,
    cont.SIGN_DATE,
    cont.CONT_BEGIN_DATE,
    cont.CONT_END_DATE,
    cont.STATUS,
    cont.CONT_FAIL_DATE,
    NULL as SUBJECT,
    NULL as BEGIN_DATE,
    NULL as END_TIME,
    NULL as POINT,
    NULL as MODEL
from
    (select
        INCOME_DAY_ID,
        CATEGORY_CODE,
        CATEGORY_NAME,
        MONEY
    from FIN_INCOME_DAY_DETAIL
    where IS_DEL = '0'
    ) incdtl
left outer join
    (select
        ID,
        MALL_ID,
        CONT_ID,
        CONT_NO,
        DATE_FORMAT(INCOME_DATE, '%Y%m%d') as INCOME_DATE,
        INCOME_MONEY
    from FIN_INCOME_DAY
    where IS_DEL = '0'
        and DATE_FORMAT(INCOME_DATE, '%Y%m%d') >= _refreshStartYMD
    ) inc
on incdtl.INCOME_DAY_ID = inc.ID
left outer join
    (select
        MALL_ID,
        ID,
        CONT_NO,
        CONT_TYPE,
        BRAND_ID,
        STORE_IDS,
        STORE_NOS,
        BRAND_NAME,
        BUILDING_TYPE,
        LAYOUT_NAME,
        LAYOUT,
        RENT_SQUARE,
        SIGN_DATE,
        CONT_BEGIN_DATE,
        CONT_END_DATE,
        STATUS,
        CONT_FAIL_DATE
    from BS_CONT
    where IS_DEL = '0'
    ) cont
on inc.CONT_NO = cont.CONT_NO
left outer join
    (select
        ID,
        CONT_ID,
        START_DATE,
        END_DATE
    from BS_CONT_CHARGE
    where FEE_TYPE = '0'    /*租金*/
    ) chag
on cont.ID = chag.CONT_ID and inc.INCOME_DATE >= DATE_FORMAT(chag.START_DATE, '%Y%m%d') and inc.INCOME_DATE <= DATE_FORMAT(chag.END_DATE, '%Y%m%d')
left outer join
    (select
        CHARGE_ID,
        CATEGORY_CODE,
        APPLY_RATE
    from BS_CONT_CHARGE_STEP
    where IS_DEL = '0'
    ) stp
on chag.ID = stp.CHARGE_ID and incdtl.CATEGORY_CODE = stp.CATEGORY_CODE
left outer join
    (select
        ID,
        MALL_NAME,
        MALL_CODE,
        SHORT_NAME
    from BS_MALL
    where IS_DEL = '0'
    ) mall
on cont.MALL_ID = mall.ID
)
;

/*=============================================================================================================================================*/
/* 002.按品类统计日销售（含统收 + 非统收类型 - 明细表）*/
/*drop table if exists ADS_SN_CATEGORY_DTL;
create table ADS_SN_CATEGORY_DTL as*/
delete from ADS_SN_CATEGORY_DTL where DATE >= _refreshStartYMD;
insert into ADS_SN_CATEGORY_DTL
(select
    *
from
    (select
        *
    from ADS_SN_CATEGORY_UNITE_DTL
    union all
    select
        *
    from ADS_SN_CATEGORY_DISUNITY_DTL
    ) a
where DATE >= _refreshStartYMD
)
;

/*=============================================================================================================================================*/
/* 003.按品类统计日销售（汇总表）*/
delete from ADS_SN_CATEGORY_D where DATE >= _refreshStartYMD;
insert into ADS_SN_CATEGORY_D
(select
    MALL_CODE,
    SHORT_NAME,
    DATE,
    SAP_CONTRACT_NO,
    STORE_IDS,
    STORE_NOS,
    BRAND_ID,
    BRAND_NAME,
    LEVEL_FOUR_CATEGORY,
    SELL_BADGE,
    sum(INTEGRAL_AMOUNT) / 100 as INTEGRAL_AMOUNT,
    sum(SELL_MONEY) as SELL_MONEY,
    sum(REBATE_MONEY) as REBATE_MONEY
from ADS_SN_CATEGORY_DTL
where DATE >= _refreshStartYMD
group by MALL_CODE, SHORT_NAME, DATE, SAP_CONTRACT_NO, STORE_IDS, STORE_NOS, BRAND_ID, BRAND_NAME, LEVEL_FOUR_CATEGORY, SELL_BADGE
)
;



/*=============================================================================================================================================*/
/* 100. 按支付方式统计日销售（统收类型 - 支付明细表）*/
/*=============================================================================================================================================*/
/*drop table if exists ADS_SN_PAYMENTTYPE_UNITE_DTL;
create table ADS_SN_PAYMENTTYPE_UNITE_DTL as*/
delete from ADS_SN_PAYMENTTYPE_UNITE_DTL where DATE >= _refreshStartYMD;
insert into ADS_SN_PAYMENTTYPE_UNITE_DTL
(select
    mall.MALL_NAME,
    mall.MALL_CODE,
    mall.SHORT_NAME,
    cont.*,
    ord.SELL_BADGE,
    pmt.*,
    IFNULL(itm.SELL_MONEY, 0) as SELL_MONEY,
    IFNULL(itm.REBATE_MONEY, 0) as REBATE_MONEY
from
    (select
        ORDER_NO,
        DATE,
        COMPANY_CODE,
        SHOP,
        PAYMENT_TYPE,
        PAYMENT_TYPE_NAME,
        case when PAYMENT_TYPE in ('1401', '1403', '1404', '1405', '1501', '1503', '1504', '1505', '02', '0701', '0702') then 'POS'        /*POS银联卡（信用卡、借记卡）*/
             when PAYMENT_TYPE = '08' then 'POS_HUAXIATONG'                                                                                /*POS华夏通卡*/
             when PAYMENT_TYPE = '01' then 'CASH'                                                                                        /*现金*/
             when PAYMENT_TYPE = '23' then 'CONSUME_INTEGRAL'                                                                            /*消费积分*/
             when PAYMENT_TYPE in ('04', '16') then 'CASH_COUPON'                                                                        /*现金券（04：现金券；16：现金券退货）*/
             when PAYMENT_TYPE = '22' then 'DISCOUNT_COUPON'                                                                            /*优惠券*/
             when PAYMENT_TYPE = '0703' then 'STORED_VALUE_CARD'                                                                        /*储值卡（预付费卡）*/
             when PAYMENT_TYPE in ('19', '21') then 'YIFUBAO'                                                                            /*易付宝（19：易付宝；21：易付宝手工退卡）*/
             when PAYMENT_TYPE = '30' then 'ALIPAY'                                                                                        /*支付宝*/
             else '其他'
        end as PAYMENT_TYPE02,
        PAYMENT_NUMBER,
        CHANGE_NUM,
        OVER_NUM
    from FIN_PAYMENTS
    where IS_DEL = '0'
        and DATE >= _refreshStartYMD
    ) pmt
left outer join
    (select
        ORDER_NO,
        SAP_CONTRACT_NO,
        sum(SELL_MONEY) as SELL_MONEY,
        sum(ROUND(SELL_MONEY * IFNULL(SELL_REBATE, 0), 2)) as REBATE_MONEY
    from FIN_ITEMS
    where IS_DEL = '0'
    group by ORDER_NO, SAP_CONTRACT_NO
    ) itm
on pmt.ORDER_NO = itm.ORDER_NO
left outer join
    (select
        ORDER_NO,
        SELL_BADGE
    from FIN_SALES
    where IS_DEL = '0'
    group by ORDER_NO, SELL_BADGE
    ) ord
on pmt.ORDER_NO = ord.ORDER_NO
left outer join
    (select
        MALL_ID,
        CONT_NO,
        CONT_TYPE,
        BRAND_ID,
        STORE_IDS,
        STORE_NOS,
        BRAND_NAME,
        BUILDING_TYPE,
        LAYOUT_NAME,
        LAYOUT,
        RENT_SQUARE,
        SIGN_DATE,
        CONT_BEGIN_DATE,
        CONT_END_DATE,
        STATUS,
        CONT_FAIL_DATE
    from BS_CONT
    where IS_DEL = '0'
    ) cont
on itm.SAP_CONTRACT_NO = cont.CONT_NO
left outer join
    (select
        ID,
        MALL_NAME,
        MALL_CODE,
        SHORT_NAME
    from BS_MALL
    where IS_DEL = '0'
    ) mall
on cont.MALL_ID = mall.ID
)
;

/*=============================================================================================================================================*/
/* 101.按支付方式统计积分（统收类型 - 积分明细表）*/
/*drop table if exists ADS_SN_ORDER_INTEGRAL_UNITE_DTL;
create table ADS_SN_ORDER_INTEGRAL_UNITE_DTL as*/
delete from ADS_SN_ORDER_INTEGRAL_UNITE_DTL where DATE >= _refreshStartYMD;
insert into ADS_SN_ORDER_INTEGRAL_UNITE_DTL
(select
    ord.MALL_CODE,
    ord.ORDER_NO,
    itm.CONT_NO,
    ord.SELL_BADGE,
    ord.DATE,
    itm.SELL_MONEY,
    itm.REBATE_MONEY,
    ord.INTEGRAL_AMOUNT,                                                                    /*总生成积分*/
    (ord.INTEGRAL_AMOUNT * IFNULL(cfg.PLAZA_SCALE, 100) / 100) as INTEGRAL_AMOUNT_PLAZA,    /*广场承担积分*/
    (ord.INTEGRAL_AMOUNT * IFNULL(cfg.SHOP_SCALE, 100) / 100) as INTEGRAL_AMOUNT_SHOP        /*商户承担积分*/
from
    (select
        SHOP as MALL_CODE,
        ORDER_NO,
        DATE,
        TIME,
        str_to_date(concat(DATE, TIME), '%Y%m%d%H%i%s') as DATE_TIME,
        SELL_BADGE,
        SALE,
        INTEGRAL_AMOUNT
    from FIN_SALES
    where IS_DEL = '0'
        and DATE >= _refreshStartYMD
    ) ord
left outer join
    (select
        ORDER_NO,
        SAP_CONTRACT_NO as CONT_NO,
        STORE,
        sum(SELL_MONEY) as SELL_MONEY,
        sum(ROUND(SELL_MONEY * IFNULL(SELL_REBATE, 0), 2)) as REBATE_MONEY
    from FIN_ITEMS
    where IS_DEL = '0'
    group by ORDER_NO, SAP_CONTRACT_NO, STORE
    ) itm
on ord.ORDER_NO = itm.ORDER_NO
left outer join
    (select
        PLAZA_CODE,
        PROMO_ID,
        NAME,
        START_TIME,
        END_TIME
    from FIN_INTEGRAL
    where DELETE_FLAG = '0'
        and STATUS = '1'
    ) prm
on ord.MALL_CODE = prm.PLAZA_CODE and ord.DATE_TIME >= prm.START_TIME and ord.DATE_TIME <= prm.END_TIME
left outer join
    (select
        PROMO_ID,
        SHOP_CODE,
        PLAZA_SCALE,
        SHOP_SCALE
    from FIN_INTEGRAL_SHOP_CFG
    ) cfg
on prm.PROMO_ID = cfg.PROMO_ID and itm.STORE = cfg.SHOP_CODE
)
;

/*=============================================================================================================================================*/
/* 102.按支付方式统计日销售（统收类型 - 汇总表，支付方式打横）*/
/*drop table if exists ADS_SN_PAYMENTTYPE_UNITE_D;
create table ADS_SN_PAYMENTTYPE_UNITE_D as*/
delete from ADS_SN_PAYMENTTYPE_UNITE_D where DATE >= _refreshStartYMD;
insert into ADS_SN_PAYMENTTYPE_UNITE_D
(select
    'UNITE' as CHARGE_TYPE,
    pmt.*,
    (itg.INTEGRAL_AMOUNT / 100) as INTEGRAL_AMOUNT,
    (itg.INTEGRAL_AMOUNT_PLAZA / 100) as INTEGRAL_AMOUNT_PLAZA,
    (itg.INTEGRAL_AMOUNT_SHOP / 100) as INTEGRAL_AMOUNT_SHOP
from
    (select
        MALL_CODE,
        SHORT_NAME,
        DATE,
        CONT_NO,
        STORE_IDS,
        STORE_NOS,
        BRAND_ID,
        BRAND_NAME,
        SELL_BADGE,
        sum(SELL_MONEY) as SALE_AMOUNT,        /*折前金额*/
        sum(case when PAYMENT_TYPE02 = 'POS' then SELL_MONEY else 0 end) as POS_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'POS_HUAXIATONG' then SELL_MONEY else 0 end) as POS_HUAXIATONG_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'CASH' then SELL_MONEY else 0 end) as CASH_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'CONSUME_INTEGRAL' then SELL_MONEY else 0 end) as CONSUME_INTEGRAL_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'CASH_COUPON' then SELL_MONEY else 0 end) as CASH_COUPON_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'DISCOUNT_COUPON' then SELL_MONEY else 0 end) as DISCOUNT_COUPON_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'STORED_VALUE_CARD' then SELL_MONEY else 0 end) as STORED_VALUE_CARD_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'YIFUBAO' then SELL_MONEY else 0 end) as YIFUBAO_AMOUNT,
        sum(case when PAYMENT_TYPE02 = 'ALIPAY' then SELL_MONEY else 0 end) as ALIPAY_AMOUNT,
        sum(REBATE_MONEY) as REBATE_AMOUNT,        /*折后金额*/
        sum(case when PAYMENT_TYPE02 = 'POS' then REBATE_MONEY else 0 end) as POS_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'POS_HUAXIATONG' then REBATE_MONEY else 0 end) as POS_HUAXIATONG_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'CASH' then REBATE_MONEY else 0 end) as CASH_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'CONSUME_INTEGRAL' then REBATE_MONEY else 0 end) as CONSUME_INTEGRAL_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'CASH_COUPON' then REBATE_MONEY else 0 end) as CASH_COUPON_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'DISCOUNT_COUPON' then REBATE_MONEY else 0 end) as DISCOUNT_COUPON_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'STORED_VALUE_CARD' then REBATE_MONEY else 0 end) as STORED_VALUE_CARD_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'YIFUBAO' then REBATE_MONEY else 0 end) as YIFUBAO_AMOUNT_RBT,
        sum(case when PAYMENT_TYPE02 = 'ALIPAY' then REBATE_MONEY else 0 end) as ALIPAY_AMOUNT_RBT
    from ADS_SN_PAYMENTTYPE_UNITE_DTL
    group by MALL_CODE, SHORT_NAME, DATE, CONT_NO, STORE_IDS, STORE_NOS, BRAND_ID, BRAND_NAME, SELL_BADGE
    ) pmt
left outer join
    (select
        MALL_CODE,
        CONT_NO,
        DATE,
        sum(INTEGRAL_AMOUNT) as INTEGRAL_AMOUNT,
        sum(INTEGRAL_AMOUNT_PLAZA) as INTEGRAL_AMOUNT_PLAZA,
        sum(INTEGRAL_AMOUNT_SHOP) as INTEGRAL_AMOUNT_SHOP
    from ADS_SN_ORDER_INTEGRAL_UNITE_DTL
    group by MALL_CODE, CONT_NO
    ) itg
on pmt.MALL_CODE = itg.MALL_CODE and pmt.CONT_NO = itg.CONT_NO and pmt.DATE = itg.DATE
)
;

/*=============================================================================================================================================*/
/* 103.按支付方式统计日销售（非统收类型 - 汇总表）*/
/*drop table if exists ADS_SN_PAYMENTTYPE_DISUNITY_D;
create table ADS_SN_PAYMENTTYPE_DISUNITY_D as*/
delete from ADS_SN_PAYMENTTYPE_DISUNITY_D where DATE >= _refreshStartYMD;
insert into ADS_SN_PAYMENTTYPE_DISUNITY_D
(select
    'DISUNITY' as CHARGE_TYPE,
    MALL_CODE,
    SHORT_NAME,
    DATE,
    CONT_NO,
    STORE_IDS,
    STORE_NOS,
    BRAND_ID,
    BRAND_NAME,
    SELL_BADGE,
    sum(SELL_MONEY) as SALE_AMOUNT,
    0 as POS_AMOUNT,
    0 as POS_HUAXIATONG_AMOUNT,
    0 as CASH_AMOUNT,
    0 as CONSUME_INTEGRAL_AMOUNT,
    0 as CASH_COUPON_AMOUNT,
    0 as DISCOUNT_COUPON_AMOUNT,
    0 as STORED_VALUE_CARD_AMOUNT,
    0 as YIFUBAO_AMOUNT,
    0 as ALIPAY_AMOUNT,
    sum(REBATE_MONEY) as REBATE_AMOUNT,
    0 as POS_AMOUNT_RBT,
    0 as POS_HUAXIATONG_AMOUNT_RBT,
    0 as CASH_AMOUNT_RBT,
    0 as CONSUME_INTEGRAL_AMOUNT_RBT,
    0 as CASH_COUPON_AMOUNT_RBT,
    0 as DISCOUNT_COUPON_AMOUNT_RBT,
    0 as STORED_VALUE_CARD_AMOUNT_RBT,
    0 as YIFUBAO_AMOUNT_RBT,
    0 as ALIPAY_AMOUNT_RBT,
    0 as INTEGRAL_AMOUNT,
    0 as INTEGRAL_AMOUNT_PLAZA,
    0 as INTEGRAL_AMOUNT_SHOP
from ADS_SN_CATEGORY_DISUNITY_DTL
group by MALL_CODE, SHORT_NAME, DATE, CONT_NO, STORE_IDS, STORE_NOS, BRAND_ID, BRAND_NAME, SELL_BADGE
)
;

/*=============================================================================================================================================*/
/* 104.按支付方式统计日销售（含统收 + 非统收类型 - 汇总表）*/
/*delete from ADS_SN_PAYMENTTYPE_D where DATE >= _refreshStartYMD;
insert into ADS_SN_PAYMENTTYPE_D*/
delete from ADS_SN_PAYMENTTYPE_D where DATE >= _refreshStartYMD;
insert into ADS_SN_PAYMENTTYPE_D
(select
    *
from
    (select
        *
    from ADS_SN_PAYMENTTYPE_UNITE_D
    union all
    select
        *
    from ADS_SN_PAYMENTTYPE_DISUNITY_D
    ) a
where DATE >= _refreshStartYMD
)
;

END$$
DELIMITER ;  #将语句的结束符号恢复为分号

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀；
在定义过程时，使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

调用存储过程：call sp_name[(传参)];

root@mysqldb 13:18:  [test]> call SP_ADS_SN(date_format(date_sub(curdate(), interval 3 day),'%Y%m%d'));
Query OK, 3 rows affected (15.68 sec)
call SP_ADS_SN('19900101');

存储过程的参数
存储过程可以有0个或多个参数，用于存储过程的定义。

3种参数类型：
　　IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
　　OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
　　INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
查看存储过程：
查看数据库里所有存储过程+内容

show procedure status\G;

查看当前数据库里存储过程列表

select specific_name from mysql.proc ;

按数据库查询

select specific_name from mysql.proc where  `type` = 'procedure' and db='DatabaseName';

查看存储过程具体内容：

select body from mysql.proc where specific_name = 'your_proc_name';

查看存储过程、函数的创建代码：

show create procedure your_proc_name;
show create function your_func_name;

删除存储、函数过程：

drop procedure your_proc_name;
drop function your_func_name;

创建 MySQL 存储过程的简单语法为：

create procedure 存储过程名字()
(
   [in|out|inout] 参数 datatype
)
begin
   MySQL 语句;
end;

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。

MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
MySQL 存储过程参数，不能在参数名称前加“@”，如：mailto:%E2%80%9C@a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。

create procedure pr_add
(
   @a int,- 错误
   b int   - 正确
)

MySQL 存储过程的参数不能指定默认值。
MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。

create procedure pr_add
(
   a int,
   b int
)
as             - 错误，MySQL 不需要 “as”
begin
   mysql statement ...;
end;

如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。

create procedure pr_add
(
   a int,
   b int
)
begin
   mysql statement 1 ...;
   mysql statement 2 ...;
end;

MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”

   ...

   declare c int;

   if a is null then
      set a = 0;
   end if;

   ...
end;

MySQL 存储过程中的注释。

   /*
     这是个
     多行 MySQL 注释。
  /

   declare c int;    - 这是单行 MySQL 注释 （注意- 后至少要有一个空格）

   if a is null then 这也是个单行 MySQL 注释
      set a = 0;
   end if;

   ...
end;

不能在 MySQL 存储过程中使用 “return” 关键字。

   set c = a + b;

   select c as sum;

   /*
   return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
  /
end;

调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”

call pr_no_param();

因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。

call pr_add(10, null);

Tag标签:「权限存储过程参数调用」更新时间:「2021-11-02 17:35:52」阅读次数:「908」

相关博文：