MySQL XML解析及數組處理

来源：互联网发布：ubuntu不能创建文件夹编辑：程序博客网时间：2024/06/15 08:56

源碼：

DROP PROCEDURE IF EXISTS `uspUpdateRoleHasRights`;DELIMITER //CREATE PROCEDURE `uspUpdateRoleHasRights`(IN xmlData text)COMMENT'<rights><roleid>1</roleid><rightid>2</rightid><rightid>3</rightid><rightid>4</rightid></rights>'BEGINselect @roleId := EXTRACTVALUE(xmlData,'/rights/roleid');select @rightIds := EXTRACTVALUE(xmlData,'/rights/rightid');SET @index = 1;SET @right_id_count = CHAR_LENGTH(@rightids) - CHAR_LENGTH(REPLACE(@rightIds,' ','')) + 1;DELETE FROM role_has_rights WHERE roleid = @roleId;WHILE @index <= @right_id_countDOINSERT INTO role_has_rights(roleid,rightid) VALUES(@roleId,SUBSTRING_INDEX(SUBSTRING_INDEX(@rightIds,' ',@index),' ',-1));SET @index = @index + 1;END WHILE;END //DELIMITER ;

測試：

 call uspUpdateRoleHasRights(' <rights><roleid>1</roleid><rightid>1</rightid><rightid>2</rightid><rightid>3</rightid><rightid>4</rightid><rightid>5</rightid><rightid>6</rightid><rightid>7</rightid><rightid>8</rightid><rightid>9</rightid><rightid>10</rightid><rightid>11</rightid><rightid>12</rightid><rightid>13</rightid><rightid>14</rightid><rightid>15</rightid><rightid>16</rightid><rightid>17</rightid><rightid>18</rightid><rightid>19</rightid><rightid>20</rightid><rightid>21</rightid><rightid>22</rightid><rightid>23</rightid></rights> ');

0 0