How to place a variable as a pattern in the LIKE? SQL ORACLE

Question

How to place a variable as a pattern in the LIKE? SQL ORACLE

Navigation

#1 by (0 votes)

0

I am doing a project and I try to make a trigger which assigns the course number automatically.

This is the trigger that comes to mind

create or replace trigger TG_curso_Auto
before insert on cursos
for each row
declare
num_var int;
begin
    select count(*)+1 into num_var from cursos WHERE siglas LIKE '{VARIABLE EN ESTE CASO :NEW.siglas}%';
    :new.siglas := concat(concat(:new.siglas,'-'),TO_CHAR(num_var));

end;
/

This is the table:

create table cursos(
id number(3) not null,
nombre varchar(40) not null,
siglas varchar(7) not null,
estudiantes_inscritos number(2) not null);

EXAMPLE:

INSERT INTO cursos(id,nombre,siglas,estudiantes_inscritos) VALUES (1,'Bases 
de Datos',MBDA,7); -----> Deberia colocarme MBDA-1

INSERT INTO cursos(id,nombre,siglas,estudiantes_inscritos) VALUES (2,'Bases 
de Datos',MBDA,8); -----> Deberia colocarme MBDA-2

INSERT INTO cursos(id,nombre,siglas,estudiantes_inscritos) VALUES 
(3,'Programación Orientada a Objetos',POOB,8); -----> Deberia colocarme 
POOB-1

Thank you very much in advance.

sql oracle

asked by OrlandoGK 26.11.2018 в 22:32

source

1 answer

IIS And the basic authentication? Section closure Error getReference (). child () with firebase

score 0 · Accepted Answer

What you can do to achieve a dynamic use of the LIKE statement is to concatenate the variable in the following way:

CREATE OR REPLACE TRIGGER TG_CURSO_AUTO
  BEFORE INSERT ON cursos
  FOR EACH ROW
  DECLARE
   num_var NUMBER;
  BEGIN
    --
    SELECT COUNT(*) + 1
    INTO   num_var
    FROM   cursos
    WHERE  siglas LIKE :NEW.siglas||'%';
    --
    :new.siglas := :NEW.siglas||'-'||num_var;
END;
/

However, I would recommend you to be very careful with the trigger you are building, since you are consulting the same table on which the trigger itself is, and this could generate a mutation of the trigger, see here a reference of the topic link .