达梦：实现函数unnest + string_to_array改写

原语句：

SELECT unnest(string_to_array('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout',',')) as Variable_name, unnest(string_to_array('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800',',')) as VALUE

 核心实现：

SELECT rownum id,
             regexp_substr('para1,para2,para3', '[^,]+', 1, LEVEL) para_name
        FROM dual
             CONNECT BY LEVEL <= LENGTH('para1,para2,para3')-LENGTH(REPLACE('para1,para2,para3', ','))+ 1

最终改写结果：

with temp_para AS
     ( SELECT rownum id,
             regexp_substr('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout', '[^,]+', 1, LEVEL) para_name
        FROM dual
             CONNECT BY LEVEL <= LENGTH('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout')-LENGTH(REPLACE('character_set_client,character_set_connection,character_set_results,character_set_server,init_connect,interactive_timeout,lower_case_table_names,max_allowed_packet,net_buffer_length,net_write_timeout,query_cache_size,query_cache_type,sql_mode,system_time_zone,system_time_zone,time_zonetransaction_isolation,tx_isolation,wait_timeout', ','))+ 1
     )
     ,
     temp_value as
     (SELECT rownum id,
             regexp_substr('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800', '[^,]+', 1, LEVEL) para_value
        FROM dual
             CONNECT BY LEVEL <= LENGTH('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800')-LENGTH(REPLACE('utf8,utf8,utf8,utf8mb4,,28800,0,4194304,16384,60,1048576,OFF,,CST,SYSTEM,REPEATABLE-READ,REPEATABLE-READ,28800', ','))+ 1
     )
  select para_name,
         para_value
    from temp_para,
         temp_value
   where temp_para.id=temp_value.id
order by temp_para.id;

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~分享完毕啦~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

更多内容，请访问达梦社区地址：https:eco.dameng.com