MySQL存儲(chǔ)過程 游標(biāo) 出錯(cuò)處理的示例代碼
發(fā)表時(shí)間:2023-07-19 來源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]MySQL存儲(chǔ)過程 游標(biāo) 錯(cuò)誤處理的示例代碼--set_account_data 重新生成用戶編號(hào)BEGINDECLARE temp_id INT(8); ...
MySQL存儲(chǔ)過程 游標(biāo) 錯(cuò)誤處理的示例代碼
--set_account_data 重新生成用戶編號(hào)
BEGIN
DECLARE temp_id INT(8); /*用戶id*/
DECLARE temp_manager INT(8); /*上級(jí)id*/
DECLARE temp_accounter_no VARCHAR(64); /*上級(jí)編碼*/
DECLARE temp_max_no VARCHAR(64); /*上級(jí)的最大下級(jí)編碼*/
DECLARE max_no VARCHAR(64); /*編碼*/
DECLARE str1 VARCHAR(64); /*編碼*/
DECLARE temp_no INT(8); /*編碼*/
DECLARE temp_level INT(8); /*級(jí)次*/
DECLARE state VARCHAR(30); /*錯(cuò)誤處理監(jiān)聽變量*/
/*定義用戶表游標(biāo)*/
DECLARE account_cursor CURSOR FOR SELECT id,manager FROM account ORDER BY manager,id;
/*定義錯(cuò)誤處理監(jiān)聽,用于結(jié)束游標(biāo)循環(huán)*/
DECLARE CONTINUE HANDLER FOR 1329
BEGIN
SET state = 'error';
END;
OPEN account_cursor;
REPEAT
FETCH account_cursor INTO temp_id,temp_manager;
IF (temp_id = 1) THEN
UPDATE account SET leaf = 0,no = '01',level = 1 WHERE id = 1;
ELSE
/*設(shè)置上級(jí)leaf為0*/
UPDATE account SET leaf = 0 WHERE id = temp_manager;
/*查詢上級(jí)編號(hào)*/
SELECT no INTO temp_accounter_no FROM account WHERE id = temp_manager;
/*設(shè)置上級(jí)編碼*/
UPDATE account SET pno = temp_accounter_no WHERE id = temp_id;
/*查詢上級(jí)原有的最大下級(jí)編碼*/
SELECT MAX(no) INTO temp_max_no FROM account WHERE pno = temp_accounter_no;
/*如果最大下級(jí)編碼為空,生成新的編碼,否則把原來的編碼加一*/
IF (temp_max_no IS NULL) THEN
SET max_no = concat(temp_accounter_no, '0001');
ELSE
SET str1 = SUBSTR(temp_max_no,LENGTH(temp_max_no)-3,4);
SET temp_no = str1;
SET temp_no = temp_no + 1;
SET str1 = temp_no;
IF (LENGTH(str1) = 1) THEN
SET str1 = concat('000', str1);
ELSEIF (LENGTH(str1) = 2) THEN
SET str1 = concat('00', str1);
ELSEIF (LENGTH(str1) = 3) THEN
SET str1 = concat('0', str1);
END IF;
SET max_no = concat(temp_accounter_no, str1);
END IF;
UPDATE account SET no = max_no WHERE id = temp_id;
SET temp_level = (LENGTH(max_no) + 2) / 4;
UPDATE account SET level = temp_level WHERE id = temp_id;
END IF;
UNTIL state = 'error'
END REPEAT;
CLOSE account_cursor;
/*修改leaf為null的為1*/
UPDATE account SET leaf = 1 WHERE leaf IS NULL;
RETURN 0;
END
以上就是MySQL存儲(chǔ)過程 游標(biāo) 錯(cuò)誤處理的示例代碼的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。