SHARE
TWEET

Untitled

a guest Sep 25th, 2017 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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;
RAW Paste Data
Top