Using Dynamic Query in Function
Posted by: Cory Hubert
Date: November 17, 2012 06:50PM
I know I am pretty close... I have a table with regular expressions.... I have successfully retrieved the Regex Expression... Now I am trying to concat a simple query that will test to see if the value is valid.
What am I doing wrong here? See Below...
DROP FUNCTION IF EXISTS CountryRegEx;
CREATE FUNCTION CountryRegEx(COUNTRY_CODE char(2), POSTAL_CODE varchar(255))
READS SQL DATA
DECLARE REGEX_VAL VARCHAR(255);
DECLARE TO_RETURN_REGEX VARCHAR(255);
DECLARE done int default 0;
DECLARE isMATCH int;
DECLARE isVALID BOOLEAN;
DECLARE cur1 CURSOR FOR select MYSQL_REGEX_POSIX_1003_2 from POSTAL_CODE_REGEX_MANUAL where ISO_3166_ALPHA_2 =COUNTRY_CODE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
FETCH cur1 INTO REGEX_VAL;
SELECT POSTAL_CODE REGEX REGEXVAL INTO isMATCH;
IF isMATCH = 1 THEN SET isVALID = TRUE;
SET isMATCH = 0;
until done end repeat;
Edited 1 time(s). Last edit at 11/17/2012 06:59PM by Cory Hubert.
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.