DROP PROCEDURE
IF
EXISTS project_contract_jieduan;
CREATE PROCEDURE `project_contract_jieduan` ( ) BEGIN
DECLARE
_Done INT DEFAULT 0;
DECLARE
FID VARCHAR ( 50 );
DECLARE
c INT ( 8 );
DECLARE
i INT;
DECLARE
rs CURSOR FOR SELECT
itsm_cid FID,
-- 计算字符串中某个关键字出现次数
( CHAR_LENGTH( mbPlan ) - CHAR_LENGTH( REPLACE ( mbPlan, 'repay', '' ) ) ) / CHAR_LENGTH( 'repay' ) c
FROM
itsm_contract
WHERE
mbPlan IS NOT NULL;
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
SET _Done = 1;
OPEN rs;
DELETE
FROM
itsm_jieduan;
FETCH NEXT
FROM
rs INTO FID,
c;
REPEAT
IF
NOT _Done THEN
SET i = 1;
WHILE
i <= c DO
INSERT INTO itsm_jieduan ( ID, NAME, FID ) SELECT
CONCAT( FID, i ),
CONCAT( '第', i, '阶段' ),
FID;
SET i = i + 1;
END WHILE;
END IF;
FETCH NEXT
FROM
rs INTO FID,
c;
UNTIL _Done
END REPEAT;
CLOSE rs;
SELECT
PRID ID,
b.TITLE NAME,
NULL FID
FROM
px_project a
JOIN aa_flow b ON a.PRID = b.FLOW_ID
WHERE
b.TITLE IS NOT NULL
AND b.FLOW_STATUS >= 0
GROUP BY
b.FLOW_NO UNION
SELECT
a.itsm_cid ID,
a.itsm_ctitle NAME,
a.itsm_project FID
FROM
itsm_contract a
WHERE
a.itsm_project IS NOT NULL
AND a.itsm_project != ''
AND a.itsm_isDelete = 1 UNION
SELECT
*
FROM
itsm_jieduan;
END
mysql-存储过程
最新推荐文章于 2024-09-19 21:52:00 发布