56,679
社区成员
发帖
与我相关
我的任务
分享mysql 存储过程
DELIMITER $$
CREATE
PROCEDURE `test`(IN p_year INT,IN p_month INT,IN p_day INT,IN p_traiCode INT)
BEGIN
INSERT INTO `chart_monthly_payment` (
`create_by`,
`create_date`,
`update_by`,
`update_date`,
`del_flag`,
`name`,
`noontrusteeship`,
`tutor`,
`enhancement`,
`trainingclass`,
`trusteeship`,
dayTrusteeship,
`temproary`,
`weixin`,
`alipay`,
`cardpay`,
`QRcode`,
`expense`,
`bankbalance`,
`cashbalance`,
`office_id`,
`YEAR`,
`month`,
`day`
)SELECT
1 AS 'create_by',
NOW() AS 'create_date',
1 AS 'update_by',
NOW() AS 'update_date',
0 AS 'del_flag',
NULL AS 'name',
SUM(a.wutuo) AS 'noontrusteeship',
SUM(a.wanfu) AS 'tutor',
SUM(a.jiaqiang) AS 'enhancement',
SUM(a.peixun) AS 'trainingclass',
SUM(a.quantuo) AS 'trusteeship',
SUM(a.rituo) AS "dayTrusteeship",
0 AS 'temproary',
SUM(a.weiXin) AS 'weixin',
SUM(a.alipay) AS 'alipay',
SUM(a.cardPay) AS 'cardpay',
SUM(a.QRcode) AS 'QRcode',
0 AS 'expense',
0 AS 'bankbalance',
SUM(a.cashBalance) AS 'cashbalance',
p_traiCode AS 'office_id',
p_year AS "year",
p_month AS 'month',
p_day AS 'day'
FROM
`contract` c
LEFT JOIN
(SELECT
a.inco_year AS "year",
a.inco_mont AS "incoMont",
a.inco_date AS "incoDate",
wechat.pay_fund AS "weiXin",
alipay.pay_fund AS "alipay",
card.pay_fund AS "cardPay",
cash.pay_fund AS "cashBalance",
QRcode.pay_fund AS "QRcode",
cpp.peixun,
cpp.cont_code,
wutuo.wutuo,
rituo.rituo,
quantuo.quantuo,
jiaqiang.jiaqiang,
wanfu.wanfu,
o.name AS "companyName",
a.trai_code AS "officeId"
FROM
fund_income_print a
LEFT JOIN sys_office o
ON o.id = a.trai_code
LEFT JOIN
(SELECT
SUM(pay_fund) AS "pay_fund",
pay_type,
pare_id
FROM
contract_pay
WHERE pay_type = 1
GROUP BY pare_id,
pay_type) cash
ON cash.PARE_ID = a.cont_code
LEFT JOIN
(SELECT
SUM(pay_fund) AS "pay_fund",
pay_type,
pare_id
FROM
contract_pay
WHERE pay_type = 2
GROUP BY pare_id,
pay_type) QRcode
ON QRcode.PARE_ID = a.cont_code
LEFT JOIN
(SELECT
SUM(pay_fund) AS "pay_fund",
pay_type,
pare_id
FROM
contract_pay
WHERE pay_type = 4
GROUP BY pare_id,
pay_type) card
ON card.PARE_ID = a.cont_code
LEFT JOIN
(SELECT
SUM(pay_fund) AS "pay_fund",
pay_type,
pare_id
FROM
contract_pay
WHERE pay_type = 6
AND trxcode = 'VSP501'
GROUP BY pare_id,
pay_type,
trxcode) wechat
ON wechat.PARE_ID = a.cont_code
LEFT JOIN
(SELECT
SUM(pay_fund) AS "pay_fund",
pay_type,
pare_id
FROM
contract_pay
WHERE pay_type = 6
AND trxcode = 'VSP511'
GROUP BY pare_id,
pay_type,
trxcode) alipay
ON alipay.PARE_ID = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "peixun",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c
ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp
ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs
ON cs.cour_code = cp.courOrDegrCode
AND cp.type = 's'
LEFT JOIN `course_degree` cd
ON cd.degr_code = cp.courOrDegrCode
AND cp.type = 'd'
LEFT JOIN course_subject csub
ON csub.cour_code = cd.cour_code
WHERE (
cs.`COUR_TYPE` = 2
OR csub.`COUR_TYPE` = 2
)
GROUP BY ccp.cont_code) cpp
ON cpp.cont_code = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "wanfu",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c
ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp
ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs
ON cs.cour_code = cp.courOrDegrCode
AND cp.type = 's'
LEFT JOIN `course_degree` cd
ON cd.degr_code = cp.courOrDegrCode
AND cp.type = 'd'
LEFT JOIN course_subject csub
ON csub.cour_code = cd.cour_code
WHERE (
cs.`COUR_TYPE` = 1
OR csub.`COUR_TYPE` = 1
)
AND (
cs.`COUR_NAME` = '晚辅'
OR csub.COUR_NAME = '晚辅'
)
GROUP BY ccp.cont_code) wanfu
ON wanfu.cont_code = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "wutuo",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c
ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp
ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs
ON cs.cour_code = cp.courOrDegrCode
AND cp.type = 's'
LEFT JOIN `course_degree` cd
ON cd.degr_code = cp.courOrDegrCode
AND cp.type = 'd'
LEFT JOIN course_subject csub
ON csub.cour_code = cd.cour_code
WHERE (
cs.`COUR_TYPE` = 1
OR csub.`COUR_TYPE` = 1
)
AND (
cs.`COUR_NAME` = '午托'
OR csub.COUR_NAME = '午托'
)
GROUP BY ccp.cont_code) wutuo
ON wutuo.cont_code = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "jiaqiang",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c
ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp
ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs
ON cs.cour_code = cp.courOrDegrCode
AND cp.type = 's'
LEFT JOIN `course_degree` cd
ON cd.degr_code = cp.courOrDegrCode
AND cp.type = 'd'
LEFT JOIN course_subject csub
ON csub.cour_code = cd.cour_code
WHERE (
cs.`COUR_TYPE` = 1
OR csub.`COUR_TYPE` = 1
)
AND (
cs.`COUR_NAME` = '加强'
OR csub.COUR_NAME = '加强'
)
GROUP BY ccp.cont_code) jiaqiang
ON jiaqiang.cont_code = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "rituo",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c
ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp
ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs
ON cs.cour_code = cp.courOrDegrCode
AND cp.type = 's'
LEFT JOIN `course_degree` cd
ON cd.degr_code = cp.courOrDegrCode
AND cp.type = 'd'
LEFT JOIN course_subject csub
ON csub.cour_code = cd.cour_code
WHERE (
cs.`COUR_TYPE` = 1
OR csub.`COUR_TYPE` = 1
)
AND (
cs.`COUR_NAME` = '日托'
OR csub.COUR_NAME = '日托'
)
GROUP BY ccp.cont_code) rituo
ON rituo.cont_code = a.cont_code
LEFT JOIN
(SELECT
ROUND(
SUM(ccp.`PACK_VALU`) / c.`pack_rece_fund` * c.`pack_fund`,
2
) AS "quantuo",
ccp.cont_code
FROM
contract_course_package ccp
LEFT JOIN `contract` c ON c.`cont_code` = ccp.`CONT_CODE`
LEFT JOIN `course_package` cp ON cp.pack_code = ccp.pack_code
LEFT JOIN `course_subject` cs ON cs.cour_code = cp.courOrDegrCode AND cp.type = 's'
LEFT JOIN `course_degree` cd ON cd.degr_code = cp.courOrDegrCode AND cp.type = 'd'
LEFT JOIN course_subject csub ON csub.cour_code = cd.cour_code
WHERE (cs.`COUR_TYPE` = 1
OR csub.`COUR_TYPE` = 1)
AND (cs.`COUR_NAME` = '全托'
OR csub.COUR_NAME = '全托')
GROUP BY ccp.cont_code) quantuo
ON quantuo.cont_code = a.cont_code
WHERE a.del_flag = 0
AND a.inco_year = p_year
AND a.trai_code = p_traiCode
AND a.inco_mont = p_month
AND a.inco_date = p_day
GROUP BY a.`cont_code`) a ON a.cont_code = c.cont_code
WHERE a.year IS NOT NULL
END$$
DELIMITER ;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 316
存储过程搞不懂Begin end内单独运行就可以，放到存储过程就一直报这个错，请大神解答
...全文
请发表友善的回复…
发表回复
