Advertisement
Guest User

Untitled

a guest
Sep 25th, 2017
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.55 KB | None | 0 0
  1. #New procedure
  2. DELIMITER $$
  3. DROP PROCEDURE IF EXISTS createSelectView$$
  4. CREATE PROCEDURE createSelectView(
  5. IN customObjectName VARCHAR(255)
  6. )
  7. BEGIN
  8. DECLARE done INT DEFAULT FALSE;
  9. DECLARE columName VARCHAR(255);
  10. DECLARE selectColumns TEXT;
  11. DECLARE idTemp INT;
  12. DECLARE finalQuery LONGTEXT;
  13. DECLARE subQuery TEXT;
  14. DECLARE columnDataTypes TEXT;
  15. DECLARE columnNames TEXT;
  16. DECLARE entityIdName VARCHAR(255);
  17. DECLARE tempVar LONGTEXT;
  18.  
  19.  
  20. DECLARE cur1 CURSOR FOR SELECT DISTINCT UDFsName from CustomEntityDisplayValue where custObjName = customObjectName; #subquery
  21. DECLARE cur2 CURSOR FOR SELECT DISTINCT custEntId FROM CustomEntityDisplayValue where custObjName = customObjectName ; #subquery
  22. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  23.  
  24. SET entityIdName = 'entity_id';
  25. SET selectColumns= '';
  26. SET columnDataTypes= '';
  27. SET columnNames= '';
  28.  
  29.  
  30. OPEN cur1;
  31. read_loop: LOOP
  32. FETCH cur1 INTO columName;
  33. IF done THEN LEAVE read_loop; END IF;
  34.  
  35. IF (LENGTH(columName) < 64) THEN
  36. SET columnDataTypes = CONCAT(columnDataTypes,', \'',columName, '\' TEXT');
  37. SET columnNames = CONCAT(columnNames,', \'',columName,'\'');
  38. SET selectColumns = CONCAT(selectColumns, ', max((case when UDFsName = \'', columName, '\' then UDVCustEntValue end )) as \'', columName, '\'');
  39. END IF;
  40. END LOOP;
  41. CLOSE cur1;
  42.  
  43. SET selectColumns = CONCAT('max(UDFUpdatedAt) AS ', 'updatedAt',' ', selectColumns);
  44. SET selectColumns = CONCAT('max(UDFCreatedAt) AS ', 'createdAt,',' ', selectColumns);
  45. SET selectColumns = CONCAT('max(custEntId) AS ', 'entity_id,',' ', selectColumns);
  46. SET finalQuery= '';
  47. SET done = FALSE;
  48. SET subQuery = 'select * from CustomEntityDisplayValue '; #subquery
  49. SET columnDataTypes = CONCAT('( ', entityIdName, ' INT ', columnDataTypes, ')');
  50. SET columnNames = CONCAT(entityIdName, ' ', columnNames);
  51.  
  52. OPEN cur2;
  53. read_loop: LOOP
  54. FETCH cur2 INTO idTemp;
  55. IF done THEN LEAVE read_loop; END IF;
  56. SET finalQuery = CONCAT(finalQuery, ' UNION SELECT ', selectColumns, ' from CustomEntityDisplayValue where custEntId = ', idTemp);
  57. END LOOP;
  58. CLOSE cur2;
  59.  
  60. SET finalQuery = SUBSTR(finalQuery, 7);
  61.  
  62. #select finalQuery;
  63.  
  64. select LENGTH(finalQuery);
  65.  
  66.  
  67. SET @s = CONCAT('CREATE OR REPLACE VIEW ', customObjectName, ' AS ', finalQuery, ' ');
  68. PREPARE stmt FROM @s; EXECUTE stmt;
  69. END$$
  70. DELIMITER ;
  71.  
  72. #call createSelectView('clients');
  73.  
  74. #ALTER TABLE UDFs MODIFY display VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement