Using Dynamic Query in Function
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))
RETURNS BOOLEAN
READS SQL DATA
BEGIN
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;
open cur1;
repeat
FETCH cur1 INTO REGEX_VAL;
SELECT POSTAL_CODE REGEX REGEXVAL INTO isMATCH;
IF isMATCH = 1 THEN SET isVALID = TRUE;
END IF;
SET isMATCH = 0;
until done end repeat;
RETURN(isVALID);
END;
Edited 1 time(s). Last edit at 11/17/2012 06:59PM by Cory Hubert.
Subject
Views
Written By
Posted
Using Dynamic Query in Function
4179
November 17, 2012 06:50PM
1383
November 17, 2012 08:51PM
1158
November 17, 2012 10:30PM
1290
November 17, 2012 10:51PM
1260
November 17, 2012 11:05PM
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.