存储过程(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」阅读次数:「1303」