Hello,
I'm new in plsql, so I need your help. I'm trying to rewrite existing plpgsql function to plsql function, but I've got an Error at line 15: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
What's wrong with LOOP statement?
CREATE OR REPLACE FUNCTION dothework(service_name text, string_value text)
RETURN text AS
BEGIN
DECLARE
result text;
tempId character varying(31);
tempFieldId bigint;
tempStringValue text;
tempCount bigint;
BEGIN
result = '';
FOR tempId IN
SELECT ce.id FROM mfc.card_element ce
WHERE ce.name LIKE service_name
LOOP
tempCount = 0;
SELECT INTO tempFieldId field_id FROM mfc.card_field cf
WHERE cf.field_name = 'NPAname';
SELECT INTO tempCount count(*) FROM mfc.card_element_field_value cefv
WHERE cefv.card_element_id LIKE tempId AND field_id = tempFieldId;
IF tempCount = 1 THEN
UPDATE mfc.card_element_field_value
SET field_string_value = string_value
WHERE card_element_id LIKE tempId AND field_id = tempFieldId;
result := result || tempId || ' UPDATE - YES, ';
ELSIF tempCount = 0 THEN
INSERT INTO mfc.card_element_field_value(id, field_date_value,field_integer_value,field_string_value,card_element_id,field_id)
VALUES (HIBERNATE_SEQUENCE.nextval,
null,
null,
string_value,
tempId,
tempFieldId);
result := result || tempId || ' INSERT - YES, ';
ELSE
result := result || tempId || ' tempCount, ';
END IF;
--END IF;
END LOOP;
RETURN result;
END;
END;