0
I’m creating a procedure in PL SQL and I have an "unregulated" field that may have more than one comma-separated record.
That one procedurecarries out the cancellation of sending extracts of a matrix (trade) to a conciliator.
The matrix can send to up to 3 conciliators.
If the cancellation is made and the matrix had only 1 conciliator, this matrix becomes an EC, keeping only the EC in the CONCIL field, and if it has more than 1, it removes the desired conciliator and maintains the others. Remember, conciliators are separated by commas.
Is there any way to validate this field and remove the conciliator according to the above business rule?
Follow what I’m developing:
CREATE OR REPLACE PROCEDURE SP_CANCELAMENTO (
P_COD_MATRIZ INT,
P_CONCIL VARCHAR2, -- Parâmetro referente a coluna conciliadora
P_ID_CONCIL INT
)
IS
BEGIN
IF P_ID_CONCIL IS NOT NULL
THEN
DELETE FROM TB_EDIEXT_PERFIL_MBX WHERE ID_MBX = P_ID_CONCIL AND ID_PERFIL = (SELECT ID FROM TB_EDIEXT_PERFIL WHERE COD_MATRIZ = P_COD_MATRIZ);
END IF;
IF TB_EDIEXT_PERFIL.CD = 1
THEN
DELETE FROM TB_EDIEXT_PERFIL_CD WHERE ID_CD = P_ID_CONCIL AND ID_PERFIL = (SELECT ID FROM TB_EDIEXT_PERFIL WHERE COD_MATRIZ = P_COD_MATRIZ);
UPDATE TB_EDIEXT_PERFIL SET CD = 0 WHERE COD_MATRIZ = P_COD_MATRIZ;
ELSE IF TB_EDIEXT_PERFIL_SFTP = 1
THEN
DELETE FROM TB_EDIEXT_PERFIL_SFTP WHERE ID_SFTP = P_ID_CONCIL AND ID_PERFIL = (SELECT ID FROM TB_EDIEXT_PERFIL WHERE COD_MATRIZ = P_COD_MATRIZ);
UPDATE TB_EDIEXT_PERFIL SET SFTP = 0 WHERE COD_MATRIZ = P_COD_MATRIZ;
END IF;
END IF;
END;
/
See if the INSTR function can work for you. My suggestion is to make an IF (something like that): cContem := SELECT INSTR(conteud_metriz, conteudo_quer_delete) as positions FROM DUAL; IF cContem IS NOT NULL then 'HERE YOUR DELETE/UPDATE' ENDIF
– Victor Freidinger
https://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql basic idea string for array , treats array , array for string
– Motta
I managed to bring the position where I want to validate with the regexp_instr function but returns as number.
SELECT regexp_instr('CONCILIADORAX,CONCILIADORAY', ',', 1, 1, 0, 'i') FROM dual;@Motta, do you have any idea what array to string would look like? I looked at the link you gave me but I couldn’t...– rdevenz